Querying Data in Relational Databases

Understanding SQL Queries

Structured Query Language (SQL) is the standard language for interacting with relational databases. It allows you to retrieve, insert, update, and delete data. This section covers the fundamental concepts of constructing SQL queries.

Basic SELECT Statement

The most common SQL statement is the SELECT statement, used to retrieve data from one or more tables.

SELECT column1, column2, ...
FROM table_name;

-- To select all columns:
SELECT *
FROM table_name;

Filtering Data with WHERE

The WHERE clause allows you to specify conditions to filter the rows returned by a query.

SELECT column1, column2
FROM table_name
WHERE condition;

Conditions can include comparison operators (=, !=, >, <, >=, <=), logical operators (AND, OR, NOT), and pattern matching (LIKE).

Sorting Results with ORDER BY

Use the ORDER BY clause to sort the results in ascending (ASC) or descending (DESC) order.

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

Advanced Querying Techniques

Mastering advanced querying techniques can significantly improve your ability to extract meaningful insights from your data.

Aggregate Functions

SQL provides aggregate functions to perform calculations on sets of rows.

  • COUNT(): Counts the number of rows.
  • SUM(): Calculates the sum of values in a column.
  • AVG(): Computes the average of values in a column.
  • MIN(): Finds the minimum value in a column.
  • MAX(): Finds the maximum value in a column.
SELECT COUNT(*) AS total_records,
AVG(price) AS average_price
FROM products
WHERE category = 'Electronics';

Grouping Data with GROUP BY

The GROUP BY clause is used with aggregate functions to group rows that have the same values in specified columns.

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

Filtering Groups with HAVING

Similar to WHERE, the HAVING clause filters groups created by GROUP BY. It is used when you want to filter based on an aggregate function's result.

SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 500;

Subqueries and Common Table Expressions (CTEs)

Subqueries and CTEs allow for more complex data manipulation by enabling queries within queries.

Subqueries

A subquery is a query nested inside another SQL query. They can be used in SELECT, FROM, WHERE, and HAVING clauses.

SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date > '2023-01-01'
);

Common Table Expressions (CTEs)

CTEs provide a way to define temporary, named result sets that you can reference within a single SQL statement. They improve readability for complex queries.

WITH RecentOrders AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date > '2023-01-01'
GROUP BY customer_id
)
SELECT c.customer_name, ro.order_count
FROM customers c
JOIN RecentOrders ro ON c.customer_id = ro.customer_id
WHERE ro.order_count > 5;

Topics Covered