Pagination and Sorting

To retrieve a specific range of records, such as skipping the first 20 entries and fetching the subsequent 10, the LIMIT clause with an offset is utilized:

SELECT * FROM articles LIMIT 20, 10;

Alternatively, the explicit offset syntax can be used:

SELECT * FROM articles LIMIT 10 OFFSET 20;

Filtering and ordering results descendingly based on a specific column, for instance, fetching records where category_id and status are both 1, ordered by sort_order:

SELECT * FROM articles WHERE category_id = 1 AND status = 1 ORDER BY sort_order DESC;

Applying pagination to an already sorted and filtered subset requires a derived table:

SELECT * FROM (
  SELECT * FROM articles WHERE category_id = 1 AND status = 1 ORDER BY sort_order DESC
) AS sorted_items LIMIT 10 OFFSET 20;

Date Data Types

The DATE type captures year, month, and day. Insertions can be performed using the CURRENT_DATE function or by supplying a formatted string like 'YYYY-MM-DD':

CREATE TABLE schedule_log (event_date DATE);
INSERT INTO schedule_log VALUES (CURRENT_DATE);
INSERT INTO schedule_log VALUES ('2023-10-15');
SELECT * FROM schedule_log;

Left Outer Joins

A left outer join combines two tables, ensuring all rows from the left table are preserved even if matching rows are absent in the right table. This mechanism is instrumental for expanding datasets or tracing referencing records, such as pinpointing which entries in table A reference a particular tuple in table B.

Normalization vs. Denormalization

Database normalization minimizes redundancy. The First Normal Form (1NF) demands atomic, indivisible attributes; employing set-valued attributes induces storage redundancy and inconsistency. Boyce-Codd Normal Form (BCNF) eradicates all redundancies attributable to functional dependencies.

Conversely, deliberate denormalization introduces data redundancy to diminish computationally expensive join operations, effectively trading storage capacity for query execution speed.

Aggregation with Joins

When computing aggregates like average scores across joined tables, column names shared by both tables must be explicitly qualified in the GROUP BY clause. The following retrieves students with an average mark exceeding 90:

CREATE TABLE pupils (pupil_id INT, pupil_name VARCHAR(50));
CREATE TABLE course_scores (pupil_id INT, marks INT, subject_name VARCHAR(50));

INSERT INTO pupils VALUES (101, 'Alice'), (102, 'Bob');
INSERT INTO course_scores VALUES (101, 88, 'Math'), (101, 95, 'Science');
INSERT INTO course_scores VALUES (102, 85, 'Math'), (102, 80, 'Science');

SELECT p.pupil_id, p.pupil_name, AVG(c.marks) AS avg_marks
FROM pupils p
INNER JOIN course_scores c ON p.pupil_id = c.pupil_id
GROUP BY p.pupil_id
HAVING AVG(c.marks) > 90;

Result:

+----------+-------------+------------+
| pupil_id | pupil_name  | avg_marks  |
+----------+-------------+------------+
|      101 | Alice       |    91.5000 |
+----------+-------------+------------+

Primary Key Uniqueness

Primary keys guarantee uniqueness because they are inherently backed by an index, commonly a B+ tree. Upon insertion, the database engine navigates this tree structure; encountering an identical key value blocks the insertion, preserving integrity.

Conditional Subqueries

To isolate individuals where every associated record satisfies a condition—such as students whose marks are uniformly above 60—leverage a subquery filtering by the minimum aggregate value:

CREATE TABLE exam_results (student_alias VARCHAR(40), discipline VARCHAR(50), score INT);
INSERT INTO exam_results VALUES ('Alpha', 'Literature', 65), ('Alpha', 'Algebra', 55);
INSERT INTO exam_results VALUES ('Beta', 'Literature', 85), ('Beta', 'Algebra', 70);
INSERT INTO exam_results VALUES ('Gamma', 'Literature', 75), ('Gamma', 'Algebra', 80), ('Gamma', 'Physics', 90);

SELECT student_alias, score
FROM exam_results
WHERE student_alias IN (
    SELECT student_alias
    FROM exam_results
    GROUP BY student_alias
    HAVING MIN(score) > 60
);