{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Data Manipulation Language(DML)\n", "---\n", "Data Manipulation Language deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database." ] }, { "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. Insert Command\n", "Insert command is used to insert values to tables. In other words inserting values in tuples." ] }, { "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", "10 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "insert into STUDENTS (country, first_name, last_name, test_score)\n", "values\n", "('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)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. Select Command\n", "Select command is used to select a table. It can be achieved by just writing the name of the table." ] }, { "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 * from STUDENTS" ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", "
countryfirst_namelast_nametest_score
UkraineGuillermoFurze53
UkraineAharonTunnow48
" ], "text/plain": [ "[('Ukraine', 'Guillermo', 'Furze', 53), ('Ukraine', 'Aharon', 'Tunnow', 48)]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "country = \"Ukraine\"\n", "%sql select * from STUDENTS where country = :country" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. Update Command\n", "Update command is used to make changes in any tuple. It is achieved by using field value of any other attribute of that particular tuple." ] }, { "cell_type": "code", "execution_count": 9, "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", "1 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
UkraineAharonTunnow52
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', 52),\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": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "update STUDENTS \n", "set test_score = 52 \n", "where first_name='Aharon';\n", "\n", "select * from STUDENTS" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. Delete Command\n", "Delete command is used to delete any tuple from the table." ] }, { "cell_type": "code", "execution_count": 10, "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", "1 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", "
countryfirst_namelast_nametest_score
United StatesMarshallBernadot54
United StatesCelindaMalkin51
UkraineGuillermoFurze53
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", " ('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": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "delete from STUDENTS where first_name='Aharon' ;\n", "\n", "select * from 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 }