{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Arithemetic and Comparison Operators\n", "---" ] }, { "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": [ "### • ARITHMETIC OPERATORS\n", "Arithmetic operators can perform arithmetical operations on numeric operands involved. Arithmetic operators are addition(+), subtraction(-), multiplication(*) and division(/)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. SQL plus (+) operator\n", "This operator is used to add two or more expressions or numbers." ] }, { "cell_type": "code", "execution_count": 10, "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
United StatesCelindaMalkin51
" ], "text/plain": [ "[('United States', 'Celinda', 'Malkin', 51)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score = 31+20" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. SQL minus (-) operator\n", "This operator is used to subtract on expression/number from another expression/number." ] }, { "cell_type": "code", "execution_count": 11, "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
United StatesMarshallBernadot54
" ], "text/plain": [ "[('United States', 'Marshall', 'Bernadot', 54)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score = 84-30" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. SQL multiply (*) operator\n", "This operator is usd to multiply two or more expressions/numbers." ] }, { "cell_type": "code", "execution_count": 12, "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
SwedenEmlynErricker55
SwedenSharlaPapaccio55
" ], "text/plain": [ "[('Sweden', 'Emlyn', 'Erricker', 55), ('Sweden', 'Sharla', 'Papaccio', 55)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score = 11*5" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. SQL divide (/) operator\n", "This operator is used to divide one expression/number by another." ] }, { "cell_type": "code", "execution_count": 13, "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
PolandColeWinteringham49
RussiaCatheeSivewright49
" ], "text/plain": [ "[('Poland', 'Cole', 'Winteringham', 49),\n", " ('Russia', 'Cathee', 'Sivewright', 49)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score = 98/2" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. SQL modulo (%) operator\n", "This operator returns the remainder(an integer) of the division." ] }, { "cell_type": "code", "execution_count": 14, "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", "
countryfirst_namelast_nametest_score
" ], "text/plain": [ "[]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score = 100%2\n", "# No row should show up" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### • COMPARISON OPERATORS\n", "Comparison operators are used in conditions that compares one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. EQUALITY operator\n", "This operator tests the equality in the query." ] }, { "cell_type": "code", "execution_count": 15, "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
PolandColeWinteringham49
PolandBarnyIngerson57
" ], "text/plain": [ "[('Poland', 'Cole', 'Winteringham', 49), ('Poland', 'Barny', 'Ingerson', 57)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where country = 'Poland'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. INEQUALITY operator\n", "In SQL, there are two ways to test the inequality in a query. Either by `<>` or by `!=` operator." ] }, { "cell_type": "code", "execution_count": 16, "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
UkraineGuillermoFurze53
UkraineAharonTunnow48
RussiaBailGoodwin46
SwedenEmlynErricker55
RussiaCatheeSivewright49
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", " ('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Russia', 'Cathee', 'Sivewright', 49),\n", " ('Sweden', 'Sharla', 'Papaccio', 55)]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where country != 'Poland'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. GREATER THAN operator\n", "We can use the `>` operator in SQL to test for an expression greater than." ] }, { "cell_type": "code", "execution_count": 17, "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", "
countryfirst_namelast_nametest_score
United StatesMarshallBernadot54
United StatesCelindaMalkin51
UkraineGuillermoFurze53
SwedenEmlynErricker55
PolandBarnyIngerson57
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", " ('Poland', 'Barny', 'Ingerson', 57),\n", " ('Sweden', 'Sharla', 'Papaccio', 55)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score > 50" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. GREATER THAN OR EQUAL operator\n", "In SQL, we can use the `>=` operator to test for an expression greater than or equal to." ] }, { "cell_type": "code", "execution_count": 18, "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", "
countryfirst_namelast_nametest_score
SwedenEmlynErricker55
PolandBarnyIngerson57
SwedenSharlaPapaccio55
" ], "text/plain": [ "[('Sweden', 'Emlyn', 'Erricker', 55),\n", " ('Poland', 'Barny', 'Ingerson', 57),\n", " ('Sweden', 'Sharla', 'Papaccio', 55)]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score >= 55" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. LESS THAN operator\n", "We can use the `<` operator in SQL to test for an expression less than." ] }, { "cell_type": "code", "execution_count": 19, "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
UkraineAharonTunnow48
RussiaBailGoodwin46
" ], "text/plain": [ "[('Ukraine', 'Aharon', 'Tunnow', 48), ('Russia', 'Bail', 'Goodwin', 46)]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score < 49" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 6. LESS THAN OR EQUAL operator\n", "In SQL, we can use the `<=` operator to test for an expression less than or equal to." ] }, { "cell_type": "code", "execution_count": 20, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
UkraineAharonTunnow48
RussiaBailGoodwin46
PolandColeWinteringham49
RussiaCatheeSivewright49
" ], "text/plain": [ "[('Ukraine', 'Aharon', 'Tunnow', 48),\n", " ('Russia', 'Bail', 'Goodwin', 46),\n", " ('Poland', 'Cole', 'Winteringham', 49),\n", " ('Russia', 'Cathee', 'Sivewright', 49)]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from STUDENTS where test_score <= 49" ] }, { "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 }