Essential SQL Query Techniques for Data Retrieval
Fundamental Data Selection
Retrieving Specific Columns
To obtain student names from a pupil table:
SELECT pupil_name FROM pupil;
Fetching multiple attributes like name and gender:
SELECT pupil_name, gender FROM pupil;
Selecting all available attributes:
SELECT * FROM pupil;
The SELECT keyword initiates a query, * represents all columns, and FROM specifies the source table.
Conditional Data Filtering
| Operator | Description |
|---|---|
| = | Equality |
| <> or != | Inequality |
| < | Less than |
| <= | Less than or equal to |
| > | Greater than |
| >= | Greater than or equal to |
| BETWEEN .. AND | Range inclusion |
| IS NULL | Null value check |
| AND | Logical conjunction |
| OR | Logical disjunction |
| IN | Set membership |
| NOT | Logical negation |
| LIKE | Pattern matching with wildcards |
Query Examples with Conditions
Retrieve all information for pupils in class 'CS101':
SELECT * FROM pupil WHERE class_code = 'CS101';
Find names of 20-year-old students:
SELECT pupil_name FROM pupil WHERE age_years = 20;
List names and genders for a specific class:
SELECT pupil_name, gender FROM pupil WHERE class_code = 'CS101';
Identify students not aged 20:
SELECT pupil_name FROM pupil WHERE age_years <> 20;
-- Alternative:
SELECT pupil_name FROM pupil WHERE age_years != 20;
Locate students younger than 21:
SELECT * FROM pupil WHERE age_years < 21;
Find pupils aged between 21 and 25 inclusive:
SELECT * FROM pupil WHERE age_years BETWEEN 21 AND 25;
Discover students without class assignment:
SELECT * FROM pupil WHERE class_code IS NULL;
Filter male students in a particular class:
SELECT * FROM pupil WHERE class_code = 'CS101' AND gender = 'M';
Retrieve pupils from multiple classes:
SELECT * FROM pupil WHERE class_code IN ('CS101', 'CS102');
Select specific records by identifier:
SELECT * FROM pupil WHERE id IN (101, 103);
Pattern-Based Filtering
Wildcards: % matches any sequence of characters, _ matches a single character.
Find all students whose surname begins with 'Smith':
SELECT * FROM pupil WHERE pupil_name LIKE 'Smith%';
Locate students with a two-character name starting with 'Li':
SELECT * FROM pupil WHERE pupil_name LIKE 'Li_';
Search for names containing 'John':
SELECT * FROM pupil WHERE pupil_name LIKE '%John%';
Aggregate Operations
| Function | Purpose |
|---|---|
| SUM() | Calculate total |
| AVG() | Compute average |
| MAX() | Find maximum value |
| MIN() | Find minimum value |
| COUNT() | Count records |
Calculate total age of all students:
SELECT SUM(age_years) FROM pupil;
Detemrine average student age:
SELECT AVG(age_years) FROM pupil;
Find maximum age:
SELECT MAX(age_years) FROM pupil;
Count total students:
SELECT COUNT(*) FROM pupil;
Count students with assigned classes:
SELECT COUNT(class_code) FROM pupil;
Note: COUNT(*) includes all rows; COUNT(column) excludes NULL values.
Grouping Data
Calculate average age per class:
SELECT AVG(age_years), class_code FROM pupil GROUP BY class_code;
Count students by age within each class:
SELECT COUNT(age_years), class_code, age_years FROM pupil GROUP BY class_code, age_years;
Find oldest student per class:
SELECT MAX(age_years), class_code FROM pupil GROUP BY class_code;
Sorting Results
Results are sorted by the primary key by default. Use ORDER BY for custom ordering.
Sort by age ascending:
SELECT * FROM pupil ORDER BY age_years;
Sort by age descending:
SELECT * FROM pupil ORDER BY age_years DESC;
Multi-level sorting:
SELECT * FROM pupil ORDER BY age_years DESC, student_id;
Sort by class then age:
SELECT * FROM pupil ORDER BY class_code, age_years;
The default sort order is ASC (ascending). Columns are evaluated left to right.
Pagination
For large datasets, retrieve results in manageable chunks.
First page (records 1-3):
SELECT * FROM pupil LIMIT 3 OFFSET 0;
Second page (records 4-6):
SELECT * FROM pupil LIMIT 3 OFFSET 3;
LIMIT specifies maximum rows, OFFSET indicates starting position (0-indexed).
Multi-Table Joins
Cross join (Cartesian product):
SELECT s.pupil_name, c.course_title FROM pupil s, course c;
SQL92 Syntax
Join students with their courses:
SELECT s.pupil_name, c.course_title
FROM pupil s, course c
WHERE s.course_id = c.course_id;
SQL99 Syntax (Preferred)
SELECT pupil.pupil_name, course.course_title
FROM pupil
JOIN course
ON pupil.course_id = course.course_id;
Join Variants
Inner Join (default):
SELECT pupil.pupil_name, course.course_title
FROM pupil
INNER JOIN course
ON pupil.course_id = course.course_id;
Left Outer Join: Includes all left table rows with matching right table rows.
SELECT pupil.pupil_name, course.course_title
FROM pupil
LEFT JOIN course
ON pupil.course_id = course.course_id;
Right Outer Join: Includes alright table rows with matching left table rows.
SELECT pupil.pupil_name, course.course_title
FROM pupil
RIGHT JOIN course
ON pupil.course_id = course.course_id;
Multi-Table Join:
SELECT pupil.pupil_name, subject.subject_name
FROM pupil
JOIN enrollment ON pupil.student_id = enrollment.student_id
JOIN subject ON enrollment.subject_id = subject.subject_id;
Subqueries
Inner queries execute first. Test inner queries independently.
Find students enrolled in course 'CS101':
SELECT t1.pupil_name
FROM
(SELECT pupil.pupil_name, enrollment.course_code
FROM pupil
INNER JOIN enrollment
ON pupil.student_id = enrollment.student_id) t1
WHERE t1.course_code = 'CS101';
Locate students taking 'Mathematics':
SELECT t2.pupil_name
FROM
(SELECT t1.pupil_name, subject.course_name
FROM
(SELECT pupil.pupil_name, enrollment.course_code
FROM pupil
INNER JOIN enrollment
ON pupil.student_id = enrollment.student_id) t1
INNER JOIN subject
ON t1.course_code = subject.course_code) t2
WHERE course_name = 'Mathematics';
Practical Exercises
- List all students with their class names:
SELECT pupil.pupil_name, class.class_name
FROM pupil
INNER JOIN class
ON pupil.class_code = class.class_code;
- Find the oldest student:
SELECT * FROM pupil ORDER BY age_years DESC LIMIT 1;
- Determine the most common age:
SELECT age_years, COUNT(age_years) AS age_count
FROM pupil
GROUP BY age_years
ORDER BY age_count DESC
LIMIT 1;
- Identify students over 21 enrolled in 'Database Systems':
SELECT t3.pupil_name
FROM
(SELECT t2.student_id, pupil.pupil_name, pupil.age_years
FROM pupil
RIGHT JOIN
(SELECT t1.student_id
FROM
(SELECT enrollment.student_id, enrollment.course_code, subject.course_name
FROM enrollment
INNER JOIN subject
ON enrollment.course_code = subject.course_code) t1
WHERE t1.course_name = 'Database Systems') t2
ON pupil.student_id = t2.student_id) t3
WHERE age_years > 21;
- Find the oldest student in 'Rainbow Class' and their courses:
SELECT t3.pupil_name, subject.course_name
FROM
(SELECT t2.pupil_name, enrollment.course_code
FROM
(SELECT t1.student_id, t1.pupil_name, t1.age_years
FROM
(SELECT pupil.student_id, pupil.pupil_name, pupil.age_years, class.class_name
FROM pupil
INNER JOIN class
ON pupil.class_code = class.class_code) t1
WHERE t1.class_name = 'Rainbow Class'
ORDER BY age_years DESC
LIMIT 1) t2
INNER JOIN enrollment
ON t2.student_id = enrollment.student_id) t3
INNER JOIN subject
ON t3.course_code = subject.course_code;
