{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Logical operations\n", "---\n", "The Logical operators are those that are true or false. They return a true or false values to combine one or more true or false values." ] }, { "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": [ "### 1. AND Operator\n", "This operator is used to temporarily show rows only when all the specified conditions are true." ] }, { "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", "Done.\n", "10 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", " \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": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "select * from STUDENTS;" ] }, { "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", "
countryfirst_namelast_nametest_score
UkraineAharonTunnow48
" ], "text/plain": [ "[('Ukraine', 'Aharon', 'Tunnow', 48)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where country = 'Ukraine' and first_name = 'Aharon'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. OR Operator\n", "This operator is used to temporarily show rows when atleast one of the specified conditions are true." ] }, { "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" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
UkraineGuillermoFurze53
UkraineAharonTunnow48
" ], "text/plain": [ "[('Ukraine', 'Guillermo', 'Furze', 53), ('Ukraine', 'Aharon', 'Tunnow', 48)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where country = 'Ukraine' or first_name = 'Aharon'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. NOT Operator\n", "This operator is used to temporarily show rows only when all the specified condition is false." ] }, { "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", "
countryfirst_namelast_nametest_score
United StatesMarshallBernadot54
United StatesCelindaMalkin51
RussiaBailGoodwin46
PolandColeWinteringham49
SwedenEmlynErricker55
RussiaCatheeSivewright49
PolandBarnyIngerson57
SwedenSharlaPapaccio55
" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54),\n", " ('United States', 'Celinda', 'Malkin', 51),\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": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where country != 'Ukraine'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. IN Operator\n", "This operator is used to temporarily show rows which are present in the values passed in paranthesis." ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
RussiaBailGoodwin46
PolandBarnyIngerson57
" ], "text/plain": [ "[('Russia', 'Bail', 'Goodwin', 46), ('Poland', 'Barny', 'Ingerson', 57)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score in(57,46)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. BETWEEN Operator\n", "This operator is used to temporarily show rows which lies between the two values(included) given." ] }, { "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", "
countryfirst_namelast_nametest_score
United StatesMarshallBernadot54
United StatesCelindaMalkin51
UkraineGuillermoFurze53
SwedenEmlynErricker55
SwedenSharlaPapaccio55
" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54),\n", " ('United States', 'Celinda', 'Malkin', 51),\n", " ('Ukraine', 'Guillermo', 'Furze', 53),\n", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Sweden', 'Sharla', 'Papaccio', 55)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score between 50 and 55" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 6. LIKE Operator\n", "Like operator is used to temporarily show all the rows having same alphanumeric in given attribute." ] }, { "cell_type": "code", "execution_count": 8, "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
UkraineAharonTunnow48
" ], "text/plain": [ "[('Ukraine', 'Aharon', 'Tunnow', 48)]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where first_name like 'A%'" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "scrolled": true }, "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
SwedenEmlynErricker55
" ], "text/plain": [ "[('Sweden', 'Emlyn', 'Erricker', 55)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where first_name like 'E___n'" ] }, { "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 }