SQL: Advanced Queries¶
In [1]:
import pandas as pd
import os
import os.path
import json
import sys
import importlib
module_dir = "../../modules"
module_path = os.path.abspath(module_dir)
if not module_path in sys.path:
sys.path.append(module_path)
import dbutil
importlib.reload(dbutil)
%load_ext sql
Instructions¶
Set User Credentials¶
Edit
creds.jsonto reflect your mysql user and passwordThis must be done prior to executing the following cell
In general, you will be able to choose whether you are using the remote MySQL database or the SQLite database(s) by setting the dbsource variable to "mysql" or "sqlite" respectively. The function dbutil.db_cstring function computes a connection string for the chosen dbsource using the information in the creds.json file. If the last argument to this function is present, the generated connection string uses that datbase as superceding the name of the database in creds.json.
In [3]:
dbsource = "sqlite"
db = "book"
cstring = dbutil.db_cstring(dbsource, "creds.json", ".", db)
%sql $cstring
12.3.5 Join Table from Subquery¶
- Develop the subquery. Do you see the flaw in the following?
In [6]:
%%sql
SELECT year, code, MAX(pop)
FROM indicators
GROUP BY code
LIMIT 15
Out[6]:
In [10]:
%%sql
SELECT code, MAX(pop) AS max_pop
FROM indicators
GROUP BY code
LIMIT 7
Out[10]:
In [13]:
%%sql
SELECT I.code, I.year, maxTable.max_pop FROM
(SELECT code, MAX(pop) AS max_pop
FROM indicators
GROUP BY code) AS maxTable
INNER JOIN indicators AS I
ON (I.pop = maxTable.max_pop) AND (I.code = maxTable.code)
ORDER BY I.code
LIMIT 14
Out[13]:
In [19]:
%%sql
SELECT code, MIN(year) AS year, ROUND(AVG(max_pop),2) AS max_pop FROM
(SELECT I.code, I.year, maxTable.max_pop FROM
(SELECT code, MAX(pop) AS max_pop
FROM indicators
GROUP BY code) AS maxTable
INNER JOIN indicators AS I
ON (I.pop = maxTable.max_pop) AND (I.code = maxTable.code)) AS inner
GROUP BY code
ORDER BY code
LIMIT 10
Out[19]:
In [21]:
%%sql
SELECT region, ROUND(AVG(life),2) AS avg_life,
COUNT(*) AS num_country, COUNT(life) AS num_life
FROM indicators LEFT JOIN countries USING (code)
WHERE year = 2017
GROUP BY region
ORDER BY avg_life DESC
Out[21]:
In [1]:
from IPython.display import Image
Image("classimages/school_schema.jpg", width=600, height=600)
Out[1]:
In [24]:
dbsource = "sqlite"
db = "school"
cstring = dbutil.db_cstring(dbsource, "creds.json", ".", db)
%sql $cstring
In [27]:
%%sql
SELECT *
FROM (students AS ST INNER JOIN subjects AS SU
ON ST.studentmajor = SU.subjectid)
INNER JOIN departments AS D
ON D.departmentid = SU.departmentid
LIMIT 10
Out[27]:
In [29]:
%%sql
SELECT D.division, COUNT(*) AS majors
FROM (students AS ST INNER JOIN subjects AS SU
ON ST.studentmajor = SU.subjectid)
INNER JOIN departments AS D
ON D.departmentid = SU.departmentid
GROUP BY division
ORDER BY majors DESC
Out[29]:
In [30]:
dbsource = "sqlite"
db = "book"
cstring = dbutil.db_cstring(dbsource, "creds.json", ".", db)
%sql $cstring
In [31]:
%%sql
SELECT AVG(gdp) * 5 AS avg_gdp
FROM indicators
WHERE year = 2017
Out[31]:
In [34]:
%%sql
SELECT code, gdp, life
FROM indicators
WHERE year=2017 AND
gdp > (SELECT AVG(gdp) * 5 AS avg_gdp
FROM indicators
WHERE year = 2017)
Out[34]:
In [35]:
%%sql
SELECT DISTINCT code FROM indicators WHERE life > 83
Out[35]:
In [36]:
%%sql
SELECT AVG(gdp) AS longlife_gdp
FROM indicators
WHERE year = 2017 AND
code IN (SELECT DISTINCT code
FROM indicators
WHERE life > 83)
Out[36]:
In [38]:
dbsource = "sqlite"
db = "school"
cstring = dbutil.db_cstring(dbsource, "creds.json", ".", db)
%sql $cstring
In [40]:
%%sql
SELECT studentmajor, COUNT(*) AS nummajor
FROM students
WHERE studentmajor IS NOT NULL
GROUP BY studentmajor
LIMIT 10
Out[40]:
In [42]:
%%sql
SELECT studentmajor, nummajor, departmentid
FROM ( SELECT studentmajor, COUNT(*) AS nummajor
FROM students
WHERE studentmajor IS NOT NULL
GROUP BY studentmajor ) AS majors
INNER JOIN subjects AS S
ON S.subjectid = majors.studentmajor
LIMIT 10
Out[42]:
In [ ]: