MASTER STUDY GUIDE

SQL:
Introduction & Advanced
Chapters 7 & 8 — Complete Reference

Modern Database Management, 8th Edition

Hoffer · Prescott · McFadden

PINE VALLEY FURNITURE COMPANY CASE · SQL:1999 · SQL:2003

 CHAPTER 7

Introduction
to SQL

SQL Overview

DDL

DML

DCL

Single-Table Queries

Views

Referential Integrity

SQL:1999 & SQL:2003 Standards

SQL Master Study Guide — Chapters 7 & 8 Page 2

 Learning Objectives
✦ Definition of terms and history & role of SQL
✦ SQL Data Definition Language (DDL)
✦ Single-table queries using SELECT
✦ Referential integrity constraints
✦ SQL:1999 & SQL:2003 standards overview

7.1 SQL Overview

SQL (Structured Query Language) is the universal standard language for Relational Database
Management Systems (RDBMS). It is the lingua franca of database interaction, supported by virtually
every major database vendor — from Oracle and Microsoft SQL Server to MySQL, PostgreSQL, and IBM
DB2.

RDBMS Defined
A Database Management System that manages data as a collection of tables in which all relationships
are represented by common values in related tables. There are no pointers or physical links — only
logical connections via shared key values.

7.1.1 History of SQL — Timeline

Year Milestone

1970 E.F. Codd publishes his landmark paper at IBM introducing the relational database concept.

1974–79 IBM Research Lab builds System R using SEQUEL (later renamed SQL).

1979 Oracle Corporation markets the first commercially available SQL relational database.

1986 ANSI releases the first official SQL standard, also adopted by ISO.

1989 SQL-89: Major update adding referential integrity and enhanced integrity constraints.

1992 SQL-92 (SQL2): New data types, schema manipulation, new JOIN types, improved error handling.

1999 SQL:1999 (SQL3): Object-relational features, triggers, stored procedures (PSM), recursive
queries.

2003 SQL:2003: XML support, BIGINT, MULTISET, MERGE statement, identity columns, window
functions.

Current SQL is supported by all major database vendors; the core ANSI standard is universally recognized.

7.1.2 Purpose of the SQL Standard

• Specify syntax and semantics for data definition and manipulation.
• Define data structures: tables, views, constraints, and schemas.

SQL Master Study Guide — Chapters 7 & 8 Page 3

 • Enable portability — applications can be migrated across RDBMS platforms with minimal
modification.

7.1.3 Benefits of a Standardized Relational Language

Benefit Explanation

Reduced training costs Personnel trained on one RDBMS can transfer skills to another.

Productivity Developers write less code; the RDBMS handles optimization.

Application portability Apps can move between environments (e.g., Oracle → PostgreSQL).

Application longevity SQL-based apps remain viable longer — the standard is stable.

Reduced vendor dependence Organisations are not locked into a single vendor's language.

Cross-system communication Systems from different vendors can exchange data using SQL.

7.2 SQL Environment

The SQL environment is a structured hierarchy organising database objects from the highest level down
to individual columns. Understanding this hierarchy is essential for writing properly-scoped SQL
statements.

Key Components
Catalog: The highest-level unit. A named collection of schemas constituting the complete description
of a database (e.g., DEV_C vs. PROD_C).

Schema: A named container within a catalog grouping related objects — base tables, views,
constraints, domains, character sets — all belonging to a particular user or application context.

7.2.2 SQL Language Sublanguages

Abbrev. Full Name Purpose Key Commands

DDL Data Definition Language Defines/modifies database CREATE TABLE, ALTER TABLE,
structure DROP TABLE, CREATE VIEW

DML Data Manipulation Language Maintains and queries a SELECT, INSERT, UPDATE,
database DELETE

DCL Data Control Language Controls database access GRANT, REVOKE, COMMIT,
and security ROLLBACK

7.2.3 SQL Data Types Reference

SQL Master Study Guide — Chapters 7 & 8 Page 4

 Type Category Description & Use

CHAR(n) String Fixed-length string. Padded with spaces to n chars. Use for state codes
(CHAR(2)).

VARCHAR(n) String Variable-length string up to n chars. Ideal for names and addresses.

BLOB Binary Binary Large Object. Variable length. Used for images, audio, documents.

DECIMAL(p,s) Number Exact number with user-defined precision and scale. DECIMAL(6,2) → 1234.56.

INTEGER Number Whole numbers. No fractional component.

TIMESTAMP Temporal Exact date + time with fractional-second precision.

BOOLEAN Boolean Stores TRUE, FALSE, or UNKNOWN (arising from NULL comparisons).

7.3 SQL Database Definition (DDL)

DDL commands build the physical structure of the database — tables, relationships, and constraints. The
design process follows a specific sequence of planning decisions before any CREATE TABLE statement
is written.

7.3.1 Major CREATE Statements

• CREATE SCHEMA — Defines a portion of the database owned by a particular user.
• CREATE TABLE — Defines a table: columns, data types, nullability, defaults, and constraints.
• CREATE VIEW — Defines a virtual table derived from one or more base tables via a stored
SELECT.
• Others: CHARACTER SET, COLLATION, TRANSLATION, ASSERTION, DOMAIN.

7.3.2 Seven-Step Table Creation Process

# Step Details

1 Identify data types Choose VARCHAR, INTEGER, DECIMAL, DATE, etc. for every column.

2 Nullability Mandatory attributes → NOT NULL; optional ones remain nullable.

3 Unique columns Use UNIQUE for candidate keys that must not contain duplicates.

4 PK–FK relationships Document which columns link related tables for referential integrity.

5 Default values Auto-populate columns when no value is provided (e.g., DEFAULT

SYSDATE).

6 Domain constraints Use CHECK to limit valid values (e.g., PRODUCT_FINISH IN ('Cherry', ...)).

7 Create table & indexes Execute CREATE TABLE; create indexes separately for performance.

7.3.3 CREATE TABLE Syntax

SQL Master Study Guide — Chapters 7 & 8 Page 5

 CREATE TABLE tablename

{column_definition [table_constraint]} , . . .

[ON COMMIT {DELETE | PRESERVE} ROWS]

);

-- column_definition:

column_name {domain_name | datatype [(size)]}

[column_constraint ...] [DEFAULT value]

-- table_constraint:

[CONSTRAINT constraint_name] constraint_type [attributes]

7.3.4 Pine Valley Furniture Company — Data Model

All examples throughout Chapters 7 and 8 use the Pine Valley Furniture Company (PVF) database.
Four related tables with the following cardinalities:

Parent Child Cardinality Rule

CUSTOMER_T (1) ■■■■ (M) ORDER_T One customer places many orders

ORDER_T (1) ■■■■ (M) ORDER_LINE_T One order contains many line items

PRODUCT_T (1) ■■■■ (M) ORDER_LINE_T One product appears on many order lines

7.3.5 Complete DDL — Pine Valley Furniture Company

-- TABLE 1: CUSTOMER_T (PK: CUSTOMER_ID)

CREATE TABLE CUSTOMER_T (

CUSTOMER_ID NUMBER(11, 0) NOT NULL,

CUSTOMER_NAME VARCHAR2(25) NOT NULL,

CUSTOMER_ADDRESS VARCHAR2(30),

CITY VARCHAR2(20),

STATE VARCHAR2(2),

POSTAL_CODE VARCHAR2(9),

CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)

);

-- TABLE 2: ORDER_T (PK: ORDER_ID | FK: CUSTOMER_ID → CUSTOMER_T)

CREATE TABLE ORDER_T (

ORDER_ID NUMBER(11, 0) NOT NULL,

ORDER_DATE DATE DEFAULT SYSDATE,

CUSTOMER_ID NUMBER(11, 0),

CONSTRAINT ORDER_PK PRIMARY KEY (ORDER_ID),

SQL Master Study Guide — Chapters 7 & 8 Page 6

 CONSTRAINT ORDER_FK FOREIGN KEY (CUSTOMER_ID)

REFERENCES CUSTOMER_T (CUSTOMER_ID)

);

-- TABLE 3: PRODUCT_T (PK: PRODUCT_ID)

CREATE TABLE PRODUCT_T (

PRODUCT_ID INTEGER NOT NULL,

PRODUCT_DESCRIPTION VARCHAR2(50),

PRODUCT_FINISH VARCHAR2(20)

CHECK (PRODUCT_FINISH IN

('Cherry','Natural Ash','White Ash','Red Oak','Natural Oak','Walnut')),

STANDARD_PRICE DECIMAL(6,2),

PRODUCT_LINE_ID INTEGER,

CONSTRAINT PRODUCT_PK PRIMARY KEY (PRODUCT_ID)

);

-- TABLE 4: ORDER_LINE_T (Composite PK: ORDER_ID + PRODUCT_ID)

CREATE TABLE ORDER_LINE_T (

ORDER_ID NUMBER(11,0) NOT NULL,

PRODUCT_ID NUMBER(11,0) NOT NULL,

ORDERED_QUANTITY NUMBER(11,0),

CONSTRAINT ORDER_LINE_PK PRIMARY KEY (ORDER_ID, PRODUCT_ID),

CONSTRAINT ORDER_LINE_FK1 FOREIGN KEY (ORDER_ID)

REFERENCES ORDER_T (ORDER_ID),

CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID)

REFERENCES PRODUCT_T (PRODUCT_ID)

);

Composite Primary Key Rule: When a PK spans multiple columns it must be declared as a table-level
constraint. Both component columns must be NOT NULL.

7.3.6 Identity Columns — SQL:2003

CREATE TABLE CUSTOMER_T (

CUSTOMER_ID INTEGER GENERATED ALWAYS AS IDENTITY

(START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 NO CYCLE),

CUSTOMER_NAME VARCHAR(25) NOT NULL,

...

CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)

);

-- INSERT does NOT include CUSTOMER_ID — it is auto-generated:

SQL Master Study Guide — Chapters 7 & 8 Page 7

 INSERT INTO CUSTOMER_T VALUES ('Contemporary Casuals', '1355 S. Himes Blvd.',
'Gainesville', 'FL', 32601);

GENERATED ALWAYS AS IDENTITY instructs the engine to auto-assign a sequential integer.

Equivalent to Oracle's SEQUENCE, MySQL's AUTO_INCREMENT, and SQL Server's IDENTITY.

7.3.7 Default Values & Domain Constraints

-- DEFAULT VALUE: today's date auto-inserted if ORDER_DATE is omitted

ORDER_DATE DATE DEFAULT SYSDATE,

-- DOMAIN CONSTRAINT: rejects any finish not in this list

PRODUCT_FINISH VARCHAR2(20) CHECK (PRODUCT_FINISH IN ('Cherry','Natural Ash','White

Ash','Red Oak','Natural Oak','Walnut'))

7.3.8 Changing and Removing Tables

-- Add a new column

ALTER TABLE CUSTOMER_T ADD (TYPE VARCHAR(2));

-- Remove a table entirely (structure + all data permanently deleted)

DROP TABLE CUSTOMER_T;

DROP TABLE is destructive — it removes the table definition and all data. Most RDBMSs require child
tables to be dropped first, or CASCADE DROP to be specified.

7.3.9 Assertions — Cross-Table Constraints

-- Prevents any employee salary > $100,000 (schema-level constraint)

CREATE ASSERTION max_salary

CHECK (NOT EXISTS

(SELECT * FROM Employees WHERE salary > 100000));

Assertions can reference data across multiple tables — unlike column or table CHECK constraints. Not all
RDBMS products support assertions (Oracle uses triggers instead).

7.4 Data Integrity Controls

7.4.1 Referential Integrity Defined

SQL Master Study Guide — Chapters 7 & 8 Page 8

 Referential Integrity
A relational constraint ensuring that foreign key values in a child table must always match a primary
key value in the parent table, or be NULL. Example: every CUSTOMER_ID in ORDER_T must exist as
a CUSTOMER_ID in CUSTOMER_T.

Restricts three types of operations:

• Deletes of primary records — cannot delete a customer with existing orders.

• Updates of primary records — cannot change a customer's ID if child rows reference the old
value.
• Inserts of dependent records — cannot insert an order for a non-existent CUSTOMER_ID.

7.4.2 Referential Integrity Action Rules

Action Rule Behaviour

RESTRICT Safest. Parent row cannot be deleted/updated if child rows reference it. Operation is blocked.

CASCADE Change is propagated. If CUSTOMER_ID is updated, all matching FKs in ORDER_T are
updated too.

SET NULL Parent deleted/updated → FK in child rows set to NULL. Child row preserved but link severed.

SET DEFAULT Parent deleted/updated → child FK values set to a predefined default value.

The same four rules apply to both ON DELETE and ON UPDATE.

7.5 DML — Data Manipulation Language

7.5.1 INSERT Statement

-- Full INSERT: supply values for every column in definition order

INSERT INTO CUSTOMER_T VALUES (001, 'Contemporary Casuals', '1355 S. Himes Blvd.',
'Gainesville', 'FL', 32601);

-- Partial INSERT: explicitly name the columns

INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE)

VALUES (1, 'End Table', 'Cherry', 175);

-- INSERT from another table

INSERT INTO CA_CUSTOMER_T

SELECT * FROM CUSTOMER_T WHERE STATE = 'CA';

7.5.2 DELETE Statement

-- Delete specific rows

DELETE FROM CUSTOMER_T WHERE STATE = 'HI';

SQL Master Study Guide — Chapters 7 & 8 Page 9

 -- Delete ALL rows (table structure remains)

DELETE FROM CUSTOMER_T;

DELETE is a DML operation that can be rolled back. Without WHERE, all rows are deleted but the table
remains.

7.5.3 UPDATE Statement

-- Update the unit price for product #7

UPDATE PRODUCT_T

SET UNIT_PRICE = 775

WHERE PRODUCT_ID = 7;

Without WHERE all rows are updated. UPDATE is subject to all CHECK constraints.

7.6 SELECT Statement — Single-Table Queries

7.6.1 SELECT Clause Reference & Processing Order

Clause Purpose Processing Order

SELECT Lists the columns/expressions to return. Use * for all. 5th

FROM Identifies the table(s) or view(s). 1st

WHERE Filters individual rows using conditions. 2nd

GROUP BY Groups rows sharing the same values for aggregate computation. 3rd

HAVING Filters groups created by GROUP BY (like WHERE but for groups). 4th

ORDER BY Sorts the final result. ASC (default) or DESC. 6th

Processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. You cannot
reference a SELECT alias in a WHERE clause because WHERE is processed before SELECT.

7.6.2 Basic SELECT with Comparison Operators

-- Find all products priced below $275

SELECT PRODUCT_NAME, STANDARD_PRICE

FROM PRODUCT_V

WHERE STANDARD_PRICE < 275;

-- Comparison operators: = > >= < <= <> !=

7.6.3 SELECT with Aliases

-- Table alias (CUST) and column alias (NAME)

SELECT CUST.CUSTOMER_NAME AS NAME, CUST.CUSTOMER_ADDRESS

SQL Master Study Guide — Chapters 7 & 8 Page 10

 FROM CUSTOMER_V CUST

WHERE NAME = 'Home Furnishings';

7.6.4 Aggregate Functions

-- Count order lines for Order #1004

SELECT COUNT(*) FROM ORDER_LINE_V WHERE ORDER_ID = 1004;

Function Description

COUNT(*) Counts all rows including NULLs

COUNT(col) Counts non-NULL values only

SUM(col) Totals numeric values

AVG(col) Calculates the arithmetic mean

MAX(col) Finds the largest value

MIN(col) Finds the smallest value

Cannot mix aggregated and non-aggregated columns in SELECT unless non-aggregated columns are in
GROUP BY.

7.6.5 Boolean Operators & LIKE Wildcard

-- Products that are desks OR tables AND cost more than $300

SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE

FROM PRODUCT_V

WHERE (PRODUCT_DESCRIPTION LIKE '%Desk'

OR PRODUCT_DESCRIPTION LIKE '%Table')

AND UNIT_PRICE > 300;

-- % = zero or more any characters

-- _ = exactly one any character

Operator precedence: NOT → AND → OR. Parentheses override. Without parentheses, SQL evaluates AND
before OR, producing wrong results.

7.6.6 ORDER BY — Sorting Results

SELECT CUSTOMER_NAME, CITY, STATE

FROM CUSTOMER_V

WHERE STATE IN ('FL', 'TX', 'CA', 'HI')

ORDER BY STATE, CUSTOMER_NAME; -- primary then secondary sort

IN replaces multiple OR conditions and can be more efficiently executed by the query optimizer.

7.6.7 GROUP BY — Categorizing Results

SQL Master Study Guide — Chapters 7 & 8 Page 11

 -- Count customers per state

SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE) AS CustCount

FROM CUSTOMER_V

GROUP BY CUSTOMER_STATE;

GROUP BY collapses rows sharing the same grouping-column value into a single output row. Scalar
aggregate → one row total; vector aggregate → one row per group.

7.6.8 HAVING — Filtering Groups

-- Only states with MORE THAN 1 customer

SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE) AS CustCount

FROM CUSTOMER_V

GROUP BY CUSTOMER_STATE

HAVING COUNT(CUSTOMER_STATE) > 1;

Dimension WHERE HAVING

Target Individual rows Groups of rows

Timing Before GROUP BY After GROUP BY

Aggregates? NOT allowed Allowed and common

Performance Better — reduces rows early Applied to already-grouped data

Use Case WHERE STATE = 'FL' HAVING COUNT(*) > 5

Best Practice Always use WHERE when possible Only when filtering on aggregate results

7.7 Views — Using and Defining

A view is a named, stored SELECT statement that can be referenced like a table. Views are one of the
most powerful features for controlling data access and simplifying complex queries.

7.7.1 Types of Views

View Type Definition Data Stored? Update
Frequency

Base Table Actual physical table containing raw data on disk. Yes — physical As needed by
DML

Dynamic View Virtual table defined by a stored SELECT. No data No — virtual Always current
stored — re-executes SELECT on every query.

Materialized View Physical copy (snapshot) stored on disk. Must be Yes — snapshot Periodically
refreshed periodically. refreshed

SQL Master Study Guide — Chapters 7 & 8 Page 12

 7.7.2 CREATE VIEW Example
-- Dynamic view showing only premium-priced products

-- WITH CHECK OPTION prevents inserting products priced ≤ $300 via this view

CREATE VIEW EXPENSIVE_STUFF_V AS

SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE

FROM PRODUCT_T

WHERE UNIT_PRICE > 300

WITH CHECK OPTION;

7.7.3 Advantages of Views

• Simplify query commands — complex joins encapsulated; users write simple SELECT statements.
• Assist with data security — expose only specific columns or rows, hiding sensitive data.
• Enhance programming productivity — developers reference stable view names.
• Contain most current base table data — dynamic views always reflect the latest data.
• Use little storage space — only the SELECT definition is stored.
• Provide customized view for different users or roles.
• Establish physical data independence — base table structure changes are hidden from apps.

7.7.4 Disadvantages of Views

• Processing overhead — dynamic views re-execute the underlying SELECT every time they are
referenced.
• Update restrictions — views with JOINs, GROUP BY, DISTINCT, aggregates, or subqueries are
typically read-only.

7.7.5 LIKE vs. IN — Comparison Matrix

Dimension LIKE Operator IN Operator

Pattern Wildcard (%, _) Exact value in a list

Data Types Strings only Any comparable data type

Performance Cannot use indexes with leading % Optimizer-friendly; can use indexes

Use Case Partial text search (names ending in Exact match: STATE IN ('CA','TX')
"Corp")

Subquery Not applicable IN (subquery) is a powerful pattern

Best Practice Avoid leading % on high-volume tables Preferred over multiple OR conditions

SQL Master Study Guide — Chapters 7 & 8 Page 13

 CHAPTER 8

Advanced
SQL

Multi-Table Joins

Subqueries

Transactions

Triggers

Stored Procedures

SQL:1999 & SQL:2003

Correlated Subqueries

Union Queries

CASE Expressions

SQL Master Study Guide — Chapters 7 & 8 Page 14

 Learning Objectives
✦ Multi-table SQL queries and joins
✦ Three types of joins: equi-join, natural join, outer join
✦ Correlated vs. non-correlated subqueries
✦ Referential integrity in SQL
✦ Triggers and stored procedures
✦ SQL:1999 object-relational extensions

8.1 Processing Multiple Tables — Joins

Joins are the cornerstone of relational database querying — they reassemble normalised data from
multiple tables into meaningful result sets. The common columns are typically the PK of the parent table
and the FK of the child table.

8.1.1 Join Type Definitions

Join Type Definition

Equi-Join Join condition based on equality (PK = FK). Common columns appear redundantly in the
result.

Natural Join Equi-join where one duplicate common column is eliminated from the result.

Outer Join Includes rows even when no match exists. Three variants: LEFT, RIGHT, FULL OUTER JOIN.

Union Join Includes all columns from each table and all rows. Does not match rows — fills NULLs
elsewhere.

8.1.2 Join Comparison Matrix

Dimension Equi-Join Natural Join Left Outer Join Union Join

Matching Equality on common Equality on common All left rows + All rows, no
cols cols matching right matching

Redundant cols? Yes No (deduped) No Both tables fully

present

NULLs? No No Yes (unmatched Yes (many)

right)

Performance Good with indexes Good with indexes Slightly higher cost Very high (full cross)

Best Use Standard PK–FK Clean reports Parents with/without Rarely used in
lookups children practice

SQL Standard SQL-92 (WHERE SQL:1999 SQL-92 LEFT SQL:1999 UNION

clause) NATURAL JOIN OUTER JOIN JOIN

SQL Master Study Guide — Chapters 7 & 8 Page 15

 8.2 Pine Valley Furniture — Sample Data
The following data is used throughout all join and subquery examples. Key observation: Only 10 of 15
customers have placed orders — critical for understanding INNER vs. OUTER JOIN results.

1 Contemporary Casuals 2 Value Furniture 3 Home Furnishings

4 Eastern Furniture 5 Impressions 6 Furniture Gallery *

7 Period Furniture * 8 California Classics 9 M and H Casual *

10 Seminole Interiors * 11 American Euro Lifestyles 12 Battle Creek Furniture

13 Heritage Furnishings * 14 Kaneohe Homes * 15 Mountain Scenes

* = No orders placed — will appear only in OUTER JOIN results with NULL ORDER_ID.

ORDER_ID ORDER_DATE CUSTOMER_ID

1001 10/21/2006 1

1002 10/21/2006 8

1003 10/22/2006 15

1004 10/22/2006 5

1005 10/24/2006 3

1006 10/24/2006 2

1007 10/27/2006 11

1008 10/30/2006 12

1009 11/5/2006 4

1010 11/5/2006 1

8.3 Natural Join Example

-- For each customer who placed an order — show name and order number

-- INNER JOIN via NATURAL JOIN — only matched rows returned (10 rows)

SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID

FROM CUSTOMER_T NATURAL JOIN ORDER_T

ON CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID;

-- Alternative traditional syntax:

SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID

FROM CUSTOMER_T, ORDER_T

WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID;

10 of 15 customers have orders → exactly 10 result rows (Customer 1 appears twice for orders 1001 and
1010). The duplicate CUSTOMER_ID from ORDER_T is suppressed in the output.

SQL Master Study Guide — Chapters 7 & 8 Page 16

 8.4 Outer Join Example
-- List ALL customers with order numbers — even those with no orders

SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID

FROM CUSTOMER_T

LEFT OUTER JOIN ORDER_T

ON CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID;

CUSTOMER_I CUSTOMER_NAME ORDER_ID

D

1 Contemporary Casuals 1001

1 Contemporary Casuals 1010

2 Value Furniture 1006

3 Home Furnishings 1005

4 Eastern Furniture 1009

5 Impressions 1004

6 Furniture Gallery NULL

7 Period Furnishings NULL

8 California Classics 1002

9 M & H Casual Furniture NULL

10 Seminole Interiors NULL

11 American Euro Lifestyles 1007

12 Battle Creek Furniture 1008

13 Heritage Furnishings NULL

14 Kaneohe Homes NULL

15 Mountain Scenes 1003

16 rows total. Customers with NULL ORDER_ID have never placed an order.

8.5 Multiple Table Join (Four-Table Join)

-- Generate an invoice for Order #1006 — data from all 4 tables

SELECT

CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS,

CITY, STATE, POSTAL_CODE,

SQL Master Study Guide — Chapters 7 & 8 Page 17

 ORDER_T.ORDER_ID, ORDER_DATE,

ORDERED_QUANTITY,

PRODUCT_DESCRIPTION, STANDARD_PRICE,

(ORDERED_QUANTITY * STANDARD_PRICE) AS LINE_TOTAL

FROM

CUSTOMER_T, ORDER_T, ORDER_LINE_T, PRODUCT_T

WHERE

CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID -- Join 1

AND ORDER_T.ORDER_ID = ORDER_LINE_T.ORDER_ID -- Join 2

AND ORDER_LINE_T.PRODUCT_ID = PRODUCT_T.PRODUCT_ID -- Join 3

AND ORDER_T.ORDER_ID = 1006; -- Filter

CustID Customer OrderID Product Price Qty Line Total

2 Value Furniture 1006 Entertainment Center $650 1 $650

2 Value Furniture 1006 Writer's Desk $325 2 $650

2 Value Furniture 1006 Dining Table $800 2 $1,600

Rule: For N tables joined, you need N-1 join conditions. Missing one produces a Cartesian product.

8.6 Subqueries
A subquery (nested query or inner query) is a SELECT statement embedded inside another SQL
statement. Subqueries provide an alternative to joins for certain multi-table operations.

8.6.1 Subquery Placement Options

• WHERE clause (most common) — subquery returns a value or list used in the comparison.
• FROM clause (derived table) — subquery generates a temporary virtual table.
• HAVING clause — subquery provides a comparison value for group-level filtering.

8.6.2 Correlated vs. Non-Correlated Subqueries

Dimension Non-Correlated Correlated

Dependency Does NOT reference outer query References data from the outer query

Execution Executes ONCE for the entire query Executes ONCE per row of the outer
query

Performance More efficient Less efficient (repeated execution)

Operators IN, NOT IN, =, >, <, ANY, ALL EXISTS, NOT EXISTS, IN with outer ref

Best Use When inner result is independent When must check against each outer row

SQL Master Study Guide — Chapters 7 & 8 Page 18

 8.7 Non-Correlated Subquery
-- Names of all customers who have placed at least one order

SELECT CUSTOMER_NAME

FROM CUSTOMER_T

WHERE CUSTOMER_ID IN

(SELECT DISTINCT CUSTOMER_ID -- Runs ONCE; produces a fixed list

FROM ORDER_T);

Step 1: Inner query runs once and produces {1,2,3,4,5,8,11,12,15}. Step 2: Outer query returns names for
customers in that set. Result: 9 rows.

8.8 Correlated Subquery

-- Orders that include at least one Natural Ash product

SELECT DISTINCT ORDER_ID

FROM ORDER_LINE_T -- Outer: iterates every order line

WHERE EXISTS

(SELECT *

FROM PRODUCT_T

WHERE PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID -- ← outer row ref

AND PRODUCT_FINISH = 'Natural Ash');

EXISTS returns TRUE if the subquery produces at least one row. It short-circuits as soon as one match is
found — more efficient than IN for existence checks. The subquery re-executes for every outer row.

8.9 Derived Table Subquery (FROM Clause)

-- Products priced above the average standard price

-- Problem: WHERE cannot use aggregate functions (AVG) directly

-- Solution: compute AVG in a FROM-clause subquery (derived table)

SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE, AVGPRICE

FROM

(SELECT AVG(STANDARD_PRICE) AVGPRICE FROM PRODUCT_T), -- derived table

PRODUCT_T

WHERE STANDARD_PRICE > AVGPRICE;

The derived table produces one row with one column (AVGPRICE). It is Cartesian-joined to PRODUCT_T,
appending AVGPRICE to every row so the WHERE clause can compare against it.

SQL Master Study Guide — Chapters 7 & 8 Page 19

 8.10 Union Queries
-- Show the customer with the LARGEST and SMALLEST order quantity in one result

SELECT C1.CUSTOMER_ID, CUSTOMER_NAME, ORDERED_QUANTITY, 'Largest Quantity' QUANTITY

FROM CUSTOMER_T C1, ORDER_T O1, ORDER_LINE_T Q1

WHERE C1.CUSTOMER_ID = O1.CUSTOMER_ID AND O1.ORDER_ID = Q1.ORDER_ID

AND ORDERED_QUANTITY = (SELECT MAX(ORDERED_QUANTITY) FROM ORDER_LINE_T)

UNION

SELECT C1.CUSTOMER_ID, CUSTOMER_NAME, ORDERED_QUANTITY, 'Smallest Quantity'

FROM CUSTOMER_T C1, ORDER_T O1, ORDER_LINE_T Q1

WHERE C1.CUSTOMER_ID = O1.CUSTOMER_ID AND O1.ORDER_ID = Q1.ORDER_ID

AND ORDERED_QUANTITY = (SELECT MIN(ORDERED_QUANTITY) FROM ORDER_LINE_T)

ORDER BY ORDERED_QUANTITY;

UNION Rule Detail

Same column count Both SELECT statements must produce the same number of columns.

Compatible data types Corresponding columns must have compatible types.

Deduplication UNION removes duplicates automatically. Use UNION ALL to preserve

duplicates.

Column names Result column names come from the first SELECT statement.

ORDER BY Appears only once, after the last SELECT; applies to the entire combined result.

8.11 Conditional Expressions — CASE

-- CASE syntax (SQL:1999+)

CASE expression

WHEN expression THEN {expression | NULL} ...

| WHEN predicate THEN {expression | NULL} ...

[ELSE {expression | NULL}]

END

-- Practical example: classify products by price tier

SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE,

CASE

WHEN STANDARD_PRICE < 300 THEN 'Budget'

WHEN STANDARD_PRICE < 600 THEN 'Mid-Range'

WHEN STANDARD_PRICE >= 600 THEN 'Premium'

ELSE 'Unpriced'

END AS PRICE_TIER

SQL Master Study Guide — Chapters 7 & 8 Page 20

 FROM PRODUCT_T;

NULLIF(a,b) returns NULL if a=b, else returns a. COALESCE(a,b,c…) returns the first non-NULL value —
powerful for null handling.

8.12 Transaction Integrity

Transaction Definition
A discrete, logical unit of work processed completely or not at all — the "all-or-nothing" principle.
Maintains ACID properties:

• Atomicity — all or nothing

• Consistency — data remains valid
• Isolation — concurrent transactions do not interfere
• Durability — committed changes persist after system failure

BEGIN TRANSACTION;

INSERT Order_ID, Order_date, Customer_ID INTO Order_t;

INSERT Order_ID, Product_ID, Quantity INTO Order_line_t;

INSERT Order_ID, Product_ID, Quantity INTO Order_line_t;

INSERT Order_ID, Product_ID, Quantity INTO Order_line_t;

END TRANSACTION;

COMMIT; -- All INSERTs succeeded → make changes permanent

ROLLBACK; -- Any INSERT failed → cancel ALL changes since BEGIN

8.13 Data Dictionary Facilities

Every RDBMS maintains system tables called the Data Dictionary (System Catalog) that store metadata
— data about data. Users can query but not directly modify these tables.

Oracle 10g Data Dictionary Views

View Name Contents

DBA_TABLES All tables: owner, name, tablespace, row count estimates

DBA_CONSTRAINTS All constraints: PK, FK, UNIQUE, CHECK, NOT NULL — names, types, tables

DBA_USERS All database users: account name, creation date, default tablespace

MS SQL Server System Tables

SQL Master Study Guide — Chapters 7 & 8 Page 21

 Table Name Contents

SYSCOLUMNS Table and column definitions — data types, lengths, nullability, defaults

SYSDEPENDS Object dependency relationships based on foreign keys

SYSPERMISSIONS Access permissions (GRANT/REVOKE records)

8.14 Routines and Triggers

8.14.1 Routines (Stored Programs)

Routines are named, stored program modules residing on the database server. They encapsulate
business logic at the database layer.

• Functions: Accept input parameters, perform computation, return a single value. Usable inline in
SQL expressions.
• Stored Procedures: Perform a set of operations, do NOT return a value via RETURN (may use
OUT parameters). Called via CALL or EXECUTE.

8.14.2 Triggers

Trigger Definition
A stored routine that automatically executes in response to a specific database event — INSERT,
UPDATE, or DELETE on a specified table. Unlike stored procedures (called explicitly), triggers are
event-driven — they fire implicitly when the triggering event occurs.

8.14.3 Stored Procedures vs. Triggers

Dimension Stored Procedures Triggers

Invocation Called explicitly by app or user Automatic on DML event

(CALL/EXEC) (INSERT/UPDATE/DELETE)

Execution Type Explicit Implicit / Event-driven

Return Value Can return via OUT params or result sets No return value; performs side-effect
action

Parameters Accept IN, OUT, INOUT parameters Access NEW and OLD virtual rows

Timing On demand BEFORE, AFTER, or INSTEAD OF the

event

Performance Predictable — called only when needed Adds overhead to every triggering DML

Best Use Case Complex logic, batch, multi-step Audit logging, cascading rules, derived
transactions columns

SQL Master Study Guide — Chapters 7 & 8 Page 22

 8.14.4 Trigger Syntax — SQL:2003
CREATE TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} {INSERT | DELETE | UPDATE} ON table_name

[FOR EACH {ROW | STATEMENT}]

[WHEN (search_condition)]

-- Example: audit log whenever a product price changes

CREATE TRIGGER trg_price_audit

AFTER UPDATE ON PRODUCT_T

FOR EACH ROW

WHEN (NEW.STANDARD_PRICE <> OLD.STANDARD_PRICE)

INSERT INTO PRICE_AUDIT_T

VALUES (OLD.PRODUCT_ID, OLD.STANDARD_PRICE, NEW.STANDARD_PRICE, SYSDATE);

Clause Explanation

BEFORE / AFTER Fires before/after the DML. BEFORE can modify data being inserted/updated.

INSTEAD OF Replaces the DML entirely — used mainly with non-updatable views.

FOR EACH ROW Fires once per affected row (row-level). FOR EACH STATEMENT fires once per DML.

WHEN Optional filter — trigger body executes only when condition is true.

NEW / OLD Virtual rows. NEW = values being inserted or new update values. OLD = values before
change.

8.14.5 Stored Routine Syntax — SQL:2003

{CREATE PROCEDURE | CREATE FUNCTION} routine_name

([parameter [{,parameter} ...]])

[RETURNS data_type] -- Functions only

[LANGUAGE {SQL | C | ADA | COBOL | ...}]

[DETERMINISTIC | NOT DETERMINISTIC]

[NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA]

routine_body;

8.15 Embedded and Dynamic SQL

SQL Master Study Guide — Chapters 7 & 8 Page 23

 Type Description Advantages Disadvantages

Embedded Hard-coded SQL inside a host Type-safe, precompiled, Inflexible — SQL cannot
SQL language (C, Java, COBOL). good performance. change at runtime without
Pre-processed by a precompiler. recompiling.
Fixed at compile time.

Dynamic SQL SQL constructed and compiled at Highly flexible — query Performance overhead;
runtime based on user input or structure can vary based risk of SQL injection if
application state. on input. input not sanitised.

8.16 SQL:1999 & SQL:2003 Enhancements

Feature Standard Description

User-Defined Types (UDT) SQL:1999 Custom data types as subclasses or complex object types. Enables
object-relational capabilities.

Analytical / OLAP SQL:1999 CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROW_NUMBER.

Functions WINDOW clause for running totals over partitioned data.

BIGINT SQL:2003 64-bit integer for very large numbers (up to ~9.2×10¹■).

MULTISET SQL:2003 Collection type allowing multiple values (including duplicates) in a

single column.

XML SQL:2003 Native XML data type with XQuery support for XML document
navigation.

CREATE TABLE LIKE SQL:2003 Creates a new table with the same structure as an existing table.

MERGE Statement SQL:2003 Combines INSERT and UPDATE into one atomic "upsert" operation.

IDENTITY Columns SQL:2003 GENERATED ALWAYS AS IDENTITY for auto-incrementing primary

key values.

SQL/PSM SQL:1999 Persistent Stored Modules — complete procedural extension with IF,
LOOP, WHILE, FOR.

Object-Relational SQL:1999 Typed tables, reference types, inheritance, row types, and array types.

Vendor implementations: Oracle → PL/SQL; Microsoft SQL Server → T-SQL; PostgreSQL → PL/pgSQL,
PL/Python, PL/Perl.

8.17 Master Comparison Reference

Topic Location Key Rule

Equi-Join vs. Natural Join vs. Outer Section 8.1.2 Equi = equality match; Natural = no duplicate cols; Outer
Join = includes unmatched rows.

Correlated vs. Non-Correlated Section 8.6.2 Non-correlated runs once (efficient); correlated runs per
Subqueries row (powerful for EXISTS).

SQL Master Study Guide — Chapters 7 & 8 Page 24

 Topic Location Key Rule

WHERE vs. HAVING Section 7.6.8 WHERE filters rows before grouping; HAVING filters
groups after GROUP BY.

Base vs. Dynamic vs. Materialized Section 7.7.1 Base stores data; Dynamic is always current virtual;
Views Materialized is OLAP snapshot.

Stored Procedures vs. Triggers Section 8.14.3 Procedures called explicitly; triggers fire automatically on
DML events.

LIKE vs. IN Operators Section 7.7.5 LIKE does wildcard pattern matching; IN tests exact
membership in a list.

8.18 Quick-Reference: Pine Valley Furniture

Schema
CUSTOMER_T
CUSTOMER_ID NUMBER(11,0) NOT NULL [PK]
CUSTOMER_NAME VARCHAR2(25) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(30)
CITY VARCHAR2(20), STATE VARCHAR2(2), POSTAL_CODE VARCHAR2(9)

ORDER_T
ORDER_ID NUMBER(11,0) NOT NULL [PK]
ORDER_DATE DATE DEFAULT SYSDATE
CUSTOMER_ID NUMBER(11,0) [FK → CUSTOMER_T]

PRODUCT_T
PRODUCT_ID INTEGER NOT NULL [PK]
PRODUCT_DESCRIPTION VARCHAR2(50)
PRODUCT_FINISH VARCHAR2(20) CHECK IN (Cherry, Natural Ash, White Ash, Red Oak,
Natural Oak, Walnut)
STANDARD_PRICE DECIMAL(6,2), PRODUCT_LINE_ID INTEGER

ORDER_LINE_T
ORDER_ID NUMBER(11,0) NOT NULL [PK, FK → ORDER_T]
PRODUCT_ID NUMBER(11,0) NOT NULL [PK, FK → PRODUCT_T]
ORDERED_QUANTITY NUMBER(11,0)
Composite PK: (ORDER_ID, PRODUCT_ID)

SQL Master Study Guide — Chapters 7 & 8 Page 25