Core SQL Commands: DDL, DML, and DCL

Comprehensive study notes covering advanced SQL operations, pattern matching, data pagination, aggregation, complex group filtering, the strict SQL execution lifecycle, and multi-table relational joins.

1. Introduction to Selection and Filtering (SELECT)

The SELECT statement is the cornerstone of Structured Query Language (SQL), used to extract and retrieve data from database tables. By tailoring projection and filtering clauses, developers control precisely which columns and rows are returned.

Basic Projection Examples

• Selecting All Columns: Using the asterisk wildcard (*) fetches every column present in the specified table.
  SQL

  Copy

  SELECT * FROM student;
  

• Projecting Specific Columns: To optimize performance and network bandwidth, select only the columns required by the application.
  SQL

  Copy

  SELECT name, email FROM student;
  

Conditional Filtering (WHERE)

Queries frequently require conditional logic to narrow down result sets using comparison operators (=, !=, <, >, <=, >=).

SELECT * FROM student WHERE age >= 15;

2. Advanced Pattern Matching with LIKE

When exact string matching is insufficient, the LIKE operator enables wildcard searches using special placeholder symbols:

• Percentage symbol (%): Represents zero, one, or multiple arbitrary characters.
• Underscore symbol (_): Represents exactly one single character.

Pattern Examples

• Starts with 'R': WHERE name LIKE 'R%'
• Ends with 'A': WHERE name LIKE '%A'
• Second character is 'R': WHERE name LIKE '_R%' (where the underscore matches exactly one character at the first position, followed by 'R', followed by any sequence of trailing characters).

3. Sorting, Ordering, and Pagination

Database results can be sorted and chunked to support user interfaces and infinite scrolling or pagination.

Sorting Records (ORDER BY)

• Ascending Order (Default): Explicitly or implicitly sorts data from lowest to highest.
  SQL

  Copy

  SELECT * FROM student ORDER BY age ASC;
  

• Descending Order: Sorts data from highest to lowest.
  SQL

  Copy

  SELECT * FROM student ORDER BY age DESC;
  

Pagination with LIMIT and OFFSET

Pagination splits large datasets into manageable chunks for UI display.

• LIMIT: Restricts the total number of records returned.
• OFFSET: Specifies the number of initial records to skip before starting to return rows.

4. Aggregation Functions

Aggregation functions perform mathematical and statistical calculations across multiple rows of data, condensing a large set of values into a single summary result.

• AVG(column): Calculates the mathematical average of numeric column values.
• MAX(column): Finds the highest value in a column.
• MIN(column): Finds the lowest value in a column.
• SUM(column): Computes the total sum of numeric values.
• COUNT(*) or COUNT(column): Counts the total number of matching rows.

SELECT AVG(age), MAX(age), MIN(age) FROM student;

5. Grouping Data: GROUP BY vs. HAVING vs. WHERE

When analyzing categorized data, records must be grouped together before running aggregate functions. Understanding the execution boundaries of WHERE and HAVING is essential.

The Trio of Conditional Filtering

1. WHERE: Filters individual rows before any grouping or aggregation takes place. It operates on raw table data.
2. GROUP BY: Organizes the filtered rows into distinct groups based on one or more column values (e.g., grouping students by department_id).
3. HAVING: Filters the aggregated groups after grouping and aggregation have occurred.

Comparative Distinction

Code Example: Grouping and Filtering

SELECT department_id, COUNT(*) AS student_count
FROM student
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) > 5;

6. The Strict SQL Query Execution Order

Databases do not execute SQL queries in the exact textual order in which they are written. Understanding the internal processing engine order is critical for writing valid queries and avoiding syntax errors.

Logical Processing Pipeline

Note: Because the SELECT clause evaluates near the end of the pipeline (Step 5), you cannot use a SELECT column alias inside a WHERE clause (Step 2). The database engine has not yet generated the alias when WHERE runs.

7. Multi-Table Relational Joins

Joins combine columns from two or more related tables based on a logical relationship between them (such as a foreign key matching a primary key).

Core Join Types

• INNER JOIN: Returns only the records that have matching values in both tables. Unmatched rows are excluded.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If no match exists on the right side, result columns for the right table contain NULL.
• RIGHT JOIN: Returns all records from the right table, with matching records from the left table (NULL filled if unmatched).
• CROSS JOIN: Produces a Cartesian product, pairing every row of the first table with every row of the second table.

Relational Schema Concept

Consider two tables:

• department: Stores department_id (Primary Key) and department_name.
• employee: Stores employee_id, employee_name, and department_id (Foreign Key referencing department).

To fetch employee names alongside their corresponding department names, combine the tables using an INNER JOIN:

SELECT e.employee_name, d.department_name
FROM employee AS e
INNER JOIN department AS d
  ON e.department_id = d.department_id;

Visualizing Join Mechanics (Venn Model)

• Inner Join: Intersecting center region where both tables overlap.
• Left Join: Entire left circle plus the overlapping intersection region.

Summary and Key Takeaways

• Projection & Filtering: Use SELECT to choose specific columns and WHERE to restrict rows based on conditional expressions.
• Pattern Wildcards: The LIKE operator utilizes % for multi-character sequences and _ for exact single-character matching.
• Pagination Control: Combine ORDER BY with LIMIT and OFFSET to implement efficient data pagination for UI applications.
• Aggregation: Condense multi-row datasets into summary metrics using aggregate functions like AVG, SUM, MIN, MAX, and COUNT.
• Grouping Rules: WHERE filters individual rows before grouping, while HAVING filters aggregate groups after grouping.
• Execution Pipeline: Queries execute starting from FROM, followed by WHERE, GROUP BY, HAVING, and finally evaluating SELECT and ORDER BY.
• Alias Constraint: Column aliases defined in SELECT cannot be referenced inside WHERE clauses due to the order of query execution.
• Relational Joins: Combine data across normalized tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN based on foreign key relationships.