Introduction to SQL, inside a Python-based Jupyter Notebook¶

This Notebook focuses on SQL, while using the Python-based IPython kernel. For this, we take advantage of the extensible IPython magic command system, as described below.

Our first step is to load the ipython-sql extension, which exposes the %%sql cell magic.

One-time setup - temporary fix¶

Note: I accidentally missed updating the hub to include the SQL IPython support. We've requested a fix on the hub, but in the meantime, the cell below will do an install if necessary.

Please use similar code in your other uses of SQL on the hub til we get this fixed (should happen soon). Once this is fixed, the cell below can be replaced with simply

%load_ext sql
In [1]:
try:
    %load_ext sql
except ModuleNotFoundError:
    %pip install ipython-sql
    import sys
    print('#'*80 + 
          '\n\nPlease restart your kernel before continuing.\n\n' + 
          '#'*80 ,
          file=sys.stderr)
In [2]:
# %load_ext sql

Then we will connect to the database. In this lecture example, the database is stored in a single file on our own computer called lec18_basic_examples.db.

Note that starting a cell with %%sql tells Jupyter that you are running SQL code, not Python code.

In actual practice, you'd usually connect to some database server via a network connection, e.g. hosted on some computer on the internet.

In [3]:
%%sql
sqlite:///data/lec18_basic_examples.db

Now that we're connected, we can, for example, display the contents of the Dragon table.

In [4]:
%%sql
SELECT * FROM Dragon;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[4]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
In [5]:
%%sql
SELECT * FROM sqlite_master WHERE type='table'

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[5]:
type name tbl_name rootpage sql
table sqlite_sequence sqlite_sequence 7 CREATE TABLE sqlite_sequence(name,seq)
table Dragon Dragon 2 CREATE TABLE Dragon (
name TEXT PRIMARY KEY,
year INTEGER CHECK (year >= 2000),
cute INTEGER
)
table Dish Dish 4 CREATE TABLE Dish (
name TEXT PRIMARY KEY,
type TEXT,
cost INTEGER CHECK (cost >= 0)
)
table Scene Scene 6 CREATE TABLE Scene (
id INTEGER PRIMARY KEY AUTOINCREMENT,
biome TEXT NOT NULL,
city TEXT NOT NULL,
visitors INTEGER CHECK (visitors >= 0),
created_at DATETIME DEFAULT (DATETIME('now'))
)
In [6]:
%%sql
SELECT * FROM Dragon;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[6]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
In [7]:
%%sql
SELECT cute, year FROM Dragon;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[7]:
cute year
10 2010
-100 2011
0 2019
In [8]:
%%sql
SELECT cute AS cuteness, year AS birth FROM Dragon;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[8]:
cuteness birth
10 2010
-100 2011
0 2019
In [9]:
%%sql
SELECT cute AS cuteness,
       year AS birth 
FROM Dragon;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[9]:
cuteness birth
10 2010
-100 2011
0 2019
In [10]:
%%sql
SELECT name, year
FROM Dragon
WHERE cute > 0;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[10]:
name year
hiccup 2010
In [11]:
%%sql
SELECT name, cute, year
FROM Dragon
WHERE cute > 0 OR year > 2015;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[11]:
name cute year
hiccup 10 2010
dragon 2 0 2019
In [12]:
%%sql
SELECT *
FROM Dragon
ORDER BY cute DESC;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[12]:
name year cute
hiccup 2010 10
dragon 2 2019 0
drogon 2011 -100
In [13]:
%%sql
SELECT *
FROM Dragon
LIMIT 2;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[13]:
name year cute
hiccup 2010 10
drogon 2011 -100
In [14]:
%%sql
SELECT *
FROM Dragon
LIMIT 2
OFFSET 1;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[14]:
name year cute
drogon 2011 -100
dragon 2 2019 0

GROUP BY operations¶

In [15]:
%%sql
SELECT *
FROM Dish;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[15]:
name type cost
ravioli entree 10
pork bun entree 7
taco entree 7
edamame appetizer 4
fries appetizer 4
potsticker appetizer 4
ice cream dessert 5
In [16]:
%%sql
SELECT type
FROM Dish;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[16]:
type
entree
entree
entree
appetizer
appetizer
appetizer
dessert
In [17]:
%%sql
SELECT type
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[17]:
type
appetizer
dessert
entree
In [18]:
%%sql
SELECT type, MAX(cost)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[18]:
type MAX(cost)
appetizer 4
dessert 5
entree 10
In [19]:
%%sql
SELECT type, 
       SUM(cost), 
       MIN(cost),
       MAX(name)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[19]:
type SUM(cost) MIN(cost) MAX(name)
appetizer 12 4 potsticker
dessert 5 5 ice cream
entree 24 7 taco

Trickier GROUP BY¶

In [20]:
%%sql
SELECT type, COUNT(cost)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[20]:
type COUNT(cost)
appetizer 3
dessert 1
entree 3
In [21]:
%%sql
SELECT type, COUNT(cost)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[21]:
type COUNT(cost)
appetizer 3
dessert 1
entree 3
In [22]:
%%sql
SELECT type, COUNT(*)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[22]:
type COUNT(*)
appetizer 3
dessert 1
entree 3
In [23]:
%%sql
SELECT type, cost
FROM Dish
GROUP BY type, cost;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[23]:
type cost
appetizer 4
dessert 5
entree 7
entree 10
In [24]:
%%sql
SELECT type, cost, COUNT(*)
FROM Dish
GROUP BY type, cost;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[24]:
type cost COUNT(*)
appetizer 4 3
dessert 5 1
entree 7 2
entree 10 1
In [25]:
%%sql
SELECT type, COUNT(*)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[25]:
type COUNT(*)
appetizer 3
dessert 1
entree 3
In [26]:
%%sql
SELECT type, COUNT(*)
FROM Dish
GROUP BY type
HAVING MAX(cost) < 8

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[26]:
type COUNT(*)
appetizer 3
dessert 1
In [27]:
%%sql
SELECT type, COUNT(*)
FROM Dish
WHERE cost < 8
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[27]:
type COUNT(*)
appetizer 3
dessert 1
entree 2

DISTINCT¶

In [28]:
%%sql
SELECT DISTINCT cost
FROM Dish;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[28]:
cost
10
7
4
5
In [29]:
%%sql
SELECT DISTINCT type 
FROM Dish
WHERE cost < 9;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[29]:
type
entree
appetizer
dessert
In [30]:
%%sql
SELECT DISTINCT type, cost 
FROM Dish
WHERE cost < 9;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[30]:
type cost
entree 7
appetizer 4
dessert 5
In [31]:
%%sql
SELECT DISTINCT type, cost 
FROM Dish;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[31]:
type cost
entree 10
entree 7
appetizer 4
dessert 5
In [32]:
%%sql
SELECT type, AVG(cost)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[32]:
type AVG(cost)
appetizer 4.0
dessert 5.0
entree 8.0
In [33]:
%%sql
SELECT type, AVG(DISTINCT cost)
FROM Dish
GROUP BY type;

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[33]:
type AVG(DISTINCT cost)
appetizer 4.0
dessert 5.0
entree 8.5