Filter and Organize Enrollment Records with SQL in PostgreSQL

Table of Contents

1. Challenge

   Step 1: Filter Student Records for a Specific Term

   Step 1: Filter Student Records for a Specific Term

   Welcome to the lab! As a data analyst, your most fundamental task is retrieving data. In this step, you'll learn to select records from a table and filter them based on specific criteria using the SELECT, FROM, and WHERE clauses.

   The students Table

   All of your queries will run against a pre-populated table named students. It contains student enrollment information, including names, courses, academic terms, and enrollment status.

   Basic Retrieval with SELECT and FROM

   The most basic SQL query retrieves all data from a table. The asterisk (*) is a wildcard that means "all columns."

   SELECT * FROM students;
   

   Filtering with the WHERE Clause

   To narrow your results, you use the WHERE clause. It acts as a filter, telling the database to return only the rows that match a specific condition. For example, to find all students in a particular course, you would write:

   SELECT * FROM students WHERE course = 'CS101';
   

   Notice that text values (strings) must be enclosed in single quotes (').

   Combining Filters with AND

   Real-world questions often require multiple conditions. You can combine filters using the AND operator. The query will only return rows that satisfy all of the specified conditions.

   SELECT * FROM students WHERE term = 'Fall 2025' AND status = 'active';
   

   Now, let's apply these concepts to answer some requests from the registrar's office. You will write your queries in the .sql files located in the workspace directory.

   How to Run SQL Files in PostgreSQL

   When you need to execute a SQL script from the terminal, run:

   psql -d postgres -f filename.sql
   

   The output may enter paging mode and you see (END) at the bottom of the terminal, press:

   q
   

   to return to the normal terminal prompt.

2. Challenge

   Step 2: Apply Logical Operators to Combine Filters

   Step 2: Apply Logical Operators to Combine Filters

   In the previous step, you used AND to combine filters. Now, you'll expand your toolkit with OR and NOT to handle more complex logical scenarios. You'll also learn why parentheses are crucial for controlling the order of operations.

   The OR Operator

   The OR operator returns rows that match at least one of the given conditions. It's useful when you need data from multiple, alternative categories.

   For example, to get a list of students in either the computer science or math department:

   SELECT * FROM students WHERE course = 'CS101' OR course = 'MATH202';
   

   The NOT Operator

   The NOT operator inverts a condition. It's often used to exclude a specific category from your results. For example, to find all students who are not inactive:

   SELECT * FROM students WHERE NOT status = 'inactive';
   

   This is equivalent to using the != or <> operator: WHERE status != 'inactive'.

   Controlling Logic with Parentheses

   When you mix AND and OR, SQL follows a specific order of operations ( AND is typically evaluated before OR). This can lead to unexpected results. To ensure your logic is evaluated correctly, use parentheses () to group conditions.

   Imagine you need active students from either the 'Fall 2025' or 'Spring 2025' term. This query is incorrect:

   -- Incorrect: This might return inactive students from Spring 2025
   SELECT * FROM students WHERE status = 'active' AND term = 'Fall 2025' OR term = 'Spring 2025';
   

   The correct way is to group the OR conditions:

   -- Correct: Groups the term conditions first
   SELECT * FROM students WHERE status = 'active' AND (term = 'Fall 2025' OR term = 'Spring 2025');
   

   Let's put this into practice.

3. Challenge

   Step 3: Use Pattern Matching for Partial Text Search

   Step 3: Use Pattern Matching for Partial Text Search

   Sometimes, you don't know the exact value you're searching for, or you need to find records that follow a certain pattern. SQL's LIKE operator is perfect for this. It allows you to perform partial text searches using wildcards.

   Wildcard Characters

   There are two primary wildcards:

   • % (Percent sign): Matches any sequence of zero or more characters.
   • _ (Underscore): Matches any single character.

   Using LIKE

   LIKE is case-sensitive in PostgreSQL. Here are some examples:

   • To find all courses that start with 'Intro':

     SELECT course FROM students WHERE course LIKE 'Intro%';
     

   • To find all students whose last name ends with 'son':

     SELECT last_name FROM students WHERE last_name LIKE '%son';
     

   • To find all courses that contain the word 'Data':

     SELECT course FROM students WHERE course LIKE '%Data%';
     

   Case-Insensitive Matching with ILIKE

   Often, you'll want to find a pattern regardless of its capitalization. PostgreSQL provides a convenient, non-standard operator called ILIKE for this.

   • To find all courses containing 'data' (matching 'data', 'Data', etc.):

     SELECT course FROM students WHERE course ILIKE '%data%';
     

   Now it's your turn to find records based on patterns.

4. Challenge

   Step 4: Handle Missing or NULL Values

   Step 4: Handle Missing or NULL Values

   In the real world, datasets are rarely perfect. A common issue is missing data, which is represented in SQL by the special value NULL. NULL doesn't equal anything—not even itself! This means you can't use standard comparison operators like = or != to find it. In this step, you'll learn the correct way to handle NULL values.

   Filtering for NULL with IS NULL

   To find rows where a value is missing, you must use the IS NULL operator.

   • To find all students who do not have an email address on file:

     SELECT * FROM students WHERE email IS NULL;
     

   Similarly, to find all students who do have an email, you would use IS NOT NULL.

   Providing Default Values with COALESCE

   When you're creating a report, showing a blank space or NULL for missing data can be confusing. The COALESCE function lets you provide a fallback value. It takes a list of arguments and returns the first one that is not NULL.

   • To display 'No Email Provided' for any student with a NULL email:

     SELECT first_name, last_name, COALESCE(email, 'No Email Provided') FROM students;
     

   You can also give this new, computed column a more descriptive name using an alias with the AS keyword:

   SELECT first_name, last_name, COALESCE(email, 'No Email Provided') AS contact_info FROM students;
   

   Let's practice cleaning up our student data.

5. Challenge

   Step 5: Add Conditional Logic with CASE Statements

   Step 5: Add Conditional Logic with CASE Statements

   As a data analyst, you often need to derive new information or categorize data based on existing values. SQL's CASE statement is a powerful tool for adding conditional, if-then-else logic directly into your queries.

   Basic CASE Syntax

   The CASE statement goes in your SELECT list. It evaluates a series of conditions and returns a value when the first condition is met. It must end with an END keyword, and it's good practice to give it an alias with AS.

   CASE
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ELSE else_result
   END AS new_column_name
   

   Example: Categorizing Courses

   Let's say you want to categorize courses by their credit hours. A course with 4 credits could be a 'Major Course', while a 3-credit course is an 'Elective'.

   SELECT
       course,
       credit_hours,
       CASE
           WHEN credit_hours >= 4 THEN 'Major Course'
           ELSE 'Elective'
       END AS course_type
   FROM students;
   

   You can include multiple WHEN clauses to handle more complex logic. The ELSE clause is optional, but without it, any rows that don't meet a WHEN condition will have a NULL value in the new column.

   Now, let's create some custom categories for our student data.

6. Challenge

   Step 6: Organize Output with ORDER BY, LIMIT, and OFFSET

   Step 6: Organize Output with ORDER BY, LIMIT, and OFFSET

   In this final step, you'll learn how to control the presentation of your query results. A well-organized report is much more useful than a random dump of data. You'll use ORDER BY to sort records, LIMIT to get the top results, and OFFSET to implement pagination.

   Sorting with ORDER BY

   The ORDER BY clause sorts the rows in your result set. By default, it sorts in ascending order (ASC). You can specify descending order with DESC.

   • To sort students by last name alphabetically:

     SELECT first_name, last_name FROM students ORDER BY last_name ASC;
     

   • To find the most recently enrolled students:

     SELECT * FROM students ORDER BY enrollment_date DESC;
     

   You can also sort by multiple columns. The database will sort by the first column, and then use the second column to sort any rows that had the same value in the first column.

   -- Sort by term, and then by last name within each term
   SELECT * FROM students ORDER BY term, last_name;
   

   Pagination with LIMIT and OFFSET

   When working with thousands of records, you rarely want to see them all at once. LIMIT restricts the number of rows returned, while OFFSET tells the database how many rows to skip before starting to count.

   • To get the top 5 most recently enrolled students:

     SELECT * FROM students ORDER BY enrollment_date DESC LIMIT 5;
     

   • To get the next 5 (i.e., page 2), you add an OFFSET:

     -- Skip the first 5, then take the next 5
     SELECT * FROM students ORDER BY enrollment_date DESC LIMIT 5 OFFSET 5;
     

   Let's apply these clauses to create some final, polished reports.