DBMS Lab: SQL & PL/SQL Reference (Day 1 to Day 10)

• 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
• Day 8: SQL concepts — referential actions, views, assertions, triggers, sequences, domains, ALTER TABLE
• Day 9: Class test — month/date-wise transaction analysis
• Day 10: Introduction to PL/SQL
• Summary: problems solved more than one way
• Recurring patterns worth remembering
• Bugs and corrections — quick index

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

Day 8 was a concept walkthrough rather than a problem set, so there's no student code to grade here. What follows recaps what the notes covered, with flags on the handful of spots where the syntax shown is standard SQL rather than something Oracle will actually run as written — worth knowing before you try it in a terminal.

#Referential actions on DELETE

The notes rebuild the same ORDERS table three times, each with a different FOREIGN KEY(customerid) REFERENCES CUSTOMER(customerid) clause, to show three different behaviours when a referenced CUSTOMER row is deleted:

Heads-up: The notes mention that standard SQL also allows ON UPDATE CASCADE/SET NULL/SET DEFAULT and ON DELETE SET DEFAULT, but flag that Oracle only implements ON DELETE CASCADE and ON DELETE SET NULL — there is no ON UPDATE action and no SET DEFAULT option in Oracle's foreign keys. If you see those in a textbook example, they won't run as-is against Oracle.

#UNIQUE is not automatically a candidate key

The notes make a sharp point about UNIQUE (customername) in the CUSTOMER table: a UNIQUE constraint on its own still permits a NULL value (and typically permits more than one row to hold a NULL, since nulls aren't considered equal to each other). A true candidate key must also be non-nullable, so the comment correctly notes that turning this into a real candidate key needs UNIQUE (customername) NOT NULL, not UNIQUE by itself.

#Views

A view is a stored query, not a stored copy of data — its definition sits in the data dictionary, and every time you SELECT from it, the underlying query is re-run (realized) against the current tables. That's why view output is always current, unlike a separately-saved snapshot.

The notes walk through three progressively more complex examples:

-- A view over one table with GROUP BY / aggregation
CREATE VIEW FINISH_QTY(PRODFINISH, TOTAL_QTY)
AS SELECT PRODUCTFINISH, SUM(QTYONHAND)
FROM PRODUCT
GROUP BY PRODUCTFINISH;

-- A view that itself contains a subquery, later used inside another query
CREATE VIEW ORDERING_CUSTOMER(CUSTOMERID, CUSTOMERNAME)
AS SELECT CUSTOMERID, CUSTOMERNAME
FROM CUSTOMER
WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM ORDERS);

SELECT CUSTOMERID, CUSTOMERNAME, CADDRESS
FROM CUSTOMER
WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM ORDERING_CUSTOMER);

What this second part shows: once a view exists, it can be used exactly like a table inside a further query — here, as the source for a second IN subquery, even though ORDERING_CUSTOMER is itself built from a subquery.

-- A view joining four tables
CREATE VIEW CUST_PROD (CUSTNAME, PRODNAME, QTYORD)
AS
SELECT DISTINCT C.CUSTOMERNAME, P.PRODUCTDESC, R.QUANTITY
FROM PRODUCT P, REQUESTS R, ORDERS O, CUSTOMER C
WHERE P.PRODUCTNO=R.PRODUCTNO AND R.ORDERNO=O.ORDERNO AND O.CUSTOMERID=C.CUSTOMERID;

What it does: this is the same four-table join chain used repeatedly in the main lab file (CUSTOMER → ORDERS → REQUESTS → PRODUCT), just packaged as a view so it doesn't have to be retyped.

Updatability rules, and why they matter:

• A view cannot be updated (via INSERT/UPDATE/DELETE through the view) if it's built from more than one table, or if it uses GROUP BY or an aggregate function. Both ORDERING_CUSTOMER (a subquery, but still single-table) would be fine on that front, while CUST_PROD (four tables) and FINISH_QTY (GROUP BY + SUM) would not be updatable.
• A view can be updated if it's defined on a single table, includes the key, and only includes columns that are either nullable or already have no default — this is why the final example works:

CREATE VIEW IMP_CUSTOMER (I, N, Z)
AS SELECT CUSTOMERID, CUSTOMERNAME, CZIP
FROM CUSTOMER;

INSERT INTO IMP_CUSTOMER VALUES(20, 'ANUP', '700019');

Why this particular view is insertable: IMP_CUSTOMER exposes exactly CUSTOMERID (the key), plus CUSTOMERNAME and CZIP — the two columns in CUSTOMER that are NOT NULL. It deliberately leaves out CADDRESS and CCITY, which are nullable. Because every NOT NULL column of the base table is covered by the view, Oracle can work out values for the omitted (nullable) columns — NULL — and the insert goes through.

The notes also mention materialized views (a view whose result actually is stored, refreshed immediately, lazily, or on a schedule) as a contrast to the always-live views above, without going into their syntax.

#CREATE ASSERTION

CREATE ASSERTION CHECK_QTY
CHECK (NOT EXISTS (SELECT * FROM PRODUCT P, REQUESTS R
 WHERE P.PRODUCTNO=R.PRODUCTNO
 AND QTYONHAND < QUANTITY));

What it's meant to do: a CHECK constraint on a single table can only look at that table's own columns. An assertion is standard SQL's way to write a constraint that spans multiple tables — here, "no request should ever ask for more than the product's current stock." Every database state has to satisfy it, not just every row.

Heads-up: the notes themselves flag this with "CHECK WHETHER AVAILABLE IN ORACLE" — and the answer is no: Oracle has never implemented CREATE ASSERTION. The practical substitute is a trigger (see next) that checks the condition and raises an error, or enforcing it at the application layer.

#CREATE TRIGGER (event-driven)

CREATE TRIGGER QTY_VIOLATION
BEFORE INSERT OR UPDATE OF QUANTITY
ON ORDERS
FOR EACH ROW
WHEN (NEW.QUANTITY > (SELECT QTYONHAND FROM PRODUCT
 WHERE PRODUCTNO=NEW.PRODUCTNO))
DO_PROCEDURE(NEW.PRODUCTNO);

What it's illustrating: triggers let you attach procedural logic to a table event (insert/update/delete), tested row by row (FOR EACH ROW), optionally restricted to only fire under a condition (WHEN (...)).

Heads-up: treat this one as pseudocode, not copy-pasteable Oracle syntax. A real Oracle trigger body needs a BEGIN ... END; block (DO_PROCEDURE(NEW.PRODUCTNO) on its own isn't valid), and this example is also fixing a bug that doesn't exist in this schema: ORDERS here has no QUANTITY column — the quantity ordered lives in REQUESTS. Day 10's own working trigger (trg_after_customer_insert, covered below) is the version of this idea that's actually valid, runnable Oracle PL/SQL.

#CREATE SEQUENCE

CREATE SEQUENCE ORDERSEQ START WITH 1 INCREMENT BY 1;

insert into orders values(ORDERSEQ.NEXTVAL, to_date('29/10/2021','DD/MM/YYYY'), 2);

What it does: a sequence is a standalone counter object, independent of any table, that hands out the next number in order each time .NEXTVAL is referenced — the standard Oracle way to generate surrogate primary keys before Oracle's newer IDENTITY column syntax. Worth keeping in mind for Day 10: the PL/SQL file creates an almost identical sequence, audit_log_seq, specifically to generate audit_log.log_id — but then never actually calls audit_log_seq.NEXTVAL anywhere. See the Q14 write-up below for why that's a real bug, not just a style note.

#CREATE DOMAIN

CREATE DOMAIN nameadd AS VARCHAR(30);

What it's illustrating: a domain is a reusable, named column type (here, nameadd standing in for VARCHAR(30)) that can be used across several CREATE TABLE statements so the type only has to be changed in one place.

Heads-up: CREATE DOMAIN is standard SQL, but Oracle does not support it — there is no Oracle equivalent statement. In practice, Oracle users either repeat the base type in every table, define a SUBTYPE inside a PL/SQL package (which only helps inside PL/SQL code, not in CREATE TABLE), or just accept the repetition.

#ALTER TABLE

ALTER TABLE CUSTOMER DROP COLUMN ADDRESS CASCADE/RESTRICT;
ALTER TABLE CUSTOMER ADD COLUMN DISCOUNT NUMERIC(5,2);
ALTER TABLE PRODUCT ALTER COLUMN QTYONHAND DROP DEFAULT;
ALTER TABLE PRODUCT ALTER COLUMN QTYONHAND ADD DEFAULT 1;

What it's illustrating: the general SQL-standard shape of schema changes — dropping a column, adding one, and changing a column's default.

Heads-up: none of these four lines are valid Oracle syntax as written; they follow the ANSI/PostgreSQL style instead. Oracle's actual equivalents:

copy

ALTER TABLE CUSTOMER DROP COLUMN ADDRESS;                 -- no CASCADE/RESTRICT keyword in Oracle
ALTER TABLE CUSTOMER ADD (DISCOUNT NUMERIC(5,2));          -- ADD, not ADD COLUMN
ALTER TABLE PRODUCT MODIFY (QTYONHAND DEFAULT NULL);       -- MODIFY, not ALTER COLUMN ... DROP DEFAULT
ALTER TABLE PRODUCT MODIFY (QTYONHAND DEFAULT 1);          -- MODIFY, not ALTER COLUMN ... ADD DEFAULT

The keyword to remember is MODIFY: Oracle never uses ALTER COLUMN.

#INSERT / DELETE / UPDATE

DELETE CUSTOMER
WHERE CUSTOMERID=1;

UPDATE CUSTOMER
SET CCITY='Pune'
WHERE CUSTOMERID=6;

What it does: ordinary row deletion and update, filtered by WHERE as usual. One thing not worth flagging here: Oracle actually does allow DELETE tablename ... without the word FROM (unlike some other databases), so DELETE CUSTOMER WHERE ... is valid Oracle syntax, not an error.

The schema, built for the test:

CREATE TABLE Transaction_Amounts (
    tno VARCHAR(20),
    Amount INT
);

CREATE TABLE Transaction_Dates (
    tno VARCHAR(20),
    t_day INT,
    t_month VARCHAR(10),
    t_year INT
);

Transaction_Amounts can hold several rows per tno (two of the nine sample transaction numbers, 01jun12345 and 18oct52314, each have two amount rows), while Transaction_Dates is meant to be one row per tno.

Heads-up — schema: neither table declares any constraint: no primary key on either table, and no foreign key tying Transaction_Amounts.tno back to Transaction_Dates.tno. Nothing stops a typo'd tno in one table from silently failing to match the other, and nothing enforces the "one row per tno" intent of Transaction_Dates. Since this was a timed test, that's a reasonable thing to skip — but worth adding if you revisit this script: PRIMARY KEY (tno) on Transaction_Dates, and FOREIGN KEY (tno) REFERENCES Transaction_Dates(tno) on Transaction_Amounts. Also worth a note: VARCHAR(20) was used for tno here, but the Day 10 tables consistently use VARCHAR2, which is the type Oracle actually recommends — Oracle's plain VARCHAR is officially reserved and its behaviour isn't guaranteed to stay the same in future versions.

#a) Output month-wise total transaction amount

SELECT d.t_month, SUM(a.Amount) AS total_amount
FROM Transaction_Amounts a
JOIN Transaction_Dates d ON a.tno = d.tno
GROUP BY d.t_month;

What it does: joins each amount row to its transaction's month via the shared tno, then groups by month and totals. Straightforward, and correct. On the test data: June 11000, September 6000, October 15000.

#b) Which month has the highest transaction made?

SELECT t_month, total_amount
FROM (
    SELECT d.t_month, SUM(a.Amount) AS total_amount
    FROM Transaction_Amounts a
    JOIN Transaction_Dates d ON a.tno = d.tno
    GROUP BY d.t_month
    ORDER BY total_amount DESC
)
WHERE ROWNUM = 1;

What it does: the inline view reuses part (a)'s logic and sorts highest-first; the outer query keeps only the first row. This is the same ORDER BY + ROWNUM = 1 pattern as Q49's first version in the main lab file. On this data October is clearly the highest (15000, well ahead of June's 11000), so the query gives the right single answer here.

Same caution as before: ROWNUM = 1 only ever returns one row. If two months had tied for the highest total, this query would arbitrarily drop one of them. It happens not to bite here because there's no tie among the three months — but see part (c), where an equivalent query over dates runs into exactly that problem.

#c) Which date has the lowest total transaction made?

SELECT t_day, t_month, total_amount
FROM (
    SELECT d.t_day, d.t_month, SUM(a.Amount) AS total_amount
    FROM Transaction_Amounts a
    JOIN Transaction_Dates d ON a.tno = d.tno
    GROUP BY d.t_day, d.t_month
    ORDER BY total_amount ASC
)
WHERE ROWNUM = 1;

What it does: same shape as (b), reversed to ascending order and grouped by date instead of month, to find the lowest-total date.

Heads-up — this one actually has a tie, and the query silently drops half of it. Running the per-date totals on the test data:

Date	Total
15 June	3000
28 June	3000
1 June	5000
18 October	5000
1 September	6000
1 October	10000

15 June and 28 June are tied for the lowest total at 3000 each. ROWNUM = 1 can only return one row, so this query reports just one of the two correct answers (which one depends on the database's internal row order, which isn't guaranteed) and silently omits the other. For a question phrased as "which date" (singular), that might be what was wanted — but if the real goal is "find the lowest total and show every date that achieves it," the fix is the same = (SELECT MIN(...)) pattern used elsewhere in this series:

copy

SELECT t_day, t_month, total_amount
FROM (
    SELECT d.t_day, d.t_month, SUM(a.Amount) AS total_amount
    FROM Transaction_Amounts a
    JOIN Transaction_Dates d ON a.tno = d.tno
    GROUP BY d.t_day, d.t_month
)
WHERE total_amount = (
    SELECT MIN(total_amount) FROM (
        SELECT SUM(a2.Amount) AS total_amount
        FROM Transaction_Amounts a2
        JOIN Transaction_Dates d2 ON a2.tno = d2.tno
        GROUP BY d2.t_day, d2.t_month
    )
);

This version is also safe to reuse for part (b), for the same reason.

#d) Which date makes a single transaction that is same as some single transaction made on a different date?

SELECT DISTINCT d1.t_day, d1.t_month
FROM Transaction_Amounts a1
JOIN Transaction_Dates d1 ON a1.tno = d1.tno
WHERE EXISTS (
    SELECT 1
    FROM Transaction_Amounts a2
    JOIN Transaction_Dates d2 ON a2.tno = d2.tno
    WHERE a1.Amount = a2.Amount
      AND (d1.t_day <> d2.t_day OR d1.t_month <> d2.t_month)
);

What it does: for each individual amount row a1, the EXISTS subquery checks whether some other amount row a2 shares the exact same Amount and was logged on a different date (comparing day and month together, so same-day-different-month and same-month-different-day are both correctly treated as "different"). DISTINCT on the outside collapses a date down to one row even if it qualifies through more than one matching amount. On the test data this correctly returns 1 June, 15 June, 28 June and 18 October — each is pulled in because it holds at least one amount (2000 or 3000) that recurs on a different date.

A genuine ambiguity worth flagging, not a bug: the question says "a single transaction," and two of the nine tno values (01jun12345, 18oct52314) are not single — each has two amount rows under the same transaction number. The query above treats "transaction" as "one row in Transaction_Amounts," regardless of whether its tno has one row or several, which is the most natural reading given that parts (a)–(c) were about sums, and part (d) is clearly about comparing individual amounts instead. It happens this is also the only reading that produces a non-empty, meaningful answer: if "single transaction" were read more strictly as "a tno with exactly one row" (excluding 01jun12345 and 18oct52314 entirely), none of the remaining amounts repeat on another date at all, and the query would return nothing. That emptiness is a good sign the broader reading used here is the intended one — but it's worth a quick check with whoever set the question if the exact wording was graded strictly.

Fourteen short programs, covering DECLARE/BEGIN/EXCEPTION blocks, control flow, loops, cursors, exception handling, and the four main named objects (procedure, function, trigger, plus the sequence that supports it). All fourteen are syntactically valid PL/SQL and run correctly except Q14, which has a genuine bug — flagged below with a fix.

#1. Print "Hello World"

SET SERVEROUTPUT ON;

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

What it does: SET SERVEROUTPUT ON tells the client (SQL*Plus, SQL Developer, etc.) to actually display anything the server buffers via DBMS_OUTPUT; without it the PUT_LINE call still runs, but nothing appears on screen. The anonymous block (BEGIN ... END;) is the smallest unit of PL/SQL that can be run on its own, and the trailing / tells the client "execute what I just typed."

#2. Add Two Numbers

DECLARE
    num1 NUMBER := 15;
    num2 NUMBER := 25;
    result NUMBER;
BEGIN
    result := num1 + num2;
    DBMS_OUTPUT.PUT_LINE('The sum of ' || num1 || ' and ' || num2 || ' is: ' || result);
END;
/

What it does: DECLARE introduces local variables, initialized with :=. result is declared without an initial value, then assigned inside the block. || is string concatenation, and PL/SQL implicitly converts the NUMBER variables to text when they're concatenated with strings.

#3. Check Whether a Number Is Even or Odd

DECLARE
    num NUMBER := 47;
BEGIN
    IF MOD(num, 2) = 0 THEN
        DBMS_OUTPUT.PUT_LINE(num || ' is Even.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(num || ' is Odd.');
    END IF;
END;
/

What it does: MOD(num, 2) is the remainder after dividing by 2; zero means even. Correct as written, including for negative input (Oracle's MOD returns a result with the same sign as the first argument, but = 0 behaves the same either way).

#4. Find the Largest of Two Numbers

DECLARE
    a NUMBER := 54;
    b NUMBER := 89;
BEGIN
    IF a > b THEN
        DBMS_OUTPUT.PUT_LINE(a || ' is larger than ' || b);
    ELSIF b > a THEN
        DBMS_OUTPUT.PUT_LINE(b || ' is larger than ' || a);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Both numbers are equal.');
    END IF;
END;
/

What it does: a three-way IF/ELSIF/ELSE correctly separates "a bigger," "b bigger," and "equal" — nothing to add here.

#5. Find the Largest of Three Numbers

DECLARE
    x NUMBER := 35;
    y NUMBER := 92;
    z NUMBER := 64;
BEGIN
    IF x >= y AND x >= z THEN
        DBMS_OUTPUT.PUT_LINE(x || ' is the largest number.');
    ELSIF y >= x AND y >= z THEN
        DBMS_OUTPUT.PUT_LINE(y || ' is the largest number.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(z || ' is the largest number.');
    END IF;
END;
/

What it does: each branch checks a variable against both of the others using >= (not >), which is the detail that makes this correct even when two or three values tie — whichever tied value is checked first in the IF/ELSIF chain gets reported, and it's a true statement either way.

#6. Calculate Factorial

DECLARE
    num NUMBER := 5;
    factorial NUMBER := 1;
BEGIN
    FOR i IN 1..num LOOP
        factorial := factorial * i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is: ' || factorial);
END;
/

What it does: a numeric FOR loop running i from 1 to num inclusive, multiplying into an accumulator that starts at 1 (not 0 — multiplying by a 0 starting value would zero out the whole product). Correct. One edge case worth knowing rather than fixing: if num were set to 0, FOR i IN 1..0 LOOP simply doesn't execute (Oracle's numeric FOR loop skips entirely when the start exceeds the end), so factorial would correctly stay 1, which is the right answer for 0! — no special-casing needed.

#7. Reverse a Number

DECLARE
    num NUMBER := 12345;
    remainder NUMBER;
    reversed_num NUMBER := 0;
BEGIN
    WHILE num > 0 LOOP
        remainder := MOD(num, 10);
        reversed_num := (reversed_num * 10) + remainder;
        num := TRUNC(num / 10);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Reversed Number is: ' || reversed_num);
END;
/

What it does: the classic digit-extraction loop — MOD(num, 10) peels off the last digit, TRUNC(num / 10) drops it, and reversed_num is rebuilt one digit at a time in the opposite order. Correct for positive input. Worth knowing: a negative num would never satisfy num > 0, so the loop wouldn't run at all and reversed_num would print as 0 — not wrong exactly, just a silent no-op rather than a reversed negative number, which may or may not be the desired behaviour.

#8. Check Whether a Number Is Palindrome

DECLARE
    original_num NUMBER := 12321;
    num NUMBER;
    remainder NUMBER;
    reversed_num NUMBER := 0;
BEGIN
    num := original_num;
    WHILE num > 0 LOOP
        remainder := MOD(num, 10);
        reversed_num := (reversed_num * 10) + remainder;
        num := TRUNC(num / 10);
    END LOOP;
    IF original_num = reversed_num THEN
        DBMS_OUTPUT.PUT_LINE(original_num || ' is a Palindrome.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(original_num || ' is NOT a Palindrome.');
    END IF;
END;
/

What it does: reuses Q7's reversal logic, but copies original_num into a working variable num first, specifically so the reversal loop can destroy num without losing the original value needed for the final comparison. That backup step is the one detail that makes this correct — reversing original_num directly would leave nothing to compare against afterward.

#9. Display Numbers Using a Loop

BEGIN
    DBMS_OUTPUT.PUT_LINE('Displaying numbers 1 to 5:');
    FOR k IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Number: ' || k);
    END LOOP;
END;
/

What it does: the simplest form of the numeric FOR loop, with no DECLARE section needed since k is implicitly declared by the loop itself and scoped only to it.

#10. Use a Cursor to Display Customer Names

DECLARE
    CURSOR c_customers IS
        SELECT customer_name FROM customers;
    v_name customers.customer_name%TYPE;
BEGIN
    OPEN c_customers;
    LOOP
        FETCH c_customers INTO v_name;
        EXIT WHEN c_customers%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_name);
    END LOOP;
    CLOSE c_customers;
END;
/

What it does: this is the full, explicit cursor lifecycle — DECLARE the cursor and bind it to a query, OPEN it (runs the query), FETCH one row at a time into a variable, EXIT WHEN ... %NOTFOUND stops once a fetch finds nothing left, then CLOSE releases the cursor's resources. v_name customers.customer_name%TYPE is good practice: the variable automatically matches the column's data type, so it stays correct even if customer_name's length changes later.

A simpler alternative worth knowing: this entire pattern — open, loop, fetch, check %NOTFOUND, exit, close — is exactly what a cursor FOR loop does automatically:

BEGIN
    FOR cust_rec IN (SELECT customer_name FROM customers) LOOP
        DBMS_OUTPUT.PUT_LINE('Customer Name: ' || cust_rec.customer_name);
    END LOOP;
END;
/

This version needs no DECLARE, no explicit OPEN/FETCH/CLOSE, and can't accidentally leak a cursor if an exception is raised partway through (Oracle closes it automatically). The explicit version above is worth learning once because it shows what's happening underneath, but in real code the FOR loop form is almost always preferred for exactly this "read every row" case — the explicit form earns its keep when you need to skip rows conditionally, fetch into multiple cursors interleaved, or stop early based on logic that a plain FOR loop can't express.

#11. Handle NO_DATA_FOUND Exception

DECLARE
    v_name VARCHAR2(100);
BEGIN
    SELECT customer_name INTO v_name
    FROM customers
    WHERE customer_id = -999;
    DBMS_OUTPUT.PUT_LINE('Name found: ' || v_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error handled: No customer was found with that ID.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

What it does: SELECT ... INTO expects to return exactly one row; customer_id = -999 is deliberately chosen to match nothing, which raises the built-in NO_DATA_FOUND exception instead of crashing the block outright. The EXCEPTION section catches it by name and prints a friendly message instead. WHEN OTHERS is a catch-all safety net for any other error type.

One improvement worth making: a bare WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.') swallows the real error message, which makes debugging anything that lands there much harder. It's good practice to at least surface what actually went wrong:

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

SQLERRM returns the text of whatever error Oracle just raised, so the catch-all stays generic in what it catches but still tells you what happened — useful the moment something besides a missing row goes wrong here (for instance, if customer_id ever matched more than one row, which would raise TOO_MANY_ROWS instead, and currently get the same unhelpful message).

#12. Create a Procedure

CREATE OR REPLACE PROCEDURE greet_customer (p_name IN VARCHAR2) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome to our system, ' || p_name || '!');
END greet_customer;
/

BEGIN
    greet_customer('Sounak');
END;
/

What it does: CREATE OR REPLACE PROCEDURE stores a named, reusable subprogram in the schema (replacing it if a procedure of that name already exists, which makes re-running the script during development painless). p_name IN VARCHAR2 is an input-only parameter. The second block is just a normal anonymous block that calls the now-stored procedure by name — correct and idiomatic.

#13. Create a Function

CREATE OR REPLACE FUNCTION calculate_square (p_num IN NUMBER)
RETURN NUMBER AS
    v_square NUMBER;
BEGIN
    v_square := p_num * p_num;
    RETURN v_square;
END calculate_square;
/

DECLARE
    v_result NUMBER;
BEGIN
    v_result := calculate_square(8);
    DBMS_OUTPUT.PUT_LINE('The square of 8 is: ' || v_result);
END;
/

What it does: the difference from a procedure is the RETURN NUMBER in the header (declaring what type the function hands back) and the RETURN v_square; statement inside the body (every execution path through a function must eventually hit a RETURN). The test block assigns the function's result straight into a variable — functions can be used anywhere an expression of their return type is expected, including directly inside a SELECT if they're written to be callable from SQL (this one doesn't need to be, since it's only ever invoked from PL/SQL here).

#14. Create a Trigger

CREATE OR REPLACE TRIGGER trg_after_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (log_message, log_date)
    VALUES ('New customer added with ID: ' || :new.customer_id, SYSDATE);
END;
/

What it does: AFTER INSERT ... FOR EACH ROW fires this trigger once for every row inserted into customers, after the insert has happened. :new is the pseudo-record holding the just-inserted row's values, so :new.customer_id is the ID of the customer that was just added. The trigger logs a message and timestamp into audit_log.

Heads-up — this will fail at runtime, and the fix is already sitting unused in the same file. audit_log was created as:

copy

CREATE TABLE audit_log (
    log_id      NUMBER PRIMARY KEY,
    log_message VARCHAR2(255),
    log_date    DATE
);

log_id is the primary key, which means it is implicitly NOT NULL and has no DEFAULT value. The trigger's INSERT INTO audit_log (log_message, log_date) VALUES (...) never supplies log_id at all, so Oracle would try to insert NULL into it — and reject the whole insert with ORA-01400: cannot insert NULL into ("...". "AUDIT_LOG"."LOG_ID"). Since this trigger fires on every insert into customers, that means every single customer insert would also fail, because the AFTER INSERT trigger's own failure rolls back the triggering statement.

The fix is exactly what the script set up and then never used — right above this trigger, the file creates:

copy

CREATE SEQUENCE audit_log_seq
START WITH 1
INCREMENT BY 1;

audit_log_seq exists for precisely this purpose but is never referenced anywhere in the trigger. The corrected trigger just needs to pull the next sequence value into log_id:

copy

CREATE OR REPLACE TRIGGER trg_after_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (log_id, log_message, log_date)
    VALUES (audit_log_seq.NEXTVAL, 'New customer added with ID: ' || :new.customer_id, SYSDATE);
END;
/

With that one change, the trigger (and therefore every customer insert that fires it) works as intended.

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

Everything below was independently checked — either by running an equivalent query against the lab's own sample data, or by tracing the logic against documented Oracle behavior — rather than asserted from a read-through. The two real (not stylistic) bugs are marked actual bug; everything else is a portability note, an edge case, or a style improvement.

The two to fix before you rely on this code again: Day 9 part (c)'s ROWNUM tie-dropping, and Day 10 Q14's missing log_id in the trigger's INSERT. Both are shown above with corrected code.