Home
About
Resume
Projects
Links
Blog
Download notebook
{ "cells": [ { "cell_type": "markdown", "id": "5c02fefb-09ac-4837-8883-e9b27d3c2f17", "metadata": {}, "source": [ "### MySQL Select" ] }, { "cell_type": "markdown", "id": "d46049be-7597-4335-942e-b49f8fb0b1b8", "metadata": {}, "source": [ "##### Pre-requisites\n", "1. Install MySQL kernel for Jupyter Notebook `pip install mysql_kernel`\n", "\n", "2. [Create](/notebook/MySQL%20Users%20and%20Privileges/) a user with privileges to the database `classicmodels`\n", "\n", "3. [Download](/notebook/raw/mysql_example_database.sql) and [import](/notebook/MySQL%20Import%20Database%20from%20SQL%20File/) example database `classicmodels`" ] }, { "cell_type": "markdown", "id": "fc0968e3-eb8e-46eb-b2e8-b3caa85d0861", "metadata": {}, "source": [ "Connect to the example database `classicmodels` \n", "*Change user:password with your own user name and password*" ] }, { "cell_type": "code", "execution_count": 1, "id": "b5504d7b-6ab7-4908-b7fd-305db36c77a9", "metadata": {}, "outputs": [ { "data": { "text/html": [] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "mysql://user:password@localhost/classicmodels" ] }, { "cell_type": "code", "execution_count": 2, "id": "0f90975b-dc68-4891-b09b-306d9576a402", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>Tables_in_classicmodels</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>customers</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>employees</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>offices</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>orderdetails</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>orders</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>payments</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>productlines</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>products</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>projects</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SHOW TABLES;" ] }, { "cell_type": "code", "execution_count": 3, "id": "b206deac-bf5c-42c2-83eb-c9b1014bbdd0", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>customerNumber</th>\n", " <th>customerName</th>\n", " <th>contactLastName</th>\n", " <th>contactFirstName</th>\n", " <th>phone</th>\n", " <th>addressLine1</th>\n", " <th>addressLine2</th>\n", " <th>city</th>\n", " <th>state</th>\n", " <th>postalCode</th>\n", " <th>country</th>\n", " <th>salesRepEmployeeNumber</th>\n", " <th>creditLimit</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370.0</td>\n", " <td>21000.0</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>112</td>\n", " <td>Signal Gift Stores</td>\n", " <td>King</td>\n", " <td>Jean</td>\n", " <td>7025551838</td>\n", " <td>8489 Strong St.</td>\n", " <td>None</td>\n", " <td>Las Vegas</td>\n", " <td>NV</td>\n", " <td>83030</td>\n", " <td>USA</td>\n", " <td>1166.0</td>\n", " <td>71800.0</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>114</td>\n", " <td>Australian Collectors, Co.</td>\n", " <td>Ferguson</td>\n", " <td>Peter</td>\n", " <td>03 9520 4555</td>\n", " <td>636 St Kilda Road</td>\n", " <td>Level 3</td>\n", " <td>Melbourne</td>\n", " <td>Victoria</td>\n", " <td>3004</td>\n", " <td>Australia</td>\n", " <td>1611.0</td>\n", " <td>117300.0</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>119</td>\n", " <td>La Rochelle Gifts</td>\n", " <td>Labrune</td>\n", " <td>Janine</td>\n", " <td>40.67.8555</td>\n", " <td>67, rue des Cinquante Otages</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370.0</td>\n", " <td>118200.0</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>121</td>\n", " <td>Baane Mini Imports</td>\n", " <td>Bergulfsen</td>\n", " <td>Jonas</td>\n", " <td>07-98 9555</td>\n", " <td>Erling Skakkes gate 78</td>\n", " <td>None</td>\n", " <td>Stavern</td>\n", " <td>None</td>\n", " <td>4110</td>\n", " <td>Norway</td>\n", " <td>1504.0</td>\n", " <td>81700.0</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>124</td>\n", " <td>Mini Gifts Distributors Ltd.</td>\n", " <td>Nelson</td>\n", " <td>Susan</td>\n", " <td>4155551450</td>\n", " <td>5677 Strong St.</td>\n", " <td>None</td>\n", " <td>San Rafael</td>\n", " <td>CA</td>\n", " <td>97562</td>\n", " <td>USA</td>\n", " <td>1165.0</td>\n", " <td>210500.0</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>125</td>\n", " <td>Havel & Zbyszek Co</td>\n", " <td>Piestrzeniewicz</td>\n", " <td>Zbyszek</td>\n", " <td>(26) 642-7555</td>\n", " <td>ul. Filtrowa 68</td>\n", " <td>None</td>\n", " <td>Warszawa</td>\n", " <td>None</td>\n", " <td>01-012</td>\n", " <td>Poland</td>\n", " <td>NaN</td>\n", " <td>0.0</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>128</td>\n", " <td>Blauer See Auto, Co.</td>\n", " <td>Keitel</td>\n", " <td>Roland</td>\n", " <td>+49 69 66 90 2555</td>\n", " <td>Lyonerstr. 34</td>\n", " <td>None</td>\n", " <td>Frankfurt</td>\n", " <td>None</td>\n", " <td>60528</td>\n", " <td>Germany</td>\n", " <td>1504.0</td>\n", " <td>59700.0</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>129</td>\n", " <td>Mini Wheels Co.</td>\n", " <td>Murphy</td>\n", " <td>Julie</td>\n", " <td>6505555787</td>\n", " <td>5557 North Pendale Street</td>\n", " <td>None</td>\n", " <td>San Francisco</td>\n", " <td>CA</td>\n", " <td>94217</td>\n", " <td>USA</td>\n", " <td>1165.0</td>\n", " <td>64600.0</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>131</td>\n", " <td>Land of Toys Inc.</td>\n", " <td>Lee</td>\n", " <td>Kwai</td>\n", " <td>2125557818</td>\n", " <td>897 Long Airport Avenue</td>\n", " <td>None</td>\n", " <td>NYC</td>\n", " <td>NY</td>\n", " <td>10022</td>\n", " <td>USA</td>\n", " <td>1323.0</td>\n", " <td>114900.0</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>141</td>\n", " <td>Euro+ Shopping Channel</td>\n", " <td>Freyre</td>\n", " <td>Diego</td>\n", " <td>(91) 555 94 44</td>\n", " <td>C/ Moralzarzal, 86</td>\n", " <td>None</td>\n", " <td>Madrid</td>\n", " <td>None</td>\n", " <td>28034</td>\n", " <td>Spain</td>\n", " <td>1370.0</td>\n", " <td>227600.0</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>144</td>\n", " <td>Volvo Model Replicas, Co</td>\n", " <td>Berglund</td>\n", " <td>Christina</td>\n", " <td>0921-12 3555</td>\n", " <td>Berguvsvägen 8</td>\n", " <td>None</td>\n", " <td>Luleå</td>\n", " <td>None</td>\n", " <td>S-958 22</td>\n", " <td>Sweden</td>\n", " <td>1504.0</td>\n", " <td>53100.0</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>145</td>\n", " <td>Danish Wholesale Imports</td>\n", " <td>Petersen</td>\n", " <td>Jytte</td>\n", " <td>31 12 3555</td>\n", " <td>Vinbæltet 34</td>\n", " <td>None</td>\n", " <td>Kobenhavn</td>\n", " <td>None</td>\n", " <td>1734</td>\n", " <td>Denmark</td>\n", " <td>1401.0</td>\n", " <td>83400.0</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>146</td>\n", " <td>Saveley & Henriot, Co.</td>\n", " <td>Saveley</td>\n", " <td>Mary</td>\n", " <td>78.32.5555</td>\n", " <td>2, rue du Commerce</td>\n", " <td>None</td>\n", " <td>Lyon</td>\n", " <td>None</td>\n", " <td>69004</td>\n", " <td>France</td>\n", " <td>1337.0</td>\n", " <td>123900.0</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>148</td>\n", " <td>Dragon Souveniers, Ltd.</td>\n", " <td>Natividad</td>\n", " <td>Eric</td>\n", " <td>+65 221 7555</td>\n", " <td>Bronz Sok.</td>\n", " <td>Bronz Apt. 3/6 Tesvikiye</td>\n", " <td>Singapore</td>\n", " <td>None</td>\n", " <td>079903</td>\n", " <td>Singapore</td>\n", " <td>1621.0</td>\n", " <td>103800.0</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT * FROM customers LIMIT 15;" ] }, { "cell_type": "code", "execution_count": 4, "id": "29f5e67a-d6e3-4520-9a80-184e11203639", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>employeeNumber</th>\n", " <th>lastName</th>\n", " <th>firstName</th>\n", " <th>extension</th>\n", " <th>email</th>\n", " <th>officeCode</th>\n", " <th>reportsTo</th>\n", " <th>jobTitle</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>1002</td>\n", " <td>Murphy</td>\n", " <td>Diane</td>\n", " <td>x5800</td>\n", " <td>dmurphy@classicmodelcars.com</td>\n", " <td>1</td>\n", " <td>NaN</td>\n", " <td>President</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>1056</td>\n", " <td>Patterson</td>\n", " <td>Mary</td>\n", " <td>x4611</td>\n", " <td>mpatterso@classicmodelcars.com</td>\n", " <td>1</td>\n", " <td>1002.0</td>\n", " <td>VP Sales</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>1076</td>\n", " <td>Firrelli</td>\n", " <td>Jeff</td>\n", " <td>x9273</td>\n", " <td>jfirrelli@classicmodelcars.com</td>\n", " <td>1</td>\n", " <td>1002.0</td>\n", " <td>VP Marketing</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>1088</td>\n", " <td>Patterson</td>\n", " <td>William</td>\n", " <td>x4871</td>\n", " <td>wpatterson@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1056.0</td>\n", " <td>Sales Manager (APAC)</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>1102</td>\n", " <td>Bondur</td>\n", " <td>Gerard</td>\n", " <td>x5408</td>\n", " <td>gbondur@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1056.0</td>\n", " <td>Sale Manager (EMEA)</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>1143</td>\n", " <td>Bow</td>\n", " <td>Anthony</td>\n", " <td>x5428</td>\n", " <td>abow@classicmodelcars.com</td>\n", " <td>1</td>\n", " <td>1056.0</td>\n", " <td>Sales Manager (NA)</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>1165</td>\n", " <td>Jennings</td>\n", " <td>Leslie</td>\n", " <td>x3291</td>\n", " <td>ljennings@classicmodelcars.com</td>\n", " <td>1</td>\n", " <td>1143.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>1166</td>\n", " <td>Thompson</td>\n", " <td>Leslie</td>\n", " <td>x4065</td>\n", " <td>lthompson@classicmodelcars.com</td>\n", " <td>1</td>\n", " <td>1143.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>1188</td>\n", " <td>Firrelli</td>\n", " <td>Julie</td>\n", " <td>x2173</td>\n", " <td>jfirrelli@classicmodelcars.com</td>\n", " <td>2</td>\n", " <td>1143.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>1216</td>\n", " <td>Patterson</td>\n", " <td>Steve</td>\n", " <td>x4334</td>\n", " <td>spatterson@classicmodelcars.com</td>\n", " <td>2</td>\n", " <td>1143.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>1286</td>\n", " <td>Tseng</td>\n", " <td>Foon Yue</td>\n", " <td>x2248</td>\n", " <td>ftseng@classicmodelcars.com</td>\n", " <td>3</td>\n", " <td>1143.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>1323</td>\n", " <td>Vanauf</td>\n", " <td>George</td>\n", " <td>x4102</td>\n", " <td>gvanauf@classicmodelcars.com</td>\n", " <td>3</td>\n", " <td>1143.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>1337</td>\n", " <td>Bondur</td>\n", " <td>Loui</td>\n", " <td>x6493</td>\n", " <td>lbondur@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>1370</td>\n", " <td>Hernandez</td>\n", " <td>Gerard</td>\n", " <td>x2028</td>\n", " <td>ghernande@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>1401</td>\n", " <td>Castillo</td>\n", " <td>Pamela</td>\n", " <td>x2759</td>\n", " <td>pcastillo@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>1501</td>\n", " <td>Bott</td>\n", " <td>Larry</td>\n", " <td>x2311</td>\n", " <td>lbott@classicmodelcars.com</td>\n", " <td>7</td>\n", " <td>1102.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>1504</td>\n", " <td>Jones</td>\n", " <td>Barry</td>\n", " <td>x102</td>\n", " <td>bjones@classicmodelcars.com</td>\n", " <td>7</td>\n", " <td>1102.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>17</th>\n", " <td>1611</td>\n", " <td>Fixter</td>\n", " <td>Andy</td>\n", " <td>x101</td>\n", " <td>afixter@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1088.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>18</th>\n", " <td>1612</td>\n", " <td>Marsh</td>\n", " <td>Peter</td>\n", " <td>x102</td>\n", " <td>pmarsh@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1088.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>19</th>\n", " <td>1619</td>\n", " <td>King</td>\n", " <td>Tom</td>\n", " <td>x103</td>\n", " <td>tking@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1088.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>20</th>\n", " <td>1621</td>\n", " <td>Nishi</td>\n", " <td>Mami</td>\n", " <td>x101</td>\n", " <td>mnishi@classicmodelcars.com</td>\n", " <td>5</td>\n", " <td>1056.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>21</th>\n", " <td>1625</td>\n", " <td>Kato</td>\n", " <td>Yoshimi</td>\n", " <td>x102</td>\n", " <td>ykato@classicmodelcars.com</td>\n", " <td>5</td>\n", " <td>1621.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>22</th>\n", " <td>1702</td>\n", " <td>Gerard</td>\n", " <td>Martin</td>\n", " <td>x2312</td>\n", " <td>mgerard@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102.0</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT * FROM employees;" ] }, { "cell_type": "code", "execution_count": 5, "id": "c1926332-eb31-4205-8748-3595677c3a14", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>customerNumber</th>\n", " <th>customerName</th>\n", " <th>contactLastName</th>\n", " <th>contactFirstName</th>\n", " <th>phone</th>\n", " <th>addressLine1</th>\n", " <th>addressLine2</th>\n", " <th>city</th>\n", " <th>state</th>\n", " <th>postalCode</th>\n", " <th>country</th>\n", " <th>salesRepEmployeeNumber</th>\n", " <th>creditLimit</th>\n", " <th>employeeNumber</th>\n", " <th>lastName</th>\n", " <th>firstName</th>\n", " <th>extension</th>\n", " <th>email</th>\n", " <th>officeCode</th>\n", " <th>reportsTo</th>\n", " <th>jobTitle</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1702</td>\n", " <td>Gerard</td>\n", " <td>Martin</td>\n", " <td>x2312</td>\n", " <td>mgerard@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1625</td>\n", " <td>Kato</td>\n", " <td>Yoshimi</td>\n", " <td>x102</td>\n", " <td>ykato@classicmodelcars.com</td>\n", " <td>5</td>\n", " <td>1621</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1621</td>\n", " <td>Nishi</td>\n", " <td>Mami</td>\n", " <td>x101</td>\n", " <td>mnishi@classicmodelcars.com</td>\n", " <td>5</td>\n", " <td>1056</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1619</td>\n", " <td>King</td>\n", " <td>Tom</td>\n", " <td>x103</td>\n", " <td>tking@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1088</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1612</td>\n", " <td>Marsh</td>\n", " <td>Peter</td>\n", " <td>x102</td>\n", " <td>pmarsh@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1088</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1611</td>\n", " <td>Fixter</td>\n", " <td>Andy</td>\n", " <td>x101</td>\n", " <td>afixter@classicmodelcars.com</td>\n", " <td>6</td>\n", " <td>1088</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1504</td>\n", " <td>Jones</td>\n", " <td>Barry</td>\n", " <td>x102</td>\n", " <td>bjones@classicmodelcars.com</td>\n", " <td>7</td>\n", " <td>1102</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1501</td>\n", " <td>Bott</td>\n", " <td>Larry</td>\n", " <td>x2311</td>\n", " <td>lbott@classicmodelcars.com</td>\n", " <td>7</td>\n", " <td>1102</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1401</td>\n", " <td>Castillo</td>\n", " <td>Pamela</td>\n", " <td>x2759</td>\n", " <td>pcastillo@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1370</td>\n", " <td>Hernandez</td>\n", " <td>Gerard</td>\n", " <td>x2028</td>\n", " <td>ghernande@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1337</td>\n", " <td>Bondur</td>\n", " <td>Loui</td>\n", " <td>x6493</td>\n", " <td>lbondur@classicmodelcars.com</td>\n", " <td>4</td>\n", " <td>1102</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1323</td>\n", " <td>Vanauf</td>\n", " <td>George</td>\n", " <td>x4102</td>\n", " <td>gvanauf@classicmodelcars.com</td>\n", " <td>3</td>\n", " <td>1143</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1286</td>\n", " <td>Tseng</td>\n", " <td>Foon Yue</td>\n", " <td>x2248</td>\n", " <td>ftseng@classicmodelcars.com</td>\n", " <td>3</td>\n", " <td>1143</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1216</td>\n", " <td>Patterson</td>\n", " <td>Steve</td>\n", " <td>x4334</td>\n", " <td>spatterson@classicmodelcars.com</td>\n", " <td>2</td>\n", " <td>1143</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>103</td>\n", " <td>Atelier graphique</td>\n", " <td>Schmitt</td>\n", " <td>Carine</td>\n", " <td>40.32.2555</td>\n", " <td>54, rue Royale</td>\n", " <td>None</td>\n", " <td>Nantes</td>\n", " <td>None</td>\n", " <td>44000</td>\n", " <td>France</td>\n", " <td>1370</td>\n", " <td>21000.0</td>\n", " <td>1188</td>\n", " <td>Firrelli</td>\n", " <td>Julie</td>\n", " <td>x2173</td>\n", " <td>jfirrelli@classicmodelcars.com</td>\n", " <td>2</td>\n", " <td>1143</td>\n", " <td>Sales Rep</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT * FROM customers, employees LIMIT 15;" ] }, { "cell_type": "code", "execution_count": 6, "id": "d86130c1-088f-4353-99cb-0374343d7bcd", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>customerNumber</th>\n", " <th>employeeNumber</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>125</td>\n", " <td>1625</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>125</td>\n", " <td>1323</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>125</td>\n", " <td>1286</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>125</td>\n", " <td>1216</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>125</td>\n", " <td>1188</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>125</td>\n", " <td>1166</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>125</td>\n", " <td>1165</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>125</td>\n", " <td>1702</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>125</td>\n", " <td>1504</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>125</td>\n", " <td>1501</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>125</td>\n", " <td>1401</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>125</td>\n", " <td>1370</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>125</td>\n", " <td>1337</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>125</td>\n", " <td>1619</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>125</td>\n", " <td>1612</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT customerNumber, employeeNumber FROM customers, employees LIMIT 15;" ] }, { "cell_type": "code", "execution_count": 7, "id": "319c3d53-a832-4e1c-951f-80c5ff00d642", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>lastName</th>\n", " <th>firstName</th>\n", " <th>officeCode</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>Patterson</td>\n", " <td>William</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>Bondur</td>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>Firrelli</td>\n", " <td>Julie</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>Patterson</td>\n", " <td>Steve</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>Tseng</td>\n", " <td>Foon Yue</td>\n", " <td>3</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>Vanauf</td>\n", " <td>George</td>\n", " <td>3</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>Bondur</td>\n", " <td>Loui</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>Hernandez</td>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>Castillo</td>\n", " <td>Pamela</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>Bott</td>\n", " <td>Larry</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>Jones</td>\n", " <td>Barry</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>Fixter</td>\n", " <td>Andy</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>Marsh</td>\n", " <td>Peter</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>King</td>\n", " <td>Tom</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>Nishi</td>\n", " <td>Mami</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>Kato</td>\n", " <td>Yoshimi</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>Gerard</td>\n", " <td>Martin</td>\n", " <td>4</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT lastName, firstName, officeCode FROM employees WHERE officeCode >= 2;" ] }, { "cell_type": "code", "execution_count": 8, "id": "dfc55e47-fc40-4f06-9ba7-2dbe85972460", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>officeCode</th>\n", " <th>employeesCount</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>1</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>2</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>3</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>4</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>5</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>6</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>7</td>\n", " <td>2</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT officeCode, COUNT(*) as 'employeesCount' FROM employees GROUP BY officeCode;" ] }, { "cell_type": "code", "execution_count": 9, "id": "0f9e4c75-1893-4271-b624-05da1b972b2d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>officeCode</th>\n", " <th>employeesCount</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>1</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>4</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>6</td>\n", " <td>4</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT officeCode, COUNT(*) as 'employeesCount' FROM employees GROUP BY officeCode HAVING employeesCount >= 4;" ] }, { "cell_type": "code", "execution_count": 10, "id": "c5addf3f-e9f0-466a-9c05-f010c15251bb", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>lastName</th>\n", " <th>firstName</th>\n", " <th>officeCode</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>Bondur</td>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>Bondur</td>\n", " <td>Loui</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>Bott</td>\n", " <td>Larry</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>Bow</td>\n", " <td>Anthony</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>Castillo</td>\n", " <td>Pamela</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>Firrelli</td>\n", " <td>Jeff</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>Firrelli</td>\n", " <td>Julie</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>Fixter</td>\n", " <td>Andy</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>Gerard</td>\n", " <td>Martin</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>Hernandez</td>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>Jennings</td>\n", " <td>Leslie</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>Jones</td>\n", " <td>Barry</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>Kato</td>\n", " <td>Yoshimi</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>King</td>\n", " <td>Tom</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>Marsh</td>\n", " <td>Peter</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>Murphy</td>\n", " <td>Diane</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>Nishi</td>\n", " <td>Mami</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>17</th>\n", " <td>Patterson</td>\n", " <td>Mary</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>18</th>\n", " <td>Patterson</td>\n", " <td>Steve</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>19</th>\n", " <td>Patterson</td>\n", " <td>William</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>20</th>\n", " <td>Thompson</td>\n", " <td>Leslie</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>21</th>\n", " <td>Tseng</td>\n", " <td>Foon Yue</td>\n", " <td>3</td>\n", " </tr>\n", " <tr>\n", " <th>22</th>\n", " <td>Vanauf</td>\n", " <td>George</td>\n", " <td>3</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT lastName, firstName, officeCode FROM employees ORDER BY lastName;" ] }, { "cell_type": "code", "execution_count": 11, "id": "7c431e35-843b-4c81-bbfa-fbc19c0e415c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>lastName</th>\n", " <th>firstName</th>\n", " <th>officeCode</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>Bondur</td>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>Bondur</td>\n", " <td>Loui</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>Bott</td>\n", " <td>Larry</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>Bow</td>\n", " <td>Anthony</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>Castillo</td>\n", " <td>Pamela</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>Firrelli</td>\n", " <td>Julie</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>Firrelli</td>\n", " <td>Jeff</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>Fixter</td>\n", " <td>Andy</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>Gerard</td>\n", " <td>Martin</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>Hernandez</td>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>Jennings</td>\n", " <td>Leslie</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>Jones</td>\n", " <td>Barry</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>Kato</td>\n", " <td>Yoshimi</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>King</td>\n", " <td>Tom</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>Marsh</td>\n", " <td>Peter</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>Murphy</td>\n", " <td>Diane</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>Nishi</td>\n", " <td>Mami</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>17</th>\n", " <td>Patterson</td>\n", " <td>William</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>18</th>\n", " <td>Patterson</td>\n", " <td>Steve</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>19</th>\n", " <td>Patterson</td>\n", " <td>Mary</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>20</th>\n", " <td>Thompson</td>\n", " <td>Leslie</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>21</th>\n", " <td>Tseng</td>\n", " <td>Foon Yue</td>\n", " <td>3</td>\n", " </tr>\n", " <tr>\n", " <th>22</th>\n", " <td>Vanauf</td>\n", " <td>George</td>\n", " <td>3</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT lastName, firstName, officeCode FROM employees ORDER BY lastName, officeCode DESC;" ] }, { "cell_type": "code", "execution_count": 12, "id": "bbfb1d7b-1cd3-42ec-8b4d-2760cb945485", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>lastName</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>Bondur</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>Bott</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>Bow</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>Castillo</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>Firrelli</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>Fixter</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>Gerard</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>Hernandez</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>Jennings</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>Jones</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>Kato</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>King</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>Marsh</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>Murphy</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>Nishi</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>Patterson</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>Thompson</td>\n", " </tr>\n", " <tr>\n", " <th>17</th>\n", " <td>Tseng</td>\n", " </tr>\n", " <tr>\n", " <th>18</th>\n", " <td>Vanauf</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT DISTINCT(lastName) FROM employees ORDER BY lastName;" ] }, { "cell_type": "code", "execution_count": 13, "id": "f3140326-9f1a-4ad7-94d1-de4eb6903d2d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>lastName</th>\n", " <th>officeCode</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>Bondur</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>Bott</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>Bow</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>Castillo</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>Firrelli</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>Firrelli</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>Fixter</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>Gerard</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>Hernandez</td>\n", " <td>4</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>Jennings</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>Jones</td>\n", " <td>7</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>Kato</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>King</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>Marsh</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>Murphy</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>Nishi</td>\n", " <td>5</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>Patterson</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>17</th>\n", " <td>Patterson</td>\n", " <td>2</td>\n", " </tr>\n", " <tr>\n", " <th>18</th>\n", " <td>Patterson</td>\n", " <td>6</td>\n", " </tr>\n", " <tr>\n", " <th>19</th>\n", " <td>Thompson</td>\n", " <td>1</td>\n", " </tr>\n", " <tr>\n", " <th>20</th>\n", " <td>Tseng</td>\n", " <td>3</td>\n", " </tr>\n", " <tr>\n", " <th>21</th>\n", " <td>Vanauf</td>\n", " <td>3</td>\n", " </tr>\n", " </tbody>\n", "</table>" ] }, "metadata": {}, "output_type": "display_data", "source": "kernel" } ], "source": [ "SELECT DISTINCT(lastName), officeCode FROM employees ORDER BY lastName;" ] }, { "cell_type": "code", "execution_count": null, "id": "17e21a07-86d4-4c43-ac70-3fb5ed1f4e78", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Mysql", "language": "sql", "name": "mysql" }, "language_info": { "file_extension": ".sql", "mimetype": "text/x-sh", "name": "sql" } }, "nbformat": 4, "nbformat_minor": 5 }
Previous Notebook:
MySQL Import Database from SQL File
Next Notebook:
MySQL Users and Privileges
Loading