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 likeIFNULL(). - Native Relational Set Operators: PostgreSQL provides direct native engine support for
FULL OUTER JOIN,INTERSECT, andEXCEPT. - Unsupported SQL Standard Clauses: PostgreSQL does not implement the SQL standard
CORRESPONDINGorCORRESPONDING BYclauses 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 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 thePrivateOwnerrelation.staffNo: Foreign key linking to theStaffmember managing the property (nullable).branchNo: Foreign key linking to the managingBranch.- 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)

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:
FROM: Identifies the source relation(s) or Cartesian products to establish the base dataset.WHERE: Filters individual source rows according to boolean predicate conditions.GROUP BY: Partitions qualifying rows into distinct groups based on common attribute values.HAVING: Filters aggregated groups based on group-level summary predicates.SELECT: Evaluates expressions, handles column aliasing, and projects the requested attributes.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), andHAVING(removes non-qualifying groups). - Clauses altering presentation only:
SELECT(restricts or transforms column attributes without adding or removing rows) andORDER BY(re-arranges row display order without altering row identity or count).
Row Filtering via the WHERE Clause:
- Predicates evaluate to
TRUE,FALSE, orUNKNOWN. - 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), butbranchNo = 'B005'(FALSE). Fails overall predicate. - Susan Brand (
SG5):salary = 24000 > 8000(TRUE),position = 'Manager'(TRUE), andbranchNo = 'B003'(TRUE). Survives and is returned. - Result: Exactly 1 row (
SG5).
- Predicates evaluate to
Three-Valued Logic and NULL Value Handling:
NULLsignifies missing, unknown, or inapplicable information; it is never equal to any value, including anotherNULL.- Standard equality operators (
= NULLor<> NULL) evaluate toUNKNOWN. In SQL boolean contexts (such asWHEREandHAVING), only expressions evaluating toTRUEare retained; rows evaluating toUNKNOWNare 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 NULLsyntax.
Result Set Sorting (ORDER BY):
- Default sort direction is ascending (
ASC). Descending order requires explicitDESCdesignation. - Multiple expressions establish hierarchical primary, secondary, and tertiary sorting keys:
sql SELECT propertyNo, type, rooms, rent FROM PropertyForRent ORDER BY type, rent DESC; - Determinism and Secondary Sort Keys:
- If four properties possess identical
typevalues and no secondary sort key is provided, relational engines provide no deterministic ordering guarantee among them. - Supplying a secondary sort key like
rent DESCensures deterministic ranking, displaying the most expensive property of that type first.
- Default sort direction is ascending (
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; - Equivalence:
salary >= 20000 AND salary <= 30000. - Matching rows: John White (
30000.00) and Susan Brand (24000.00).

- 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'); - Logical equivalence:
position = 'Manager' OR position = 'Supervisor'. - Matching rows: John White (Manager), David Ford (Supervisor), Susan Brand (Manager).
- Expressive characteristics: While
INadds no formal theoretical expressive power over chainedORcomparisons, it improves query readability and allows query planners to execute optimized index lookups against larger sets. NOT INserves as the negated quantifier, equivalent toposition <> 'Manager' AND position <> 'Supervisor'.
- Tests whether an attribute value equals any member in a specified literal set or single-column subquery:

- 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%'; - 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)

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(), andMAX()operate across numeric, string, and chronological types.SUM()andAVG()are strictly restricted to numeric types.Treatment of Nulls:
COUNT(*)counts all qualifying tuples, unconditionally including rows containingNULLvalues and duplicate entries.All other aggregate functions (
COUNT(column),SUM(column),AVG(column),MIN(column),MAX(column)) automatically eliminateNULLvalues before evaluating the aggregate expression.DISTINCT Modifier:
COUNT(DISTINCT column)computes the count of unique, non-null values.DISTINCTmodifies calculation results forSUM()andAVG(), but has zero effect onMIN()orMAX().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,
COALESCEmust 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
SELECTlist that is not wrapped in an aggregate function must be included in theGROUP BYclause. 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):
WHEREfilters individual base rows before any grouping or aggregate computation takes place.HAVINGfilters generated groups after theGROUP BYclause has partitioned the data and evaluated the group aggregates.- Aggregates cannot appear in
WHEREclauses (e.g.,WHERE COUNT(*) > 1is 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; - 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; - Execution trace on Staff instance: Branch
B003has 3 employees (SG37,SG14,SG5); branchB005has 2 employees (SL21,SL41); branchB007has 1 employee (SA9). OnlyB003satisfiesCOUNT(*) > 2, yielding 1 row withtotal_salary = 12000 + 18000 + 24000 = 54000.
Relational Joins and Multi-Table Query Formulation
- Theoretical Join Classifications:

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 JOINcan implement either a general theta join or an equi join depending on itsONexpression.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
ONcondition evaluates toTRUE.Basic two-table join:
sql SELECT b.branchNo, b.city, s.staffNo FROM Branch b JOIN Staff s ON b.branchNo = s.branchNo; Multiplicative Join Cardinality Prediction: If Branch
B003contains 3 assigned staff tuples and BranchB005contains 2 assigned staff tuples, joining on matchingbranchNoyields exactly 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; Critical Join Debugging Warning: Omitting an
ONcondition 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 :
sql SELECT * FROM Branch CROSS JOIN Staff; - 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
NULLvalues 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; 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;
Correlated Queries, Subqueries, and Quantifiers
Scalar vs. Multi-Row Subqueries:
- Subqueries are queries nested inside outer
SELECT,INSERT,UPDATE, orDELETEstatements. - 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' ); - 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.
- Subqueries are queries nested inside outer
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 withSOME): ReturnsTRUEif the comparison is satisfied for at least one value returned by the subquery.ALL: ReturnsTRUEonly if the comparison is satisfied against every value returned by the subquery.

Quantifier Equivalences and Boundary Conditions:
= ANYis functionally equivalent toIN.<> ALLis functionally equivalent toNOT IN.> ANYis functionally equivalent to> (SELECT MIN(col) FROM ...).> ALLis functionally equivalent to> (SELECT MAX(col) FROM ...).< ANYis functionally equivalent to< (SELECT MAX(col) FROM ...).< ALLis functionally equivalent to< (SELECT MIN(col) FROM ...).- Worked Example: If a subquery returns the salary set , the predicate
WHERE salary < ANY (3000, 5000, 7000)requires only thatsalary < 7000(less than the maximum value in the set). - Empty Set Evaluation: When the subquery produces an empty set:
ALLevaluates unconditionally toTRUE.ANYevaluates unconditionally toFALSE.
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); ``` * *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 BYclause 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; ``` * `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; ```
- Embedding an
EXISTS and NOT EXISTS Operators:
EXISTStests for the existence of tuples meeting a condition, returning a booleanTRUEorFALSE.- Evaluates to
TRUEif the nested subquery produces at least one row; evaluates toFALSEif the subquery produces an empty result set. - Because
EXISTStests only row existence, the projection list of the subquery is immaterial. Standard conventions includeSELECT 1,SELECT *, orSELECT NULL. - Correlation Requirement: The subquery must correlate with the outer query; without a correlation predicate connecting the two,
EXISTSevaluates 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 JOINis 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.EXISTSdirectly models boolean containment questions without creating duplicate rows.
Relational Set Operations
Mathematical Set Requirements:
- Operations combine the results of two independent
SELECTqueries. - 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
ALLqualifier (UNION ALL,INTERSECT ALL,EXCEPT ALL).
- Operations combine the results of two independent
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; ``` * `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; ``` * `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
CORRESPONDINGorCORRESPONDING BYclauses. - 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; ```
- PostgreSQL does not implement the SQL standard
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'); ``` * *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 aWHEREclause 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'; ```
- Hazard of Unconstrained Updates: Executing
The Safe DML Workflow Protocol:
- To prevent catastrophic accidental data alteration or deletion:
- Formulate Read Verification Query: Draft a
SELECTstatement featuring the exact intendedWHEREfiltering condition. - Inspect Cardinality and Targets: Execute the read query and verify that every targeted row—and only targeted rows—appear in the result set.
- Execute Modification Statement: Substitute the projection with the modifying
UPDATEorDELETEclause and execute.
Systematic Query Design and Capstone Challenges
The Seven-Step SQL Query Construction Checklist:
- Identify Source Relations: Which base tables or views contain the necessary attributes? (
FROM) - Determine Row-Level Filtering: Which predicates must individual tuples satisfy? (
WHERE) - Determine Grouping Requirements: Must aggregate calculations be segmented across categorical dimensions? (
GROUP BY) - Identify Relational Associations: How do multiple tables connect, and which join type correctly handles potential missing values? (
JOIN ... ON ...) - Evaluate Structural Alternatives: Would a nested subquery, existence test (
EXISTS), or set operation (UNION/INTERSECT/EXCEPT) express the question with greater clarity and maintainability? - Formulate Result Predictions: What should the output table schema, row count, uniqueness, and presentation order look like before execution?
- Verify Clause Semantics: Can every clause in the query pipeline be rigorously justified based on relational algebra and business logic?
- Identify Source Relations: Which base tables or views contain the necessary attributes? (
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 bandStaff s. - Association:
b.branchNo = s.branchNo(Inner Join). - Grouping Keys:
b.branchNo,b.city. - Group Filter:
COUNT(*) > 1(Applied inHAVING). - 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;