🔍 Querying Data

Querying is the process of retrieving data from your database. It's one of the most fundamental operations you'll perform. Whether you need to get all records or filter for specific data, SQL's `SELECT` statement is your primary tool.

📋 The SELECT Statement Basics

The `SELECT` statement retrieves data from one or more tables. Here's the fundamental syntax:

SELECT column1, column2, column3
FROM table_name;

Example 1: Selecting All Columns

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Retrieve all data from the customers table
cursor.execute("SELECT * FROM customers")
all_customers = cursor.fetchall()

for customer in all_customers:
    print(customer)

conn.close()

Example 2: Selecting Specific Columns

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Retrieve only names and emails
cursor.execute("SELECT name, email FROM customers")
results = cursor.fetchall()

for name, email in results:
    print(f"{name}: {email}")

conn.close()

🔎 Filtering with WHERE

The `WHERE` clause filters records based on specified conditions. This is essential for finding exactly what you need.

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition;

Common Operators:

• `=` : Equal to

• `!=` or `<>` : Not equal to

• `>` : Greater than

• `<` : Less than

• `>=` : Greater than or equal to

• `<=` : Less than or equal to

• `AND` : Multiple conditions (all must be true)

• `OR` : Multiple conditions (at least one must be true)

• `IN` : Value in a list

• `LIKE` : Pattern matching

• `BETWEEN` : Within a range

Example 3: Simple WHERE Condition

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find all books with price greater than $20
cursor.execute("""
    SELECT title, price
    FROM books
    WHERE price > 20
""")

expensive_books = cursor.fetchall()
for title, price in expensive_books:
    print(f"{title}: ${price:.2f}")

conn.close()

Example 4: Multiple Conditions with AND/OR

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find fiction books published after 2020 with price < $30
cursor.execute("""
    SELECT title, genre, year, price
    FROM books
    WHERE genre = 'Fiction'
    AND year > 2020
    AND price < 30
""")

results = cursor.fetchall()
for title, genre, year, price in results:
    print(f"{title} ({year}): ${price}")

conn.close()

📊 Sorting with ORDER BY

The `ORDER BY` clause sorts your results in ascending (ASC) or descending (DESC) order.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

Example 5: Sorting Results

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Get books sorted by price (lowest to highest)
cursor.execute("""
    SELECT title, price
    FROM books
    ORDER BY price ASC
""")

affordable_books = cursor.fetchall()
print("Books from cheapest to most expensive:")
for title, price in affordable_books:
    print(f"  {title}: ${price:.2f}")

conn.close()

⚙️ Limiting Results with LIMIT

The `LIMIT` clause restricts the number of rows returned. This is useful for pagination and getting a sample of your data.

Syntax:

SELECT column1, column2
FROM table_name
LIMIT number;

Example 6: Limiting and Pagination

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Get the 5 most expensive books
cursor.execute("""
    SELECT title, price
    FROM books
    ORDER BY price DESC
    LIMIT 5
""")

expensive = cursor.fetchall()
print("Top 5 most expensive books:")
for title, price in expensive:
    print(f"  {title}: ${price:.2f}")

# Pagination: Get books 11-20
cursor.execute("""
    SELECT title, author
    FROM books
    LIMIT 10 OFFSET 10
""")

page_2 = cursor.fetchall()

conn.close()

📈 Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single result.

Common Aggregate Functions:

• `COUNT(column)` : Count non-null values

• `SUM(column)` : Sum of values

• `AVG(column)` : Average of values

• `MIN(column)` : Minimum value

• `MAX(column)` : Maximum value

Example 7: Using Aggregate Functions

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Get statistics about the books table
cursor.execute("""
    SELECT
        COUNT(*) as total_books,
        AVG(price) as average_price,
        MIN(price) as cheapest_price,
        MAX(price) as most_expensive_price
    FROM books
""")

stats = cursor.fetchone()
print(f"Total Books: {stats[0]}")
print(f"Average Price: ${stats[1]:.2f}")
print(f"Cheapest: ${stats[2]:.2f}")
print(f"Most Expensive: ${stats[3]:.2f}")

conn.close()

🏷️ GROUP BY Basics

The `GROUP BY` clause groups rows based on one or more columns. It's typically used with aggregate functions to get summaries by category.

Syntax:

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Example 8: Grouping Data

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Count books by genre
cursor.execute("""
    SELECT genre, COUNT(*) as book_count
    FROM books
    GROUP BY genre
""")

genre_stats = cursor.fetchall()
print("Books per genre:")
for genre, count in genre_stats:
    print(f"  {genre}: {count} books")

# Average price by genre
cursor.execute("""
    SELECT genre, AVG(price) as average_price
    FROM books
    GROUP BY genre
    ORDER BY average_price DESC
""")

price_by_genre = cursor.fetchall()
print("\nAverage price by genre:")
for genre, avg_price in price_by_genre:
    print(f"  {genre}: ${avg_price:.2f}")

conn.close()

🔑 Key Takeaways

• **SELECT** retrieves columns from one or more tables

• **WHERE** filters results based on conditions

• **ORDER BY** sorts results in ascending or descending order

• **LIMIT** restricts the number of rows returned

• **Aggregate functions** (COUNT, SUM, AVG, MIN, MAX) summarize data

• **GROUP BY** groups rows by column values for aggregate calculations

• Always close database connections when done using `conn.close()`

📚 Further Learning

Ready to take the next step? Check out these resources:

• [Advanced Querying](/courses/python/database_basics/advanced/querying_data) - Learn about subqueries, CTEs, and window functions

• [Challenges & Quizzes](/#) - Test your querying skills with practical exercises