3.
Class Lecture: Basic SQL Queries (with Examples, Lab
& Diagram)
Learning Objectives
By the end of this lecture, students should be able to:
Write SQL queries using SELECT, WHERE, ORDER BY, LIMIT
Filter data using LIKE, BETWEEN, and IN
Query relational tables (Customers & Orders)
Understand how SQL queries are executed internally
1. Introduction to SQL
SQL (Structured Query Language) is used to interact with relational databases such as:
MySQL
PostgreSQL
Microsoft SQL Server
SQL allows you to:
Retrieve data
Filter records
Sort results
Aggregate and analyze datasets
2. Sample Database Schema
Customers Table
customer_id name city age
1 John Doe Lagos 32
2 Mary Smith Abuja 25
3 Ahmed Ali Kano 40
Orders Table
order_id customer_id amount order_date
101 1 5000 2024-01-10
102 2 12000 2024-02-15
103 1 8000 2024-03-01
3. SELECT Statement
The SELECT statement retrieves data from a database.
SELECT name, city FROM Customers;
Retrieves only the name and city columns.
SELECT * FROM Customers;
Retrieves all columns.
4. WHERE Clause (Filtering Data)
Used to filter records based on conditions.
SELECT * FROM Customers
WHERE city = 'Lagos';
Returns customers located in Lagos.
SELECT * FROM Customers
WHERE age > 30;
Returns customers older than 30.
5. LIKE Operator (Pattern Matching)
Used for searching patterns in text.
SELECT * FROM Customers
WHERE name LIKE 'J%';
Names starting with "J"
SELECT * FROM Customers
WHERE name LIKE '%Smith';
Names ending with "Smith"
6. BETWEEN Operator
Filters values within a range.
SELECT * FROM Customers
WHERE age BETWEEN 25 AND 35;
Customers aged between 25 and 35.
7. IN Operator
Filters values that match a list.
SELECT * FROM Customers
WHERE city IN ('Lagos', 'Abuja');
Customers in Lagos or Abuja.
8. ORDER BY (Sorting Results)
Sorts query results.
SELECT * FROM Customers
ORDER BY age ASC;
Sort by age (ascending)
SELECT * FROM Customers
ORDER BY age DESC;
Sort by age (descending)
9. LIMIT (Restrict Output)
Restricts number of rows returned.
SELECT * FROM Customers
LIMIT 2;
Returns only 2 records.
10. Combining Multiple Clauses
SELECT name, city, age
FROM Customers
WHERE age BETWEEN 25 AND 40
AND city IN ('Lagos', 'Abuja')
ORDER BY age DESC
LIMIT 2;
Combines filtering, sorting, and limiting.
11. LAB SESSION: Querying Customers & Orders
Task 1: Retrieve all customers
SELECT * FROM Customers;
Task 2: Customers from Lagos
SELECT name FROM Customers
WHERE city = 'Lagos';
Task 3: Orders above ₦7000
SELECT * FROM Orders
WHERE amount > 7000;
Task 4: Orders between ₦5000 and ₦10000
SELECT * FROM Orders
WHERE amount BETWEEN 5000 AND 10000;
Task 5: Orders by specific customers
SELECT * FROM Orders
WHERE customer_id IN (1, 2);
Task 6: Sort orders by amount (highest first)
SELECT * FROM Orders
ORDER BY amount DESC;
Task 7: Top 2 highest orders
SELECT * FROM Orders
ORDER BY amount DESC
LIMIT 2;
Task 8 (Advanced): Combine conditions
SELECT * FROM Orders
WHERE amount > 5000
ORDER BY order_date DESC
LIMIT 3;
12. Diagram: SQL Query Execution Flow
Explanation of Flow:
1. FROM → Select table
2. WHERE → Filter rows
3. SELECT → Choose columns
4. ORDER BY → Sort results
5. LIMIT → Restrict output
13. Real-Life Case Study (Nigerian Context)
A retail company in Lagos wants to:
Identify top customers
Analyze high-value orders
Filter customers by location
Example:
SELECT name, city
FROM Customers
WHERE city = 'Lagos'
ORDER BY age DESC;
Helps target premium customers for marketing campaigns.
14. Common Mistakes to Avoid
Forgetting WHERE when filtering
Using = instead of LIKE for patterns
Misplacing ORDER BY before WHERE
Not limiting large datasets
15. Summary
Clause Purpose
SELECT Retrieve columns
WHERE Filter rows
LIKE Pattern matching
BETWEEN Range filtering
IN Multiple values
ORDER BY Sorting
LIMIT Restr results
16. Assignment
1. Write a query to find customers older than 30 in Abuja
2. Retrieve the 3 lowest orders
3. Find customers whose names start with "A"
4. List orders between ₦6000 and ₦15000 sorted by date
Next Class Preview
JOIN operations (INNER, LEFT, RIGHT)
Aggregations (COUNT, SUM, AVG)
GROUP BY and HAVING