# DBMS Lab: SQL Query Reference (Day 1 to Day 7)

A consolidated walkthrough of every query from the lab file. For each one you get the **question**, the **query**, a plain-English **explanation** of what it does, and, wherever the same problem was solved more than one way, **how the approaches differ**.

## Contents

- [How to read this document](#how-to-read-this-document)
- [Tables at a glance](#tables-at-a-glance)
- [Day 1: Basic SELECT, WHERE, ORDER BY](#day-1-basic-select-where-order-by)
- [Day 2: Aggregates, GROUP BY, HAVING, simple subqueries](#day-2-aggregates-group-by-having-simple-subqueries)
- [Day 3: Set operators and more subqueries](#day-3-set-operators-and-more-subqueries)
- [Day 4: Constraint violations, cartesian products, first multi-table queries](#day-4-constraint-violations-cartesian-products-first-multi-table-queries)
- [Day 5: Multi-table joins (comma-style) and UNION patterns](#day-5-multi-table-joins-comma-style-and-union-patterns)
- [Day 6: Division, ranking and "every / exactly" problems](#day-6-division-ranking-and-every--exactly-problems)
- [Day 7: ANSI joins, outer joins, self-joins](#day-7-ansi-joins-outer-joins-self-joins)
- [Day 7 (continued): Day 6 problems re-solved with joins](#day-7-continued-day-6-problems-re-solved-with-joins)
- [Summary: problems solved more than one way](#summary-problems-solved-more-than-one-way)
- [Recurring patterns worth remembering](#recurring-patterns-worth-remembering)

---

## How to read this document

- **Dialect.** The queries use Oracle SQL (`MINUS`, `ROWNUM`, `TO_DATE`, `SYSDATE`, nested aggregates like `AVG(SUM(...))`). Where standard SQL differs, I say so.
- **Verbatim queries.** Queries are copied exactly as written in the lab file, including your inline comments. Two cosmetic exceptions: extra blank spaces inside Day 2 Q12 were trimmed, and a stray `2` line inside a Day 7 query (a SQL\*Plus line-number paste artefact) was removed. Both are flagged where they occur.
- **Numbering.** Question numbers (1 to 56) come from your file. Day 4 and the first half of Day 7 are unnumbered in the source, so I've labelled them **4A-1, 4B-1, 7A-1** and so on.
- **"On the lab data" results.** I re-ran equivalent queries on the sample rows from your INSERT statements to check my descriptions. Oracle-only syntax was translated for the check (e.g. `MINUS` to `EXCEPT`). Treat these results as a sanity check and confirm against your own database.
- **Heads-up blocks** mark places where a query doesn't exactly match its question, contains a typo, or would raise an error.

## Tables at a glance

| Table | Columns | Keys and constraints |
| --- | --- | --- |
| `PRODUCT` | productno, productdesc, productfinish, unitprice, qtyonhand | PK `productno`; `productdesc` NOT NULL; `unitprice > 0`; `qtyonhand` NUMERIC(3) default 0 |
| `CUSTOMER` | customerid, customername, ccity, cstate, caddress, czip | PK `customerid`; `customername` NOT NULL; `czip` between 100000 and 999999 |
| `ORDERS` | orderid, orderdate, customerid | PK `orderid`; FK `customerid` to CUSTOMER |
| `REQUESTS` | productno, orderno, quantity | PK (`productno`, `orderno`); FKs to PRODUCT and ORDERS |

**Relationships:** one CUSTOMER places many ORDERS; one ORDER contains many REQUESTS (line items); each REQUEST points to one PRODUCT.

> **Note from your file:** `orderno` in `REQUESTS` is the same thing as `orderid` in `ORDERS`. That is why joins read `o.orderid = r.orderno`.

---

## Day 1: Basic SELECT, WHERE, ORDER BY

### Q1. List all products

```sql
select *
from product;
```

**What it does:** `*` means "every column", and with no `WHERE` clause every row comes back. This is the full contents of `PRODUCT`.

---

### Q2. List all product names and prices (unitprice)

```sql
select productno, productdesc, unitprice
from product;
```

**What it does:** A projection. Only the three named columns are returned, for every row. `productno` is included alongside the description so that products sharing a name (e.g. the two 48" Bookcases) can be told apart.

---

### Q3. List all product names whose product prices > Rs.200

```sql
select productno, productdesc, unitprice
from product
where unitprice>200;
```

**What it does:** Adds a row filter. `WHERE` is evaluated per row, and only products priced strictly above 200 survive (a product priced exactly 200 is excluded).

---

### Q4. List all product names whose product prices > Rs.200 and qty_on_hand > 5

```sql
select productno, productdesc, unitprice, qtyonhand
from product
where unitprice>200 and qtyonhand>5;
```

**What it does:** Two conditions joined with `AND`, so a row must satisfy both: price above 200 *and* more than 5 units in stock.

---

### Q5. List all product names whose product prices > Rs.200 and qty_on_hand between 1 and 5

```sql
select productno, productdesc, unitprice, qtyonhand
from product
where unitprice>200 and qtyonhand>1 and qtyonhand <5;
```

**What it does:** Filters on price above 200 and a quantity range.

> **Heads-up:** `qtyonhand>1 and qtyonhand<5` is an *exclusive* range (2, 3 or 4). SQL's `BETWEEN 1 AND 5` is *inclusive* (1 to 5). To match the question literally, use `qtyonhand between 1 and 5`. On the lab data the two versions return different rows because some products have quantity exactly 5.

---

### Q6. List all products with finish = "Oak"

```sql
select productno, productdesc, productfinish
from product
where productfinish='Oak';
```

**What it does:** Simple equality filter on a text column. String literals use single quotes and comparisons are case-sensitive in Oracle (`'oak'` would not match `'Oak'`).

---

### Q7. List all products with finish = "Oak" or "Walnut"

```sql
select productno, productdesc, productfinish
from product
where productfinish='Oak' or productfinish='Walnut';
```

**What it does:** `OR` returns a row if either condition holds. Equivalent shorthand: `WHERE productfinish IN ('Oak','Walnut')`.

---

### Q8. List all products with finish = "Oak" or unitprice >= 300

```sql
select productno, productdesc, productfinish, unitprice
from product
where productfinish='Oak' and unitprice>=300;
```

**What it does:** As written, returns Oak products that *also* cost 300 or more.

> **Heads-up:** The question says **or**, but the query uses **and**. With `AND` you get only the expensive Oak items (on the lab data: products 3, 5, 11, 12). With `OR` you would additionally get every non-Oak product priced at 300 or above (e.g. products 4, 6, 14). Change `and` to `or` to match the question.

---

### Q9. List all product names and prices arranged by prices

```sql
select productno, productdesc, unitprice
from product
order by unitprice;
```

**What it does:** `ORDER BY` sorts the output. Without `ASC` or `DESC` the default is ascending, so the cheapest product comes first.

---

### Q10. List all product names and prices whose finish is "Oak" arranged by names in reverse order

```sql
select productno, productdesc, productfinish, unitprice
from product
where productfinish='Oak'
order by productdesc desc;
```

**What it does:** Filters to Oak products first, then sorts those by description in descending (Z to A) order. `WHERE` always runs before `ORDER BY`.

---

## Day 2: Aggregates, GROUP BY, HAVING, simple subqueries

### Q11. List all distinct Bookcases

```sql
SELECT DISTINCT * FROM PRODUCT WHERE PRODUCTDESC LIKE '%Bookcase%';
```

**What it does:** `LIKE '%Bookcase%'` matches any description containing the word "Bookcase" (`%` = any run of characters). `DISTINCT` removes duplicate result rows.

> **Heads-up:** Because `SELECT *` includes `productno` (a primary key), every row is already unique, so `DISTINCT` has no effect here. You get all four bookcase rows (products 7, 8, 9, 10). If "distinct Bookcases" means distinct *types*, use `SELECT DISTINCT PRODUCTDESC ...`, which collapses to two rows: the 48" and 96" bookcases.

---

### Q12. List the product prices if they are increased by 10%

```sql
SELECT PRODUCTDESC, UNITPRICE AS INC10UP , UNITPRICE * 1.10 AS NEWPRICE FROM PRODUCT;
```

*(extra whitespace in the source line trimmed)*

**What it does:** Shows each product's current price next to a computed price that is 10% higher (`price * 1.10`). It is read-only: nothing in the table is changed. Making the change permanent would need an `UPDATE`.

> **Heads-up:** The alias `INC10UP` labels the *original* `UNITPRICE` column, which is a misleading name. Something like `AS OLDPRICE` would be clearer. `NEWPRICE` is the actual 10%-increased value.

---

### Q13. How many products are there?

```sql
SELECT COUNT(*) AS TOTAL FROM PRODUCT;
```

**What it does:** `COUNT(*)` is an aggregate that collapses the whole table into one row holding the number of rows. `AS TOTAL` names the output column.

---

### Q14. For every product finish, find out the total quantity of products

```sql
SELECT PRODUCTFINISH, SUM(QTYONHAND) AS TOTALQUANTITY FROM PRODUCT GROUP BY PRODUCTFINISH;
```

**What it does:** `GROUP BY` puts rows with the same finish into one bucket, and `SUM(QTYONHAND)` adds up stock inside each bucket. You get one output row per distinct finish. Every non-aggregated column in `SELECT` must appear in `GROUP BY`.

---

### Q15. List every product finish having unit price > 200 and whose average of qtyonhand greater than 3

```sql
SELECT PRODUCTFINISH FROM PRODUCT WHERE UNITPRICE > 200 GROUP BY PRODUCTFINISH HAVING AVG(QTYONHAND) > 3;
```

**What it does:** Uses **both** row-level and group-level filtering:

1. `WHERE UNITPRICE > 200` discards cheap products *before* grouping.
2. `GROUP BY` forms a group per finish from the remaining rows.
3. `HAVING AVG(QTYONHAND) > 3` keeps only groups whose average stock exceeds 3.

The important detail is that the average is computed **only over products priced above 200**, because the `WHERE` already removed the others. `WHERE` filters rows; `HAVING` filters groups.

---

### Q16. List product name(s) having maximum quantity on hand

```sql
SELECT PRODUCTDESC, QTYONHAND FROM PRODUCT WHERE QTYONHAND = (SELECT MAX(QTYONHAND) FROM PRODUCT);
```

**What it does:** The inner query returns a single number, the highest `qtyonhand`. The outer query returns every product whose quantity equals it. Using `=` with a subquery (instead of `ORDER BY ... FETCH FIRST 1`) means ties are all returned. On the lab data this is the End table (8 units).

---

### Q17. Find out value of inventory for every product finish

```sql
SELECT PRODUCTFINISH, SUM(UNITPRICE * QTYONHAND) AS INVENTORYVALUE FROM PRODUCT GROUP BY PRODUCTFINISH;
```

**What it does:** Multiplies price by stock for each product (the money tied up in that item), then sums it per finish. Compare with Q14: same grouping, but the aggregated expression is `price * quantity` instead of just quantity.

---

### Q18. List all products whose unitprice is same as some made of 'Oak'

```sql
SELECT * FROM PRODUCT WHERE UNITPRICE IN (SELECT UNITPRICE FROM PRODUCT WHERE PRODUCTFINISH = 'Oak');
```

**What it does:** The subquery produces a *list* of all prices charged for Oak products. The outer query keeps any product whose price appears in that list (`IN` means "equal to some member"). Note that Oak products themselves qualify (their own price is in the list), and so do non-Oak products that happen to share an Oak price, such as the Coffee table at 200 (the 96" Oak bookcase is also 200).

---

### Q19. List product name(s) having qty on hand more than the average qty on hand of products

```sql
SELECT PRODUCTDESC FROM PRODUCT WHERE QTYONHAND > (SELECT AVG(QTYONHAND) FROM PRODUCT);
```

**What it does:** A scalar subquery computes one number, the average stock across all products, and the outer query keeps products above it. Aggregates can't appear directly in `WHERE`, which is exactly why the subquery is needed.

---

### Q20. List products whose unit price is greater than every unitprice of different productfinish

```sql
SELECT * FROM PRODUCT P1 WHERE UNITPRICE > ALL (
    SELECT UNITPRICE 
    FROM PRODUCT P2 
    WHERE P2.PRODUCTFINISH <> P1.PRODUCTFINISH
);
```

**What it does:** A **correlated subquery**. For each product `P1`, the inner query lists the prices of all products with a *different* finish. `> ALL` requires `P1`'s price to beat every price in that list. On the lab data only the 8-Drawer Oak dresser (800) qualifies.

**Contrast with Q18:** `IN` / `= ANY` means "matches at least one value in the list"; `> ALL` means "beats every value in the list".

---

## Day 3: Set operators and more subqueries

### Q21. List all products which are either made of "Oak" or which has qtyonhand > 2 (without using OR/AND clause)

```sql
SELECT * FROM Product WHERE productfinish = 'Oak'
UNION
SELECT * FROM Product WHERE qtyonhand > 2;
```

**What it does:** Runs two separate queries and merges their results with `UNION`. Since `OR` and `AND` are banned, the "either/or" logic is achieved by set union. `UNION` also removes duplicates, so an Oak product with more than 2 in stock appears once, not twice. The result is logically the same as `WHERE productfinish='Oak' OR qtyonhand > 2`.

---

### Q22. List the products which are not made of "Oak" or "Walnut"

```sql
SELECT * FROM Product 
WHERE productfinish NOT IN ('Oak', 'Walnut');
```

**What it does:** `NOT IN` excludes rows whose finish matches any value in the list. Only Cherry, Birch and Maple products remain.

> **Watch out:** a product whose `productfinish` is `NULL` would *not* be returned, because `NULL NOT IN (...)` evaluates to "unknown", not true. Not an issue with the lab data, but a classic trap.

---

### Q23. Find the product(s) with the second highest unit price

```sql
SELECT * FROM Product
WHERE unitprice = (
    SELECT MAX(unitprice) 
    FROM Product 
    WHERE unitprice < (SELECT MAX(unitprice) FROM Product)
);
```

**What it does:** Three nested layers, read from the inside out:

1. Innermost: the highest price overall (800).
2. Middle: the highest price among products cheaper than that, i.e. the *second* highest distinct price (750).
3. Outer: every product with that price.

Ties are handled naturally. On the lab data this returns the 8-Drawer Birch dresser (750).

---

### Q24. Find the most expensive product in each finish

```sql
SELECT * FROM Product p
WHERE unitprice = (
    SELECT MAX(unitprice) 
    FROM Product 
    WHERE productfinish = p.productfinish
);
```

**What it does:** A **correlated subquery**. For each product `p`, the inner query finds the maximum price *within p's own finish* (the link is `productfinish = p.productfinish`). The product is kept only if its price equals that maximum. A plain `GROUP BY` could give the max price per finish but not the full product row, which is why this pattern is used. If two products tie for the top in a finish, both appear.

---

### Q25. Find products having the same finish as the most expensive product

```sql
SELECT * 
FROM Product 
WHERE productfinish = (
    SELECT productfinish 
    FROM Product 
    WHERE unitprice = (SELECT MAX(unitprice) FROM Product)
);
```

**What it does:** Innermost finds the top price (800). The middle query finds that product's finish (Oak). The outer query returns every product with that finish. Note it is a *non-correlated* chain: each layer runs once.

> **Watch out:** if two products with *different* finishes tied for the top price, the middle subquery would return two values and `=` would fail. `IN` would be the safer operator.

---

### Q26. Find the finish having the highest average unit price

```sql
SELECT productfinish
FROM Product
GROUP BY productfinish
HAVING AVG(unitprice) >= ALL (
    SELECT AVG(unitprice) 
    FROM Product 
    GROUP BY productfinish
);
```

**What it does:** Groups by finish, then keeps only the group(s) whose average price is greater than or equal to *every* group's average (including its own). That is the maximum, found without ever writing `MAX(AVG(...))`. On the lab data: Birch (about 416.67).

**Pattern:** `>= ALL` = "is the max"; `<= ALL` = "is the min". Ties are all returned.

---

### Q27. Find the finish that has the maximum number of products

```sql
SELECT productfinish
FROM Product
GROUP BY productfinish
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*) 
    FROM Product 
    GROUP BY productfinish
);
```

**What it does:** Same `>= ALL` pattern as Q26, but the measure is the *number of products* per finish. On the lab data: Oak (6 products).

---

### Q28. Find the finish with the lowest total inventory value (unitprice × qtyonhand)

```sql
SELECT productfinish
FROM Product
GROUP BY productfinish
HAVING SUM(unitprice * qtyonhand) <= ALL (
    SELECT SUM(unitprice * qtyonhand) 
    FROM Product 
    GROUP BY productfinish
);
```

**What it does:** Same shape again, flipped to `<= ALL` to find the *minimum*, using the Q17 measure (`SUM(price * qty)`) per finish. On the lab data: Walnut (1,650).

---

### Q29. Find products whose finish has more than two products

```sql
SELECT * FROM Product 
WHERE productfinish IN (
    SELECT productfinish 
    FROM Product 
    GROUP BY productfinish 
    HAVING COUNT(*) > 2
);
```

**What it does:** The subquery returns the list of "popular" finishes (those with 3 or more products: Birch and Oak). The outer query then lists every product wearing one of those finishes. This is the standard trick for combining group-level facts with row-level output: find the qualifying groups in a subquery, then join back with `IN`.

---

### Q30. Find products whose price is above the average price of their finish

```sql
SELECT * FROM Product p
WHERE unitprice > (
    SELECT AVG(unitprice) 
    FROM Product 
    WHERE productfinish = p.productfinish
);
```

**What it does:** Correlated subquery, the same structure as Q24 but comparing against `AVG` instead of `MAX`. Each product is compared with the average of *its own finish*, not the global average (contrast with Q19, which used the global average).

---

### Q31. Find products that have the minimum price among products with quantity greater than zero

```sql
SELECT * FROM Product 
WHERE unitprice = (
    SELECT MIN(unitprice) 
    FROM Product 
    WHERE qtyonhand > 0
);
```

**What it does:** The inner query looks only at in-stock products (`qtyonhand > 0`) and finds the lowest price among them. The outer query returns products with that price.

> **Subtle point:** the outer query has no stock filter of its own. A product with zero stock that happens to have the same price would also be returned.

---

## Day 4: Constraint violations, cartesian products, first multi-table queries

Day 4 is unnumbered in the source. It has three parts: **4A**, statements that deliberately break constraints; **4B**, questions about the output of cross joins and joins; **4C**, three subquery questions.

### Part 4A: Constraint violations

Each statement below is designed to be **rejected** by the database. Typical Oracle errors: `ORA-00001` (unique/PK violated), `ORA-01400` (cannot insert NULL), `ORA-02290` (check constraint violated), `ORA-02291` (parent key not found).

#### 4A-1. Duplicate primary key on CUSTOMER

```sql
-- Constraint Violation
INSERT INTO CUSTOMER (customerid, customername, ccity, cstate, caddress, czip) 
VALUES ('1', 'Test Name', 'New York', 'NY', '123 St', '10001');
```

**What it does:** Tries to add a customer with `customerid` 1, but customer 1 (Contemporary Casuals) already exists, so the **primary key** rejects the duplicate.

> **Heads-up:** The zip `'10001'` is a 5-digit value, which also breaks the `czip BETWEEN 100000 AND 999999` check constraint. The statement fails either way; which error you see depends on which constraint Oracle checks first.

#### 4A-2. NULL primary key

```sql
INSERT INTO PRODUCT (productno, productdesc) VALUES (NULL, 'Gaming Chair');
```

**What it does:** Primary keys can never be NULL, so this fails (`ORA-01400`).

#### 4A-3. NOT NULL violation

```sql
-- Violates NOT NULL on productdesc
INSERT INTO PRODUCT (productno, productdesc) VALUES (1, NULL);
```

**What it does:** `productdesc` was declared `NOT NULL`, so inserting NULL is refused. (Product number 1 also already exists, so the PK would object as well.)

#### 4A-4. CHECK violation on price

```sql
-- Violates CHECK (unitprice > 0)
INSERT INTO PRODUCT (productno, productdesc, unitprice) 
VALUES (2, 'Desk Lamp', -5.00);
```

**What it does:** `unitprice` has `CHECK (unitprice > 0)`, and -5.00 fails it. (Product 2 also already exists.)

#### 4A-5. Foreign key violation on ORDERS

```sql
-- Violates Foreign Key because customerid 9999 does not exist in CUSTOMER
INSERT INTO ORDERS (orderid, orderdate, customerid) 
VALUES (5001, SYSDATE, 9999);
```

**What it does:** An order must belong to an existing customer. There is no customer 9999, so the foreign key rejects the row (`ORA-02291`, parent key not found). `SYSDATE` supplies today's date.

#### 4A-6. Composite primary key on REQUESTS

```sql
-- Violation: Attempting to insert the exact same pair again
INSERT INTO requests (productno, orderno, quantity) VALUES (10, 30, 2);
```

**What it does:** The intent is to demonstrate that the *pair* (`productno`, `orderno`) must be unique.

> **Heads-up:** The pair (10, 30) does not exist in the table, and there is **no order 30** in `ORDERS`. So the statement actually fails on the **foreign key** (parent order not found), not the primary key. To trigger a genuine composite-PK violation, repeat an existing pair, e.g. `(10, 1, 2)`.

#### 4A-7. CHECK violation on zip range

```sql
--Violation: Attempting to insert to CZIP with pincode larger than 999999
INSERT INTO CUSTOMER (customerid, customername, ccity, cstate, caddress, czip)
VALUES (999, 'Test Failure User', 'Test City', 'TS', '123 Test St', 99999);
```

**What it does:** Breaks the constraint `chk_customer_czip_range` (`czip BETWEEN 100000 AND 999999`), so the row is rejected.

> **Heads-up:** The comment says "larger than 999999", but `99999` (five digits) is *smaller* than 100000. It's still a violation, just on the low side. For a "too large" example, use something like `1000000`.

#### 4A-8. Numeric precision overflow

```sql
INSERT INTO PRODUCT (productno, productdesc, qtyonhand) 
VALUES (3, 'Bookshelf', 1250);
```

**What it does:** `qtyonhand` is `NUMERIC(3)`, which holds at most 999. The value 1250 overflows that precision and Oracle raises `ORA-01438` (value larger than specified precision). Product 3 also already exists, so the PK would object too.

---

### Part 4B: Cross joins and joins (predict-the-output questions)

#### 4B-1. Is the following SQL statement valid? What output will be displayed?

```sql
SELECT *
FROM CUSTOMER, CUSTOMER;
```

**Answer:** **Not valid.** The same table appears twice in `FROM` with no aliases, so every column name (`customerid`, `customername`, ...) is ambiguous: the database can't tell which copy you mean. Oracle rejects it with an ambiguity error and displays no output. The fix is to alias each copy (next query).

#### 4B-2. What will be the output for `SELECT * FROM CUSTOMER C1, CUSTOMER C2`?

```sql
--What will be the output for
--SELECT *
--FROM CUSTOMER C1, CUSTOMER C2;

SELECT *
FROM CUSTOMER C1, CUSTOMER C2;
```

**Answer:** Valid. With no `WHERE` condition this is a **Cartesian product** (cross join): every row of `C1` is paired with every row of `C2`. With 6 customers that is 6 × 6 = **36 rows**, and each row has **12 columns** (6 from each copy).

#### 4B-3. Same, with a join condition on customerid

```sql
--What will be the output for
--SELECT *
--FROM CUSTOMER C1, CUSTOMER C2
--WHERE C1.CUSTOMERID=C2.CUSTOMERID;

SELECT *
FROM CUSTOMER C1, CUSTOMER C2
WHERE C1.CUSTOMERID=C2.CUSTOMERID;
```

**Answer:** A **self-join**. Of the 36 pairs from 4B-2, only those where both sides have the same `customerid` survive, meaning each customer is matched with *itself*. Result: **6 rows**, 12 columns, where the two halves of each row are identical.

#### 4B-4. CUSTOMER joined with ORDERS

```sql
--What will be the output for
--SELECT *
--FROM CUSTOMER C, ORDERS O
--WHERE C.CUSTOMERID = O.CUSTOMERID;
SELECT *
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTOMERID = O.CUSTOMERID;
```

**Answer:** An **equi-join** (inner join). Each order is paired with its customer's details. There are 5 orders, so **5 rows** with 6 + 3 = **9 columns**. Customer 5 (Impressions) has placed no orders and therefore does **not** appear.

---

### Part 4C: Subquery questions

> **Disclaimer from your file:** "According to my database, orderno IS orderid in table ORDERS."

#### 4C-1. List all product descriptions ordered in orderno 1

```sql
--List all product descriptions ordered in orderno1
SELECT productdesc 
FROM PRODUCT 
WHERE productno IN (
    SELECT productno 
    FROM requests 
    WHERE orderno = 1
);
```

**What it does:** The subquery finds the product numbers on order 1 (products 4, 5, 10). The outer query turns those numbers into descriptions: Entertainment Center, Writer's Desk and the 96" Bookcase. This avoids joining the tables; `IN` does the matching.

#### 4C-2. Every request where quantity ordered exceeds the average quantity ordered for that product

```sql
--List every request where qty ordered is more than the average quantity ordered for the product
SELECT R.* 
FROM requests R 
WHERE R.quantity > (
    SELECT AVG(R2.quantity) 
    FROM requests R2 
    WHERE R2.productno = R.productno
);
```

**What it does:** A correlated subquery. For each request row `R`, the inner query averages the quantity of **all requests for the same product** (`R2.productno = R.productno`). The row is kept if its own quantity beats that per-product average. Products ordered only once (or always in the same amount) can never qualify, because their quantity equals their average. On the lab data three requests qualify: (order 1, product 10, qty 2), (order 4, product 8, qty 3) and (order 4, product 14, qty 5).

#### 4C-3. Names of all customers who have placed an order

```sql
--List the names of all customers who have placed an order
SELECT customername 
FROM CUSTOMER 
WHERE customerid IN (
    SELECT customerid 
    FROM ORDERS
);
```

**What it does:** The subquery lists every `customerid` that appears in `ORDERS`; the outer query returns the names of customers in that list. On the lab data that is five of the six customers (all except Impressions). Using `IN` (rather than a join) means a customer with several orders is still listed only once.

---

## Day 5: Multi-table joins (comma-style) and UNION patterns

From Day 5 onwards, tables are combined with the **old-style (comma) join**: list the tables in `FROM`, then put the join conditions in `WHERE`. (Day 7 introduces the modern `JOIN ... ON` syntax for the same idea.)

### Q39. List the product descriptions which are ordered by customer named "Contemporary Casuals"

```sql
SELECT DISTINCT p.productdesc
FROM PRODUCT p, CUSTOMER c, ORDERS o, requests r
WHERE c.customername = 'Contemporary Casuals'
  AND c.customerid = o.customerid
  AND o.orderid = r.orderno
  AND r.productno = p.productno;
```

**What it does:** Follows the chain of relationships **CUSTOMER → ORDERS → REQUESTS → PRODUCT**:

- `c.customername = 'Contemporary Casuals'` picks the customer.
- `c.customerid = o.customerid` gets that customer's orders.
- `o.orderid = r.orderno` gets the line items on those orders.
- `r.productno = p.productno` looks up the product for each line item.

`DISTINCT` stops a product from being listed twice if it appears in several orders. On the lab data: End table, Writer's Desk, 8-Drawer dresser.

---

### Q40. List the order dates for every order made by customer "Value furniture"

```sql
SELECT o.orderdate
FROM ORDERS o, CUSTOMER c
WHERE c.customername = 'Value furniture'
  AND c.customerid = o.customerid;
```

**What it does:** A two-table join, because the customer's name lives in `CUSTOMER` but the dates live in `ORDERS`. No `DISTINCT` is used: "every order" means one row per order, even if two orders share a date.

---

### Q41. List every order which includes a request for a product made of "Oak"

```sql
SELECT DISTINCT o.orderid, o.orderdate, o.customerid
FROM ORDERS o, requests r, PRODUCT p
WHERE o.orderid = r.orderno
  AND r.productno = p.productno
  AND p.productfinish = 'Oak';
```

**What it does:** Joins orders to their line items and then to products, keeping only Oak products. `DISTINCT` is essential here: an order with two Oak items would otherwise appear twice. This is the "**at least one**" case. Compare with Q42, which asks for "**only**".

---

### Q42. List every order which includes request only for products made of "Oak"

**Version A: using MINUS (set difference)**

```sql
SELECT orderno 
FROM requests
MINUS
SELECT DISTINCT r.orderno
FROM requests r, PRODUCT p
WHERE r.productno = p.productno
  AND p.productfinish != 'Oak';
```

**What it does:** Start with *all* orders that have line items, then subtract the orders that contain **at least one non-Oak** product. Whatever remains has only Oak products. (`MINUS` is Oracle's name; standard SQL calls it `EXCEPT`.)

**Version B: using NOT EXISTS ("Sir's favorite way")**

```sql
-- Another way of doing the same (Sir's favorite way).  
SELECT DISTINCT o1.orderid
FROM ORDERS o1, requests r1
WHERE o1.orderid = r1.orderno
  AND NOT EXISTS (
    SELECT *
    FROM requests r2, product p
    WHERE r2.orderno = o1.orderid
    AND r2.productno = p.productno
    AND p.productfinish <> 'Oak'
  );
```

**What it does:** For each order `o1`, the correlated subquery searches for a line item on that order with a non-Oak product. `NOT EXISTS` keeps the order only if that search finds **nothing**. The outer join with `requests` ensures the order actually has line items (so empty orders don't sneak in).

**How they differ:** Both compute the same answer, "orders with no non-Oak lines". A is *set-based* (build two sets, subtract). B is *row-by-row logic* (for each order, prove that no counter-example exists). B's negative-existence style is the same technique that appears in Day 6's division problems. On the lab data both return **no rows**, since every order contains at least one non-Oak item.

---

### Q43. List the total quantity ordered for every product finish

```sql
-- Apparently this is wrong. 
SELECT p.productfinish, SUM(r.quantity) AS total_quantity
FROM PRODUCT p, requests r
WHERE p.productno = r.productno
GROUP BY p.productfinish


--Sir's "correct" way
UNION
SELECT DISTINCT productfinish, 0 AS total_quantity
FROM PRODUCT 
WHERE productfinish NOT IN (SELECT DISTINCT p2.productfinish FROM Product p2, requests r2 WHERE p2.productno = r2.productno);
```

*(This is one single statement; the comments in the middle are yours.)*

**What it does, in two stages:**

1. **The first SELECT** joins products to line items, groups by finish and sums the quantity ordered. This is only correct for finishes that have been ordered at least once, because a finish with no requests never survives the join. That's why you noted it was "apparently wrong".
2. **The UNION branch** adds back the missing finishes with a hard-coded total of `0`. It picks finishes that don't appear in any request (`NOT IN` against the set of ordered finishes).

**Together:** every finish appears, with either its real total or 0. On the lab data: Birch 10, Cherry 5, Maple 1, Oak 30, and **Walnut 0** (supplied by the UNION branch).

**Modern alternative (not in your file):** a `LEFT JOIN` with `COALESCE(SUM(r.quantity), 0)` does this in one query. You'll see the same idea in Day 7.

---

### Q44. List quantity on hand and quantity ordered for every product

```sql
SELECT p.productno, p.qtyonhand, SUM(r.quantity) AS qty_ordered
FROM Product p, requests r
WHERE p.productno = r.productno
GROUP BY p.productno, p.productdesc, p.qtyonhand
UNION
SELECT p.productno, p.qtyonhand, 0 AS qty_ordered
FROM Product p
WHERE p.productno NOT IN(SELECT r.productno
                          FROM requests r
                          );
```

**What it does:** The same "real numbers + zero-filled leftovers" structure as Q43, applied per product:

1. The first branch totals what has been ordered per product (only products that appear in `requests`).
2. The second branch adds every product that has **never** been ordered, with `qty_ordered = 0`.

`UNION` merges them so all 14 products appear. On the lab data, six products (2, 7, 9, 11, 12, 13) come from the second branch.

`productdesc` in the `GROUP BY` is unnecessary since it's not selected, but harmless. `NOT IN` is safe here because `requests.productno` can never be NULL (it's part of the primary key).

---

### Q45. List the product nos along with total ordered quantity for those products whose total ordered quantity is more than qty on hand

```sql
SELECT r.productno, SUM(r.quantity) AS total_ordered
FROM requests r, PRODUCT p
WHERE r.productno = p.productno
GROUP BY r.productno, p.qtyonhand
HAVING SUM(r.quantity) > p.qtyonhand;
```

**What it does:** Joins line items to their product, totals the quantity ordered per product, and then uses `HAVING` to keep only products where demand exceeds current stock (`SUM(...) > p.qtyonhand`). `p.qtyonhand` must be in the `GROUP BY` so it can be referenced in `HAVING`. On the lab data: products 5, 8 and 14.

---

### Q46. Same as Q45, but also show qty on hand

```sql
SELECT r.productno, SUM(r.quantity) AS total_ordered, p.qtyonhand
FROM requests r, PRODUCT p
WHERE r.productno = p.productno
GROUP BY r.productno, p.qtyonhand
HAVING SUM(r.quantity) > p.qtyonhand;
```

**How it differs from Q45:** Only the `SELECT` list changes: `p.qtyonhand` is now displayed as well. Logic and filtering are identical. Result on the lab data: (5, 20, 0), (8, 5, 2), (14, 6, 2).

---

## Day 6: Division, ranking and "every / exactly" problems

Day 6 introduces harder patterns: "customers who ordered **all** of X" (relational division), "the **largest** total", and "**exactly the same** set as". Many are solved in several ways, and Day 7 re-solves them with joins (see the [second half of Day 7](#day-7-continued-day-6-problems-re-solved-with-joins)).

### Q47. Find customers who ordered all products of Oak finish

**Version A: MINUS inside NOT EXISTS**

```sql
SELECT c.customerid, c.customername
FROM CUSTOMER c
WHERE NOT EXISTS (
    SELECT p.productno 
    FROM PRODUCT p 
    WHERE p.productfinish = 'Oak'
    MINUS
    SELECT r.productno 
    FROM ORDERS o, requests r
    WHERE o.orderid = r.orderno 
      AND o.customerid = c.customerid
);
```

**What it does:** For each customer, it builds two sets and subtracts them:

- Set 1: all Oak products.
- Set 2: all products this customer has ordered.

`Set 1 MINUS Set 2` is "Oak products the customer has *not* ordered". `NOT EXISTS` keeps the customer only if that leftover set is **empty**, i.e. no Oak product was missed.

**Version B: "Using double not exists"**

```sql
--Using double not exists
SELECT c.customerid, c.customername
FROM CUSTOMER c
WHERE NOT EXISTS (
    SELECT p.productno 
    FROM PRODUCT p 
    WHERE p.productfinish = 'Oak'
      AND NOT EXISTS (
          SELECT 1 
          FROM ORDERS o, requests r
          WHERE o.orderid = r.orderno 
            AND o.customerid = c.customerid
            AND r.productno = p.productno
      )
);
```

**What it does:** Reads as a double negative: "there is **no** Oak product for which there is **no** order by this customer". The inner `NOT EXISTS` tests whether the customer ordered product `p`; the outer one rejects a customer if even one Oak product fails that test.

**How A and B differ:** Same logic (relational division), different mechanism. A uses set subtraction (`MINUS`, Oracle-specific). B uses only `NOT EXISTS`, so it is portable to any SQL database and doesn't build sets.

**Version C (Day 7):** See [Q47 with joins](#q47-find-customers-who-ordered-all-products-of-oak-finish-1), which counts distinct Oak products instead.

> **On the lab data all versions return no rows**: no customer has ordered all six Oak products.

---

### Q48. Find products whose total ordered quantity is greater than the average quantity ordered per product

```sql
SELECT productno
FROM requests
GROUP BY productno
HAVING SUM(quantity) > (
    SELECT AVG(SUM(quantity)) 
    FROM requests 
    GROUP BY productno
);
```

**What it does:** Groups the line items by product and totals each product's ordered quantity. `HAVING` then compares each total to a subquery that computes the *average of those totals*. `AVG(SUM(quantity))` is a **nested aggregate**: first `SUM` per product, then `AVG` of those sums. Nesting aggregates like this is Oracle-specific; other databases need a derived table (as in the Day 7 version). On the lab data the average is 5.75, and products 5 (total 20) and 14 (total 6) exceed it.

---

### Q49. Find the customer who has ordered the largest total quantity

**Version A: ORDER BY + ROWNUM**

```sql
SELECT customerid, customername 
FROM (
    SELECT c.customerid, c.customername, SUM(r.quantity) AS total_qty
    FROM CUSTOMER c, ORDERS o, requests r
    WHERE c.customerid = o.customerid AND o.orderid = r.orderno
    GROUP BY c.customerid, c.customername
    ORDER BY total_qty DESC
)
WHERE ROWNUM = 1;
```

**What it does:** The inner query totals quantity per customer and sorts highest first. The outer query keeps only the first row (`ROWNUM = 1`), i.e. the top customer. The `ORDER BY` has to sit *inside* the subquery, because `ROWNUM` is assigned before an outer `ORDER BY` would run.

**Limitations:** `ROWNUM` is Oracle-specific, and it returns exactly **one** row even if two customers tie.

**Version B: "Trying a new way" (HAVING = MAX)**

```sql
--Trying a new way
SELECT o.customerid, SUM(r.quantity) AS total_qty
FROM ORDERS o, requests r
WHERE o.orderid = r.orderno
GROUP BY o.customerid
HAVING SUM(r.quantity) = (
  SELECT MAX(cust_total)
  FROM (
    SELECT SUM (r2.quantity) AS cust_total
    FROM ORDERS o2, requests r2
    WHERE o2.orderid = r2.orderno
    GROUP BY o2.customerid
    )
  );
```

**What it does:** Totals per customer, then keeps only groups whose total **equals the maximum** of all customer totals (computed by the nested subquery).

**How A and B differ:** B handles **ties** (all customers sharing the top total are returned) and is not tied to `ROWNUM`. However, it shows only `customerid` and the total, not the name. A shows the name but returns just one row.

**Version C (Day 7):** joins derived tables to give the name, total and tie-handling all together; see [Q49 with joins](#q49-find-the-customer-who-has-ordered-the-largest-total-quantity-1).

> On the lab data: customer 1, Contemporary Casuals, with a total of 17.

---

### Q50. Find products that were ordered by more than one customer

**Version A: GROUP BY + COUNT DISTINCT**

```sql
SELECT r.productno
FROM requests r, ORDERS o
WHERE r.orderno = o.orderid
GROUP BY r.productno
HAVING COUNT(DISTINCT o.customerid) > 1;
```

**What it does:** Joins line items to orders (to learn *who* ordered), groups by product, and counts **distinct customers** per product. `DISTINCT` matters: without it, one customer ordering the same product on two orders would be counted twice. Keeps products with more than one customer.

**Version B: "Using EXISTS"**

```sql
-- Using EXISTS
SELECT DISTINCT r1.productno
FROM requests r1, orders o1
WHERE r1.orderno = o1.orderid
AND EXISTS (
    SELECT 1 
    FROM requests r2, orders o2
    WHERE r2.productno = r1.productno 
      AND r2.orderno = o2.orderid  -- Same product
      AND o2.customerid <> o1.customerid -- Different customer
);
```

**What it does:** For each product/customer pair `(r1, o1)`, asks: "does a request exist for the **same product** by a **different customer**?". If yes, the product qualifies. `DISTINCT` on the outside removes repeats when a product qualifies via several rows.

**How they differ:** A *counts* distinct customers and compares to 1. B *searches for a witness* (a second customer) and never counts. A is shorter and scales to "more than N"; B is a direct translation of "there exists another customer". A Day 7 join version is covered [later](#q50-find-products-that-were-ordered-by-more-than-one-customer-1).

> On the lab data: products 3, 5, 6, 8, 10, 14.

---

### Q51. Find customers who ordered more than two different products

**Version A: GROUP BY + COUNT DISTINCT**

```sql
SELECT c.customerid, c.customername
FROM CUSTOMER c, ORDERS o, requests r
WHERE c.customerid = o.customerid AND o.orderid = r.orderno
GROUP BY c.customerid, c.customername
HAVING COUNT(DISTINCT r.productno) > 2;
```

**What it does:** Joins customer → orders → line items, groups per customer, and keeps customers with more than two distinct products across all their orders.

**Version B: "Using AND EXISTS" (triple nesting)**

```sql
-- Using AND EXISTS
SELECT DISTINCT c.customerid, c.customername
FROM customer c
JOIN orders o1 ON c.customerid = o1.customerid
JOIN requests r1 ON o1.orderid = r1.orderno
WHERE EXISTS (
    SELECT 1
    FROM orders o2
    JOIN requests r2 ON o2.orderid = r2.orderno
    WHERE o2.customerid = c.customerid    -- Same customer
      AND r2.productno <> r1.productno    -- Second distinct product
      AND EXISTS (
          SELECT 1
          FROM orders o3
          JOIN requests r3 ON o3.orderid = r3.orderno
          WHERE o3.customerid = c.customerid    -- Same customer
            AND r3.productno <> r1.productno    -- Different from first product
            AND r3.productno <> r2.productno    -- Different from second product
      )
);
```

**What it does:** Instead of counting, it *proves* the customer has three different products:

1. `r1` is one product the customer ordered.
2. The first `EXISTS` finds a second product `r2` that differs from `r1`.
3. The nested `EXISTS` finds a third product `r3` that differs from both.

If all three can be found, the customer qualifies. (This version also uses the ANSI `JOIN ... ON` syntax, an early preview of Day 7.)

**How they differ:** Both give the same customers (on the lab data: 1, 2, 3, 6). A is compact and easily changed to "more than N". B hard-codes "three" into its structure, so going to "more than 4" would need yet another nesting level. B is mainly a demonstration of what `COUNT(DISTINCT ...) > 2` is really saying.

---

### Q52. Find the product(s) with the highest total ordered quantity

**Version A: inline view + MAX(SUM)**

```sql
SELECT productno, total_qty 
FROM (
    SELECT productno, SUM(quantity) AS total_qty
    FROM requests
    GROUP BY productno
    ORDER BY total_qty DESC
)
WHERE total_qty = (
    SELECT MAX(SUM(quantity)) 
    FROM requests 
    GROUP BY productno
);
```

**What it does:** The inline view totals quantity per product. The outer `WHERE` keeps rows whose total equals the largest total, computed with the nested aggregate `MAX(SUM(quantity))` (Oracle-specific). Ties are all returned.

> The `ORDER BY total_qty DESC` inside the inline view is redundant here: the `WHERE` filter does all the work of picking the maximum, so sorting has no effect on which rows come out.

**Version B: "Smaller query because why not" (>= ALL)**

```sql
--Smaller query because why not
SELECT productno, SUM(quantity) AS total_ordered
FROM requests
GROUP BY productno
HAVING SUM(quantity) >= ALL (
    SELECT SUM(quantity)
    FROM requests
    GROUP BY productno
);
```

**What it does:** Groups by product and keeps groups whose total is greater than or equal to *every* group's total, which is the maximum (the same trick as Q26 to Q28).

**How they differ:** Same answer and tie handling. A uses a wrapper query plus a nested aggregate; B needs no wrapper and no nested aggregate, so it is shorter and more portable. A Day 7 version uses joins and is covered [later](#q52-find-the-products-with-the-highest-total-ordered-quantity-1). On the lab data: product 5 (total 20).

---

### Q53. Find customers who ordered more quantity than the average customer ordered

```sql
SELECT c.customerid, c.customername
FROM CUSTOMER c, ORDERS o, requests r
WHERE c.customerid = o.customerid AND o.orderid = r.orderno
GROUP BY c.customerid, c.customername
HAVING SUM(r.quantity) > (
    SELECT AVG(total_cust_qty)
    FROM (
        SELECT SUM(r2.quantity) AS total_cust_qty
        FROM ORDERS o2, requests r2
        WHERE o2.orderid = r2.orderno
        GROUP BY o2.customerid
    )
);
```

**What it does:** The outer query totals quantity per customer. The subquery computes the *average of the per-customer totals*: an inner query sums per customer, and `AVG` runs over that result. `HAVING` keeps customers whose own total is above that average. It needs the inline view because you can't write `AVG(SUM(...))` portably. On the lab data, the average is 9.2 and customers 1 (17) and 2 (13) exceed it.

> The average is taken over customers **who have ordered**. Customer 5 (no orders) isn't in the calculation. Counting them as 0 would lower the average to about 7.67.

#### Helper: "Checking Average"

```sql
--Checking Average
SELECT AVG(total_cust_qty)
    FROM (
        SELECT SUM(r2.quantity) AS total_cust_qty
        FROM ORDERS o2, requests r2
        WHERE o2.orderid = r2.orderno
        GROUP BY o2.customerid
      );
```

**What it does:** The subquery from Q53 run on its own, just to *display* the average being compared against (9.2 on the lab data). It's a debugging step, not an answer to a question.

---

### Q54. Find products that have been ordered in every order

```sql
SELECT productno
FROM requests
GROUP BY productno
HAVING COUNT(DISTINCT orderno) = (SELECT COUNT(*) FROM ORDERS);
```

**What it does:** For each product, counts the distinct orders it appears in. If that number equals the **total number of orders** in the system, the product appeared in every order. This "count matches total" idea is the counting alternative to the `NOT EXISTS` division of Q47. On the lab data there are 5 orders and no product appears in more than 2, so the result is empty.

---

### Q55. Find customers who ordered exactly the same products as Contemporary Casuals

```sql
SELECT c.customerid, c.customername
FROM CUSTOMER c
WHERE c.customername <> 'Contemporary Casuals'
  AND NOT EXISTS (
    SELECT r.productno 
    FROM ORDERS o, requests r, CUSTOMER c2
    WHERE o.orderid = r.orderno AND o.customerid = c2.customerid AND c2.customername = 'Contemporary Casuals'
    MINUS
    SELECT r3.productno 
    FROM ORDERS o3, requests r3
    WHERE o3.orderid = r3.orderno AND o3.customerid = c.customerid
  )
  AND NOT EXISTS (
    SELECT r4.productno 
    FROM ORDERS o4, requests r4
    WHERE o4.orderid = r4.orderno AND o4.customerid = c.customerid
    MINUS
    SELECT r5.productno 
    FROM ORDERS o5, requests r5, CUSTOMER c5
    WHERE o5.orderid = r5.orderno AND o5.customerid = c5.customerid AND c5.customername = 'Contemporary Casuals'
  );
```

**What it does:** "Exactly the same set" means each set contains the other, so the query checks **both directions** with two `NOT EXISTS` blocks, each containing a `MINUS`:

- **First `NOT EXISTS`:** *(Contemporary Casuals' products)* MINUS *(this customer's products)* must be **empty**, meaning the customer ordered everything CC did.
- **Second `NOT EXISTS`:** *(this customer's products)* MINUS *(CC's products)* must be **empty**, meaning the customer ordered nothing extra.

`c.customername <> 'Contemporary Casuals'` stops CC from matching themselves. On the lab data no other customer has the same product set as CC, so the result is empty.

#### Sir's additional problem (Q55 variant)

```sql
--Changed query according to Sir's Additional Problem** 
SELECT c.customerid, c.customername
FROM CUSTOMER c
WHERE c.customername <> 'Furniture Gallery'
  AND NOT EXISTS (
    SELECT r.productno 
    FROM ORDERS o, requests r, CUSTOMER c2
    WHERE o.orderid = r.orderno AND o.customerid = c2.customerid AND c2.customername = 'Furniture Gallery'
    MINUS
    SELECT r3.productno 
    FROM ORDERS o3, requests r3
    WHERE o3.orderid = r3.orderno AND o3.customerid = c.customerid
  )
  AND NOT EXISTS (
    SELECT r4.productno 
    FROM ORDERS o4, requests r4
    WHERE o4.orderid = r4.orderno AND o4.customerid = c.customerid
    MINUS
    SELECT r5.productno 
    FROM ORDERS o5, requests r5, CUSTOMER c5
    WHERE o5.orderid = r5.orderno AND o5.customerid = c5.customerid AND c5.customername = 'Furniture Gallery'
  );
```

**How it differs from the original:** The structure is **identical**. Only the customer name changes, from `'Contemporary Casuals'` to `'Furniture Gallery'` (in four places). It's the same logic aimed at a different reference customer, so it can actually produce a match. On the lab data it returns **Home Furnishings**, since both Furniture Gallery (order 4) and Home Furnishings (order 2) ordered exactly products 3, 8 and 14.

**The instructions from your file, for reference:**

```sql
--**Sir's Additional Problem for query number 55
--He has asked to insert a row in requests
--Uncomment the following query to add row
--INSERT  INTO requests (orderno, productno, quantity) VALUES (4, 14, _);
--Replace underscore (_) with any quantity you wish
--Afterwards, go back to 55 and change 'Contemporary Casuals' to 'Furniture Gallery'
```

> **Heads-up:** With the rows from your INSERT statements, `requests` already contains the pair `(orderno 4, productno 14)` with quantity 5. Running that INSERT as-is would therefore violate the composite primary key (`ORA-00001`). If your own data differs (for instance, that row was deleted first), it works as intended. Otherwise, use an `UPDATE` or pick a pair that doesn't yet exist.

---

### Q56. Find customers who ordered both Oak and Birch products

**Version A: GROUP BY + COUNT DISTINCT finish**

```sql
SELECT c.customerid, c.customername
FROM CUSTOMER c, ORDERS o, requests r, PRODUCT p
WHERE c.customerid = o.customerid
  AND o.orderid = r.orderno
  AND r.productno = p.productno
  AND p.productfinish IN ('Oak', 'Birch')
GROUP BY c.customerid, c.customername
HAVING COUNT(DISTINCT p.productfinish) = 2;
```

**What it does:** Joins all four tables, keeps only lines for Oak or Birch products, then groups by customer. `COUNT(DISTINCT p.productfinish) = 2` means both finishes are present in that customer's orders.

**Version B: "Using EXISTS keyword"**

```sql
--Using EXISTS keyword
SELECT c.customerid, c.customername
FROM customer c
WHERE EXISTS (
    SELECT * 
    FROM orders o 
    JOIN requests r ON o.orderid = r.orderno
    JOIN product p ON r.productno = p.productno
    WHERE o.customerid = c.customerid 
      AND p.productfinish = 'Oak'
)
AND EXISTS (
    SELECT * 
    FROM orders o 
    JOIN requests r ON o.orderid = r.orderno
    JOIN product p ON r.productno = p.productno
    WHERE o.customerid = c.customerid 
      AND p.productfinish = 'Birch'
);
```

**What it does:** Two independent yes/no tests joined by `AND`: "this customer has ordered an Oak product" *and* "this customer has ordered a Birch product". No grouping or counting.

**How they differ:** A relies on counting distinct finishes and is tied to the number 2 (adding a third finish means changing the number and the list). B reads like the English sentence and each finish is a separate test, which is easier to extend or change to "Oak but *not* Birch". A Day 7 join version is covered [later](#q56-find-customers-who-ordered-both-oak-and-birch-products-1). On the lab data: customers 1, 3, 4 and 6.

---

## Day 7: ANSI joins, outer joins, self-joins

Day 7 switches to the modern **ANSI join syntax** (`FROM a JOIN b ON condition`), which separates join conditions from filter conditions, and introduces **outer joins**: ways to keep rows from one side even when there's no match on the other.

### 7A-1. Inner join, CUSTOMER and ORDERS

```sql
SELECT *
FROM (CUSTOMER JOIN ORDERS ON
CUSTOMER.CUSTOMERID=ORDERS.CUSTOMERID);
```

**What it does:** A plain `JOIN` (inner join) is the ANSI-syntax equivalent of the Day 5 comma-join with a `WHERE` condition. Every order paired with its customer's details, exactly like question 4B-4, just written differently. Customer 5 (Impressions), who has no orders, does not appear. 5 rows.

---

### 7A-2. Inner join with an extra date condition

```sql
SELECT *
FROM (CUSTOMER JOIN ORDERS ON
CUSTOMER.CUSTOMERID=ORDERS.CUSTOMERID AND ORDERDATE > '01-nov2021');
```

**What it does:** The `ON` clause carries **two** conditions: match the customer, *and* only consider orders after 1 Nov 2021. Because this is an inner join, a customer with no *matching* order (either no orders at all, or only earlier orders) is dropped entirely, not shown with NULLs.

> **Heads-up:** the date literal is written `'01-nov2021'` (missing the hyphen before "2021"). Whether Oracle accepts this depends on the session's `NLS_DATE_FORMAT`; the safe way to write it is `TO_DATE('01-NOV-2021','DD-MON-YYYY')`. On the sample data, only order 5 (2 Nov 2021, customer 4) is after 1 Nov, so the intended result is one row.

---

### 7A-3. Inner join with an "after 1 Oct" condition

```sql
SELECT *
FROM (CUSTOMER JOIN ORDERS ON
CUSTOMER.CUSTOMERID=ORDERS.CUSTOMERID AND ORDERDATE > '01-oct2021');
```

**What it does:** Same shape as 7A-2, with the cutoff moved to 1 October. Same date-literal caveat applies. On the sample data, four of the five orders are after 1 Oct 2021 (all except order 2, dated 11 Jan 2021), giving four qualifying customer/order pairs.

---

### 7A-4. NATURAL JOIN

```sql
SELECT *
FROM (CUSTOMER NATURAL JOIN ORDERS);
```

**What it does:** `NATURAL JOIN` automatically joins on every column the two tables have in common by name — here, `customerid` — without writing an `ON` clause. It behaves like 7A-1, but the shared column appears **once** in the output instead of twice. It's convenient, but risky: if the tables ever gain another same-named column, it silently joins on that too.

---

### 7A-5. LEFT OUTER JOIN

```sql
SELECT *
FROM (CUSTOMER C LEFT OUTER JOIN ORDERS O ON
C.CUSTOMERID=O.CUSTOMERID);
```

**What it does:** Keeps **every** customer, matched to their orders where they exist. A customer with no orders (Impressions) still appears, with all of the `ORDERS` columns filled with NULL. This is the first query that can show customers without orders directly, no anti-join trick needed. 6 rows total (5 matched + 1 unmatched).

---

### 7A-6. RIGHT OUTER JOIN

```sql
SELECT *
2
FROM (REQUESTS R RIGHT OUTER JOIN PRODUCT P ON
P.PRODUCTNO=R.PRODUCTNO);
```

*(the stray `2` on its own line is a paste artefact from a SQL\*Plus session and is not part of the SQL — it should be removed)*

**What it does:** A `RIGHT OUTER JOIN` keeps every row from the **right** table (`PRODUCT`), matched to `REQUESTS` where possible. Every product appears at least once, including the six products that have never been requested (2, 7, 9, 11, 12, 13) — for those, the `REQUESTS` columns are NULL. 14 total distinct products, 20 rows overall once you count a product's multiple requests.

---

### 7A-7. FULL OUTER JOIN

```sql
SELECT *
FROM (REQUESTS R FULL OUTER JOIN PRODUCT P ON
P.PRODUCTNO=R.PRODUCTNO);
```

**What it does:** Combines both directions: every request is shown (matched to its product), **and** every product is shown (even ones with no requests). Nothing on either side is dropped. Since every request always has a matching product here (`requests.productno` is a foreign key), this happens to return the same rows as the right outer join above — but in general a full outer join would also preserve unmatched rows from the *left* table if any existed.

---

### 7A-8. LEFT OUTER JOIN with a filtered ON clause

```sql
SELECT *
FROM (CUSTOMER C LEFT OUTER JOIN ORDERS O ON
C.CUSTOMERID=O.CUSTOMERID AND O.ORDERDATE >'01-nov-2021');
```

**What it does:** Combines 7A-5's "keep every customer" behaviour with 7A-2's date filter, but crucially the filter sits **inside the ON clause**, not in a `WHERE`. That distinction matters: because it's a left join, every customer still appears; for a customer whose orders don't satisfy the date condition (or who has none), the `ORDERS` columns come back NULL instead of the row disappearing. If the date filter had been placed in a `WHERE` clause instead, the left join would effectively become an inner join, since `WHERE` runs after the join and would then discard the NULL-order rows.

---

### 7A-9. Products which have not been ordered

```sql
-- PRODUCTS WHICH HAVE NOT BEEN ORDERED
SELECT P.* 
FROM PRODUCT P LEFT OUTER JOIN requests R 
ON P.productno = R.productno
WHERE R.productno IS NULL;
```

**What it does:** This is the standard **anti-join** pattern: left join products to requests, then keep only the rows where the join found **no match** (`R.productno IS NULL`). Those are exactly the products nobody ordered. On the lab data: products 2, 7, 9, 11, 12, 13 — the same answer Day 5's Q44 arrived at differently, using `NOT IN`.

---

### 7A-10. Customers who have not ordered

```sql
-- CUSTOMERS WHO HAVE NOT ORDERED
SELECT C.* 
FROM CUSTOMER C LEFT OUTER JOIN ORDERS O 
ON C.customerid = O.customerid
WHERE O.orderid IS NULL;
```

**What it does:** Same anti-join idea as 7A-9, applied to customers and orders. On the lab data: only Impressions (customer 5) has never placed an order.

---

### 7A-11. Products whose price is more than the price of any other product

```sql
-- PRODUCTS WHOSE PRICE IS MORE THAN THE PRICE OF ANY OTHER PRODUCT
SELECT P1.*
FROM PRODUCT P1 LEFT OUTER JOIN PRODUCT P2
ON P1.unitprice < P2.unitprice
WHERE P2.productno IS NULL;
```

**What it does:** A **self-join anti-join** used to find the maximum. For each product `P1`, it tries to find some other product `P2` that is *more expensive* (`P1.unitprice < P2.unitprice`). If no such `P2` exists (`P2.productno IS NULL` after the left join), then `P1` must be at least tied for the highest price. This is a join-based alternative to the `>= ALL` subquery pattern from Q26 to Q28. On the lab data: the 8-Drawer Oak dresser at 800.

> **Note on the phrasing:** "more than the price of *any* other product" colloquially means "the most expensive", which is what this query finds. Read strictly, "more than any" could also be misread as "more than at least one", which nearly every product would satisfy — the query implements the "most expensive" interpretation.

---

### 7A-12. Products whose price is less than the price of any other product with a different finish

```sql
-- PRODUCTS WHOSE PRICE IS LESS THAN THE PRICE OF ANY OTHER PRODUCT WITH DIFFERENT PRODUCT FINISH
SELECT DISTINCT P1.*
FROM PRODUCT P1 JOIN PRODUCT P2
ON P1.productfinish <> P2.productfinish 
AND P1.unitprice < P2.unitprice;
```

**What it does:** A plain (inner) self-join. `P1` is returned if **some** product `P2` with a *different* finish costs more than `P1`. This is a much weaker condition than 7A-11 — it just needs one pricier product of another finish to exist, not the single cheapest overall. `DISTINCT` prevents a `P1` row from appearing once per qualifying `P2`. On the lab data, only the single most expensive product overall (the 800 dresser) fails to qualify; every other product is cheaper than at least one differently-finished item.

---

### 7A-13. Customers who live in the same city as some other customer

```sql
-- CUSTOMERS WHO LIVE IN THE SAME CITY AS SOME OTHER CUSTOMER
SELECT DISTINCT C1.*
FROM CUSTOMER C1 JOIN CUSTOMER C2
ON C1.ccity = C2.ccity 
AND C1.customerid <> C2.customerid;
```

**What it does:** Another self-join: pair each customer with a different customer (`<>` on id) who shares the same city. `DISTINCT` avoids duplicates if more than two customers ever shared a city. On the lab data, all six customers are in different cities, so this returns **no rows**.

---

## Day 7 (continued): Day 6 problems re-solved with joins

This block re-solves problems 47 to 56 from Day 6, this time using ANSI `JOIN` syntax and derived tables (subqueries in `FROM`) instead of comma-joins and `WHERE`-clause subqueries. Comparisons below focus on how each join version differs from its Day 6 counterpart(s).

### Q47 (with joins). Find customers who ordered all products of Oak finish

```sql
SELECT c.customerid, c.customername
FROM customer c
JOIN orders o ON c.customerid = o.customerid
JOIN requests r ON o.orderid = r.orderno
JOIN product p ON r.productno = p.productno
WHERE p.productfinish = 'Oak'
GROUP BY c.customerid, c.customername
HAVING COUNT(DISTINCT p.productno) = (
    SELECT COUNT(*) 
    FROM product 
    WHERE productfinish = 'Oak'
);
```

**What it does:** Joins customer → orders → requests → product, keeps only Oak lines, then groups by customer and counts the **distinct** Oak products each one ordered. `HAVING` compares that count to the total number of Oak products that exist. If they match, the customer ordered every single one — relational division done by counting, rather than by `NOT EXISTS` / `MINUS`.

**How it differs from the Day 6 versions:** Day 6's Version A (`MINUS` inside `NOT EXISTS`) and Version B (double `NOT EXISTS`) both prove the *absence* of a missing product. This join version instead *counts* how many distinct Oak products were matched and compares to a known total — arguably the easiest of the three to read, but it depends on knowing the total count of Oak products up front, which the subquery supplies. On the lab data, the highest per-customer Oak count is 2 (customers 2, 3 and 6), against 6 Oak products total, so nobody qualifies — the same empty result as Day 6.

---

### Q48 (with joins). Find products whose total ordered quantity is greater than the average quantity ordered per product

```sql
SELECT p.productno, p.productdesc, total_query.total_qty
FROM product p
JOIN (
    SELECT r.productno, SUM(r.quantity) AS total_qty
    FROM requests r
    GROUP BY r.productno
) total_query ON p.productno = total_query.productno
JOIN (
    SELECT AVG(prod_sums.total_qty) AS avg_qty
    FROM (
        SELECT SUM(r2.quantity) AS total_qty
        FROM requests r2
        GROUP BY r2.productno
    ) prod_sums
) avg_query ON total_query.total_qty > avg_query.avg_qty;
```

**What it does:** Builds two **derived tables** (subqueries treated as if they were tables): `total_query` holds each product's total ordered quantity, and `avg_query` holds a single row with the average of those totals. They are joined to `product` — the second join's `ON` condition (`total_query.total_qty > avg_query.avg_qty`) is really acting as a filter, comparing each product's total against the one-row average.

**How it differs from Day 6's version:** Day 6 wrote `HAVING SUM(quantity) > (SELECT AVG(SUM(quantity)) FROM requests GROUP BY productno)`, relying on Oracle's ability to nest `AVG(SUM(...))` directly. This version reaches the same average without nested aggregates, by first materializing per-product sums in a derived table and then averaging *that*. It's more portable SQL, at the cost of being longer. Same result: products 5 and 14.

---

### Q49 (with joins). Find the customer who has ordered the largest total quantity

```sql
SELECT c.customerid, c.customername, cust_totals.total_ordered
FROM customer c
JOIN (
    SELECT o.customerid, SUM(r.quantity) AS total_ordered
    FROM orders o
    JOIN requests r ON o.orderid = r.orderno
    GROUP BY o.customerid
) cust_totals ON c.customerid = cust_totals.customerid
JOIN (
    SELECT MAX(max_sums.total_ordered) AS max_qty
    FROM (
        SELECT SUM(r2.quantity) AS total_ordered
        FROM orders o2
        JOIN requests r2 ON o2.orderid = r2.orderno
        GROUP BY o2.customerid
    ) max_sums
) max_query ON cust_totals.total_ordered = max_query.max_qty;
```

**What it does:** `cust_totals` holds each customer's total ordered quantity. `max_query` holds the single highest of those totals. Joining all three together keeps only the customer(s) whose total equals the maximum.

**How it differs from Day 6's two versions:** This gets the best of both: like Version A (`ROWNUM`) it shows the customer's name, and like Version B (`HAVING = MAX`) it correctly returns every customer tied for first, since it's a join rather than a `ROWNUM = 1` cutoff. On the lab data: customer 1, Contemporary Casuals, total 17.

---

### Q50 (with joins). Find products that were ordered by more than one customer

```sql
SELECT p.productno, p.productdesc
FROM product p
JOIN requests r ON p.productno = r.productno
JOIN orders o ON r.orderno = o.orderid
GROUP BY p.productno, p.productdesc
HAVING COUNT(DISTINCT o.customerid) > 1;
```

**How it differs from Day 6:** Logically identical to Day 6's Version A (`GROUP BY` + `COUNT(DISTINCT customerid) > 1`); only the join syntax changed from comma-style to `JOIN ... ON`. The `EXISTS`-based Version B from Day 6 has no direct counterpart here. Same result: products 3, 5, 6, 8, 10, 14.

---

### Q51 (with joins). Find customers who ordered more than two different products

```sql
SELECT c.customerid, c.customername
FROM customer c
JOIN orders o ON c.customerid = o.customerid
JOIN requests r ON o.orderid = r.orderno
GROUP BY c.customerid, c.customername
HAVING COUNT(DISTINCT r.productno) > 2;
```

**How it differs from Day 6:** Again, a direct syntax translation of Day 6's Version A. Same result as before: customers 1, 2, 3, 6.

---

### Q52 (with joins). Find the product(s) with the highest total ordered quantity

```sql
SELECT p.productno, p.productdesc, total_query.total_qty
FROM product p
JOIN (
    SELECT r.productno, SUM(r.quantity) AS total_qty
    FROM requests r
    GROUP BY r.productno
) total_query ON p.productno = total_query.productno
JOIN (
    SELECT MAX(max_sums.total_qty) AS max_qty
    FROM (
        SELECT SUM(r2.quantity) AS total_qty
        FROM requests r2
        GROUP BY r2.productno
    ) max_sums
) max_query ON total_query.total_qty = max_query.max_qty;
```

**How it differs from Day 6:** Same "derived table + join to the max" pattern used in Q49 above, applied to products instead of customers. This sidesteps both of Day 6's tricks (the nested `MAX(SUM(...))` in Version A, and the `>= ALL` correlated comparison in Version B). Same result: product 5, total 20.

---

### Q53 (with joins). Find customers who ordered more quantity than the average customer ordered

```sql
SELECT c.customerid, c.customername, cust_totals.total_qty
FROM customer c
JOIN (
    SELECT o.customerid, SUM(r.quantity) AS total_qty
    FROM orders o
    JOIN requests r ON o.orderid = r.orderno
    GROUP BY o.customerid
) cust_totals ON c.customerid = cust_totals.customerid
JOIN (
    SELECT AVG(all_totals.total_qty) AS avg_cust_qty
    FROM (
        SELECT SUM(r2.quantity) AS total_qty
        FROM orders o2
        JOIN requests r2 ON o2.orderid = r2.orderno
        GROUP BY o2.customerid
    ) all_totals
) avg_query ON cust_totals.total_qty > avg_query.avg_cust_qty;
```

**How it differs from Day 6:** Same idea as Q48's rewrite — reaches the "average of per-customer sums" through a derived table instead of a nested `AVG(SUM(...))`. Same result: customers 1 and 2.

---

### Q54 (with joins). Find products that have been ordered in every order

```sql
SELECT p.productno, p.productdesc
FROM product p
JOIN requests r ON p.productno = r.productno
GROUP BY p.productno, p.productdesc
HAVING COUNT(DISTINCT r.orderno) = (
    SELECT COUNT(*) 
    FROM orders
);
```

**How it differs from Day 6:** Practically identical to Day 6's version, just written with `JOIN ... ON`. Same empty result (no product appears in all 5 orders).

---

### Q55 (with joins). Find customers who ordered exactly the same products as Contemporary Casuals

```sql
SELECT c.customerid, c.customername
FROM customer c
JOIN orders o ON c.customerid = o.customerid
JOIN requests r ON o.orderid = r.orderno
-- Join with Contemporary Casuals' products to ensure they match
LEFT JOIN (
    SELECT DISTINCT r2.productno
    FROM customer c2
    JOIN orders o2 ON c2.customerid = o2.customerid
    JOIN requests r2 ON o2.orderid = r2.orderno
    WHERE c2.customername = 'Contemporary Casuals'
) cc_prods ON r.productno = cc_prods.productno
WHERE c.customername <> 'Contemporary Casuals'
GROUP BY c.customerid, c.customername
-- Check that the number of matching products equals total products for both
HAVING COUNT(DISTINCT r.productno) = COUNT(DISTINCT cc_prods.productno)
   AND COUNT(DISTINCT r.productno) = (
       SELECT COUNT(DISTINCT r3.productno)
       FROM customer c3
       JOIN orders o3 ON c3.customerid = o3.customerid
       JOIN requests r3 ON o3.orderid = r3.orderno
       WHERE c3.customername = 'Contemporary Casuals'
   );
```

**What it does:** `cc_prods` is a derived table holding Contemporary Casuals' distinct products. Each candidate customer's own products are left-joined against that list, so `cc_prods.productno` is NULL wherever the candidate ordered something CC did *not*. The `HAVING` clause then checks two things at once:

1. `COUNT(DISTINCT r.productno) = COUNT(DISTINCT cc_prods.productno)` — the candidate's product count matches the number of *those same products* that also belong to CC. If the candidate ordered anything CC didn't, this equality breaks (the CC-side count stays lower because the unmatched rows contribute NULLs, which `COUNT(DISTINCT ...)` ignores).
2. `COUNT(DISTINCT r.productno) = (CC's total product count)` — the candidate's total product count matches CC's total, so nothing is missing on either side.

**How it differs from Day 6's version:** Day 6 solved "same set" with **two `NOT EXISTS`/`MINUS` blocks**, one per direction. This join version instead does the comparison through row counts on a left join, needing a bit of care in how `COUNT(DISTINCT ...)` treats the resulting NULLs. It's a genuinely different technique, not just a syntax change, and arguably harder to read than the two-directional `NOT EXISTS` version.

There's also a plain re-run of the same structure with the name swapped to `'Furniture Gallery'`, following the same *"Sir's Additional Problem"* idea from Day 6 (see the code you provided, unchanged in shape). On the lab data, the Contemporary-Casuals version returns no rows, but the Furniture-Gallery version correctly returns Home Furnishings, matching the Day 6 result.

---

### Q56 (with joins). Find customers who ordered both Oak and Birch products

```sql
SELECT c.customerid, c.customername
FROM customer c
-- Join for Oak products
JOIN orders o1 ON c.customerid = o1.customerid
JOIN requests r1 ON o1.orderid = r1.orderno
JOIN product p1 ON r1.productno = p1.productno AND p1.productfinish = 'Oak'
-- Join again for Birch products
JOIN orders o2 ON c.customerid = o2.customerid
JOIN requests r2 ON o2.orderid = r2.orderno
JOIN product p2 ON r2.productno = p2.productno AND p2.productfinish = 'Birch'
GROUP BY c.customerid, c.customername;
```

**What it does:** Joins the customer to their Oak orders (`o1`/`r1`/`p1`) **and separately** to their Birch orders (`o2`/`r2`/`p2`), using the *same* customer both times. Because both joins are inner joins, a customer only survives at all if they have at least one Oak match and at least one Birch match — the two conditions are enforced by requiring both joins to succeed simultaneously. If a customer has, say, two Oak orders and one Birch order, the joins would produce two combined rows for them; `GROUP BY` collapses those back down to one row per customer (there's no `HAVING`, since the inner joins have already done the real filtering).

**How it differs from Day 6:** Day 6's Version A grouped a single joined result and checked `COUNT(DISTINCT productfinish) = 2`; Version B used two independent `EXISTS` checks. This join version instead threads through the tables **twice in parallel**, once per finish, and lets the double inner join do the "both must exist" logic. It's a different mechanism from either Day 6 version, and it's worth noting it only works cleanly for a fixed, small number of finishes (two) — extending it to "ordered Oak, Birch, *and* Walnut" would mean adding a third parallel set of joins, whereas Version B's `EXISTS` approach extends by just adding one more `AND EXISTS (...)` block. Same result on the lab data: customers 1, 3, 4, 6.

---

## Summary: problems solved more than one way

| Problem | Approaches used | What actually differs |
| --- | --- | --- |
| Q42 — orders with only Oak products | `MINUS` (set difference) vs. `NOT EXISTS` (row-by-row) | Set-based subtraction vs. proving no counter-example exists |
| Q43 — total quantity ordered per finish, including unordered finishes | Join + `GROUP BY`, `UNION`-ed with a zero-filled branch for missing finishes | Two-part query stitched with `UNION`, vs. a single `LEFT JOIN` (not written, but implied as the modern alternative) |
| Q44 — quantity on hand vs. ordered per product | Same "real data `UNION` zero-filled leftovers" pattern as Q43, applied per product | — |
| Q47 — customers who ordered all Oak products | (a) `MINUS` inside `NOT EXISTS`; (b) double `NOT EXISTS`; (c, Day 7) `COUNT(DISTINCT ...)` = total, via joins | (a)/(b) prove absence of a gap; (c) counts a match against a known total |
| Q48 — products above average ordered quantity | Nested aggregate `AVG(SUM(...))` vs. derived-table average (Day 7 join) | Oracle-specific nested aggregate vs. portable two-step derived table |
| Q49 — customer with the largest total quantity | (a) `ROWNUM = 1` after `ORDER BY`; (b) `HAVING = MAX` subquery; (c, Day 7) join to a derived max table | (a) picks exactly one row, Oracle-only, no tie handling; (b)/(c) handle ties, (c) also keeps the customer name |
| Q50 — products ordered by more than one customer | `GROUP BY` + `COUNT(DISTINCT customerid)` vs. `EXISTS` "different customer" search | Counting vs. finding a witness row |
| Q51 — customers who ordered more than two products | `GROUP BY` + `COUNT(DISTINCT productno)` vs. triple-nested `EXISTS` | Counting (extends easily to "more than N") vs. hard-coded three-witness proof |
| Q52 — product(s) with highest total ordered quantity | (a) inline view + `WHERE total = MAX(SUM(...))`; (b) `HAVING SUM >= ALL (...)`; (c, Day 7) join to derived max table | All three find the max; (b) is the shortest, (c) avoids nested aggregates |
| Q53 — customers above average ordered quantity | Nested `AVG(SUM(...))` vs. derived-table average (Day 7 join) | Same nested-aggregate-vs-derived-table contrast as Q48 |
| Q55 — customers with exactly CC's product set | Two-directional `NOT EXISTS`/`MINUS` vs. a `LEFT JOIN` + matching `COUNT(DISTINCT ...)` comparison (Day 7) | Set-equality via subtraction vs. count-equality via an outer join |
| Q56 — customers who ordered both Oak and Birch | (a) `GROUP BY` + `COUNT(DISTINCT productfinish) = 2`; (b) two independent `EXISTS`; (c, Day 7) two parallel inner joins, deduped with `GROUP BY` | (b) extends most easily to more finishes; (c) duplicates the join path per finish |

---

## Recurring patterns worth remembering

- **Scalar subquery for "the value that matters":** `WHERE col = (SELECT MAX(col) FROM ...)` — turns an aggregate into a comparison value (Q16, Q25, and others).
- **Correlated subquery for "compared to my own group":** the inner query references the outer row (`WHERE ... = p.productfinish`), recomputing per row instead of once globally (Q20, Q24, Q30, the Day 4C average-quantity query).
- **`>= ALL` / `<= ALL` to avoid writing `MAX`/`MIN` of an aggregate:** useful when you need the *maximum of group sums* without nesting aggregates directly (Q26 to Q28, Q52's second version).
- **`NOT EXISTS` for "no counter-example survives":** the standard, portable way to express relational division ("ordered *all* of X") and can also express "not any" conditions (Q42's second version, Q47's versions A and B).
- **Anti-join with `LEFT JOIN ... WHERE right.key IS NULL`:** the join-based equivalent of `NOT IN` / `NOT EXISTS`, used for "has none of these" questions (products never ordered, customers who never ordered, and — in a cleverer form — "no one beats my price", finding a maximum via self-join).
- **`UNION` to patch in missing groups:** when a `JOIN`/`GROUP BY` naturally drops groups with no matching rows (an unordered finish, a never-ordered product), a second `SELECT ... WHERE NOT IN (...)` branch adds them back with a default value. A `LEFT JOIN` with `COALESCE` achieves the same result in one pass.
- **Derived tables instead of nested aggregates:** anywhere Day 6 wrote `AVG(SUM(quantity))` or `MAX(SUM(quantity))` directly, the Day 7 join versions instead compute the inner `SUM` in a subquery, then aggregate that subquery's result in a second query. This trades brevity for portability outside Oracle.
- **Counting vs. existence-checking as twin techniques for the same question:** "more than N distinct items" can be answered either by `COUNT(DISTINCT ...) > N` or by nesting N `EXISTS` calls proving N different witnesses. Counting scales better; nested `EXISTS` is a more literal reading of the English sentence.