Our first step is to load the sql extension.

In [1]:
%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 [2]:
%%sql
sqlite:///data/lec18_basic_examples.db

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

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

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

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[4]:
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 [5]:
%%sql
SELECT * FROM Dragon;

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

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

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[7]:
cuteness birth
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 name, year
FROM Dragon
WHERE cute > 0;

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

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

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

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

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

GROUP BY operations¶

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

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[14]:
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 [15]:
%%sql
SELECT type
FROM Dish;

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

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

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

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

Trickier GROUP BY¶

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

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[19]:
type COUNT(cost)
appetizer 3
dessert 1
entree 3
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(*)
FROM Dish
GROUP BY type;

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

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

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

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

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

 * sqlite:///data/lec18_basic_examples.db
Done.
Out[26]:
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 [35]:
%%sql
SELECT DISTINCT type 
FROM Dish
WHERE cost < 9;

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

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

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

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

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