{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Relational Algebra\n", "---\n", "\n", "`Relational algebra` is a `procedural query language`, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An `operator` can be either unary or binary. They accept relations as their input and yield relations as their output." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Connecting with the database" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%load_ext sql\n", "# replace with your own credentials\n", "%sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • SELECT\n", "it is used to select the subset of the tuples of a relation that satisfies the selection condition." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
United StatesMarshallBernadot54
United StatesCelindaMalkin51
UkraineGuillermoFurze53
UkraineAharonTunnow48
RussiaBailGoodwin46
PolandColeWinteringham49
SwedenEmlynErricker55
RussiaCatheeSivewright49
PolandBarnyIngerson57
SwedenSharlaPapaccio55
RomaniaAlinaSmolyar52
" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54),\n", " ('United States', 'Celinda', 'Malkin', 51),\n", " ('Ukraine', 'Guillermo', 'Furze', 53),\n", " ('Ukraine', 'Aharon', 'Tunnow', 48),\n", " ('Russia', 'Bail', 'Goodwin', 46),\n", " ('Poland', 'Cole', 'Winteringham', 49),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Russia', 'Cathee', 'Sivewright', 49),\n", " ('Poland', 'Barny', 'Ingerson', 57),\n", " ('Sweden', 'Sharla', 'Papaccio', 55),\n", " ('Romania', 'Alina', 'Smolyar', 52)]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • PROJECT\n", "It selects only those columns or attributes in which the user is intersted." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
first_nametest_score
Marshall54
Celinda51
Guillermo53
Aharon48
Bail46
Cole49
Emlyn55
Cathee49
Barny57
Sharla55
Alina52
" ], "text/plain": [ "[('Marshall', 54),\n", " ('Celinda', 51),\n", " ('Guillermo', 53),\n", " ('Aharon', 48),\n", " ('Bail', 46),\n", " ('Cole', 49),\n", " ('Emlyn', 55),\n", " ('Cathee', 49),\n", " ('Barny', 57),\n", " ('Sharla', 55),\n", " ('Alina', 52)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select first_name,test_score from STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • SET OPERATIONS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Lets make another table since we'll need two tables in the following operations." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\n", "Done.\n", "4 rows affected.\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
MexicoNatashaMartinax48
MexicoRachaelGrate45
RomaniaAlexandraBotez57
RomaniaAlinaSmolyar52
" ], "text/plain": [ "[('Mexico', 'Natasha', 'Martinax', 48),\n", " ('Mexico', 'Rachael', 'Grate', 45),\n", " ('Romania', 'Alexandra', 'Botez', 57),\n", " ('Romania', 'Alina', 'Smolyar', 52)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "drop table NEW_STUDENTS;\n", "create table NEW_STUDENTS (\n", "\tcountry VARCHAR(50),\n", "\tfirst_name VARCHAR(50),\n", "\tlast_name VARCHAR(50),\n", "\ttest_score INT\n", ");\n", "insert into NEW_STUDENTS (country, first_name, last_name, test_score)\n", "values\n", "('Mexico', 'Natasha', 'Martinax', 48),\n", "('Mexico', 'Rachael', 'Grate', 45),\n", "('Romania', 'Alexandra', 'Botez', 57),\n", "('Romania', 'Alina', 'Smolyar', 52);\n", "select * from NEW_STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. UNION\n", "It is used to combine the results of two or more select statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and data type must be saame in all the tables." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
MexicoRachaelGrate45
RussiaBailGoodwin46
MexicoNatashaMartinax48
UkraineAharonTunnow48
RussiaCatheeSivewright49
PolandColeWinteringham49
United StatesCelindaMalkin51
RomaniaAlinaSmolyar52
UkraineGuillermoFurze53
United StatesMarshallBernadot54
SwedenEmlynErricker55
SwedenSharlaPapaccio55
RomaniaAlexandraBotez57
PolandBarnyIngerson57
" ], "text/plain": [ "[('Mexico', 'Rachael', 'Grate', 45),\n", " ('Russia', 'Bail', 'Goodwin', 46),\n", " ('Mexico', 'Natasha', 'Martinax', 48),\n", " ('Ukraine', 'Aharon', 'Tunnow', 48),\n", " ('Russia', 'Cathee', 'Sivewright', 49),\n", " ('Poland', 'Cole', 'Winteringham', 49),\n", " ('United States', 'Celinda', 'Malkin', 51),\n", " ('Romania', 'Alina', 'Smolyar', 52),\n", " ('Ukraine', 'Guillermo', 'Furze', 53),\n", " ('United States', 'Marshall', 'Bernadot', 54),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Sweden', 'Sharla', 'Papaccio', 55),\n", " ('Romania', 'Alexandra', 'Botez', 57),\n", " ('Poland', 'Barny', 'Ingerson', 57)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS union select * from NEW_STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. INTERSECTION\n", "It returna the rows which are common in both the tables." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
RomaniaAlinaSmolyar52
" ], "text/plain": [ "[('Romania', 'Alina', 'Smolyar', 52)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS intersect select * from NEW_STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. DIFFERENCE\n", "It combines the result of two select staements and returns only those rows which belongs to first set of result." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * ibm_db_sa://lzv55210:***@dashdb-txn-sbox-yp-dal09-12.services.dal.bluemix.net:50000/BLUDB\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
United StatesMarshallBernadot54
United StatesCelindaMalkin51
UkraineGuillermoFurze53
UkraineAharonTunnow48
RussiaBailGoodwin46
PolandColeWinteringham49
SwedenEmlynErricker55
RussiaCatheeSivewright49
PolandBarnyIngerson57
SwedenSharlaPapaccio55
" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54),\n", " ('United States', 'Celinda', 'Malkin', 51),\n", " ('Ukraine', 'Guillermo', 'Furze', 53),\n", " ('Ukraine', 'Aharon', 'Tunnow', 48),\n", " ('Russia', 'Bail', 'Goodwin', 46),\n", " ('Poland', 'Cole', 'Winteringham', 49),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Russia', 'Cathee', 'Sivewright', 49),\n", " ('Poland', 'Barny', 'Ingerson', 57),\n", " ('Sweden', 'Sharla', 'Papaccio', 55)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select STUDENTS.* from STUDENTS where STUDENTS.first_name not in ( select first_name from NEW_STUDENTS )" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Thanks for reading :)\n", "Created by [Tarun Kamboj](https://www.linkedin.com/in/kambojtarun/)." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.6" } }, "nbformat": 4, "nbformat_minor": 4 }