Why query patterns matter in SQL - See It in Action

Why Query Patterns Matter

📖 Scenario: You are working as a data analyst for a small online bookstore. You need to organize and retrieve information about books and their sales efficiently. Understanding how to write clear and effective SQL queries will help you get the right data quickly and avoid mistakes.

🎯 Goal: Build a simple database with books and sales tables, then write queries using good patterns to retrieve useful information about book sales.

📋 What You'll Learn

Create a books table with columns book_id, title, and author.

Create a sales table with columns sale_id, book_id, and quantity.

Write a query to find the total quantity sold for each book using a proper JOIN and GROUP BY pattern.

Write a query to find books that have no sales using a LEFT JOIN pattern.

💡 Why This Matters

🌍 Real World

In real businesses, writing clear and efficient SQL queries helps get the right data fast, which supports good decisions.

💼 Career

Data analysts and developers use these query patterns daily to combine and summarize data from multiple tables.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id (integer), title (text), and author (text). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee').

SQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the sales table and insert data

Write SQL statements to create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Then insert these exact rows: (1, 1, 3), (2, 2, 5), (3, 1, 2).

SQL

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE TABLE and INSERT INTO like before, matching the exact column names and values.

3

Write a query to find total quantity sold per book

Write a SQL query that uses JOIN to combine books and sales tables on book_id. Use GROUP BY on books.book_id and books.title to find the total quantity sold for each book. Select books.title and the sum of sales.quantity as total_sold.

SQL

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  book_id INTEGER,
  quantity INTEGER
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
  (1, 1, 3),
  (2, 2, 5),
  (3, 1, 2);

-- Write the query to find total quantity sold per book
-- Your code here

Hint

Use JOIN to connect tables and GROUP BY to sum quantities per book.

4

Write a query to find books with no sales

Write a SQL query that uses LEFT JOIN to join books with sales on book_id. Select books.title for books where sales.book_id is NULL, meaning no sales exist for that book.

SQL

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (book_id, title, author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  book_id INTEGER,
  quantity INTEGER
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
  (1, 1, 3),
  (2, 2, 5),
  (3, 1, 2);

SELECT books.title, SUM(sales.quantity) AS total_sold
FROM books
JOIN sales ON books.book_id = sales.book_id
GROUP BY books.book_id, books.title;

-- Write the query to find books with no sales
-- Your code here

Hint

Use LEFT JOIN and check for NULL in the joined table to find books without sales.

Practice

1. Why is it important to use consistent query patterns when writing SQL queries?

easy

A. They make queries easier to read and maintain over time.

B. They always make queries run faster regardless of data size.

C. They allow you to write queries without knowing the database structure.

D. They prevent any syntax errors automatically.

Solution

1. Step 1: Understand the role of query patterns

   Query patterns help organize SQL commands in a clear and consistent way.

2. Step 2: Identify the main benefit of consistency

   Consistent patterns make queries easier to read and maintain, especially for teams or future updates.

3. Final Answer:

   They make queries easier to read and maintain over time. -> Option A

4. Quick Check:

   Consistency improves readability and maintenance [OK]

Hint: Think about teamwork and future you reading the query [OK]

Common Mistakes:

• Assuming patterns always improve speed
• Believing patterns remove need to know database
• Thinking patterns fix syntax errors automatically

2. Which of the following is the correct SQL syntax to select all columns from a table named employees?

easy

A. SELECT ALL FROM employees;

B. SELECT * FROM employees;

C. GET * FROM employees;

D. SELECT employees ALL;

Solution

1. Step 1: Recall the standard SQL SELECT syntax

   The correct way to select all columns is using SELECT * FROM table_name;.

2. Step 2: Check each option for syntax correctness

   SELECT * FROM employees; matches the correct syntax. Options B, C, and D use invalid keywords or order.

3. Final Answer:

   SELECT * FROM employees; -> Option B

4. Quick Check:

   Standard SELECT syntax uses * for all columns [OK]

Hint: Remember: SELECT * FROM table_name; is standard [OK]

Common Mistakes:

• Using GET instead of SELECT
• Using ALL without SELECT
• Incorrect keyword order

3. Given the table orders with columns order_id, customer_id, and amount, what will this query return?

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

medium

A. A list of customers with the total amount they ordered.

B. All orders sorted by customer_id.

C. The average amount per order for each customer.

D. An error because SUM cannot be used without WHERE.

Solution

1. Step 1: Understand GROUP BY with aggregation

   The query groups rows by customer_id and sums the amount for each group.

2. Step 2: Interpret the SELECT clause

   It returns each customer_id with the total amount they ordered.

3. Final Answer:

   A list of customers with the total amount they ordered. -> Option A

4. Quick Check:

   GROUP BY + SUM gives totals per group [OK]

Hint: GROUP BY groups rows; SUM adds values per group [OK]

Common Mistakes:

• Confusing SUM with AVG
• Thinking GROUP BY sorts data
• Believing WHERE is needed for aggregation

4. Identify the error in this SQL query:

SELECT name, age FROM users WHERE age > 18 GROUP BY name;

medium

A. The query is correct as written.

B. WHERE clause cannot be used with GROUP BY.

C. SELECT must include only columns in GROUP BY.

D. GROUP BY should include all selected columns or use aggregation.

Solution

1. Step 1: Review SELECT and GROUP BY rules

   When using GROUP BY, all selected columns must be grouped or aggregated.

2. Step 2: Check the query columns

   The query selects name and age but only groups by name, so age is neither grouped nor aggregated.

3. Final Answer:

   GROUP BY should include all selected columns or use aggregation. -> Option D

4. Quick Check:

   All selected columns must be in GROUP BY or aggregated [OK]

Hint: All selected columns must be grouped or aggregated [OK]

Common Mistakes:

• Thinking WHERE conflicts with GROUP BY
• Ignoring missing aggregation for non-grouped columns
• Assuming query is correct without aggregation

5. You want to find customers who placed more than 3 orders and spent over $500 total. Which query pattern correctly achieves this?

SELECT customer_id, COUNT(*), SUM(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3 AND SUM(amount) > 500;

hard

A. Use WHERE to filter counts and sums after grouping.

B. Use GROUP BY only without HAVING or WHERE.

C. Use HAVING to filter groups based on aggregated values.

D. Use ORDER BY to filter groups with conditions.

Solution

1. Step 1: Understand filtering after aggregation

   WHERE filters rows before grouping; HAVING filters groups after aggregation.

2. Step 2: Identify correct clause for aggregated conditions

   Conditions on COUNT and SUM require HAVING, not WHERE.

3. Final Answer:

   Use HAVING to filter groups based on aggregated values. -> Option C

4. Quick Check:

   HAVING filters aggregated group results [OK]

Hint: Use HAVING for conditions on grouped data [OK]

Common Mistakes:

• Using WHERE instead of HAVING for aggregates
• Forgetting to group before filtering aggregates
• Using ORDER BY to filter data