TRAINING | DAY 8

Self Join, UNION ALL Reports, Views & Foreign Keys

Assignment review (team stats, profit rides, hierarchy report), then views, referential integrity and identity columns in SQL Server.

The Crux in 60 Seconds

Self join to see the "next row"

A single row cannot tell you about the following ride. Join the table to itself on id, end_location = start_location and end_time = start_time. Use a LEFT JOIN so every ride stays, then count matches.

UNION ALL stacks report blocks

One SELECT per hierarchy (category, subcategory, ship mode), each with a literal hierarchy_type. Every block needs the same number of columns; output column names come from the first query.

A view stores a query, not data

Querying a view re-runs its SELECT on the base tables every time. It sees new data, takes no storage, and fails if a base table is gone. Good for sharing big queries and regional slices.

Foreign key means parent first

A value in the child column must already exist in the parent's primary key (NULL is allowed unless the column is NOT NULL). Orphan inserts, and deletes or updates of a referenced parent row, throw errors.

Foundational Concepts & Comparisons

UNION

Removes duplicate rows; the database still sorts and de-duplicates.
Use it when you need a unique list: team_1 plus team_2 gives 6 unique teams.

UNION ALL

Keeps every row, so the same query gives 10 rows (India repeats).
Use it when every row must count (matches played) or when you know there are no duplicates, as in the hierarchy report.

Table

Physical object that holds the data.
Data stays until you change or delete it.

View (virtual table)

Holds only a SELECT definition: an "envelope" around a query. No data, no storage.
Still a permanent database object: it survives log-out and anyone with database access can see it.

INNER vs LEFT JOIN (in a self join)

INNER returned only the matching ride pair (1 row).
LEFT returned all 7 rows of the first copy; only the matching row had data from the second copy, the rest were NULL.

Transactional vs analytical systems

Transactional (Amazon purchases, bank transactions): put constraints on so no junk or wrong data enters.
Analytical / warehouse loading millions of rows: usually no constraints (each insert would be checked); validate data at the SQL level instead.

What this session covered Assignment review of three questions (team win/loss table, driver "profit rides", hierarchy sales report); then views (with Q&A on security and performance), referential integrity and foreign keys, and the identity column. Subqueries, CTEs and window functions start in the next session.

Architectural Mechanics & Workflows

Assignment Q1: team name, matches played, wins, losses

Source table: team_1, team_2, winner (5 matches, 6 teams). A team can appear in either column, so India is not always in column one. The expected output has one row per team (6 rows).

Expected result (instructor's example)India played 2 matches, won 2, lost 0. A team has won a match when the team name equals the winner, so that comparison becomes a win flag that can be aggregated later.

1. Team listteam_1 UNION team_2
(6 unique rows)

→

2. Win flagCASE WHEN team_1 = winner THEN 1 ELSE 0

→

3. Same for team_2UNION ALL both blocks

→

4. Aggregateneeds subquery / CTE (parked)

SELECT team_1 AS team_name, CASE WHEN team_1 = winner THEN 1 ELSE 0 END AS win_flag FROM icc_world_cup
UNION ALL
SELECT team_2,              CASE WHEN team_2 = winner THEN 1 ELSE 0 END FROM icc_world_cup

Step / test	What it proved
Team list with UNION ALL, then UNION	UNION ALL gave 10 rows (duplicates); UNION gave the 6 unique teams.
Win flag on team_1, then on team_2	Flag is 1 only where that team equals the winner; both blocks together carry every match for every team.
Combine with UNION ALL	Right choice: a team that lost both matches must still be counted twice. Five matches gave five win flags.
Student ideas: SUM the flags, join with winner, separate queries for played / wins / losses then join on team name	Plain aggregation leaves two rows for teams that sit in both columns. Every idea needs the unique team list first, so a subquery or CTE is required. Instructor parked it until subqueries are covered.

Assignment Q3: total rides and "profit rides" per driver

drivers table: driver id, start time, end time, start location, end location. One row is one ride; 7 rows in total (driver 1 has 5 rides, driver 2 has 2). A profit ride is one whose end location equals the start location of the immediately next ride (end time equals next start time), so the driver goes nowhere between trips.

Driver	Total rides	Profit rides	Why
1	5	1	Only one ride ends where the next begins (B and B).
2	2	0	The end and next start locations differ.

Driver 1 has 1 profit ride (location B ends, B starts); driver 2 has 0.
Total rides is easy: COUNT(1) ... GROUP BY id gives 5 and 2.
Profit rides cannot be judged row by row; you must look at the next ride, so make a second instance of the table and self join.

d1every ride

→

LEFT JOIN d2 ONid = id, d1.end_location = d2.start_location, d1.end_time = d2.start_time

→

GROUP BY d1.idCOUNT(1) = total

→

COUNT(d2.id)= profit rides

SELECT d1.id AS driver_id, COUNT(1) AS total_rides, COUNT(d2.id) AS profit_rides
FROM drivers d1
LEFT JOIN drivers d2
  ON  d1.id = d2.id
  AND d1.end_location = d2.start_location
  AND d1.end_time     = d2.start_time
GROUP BY d1.id

Q3 continued: what each test run proved (in order)

Test	Result and meaning
`COUNT(1) GROUP BY id`	5 and 2 total rides. Profit rides cannot come from this alone.
Self join on id + `end_location = start_location` only	2 rows. One is a real profit ride; the other (end location C) is not, because its end time is not its next start time.
Add `end_time = start_time`	Only 1 row, the true profit ride.
Join on id with the location and time tests in `WHERE`	Works, but the join first produces 29 rows (5 x 5 + 2 x 2, a cross join per driver) and WHERE filters afterwards. Put the tests in `ON`: they are join conditions, so it is better for performance.
Switch INNER to LEFT JOIN	All 7 rides of d1 come back; only the matching row has d2 data, everything else is NULL.
`COUNT(d2.id)` with GROUP BY	Driver 1 gives 1, driver 2 gives 0. COUNT ignores NULLs, so any d2 column works.

Why both location AND time?Students asked about joining on time only, or location only. Instructor: it works on this data, but same time does not guarantee same location, and a same location can match non-consecutive rides (10:30 vs 12:30 / 14:30). Using both covers the definition of "immediate next ride".

Ways students counted matches(a) CASE WHEN d2 column IS NOT NULL THEN 1 END, then COUNT; (b) count where d2 end location is not null; (c) simplest: COUNT(d2.id). Remember GROUP BY d1.id.

A self join is any join of a table with itself, whatever the join type (inner, left). Window functions (later sessions) give an easier route, but this is still a solid solution; the key is thinking up the join conditions.

Assignment Q5: hierarchy report (category, subcategory, ship mode)

From orders, produce rows with hierarchy_type, hierarchy_name, total sales in the West region and total sales in the East region: one block per hierarchy, stacked.

SELECT 'category' AS hierarchy_type, category AS hierarchy_name,
       SUM(CASE WHEN region = 'West' THEN sales END) AS total_sales_west,
       SUM(CASE WHEN region = 'East' THEN sales END) AS total_sales_east
FROM orders GROUP BY category
UNION ALL
SELECT 'subcategory', subcategory, ... FROM orders GROUP BY subcategory
UNION ALL
SELECT 'ship_mode', ship_mode, ... FROM orders GROUP BY ship_mode

Build onecategory block
(3 rows)

→

Pivot regionsCASE per region as its own column

→

Replicatesubcategory, then ship mode (4 modes)

→

UNION ALLall blocks in one output

Set-operation rules shown in this question

Rule / question	Instructor's answer
UNION or UNION ALL here?	UNION ALL. UNION would still sort and try to remove duplicates. "When you know there are no duplicates, always use union all." Here the hierarchy type differs in every block.
Is `'category'` needed in GROUP BY?	No. It is a literal value in single quotes, not a column. A real column in SELECT must be in GROUP BY.
Output column names	Always taken from the first query (changing it to `hierarchy_type_one` showed that name everywhere). Later aliases can differ or be removed, but matching names is good practice.
Misspelt literal `'subcategories'`	No error, because it is only a value. The real column name is what matters.

Q5 trainer Q&A: changing the report shape

Student question	Answer
Show category and subcategory in the same row?	Select both columns and `GROUP BY` both: sales per combination (for example furniture split into its subcategories, with West and East columns).
Add a South region column for subcategories?	Add the new column in every query of the UNION ALL. All queries must have the same number of columns and structure, otherwise the union fails.
Show South sales only for ship mode?	Put `NULL` (or 0) in that position in the other queries; whatever you put will appear.

Views

CREATE VIEW vw_orders AS SELECT * FROM orders;     -- name with vw so others recognise it
SELECT * FROM vw_orders;                            -- same result as the table
DROP VIEW vw_orders;                                -- drops the view only, never the table

Naming: no restriction, but include vw (for example vw_orders, orders_summary_vw) so it is recognisable as a view.
See a view's definition: in SSMS right-click the view, Script View as, CREATE To, clipboard or file. It shows only CREATE VIEW ... AS SELECT ..., because the view holds no data.
How it runs: SELECT * FROM view goes to the definition and runs the inner query every time.

Use 1: share a big queryCreate orders_summary_vw AS the long query, then give teammates or your manager just the view name instead of the SQL.

Use 2: restrict rows by regionCREATE VIEW orders_south_view AS SELECT * FROM orders WHERE region = 'south'. Make one view per region and hand each regional manager only theirs.

Use 3: anything a SELECT can doJoins, aggregations and more can be wrapped in a view.

Use 4: cross-databaseReference another database as database.schema.table. A view emp_master was created over a table in the master system database, and can be joined with tables of the working database.

View behaviour and trainer Q&A

Question / situation	Answer
Can data be changed through a view?	Student recap accepted by instructor: only SELECT (viewing). To change the logic, drop the view and create it again.
Incremental loads, updates or deletes on the base table?	The next query on the view shows the new data, because it re-runs on the table.
A table used by the view is dropped?	Error that the table does not exist. Old records are not shown. Dropping the view leaves the table alone.
View vs temp table	Temp tables hold data, views do not. Temp tables come in a later session.
Is a view slower than a plain SELECT? Why create one?	Not much difference: it runs the same query. Worth it for recurring queries or sharing, instead of keeping a big multi-table join in Notepad.
Do users still need table permissions?	Yes: underneath, the table is queried. A user who scripts the view can run its SELECT on the table.
Can I hide the table from reporting users?	GRANT on the view alone does not serve the purpose: the table needs the grant too, or the error still appears. Alternative for row-level security: a static table of `username` and `region` that you filter on and join with orders.

Referential Integrity & Foreign Keys

Problem: the employee table held department ID 500, which was not in the department table. Every employee should belong to a department listed in the master table. Declare a constraint so a bad value is rejected.

Parent (master)dpt.department_id
PRIMARY KEY, NOT NULL

←

REFERENCESforeign key constraint

←

Childemp.department_id
values must exist in parent

Created emp with a constraint referencing dpt(department_id). Error while creating: no primary key or candidate keys in the referenced table matching the foreign key column list. The referenced column must be a primary key.
Making it a primary key first failed because the column was nullable. ALTER TABLE ... ALTER COLUMN department_id ... NOT NULL, then ALTER TABLE ... ADD CONSTRAINT <name> PRIMARY KEY (department_id). The Keys folder then showed the key.
Re-running the create statement worked. Add later with: ALTER TABLE child ADD CONSTRAINT name FOREIGN KEY (col) REFERENCES parent(col).

Test matrix (in the order run)

Action	Outcome
Insert department 100 into emp (exists in parent)	1 row affected.
Insert department 500 (not in parent)	Error: the INSERT statement conflicted with the FOREIGN KEY constraint, at insert into emp.
Insert 500 into `dpt` first, repeat emp insert	Succeeds. Load the master table first.
Insert NULL into emp.department_id	Works: no NOT NULL on the child column. The column accepts NULL or a parent value.
Recreate with NOT NULL on the child column, insert NULL	Error: cannot insert NULL into the column.
DELETE a parent row that emp references	Error: the DELETE statement conflicted with the constraint, at delete from dpt.
UPDATE a referenced parent department ID (for example to 600)	Same conflict error; it does not auto-update the children. Delete child rows first, or remove the constraint.

Composite foreign key: a foreign key points to the referenced column(s), which must be the parent's primary key. For two columns, make the parent primary key (department_id, department_name) and put both in the child. Instructor believed this works and left it as homework (not demonstrated).
This is an important interview concept; views are mostly a concept with few questions.

Identity (auto-increment) column

For tables with no natural key, add an incremental id column (surrogate key). In SQL Server it takes two numbers, start and increment (1,1). The instructor could not recall the exact syntax at first and checked it.

Insert only the other columns (for example department id and name): the id fills automatically (1, then 2 ...).
Supplying the identity value yourself fails; leave the column out as if it does not exist.
With increment 2 the values would be 1, 3, 5. It can be made primary key and NOT NULL, then referenced from another table.

Environment & troubleshooting seen in class

Case-sensitive database: creating a table in capitals made a query error, so it was recreated with everything in small letters. A student's orders failed with "invalid object name" until the table was renamed to lowercase.
Wrong database selected: that student was also running in master; switch to the working database in the top-left dropdown. Row counts were doubled because data was inserted twice; delete and reload.
SQL Server 2019 install: still reported the 2017 C++ redistributable missing after downloading it and restarting. Installing SQL Server 2017 worked (little difference from 2019); then install SSMS.

Best Practices & Golden Rules

UNION ALL by defaultIf duplicates cannot occur, never ask the database to remove them.

Join tests belong in ONCross-joining first (29 rows) and filtering in WHERE is the slower habit.

Parent table firstLoad the master table before child tables, or foreign key inserts fail.

Stay lowercase on a case-sensitive serverTable names, column names and string values must match exactly.

Practise the self join by handPut the table in Excel twice, add the join conditions one at a time, then add aggregation. Spend time thinking before writing the SQL.

Constraints depend on the systemAdd them in transactional systems; skip them in analytical loads of millions of rows for performance.

Numbers & Metrics to Remember

rows from the unconditioned drivers join (5x5 + 2x2)

10 / 6

rows: UNION ALL vs UNION of team columns

500

orphan department ID that broke the foreign key

1, 1

identity start and increment

Mini Glossary

Self join: joining a table to another instance of itself, for example to compare a row with the next one.

Profit ride: a ride whose end location and time equal the next ride's start location and time.

Literal value: a constant in single quotes in SELECT; not a column, so not needed in GROUP BY.

UNION / UNION ALL: set operations stacking query results; UNION removes duplicates, UNION ALL keeps them.

View: a stored SELECT acting as a virtual table; holds no data.

Referential integrity: rule that child values must match values in the parent (master) table.

Foreign key: the child column that references a parent's primary key.

Identity column: an auto-incrementing column defined by start and increment values.

Quick Self-Test

1For the team table, why is UNION used to build the team list but UNION ALL to combine the two win-flag queries?
2In the profit-ride self join, why is joining on id and end/start location alone not enough?
3Scenario: your self join with INNER JOIN returns one row, but you need totals for every driver. What do you change, and why does COUNT(d2.id) return 0 for a driver with no profit rides?
4Why should the location and time tests go in ON rather than WHERE? How many rows did the unconditioned join return?
5Scenario: you must add a South sales column to only the ship-mode block of the UNION ALL report. What must you do in the other blocks, and which query decides the final column names?
6What does a view actually store? What happens to its results after new rows load into the base table, and after the base table is dropped?
7Scenario: you grant a reporting user access to a view but not the underlying table. What happens, and what alternative did the instructor describe for row-level security?
8Troubleshooting: creating a foreign key says the referenced table has no primary key or candidate key, and later an insert of 500 is rejected. Explain the fix for each.