Python SQL Fundamentals Practice Problems & Exercises
Practice: SQL Fundamentals
← Back to lessonWrite a query that returns all employees in the Engineering department with a salary greater than 90000, ordered by salary descending.
Solution:
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary REAL
)
""")
conn.executemany("INSERT INTO employees VALUES (?,?,?,?)", [
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Marketing', 72000),
(3, 'Carol', 'Engineering', 105000),
(4, 'Dave', 'HR', 68000),
(5, 'Eve', 'Engineering', 88000),
])
conn.commit()
def high_paid_engineers(conn):
cur = conn.execute("""
SELECT name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 90000
ORDER BY salary DESC
""")
return cur.fetchall()
print(high_paid_engineers(conn))
# [('Carol', 105000.0), ('Alice', 95000.0)]
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary REAL
)
""")
conn.executemany("INSERT INTO employees VALUES (?,?,?,?)", [
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Marketing', 72000),
(3, 'Carol', 'Engineering', 105000),
(4, 'Dave', 'HR', 68000),
(5, 'Eve', 'Engineering', 88000),
])
conn.commit()
# TODO: Query all engineers with salary > 90000
# Return list of (name, salary) tuples sorted by salary DESC
def high_paid_engineers(conn):
pass
print(high_paid_engineers(conn))
Expected Output
[('Carol', 105000.0), ('Alice', 95000.0)]Hints
Hint 1: Use WHERE with two conditions joined by AND.
Hint 2: Use ORDER BY salary DESC to sort descending.
Hint 3: cursor.fetchall() returns a list of tuples.
Using GROUP BY, compute the number of orders and total amount spent per customer, sorted by total spend descending.
Solution:
def customer_summary(conn):
cur = conn.execute("""
SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("""
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT,
product TEXT,
amount REAL
)
""")
conn.executemany("INSERT INTO orders VALUES (?,?,?,?)", [
(1, 'Alice', 'Widget', 50.0),
(2, 'Bob', 'Gadget', 120.0),
(3, 'Alice', 'Gadget', 80.0),
(4, 'Carol', 'Widget', 30.0),
(5, 'Bob', 'Widget', 45.0),
(6, 'Alice', 'Doohickey', 200.0),
])
conn.commit()
# TODO: Return (customer, order_count, total_spent) for each customer
# sorted by total_spent DESC
def customer_summary(conn):
pass
for row in customer_summary(conn):
print(row)
Expected Output
('Alice', 3, 330.0)
('Bob', 2, 165.0)
('Carol', 1, 30.0)Hints
Hint 1: GROUP BY customer aggregates rows per customer.
Hint 2: COUNT(*) counts rows; SUM(amount) totals the amount.
Hint 3: ORDER BY the SUM alias or column position.
Write an INNER JOIN query that returns each employee's name alongside their department name, sorted alphabetically by department then employee.
Solution:
def employee_departments(conn):
cur = conn.execute("""
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY d.name, e.name
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
dept_id INTEGER REFERENCES departments(id)
);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Marketing'),(3,'HR');
INSERT INTO employees VALUES
(1,'Alice',1),(2,'Bob',2),(3,'Carol',1),(4,'Dave',3),(5,'Eve',1);
""")
# TODO: Return (employee_name, department_name) for all employees
# sorted by department_name, then employee_name
def employee_departments(conn):
pass
for row in employee_departments(conn):
print(row)
Expected Output
('Alice', 'Engineering')
('Carol', 'Engineering')
('Eve', 'Engineering')
('Dave', 'HR')
('Bob', 'Marketing')Hints
Hint 1: JOIN employees e ON e.dept_id = d.id links the tables.
Hint 2: Select e.name and d.name in your SELECT clause.
Hint 3: ORDER BY d.name, e.name gives alphabetical ordering.
Use HAVING to filter sales reps whose total sales exceed 1200, returning name and total sorted by total descending.
Solution:
def top_reps(conn):
cur = conn.execute("""
SELECT rep, SUM(amount) AS total_sales
FROM sales
GROUP BY rep
HAVING SUM(amount) > 1200
ORDER BY total_sales DESC
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE sales (rep TEXT, amount REAL)")
conn.executemany("INSERT INTO sales VALUES (?,?)", [
('Alice', 500), ('Bob', 300), ('Alice', 700),
('Carol', 200), ('Bob', 900), ('Carol', 150),
('Alice', 400), ('Bob', 600),
])
conn.commit()
# TODO: Return (rep, total_sales) for reps whose total exceeds 1200
# sorted by total_sales DESC
def top_reps(conn):
pass
for row in top_reps(conn):
print(row)
Expected Output
('Bob', 1800.0)
('Alice', 1600.0)Hints
Hint 1: Use HAVING SUM(amount) > 1200 after GROUP BY.
Hint 2: HAVING filters aggregated groups; WHERE filters individual rows.
Hint 3: Order the results by the SUM descending.
Use a LEFT JOIN to find all customers who have placed zero orders. Return a sorted list of their names.
Solution:
def customers_without_orders(conn):
cur = conn.execute("""
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name
""")
return [row[0] for row in cur.fetchall()]
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1,'Alice'),(2,'Bob'),(3,'Carol'),(4,'Dave');
INSERT INTO orders VALUES (1,1,250.0),(2,1,75.0),(3,3,400.0);
""")
# TODO: Return names of customers who have NO orders
def customers_without_orders(conn):
pass
print(customers_without_orders(conn))
Expected Output
['Bob', 'Dave']Hints
Hint 1: LEFT JOIN keeps all rows from the left table even with no match.
Hint 2: Where there is no match, the joined columns are NULL.
Hint 3: Filter with WHERE o.id IS NULL to find unmatched customers.
Write a correlated subquery that finds products priced above their category's average price, sorted by price descending.
Solution:
def above_category_avg(conn):
cur = conn.execute("""
SELECT p1.name, p1.price, p1.category
FROM products p1
WHERE p1.price > (
SELECT AVG(p2.price)
FROM products p2
WHERE p2.category = p1.category
)
ORDER BY p1.price DESC
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, category TEXT);
INSERT INTO products VALUES
(1,'Laptop',999,'Electronics'),
(2,'Phone',699,'Electronics'),
(3,'Tablet',499,'Electronics'),
(4,'Desk',350,'Furniture'),
(5,'Chair',275,'Furniture'),
(6,'Monitor',450,'Electronics');
""")
# TODO: Return products that are priced above the average price
# of their own category. Return (name, price, category).
def above_category_avg(conn):
pass
for row in above_category_avg(conn):
print(row)
Expected Output
('Laptop', 999.0, 'Electronics')
('Monitor', 450.0, 'Electronics')
('Desk', 350.0, 'Furniture')Hints
Hint 1: A correlated subquery references the outer query's row.
Hint 2: SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category gives the per-category average.
Hint 3: Filter WHERE p1.price > (subquery).
Use a window function with ROW_NUMBER() PARTITION BY to find the highest-scoring student in each subject.
Solution:
def top_per_subject(conn):
cur = conn.execute("""
SELECT subject, student, score
FROM (
SELECT subject, student, score,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rn
FROM scores
)
WHERE rn = 1
ORDER BY subject
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE scores (
student TEXT,
subject TEXT,
score INTEGER
);
INSERT INTO scores VALUES
('Alice','Math',92),('Alice','Science',85),('Alice','English',78),
('Bob','Math',88),('Bob','Science',91),('Bob','English',72),
('Carol','Math',95),('Carol','Science',80),('Carol','English',90);
""")
# TODO: For each subject, return the top-scoring student.
# Return (subject, student, score).
def top_per_subject(conn):
pass
for row in top_per_subject(conn):
print(row)
Expected Output
('English', 'Carol', 90)
('Math', 'Carol', 95)
('Science', 'Bob', 91)Hints
Hint 1: Use ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) to rank within each subject.
Hint 2: Wrap in a subquery or CTE, then filter WHERE rn = 1.
Hint 3: SQLite supports window functions since version 3.25.
Use a CTE to calculate net balance per account (credits minus debits), returning results sorted by balance descending.
Solution:
def account_balances(conn):
cur = conn.execute("""
WITH balances AS (
SELECT
account,
SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) AS balance
FROM transactions
GROUP BY account
)
SELECT account, balance
FROM balances
ORDER BY balance DESC
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE transactions (
id INTEGER PRIMARY KEY,
account TEXT,
amount REAL,
type TEXT -- 'credit' or 'debit'
);
INSERT INTO transactions VALUES
(1,'ACC001',1000,'credit'),
(2,'ACC001',200,'debit'),
(3,'ACC001',500,'credit'),
(4,'ACC002',3000,'credit'),
(5,'ACC002',1500,'debit'),
(6,'ACC003',750,'credit');
""")
# TODO: Use a CTE to calculate the balance for each account
# (sum of credits - sum of debits).
# Return (account, balance) sorted by balance DESC.
def account_balances(conn):
pass
for row in account_balances(conn):
print(row)
Expected Output
('ACC002', 1500.0)
('ACC001', 1300.0)
('ACC003', 750.0)Hints
Hint 1: WITH credits AS (SELECT account, SUM(amount) ... WHERE type='credit') creates a CTE.
Hint 2: Create two CTEs — one for credits, one for debits — then JOIN them.
Hint 3: Alternatively, use CASE WHEN type='credit' THEN amount ELSE -amount END in a single SUM.
Compute a running total and 3-day moving average of revenue using SQL window frame clauses.
Solution:
def revenue_analytics(conn):
cur = conn.execute("""
SELECT
day,
revenue,
SUM(revenue) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3day
FROM daily_revenue
ORDER BY day
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE daily_revenue (
day INTEGER,
revenue REAL
);
INSERT INTO daily_revenue VALUES
(1, 100),(2, 150),(3, 80),(4, 200),(5, 120),
(6, 175),(7, 90),(8, 250),(9, 130),(10, 160);
""")
# TODO: Return (day, revenue, running_total, moving_avg_3day)
# running_total: cumulative sum up to and including that day
# moving_avg_3day: average of current day + previous 2 days
def revenue_analytics(conn):
pass
for row in revenue_analytics(conn):
print(row)
Expected Output
(1, 100.0, 100.0, 100.0)
(2, 150.0, 250.0, 125.0)
(3, 80.0, 330.0, 110.0)
(4, 200.0, 530.0, 143.33333333333334)
(5, 120.0, 650.0, 133.33333333333334)
(6, 175.0, 825.0, 165.0)
(7, 90.0, 915.0, 128.33333333333334)
(8, 250.0, 1165.0, 171.66666666666666)
(9, 130.0, 1295.0, 156.66666666666666)
(10, 160.0, 1455.0, 180.0)Hints
Hint 1: SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives a running total.
Hint 2: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) gives the 3-day moving average.
Hint 3: Both window functions can appear in the same SELECT.
Write a recursive CTE to traverse an organizational hierarchy and assign a depth level to each person.
Solution:
def org_depth(conn):
cur = conn.execute("""
WITH RECURSIVE hierarchy AS (
SELECT id, name, 0 AS depth
FROM org
WHERE manager_id IS NULL
UNION ALL
SELECT o.id, o.name, h.depth + 1
FROM org o
JOIN hierarchy h ON o.manager_id = h.id
)
SELECT name, depth
FROM hierarchy
ORDER BY depth, name
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE org (
id INTEGER PRIMARY KEY,
name TEXT,
manager_id INTEGER
);
INSERT INTO org VALUES
(1,'CEO',NULL),
(2,'VP Engineering',1),
(3,'VP Marketing',1),
(4,'Eng Manager',2),
(5,'Alice',4),
(6,'Bob',4),
(7,'Carol',2),
(8,'Marketing Lead',3),
(9,'Dave',8);
""")
# TODO: Return each person's name and their depth in the hierarchy
# (CEO = 0, VPs = 1, Managers = 2, etc.)
# Return (name, depth) sorted by depth, then name.
def org_depth(conn):
pass
for row in org_depth(conn):
print(row)
Expected Output
('CEO', 0)
('VP Engineering', 1)
('VP Marketing', 1)
('Carol', 2)
('Eng Manager', 2)
('Marketing Lead', 2)
('Alice', 3)
('Bob', 3)
('Dave', 3)Hints
Hint 1: WITH RECURSIVE cte AS (base_case UNION ALL recursive_case) builds the hierarchy.
Hint 2: Base case: SELECT id, name, 0 AS depth FROM org WHERE manager_id IS NULL.
Hint 3: Recursive case: JOIN org to cte on org.manager_id = cte.id, increment depth.
Build a complex analytics query combining JOINs, CTEs, and window functions to produce a category performance report.
Solution:
def category_report(conn):
cur = conn.execute("""
WITH product_revenue AS (
SELECT
p.id,
p.name AS product_name,
p.category,
SUM(p.price * oi.qty) AS rev,
COUNT(DISTINCT oi.order_id) AS orders
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name, p.category
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY rev DESC) AS rn
FROM product_revenue
),
cat_totals AS (
SELECT
category,
SUM(rev) AS total_revenue,
SUM(orders) AS order_count
FROM product_revenue
GROUP BY category
)
SELECT
ct.category,
ct.total_revenue,
ct.order_count,
r.product_name AS top_product
FROM cat_totals ct
JOIN ranked r ON r.category = ct.category AND r.rn = 1
ORDER BY ct.category
""")
return cur.fetchall()
import sqlite3
conn = sqlite3.connect(':memory:')
conn.executescript("""
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, qty INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, order_date TEXT);
INSERT INTO products VALUES
(1,'Widget','Hardware',25.0),(2,'Gadget','Electronics',150.0),
(3,'Doohickey','Hardware',40.0),(4,'Thingamajig','Electronics',200.0),
(5,'Whatsit','Hardware',15.0);
INSERT INTO orders VALUES
(1,'Alice','2024-01-10'),(2,'Bob','2024-01-15'),
(3,'Alice','2024-02-01'),(4,'Carol','2024-02-10');
INSERT INTO order_items VALUES
(1,1,3),(1,2,1),(2,3,2),(2,4,1),
(3,1,1),(3,5,4),(4,2,2),(4,3,1);
""")
# TODO: For each category, find:
# - total revenue (price * qty)
# - number of distinct orders
# - best-selling product (by revenue) within the category
# Return (category, total_revenue, order_count, top_product)
def category_report(conn):
pass
for row in category_report(conn):
print(row)
Expected Output
('Electronics', 850.0, 3, 'Thingamajig')
('Hardware', 315.0, 4, 'Doohickey')Hints
Hint 1: First compute revenue per product: JOIN order_items to products.
Hint 2: Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY product_revenue DESC) to rank within category.
Hint 3: Join back to get per-category totals alongside the per-product ranking.