Module 1

Module 1: Reading Data – The Foundation of Displaying Information

Now that you have your environment set up and understand the basics of relational databases, it’s time to learn the most fundamental operation in SQL: retrieving data. This is the core of how applications display information to users.

Lesson 1.1: Your First Query: SELECT and FROM

The most basic query structure involves two keywords: SELECT and FROM.

  • SELECT: Specifies the columns (pieces of information) you want to retrieve.
  • FROM: Specifies the table you want to retrieve the data from.

Example 1: Getting all information about all users

Imagine you need to display a list of all registered users in an admin dashboard. You’d want all the information stored in the users table. The asterisk (*) is a shorthand for “all columns”.

SQL

SELECT *
FROM users;

Running this query in your SQL client will return all rows and all columns from the users table.

Example 2: Getting specific information about products

Now, imagine you want to display a list of products on your shop page, but you only need their names and prices. Specifying column names is generally better practice than using *, especially in applications, as it’s more explicit and can be slightly more efficient (the database only sends the data you need).

SQL

SELECT product_name, price
FROM products;

This query returns only the product_name and price columns for all rows in the products table.

Syntax Recap:

  • Get all columns: SELECT * FROM tablename;
  • Get specific columns: SELECT column1, column2 FROM tablename;
  • SQL keywords like SELECT and FROM are typically written in uppercase by convention, but are not usually case-sensitive. Table and column names might be case-sensitive depending on the database system and configuration.
  • The semicolon (;) marks the end of the SQL statement. It’s required by some tools and good practice to include.

Try it yourself:

  1. Write a query to select the email addresses of all users.
  2. Write a query to select all columns from the categories table.
  3. Write a query to select the order_id and order_date from the orders table.

Lesson 1.2: Filtering Data: WHERE Clause

Retrieving all data is often not what you need. Applications usually require specific data based on certain criteria. The WHERE clause allows you to filter rows based on conditions.

Application Scenario: How does a login system find the user record matching the provided email? How does a category page show only products belonging to that category? The WHERE clause is the answer.

Syntax: SELECT columns FROM tablename WHERE condition;

The condition uses operators to compare column values:

1. Comparison Operators:

  • =: Equal to
  • != or <>: Not equal to
  • >: Greater than
  • <: Less than
  • >=: Greater than or equal to
  • <=: Less than or equal to

Example 1: Find a specific user by email

SQL

SELECT user_id, first_name, last_name
FROM users
WHERE email = 'alice.s@email.com';

This finds the user whose email address exactly matches ‘alice.s@email.com’. Note that text values are enclosed in single quotes.

Example 2: Find products over a certain price

SQL

SELECT product_name, price
FROM products
WHERE price > 100;

This retrieves products costing more than $100.

2. LIKE Operator (Pattern Matching):

Used for searching within text columns.

  • %: Wildcard representing zero or more characters.
  • _: Wildcard representing exactly one character.

Example 3: Find products with “Laptop” in their name

SQL

SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%Laptop%'; -- Matches 'Laptop Pro 15"', 'Gaming Laptop', etc.

Example 4: Find users whose first name starts with ‘A’

SQL

SELECT first_name, last_name
FROM users
WHERE first_name LIKE 'A%';

3. BETWEEN Operator (Ranges):

Selects values within a given range (inclusive).

Example 5: Find orders placed within a specific date range (Date format might vary slightly by DB)

SQL

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31'; -- Example dates

4. IN Operator (List of Values):

Checks if a column’s value matches any value in a list.

Example 6: Find products in the ‘Electronics’ (ID 1) or ‘Books’ (ID 2) categories

SQL

SELECT product_name, category_id
FROM products
WHERE category_id IN (1, 2);

This is often more concise than WHERE category_id = 1 OR category_id = 2.

5. Combining Conditions (AND, OR, NOT):

  • AND: Both conditions must be true.
  • OR: At least one condition must be true.
  • NOT: Reverses the truth of a condition.
  • Use parentheses () to control the order of evaluation, just like in mathematics.

Example 7: Find electronics (category 1) that cost less than $50

SQL

SELECT product_name, price
FROM products
WHERE category_id = 1 AND price < 50;

Example 8: Find users named ‘Alice’ OR users registered after a certain date

SQL

SELECT user_id, first_name, registration_date
FROM users
WHERE first_name = 'Alice' OR registration_date > '2025-02-01';

Try it yourself:

  1. Find the product with product_id = 3.
  2. Find all orders placed by user_id = 2.
  3. Find all products that are not in category 3 (Clothing).
  4. Find users whose email address contains ‘bob’.
  5. Find products whose price is between $20 and $40.

Lesson 1.3: Sorting Results: ORDER BY

By default, the database returns rows in whatever order it finds most efficient, which often appears arbitrary. Applications almost always need to present data in a meaningful order. The ORDER BY clause lets you specify the sorting criteria.

Application Scenario: Displaying products from cheapest to most expensive, showing blog posts or user comments with the newest ones first, listing users alphabetically by last name.

Syntax: SELECT columns FROM tablename [WHERE condition] ORDER BY column_to_sort_by [ASC|DESC];

  • ASC: Ascending order (A-Z, 0-9, earliest date to latest). This is the default if unspecified.
  • DESC: Descending order (Z-A, 9-0, latest date to earliest).

Example 1: List products by price, highest first

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC;

Example 2: List users by registration date, newest first

SQL

SELECT first_name, last_name, registration_date
FROM users
ORDER BY registration_date DESC;

Sorting by Multiple Columns:

You can sort by more than one column. The sorting happens in the order the columns are listed.

Example 3: List products first by category ID (ascending), then by name (ascending)

SQL

SELECT product_id, product_name, category_id
FROM products
ORDER BY category_id ASC, product_name ASC;
-- or simply ORDER BY category_id, product_name; (ASC is default)

This groups products by category, and within each category, sorts them alphabetically by name.

Try it yourself:

  1. List all categories alphabetically by category_name.
  2. List all orders, showing the most expensive (total_amount) first.
  3. List users alphabetically by last_name, then first_name.

Lesson 1.4: Limiting Results: LIMIT and OFFSET

Often, you don’t need all the rows that match your query, especially if there are thousands or millions. You might only need the top few, or a specific “page” of results. The LIMIT and OFFSET clauses handle this.

Application Scenario: This is essential for:

  • Pagination: Displaying search results or lists 10 items at a time. If the user clicks “Page 2”, you show items 11-20.
  • Top N Lists: Showing the “Top 5 most recent orders” or the “Top 10 best-selling products”.

Syntax (Standard SQL):

  • LIMIT count: Returns only the first count rows.
  • LIMIT count OFFSET offset_value: Skips the first offset_value rows, then returns the next count rows.

(Note: MySQL uses a slightly different shorthand LIMIT offset_value, count but also supports the standard LIMIT count OFFSET offset_value which we will use here for broader compatibility).

Example 1: Get the 5 most expensive products

We need to sort first, then limit the results.

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

Example 2: Implementing Pagination – Getting Page 2 of Products (assuming 10 items per page)

  • Page 1: LIMIT 10 OFFSET 0 (Items 1-10)
  • Page 2: LIMIT 10 OFFSET 10 (Items 11-20)
  • Page 3: LIMIT 10 OFFSET 20 (Items 21-30)
  • …and so on. The offset is (page_number - 1) * items_per_page.

SQL

-- Get products for page 2 (items 11-20), ordered by name
SELECT product_id, product_name
FROM products
ORDER BY product_name
LIMIT 10 OFFSET 10;

Try it yourself:

  1. Find the 3 newest users (registration_date).
  2. Find the details for the 5 cheapest products (price).
  3. Imagine you have 5 products per page. Write the query to get the products for Page 3, ordered alphabetically.

Mini-Project 1: Querying the Store

Let’s combine what you’ve learned! Write SQL queries to answer the following questions based on our E-commerce database:

  1. Recent Users: Find the first_name, last_name, and email of all users who registered after January 1st, 2025. (Use ‘2025-01-01’ as the date).
  2. Affordable Electronics: List the product_name and price of all products in the ‘Electronics’ category (assuming category_id = 1) that cost less than $100. The results should be sorted by price from lowest to highest.
  3. Latest Orders: Show the order_id, order_date, and total_amount for the 5 most recent orders placed.

(Feel free to experiment and check your results in your SQL client!)

That concludes Module 1! You can now retrieve data using SELECT and FROM, filter it precisely using WHERE, control the presentation order using ORDER BY, and manage the number of results using LIMIT and OFFSET. These are the absolute workhorses for displaying data in any application. Next, we’ll explore how to combine data from multiple tables using JOINs.