SQL Query Patterns

📘 Key Concepts and Definitions

• Database: An organized collection of structured information, or data.
• Table: A collection of related data held in a table format within a database. It consists of columns and rows.
• Column (or Field): A vertical entity in a table that contains all information of one specific type.
• Row (or Record): A horizontal entity in a table representing a single data item.
• Primary Key: A column (or set of columns) that uniquely identifies each row in a table.
• Foreign Key: A key used to link two tables together. It's a column in one table that refers to the primary key in another.
• Query: A request for data or information from a database.
• JOIN: A clause used to combine rows from two or more tables based on a related column between them.

🧮 The Structure of a SELECT Query

A SELECT statement retrieves data. The clauses are written in a specific order, but the database executes them in a different logical order.

Written Order

SELECT ...
FROM ...
JOIN ... ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;

Logical Execution Order

1. FROM / JOIN: Gathers and joins the tables.
2. WHERE: Filters rows based on conditions.
3. GROUP BY: Groups rows into summary rows.
4. HAVING: Filters groups based on conditions.
5. SELECT: Selects the final columns.
6. ORDER BY: Sorts the final result set.
7. LIMIT: Restricts the number of rows returned.

🛠️ Core Commands & Clauses

Data Retrieval & Filtering

SELECT first_name, last_name, email
FROM users
WHERE country = 'Canada' AND age > 25;

Common Filtering Operators

• AND, OR: Combine multiple conditions.
• IN (...): Match any value in a list (e.g., country IN ('USA', 'Canada')).
• BETWEEN ... AND ...: Match a value within a range.
• LIKE: Simple pattern matching (e.g., email LIKE '%@gmail.com'). The % is a wildcard.
• IS NULL / IS NOT NULL: Check for empty values.

Joining Tables

SELECT users.name, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Aggregation & Grouping

SELECT country, COUNT(id) AS user_count
FROM users
GROUP BY country
HAVING COUNT(id) > 10; -- 'HAVING' filters after grouping

Common aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().

Sorting & Limiting

SELECT name, salary
FROM employees
ORDER BY salary DESC -- 'DESC' for descending, 'ASC' for ascending
LIMIT 10; -- Get the top 10 highest salaries

🧭 Workflow for Building a Complex Query

1. Identify the goal: What question are you trying to answer? (e.g., "Who are our top 10 customers by total spending?").
2. Find the tables: Locate the tables containing the required data (e.g., `customers`, `orders`).
3. Start simple: Write a basic `SELECT ... FROM ...` query.
4. Add JOINs: Join the tables together using primary and foreign keys. Use aliases for readability.
5. Apply WHERE filters: Filter the rows to include only the data you need (e.g., completed orders).
6. Add GROUP BY and aggregates: If you need to summarize data, add your `GROUP BY` clause and aggregate functions (e.g., `GROUP BY customer_id`, `SUM(order_total)`).
7. Apply HAVING filters: If you need to filter the aggregated groups, use `HAVING`.
8. Format the output: Use `ORDER BY` to sort the results and `SELECT` aliases to name your columns.
9. Apply LIMIT: Restrict the final output to the desired number of rows.

⌨️ Productivity Tips

• Use Aliases: Shorten table and column names to make queries easier to write and read.

  SELECT c.name, o.order_date
  FROM customers AS c JOIN orders AS o ON c.id = o.customer_id;

• Common Table Expressions (CTEs): Use the WITH clause to break down complex queries into logical, readable steps.

  WITH RegionalSales AS (
      SELECT region, SUM(amount) as total_sales
      FROM sales
      GROUP BY region
  )
  SELECT * FROM RegionalSales WHERE total_sales > 1000;

• Format Your Code: Use indentation and line breaks to format your SQL. It doesn't affect execution but drastically improves readability.

📊 Visualizing JOINs

Venn diagrams are a helpful way to understand how different JOINs work.

🧪 Example: Finding Top Spending Customers

Goal: Get the names and total spending of the top 3 customers who have spent more than $1000 in total.

Tables: customers (id, name), orders (id, customer_id, amount)

SELECT
    c.name,
    SUM(o.amount) AS total_spent
FROM
    customers AS c
INNER JOIN
    orders AS o ON c.id = o.customer_id
GROUP BY
    c.id, c.name -- Group by both ID and name
HAVING
    SUM(o.amount) > 1000
ORDER BY
    total_spent DESC
LIMIT 3;

🧹 Troubleshooting Common Errors

• Error: Syntax error near '...'
  • Fix: You have a typo. Carefully check for misspelled keywords, missing commas, or misplaced parentheses. The error message usually points to the location.
• Error: Ambiguous column name 'id'
  • Fix: The column name exists in more than one table in your query. Specify the table it belongs to using an alias: customers.id or c.id.
• Problem: Query returns incorrect or duplicate rows.
  • Fix: Your JOIN condition is likely incorrect or missing. Ensure you are joining on the correct primary/foreign key relationship. Using SELECT DISTINCT can sometimes hide the problem, but it's better to fix the join.
• Error: misuse of aggregate function or filtering on an aggregate doesn't work.
  • Fix: You are trying to use an aggregate function like SUM() or COUNT() in the WHERE clause. Aggregates can only be filtered in the HAVING clause.

📚 References and Further Reading

• SQLZoo: Interactive SQL tutorials.
• W3Schools SQL Tutorial: A comprehensive reference for beginners.
• Mode's SQL Tutorial: Tutorials for beginners to advanced users.
• PostgreSQL Documentation: Official documentation for one of the most popular databases.