Your first article built a strong foundation: what databases are, why we use them, core concepts (tables, keys, relationships), and basic SQL actions. The best next step is to become confident in writing real queries and understanding how SQL thinks. This Part 2 focuses on the most common skills beginners need to pass labs and start building projects: filtering, pattern matching, handling missing values, grouping, aggregates, joins (deeper), subqueries, and good query habits.
- 1. The SQL Mindset: How to Think When Writing Queries
- 2. Setup: Reuse the Same School Database
- 3. SELECT Deep Dive: Columns, Aliases, and Expressions
- 4. Filtering Correctly: WHERE, AND/OR, BETWEEN, IN
- 5. Pattern Matching and Missing Values: LIKE and NULL
- 6. Summaries: COUNT, SUM, AVG, MIN, MAX
- 7. GROUP BY and HAVING (Most Common Reporting Lab Topic)
- 8. JOINs Explained Clearly (INNER, LEFT, and Common Mistakes)
- 9. Subqueries: Queries Inside Queries (Beginner-Friendly)
- 10. Practical Habits: Naming, Testing, and Debugging Queries
- 11. Practice Labs (With Expected Outputs and Hints)
- 12. Mini Project: Build a Simple “School Report” Feature
- 13. What to Learn Next (Part 3 Preview)
1. The SQL Mindset: How to Think When Writing Queries
Beginners often think SQL is “memorizing commands.” In reality, SQL is about answering questions using data. Every SQL query is a response to a question like:
- “Which students are in BSIT?”
- “How many students are enrolled in Database Systems?”
- “Show each student’s courses and grades.”
- “Which students are not enrolled in any course?”
A simple process you can follow every time:
- Understand the question. What is being asked?
- Identify the tables needed. Where is the data stored?
- Start simple. Write a basic
SELECT *and confirm the tables contain what you think. - Add conditions. Use
WHERE, thenORDER BY. - Combine tables only when needed. Add
JOINstep-by-step. - Test your results. Check if the answer makes sense.
SQL is also “set-based,” meaning it works with groups of rows at once (not one row at a time like many programming languages).
This is why GROUP BY and aggregates are so powerful.
2. Setup: Reuse the Same School Database
We will reuse the same schema from Part 1 so you can practice consistently. If you already created these tables, you can skip to the next section.
2.1 Create Tables
CREATE TABLE Students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
program VARCHAR(20),
year_level INT
);
CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
credits INT
);
CREATE TABLE Enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
grade VARCHAR(2),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
2.2 Insert Sample Data
INSERT INTO Students (student_id, first_name, last_name, program, year_level) VALUES
(1001, 'Maria', 'Santos', 'BSIT', 1),
(1002, 'Juan', 'Cruz', 'BSIT', 2),
(1003, 'Ana', 'Reyes', 'BSCS', 1),
(1004, 'Mark', 'Dela Cruz', 'BSIT', 1),
(1005, 'Liza', 'Garcia', 'BSBA', 2);
INSERT INTO Courses (course_id, course_name, credits) VALUES
(1, 'Database Systems', 3),
(2, 'Programming 1', 3),
(3, 'Web Development', 3),
(4, 'Networking 1', 3);
INSERT INTO Enrollments (enrollment_id, student_id, course_id, grade) VALUES
(1, 1001, 1, 'A'),
(2, 1001, 2, 'B'),
(3, 1002, 1, 'B'),
(4, 1003, 3, 'A'),
(5, 1004, 1, 'C'),
(6, 1004, 4, 'B');
Notice that student 1005 (Liza) has no enrollments yet.
This is intentional so you can practice LEFT JOIN and “missing data” queries.
3. SELECT Deep Dive: Columns, Aliases, and Expressions
3.1 Selecting only what you need
Beginners often use SELECT * all the time. It’s okay for learning, but in real work you should
select only the needed columns.
SELECT student_id, first_name, last_name
FROM Students;
3.2 Aliases: making output readable
Aliases rename columns in the output. This helps when you are creating reports.
SELECT
student_id AS id,
first_name AS given_name,
last_name AS family_name
FROM Students;
3.3 Expressions: create computed values
You can create new values from existing columns. Example: a simple full name display. Different databases have different concatenation methods. Here are common patterns:
MySQL:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM Students;
PostgreSQL / SQLite:
SELECT first_name || ' ' || last_name AS full_name
FROM Students;
If you’re unsure which one works in your lab environment, test both. This is a normal beginner situation.
4. Filtering Correctly: WHERE, AND/OR, BETWEEN, IN
4.1 WHERE: the most used keyword after SELECT
SELECT *
FROM Students
WHERE program = 'BSIT';
4.2 AND vs OR (common logic mistake)
AND means all conditions must be true.
OR means at least one condition must be true.
Example: BSIT students in year level 1:
SELECT *
FROM Students
WHERE program = 'BSIT'
AND year_level = 1;
If you mistakenly use OR here, you would also get: students who are year 1 but not BSIT, and BSIT students who are not year 1.
4.3 BETWEEN (ranges)
SELECT *
FROM Students
WHERE year_level BETWEEN 1 AND 2;
4.4 IN (multiple possible values)
SELECT *
FROM Students
WHERE program IN ('BSIT', 'BSCS');
5. Pattern Matching and Missing Values: LIKE and NULL
5.1 LIKE (searching text patterns)
Use LIKE when you want “matches that look like this,” not exact matches.
Students with last name starting with “De”:
SELECT *
FROM Students
WHERE last_name LIKE 'De%';
% means “any characters.”
So 'De%' means “De + anything after it.”
5.2 NULL: the value that means “missing”
NULL does not mean zero. It does not mean empty string. It means “no value / unknown.”
This is extremely important because many beginners write:
WHERE column = NULL and wonder why it returns no results.
Correct way:
SELECT *
FROM Enrollments
WHERE grade IS NULL;
Correct way for “not null”:
SELECT *
FROM Enrollments
WHERE grade IS NOT NULL;
6. Summaries: COUNT, SUM, AVG, MIN, MAX
Aggregates summarize many rows into one result.
6.1 COUNT: how many rows?
SELECT COUNT(*) AS total_students
FROM Students;
6.2 AVG: average of numeric values
Our sample grades are letters. So let’s average course credits instead.
SELECT AVG(credits) AS avg_credits
FROM Courses;
6.3 MIN and MAX
SELECT MIN(year_level) AS min_year, MAX(year_level) AS max_year
FROM Students;
7. GROUP BY and HAVING (Most Common Reporting Lab Topic)
Many lab activities ask “how many” or “total per group.”
When you see phrases like:
“per course,” “per student,” “per program,” you probably need GROUP BY.
7.1 Count students per program
SELECT program, COUNT(*) AS total_students
FROM Students
GROUP BY program
ORDER BY total_students DESC;
Read it like this: “Group students by program, and count how many students are in each program.”
7.2 Count enrollments per course
SELECT c.course_name, COUNT(e.enrollment_id) AS total_enrolled
FROM Courses c
LEFT JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name
ORDER BY total_enrolled DESC;
We used LEFT JOIN because we want to include courses even if no one is enrolled yet.
7.3 HAVING: filter after grouping
Use WHERE to filter rows before grouping.
Use HAVING to filter groups after grouping.
Show only programs with at least 2 students:
SELECT program, COUNT(*) AS total_students
FROM Students
GROUP BY program
HAVING COUNT(*) >= 2;
8. JOINs Explained Clearly (INNER, LEFT, and Common Mistakes)
JOIN is one of the most important topics in databases. You use JOIN when the answer needs data from more than one table.
8.1 INNER JOIN: only matching rows
If a student has no enrollment, INNER JOIN will not show that student in the result.
SELECT
s.student_id,
s.first_name,
s.last_name,
c.course_name,
e.grade
FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_name;
8.2 LEFT JOIN: keep all rows from the left table
LEFT JOIN is used when you want to keep everything from the main table, even if there’s no match in the other table. This is perfect for questions like: “Show all students, even those not enrolled yet.”
SELECT
s.student_id,
s.first_name,
s.last_name,
c.course_name,
e.grade
FROM Students s
LEFT JOIN Enrollments e ON e.student_id = s.student_id
LEFT JOIN Courses c ON c.course_id = e.course_id
ORDER BY s.student_id;
In this output, students with no enrollments will show NULL for course_name and grade.
8.3 Common JOIN mistake: missing or wrong ON condition
If you forget the ON condition or join on the wrong columns, you can create a “cartesian product” (too many rows) and the results will look wrong.
Bad example (do not do this):
SELECT *
FROM Students s
JOIN Courses c;
The above will match every student with every course, which is not what we want.
9. Subqueries: Queries Inside Queries (Beginner-Friendly)
A subquery is simply a query inside another query. It helps when:
- You want to filter using a list produced by another query.
- You want to compare values to a computed value (like average credits).
9.1 Find students who are enrolled in “Database Systems”
Step 1: Find the course_id of “Database Systems”.
Step 2: Find enrollments that match that course_id.
Step 3: Find students who match those enrollments.
SELECT *
FROM Students
WHERE student_id IN (
SELECT student_id
FROM Enrollments
WHERE course_id IN (
SELECT course_id
FROM Courses
WHERE course_name = 'Database Systems'
)
);
This looks long, but it’s readable if you think in steps. Later you can also write it using JOINs (often faster and clearer), but subqueries are still important to know.
9.2 Find courses with no enrollments
SELECT *
FROM Courses
WHERE course_id NOT IN (
SELECT course_id
FROM Enrollments
);
If you want an alternative approach, you can use LEFT JOIN and check for NULL:
SELECT c.*
FROM Courses c
LEFT JOIN Enrollments e ON e.course_id = c.course_id
WHERE e.course_id IS NULL;
10. Practical Habits: Naming, Testing, and Debugging Queries
10.1 Good naming habits
A beginner-friendly naming rule:
- Tables: plural nouns (Students, Courses, Enrollments)
- Primary keys: table name +
_id(student_id, course_id) - Foreign keys match the referenced primary key name (student_id, course_id)
10.2 Debugging habit: run the pieces
When a JOIN query looks wrong, test it in parts:
- Run
SELECT * FROM Students; - Run
SELECT * FROM Enrollments; - Run the JOIN with only one join first (Students + Enrollments)
- Add Courses only after the first join works
10.3 Always verify with a small known case
Example: If you know Maria (1001) is enrolled in 2 courses, your query that counts her courses should return 2. If it returns 4, you likely duplicated rows with a join or grouped incorrectly.
11. Practice Labs (With Expected Outputs and Hints)
These practice questions are designed to match typical classroom/lab tasks. Try to answer them without copying solutions. If you get stuck, write your attempt and then fix it.
Lab A: Simple filtering
- List all BSIT students.
- List all students whose last name starts with “C”.
- List all students in year level 1 or 2, sorted by last_name.
Lab B: JOIN practice
- Show each enrollment with student full name, course name, and grade.
- Show all students even if not enrolled (course and grade can be NULL).
- Show all courses even if nobody is enrolled yet.
Lab C: GROUP BY practice
- Count students per program.
- Count how many students are enrolled per course.
- Show only courses with at least 2 enrollments (use HAVING).
Lab D: “Find missing” (classic lab problem)
Question: List students who are not enrolled in any course.
Hint: Use LEFT JOIN from Students to Enrollments and check for NULL in Enrollments.
12. Mini Project: Build a Simple “School Report” Feature
A good beginner milestone is being able to produce useful “reports.” Here are three practical reports you can generate with SQL.
12.1 Report 1: Student Enrollment List
Goal: show student name + course name + grade for all enrollments.
SELECT
s.student_id,
s.first_name,
s.last_name,
c.course_name,
e.grade
FROM Enrollments e
JOIN Students s ON s.student_id = e.student_id
JOIN Courses c ON c.course_id = e.course_id
ORDER BY s.last_name, s.first_name, c.course_name;
12.2 Report 2: Students with number of courses
Goal: show every student and how many courses they are taking (including zero).
SELECT
s.student_id,
s.first_name,
s.last_name,
COUNT(e.course_id) AS num_courses
FROM Students s
LEFT JOIN Enrollments e ON e.student_id = s.student_id
GROUP BY s.student_id, s.first_name, s.last_name
ORDER BY num_courses DESC, s.last_name;
12.3 Report 3: Course popularity
Goal: show each course and the number of enrollments.
SELECT
c.course_id,
c.course_name,
COUNT(e.enrollment_id) AS total_enrolled
FROM Courses c
LEFT JOIN Enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_name
ORDER BY total_enrolled DESC, c.course_name;
If you can write and explain these three reports, you are already ahead of many beginners. These are the exact patterns used in many real systems (school systems, HR systems, store systems).
13. What to Learn Next (Part 3 Preview)
After Part 2, a beginner is usually ready for Part 3 topics that move from “writing queries” to “designing and protecting data correctly.”
Recommended Part 3: Database Design and Data Integrity (Beginner to Intermediate)
- How to choose correct primary keys (natural vs surrogate keys)
- Composite keys and junction tables (many-to-many done right)
- Normalization (1NF, 2NF, 3NF) with simple examples
- Constraints in real projects (UNIQUE, NOT NULL, CHECK)
- Cascading rules (ON DELETE CASCADE, ON UPDATE CASCADE) and when to avoid them
- Transactions in practice (COMMIT/ROLLBACK) and why they matter
- Intro to performance: indexes, query plans (conceptual)
If you want, tell me which DBMS your student uses in lab (MySQL, PostgreSQL, SQLite, SQL Server). I can adjust the SQL syntax (especially for string concatenation and date functions) so every query works exactly in her environment.
