Lecture 20 – Data 100, Spring 2025¶

Data 100, Spring 2025

Acknowledgments Page

Starting Up SQL¶

Before we look at SQL syntax in detail, let's first get ourselves set up to run SQL queries in Jupyter.

Approach #1: SQL Magic¶

1. Load the sql Module.

Load %%sql cell magic.

In [1]:
%load_ext sql

2. Connect to a database.

Here, we connect to the SQLite database basic_examples.db and duckdb database example_duck.db.

In [2]:
%sql sqlite:///data/basic_examples.db --alias sqlite
Connecting to 'sqlite'
In [3]:
%sql duckdb:///data/example_duck.db --alias duckdb
Connecting and switching to connection 'duckdb'

If you were connecting to an "enterprise data platform"

from sqlalchemy import create_engine

snow_engine = create_engine(
    f"snowflake://{user}:{password}@{account_identifier}")
%sql snow_engine --alias snow

db_engine = create_engine(
  url = f"databricks://token:{access_token}@{server_hostname}?" +
        f"http_path={http_path}&catalog={catalog}&schema={schema}"
)
%sql db_engine --alias db


3. Run a simple SQL query.

%sql parses only the immmediate line as a SQL command.

In [4]:
%sql SELECT * FROM Dragon;
Running query in 'duckdb'
Out[4]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None

The %%sql command, on the other hand, lets Jupyter parse the rest of the lines (the entire code block) as a SQL command.

In [5]:
%%sql
SELECT * FROM Dragon;
Running query in 'duckdb'
Out[5]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None

Simple query, this time on two different lines.

In [6]:
%%sql
SELECT *
FROM Dragon;
Running query in 'duckdb'
Out[6]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None

Storing one-line %sql queries¶

For simple one-line queries, you can use IPython's ability to store the result of a magic command like %sql as if it were any other Python statement, and save the output to a variable:

In [7]:
dragon_table = %sql SELECT * FROM Dragon
dragon_table
Running query in 'duckdb'
Out[7]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None

As noted above, the result of the query is a Python variable of type ResultSet, more specifically:

In [8]:
type(dragon_table)
Out[8]:
sql.run.resultset.ResultSet

You need to manually convert it to a Pandas DataFrame if you want to do pandas-things with its content:

In [9]:
dragon_df = dragon_table.DataFrame()
dragon_df
Out[9]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN

You can configure jupysql to automatically convert all outputs to Pandas DataFrames. This can be handy if you intend all your Python-side work to be done with Pandas, as it saves you from manually having to call .DataFrame() first on all outputs. On the other hand, you don't get access to the original SQL ResultSet object, which have a number of interesting properties and capabilities. You can learn more about those in the jupysql documentation.

For now, let's turn this on so you can see how this simplified, "pandas all the way" workflow looks like:

In [10]:
%config SqlMagic.autopandas = True
In [11]:
dragon_df = %sql SELECT * FROM Dragon
dragon_df
Running query in 'duckdb'
Out[11]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN
In [12]:
type(dragon_df)
Out[12]:
pandas.core.frame.DataFrame

Storing output of multiple SQL lines¶

You can use the variable << syntax in jupysql to store its output (this will honor your autopandas state and store either a sql.run.ResultState or a Pandas DataFrame):

In [13]:
%%sql res <<
SELECT *
FROM Dragon;
Running query in 'duckdb'
In [14]:
res
Out[14]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN

Approach #2: pd.read_sql¶

It turns out that pandas has a special-purpose function to parse SQL queries. We can pass in a SQL query as a string to return a pandas DataFrame. To achieve the same result as we did using cell magic above, we can do the following.

1. Connect to a database

In [15]:
import sqlalchemy 
import pandas as pd

engine = sqlalchemy.create_engine("duckdb:///data/example_duck.db")

2. Run a simple SQL query

In [16]:
query = """
SELECT * 
FROM Dragon;
"""

df = pd.read_sql(query, engine)
df
Out[16]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN

Approach #3 -- DuckDB Special¶

Now that we are using DuckDB we can do something extra crazy:

In [17]:
import seaborn as sns
import duckdb
mpg = sns.load_dataset("mpg")
In [18]:
duckdb.query("SELECT * FROM mpg").df()
Out[18]:
mpg cylinders displacement horsepower weight acceleration model_year origin name
0 18.0 8 307.0 130.0 3504 12.0 70 usa chevrolet chevelle malibu
1 15.0 8 350.0 165.0 3693 11.5 70 usa buick skylark 320
2 18.0 8 318.0 150.0 3436 11.0 70 usa plymouth satellite
3 16.0 8 304.0 150.0 3433 12.0 70 usa amc rebel sst
4 17.0 8 302.0 140.0 3449 10.5 70 usa ford torino
... ... ... ... ... ... ... ... ... ...
393 27.0 4 140.0 86.0 2790 15.6 82 usa ford mustang gl
394 44.0 4 97.0 52.0 2130 24.6 82 europe vw pickup
395 32.0 4 135.0 84.0 2295 11.6 82 usa dodge rampage
396 28.0 4 120.0 79.0 2625 18.6 82 usa ford ranger
397 31.0 4 119.0 82.0 2720 19.4 82 usa chevy s-10

398 rows × 9 columns

DuckDB can also see my dataframes in the python environment allowing me to do dataframe manipulation in SQL!

Tables and Schema¶

A database contains a collection of SQL tables. Let's connect to our "toy" database example_duck.db and explore the tables it stores.

In [19]:
%%sql
SELECT * FROM information_schema.tables
Running query in 'duckdb'
Out[19]:
table_catalog table_schema table_name table_type self_referencing_column_name reference_generation user_defined_type_catalog user_defined_type_schema user_defined_type_name is_insertable_into is_typed commit_action TABLE_COMMENT
0 example_duck main dish BASE TABLE None None None None None YES NO None None
1 example_duck main dragon BASE TABLE None None None None None YES NO None None
2 example_duck main scene BASE TABLE None None None None None YES NO None None
In [20]:
%%sql
SELECT * FROM information_schema.columns
Running query in 'duckdb'
Out[20]:
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length ... identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable COLUMN_COMMENT
0 example_duck main dish name 1 None NO VARCHAR NaN NaN ... None None None None None NaN None None NaN None
1 example_duck main dish type 2 None YES VARCHAR NaN NaN ... None None None None None NaN None None NaN None
2 example_duck main dish cost 3 None YES INTEGER NaN NaN ... None None None None None NaN None None NaN None
3 example_duck main dragon name 1 None NO VARCHAR NaN NaN ... None None None None None NaN None None NaN None
4 example_duck main dragon year 2 None YES INTEGER NaN NaN ... None None None None None NaN None None NaN None
5 example_duck main dragon cute 3 None YES INTEGER NaN NaN ... None None None None None NaN None None NaN None
6 example_duck main scene id 1 None NO INTEGER NaN NaN ... None None None None None NaN None None NaN None
7 example_duck main scene biome 2 None NO VARCHAR NaN NaN ... None None None None None NaN None None NaN None
8 example_duck main scene city 3 None NO VARCHAR NaN NaN ... None None None None None NaN None None NaN None
9 example_duck main scene visitors 4 None YES INTEGER NaN NaN ... None None None None None NaN None None NaN None
10 example_duck main scene created_at 5 current_date() YES TIMESTAMP NaN NaN ... None None None None None NaN None None NaN None

11 rows × 45 columns

Getting Schema information with SQLAlchemy¶

How you list the tables varies across database platforms. For example, the statement:

SELECT * FROM information_schema.columns

only works on Postgres compatible databases.

For example, if we wanted to get the schema for tables in sqlite we would need the following:

In [21]:
pd.read_sql("SELECT * FROM sqlite_schema", "sqlite:///data/basic_examples.db")
Out[21]:
type name tbl_name rootpage sql
0 table sqlite_sequence sqlite_sequence 7 CREATE TABLE sqlite_sequence(name,seq)
1 table Dragon Dragon 2 CREATE TABLE Dragon (\n name TEXT PRIMARY K...
2 index sqlite_autoindex_Dragon_1 Dragon 3 None
3 table Dish Dish 4 CREATE TABLE Dish (\n name TEXT PRIMARY KEY...
4 index sqlite_autoindex_Dish_1 Dish 5 None
5 table Scene Scene 6 CREATE TABLE Scene (\n id INTEGER PRIMARY K...

Fortunately, SQLAlchemy has some generic tools that will be helpful regardless of what database platform you use.

In [22]:
from sqlalchemy import inspect
inspector = inspect(engine)
inspector.get_table_names()
Out[22]:
['dish', 'dragon', 'scene']
In [23]:
inspector.get_columns('scene')
Out[23]:
[{'name': 'id',
  'type': Integer(),
  'nullable': False,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'biome',
  'type': String(),
  'nullable': False,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'city',
  'type': String(),
  'nullable': False,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'visitors',
  'type': Integer(),
  'nullable': True,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'created_at',
  'type': TIMESTAMP(),
  'nullable': True,
  'default': 'current_date()',
  'autoincrement': False,
  'comment': None}]

Same with SQLite

In [24]:
sqlite_engine = sqlalchemy.create_engine("sqlite:///data/basic_examples.db")
inspect(sqlite_engine).get_columns("scene")
Out[24]:
[{'name': 'id',
  'type': INTEGER(),
  'nullable': True,
  'default': None,
  'primary_key': 1},
 {'name': 'biome',
  'type': TEXT(),
  'nullable': False,
  'default': None,
  'primary_key': 0},
 {'name': 'city',
  'type': TEXT(),
  'nullable': False,
  'default': None,
  'primary_key': 0},
 {'name': 'visitors',
  'type': INTEGER(),
  'nullable': True,
  'default': None,
  'primary_key': 0},
 {'name': 'created_at',
  'type': DATETIME(),
  'nullable': True,
  'default': "DATETIME('now')",
  'primary_key': 0}]

More advanced example of creating tables with primary and foreign key constraints:

In [25]:
%sql duckdb:///data/duckdb_example.db --alias student_db
Connecting and switching to connection 'student_db'
In [26]:
%%sql student_db

DROP TABLE IF EXISTS grade;
DROP TABLE IF EXISTS assignment;
DROP TABLE IF EXISTS student;


CREATE TABLE student (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR,
    email VARCHAR
);

CREATE TABLE assignment (
    assignment_id INTEGER PRIMARY KEY,
    description VARCHAR
);

CREATE TABLE grade (
    student_id INTEGER,
    assignment_id INTEGER,
    score REAL CHECK (score > 0 AND score <= 100),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (assignment_id) REFERENCES assignment(assignment_id)
);

INSERT INTO student VALUES
(123, 'JoeyG', 'jegonzal@berkeley.edu'),
(456, 'NargesN', 'norouzi@berkeley.edu');

INSERT INTO assignment VALUES
(1, 'easy assignment'),
(2, 'hard assignment');
Out[26]:
Success
In [27]:
%%sql 
INSERT INTO grade VALUES
(123, 1, 80),
(123, 2, 42),
(456, 2, 100);
Running query in 'student_db'
Out[27]:
Success
In [28]:
%sql SELECT * FROM grade;
Running query in 'student_db'
Out[28]:
student_id assignment_id score
0 123 1 80.0
1 123 2 42.0
2 456 2 100.0


Basic Queries¶

SELECT and FROM¶

Every SQL query must contain a SELECT and FROM clause.

  • SELECT: specify the column(s) to return in the output.
  • FROM: specify the database table from which to extract data.
In [29]:
%%sql duckdb
SELECT * FROM Dragon;
Switching to connection 'duckdb'
Out[29]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN
In [30]:
%%sql
SELECT cute, year FROM Dragon;
Running query in 'duckdb'
Out[30]:
cute year
0 10.0 2010
1 -100.0 2011
2 0.0 2019
3 100.0 2010
4 NaN 2011

Aliasing with AS¶

In [31]:
%%sql
SELECT cute AS cuteness,
       year AS "birth year"
FROM Dragon;
Running query in 'duckdb'
Out[31]:
cuteness birth year
0 10.0 2010
1 -100.0 2011
2 0.0 2019
3 100.0 2010
4 NaN 2011

Uniqueness with DISTINCT¶

In [32]:
%%sql
SELECT DISTINCT year
FROM Dragon;
Running query in 'duckdb'
Out[32]:
year
0 2010
1 2019
2 2011

Filtering with WHERE¶

In [33]:
%%sql
SELECT name, year
FROM Dragon
WHERE cute > 0;
Running query in 'duckdb'
Out[33]:
name year
0 hiccup 2010
1 puff 2010
In [34]:
%%sql
SELECT name, cute, year
FROM Dragon
WHERE cute > 0 OR year > 2013;
Running query in 'duckdb'
Out[34]:
name cute year
0 hiccup 10 2010
1 puff 100 2010
2 dragon 2 0 2019
In [35]:
%%sql
SELECT name, year
FROM Dragon 
WHERE name IN ('puff', 'hiccup');
Running query in 'duckdb'
Out[35]:
name year
0 puff 2010
1 hiccup 2010
In [36]:
%%sql
SELECT name, cute
FROM Dragon
WHERE cute IS NOT NULL;
Running query in 'duckdb'
Out[36]:
name cute
0 hiccup 10
1 drogon -100
2 dragon 2 0
3 puff 100

Ordering data using ORDER BY¶

In [37]:
%%sql
SELECT *
FROM Dragon
ORDER BY cute DESC;
Running query in 'duckdb'
Out[37]:
name year cute
0 puff 2010 100.0
1 hiccup 2010 10.0
2 dragon 2 2019 0.0
3 drogon 2011 -100.0
4 smaug 2011 NaN

Restricting output with LIMIT and OFFSET¶

In [38]:
%%sql
SELECT *
FROM Dragon
LIMIT 2;
Running query in 'duckdb'
Out[38]:
name year cute
0 hiccup 2010 10
1 drogon 2011 -100
In [39]:
%%sql
SELECT *
FROM Dragon
LIMIT 2
OFFSET 1;
Running query in 'duckdb'
Out[39]:
name year cute
0 drogon 2011 -100
1 dragon 2 2019 0

Sampling with RANDOM()¶

What if we wanted a random sample:

In [40]:
%%sql
SELECT *
FROM Dragon
ORDER BY RANDOM() 
LIMIT 2
Running query in 'duckdb'
Out[40]:
name year cute
0 dragon 2 2019 0
1 hiccup 2010 10
In [41]:
%%sql
SELECT * 
FROM Dragon USING SAMPLE reservoir(2 ROWS) REPEATABLE (100);
Running query in 'duckdb'
Out[41]:
name year cute
0 puff 2010 100
1 drogon 2011 -100

Grouping Data with GROUP BY¶

In [42]:
%%sql
SELECT *
FROM Dish;
Running query in 'duckdb'
Out[42]:
name type cost
0 ravioli entree 10
1 ramen entree 13
2 taco entree 7
3 edamame appetizer 4
4 fries appetizer 4
5 potsticker appetizer 4
6 ice cream dessert 5
In [43]:
%%sql
SELECT type
FROM Dish;
Running query in 'duckdb'
Out[43]:
type
0 entree
1 entree
2 entree
3 appetizer
4 appetizer
5 appetizer
6 dessert
In [44]:
%%sql
SELECT type
FROM Dish
GROUP BY type;
Running query in 'duckdb'
Out[44]:
type
0 entree
1 dessert
2 appetizer
In [45]:
%%sql
SELECT type, SUM(cost)
FROM Dish
GROUP BY type;
Running query in 'duckdb'
Out[45]:
type sum("cost")
0 entree 30.0
1 dessert 5.0
2 appetizer 12.0
In [46]:
%%sql
SELECT type, 
       SUM(cost), 
       MIN(cost),
       MAX(name)
FROM Dish
GROUP BY type;
Running query in 'duckdb'
Out[46]:
type sum("cost") min("cost") max("name")
0 entree 30.0 7 taco
1 dessert 5.0 5 ice cream
2 appetizer 12.0 4 potsticker