PostgreSQL Data Manipulation and Query Formulation Study Guide

PostgreSQL Implementation Environment and Learning Methodology

  • Database Engine Specifications:

    • All SQL queries and syntax rules are written specifically for the PostgreSQL database management system.
    • String Literal Delimiters: String literals must be enclosed in straight single quotation marks (e.g., 'London'). Double quotes designate database identifiers such as table and column names.
    • NULL Value Substitution: Standard SQL function COALESCE(expr, 0) is used instead of non-standard vendor alternatives like IFNULL().
    • Native Relational Set Operators: PostgreSQL provides direct native engine support for FULL OUTER JOIN, INTERSECT, and EXCEPT.
    • Unsupported SQL Standard Clauses: PostgreSQL does not implement the SQL standard CORRESPONDING or CORRESPONDING BY clauses for set operations. Target columns across union-compatible queries must be explicitly specified in identical projection order.
    • Prohibition on Nested Aggregates: Nested aggregate function calls, such as MAX(AVG(x)), are strictly disallowed and trigger execution errors. Multi-level aggregations must be implemented using derived tables, subqueries, or Common Table Expressions (CTEs).
    • Diagnostic Messages: All system error strings and syntax warnings reflect standard PostgreSQL server diagnostic outputs.
  • Pedagogical Framework:

    • Active Inquiry over Slide Memorization: Mastery of relational data manipulation requires active problem formulation, semantic decomposition, and iterative validation rather than passive slide review.
    • The Six-Step Problem-Solving Cycle:
    • Question: Define the business or domain question precisely.
    • Predict: Deduce the expected schema, number of rows, column projections, and edge-case behaviors prior to database execution.
    • Write: Construct clean, standard PostgreSQL queries adhering to declarative best practices.
    • Run: Execute the query against the target database engine.
    • Explain: Deconstruct how PostgreSQL's relational engine parsed, filtered, and aggregated the data to produce the output.
    • Modify: Adjust constraints, projections, or operations to evaluate edge-case behaviors and performance.
    • The Cardinal Class Rule: Never execute an SQL query before forming an explicit prediction regarding its result set.

Reference Schema and Core Relations

Staff and PropertyForRent tables schema and sample data

  • Staff Relation Schema and Instance Data:

    • staffNo (Primary Key): Unique alphanumeric identifier for each staff member.
    • fName: Employee given name.
    • lName: Employee surname.
    • position: Job title or organizational role.
    • sex: Biological sex indicator ('M', 'F').
    • DOB: Date of birth formatted as DD-Mon-YY.
    • salary: Numeric monthly compensation.
    • branchNo: Foreign key referencing the branch office to which the staff member is assigned.
    • Instance Tuples:
    • ('SL21', 'John', 'White', 'Manager', 'M', '1-Oct-45', 30000.00, 'B005')
    • ('SG37', 'Ann', 'Beech', 'Assistant', 'F', '10-Nov-60', 12000.00, 'B003')
    • ('SG14', 'David', 'Ford', 'Supervisor', 'M', '24-Mar-58', 18000.00, 'B003')
    • ('SA9', 'Mary', 'Howe', 'Assistant', 'F', '19-Feb-70', 9000.00, 'B007')
    • ('SG5', 'Susan', 'Brand', 'Manager', 'F', '3-Jun-40', 24000.00, 'B003')
    • ('SL41', 'Julie', 'Lee', 'Assistant', 'F', '13-Jun-65', 9000.00, 'B005')
  • PropertyForRent Relation Schema and Instance Data:

    • propertyNo (Primary Key): Unique alphanumeric property identifier.
    • street: Local street address.
    • city: City where property is located.
    • postcode: Postal code.
    • type: Property classification ('House', 'Flat').
    • rooms: Total number of rooms.
    • rent: Monthly rent charge.
    • ownerNo: Foreign key linking to the PrivateOwner relation.
    • staffNo: Foreign key linking to the Staff member managing the property (nullable).
    • branchNo: Foreign key linking to the managing Branch.
    • Instance Tuples:
    • ('PA14', '16 Holhead', 'Aberdeen', 'AB7 5SU', 'House', 6, 650, 'CO46', 'SA9', 'B007')
    • ('PL94', '6 Argyll St', 'London', 'NW2', 'Flat', 4, 400, 'CO87', 'SL41', 'B005')
    • ('PG4', '6 Lawrence St', 'Glasgow', 'G11 9QX', 'Flat', 3, 350, 'CO40', NULL, 'B003')
    • ('PG36', '2 Manor Rd', 'Glasgow', 'G32 4QX', 'Flat', 3, 375, 'CO93', 'SG37', 'B003')
    • ('PG21', '18 Dale Rd', 'Glasgow', 'G12', 'House', 5, 600, 'CO87', 'SG37', 'B003')
    • ('PG16', '5 Novar Dr', 'Glasgow', 'G12 9AX', 'Flat', 4, 450, 'CO93', 'SG14', 'B003')
  • Viewing Relation Schema and Instance Data:

    • Represents client appointment viewings of rental properties.
    • Schema: clientNo, propertyNo, viewDate, comment.
    • Instance Tuples:
    • ('CR56', 'PA14', '24-May-13', 'too small')
    • ('CR76', 'PG4', '20-Apr-13', 'too remote')
    • ('CR56', 'PG4', '26-May-13', NULL)
    • ('CR62', 'PA14', '14-May-13', 'no dining room')
    • ('CR56', 'PG36', '28-Apr-13', NULL)

Viewing relation sample table

The SELECT Query Pipeline, Filtering, Sorting, and NULL Semantics

  • Logical Execution Pipeline:

    • SQL clauses do not execute in lexical written order. The internal processing sequence is:
    1. FROM: Identifies the source relation(s) or Cartesian products to establish the base dataset.
    2. WHERE: Filters individual source rows according to boolean predicate conditions.
    3. GROUP BY: Partitions qualifying rows into distinct groups based on common attribute values.
    4. HAVING: Filters aggregated groups based on group-level summary predicates.
    5. SELECT: Evaluates expressions, handles column aliasing, and projects the requested attributes.
    6. ORDER BY: Sorts the final result rows for presentation.
    • Cardinality Alteration vs. Presentation Formatting:
    • Clauses changing result set cardinality (row count): FROM (via joins/cross-products), WHERE (removes non-qualifying tuples), GROUP BY (collapses rows into groups), and HAVING (removes non-qualifying groups).
    • Clauses altering presentation only: SELECT (restricts or transforms column attributes without adding or removing rows) and ORDER BY (re-arranges row display order without altering row identity or count).
  • Row Filtering via the WHERE Clause:

    • Predicates evaluate to TRUE, FALSE, or UNKNOWN.
    • Compound Predicate Example:sql SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 8000 AND position = 'Manager' AND branchNo = 'B003';     
    • Deductive Row Evaluation:
    • John White (SL21): salary = 30000 > 8000 (TRUE), position = 'Manager' (TRUE), but branchNo = 'B005' (FALSE). Fails overall predicate.
    • Susan Brand (SG5): salary = 24000 > 8000 (TRUE), position = 'Manager' (TRUE), and branchNo = 'B003' (TRUE). Survives and is returned.
    • Result: Exactly 1 row (SG5).
  • Three-Valued Logic and NULL Value Handling:

    • NULL signifies missing, unknown, or inapplicable information; it is never equal to any value, including another NULL.
    • Standard equality operators (= NULL or <> NULL) evaluate to UNKNOWN. In SQL boolean contexts (such as WHERE and HAVING), only expressions evaluating to TRUE are retained; rows evaluating to UNKNOWN are dropped.
    • Syntax Error Analysis: ```sql -- Buggy query: produces 0 rows because comment_ = NULL evaluates to UNKNOWN SELECT * FROM Viewing WHERE comment_ = NULL;

    -- Correct query using proper unary null-test operator: SELECT clientNo, viewDate FROM Viewing WHERE propertyNo = 'PG4' AND comment_ IS NULL;     ```

    • Non-null tests must explicitly invoke the IS NOT NULL syntax.
  • Result Set Sorting (ORDER BY):

    • Default sort direction is ascending (ASC). Descending order requires explicit DESC designation.
    • Multiple expressions establish hierarchical primary, secondary, and tertiary sorting keys: sql SELECT propertyNo, type, rooms, rent FROM PropertyForRent ORDER BY type, rent DESC; &nbsp;&nbsp;&nbsp;&nbsp;
    • Determinism and Secondary Sort Keys:
    • If four properties possess identical type values and no secondary sort key is provided, relational engines provide no deterministic ordering guarantee among them.
    • Supplying a secondary sort key like rent DESC ensures deterministic ranking, displaying the most expensive property of that type first.

Range Filtering, Set Membership, and Pattern Matching

  • Range Search Conditions (BETWEEN … AND):
    • Evaluates whether an attribute falls within a closed numeric, lexicographic, or chronological interval.
    • The endpoints are inclusive: sql SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000; &nbsp;&nbsp;&nbsp;&nbsp;
    • Equivalence: salary >= 20000 AND salary <= 30000.
    • Matching rows: John White (30000.00) and Susan Brand (24000.00).

Staff salary range search result

  • Set Membership (IN and NOT IN):
    • Tests whether an attribute value equals any member in a specified literal set or single-column subquery: sql SELECT staffNo, fName, lName, position FROM Staff WHERE position IN ('Manager', 'Supervisor'); &nbsp;&nbsp;&nbsp;&nbsp;
    • Logical equivalence: position = 'Manager' OR position = 'Supervisor'.
    • Matching rows: John White (Manager), David Ford (Supervisor), Susan Brand (Manager).
    • Expressive characteristics: While IN adds no formal theoretical expressive power over chained OR comparisons, it improves query readability and allows query planners to execute optimized index lookups against larger sets.
    • NOT IN serves as the negated quantifier, equivalent to position <> 'Manager' AND position <> 'Supervisor'.

Staff position set membership result

  • Pattern Matching (LIKE):
    • Evaluates strings against wildcard search patterns:
    • % (percent): Matches any string of zero or more arbitrary characters.
    • _ (underscore): Matches exactly one arbitrary character.
    • Substring Search Query: sql SELECT ownerNo, fName, lName, address, telNo FROM PrivateOwner WHERE address LIKE '%Glasgow%'; &nbsp;&nbsp;&nbsp;&nbsp;
    • Matching rows in PrivateOwner:
    • CO87: Carol Farrel (6 Achray St, Glasgow G32 9DX)
    • CO40: Tina Murphy (63 Well St, Glasgow G42)
    • CO93: Tony Shaw (12 Park Pl, Glasgow G4 0QR)

PrivateOwner address pattern matching result

SQL Aggregate Functions and GROUP BY / HAVING Semantics

  • Standard Aggregate Functions:

    • COUNT: Quantifies row occurrences or non-null attribute instances.
    • SUM: Computes arithmetic totals over numeric attributes.
    • AVG: Computes the arithmetic mean over numeric attributes.
    • MIN: Returns the minimum value across numeric, string, or date attributes.
    • MAX: Returns the maximum value across numeric, string, or date attributes.
  • Aggregate Processing Rules and NULL Behaviors:

    • Aggregates operate on a column of values and compress the input to a single scalar result.

    • COUNT(*), MIN(), and MAX() operate across numeric, string, and chronological types.

    • SUM() and AVG() are strictly restricted to numeric types.

    • Treatment of Nulls:

    • COUNT(*) counts all qualifying tuples, unconditionally including rows containing NULL values and duplicate entries.

    • All other aggregate functions (COUNT(column), SUM(column), AVG(column), MIN(column), MAX(column)) automatically eliminate NULL values before evaluating the aggregate expression.

    • DISTINCT Modifier:

    • COUNT(DISTINCT column) computes the count of unique, non-null values.

    • DISTINCT modifies calculation results for SUM() and AVG(), but has zero effect on MIN() or MAX().

    • Mitigating Null Exclusion in Arithmetic Means:

    • Standard AVG(commission_pct) calculates the average exclusively over employees who received a non-null commission, producing an elevated conditional mean.

    • To compute the true organizational mean where non-commissioned staff count as zero, COALESCE must be explicitly embedded: ```sql -- Average over staff with non-null commissions SELECT AVG(commission_pct) FROM employees;

      -- Average over all staff members, converting NULL to 0 SELECT AVG(COALESCE(commission_pct, 0)) FROM employees;       ```

  • Grouping Semantics (GROUP BY):

    • Partitions the intermediate table rows into distinct subgroups sharing identical values across all grouping columns.
    • Aggregate functions are evaluated separately for each subgroup.
    • The Single-Value Rule: Any column appearing in the SELECT list that is not wrapped in an aggregate function must be included in the GROUP BY clause. Omitting a un-aggregated projection column produces a syntax error because the engine cannot determine which row's value to represent for the group.
    • Debugging Invalid Queries: ```sql -- Syntax Error: branchNo is unaggregated and missing from GROUP BY SELECT branchNo, COUNT(*) FROM Staff;

    -- Correct Query: SELECT branchNo, COUNT(*) AS staff_count, SUM(salary) AS total_salary FROM Staff GROUP BY branchNo ORDER BY branchNo;     ```

  • Group Filtering (HAVING vs. WHERE):

    • WHERE filters individual base rows before any grouping or aggregate computation takes place.
    • HAVING filters generated groups after the GROUP BY clause has partitioned the data and evaluated the group aggregates.
    • Aggregates cannot appear in WHERE clauses (e.g., WHERE COUNT(*) > 1 is invalid SQL).
    • Pipeline Integration Query:sql SELECT branchNo, AVG(salary) AS avg_salary FROM Staff WHERE salary IS NOT NULL GROUP BY branchNo HAVING AVG(salary) > 8000 ORDER BY avg_salary DESC; &nbsp;&nbsp;&nbsp;&nbsp;
    • Class Exit Challenge Query: Identify branches with more than 2 staff members and compute their total compensation: sql SELECT branchNo, SUM(salary) AS total_salary FROM Staff WHERE salary IS NOT NULL GROUP BY branchNo HAVING COUNT(*) > 2; &nbsp;&nbsp;&nbsp;&nbsp;
    • Execution trace on Staff instance: Branch B003 has 3 employees (SG37, SG14, SG5); branch B005 has 2 employees (SL21, SL41); branch B007 has 1 employee (SA9). Only B003 satisfies COUNT(*) > 2, yielding 1 row with total_salary = 12000 + 18000 + 24000 = 54000.

Relational Joins and Multi-Table Query Formulation

  • Theoretical Join Classifications:

Join concepts comparison table

  • Theta Join: The general join category matching tuples across relations using any valid comparison predicate (=,<,<=,>,>=,<>=, <, <=, >, >=, <>).

  • Equi Join: A specialized subset of theta join where the comparison predicate consists solely of equality operators (==).

  • Inner Join: A join operation that preserves only those tuple combinations satisfying the join condition; non-matching tuples from either relation are discarded. An SQL INNER JOIN can implement either a general theta join or an equi join depending on its ON expression.

  • Natural Join: A relational operator that automatically performs an equi join over all attributes sharing identical column names across two relations, removing duplicate matching columns from the projection.

    • INNER JOIN Processing:
  • Combines rows from two tables where the ON condition evaluates to TRUE.

  • Basic two-table join: sql SELECT b.branchNo, b.city, s.staffNo FROM Branch b JOIN Staff s ON b.branchNo = s.branchNo; &nbsp;&nbsp;&nbsp;&nbsp;

  • Multiplicative Join Cardinality Prediction: If Branch B003 contains 3 assigned staff tuples and Branch B005 contains 2 assigned staff tuples, joining on matching branchNo yields exactly 3+2=53 + 2 = 5 result tuples for those branches.

    • Multi-Table Joins (Three or More Relations):
  • Tables are chained sequentially via consecutive JOIN ... ON ... clauses: sql SELECT b.branchNo, b.city, s.staffNo, p.propertyNo FROM Branch b JOIN Staff s ON b.branchNo = s.branchNo JOIN PropertyForRent p ON s.staffNo = p.staffNo ORDER BY b.branchNo, s.staffNo, p.propertyNo; &nbsp;&nbsp;&nbsp;&nbsp;

  • Critical Join Debugging Warning: Omitting an ON condition between joined relations causes an unintentional Cartesian product (Cross Join) across those tables, causing exponential row explosion.

    • CROSS JOIN (Cartesian Product):
  • Combines every tuple of the first relation with every tuple of the second relation unconditionally.

  • Resulting table cardinality equals ∣R1∣×∣R2∣|R_1| \times |R_2|: sql SELECT * FROM Branch CROSS JOIN Staff; &nbsp;&nbsp;&nbsp;&nbsp;

    • Outer Joins (LEFT, RIGHT, FULL):
  • Outer joins preserve unmatched rows from one or both tables, populating missing attribute values with NULL.

  • LEFT JOIN (LEFT OUTER JOIN): Retains every tuple from the left relation. Unmatched right-side attributes evaluate to NULL.

  • RIGHT JOIN (RIGHT OUTER JOIN): Retains every tuple from the right relation. Unmatched left-side attributes evaluate to NULL.

  • FULL JOIN (FULL OUTER JOIN): Retains all tuples from both relations, matching where possible and injecting NULL values for unmatched tuples on either side: sql -- Native PostgreSQL Full Outer Join SELECT b.*, p.* FROM Branch1 b FULL OUTER JOIN PropertyForRent1 p ON b.bCity = p.pCity; &nbsp;&nbsp;&nbsp;&nbsp;

  • Systems lacking native full outer join support emulate the operation using set union over directed outer joins: sql SELECT b.*, p.* FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity UNION SELECT b.*, p.* FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity = p.pCity; &nbsp;&nbsp;&nbsp;&nbsp;

Correlated Queries, Subqueries, and Quantifiers

  • Scalar vs. Multi-Row Subqueries:

    • Subqueries are queries nested inside outer SELECT, INSERT, UPDATE, or DELETE statements.
    • Scalar Subqueries: Return precisely one value (one row and one column). Used alongside standard comparison operators (=, >, <, etc.): sql SELECT lName, salary FROM Staff WHERE salary > ( SELECT salary FROM Staff WHERE lName = 'Ford' ); &nbsp;&nbsp;&nbsp;&nbsp;
    • Subquery Cardinality Violation: If the inner query returns multiple rows (e.g., multiple staff members named 'Ford'), a scalar comparison fails with the PostgreSQL runtime error:     ERROR: more than one row returned by a subquery used as an expression.
  • Multi-Row Set Quantifiers (IN, ANY, ALL):

    • Multi-row subqueries output a column containing zero, one, or many values.
    • IN: Tests whether the outer expression matches at least one element in the subquery result set.
    • ANY (synonymous with SOME): Returns TRUE if the comparison is satisfied for at least one value returned by the subquery.
    • ALL: Returns TRUE only if the comparison is satisfied against every value returned by the subquery.

Subquery quantifier equivalences for ANY and ALL

  • Quantifier Equivalences and Boundary Conditions:

    • = ANY is functionally equivalent to IN.
    • <> ALL is functionally equivalent to NOT IN.
    • > ANY is functionally equivalent to > (SELECT MIN(col) FROM ...).
    • > ALL is functionally equivalent to > (SELECT MAX(col) FROM ...).
    • < ANY is functionally equivalent to < (SELECT MAX(col) FROM ...).
    • < ALL is functionally equivalent to < (SELECT MIN(col) FROM ...).
    • Worked Example: If a subquery returns the salary set {3000,5000,7000}\{3000, 5000, 7000\}, the predicate WHERE salary < ANY (3000, 5000, 7000) requires only that salary < 7000 (less than the maximum value in the set).
    • Empty Set Evaluation: When the subquery produces an empty set:
    • ALL evaluates unconditionally to TRUE.
    • ANY evaluates unconditionally to FALSE.
  • Uncorrelated vs. Correlated Subqueries:

    • Uncorrelated Subqueries: Execute completely independently of the outer query. The inner query can be extracted and run standalone once, returning a static result set that the outer query consumes:
    -- Uncorrelated: runs once globally
    SELECT equipNo, description, dailyRate 
    FROM Equipment 
    WHERE dailyRate > (SELECT AVG(dailyRate) FROM Equipment);
    &nbsp;&nbsp;&nbsp;&nbsp;```
    * *Correlated Subqueries:* Re-evaluate in the context of each outer tuple because the inner predicate references attributes from the current outer row:
    

    sql -- Correlated: subquery references outer attribute e1.category SELECT e1.equipNo, e1.description, e1.dailyRate FROM Equipment e1 WHERE e1.dailyRate > ( SELECT AVG(e2.dailyRate) FROM Equipment e2 WHERE e2.category = e1.category );     ```

    • Query Optimizer Physical Planning: Correlated subqueries conceptually run once per outer tuple. However, modern cost-based relational query optimizers (including PostgreSQL's) often decorrelate or transform nested subqueries into semijoins, hash joins, or window aggregates. The pedagogical aphorism "subqueries always execute first" is physically inaccurate.
  • Subquery ORDER BY and Result Determinism:

    • Embedding an ORDER BY clause inside a nested derived table does not guarantee the ordering of the outer query's final result:
    -- Final output order is non-deterministic without an outer ORDER BY
    SELECT fName, lName, salary 
    FROM (SELECT fName, lName, salary FROM Staff ORDER BY salary DESC) AS s;
    &nbsp;&nbsp;&nbsp;&nbsp;```
    * `ORDER BY` inside a subquery is meaningful primarily when constraining intermediate row selection using pagination constructs like `LIMIT` or `FETCH FIRST`:
    

    sql -- Deterministically retrieves the top 2 highest paid staff members SELECT fName, lName, salary FROM (SELECT fName, lName, salary FROM Staff ORDER BY salary DESC LIMIT 2) AS top2;     ```

  • EXISTS and NOT EXISTS Operators:

    • EXISTS tests for the existence of tuples meeting a condition, returning a boolean TRUE or FALSE.
    • Evaluates to TRUE if the nested subquery produces at least one row; evaluates to FALSE if the subquery produces an empty result set.
    • Because EXISTS tests only row existence, the projection list of the subquery is immaterial. Standard conventions include SELECT 1, SELECT *, or SELECT NULL.
    • Correlation Requirement: The subquery must correlate with the outer query; without a correlation predicate connecting the two, EXISTS evaluates globally rather than row-by-row.
    • Join vs. EXISTS Formulation: ```sql -- Formulated with INNER JOIN SELECT S.staffNo, S.fName, S.lName FROM Staff AS S INNER JOIN Branch AS B ON S.branchNo = B.branchNo WHERE B.city = 'London';

    -- Formulated with Correlated EXISTS SELECT S.staffNo, S.fName, S.lName FROM Staff AS S WHERE EXISTS ( SELECT 1 FROM Branch AS B WHERE B.branchNo = S.branchNo AND B.city = 'London' );     ```

    • Design Choice: An INNER JOIN is required if columns from both tables must appear in the final projection, but can produce duplicate outer rows if the right table has multiple matches. EXISTS directly models boolean containment questions without creating duplicate rows.

Relational Set Operations

  • Mathematical Set Requirements:

    • Operations combine the results of two independent SELECT queries.
    • Union-Compatibility: Participating queries must project identical numbers of attributes, and corresponding pairwise columns must share mutually compatible data types.
    • Set operators eliminate duplicates by default. Preserving duplicate rows requires the explicit ALL qualifier (UNION ALL, INTERSECT ALL, EXCEPT ALL).
  • Set Operation Semantics:

    • UNION: Produces a unified relation containing all distinct tuples present in either the first or second query result:
    SELECT city FROM Branch 
    UNION 
    SELECT city FROM PropertyForRent;
    &nbsp;&nbsp;&nbsp;&nbsp;```
    * `INTERSECT`: Produces a relation containing only tuples present simultaneously in both query results:
    

    sql SELECT city FROM Branch INTERSECT SELECT city FROM PropertyForRent;     ```

    • Equi-Join Emulation of INTERSECT: Identical output can be generated using an inner join combined with DISTINCT:
      SELECT DISTINCT b.city 
      FROM Branch b 
      JOIN PropertyForRent p ON b.city = p.city;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;```
    * `EXCEPT`: Produces a relation containing tuples that exist in the first query result but do not appear in the second query result:
    

    sql SELECT city FROM Branch EXCEPT SELECT city FROM PropertyForRent;     ```

  • PostgreSQL Set Operation Syntax Restrictions:

    • PostgreSQL does not implement the SQL standard CORRESPONDING or CORRESPONDING BY clauses.
    • Queries attempting to align columns implicitly by name (e.g., UNION CORRESPONDING BY (staffNo, fName)) will fail. All target columns must be aligned manually in identical projection positions: ```sql -- Invalid in PostgreSQL: -- SELECT staffNo, fName FROM Staff UNION CORRESPONDING BY (staffNo, fName) SELECT fName, staffNo FROM OtherStaff;

    -- Correct PostgreSQL syntax: SELECT staffNo, fName FROM Staff UNION SELECT staffNo, fName FROM OtherStaff;     ```

Data Manipulation Language (DML) and Safe Modification Protocols

  • Tuple Insertion (INSERT):

    • Direct Tuple Insertion:
    INSERT INTO Staff 
    VALUES ('SG16', 'Alan', 'Brown', 'Assistant', 'M', '1957-05-25', 8300, 'B003');
    &nbsp;&nbsp;&nbsp;&nbsp;```
    * *Production Best Practice:* Always include an explicit target column list (e.g., `INSERT INTO Staff (staffNo, fName, ...)`) to ensure schema modifications (such as column re-ordering or default additions) do not break existing data pipelines.
    * *Multi-Row Insertion via Query (INSERT ... SELECT):*
    

    sql INSERT INTO StaffPropCount (staffNo, fName, lName, propCnt) SELECT s.staffNo, s.fName, s.lName, COUNT(*) FROM Staff s JOIN PropertyForRent p ON s.staffNo = p.staffNo GROUP BY s.staffNo, s.fName, s.lName;     ```

  • Tuple Modification and Deletion (UPDATE and DELETE):

    • Hazard of Unconstrained Updates: Executing UPDATE Staff SET salary = salary * 1.05; without a WHERE clause applies a 5% raise globally across every tuple in the relation.
    • Targeted DML Execution: ```sql -- Constrained modification UPDATE Staff SET salary = salary * 1.05 WHERE branchNo = 'B003';

    -- Constrained deletion DELETE FROM Staff WHERE staffNo = 'SG16';     ```

  • The Safe DML Workflow Protocol:

    • To prevent catastrophic accidental data alteration or deletion:
    1. Formulate Read Verification Query: Draft a SELECT statement featuring the exact intended WHERE filtering condition.
    2. Inspect Cardinality and Targets: Execute the read query and verify that every targeted row—and only targeted rows—appear in the result set.
    3. Execute Modification Statement: Substitute the projection with the modifying UPDATE or DELETE clause and execute.

Systematic Query Design and Capstone Challenges

  • The Seven-Step SQL Query Construction Checklist:

    1. Identify Source Relations: Which base tables or views contain the necessary attributes? (FROM)
    2. Determine Row-Level Filtering: Which predicates must individual tuples satisfy? (WHERE)
    3. Determine Grouping Requirements: Must aggregate calculations be segmented across categorical dimensions? (GROUP BY)
    4. Identify Relational Associations: How do multiple tables connect, and which join type correctly handles potential missing values? (JOIN ... ON ...)
    5. Evaluate Structural Alternatives: Would a nested subquery, existence test (EXISTS), or set operation (UNION/INTERSECT/EXCEPT) express the question with greater clarity and maintainability?
    6. Formulate Result Predictions: What should the output table schema, row count, uniqueness, and presentation order look like before execution?
    7. Verify Clause Semantics: Can every clause in the query pipeline be rigorously justified based on relational algebra and business logic?
  • Capstone Challenge Implementation:

    • Business Requirement: Find every branch office that employs more than one staff member, reporting the branch number, city, staff count, and total branch salary expenditure, sorted by total salary in descending order.
    • Systematic Query Formulation:
    • Tables: Branch b and Staff s.
    • Association: b.branchNo = s.branchNo (Inner Join).
    • Grouping Keys: b.branchNo, b.city.
    • Group Filter: COUNT(*) > 1 (Applied in HAVING).
    • Sorting Key: total_salary DESC. sql SELECT b.branchNo, b.city, COUNT(*) AS staff_count, SUM(s.salary) AS total_salary FROM Branch b JOIN Staff s ON b.branchNo = s.branchNo GROUP BY b.branchNo, b.city HAVING COUNT(*) > 1 ORDER BY total_salary DESC; &nbsp;&nbsp;&nbsp;&nbsp;