SQL Examples: Basic SELECT Queries¶

This notebook contains simple SQL queries written for practice using the Chinook SQLite database.
I’m using it to demonstrate clear, well-structured query writing in a Jupyter environment.

The examples below cover:

  • Selecting and filtering data
  • Sorting and limiting results
  • Using basic expressions and aliases

Each query is executed through Python (sqlite + pandas), with outputs shown directly below for clarity.

Setup¶

In [1]:
from pathlib import Path
from IPython.display import Image, display
import pandas as pd

# project root
repo_root = Path().resolve().parent  

# database path relative to project root
DB_PATH = repo_root / "data" / "Chinook_Sqlite.sqlite"

assert DB_PATH.exists(), f"Database file not found: {DB_PATH}"

# SQLAlchemy engine
from sqlalchemy import create_engine
engine = create_engine(f"sqlite:///{DB_PATH}")

# image path relative to repo root
image_path = repo_root / "input" / "ChinookDBStructure.png"

# check it exists
assert image_path.exists(), f"Image file not found: {image_path}"

# display
display(Image(filename=str(image_path), width=600, height=400))
No description has been provided for this image

Query Examples¶

1) List all customers with their first and last names.¶

In [5]:
query = """

SELECT DISTINCT TRIM(FirstName || ' ' || LastName) as customer_name
FROM customer 
ORDER BY customer_name 
LIMIT 10 ;

"""
df = pd.read_sql_query(query, engine)
df
Out[5]:
customer_name
0 Aaron Mitchell
1 Alexandre Rocha
2 Astrid Gruber
3 Bjørn Hansen
4 Camille Bernard
5 Daan Peeters
6 Dan Miller
7 Diego Gutiérrez
8 Dominique Lefebvre
9 Eduardo Martins

2) Count how many invoices each customer has.¶

In [6]:
query = """

SELECT TRIM(c.FirstName || ' ' || c.LastName) AS customer_name,
       COUNT(i.InvoiceId) AS invoice_count
FROM customer AS c
JOIN invoice AS i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY customer_name
LIMIT 10;

"""
df = pd.read_sql_query(query, engine)
df
Out[6]:
customer_name invoice_count
0 Aaron Mitchell 7
1 Alexandre Rocha 7
2 Astrid Gruber 7
3 Bjørn Hansen 7
4 Camille Bernard 7
5 Daan Peeters 7
6 Dan Miller 7
7 Diego Gutiérrez 7
8 Dominique Lefebvre 7
9 Eduardo Martins 7

3) Show the total revenue from all invoices.¶

In [5]:
query = """

SELECT SUM(Total) AS total_revenue
FROM invoice ;

"""
df = pd.read_sql_query(query, engine)
df
Out[5]:
total_revenue
0 2328.6

4) Find all tracks with a unit price greater than 0.99.¶

In [31]:
query = """

SELECT TrackId,
       Name,
       UnitPrice
FROM track
WHERE UnitPrice > 0.99

"""
df = pd.read_sql_query(query, engine)
df
Out[31]:
TrackId Name UnitPrice
0 2819 Battlestar Galactica: The Story So Far 1.99
1 2820 Occupation / Precipice 1.99
2 2821 Exodus, Pt. 1 1.99
3 2822 Exodus, Pt. 2 1.99
4 2823 Collaborators 1.99
... ... ... ...
208 3362 There's No Place Like Home, Pt. 1 1.99
209 3363 There's No Place Like Home, Pt. 2 1.99
210 3364 There's No Place Like Home, Pt. 3 1.99
211 3428 Branch Closing 1.99
212 3429 The Return 1.99

213 rows × 3 columns

5) List all unique genres in the database.¶

In [30]:
query = """

SELECT DISTINCT GenreId,
                Name AS Genre
FROM genre
ORDER BY Genre

"""
df = pd.read_sql_query(query, engine)
df
Out[30]:
GenreId Genre
0 23 Alternative
1 4 Alternative & Punk
2 6 Blues
3 11 Bossa Nova
4 24 Classical
5 22 Comedy
6 21 Drama
7 12 Easy Listening
8 15 Electronica/Dance
9 13 Heavy Metal
10 17 Hip Hop/Rap
11 2 Jazz
12 7 Latin
13 3 Metal
14 25 Opera
15 9 Pop
16 14 R&B/Soul
17 8 Reggae
18 1 Rock
19 5 Rock And Roll
20 20 Sci Fi & Fantasy
21 18 Science Fiction
22 10 Soundtrack
23 19 TV Shows
24 16 World

6) Retrieve all invoices from the year 2022, ordered by date.¶

In [38]:
query = """

SELECT *
FROM invoice
WHERE InvoiceDate >= '2022-01-01 00:00:00' 	
  AND InvoiceDate < '2023-01-01 00:00:00'
ORDER BY InvoiceDate ;

"""
df = pd.read_sql_query(query, engine)
df
Out[38]:
InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total
0 84 43 2022-01-08 00:00:00 68, Rue Jouvence Dijon None France 21000 1.98
1 85 45 2022-01-08 00:00:00 Erzsébet krt. 58. Budapest None Hungary H-1073 1.98
2 86 47 2022-01-09 00:00:00 Via Degli Scipioni, 43 Rome RM Italy 00192 3.96
3 87 51 2022-01-10 00:00:00 Celsiusg. 9 Stockholm None Sweden 11230 6.94
4 88 57 2022-01-13 00:00:00 Calle Lira, 198 Santiago None Chile None 17.91
... ... ... ... ... ... ... ... ... ...
78 162 50 2022-12-15 00:00:00 C/ San Bernardo 85 Madrid None Spain 28015 1.98
79 163 52 2022-12-16 00:00:00 202 Hoxton Street London None United Kingdom N1 5LH 3.96
80 164 56 2022-12-17 00:00:00 307 Macacha Güemes Buenos Aires None Argentina 1106 5.94
81 165 3 2022-12-20 00:00:00 1498 rue Bélanger Montréal QC Canada H2G 1A7 8.91
82 166 12 2022-12-25 00:00:00 Praça Pio X, 119 Rio de Janeiro RJ Brazil 20040-020 13.86

83 rows × 9 columns

7) Calculate the average track duration for each genre.¶

In [39]:
query = """

SELECT ge.Name AS Genre,
       ROUND(AVG(tr.Milliseconds)/60000, 2) AS track_duration_minutes
FROM track AS tr
JOIN genre AS ge ON tr.GenreId = ge.GenreId
GROUP BY ge.GenreId
ORDER BY Genre ;

"""
df = pd.read_sql_query(query, engine)
df
Out[39]:
Genre track_duration_minutes
0 Alternative 4.40
1 Alternative & Punk 3.91
2 Blues 4.51
3 Bossa Nova 3.66
4 Classical 4.90
5 Comedy 26.42
6 Drama 42.92
7 Easy Listening 3.15
8 Electronica/Dance 5.05
9 Heavy Metal 4.96
10 Hip Hop/Rap 2.97
11 Jazz 4.86
12 Latin 3.88
13 Metal 5.16
14 Opera 2.91
15 Pop 3.82
16 R&B/Soul 3.67
17 Reggae 4.12
18 Rock 4.73
19 Rock And Roll 2.24
20 Sci Fi & Fantasy 48.53
21 Science Fiction 43.76
22 Soundtrack 4.07
23 TV Shows 35.75
24 World 3.75