Relational Database Design: Primary & Foreign Keys

While designing relational databases with primary and foreign keys establishes the structural blueprint of your data, retrieving and filtering that data efficiently is what powers real-world applications. This chapter focuses on the practical execution of SQL queries to sort, restrict, and perform pattern-based searches on your dataset.

1. The Need for Data Control: Sorting, Limiting, and Searching

When querying a relational database table (such as a course_enrollments table), the database engine returns records in an arbitrary order by default. In production environments containing thousands or millions of rows, returning all records unordered causes significant performance bottlenecks and a poor user experience.

To build functional application features—such as search bars, paginated lists, or leaderboard rankings—we must be able to:

• Sort data based on specific criteria (e.g., showing the most expensive courses first).
• Restrict payload sizes to prevent system memory overload.
• Perform pattern matching to find records matching partial user inputs.

2. Sorting Data with ORDER BY

The ORDER BY clause allows you to sort the result set of a query by one or more columns in ascending (ASC) or descending (DESC) order.

Real-Life Analogy

Think of sorting like organizing a stack of exam papers. You can arrange them alphabetically by student name (A to Z) or numerically by score (highest to lowest).

Syntax and Execution

To sort course enrollments by price in descending order (highest price first):

SELECT student_name, price 
FROM course_enrollments 
ORDER BY price DESC;

Execution Flow

Key Behaviors

• Descending Order (DESC): Places the largest values (or latest dates/alphabetically last strings) at the top. For example, a course priced at $399 will appear before a course priced at $99.
• Alphabetical Sorting: If you sort by a text field like student_name without specifying an order, PostgreSQL defaults to ascending order (ASC), sorting names from A to Z.

3. Restricting Results with LIMIT

The LIMIT clause specifies the maximum number of rows that the database should return. This is crucial for performance optimization and implementing pagination.

Syntax

SELECT student_name, price 
FROM course_enrollments 
ORDER BY price DESC 
LIMIT 5;

This query sorts the records by price in descending order and returns only the top 5 most expensive enrollments.

Note: Always use LIMIT in conjunction with ORDER BY if you need a deterministic result (e.g., "the top 5 highest"). Using LIMIT without ORDER BY returns an arbitrary set of rows depending on how the database engine scans the disk.

4. Pattern Matching with the LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. This is the foundational mechanism behind basic search bar functionalities in web applications.

The % (Percentage) Wildcard

The % wildcard represents zero, one, or multiple characters.

1. Finding Prefixes (Starts With)

To find all students whose names start with the capital letter 'A':

SELECT * 
FROM course_enrollments 
WHERE student_name LIKE 'A%';

Output:

• Amit
• Anita

2. Finding Suffixes (Ends With)

To find all students whose names end with a specific sequence of characters, place the % wildcard at the beginning of the pattern. For example, to find names ending in 'sing' or 'singh':

SELECT * 
FROM course_enrollments 
WHERE student_name LIKE '%sing';

Output:

• Priya Singh
• Vikram Singh

5. Case Sensitivity in Pattern Matching

By default, the LIKE operator in PostgreSQL is case-sensitive. This is a common source of bugs during development.

Case Sensitivity Demonstration

• Query 1 (Capital 'A'):
  SQL

  Copy

  SELECT * FROM course_enrollments WHERE student_name LIKE 'A%';
  -- Returns: Amit, Anita
  

• Query 2 (Lowercase 'a'):
  SQL

  Copy

  SELECT * FROM course_enrollments WHERE student_name LIKE 'a%';
  -- Returns: (0 rows)
  

Note: If you want to perform a case-insensitive search in PostgreSQL, use the ILIKE operator instead of LIKE.

SQL

Copy

SELECT * FROM course_enrollments WHERE student_name ILIKE 'a%';
-- Returns: Amit, Anita

6. Single-Character Wildcard (_) and Exact Length Matching

The underscore (_) wildcard represents exactly one single character. This is useful when you know the exact length of the string or want to match patterns with variable characters at specific positions.

Matching Exact String Lengths

To find names that have an exact length of 5 characters and end with the letter 'a', you use four underscores followed by the letter 'a':

SELECT * 
FROM course_enrollments 
WHERE student_name LIKE '____a';

How the Database Evaluates ____a

Position:  1   2   3   4   5
Pattern:   _   _   _   _   a
           |   |   |   |   |
Matches:  Any Any Any Any Must be 'a'

If your database contains no names that are exactly 5 characters long and end with 'a', this query will return an empty result set.

7. Tabular Synthesis of Wildcards

8. Complete Query Execution Flow

The following diagram illustrates how PostgreSQL processes a complex query containing filtering, pattern matching, sorting, and limiting:

Summary and Key Takeaways

• ORDER BY is used to sort query results. Use DESC for descending order (e.g., highest numerical values or reverse alphabetical order) and ASC (default) for ascending order.
• LIMIT restricts the number of rows returned by a query, which is essential for performance and pagination workflows.
• LIKE enables pattern matching using wildcards.
• % (Percent sign) matches zero or more characters of any type.
• _ (Underscore) matches exactly one single character.
• Case Sensitivity is strictly enforced by PostgreSQL's LIKE operator. Use ILIKE for case-insensitive pattern matching.
• Combining Clauses in the correct order (WHERE -> ORDER BY -> LIMIT) allows you to build highly optimized, deterministic search and retrieval queries.