Conditional Logic and Query Control in SQL: Constraints, Filtering, and Sorting

This chapter explores the foundational mechanisms used to enforce data integrity and control query outputs in PostgreSQL. We will examine how database constraints (such as CHECK and DEFAULT) act as gatekeepers during data entry, and how query clauses (WHERE, DISTINCT, and ORDER BY) allow developers to filter, deduplicate, and sort retrieved datasets.

1. Introduction to SQL Constraints: The CHECK Constraint

Database constraints are rules applied to table columns to prevent invalid data from being written to the database. They act as the first line of defense for data integrity at the storage layer, ensuring that any data inserted or updated conforms to strict business rules.

The CHECK constraint is a conditional validation rule. When a write operation (INSERT or UPDATE) occurs, PostgreSQL evaluates the CHECK expression. If the expression evaluates to TRUE or NULL, the operation succeeds. If it evaluates to FALSE, the database rejects the transaction and throws an error.

Real-Life Analogy

Think of a CHECK constraint as a security guard at an amusement park ride checking if a passenger meets the minimum height requirement. If you are not tall enough, you are not allowed on the ride. Similarly, if a data row does not satisfy the CHECK condition, it is blocked from entering the table.

2. Real-World Applications of CHECK Constraints

In production databases, CHECK constraints are commonly used to validate ranges, enforce non-negative values, and restrict inputs to specific business domains.

Common Use Cases

• Rating Boundaries: Ensuring user-submitted ratings fall within a standard scale (e.g., 1 to 5 stars).
• Age Verification: Restricting registration to users who meet a minimum age requirement (e.g., 18+).
• Financial Guardrails: Preventing products from being listed with negative prices or zero values.

SQL Implementation: Creating a Table with CHECK Constraints

Below is a realistic SQL schema demonstrating how to declare multiple CHECK constraints during table creation:

CREATE TABLE course_enrollment (
    enrollment_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    student_email VARCHAR(150) UNIQUE NOT NULL,
    course_name VARCHAR(150) NOT NULL,
    level VARCHAR(50) NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0), -- Price must be greater than zero
    rating INT CHECK (rating >= 1 AND rating <= 5), -- Rating must be between 1 and 5
    age INT CHECK (age >= 18) -- Age must be 18 or older
);

Note: While application-level validation (e.g., in Node.js, Python, or Java) is important for user feedback, database-level CHECK constraints are critical. They guarantee data integrity even if a bug bypasses the application layer or if a developer modifies the database directly via a CLI tool.

3. The DEFAULT Constraint: Automating Value Assignment

The DEFAULT constraint is used to automatically assign a fallback value to a column when an INSERT statement does not explicitly provide one. This simplifies application logic and ensures that optional fields are populated with sensible starting values.

Common Use Cases

• Timestamps: Automatically recording the exact date and time a record was created.
• Status Flags: Setting a default state for new records (e.g., setting a user account status to 'active' or a course level to 'Beginner').

SQL Implementation: Applying DEFAULT Constraints

CREATE TABLE user_profiles (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    account_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Automatically captures system time
    membership_status VARCHAR(20) DEFAULT 'free' -- Defaults to 'free' if not specified
);

4. Row-Level Filtering with the WHERE Clause

Once data is safely stored in the database, we use the WHERE clause to filter the rows returned by a SELECT statement. The WHERE clause evaluates a boolean condition for each row in the table, returning only the rows where the condition evaluates to TRUE.

Supported Operators in the WHERE Clause

To build flexible filtering logic, SQL supports several operators:

SQL Implementation: Filtering by Level

Consider a scenario where we want to retrieve only the courses tailored for beginners:

SELECT 
    student_name, 
    course_name, 
    level, 
    price 
FROM 
    course_enrollment 
WHERE 
    level = 'Beginner';

5. Step-by-Step Execution of a WHERE Query

To understand how PostgreSQL processes a filtered query, let us trace the execution flow of the query from Section 4.

Execution Flow

Sample Query Output

Assuming our database contains the records discussed in the lecture, the output of our filtered query would look like this:

Note on Pricing: As noted in the lecture, these prices are assumed to be in USD, as high-quality technical courses are valued globally and cannot realistically be sold for nominal amounts.

6. Eliminating Duplicates using the DISTINCT Clause

In a relational database, queries often return duplicate values when selecting specific columns. The DISTINCT clause filters out these duplicate rows, returning only unique values. It behaves similarly to a mathematical Set, where every element must be unique.

SQL Implementation: Fetching Unique Course Offerings

If we want to see a list of all unique courses currently offered in our enrollment table without seeing duplicate entries for popular courses, we use:

SELECT DISTINCT course_name FROM course_enrollment;

Sample Output of Unique Courses

Running the query above returns a clean, deduplicated list of our catalog:

• Docker Essentials
• Full Stack React
• Node.js API
• Python for AI
• TypeScript Pro
• AI Integration
• Cloudflare Workers
• Next.js Advanced
• SQL
• React Native Basics

7. Sorting Datasets with the ORDER BY Clause

By default, relational databases do not guarantee the order of rows returned by a SELECT query. To present data in a meaningful sequence, we must use the ORDER BY clause.

Data can be sorted in two directions:

• Ascending (ASC): Sorts data from smallest to largest (e.g., 1 to 10, A to Z, oldest date to newest date). This is the default sorting behavior in PostgreSQL.
• Descending (DESC): Sorts data from largest to smallest (e.g., 10 to 1, Z to A, newest date to oldest date).

SQL Implementation: Sorting by Price and Rating

-- Sort courses from cheapest to most expensive (Ascending)
SELECT course_name, price 
FROM course_enrollment 
ORDER BY price ASC;

-- Sort courses from highest rated to lowest rated (Descending)
SELECT course_name, rating 
FROM course_enrollment 
ORDER BY rating DESC;

8. Summary and Key Takeaways

• CHECK Constraints act as database-level validation rules, ensuring that values (such as ratings, ages, or prices) conform to specific business logic before they are written to disk.
• DEFAULT Constraints automate data entry by automatically assigning fallback values (like the current timestamp or a default status) when a column is omitted during an insert.
• The WHERE Clause filters rows returned by a query based on conditional expressions, using comparison and logical operators.
• The DISTINCT Clause eliminates duplicate rows from a query result set, behaving like a mathematical set to return only unique values.
• The ORDER BY Clause provides control over the presentation order of your data, allowing you to sort records in either Ascending (ASC) or Descending (DESC) order.
• Layered Validation is a best practice; while application-level checks provide a smooth user experience, database constraints provide an absolute guarantee of data integrity.