Reading from SQL Databases

Basic SELECT Queries

Reading data is the most common database operation. The SELECT command gets data from tables.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

What SELECT * means: Get all columns from the table.

Selecting Specific Columns

Get only the columns you need.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("SELECT name, grade FROM students")
rows = cursor.fetchall()

for name, grade in rows:
    print("Student:", name, "Grade:", grade)

connection.close()

Why this is better: Faster and uses less memory when tables have many columns.

Filtering with WHERE

Get specific rows that match conditions.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT name, age
    FROM students
    WHERE age >= 21
""")

rows = cursor.fetchall()

print("Students 21 or older:")
for row in rows:
    print(row)

connection.close()

Common WHERE operators:

= (equals)
!= (not equals)
(greater than)
< (less than)
= (greater than or equal)
<= (less than or equal)

Multiple Conditions

Use AND and OR to combine conditions.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT name, age, grade
    FROM students
    WHERE age > 20 AND grade = "A"
""")

rows = cursor.fetchall()

print("Students over 20 with A grade:")
for row in rows:
    print(row)

connection.close()

What AND does: Both conditions must be true.

Using OR:

code.py

cursor.execute("""
    SELECT name FROM students
    WHERE grade = "A" OR grade = "B"
""")

What OR does: At least one condition must be true.

Sorting Results

Use ORDER BY to sort data.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT name, age
    FROM students
    ORDER BY age DESC
""")

rows = cursor.fetchall()

print("Students by age (oldest first):")
for row in rows:
    print(row)

connection.close()

What ORDER BY does:

ASC: Smallest to largest (default)
DESC: Largest to smallest

Sort by multiple columns:

code.py

cursor.execute("""
    SELECT name, grade, age
    FROM students
    ORDER BY grade ASC, age DESC
""")

What this does: Sorts by grade first, then by age within each grade.

Limiting Results

Get only a specific number of rows.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT name, age
    FROM students
    ORDER BY age DESC
    LIMIT 5
""")

rows = cursor.fetchall()

print("Top 5 oldest students:")
for row in rows:
    print(row)

connection.close()

What LIMIT does: Returns only first 5 results.

Counting Rows

Find out how many rows match your query.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("SELECT COUNT(*) FROM students")
total = cursor.fetchone()[0]
print("Total students:", total)

cursor.execute("SELECT COUNT(*) FROM students WHERE grade = "A"")
a_students = cursor.fetchone()[0]
print("A students:", a_students)

connection.close()

What COUNT(*) does: Counts all rows that match the query.

Finding Maximum and Minimum

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("SELECT MAX(age) FROM students")
oldest = cursor.fetchone()[0]
print("Oldest student age:", oldest)

cursor.execute("SELECT MIN(age) FROM students")
youngest = cursor.fetchone()[0]
print("Youngest student age:", youngest)

connection.close()

What these do:

MAX() finds highest value
MIN() finds lowest value

Calculating Averages and Sums

code.py

import sqlite3

connection = sqlite3.connect("sales.db")
cursor = connection.cursor()

cursor.execute("SELECT AVG(price) FROM products")
average_price = cursor.fetchone()[0]
print("Average price:", round(average_price, 2))

cursor.execute("SELECT SUM(quantity) FROM products")
total_items = cursor.fetchone()[0]
print("Total items:", total_items)

connection.close()

What these do:

AVG() calculates average
SUM() adds all values

Grouping Data

Group rows and perform calculations on each group.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT grade, COUNT(*)
    FROM students
    GROUP BY grade
""")

rows = cursor.fetchall()

print("Students per grade:")
for grade, count in rows:
    print("Grade:", grade, "Count:", count)

connection.close()

What GROUP BY does: Combines rows with same grade and counts each group.

Filtering Groups with HAVING

HAVING is like WHERE but for groups.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT grade, COUNT(*) as student_count
    FROM students
    GROUP BY grade
    HAVING student_count > 5
""")

rows = cursor.fetchall()

print("Grades with more than 5 students:")
for row in rows:
    print(row)

connection.close()

Difference between WHERE and HAVING:

WHERE filters rows before grouping
HAVING filters groups after grouping

Pattern Matching with LIKE

Search for patterns in text.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT name FROM students
    WHERE name LIKE "J%"
""")

rows = cursor.fetchall()

print("Names starting with J:")
for row in rows:
    print(row[0])

connection.close()

LIKE patterns:

% matches any characters
_ matches single character

Examples:

"J%" : Starts with J
"%son" : Ends with son
"%an%" : Contains an
"J_hn" : J, any char, hn (matches John, Jahn)

Practice Example

The scenario: Analyze product sales data from database.

code.py

import sqlite3

connection = sqlite3.connect("store.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT COUNT(*) FROM products
""")
total_products = cursor.fetchone()[0]
print("Total products:", total_products)
print()

cursor.execute("""
    SELECT AVG(price) FROM products
""")
avg_price = cursor.fetchone()[0]
print("Average price:", round(avg_price, 2))
print()

cursor.execute("""
    SELECT name, price
    FROM products
    WHERE price > 500
    ORDER BY price DESC
""")
expensive = cursor.fetchall()

print("Expensive products (over 500):")
for name, price in expensive:
    print("-", name + ":", price)
print()

cursor.execute("""
    SELECT category, COUNT(*), AVG(price)
    FROM products
    GROUP BY category
    ORDER BY COUNT(*) DESC
""")
categories = cursor.fetchall()

print("Products by category:")
for category, count, avg in categories:
    print("Category:", category)
    print("  Count:", count)
    print("  Avg Price:", round(avg, 2))
print()

cursor.execute("""
    SELECT name, quantity
    FROM products
    WHERE quantity < 5
    ORDER BY quantity ASC
""")
low_stock = cursor.fetchall()

print("Low stock warning (less than 5):")
for name, qty in low_stock:
    print("-", name + ":", qty, "left")

connection.close()

What this analysis does:

Counts total products
Calculates average price
Lists expensive products sorted by price
Groups products by category with counts and averages
Shows low stock items as warning

Converting to List of Dictionaries

Make results easier to work with.

code.py

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("SELECT name, age, grade FROM students")

columns = [description[0] for description in cursor.description]
rows = cursor.fetchall()

students = []
for row in rows:
    student = dict(zip(columns, row))
    students.append(student)

for student in students:
    print(student)

connection.close()

What this creates: List of dictionaries: [{'name': 'John', 'age': 20, 'grade': 'A'}, ...]

Key Points to Remember

SELECT specifies columns to get. Use * for all columns or list specific ones for better performance.

WHERE filters rows before results. Use comparison operators and AND/OR for complex conditions.

ORDER BY sorts results. ASC for ascending, DESC for descending. LIMIT restricts number of results.

Aggregate functions: COUNT, SUM, AVG, MAX, MIN. These calculate values across multiple rows.

GROUP BY combines rows with same values. HAVING filters groups after grouping.

Common Mistakes

Mistake 1: Forgetting WHERE quotes

code.py

cursor.execute("SELECT * FROM students WHERE grade = A")  # Error!
cursor.execute("SELECT * FROM students WHERE grade = 'A'")  # Correct

Mistake 2: Using HAVING instead of WHERE

code.py

SELECT * FROM students HAVING age > 20  # Wrong! Use WHERE
SELECT * FROM students WHERE age > 20  # Correct

Mistake 3: Wrong ORDER BY

code.py

cursor.execute("SELECT * FROM students ORDER BY grade")  # ASC by default
cursor.execute("SELECT * FROM students ORDER BY grade DESC")  # Correct for reverse

What's Next?

You now know how to read from databases. Next, you'll learn about writing to SQL databases - inserting, updating, and managing data efficiently.