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

• How to read this document
• Tables at a glance
• Day 1: Basic SELECT, WHERE, ORDER BY
• Day 2: Aggregates, GROUP BY, HAVING, simple subqueries
• Day 3: Set operators and more subqueries
• Day 4: Constraint violations, cartesian products, first multi-table queries
• Day 5: Multi-table joins (comma-style) and UNION patterns
• Day 6: Division, ranking and "every / exactly" problems
• Day 7: ANSI joins, outer joins, self-joins
• Day 7 (continued): Day 6 problems re-solved with joins
• Summary: problems solved more than one way
• Recurring patterns worth remembering

• 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.

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.

#Q1. List all products

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)

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

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

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

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"

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"

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

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

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

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.

#Q11. List all distinct Bookcases

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%

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?

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

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

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

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

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'

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

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

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".

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

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"

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

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

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

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

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

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)

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

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

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

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 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

-- 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

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

-- 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

-- 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

-- 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

-- 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

--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

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?

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?

--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

--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

--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

--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

--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

--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.

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"

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"

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"

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)

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")

-- 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

-- 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

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

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

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 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).

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

Version A: MINUS inside NOT EXISTS

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"

--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, 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

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

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)

--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.

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

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"

-- 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.

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

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)

-- 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)

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)

--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. On the lab data: product 5 (total 20).

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

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"

--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

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

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)

--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:

--**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

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"

--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. On the lab data: customers 1, 3, 4 and 6.

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

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

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

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

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

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

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

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

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

-- 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

-- 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

-- 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

-- 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

-- 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.

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

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

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

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

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

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

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

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

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

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

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.

• 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.