Lab — Database Management Systems

3 experiments plus PL/SQL, each set out as 1. Question, 2. Aim, 3. Steps, 4. Programme,

5. Execution and Results.

All SQL is in labs/course-5-dbms/:

python3 tools/data-science/run_sql_labs.py

This loads each schema with its official sample data, runs every statement, and then deliberately attempts nine illegal operations to confirm the constraints reject them. Current result: 118 statements executed, 70 SELECT queries returning 301 rows, 9/9 constraints enforced.

The output under 5. Execution and Results is the sqlite3 shell's, in box mode, from running each script on a new database. So that you can tell which result answers which question, each question's number and text are printed before it; a question that only creates or changes data prints nothing but its label. The constraint tests come last, each statement with the error the database gave. To see the tables yourself:

sqlite3 -box inventory.db < labs/course-5-dbms/01_inventory.sql

Two questions ask about today: Experiment 2's question 27 and Experiment 3's questions 27 and 28. Their answers change with the date you run them, so the output shown was made with the clock set to 4 October 2026.

The PL/SQL file is not executed. PL/SQL is Oracle-specific, SQLite cannot run it, and no Oracle instance was available. Those blocks are written to Oracle syntax and reviewed by hand — run them on your college's Oracle installation before relying on them. This is stated in the file itself rather than left for you to discover.

A note on question numbering

The official question lists have gaps — numbers that were dropped when the PDF was produced:

Some lost their text entirely; others left orphans. PL/SQL question 2 survives only as the dangling fragment "If yes, print 'High Salary'; Otherwise print 'Standard Salary'", with no question in front of it.

The lab files reconstruct each missing item and mark it [RECONSTRUCTED], so you can tell the reconstruction from the official text. Two items in Experiment 1 (questions 6 and 8) are also cut off mid-sentence — "Update the stock quantity of" — and are completed the same way.

See SYLLABUS-REVIEW.md finding D4.

Experiment 1 — Inventory Management

1. Question

Build an inventory database of products and their suppliers: create the Products and Suppliers tables with their constraints, insert the official sample data, change it, and answer the 24 questions set on it.

2. Aim

Define, fill and query two tables joined by a foreign key, using DDL, DML and DQL.

3. Steps

1. Create the tables, with their constraints. Section A, questions 1–3.
2. Insert the sample data, then update and delete rows. Section B, questions 4–8.
3. Query the tables. Section C, questions 9–24.

4. Programme

-- =====================================================================
-- Course 5 (DBMS) Lab -- Experiment 1: Inventory Management
-- Syllabus source: pages 28-29
--
-- NUMBERING NOTE (see SYLLABUS-REVIEW.md finding D4): the official question
-- list skips 3, 13, 20 and 22, and items 6 and 8 are cut off mid-sentence.
-- The missing items are reconstructed below and marked [RECONSTRUCTED] so you
-- can tell them from the official text.
--
-- Dialect: written for SQLite so the queries can be executed and checked
-- (see tools/run_sql_labs.py). Oracle differences are noted inline.
-- =====================================================================

-- ---------------------------------------------------------------------
-- SECTION A: DDL (Data Definition Language)
-- Step 1: Create the tables, with their constraints
-- ---------------------------------------------------------------------

-- Q1. Create a database called InventoryDB.
--     Oracle:  CREATE DATABASE is a DBA operation; in practice you use an
--              existing instance and create a schema/user instead:
--                CREATE USER inventory IDENTIFIED BY password;
--     MySQL:   CREATE DATABASE InventoryDB;
--     SQLite:  a database is just a file -- opening it creates it.

-- Q2. Create the Products and Suppliers tables with the specified constraints.
DROP TABLE IF EXISTS Suppliers;
DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
    product_id   INTEGER      PRIMARY KEY,
    product_name VARCHAR(50)  NOT NULL,
    price        DECIMAL(10,2) CHECK (price > 0),
    stock_qty    INTEGER      CHECK (stock_qty >= 0)
);

CREATE TABLE Suppliers (
    supplier_id   INTEGER     PRIMARY KEY,
    supplier_name VARCHAR(50) NOT NULL,
    contact_no    VARCHAR(20) UNIQUE,
    product_id    INTEGER,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- Q3. [RECONSTRUCTED -- missing from the official list]
--     Add a column to Products recording when each item was last restocked.
ALTER TABLE Products ADD COLUMN last_restocked DATE;

-- ---------------------------------------------------------------------
-- SECTION B: DML (Data Manipulation Language)
-- Step 2: Insert the sample data, then update and delete rows
-- ---------------------------------------------------------------------

-- Q4. Insert at least 5 rows into Products (official sample data).
INSERT INTO Products (product_id, product_name, price, stock_qty) VALUES
    (1, 'Pen',         10.00, 100),
    (2, 'Notebook',    50.00, 200),
    (3, 'Stapler',    120.00,  50),
    (4, 'Marker',      25.00,  80),
    (5, 'File Folder', 60.00, 150);

-- Q5. Insert at least 5 rows into Suppliers (official sample data).
INSERT INTO Suppliers (supplier_id, supplier_name, contact_no, product_id) VALUES
    (101, 'StationeryMart', '9876543210', 1),
    (102, 'PaperWorld',     '9876500000', 2),
    (103, 'OfficeSupplies', '9876512345', 3),
    (104, 'MarkerHub',      '9876522222', 4),
    (105, 'FileDepot',      '9876533333', 5);

-- Q6. Update the stock quantity of [a given product -- the official text is
--     truncated here]. Taking 'Pen' as the product:
UPDATE Products SET stock_qty = 150 WHERE product_name = 'Pen';

-- Q7. Delete a supplier with a specific supplier_id.
DELETE FROM Suppliers WHERE supplier_id = 105;

-- Q8. [Official text truncated] Reconstructed as: increase the price of every
--     product by 5%.
UPDATE Products SET price = price * 1.05;

-- ---------------------------------------------------------------------
-- SECTION C: DQL (SELECT queries)
-- Step 3: Query the tables
-- ---------------------------------------------------------------------

-- Q9. Display all records from the Products table.
SELECT * FROM Products;

-- Q10. Display only product_name and price of all products.
SELECT product_name, price FROM Products;

-- Q11. List all products with a stock quantity less than 100.
SELECT product_name, stock_qty FROM Products WHERE stock_qty < 100;

-- Q12. Show all products in the 20 to 100 price range.
SELECT product_name, price FROM Products WHERE price BETWEEN 20 AND 100;

-- Q13. [RECONSTRUCTED -- missing from the official list]
--      List all products whose name starts with the letter 'S'.
SELECT product_name FROM Products WHERE product_name LIKE 'S%';

-- Q14. Find the average price of products.
SELECT ROUND(AVG(price), 2) AS average_price FROM Products;

-- Q15. Display the total number of products in the inventory.
SELECT COUNT(*) AS total_products FROM Products;

-- Q16. Show the maximum and minimum stock quantities.
SELECT MAX(stock_qty) AS max_stock, MIN(stock_qty) AS min_stock FROM Products;

-- Q17. Count how many suppliers supply each product.
SELECT p.product_name, COUNT(s.supplier_id) AS supplier_count
FROM Products p
LEFT JOIN Suppliers s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name;

-- Q18. Show all products where price > 50 AND stock_qty > 100.
SELECT product_name, price, stock_qty
FROM Products
WHERE price > 50 AND stock_qty > 100;

-- Q19. Show all products where price < 20 OR stock_qty < 80.
SELECT product_name, price, stock_qty
FROM Products
WHERE price < 20 OR stock_qty < 80;

-- Q20. [RECONSTRUCTED -- missing from the official list]
--      Display products ordered by price, highest first.
SELECT product_name, price FROM Products ORDER BY price DESC;

-- Q21. List all suppliers with the product they supply (INNER JOIN).
SELECT s.supplier_name, p.product_name, p.price
FROM Suppliers s
INNER JOIN Products p ON s.product_id = p.product_id;

-- Q22. [RECONSTRUCTED -- missing from the official list]
--      List every product together with its supplier, including products that
--      have no supplier (LEFT JOIN).
SELECT p.product_name, s.supplier_name
FROM Products p
LEFT JOIN Suppliers s ON p.product_id = s.product_id;

-- Q23. Find products whose name is exactly 5 characters long.
--      SQLite/MySQL/Oracle all support LENGTH().
SELECT product_name FROM Products WHERE LENGTH(product_name) = 5;

-- Q24. Find suppliers who supply products costing more than 100.
SELECT s.supplier_name, p.product_name, p.price
FROM Suppliers s
JOIN Products p ON s.product_id = p.product_id
WHERE p.price > 100;

-- ---------------------------------------------------------------------
-- CONSTRAINT CHECKS -- these SHOULD fail. Run them to prove the constraints
-- are doing their job; the runner expects each to raise an error.
-- ---------------------------------------------------------------------
-- INSERT INTO Products VALUES (6, 'Eraser', -5.00, 10, NULL);  -- CHECK price > 0
-- INSERT INTO Products VALUES (1, 'Duplicate', 10.00, 5, NULL); -- PRIMARY KEY
-- INSERT INTO Suppliers VALUES (106, 'Ghost', '9999999999', 99); -- FOREIGN KEY

5. Execution and Results

Q1. Create a database called InventoryDB.

Q2. Create the Products and Suppliers tables with the specified constraints.

Q3. [RECONSTRUCTED -- missing from the official list] Add a column to Products recording
    when each item was last restocked.

Q4. Insert at least 5 rows into Products (official sample data).

Q5. Insert at least 5 rows into Suppliers (official sample data).

Q6. Update the stock quantity of [a given product -- the official text is truncated here].
    Taking 'Pen' as the product:

Q7. Delete a supplier with a specific supplier_id.

Q8. [Official text truncated] Reconstructed as: increase the price of every product by 5%.

Q9. Display all records from the Products table.
┌────────────┬──────────────┬───────┬───────────┬────────────────┐
│ product_id │ product_name │ price │ stock_qty │ last_restocked │
├────────────┼──────────────┼───────┼───────────┼────────────────┤
│ 1          │ Pen          │ 10.5  │ 150       │                │
│ 2          │ Notebook     │ 52.5  │ 200       │                │
│ 3          │ Stapler      │ 126   │ 50        │                │
│ 4          │ Marker       │ 26.25 │ 80        │                │
│ 5          │ File Folder  │ 63    │ 150       │                │
└────────────┴──────────────┴───────┴───────────┴────────────────┘

Q10. Display only product_name and price of all products.
┌──────────────┬───────┐
│ product_name │ price │
├──────────────┼───────┤
│ Pen          │ 10.5  │
│ Notebook     │ 52.5  │
│ Stapler      │ 126   │
│ Marker       │ 26.25 │
│ File Folder  │ 63    │
└──────────────┴───────┘

Q11. List all products with a stock quantity less than 100.
┌──────────────┬───────────┐
│ product_name │ stock_qty │
├──────────────┼───────────┤
│ Stapler      │ 50        │
│ Marker       │ 80        │
└──────────────┴───────────┘

Q12. Show all products in the 20 to 100 price range.
┌──────────────┬───────┐
│ product_name │ price │
├──────────────┼───────┤
│ Notebook     │ 52.5  │
│ Marker       │ 26.25 │
│ File Folder  │ 63    │
└──────────────┴───────┘

Q13. [RECONSTRUCTED -- missing from the official list] List all products whose name starts
    with the letter 'S'.
┌──────────────┐
│ product_name │
├──────────────┤
│ Stapler      │
└──────────────┘

Q14. Find the average price of products.
┌───────────────┐
│ average_price │
├───────────────┤
│ 55.65         │
└───────────────┘

Q15. Display the total number of products in the inventory.
┌────────────────┐
│ total_products │
├────────────────┤
│ 5              │
└────────────────┘

Q16. Show the maximum and minimum stock quantities.
┌───────────┬───────────┐
│ max_stock │ min_stock │
├───────────┼───────────┤
│ 200       │ 50        │
└───────────┴───────────┘

Q17. Count how many suppliers supply each product.
┌──────────────┬────────────────┐
│ product_name │ supplier_count │
├──────────────┼────────────────┤
│ Pen          │ 1              │
│ Notebook     │ 1              │
│ Stapler      │ 1              │
│ Marker       │ 1              │
│ File Folder  │ 0              │
└──────────────┴────────────────┘

Q18. Show all products where price > 50 AND stock_qty > 100.
┌──────────────┬───────┬───────────┐
│ product_name │ price │ stock_qty │
├──────────────┼───────┼───────────┤
│ Notebook     │ 52.5  │ 200       │
│ File Folder  │ 63    │ 150       │
└──────────────┴───────┴───────────┘

Q19. Show all products where price < 20 OR stock_qty < 80.
┌──────────────┬───────┬───────────┐
│ product_name │ price │ stock_qty │
├──────────────┼───────┼───────────┤
│ Pen          │ 10.5  │ 150       │
│ Stapler      │ 126   │ 50        │
└──────────────┴───────┴───────────┘

Q20. [RECONSTRUCTED -- missing from the official list] Display products ordered by price,
    highest first.
┌──────────────┬───────┐
│ product_name │ price │
├──────────────┼───────┤
│ Stapler      │ 126   │
│ File Folder  │ 63    │
│ Notebook     │ 52.5  │
│ Marker       │ 26.25 │
│ Pen          │ 10.5  │
└──────────────┴───────┘

Q21. List all suppliers with the product they supply (INNER JOIN).
┌────────────────┬──────────────┬───────┐
│ supplier_name  │ product_name │ price │
├────────────────┼──────────────┼───────┤
│ StationeryMart │ Pen          │ 10.5  │
│ PaperWorld     │ Notebook     │ 52.5  │
│ OfficeSupplies │ Stapler      │ 126   │
│ MarkerHub      │ Marker       │ 26.25 │
└────────────────┴──────────────┴───────┘

Q22. [RECONSTRUCTED -- missing from the official list] List every product together with its
    supplier, including products that have no supplier (LEFT JOIN).
┌──────────────┬────────────────┐
│ product_name │ supplier_name  │
├──────────────┼────────────────┤
│ Pen          │ StationeryMart │
│ Notebook     │ PaperWorld     │
│ Stapler      │ OfficeSupplies │
│ Marker       │ MarkerHub      │
│ File Folder  │                │
└──────────────┴────────────────┘

Q23. Find products whose name is exactly 5 characters long.

Q24. Find suppliers who supply products costing more than 100.
┌────────────────┬──────────────┬───────┐
│ supplier_name  │ product_name │ price │
├────────────────┼──────────────┼───────┤
│ OfficeSupplies │ Stapler      │ 126   │
└────────────────┴──────────────┴───────┘

Constraint tests: each statement below must be rejected (tools/data-science/run_sql_labs.py runs the same ones).

-- CHECK price > 0
INSERT INTO Products (product_id, product_name, price, stock_qty) VALUES (6, 'Eraser', -5.00, 10);
Error: stepping, CHECK constraint failed: price > 0 (19)

-- PRIMARY KEY uniqueness
INSERT INTO Products (product_id, product_name, price, stock_qty) VALUES (1, 'Duplicate', 10.00, 5);
Error: stepping, UNIQUE constraint failed: Products.product_id (19)

-- NOT NULL on product_name
INSERT INTO Products (product_id, product_name, price, stock_qty) VALUES (7, NULL, 10.00, 5);
Error: stepping, NOT NULL constraint failed: Products.product_name (19)

Worth practising: question 17, "count how many suppliers supply each product", needs a LEFT JOIN — a product with no supplier must still appear with a count of zero. INNER JOIN silently drops it. In the output, File Folder has no supplier and is listed with 0.

Question 23 prints only its label: no product name in the sample data is five characters long, so the query rightly returns no rows.

Experiment 2 — Online Bookstore

1. Question

Build an online bookstore database of authors, books, customers and orders: create the four tables with their keys and constraints, alter them, insert the official sample data, change it, and answer the 36 questions set on it.

2. Aim

Design a four-table schema, and query it with conditions, date functions, aggregates and GROUP BY with HAVING.

3. Steps

1. Create the four tables, then alter them. Section A, questions 1–4.
2. Insert the sample data, then update and delete rows. Section B, questions 5–8.
3. Select rows by condition, pattern and order. Section C, questions 9–23.
4. Use the date functions. Questions 24–27.
5. Summarise with the aggregate functions. Questions 28–32.
6. Group with GROUP BY and HAVING. Questions 33–36.

4. Programme

-- =====================================================================
-- Course 5 (DBMS) Lab -- Experiment 2: Online Bookstore
-- Syllabus source: pages 29-32
--
-- NUMBERING NOTE (SYLLABUS-REVIEW.md finding D4): the official list skips
-- items 12 and 19. Both are reconstructed below, marked [RECONSTRUCTED].
--
-- Dialect: SQLite (executable via tools/run_sql_labs.py). Oracle equivalents
-- for the date functions are given inline -- these differ the most between
-- vendors and are a favourite exam topic.
-- =====================================================================

DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Authors;

-- ---------------------------------------------------------------------
-- SECTION A: DDL (schema design and constraints)
-- Step 1: Create the four tables, then alter them
-- ---------------------------------------------------------------------

-- Q1. Create all four tables with primary keys, foreign keys, appropriate
--     data types and NOT NULL constraints.
CREATE TABLE Authors (
    author_id   INTEGER PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    nationality VARCHAR(50)              -- NULL allowed
);

CREATE TABLE Books (
    book_id          INTEGER PRIMARY KEY,
    title            VARCHAR(200) NOT NULL,
    author_id        INTEGER,
    publication_year INTEGER,
    price            DECIMAL(10,2),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Customers (
    customer_id INTEGER PRIMARY KEY,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL,
    address     VARCHAR(200) NOT NULL
);

CREATE TABLE Orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id     INTEGER,
    order_date  DATE    NOT NULL,
    quantity    INTEGER NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (book_id)     REFERENCES Books(book_id)
);

-- Q2. Alter Books so that price must be greater than 0.
--     Oracle:  ALTER TABLE Books ADD CONSTRAINT chk_price CHECK (price > 0);
--     SQLite cannot ADD CONSTRAINT after creation, so the check belongs in
--     CREATE TABLE. Shown here as the Oracle statement, commented out:
--     ALTER TABLE Books ADD CONSTRAINT chk_price CHECK (price > 0);

-- Q3. Add a unique phone_number column to Customers.
ALTER TABLE Customers ADD COLUMN phone_number VARCHAR(15);
CREATE UNIQUE INDEX idx_customers_phone ON Customers(phone_number);
--     Oracle: ALTER TABLE Customers ADD (phone_number VARCHAR2(15) UNIQUE);

-- Q4. Drop the phone_number column from Customers.
DROP INDEX IF EXISTS idx_customers_phone;
ALTER TABLE Customers DROP COLUMN phone_number;

-- ---------------------------------------------------------------------
-- SECTION B: DML
-- Step 2: Insert the sample data, then update and delete rows
-- ---------------------------------------------------------------------

-- Q5. Insert at least 7 records into each table (official sample data).
INSERT INTO Authors (author_id, first_name, last_name, nationality) VALUES
    (1, 'Jane',    'Austen',         'British'),
    (2, 'George',  'Orwell',         'British'),
    (3, 'Gabriel', 'Garcia Marquez', 'Colombian'),
    (4, 'Toni',    'Morrison',       'American'),
    (5, 'Mark',    'Twain',          'American'),
    (6, 'Harper',  'Lee',            'American'),
    (7, 'Fyodor',  'Dostoevsky',     'Russian');

INSERT INTO Books (book_id, title, author_id, publication_year, price) VALUES
    (101, 'Pride and Prejudice',            1, 1813, 12.99),
    (102, '1984',                           2, 1949,  9.50),
    (103, 'One Hundred Years of Solitude',  3, 1967, 15.00),
    (104, 'Beloved',                        4, 1987, 11.25),
    (105, 'Animal Farm',                    2, 1945,  8.75),
    (106, 'Adventures of Huckleberry Finn', 5, 1884, 10.50),
    (107, 'To Kill a Mockingbird',          6, 1960, 14.00);

INSERT INTO Customers (customer_id, first_name, last_name, email, address) VALUES
    (201, 'Alice',   'Smith',   'alice.s@example.com',   '12 Oak St, London'),
    (202, 'Bob',     'Johnson', 'bob.j@example.com',     '45 Pine Ave, Oxford'),
    (203, 'Charlie', 'Brown',   'charlie.b@example.com', '78 Maple Rd, Bristol'),
    (204, 'Diana',   'Prince',  'diana.p@example.com',   '34 Queen St, York'),
    (205, 'Edward',  'Norton',  'edward.n@example.com',  '22 River Ln, Leeds'),
    (206, 'Fiona',   'Hall',    'fiona.h@example.com',   '56 Lake Dr, Bath'),
    (207, 'Greg',    'Miller',  'greg.m@example.com',    '89 Park Ave, Glasgow');

INSERT INTO Orders (order_id, customer_id, book_id, order_date, quantity) VALUES
    (301, 201, 101, '2025-07-20', 1),
    (302, 202, 102, '2025-07-21', 2),
    (303, 201, 105, '2025-07-22', 1),
    (304, 203, 103, '2025-07-23', 1),
    (305, 204, 106, '2025-07-24', 1),
    (306, 205, 107, '2025-07-25', 3),
    (307, 206, 104, '2025-07-26', 2);

-- Q6. Increase the price of 'Animal Farm' by 10%.
UPDATE Books SET price = price * 1.10 WHERE title = 'Animal Farm';

-- Q7. Delete all orders made before 2025-07-21.
DELETE FROM Orders WHERE order_date < '2025-07-21';

-- Q8. Change the nationality of Gabriel Garcia Marquez to 'Latino-American'.
UPDATE Authors SET nationality = 'Latino-American'
WHERE first_name = 'Gabriel' AND last_name = 'Garcia Marquez';

-- ---------------------------------------------------------------------
-- SECTION C: SELECT queries
-- Step 3: Select rows by condition, pattern and order
-- ---------------------------------------------------------------------

-- Q9. List all books published between 1900 and 2000.
SELECT title, publication_year FROM Books
WHERE publication_year BETWEEN 1900 AND 2000;

-- Q10. Find all customers whose email contains 'example.com'.
SELECT first_name, last_name, email FROM Customers
WHERE email LIKE '%example.com%';

-- Q11. Books priced between 10 and 15 AND published before 1950.
SELECT title, price, publication_year FROM Books
WHERE price BETWEEN 10 AND 15 AND publication_year < 1950;

-- Q12. [RECONSTRUCTED -- missing from the official list]
--      List every book together with its author's full name.
SELECT b.title, a.first_name || ' ' || a.last_name AS author
FROM Books b JOIN Authors a ON b.author_id = a.author_id;
--      Oracle uses || too; MySQL needs CONCAT(a.first_name,' ',a.last_name).

-- Q13. Books priced under 10 OR published after 1980.
SELECT title, price, publication_year FROM Books
WHERE price < 10 OR publication_year > 1980;

-- Q14. Orders placed after 2025-07-22.
SELECT * FROM Orders WHERE order_date > '2025-07-22';

-- Q15. All books written by author_id = 2.
SELECT title FROM Books WHERE author_id = 2;

-- Q16. Customers whose last name starts with 'B'.
SELECT first_name, last_name FROM Customers WHERE last_name LIKE 'B%';

-- Q17. Books with a price NOT between 9 and 13.
SELECT title, price FROM Books WHERE price NOT BETWEEN 9 AND 13;

-- Q18. Books whose publication_year is in (1813, 1945, 1987).
SELECT title, publication_year FROM Books
WHERE publication_year IN (1813, 1945, 1987);

-- Q19. [RECONSTRUCTED -- missing from the official list]
--      Find authors who have no nationality recorded.
SELECT first_name, last_name FROM Authors WHERE nationality IS NULL;
--      Note: use IS NULL, never "= NULL" -- comparing to NULL is never true.

-- Q20. Customers whose address contains the word 'Park'.
SELECT first_name, last_name, address FROM Customers
WHERE address LIKE '%Park%';

-- Q21. All books sorted by price, descending.
SELECT title, price FROM Books ORDER BY price DESC;

-- Q22. Authors in alphabetical order by last_name.
SELECT first_name, last_name FROM Authors ORDER BY last_name ASC;

-- Q23. Orders sorted by order_date, latest first.
SELECT order_id, order_date FROM Orders ORDER BY order_date DESC;

-- ------------------------- DATE FUNCTIONS ---------------------------
-- Step 4: Use the date functions
-- These differ most between vendors. SQLite versions run; Oracle given.

-- Q24. Orders placed in July 2025.
SELECT order_id, order_date FROM Orders
WHERE strftime('%Y-%m', order_date) = '2025-07';
--      Oracle: WHERE TO_CHAR(order_date, 'YYYY-MM') = '2025-07';

-- Q25. Estimated delivery date, 5 days after the order date.
SELECT order_id, order_date,
       DATE(order_date, '+5 days') AS estimated_delivery
FROM Orders;
--      Oracle: SELECT order_id, order_date, order_date + 5 AS estimated_delivery

-- Q26. Customers who placed an order at a weekend.
--      SQLite %w: 0 = Sunday, 6 = Saturday.
SELECT c.first_name, c.last_name, o.order_date,
       CASE strftime('%w', o.order_date)
            WHEN '0' THEN 'Sunday' WHEN '6' THEN 'Saturday' END AS day_name
FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id
WHERE strftime('%w', o.order_date) IN ('0', '6');
--      Oracle: WHERE TO_CHAR(order_date, 'DY') IN ('SAT', 'SUN');

-- Q27. How many days have passed since the last order.
SELECT MAX(order_date) AS last_order,
       CAST(julianday('now') - julianday(MAX(order_date)) AS INTEGER) AS days_since
FROM Orders;
--      Oracle: SELECT SYSDATE - MAX(order_date) FROM Orders;

-- ------------------------ AGGREGATE FUNCTIONS -----------------------
-- Step 5: Summarise with the aggregate functions

-- Q28. Total number of books.
SELECT COUNT(*) AS total_books FROM Books;

-- Q29. Average price of all books.
SELECT ROUND(AVG(price), 2) AS average_price FROM Books;

-- Q30. The highest-priced book.
SELECT title, price FROM Books ORDER BY price DESC LIMIT 1;
--      Oracle 12c+: ... ORDER BY price DESC FETCH FIRST 1 ROWS ONLY;

-- Q31. How many orders each customer has placed.
SELECT c.first_name, c.last_name, COUNT(o.order_id) AS order_count
FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- Q32. Total sales (price x quantity) per customer.
SELECT c.first_name, c.last_name,
       ROUND(SUM(b.price * o.quantity), 2) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Books  b ON o.book_id = b.book_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- --------------------- GROUP BY and HAVING --------------------------
-- Step 6: Group with GROUP BY and HAVING

-- Q33. How many books each author has written.
SELECT a.first_name || ' ' || a.last_name AS author, COUNT(b.book_id) AS books
FROM Authors a LEFT JOIN Books b ON a.author_id = b.author_id
GROUP BY a.author_id, a.first_name, a.last_name;

-- Q34. Total quantity ordered per customer.
SELECT customer_id, SUM(quantity) AS total_quantity
FROM Orders GROUP BY customer_id;

-- Q35. Customers who have ordered more than 2 books in total.
--      HAVING filters groups; WHERE filters rows before grouping. Using
--      WHERE SUM(...) here is a syntax error -- a classic exam trap.
SELECT customer_id, SUM(quantity) AS total_quantity
FROM Orders GROUP BY customer_id HAVING SUM(quantity) > 2;

-- Q36. Total books sold per author.
SELECT a.last_name, SUM(o.quantity) AS copies_sold
FROM Authors a
JOIN Books  b ON a.author_id = b.author_id
JOIN Orders o ON b.book_id = o.book_id
GROUP BY a.author_id, a.last_name
ORDER BY copies_sold DESC;

5. Execution and Results

Q1. Create all four tables with primary keys, foreign keys, appropriate data types and NOT
    NULL constraints.

Q2. Alter Books so that price must be greater than 0.

Q3. Add a unique phone_number column to Customers.

Q4. Drop the phone_number column from Customers.

Q5. Insert at least 7 records into each table (official sample data).

Q6. Increase the price of 'Animal Farm' by 10%.

Q7. Delete all orders made before 2025-07-21.

Q8. Change the nationality of Gabriel Garcia Marquez to 'Latino-American'.

Q9. List all books published between 1900 and 2000.
┌───────────────────────────────┬──────────────────┐
│             title             │ publication_year │
├───────────────────────────────┼──────────────────┤
│ 1984                          │ 1949             │
│ One Hundred Years of Solitude │ 1967             │
│ Beloved                       │ 1987             │
│ Animal Farm                   │ 1945             │
│ To Kill a Mockingbird         │ 1960             │
└───────────────────────────────┴──────────────────┘

Q10. Find all customers whose email contains 'example.com'.
┌────────────┬───────────┬───────────────────────┐
│ first_name │ last_name │         email         │
├────────────┼───────────┼───────────────────────┤
│ Alice      │ Smith     │ alice.s@example.com   │
│ Bob        │ Johnson   │ bob.j@example.com     │
│ Charlie    │ Brown     │ charlie.b@example.com │
│ Diana      │ Prince    │ diana.p@example.com   │
│ Edward     │ Norton    │ edward.n@example.com  │
│ Fiona      │ Hall      │ fiona.h@example.com   │
│ Greg       │ Miller    │ greg.m@example.com    │
└────────────┴───────────┴───────────────────────┘

Q11. Books priced between 10 and 15 AND published before 1950.
┌────────────────────────────────┬───────┬──────────────────┐
│             title              │ price │ publication_year │
├────────────────────────────────┼───────┼──────────────────┤
│ Pride and Prejudice            │ 12.99 │ 1813             │
│ Adventures of Huckleberry Finn │ 10.5  │ 1884             │
└────────────────────────────────┴───────┴──────────────────┘

Q12. [RECONSTRUCTED -- missing from the official list] List every book together with its
    author's full name.
┌────────────────────────────────┬────────────────────────┐
│             title              │         author         │
├────────────────────────────────┼────────────────────────┤
│ Pride and Prejudice            │ Jane Austen            │
│ 1984                           │ George Orwell          │
│ One Hundred Years of Solitude  │ Gabriel Garcia Marquez │
│ Beloved                        │ Toni Morrison          │
│ Animal Farm                    │ George Orwell          │
│ Adventures of Huckleberry Finn │ Mark Twain             │
│ To Kill a Mockingbird          │ Harper Lee             │
└────────────────────────────────┴────────────────────────┘

Q13. Books priced under 10 OR published after 1980.
┌─────────────┬───────┬──────────────────┐
│    title    │ price │ publication_year │
├─────────────┼───────┼──────────────────┤
│ 1984        │ 9.5   │ 1949             │
│ Beloved     │ 11.25 │ 1987             │
│ Animal Farm │ 9.625 │ 1945             │
└─────────────┴───────┴──────────────────┘

Q14. Orders placed after 2025-07-22.
┌──────────┬─────────────┬─────────┬────────────┬──────────┐
│ order_id │ customer_id │ book_id │ order_date │ quantity │
├──────────┼─────────────┼─────────┼────────────┼──────────┤
│ 304      │ 203         │ 103     │ 2025-07-23 │ 1        │
│ 305      │ 204         │ 106     │ 2025-07-24 │ 1        │
│ 306      │ 205         │ 107     │ 2025-07-25 │ 3        │
│ 307      │ 206         │ 104     │ 2025-07-26 │ 2        │
└──────────┴─────────────┴─────────┴────────────┴──────────┘

Q15. All books written by author_id = 2.
┌─────────────┐
│    title    │
├─────────────┤
│ 1984        │
│ Animal Farm │
└─────────────┘

Q16. Customers whose last name starts with 'B'.
┌────────────┬───────────┐
│ first_name │ last_name │
├────────────┼───────────┤
│ Charlie    │ Brown     │
└────────────┴───────────┘

Q17. Books with a price NOT between 9 and 13.
┌───────────────────────────────┬───────┐
│             title             │ price │
├───────────────────────────────┼───────┤
│ One Hundred Years of Solitude │ 15    │
│ To Kill a Mockingbird         │ 14    │
└───────────────────────────────┴───────┘

Q18. Books whose publication_year is in (1813, 1945, 1987).
┌─────────────────────┬──────────────────┐
│        title        │ publication_year │
├─────────────────────┼──────────────────┤
│ Pride and Prejudice │ 1813             │
│ Beloved             │ 1987             │
│ Animal Farm         │ 1945             │
└─────────────────────┴──────────────────┘

Q19. [RECONSTRUCTED -- missing from the official list] Find authors who have no nationality
    recorded.

Q20. Customers whose address contains the word 'Park'.
┌────────────┬───────────┬──────────────────────┐
│ first_name │ last_name │       address        │
├────────────┼───────────┼──────────────────────┤
│ Greg       │ Miller    │ 89 Park Ave, Glasgow │
└────────────┴───────────┴──────────────────────┘

Q21. All books sorted by price, descending.
┌────────────────────────────────┬───────┐
│             title              │ price │
├────────────────────────────────┼───────┤
│ One Hundred Years of Solitude  │ 15    │
│ To Kill a Mockingbird          │ 14    │
│ Pride and Prejudice            │ 12.99 │
│ Beloved                        │ 11.25 │
│ Adventures of Huckleberry Finn │ 10.5  │
│ Animal Farm                    │ 9.625 │
│ 1984                           │ 9.5   │
└────────────────────────────────┴───────┘

Q22. Authors in alphabetical order by last_name.
┌────────────┬────────────────┐
│ first_name │   last_name    │
├────────────┼────────────────┤
│ Jane       │ Austen         │
│ Fyodor     │ Dostoevsky     │
│ Gabriel    │ Garcia Marquez │
│ Harper     │ Lee            │
│ Toni       │ Morrison       │
│ George     │ Orwell         │
│ Mark       │ Twain          │
└────────────┴────────────────┘

Q23. Orders sorted by order_date, latest first.
┌──────────┬────────────┐
│ order_id │ order_date │
├──────────┼────────────┤
│ 307      │ 2025-07-26 │
│ 306      │ 2025-07-25 │
│ 305      │ 2025-07-24 │
│ 304      │ 2025-07-23 │
│ 303      │ 2025-07-22 │
│ 302      │ 2025-07-21 │
└──────────┴────────────┘

Q24. Orders placed in July 2025.
┌──────────┬────────────┐
│ order_id │ order_date │
├──────────┼────────────┤
│ 302      │ 2025-07-21 │
│ 303      │ 2025-07-22 │
│ 304      │ 2025-07-23 │
│ 305      │ 2025-07-24 │
│ 306      │ 2025-07-25 │
│ 307      │ 2025-07-26 │
└──────────┴────────────┘

Q25. Estimated delivery date, 5 days after the order date.
┌──────────┬────────────┬────────────────────┐
│ order_id │ order_date │ estimated_delivery │
├──────────┼────────────┼────────────────────┤
│ 302      │ 2025-07-21 │ 2025-07-26         │
│ 303      │ 2025-07-22 │ 2025-07-27         │
│ 304      │ 2025-07-23 │ 2025-07-28         │
│ 305      │ 2025-07-24 │ 2025-07-29         │
│ 306      │ 2025-07-25 │ 2025-07-30         │
│ 307      │ 2025-07-26 │ 2025-07-31         │
└──────────┴────────────┴────────────────────┘

Q26. Customers who placed an order at a weekend.
┌────────────┬───────────┬────────────┬──────────┐
│ first_name │ last_name │ order_date │ day_name │
├────────────┼───────────┼────────────┼──────────┤
│ Fiona      │ Hall      │ 2025-07-26 │ Saturday │
└────────────┴───────────┴────────────┴──────────┘

Q27. How many days have passed since the last order.
┌────────────┬────────────┐
│ last_order │ days_since │
├────────────┼────────────┤
│ 2025-07-26 │ 435        │
└────────────┴────────────┘

Q28. Total number of books.
┌─────────────┐
│ total_books │
├─────────────┤
│ 7           │
└─────────────┘

Q29. Average price of all books.
┌───────────────┐
│ average_price │
├───────────────┤
│ 11.84         │
└───────────────┘

Q30. The highest-priced book.
┌───────────────────────────────┬───────┐
│             title             │ price │
├───────────────────────────────┼───────┤
│ One Hundred Years of Solitude │ 15    │
└───────────────────────────────┴───────┘

Q31. How many orders each customer has placed.
┌────────────┬───────────┬─────────────┐
│ first_name │ last_name │ order_count │
├────────────┼───────────┼─────────────┤
│ Alice      │ Smith     │ 1           │
│ Bob        │ Johnson   │ 1           │
│ Charlie    │ Brown     │ 1           │
│ Diana      │ Prince    │ 1           │
│ Edward     │ Norton    │ 1           │
│ Fiona      │ Hall      │ 1           │
│ Greg       │ Miller    │ 0           │
└────────────┴───────────┴─────────────┘

Q32. Total sales (price x quantity) per customer.
┌────────────┬───────────┬─────────────┐
│ first_name │ last_name │ total_spent │
├────────────┼───────────┼─────────────┤
│ Alice      │ Smith     │ 9.63        │
│ Bob        │ Johnson   │ 19.0        │
│ Charlie    │ Brown     │ 15.0        │
│ Diana      │ Prince    │ 10.5        │
│ Edward     │ Norton    │ 42.0        │
│ Fiona      │ Hall      │ 22.5        │
└────────────┴───────────┴─────────────┘

Q33. How many books each author has written.
┌────────────────────────┬───────┐
│         author         │ books │
├────────────────────────┼───────┤
│ Jane Austen            │ 1     │
│ George Orwell          │ 2     │
│ Gabriel Garcia Marquez │ 1     │
│ Toni Morrison          │ 1     │
│ Mark Twain             │ 1     │
│ Harper Lee             │ 1     │
│ Fyodor Dostoevsky      │ 0     │
└────────────────────────┴───────┘

Q34. Total quantity ordered per customer.
┌─────────────┬────────────────┐
│ customer_id │ total_quantity │
├─────────────┼────────────────┤
│ 201         │ 1              │
│ 202         │ 2              │
│ 203         │ 1              │
│ 204         │ 1              │
│ 205         │ 3              │
│ 206         │ 2              │
└─────────────┴────────────────┘

Q35. Customers who have ordered more than 2 books in total.
┌─────────────┬────────────────┐
│ customer_id │ total_quantity │
├─────────────┼────────────────┤
│ 205         │ 3              │
└─────────────┴────────────────┘

Q36. Total books sold per author.
┌────────────────┬─────────────┐
│   last_name    │ copies_sold │
├────────────────┼─────────────┤
│ Orwell         │ 3           │
│ Lee            │ 3           │
│ Morrison       │ 2           │
│ Garcia Marquez │ 1           │
│ Twain          │ 1           │
└────────────────┴─────────────┘

Constraint tests: each statement below must be rejected (tools/data-science/run_sql_labs.py runs the same ones).

-- UNIQUE email
INSERT INTO Customers (customer_id, first_name, last_name, email, address) VALUES (208, 'Dup', 'Email', 'alice.s@example.com', 'somewhere');
Error: stepping, UNIQUE constraint failed: Customers.email (19)

-- NOT NULL on order_date
INSERT INTO Orders (order_id, customer_id, book_id, order_date, quantity) VALUES (308, 201, 101, NULL, 1);
Error: stepping, NOT NULL constraint failed: Orders.order_date (19)

Section C also covers GROUP BY with HAVING — question 35, "customers who have ordered more than 2 books in total", is the standard HAVING question. Here only customer 205 has, with 3.

Question 27 counts the days from the last order, 26 July 2025, to the day the script runs; on 4 October 2026 that is 435. Run it yourself and you will get a different number.

After question 6, Animal Farm costs 9.625: 10% on 8.75. The column is declared DECIMAL(10,2), but SQLite does not enforce the two decimal places, so it keeps all three. Oracle's NUMBER(10,2) would round it to 9.63 as it is stored.

Experiment 3 — Employee Database

1. Question

Build an employee database of departments, employees, projects and the employees' work on projects: create the tables with their constraints, insert the official sample data, change it, and answer the questions set in sections A–D, two of which must fail.

2. Aim

Build a schema with a self-referential and a many-to-many relationship, show that its constraints reject bad data, and query it with grouping and joins.

3. Steps

1. Create the four tables, then add and drop a column. Section A, questions 1–3.
2. Insert the sample data, then update and delete rows. Section B, questions 4–9. Questions 5 and 9 must fail, so the script leaves them as comments, and they are run separately, after it.
3. Query, group and summarise the employees. Section C, questions 10–28.
4. Join the tables. Section D, questions 29–34, then a self join.

4. Programme

-- =====================================================================
-- Course 5 (DBMS) Lab -- Experiment 3: Employee Database
-- Syllabus source: pages 33-37 (Sections A-D; Section E PL/SQL is in
-- 04_plsql_oracle.sql because SQLite cannot execute PL/SQL)
--
-- NUMBERING NOTE (SYLLABUS-REVIEW.md finding D4): the official list skips
-- item 8. It is reconstructed below, marked [RECONSTRUCTED].
--
-- Dialect: SQLite (executable via tools/run_sql_labs.py); Oracle noted inline.
-- =====================================================================

PRAGMA foreign_keys = ON;   -- SQLite needs this; other DBMSs enforce FKs always

DROP TABLE IF EXISTS Employee_Project;
DROP TABLE IF EXISTS Projects;
DROP TABLE IF EXISTS Employees;
DROP TABLE IF EXISTS Departments;

-- ---------------------------------------------------------------------
-- SECTION A: DDL
-- Step 1: Create the four tables, then add and drop a column
-- ---------------------------------------------------------------------

-- Q1. Create the tables with the specified constraints.
CREATE TABLE Departments (
    dept_id   INTEGER PRIMARY KEY,
    dept_name VARCHAR(50) UNIQUE NOT NULL,
    location  VARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
    emp_id     INTEGER PRIMARY KEY,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    phone      VARCHAR(20)  CHECK (phone LIKE '___-___-____'),
    hire_date  DATE         NOT NULL,
    job_title  VARCHAR(50)  NOT NULL,
    salary     DECIMAL(10,2) CHECK (salary > 0),
    dept_id    INTEGER,
    manager_id INTEGER,
    FOREIGN KEY (dept_id)    REFERENCES Departments(dept_id),
    FOREIGN KEY (manager_id) REFERENCES Employees(emp_id)   -- self-referential
);

CREATE TABLE Projects (
    project_id   INTEGER PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    start_date   DATE NOT NULL,
    end_date     DATE,
    dept_id      INTEGER,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

CREATE TABLE Employee_Project (
    emp_id          INTEGER,
    project_id      INTEGER,
    hours_allocated INTEGER CHECK (hours_allocated > 0),
    PRIMARY KEY (emp_id, project_id),          -- composite key, many-to-many
    FOREIGN KEY (emp_id)     REFERENCES Employees(emp_id),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

-- Q2. Add a bonus column with default 0.
ALTER TABLE Employees ADD COLUMN bonus DECIMAL(8,2) DEFAULT 0;

-- Q3. Drop the bonus column.
ALTER TABLE Employees DROP COLUMN bonus;
--     Oracle: ALTER TABLE Employees DROP COLUMN bonus;

-- ---------------------------------------------------------------------
-- SECTION B: DML
-- Step 2: Insert the sample data, then update and delete rows
-- ---------------------------------------------------------------------

-- Q4. Insert 10 rows into each table (official sample data).
INSERT INTO Departments (dept_id, dept_name, location) VALUES
    (1,  'HR',            'New York'),
    (2,  'IT',            'San Francisco'),
    (3,  'Finance',       'Chicago'),
    (4,  'Marketing',     'Boston'),
    (5,  'Operations',    'Seattle'),
    (6,  'Legal',         'Washington D.C.'),
    (7,  'Sales',         'Dallas'),
    (8,  'R&D',           'Austin'),
    (9,  'Procurement',   'Denver'),
    (10, 'Customer Care', 'Miami');

-- Managers are inserted before their reports so the self-referential FK holds.
INSERT INTO Employees (emp_id, first_name, last_name, email, phone, hire_date,
                       job_title, salary, dept_id, manager_id) VALUES
    (101, 'Alice',  'Johnson', 'alice.j@corp.com',  '123-456-7890', '2020-03-15', 'HR Manager',         75000, 1, NULL),
    (104, 'Diana',  'Prince',  'diana.p@corp.com',  '456-789-0123', '2018-07-12', 'IT Manager',         90000, 2, NULL),
    (106, 'Fiona',  'Hall',    'fiona.h@corp.com',  '678-901-2345', '2017-11-01', 'Finance Manager',    85000, 3, NULL),
    (102, 'Bob',    'Smith',   'bob.s@corp.com',    '234-567-8901', '2019-05-20', 'IT Analyst',         65000, 2, 104),
    (103, 'Charlie','Brown',   'charlie.b@corp.com','345-678-9012', '2021-01-10', 'Finance Executive',  58000, 3, 106),
    (105, 'Ethan',  'Hunt',    'ethan.h@corp.com',  '567-890-1234', '2022-02-25', 'Marketing Lead',     62000, 4, NULL),
    (107, 'Greg',   'Miles',   'greg.m@corp.com',   '789-012-3456', '2023-04-15', 'IT Support',         45000, 2, 104),
    (108, 'Hannah', 'White',   'hannah.w@corp.com', '890-123-4567', '2021-09-05', 'HR Executive',       50000, 1, 101),
    (109, 'Ian',    'Scott',   'ian.s@corp.com',    '901-234-5678', '2020-11-20', 'Operations Analyst', 56000, 5, NULL),
    (110, 'Julia',  'Adams',   'julia.a@corp.com',  '012-345-6789', '2019-12-18', 'Legal Advisor',      70000, 6, NULL);

INSERT INTO Projects (project_id, project_name, start_date, end_date, dept_id) VALUES
    (201, 'Payroll System',     '2023-01-01', NULL, 3),
    (202, 'Website Upgrade',    '2023-02-10', NULL, 2),
    (203, 'Recruitment Drive',  '2023-03-05', NULL, 1),
    (204, 'Ad Campaign',        '2023-05-20', NULL, 4),
    (205, 'New CRM Tool',       '2023-04-15', NULL, 7),
    (206, 'Compliance Portal',  '2023-06-10', NULL, 6),
    (207, 'Inventory System',   '2023-07-01', NULL, 5),
    (208, 'AI Research',        '2023-08-05', NULL, 8),
    (209, 'Customer Feedback',  '2023-09-10', NULL, 10),
    (210, 'Procurement System', '2023-10-01', NULL, 9);

INSERT INTO Employee_Project (emp_id, project_id, hours_allocated) VALUES
    (102, 202, 120), (104, 202,  80), (103, 201, 100), (106, 201, 150),
    (101, 203,  50), (105, 204,  70), (107, 202,  60), (109, 207,  90),
    (110, 206, 110), (108, 203,  40);

-- Q5. Try inserting an employee with a negative salary -- SHOULD FAIL on the
--     CHECK constraint. Commented out so the script completes; the runner
--     tests it separately and expects an error.
-- INSERT INTO Employees VALUES (111,'Test','User','t@corp.com','111-222-3333',
--     '2024-01-01','Tester',-5000,1,NULL);

-- Q6. Increase the salary of emp_id 103 by 15%.
UPDATE Employees SET salary = salary * 1.15 WHERE emp_id = 103;

-- Q7. Delete an employee who has resigned.
--     emp_id 105 is used because nothing references them as a manager.
DELETE FROM Employee_Project WHERE emp_id = 105;   -- clear the child rows first
DELETE FROM Employees WHERE emp_id = 105;

-- Q8. [RECONSTRUCTED -- missing from the official list]
--     Give every IT department employee a 10% raise.
UPDATE Employees SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'IT');

-- Q9. Change an employee's department to 'Research' -- SHOULD FAIL, because no
--     such department exists (foreign key violation). Tested by the runner.
-- UPDATE Employees SET dept_id = 99 WHERE emp_id = 102;

-- ---------------------------------------------------------------------
-- SECTION C: DQL
-- Step 3: Query, group and summarise the employees
-- ---------------------------------------------------------------------

-- Q10. List all employees and their details.
SELECT * FROM Employees;

-- Q11. All employees in the HR department.
SELECT e.first_name, e.last_name, e.job_title
FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'HR';

-- Q12. Employees with salaries between 50,000 and 80,000.
SELECT first_name, last_name, salary FROM Employees
WHERE salary BETWEEN 50000 AND 80000;

-- Q13. Employees hired after 2020.
SELECT first_name, last_name, hire_date FROM Employees
WHERE hire_date > '2020-12-31';

-- Q14. Employees in either the IT or Finance department.
SELECT e.first_name, e.last_name, d.dept_name
FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_name IN ('IT', 'Finance');

-- Q15. Employees whose email ends with '@corp.com'.
SELECT first_name, email FROM Employees WHERE email LIKE '%@corp.com';

-- Q16. Salary > 60,000 AND located in New York.
SELECT e.first_name, e.last_name, e.salary, d.location
FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id
WHERE e.salary > 60000 AND d.location = 'New York';

-- Q17. Employees in descending order of salary.
SELECT first_name, last_name, salary FROM Employees ORDER BY salary DESC;

-- Q18. Number of employees in each department.
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM Departments d LEFT JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- Q19. Average salary department-wise.
SELECT d.dept_name, ROUND(AVG(e.salary), 2) AS avg_salary
FROM Departments d JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- Q20. Departments where the average salary exceeds 70,000.
SELECT d.dept_name, ROUND(AVG(e.salary), 2) AS avg_salary
FROM Departments d JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > 70000;

-- Q21. Number of employees on each project.
SELECT p.project_name, COUNT(ep.emp_id) AS employee_count
FROM Projects p LEFT JOIN Employee_Project ep ON p.project_id = ep.project_id
GROUP BY p.project_id, p.project_name;

-- Q22. Departments with more than 3 employees.
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM Departments d JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 3;

-- Q23. Sum of all salaries department-wise.
SELECT d.dept_name, SUM(e.salary) AS total_salary
FROM Departments d JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- Q24. Distinct department IDs present in Employees.
SELECT DISTINCT dept_id FROM Employees ORDER BY dept_id;

-- Q25. Employee names with their year of hire.
SELECT first_name, last_name, strftime('%Y', hire_date) AS hire_year
FROM Employees;
--      Oracle: TO_CHAR(hire_date, 'YYYY') or EXTRACT(YEAR FROM hire_date)

-- Q26. Employees grouped by year of hire.
SELECT strftime('%Y', hire_date) AS hire_year, COUNT(*) AS employees
FROM Employees GROUP BY hire_year ORDER BY hire_year;

-- Q27. Employees hired in the last 90 days.
SELECT first_name, last_name, hire_date FROM Employees
WHERE julianday('now') - julianday(hire_date) <= 90;
--      Oracle: WHERE hire_date >= SYSDATE - 90;
--      (The sample data is from 2017-2023, so this correctly returns nothing.)

-- Q28. Years of experience of every employee.
SELECT first_name, last_name, hire_date,
       CAST((julianday('now') - julianday(hire_date)) / 365.25 AS INTEGER)
           AS years_experience
FROM Employees ORDER BY years_experience DESC;
--      Oracle: MONTHS_BETWEEN(SYSDATE, hire_date)/12

-- ---------------------------------------------------------------------
-- SECTION D: Joins
-- Step 4: Join the tables
-- ---------------------------------------------------------------------

-- Q29. All employees with their department names (INNER JOIN).
SELECT e.first_name, e.last_name, d.dept_name
FROM Employees e INNER JOIN Departments d ON e.dept_id = d.dept_id;

-- Q30. All departments with employees, including empty departments (LEFT JOIN).
SELECT d.dept_name, e.first_name, e.last_name
FROM Departments d LEFT JOIN Employees e ON d.dept_id = e.dept_id
ORDER BY d.dept_name;

-- Q31. Employees and the projects they work on (three-table join).
SELECT e.first_name, e.last_name, p.project_name, ep.hours_allocated
FROM Employees e
JOIN Employee_Project ep ON e.emp_id = ep.emp_id
JOIN Projects p          ON ep.project_id = p.project_id
ORDER BY e.first_name;

-- Q32. Projects with the total hours allocated.
SELECT p.project_name, SUM(ep.hours_allocated) AS total_hours
FROM Projects p JOIN Employee_Project ep ON p.project_id = ep.project_id
GROUP BY p.project_id, p.project_name
ORDER BY total_hours DESC;

-- Q33. Employees working on more than one project.
SELECT e.first_name, e.last_name, COUNT(ep.project_id) AS project_count
FROM Employees e JOIN Employee_Project ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.first_name, e.last_name
HAVING COUNT(ep.project_id) > 1;

-- Q34. All projects handled by the Finance department.
SELECT p.project_name, p.start_date
FROM Projects p JOIN Departments d ON p.dept_id = d.dept_id
WHERE d.dept_name = 'Finance';

-- BONUS: SELF JOIN -- each employee alongside their manager. The syllabus
-- defines manager_id as self-referential, so this is a likely exam question.
SELECT e.first_name || ' ' || e.last_name AS employee,
       COALESCE(m.first_name || ' ' || m.last_name, 'No manager') AS manager
FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.emp_id;

5. Execution and Results

Q1. Create the tables with the specified constraints.

Q2. Add a bonus column with default 0.

Q3. Drop the bonus column.

Q4. Insert 10 rows into each table (official sample data).

Q5. Try inserting an employee with a negative salary -- SHOULD FAIL on the CHECK constraint.
    Commented out so the script completes; the runner tests it separately and expects an
    error.

Q6. Increase the salary of emp_id 103 by 15%.

Q7. Delete an employee who has resigned.

Q8. [RECONSTRUCTED -- missing from the official list] Give every IT department employee a
    10% raise.

Q9. Change an employee's department to 'Research' -- SHOULD FAIL, because no such department
    exists (foreign key violation). Tested by the runner.

Q10. List all employees and their details.
┌────────┬────────────┬───────────┬────────────────────┬──────────────┬────────────┬────────────────────┬─────────┬─────────┬────────────┐
│ emp_id │ first_name │ last_name │       email        │    phone     │ hire_date  │     job_title      │ salary  │ dept_id │ manager_id │
├────────┼────────────┼───────────┼────────────────────┼──────────────┼────────────┼────────────────────┼─────────┼─────────┼────────────┤
│ 101    │ Alice      │ Johnson   │ alice.j@corp.com   │ 123-456-7890 │ 2020-03-15 │ HR Manager         │ 75000   │ 1       │            │
│ 102    │ Bob        │ Smith     │ bob.s@corp.com     │ 234-567-8901 │ 2019-05-20 │ IT Analyst         │ 71500   │ 2       │ 104        │
│ 103    │ Charlie    │ Brown     │ charlie.b@corp.com │ 345-678-9012 │ 2021-01-10 │ Finance Executive  │ 66700   │ 3       │ 106        │
│ 104    │ Diana      │ Prince    │ diana.p@corp.com   │ 456-789-0123 │ 2018-07-12 │ IT Manager         │ 99000.0 │ 2       │            │
│ 106    │ Fiona      │ Hall      │ fiona.h@corp.com   │ 678-901-2345 │ 2017-11-01 │ Finance Manager    │ 85000   │ 3       │            │
│ 107    │ Greg       │ Miles     │ greg.m@corp.com    │ 789-012-3456 │ 2023-04-15 │ IT Support         │ 49500.0 │ 2       │ 104        │
│ 108    │ Hannah     │ White     │ hannah.w@corp.com  │ 890-123-4567 │ 2021-09-05 │ HR Executive       │ 50000   │ 1       │ 101        │
│ 109    │ Ian        │ Scott     │ ian.s@corp.com     │ 901-234-5678 │ 2020-11-20 │ Operations Analyst │ 56000   │ 5       │            │
│ 110    │ Julia      │ Adams     │ julia.a@corp.com   │ 012-345-6789 │ 2019-12-18 │ Legal Advisor      │ 70000   │ 6       │            │
└────────┴────────────┴───────────┴────────────────────┴──────────────┴────────────┴────────────────────┴─────────┴─────────┴────────────┘

Q11. All employees in the HR department.
┌────────────┬───────────┬──────────────┐
│ first_name │ last_name │  job_title   │
├────────────┼───────────┼──────────────┤
│ Alice      │ Johnson   │ HR Manager   │
│ Hannah     │ White     │ HR Executive │
└────────────┴───────────┴──────────────┘

Q12. Employees with salaries between 50,000 and 80,000.
┌────────────┬───────────┬────────┐
│ first_name │ last_name │ salary │
├────────────┼───────────┼────────┤
│ Alice      │ Johnson   │ 75000  │
│ Bob        │ Smith     │ 71500  │
│ Charlie    │ Brown     │ 66700  │
│ Hannah     │ White     │ 50000  │
│ Ian        │ Scott     │ 56000  │
│ Julia      │ Adams     │ 70000  │
└────────────┴───────────┴────────┘

Q13. Employees hired after 2020.
┌────────────┬───────────┬────────────┐
│ first_name │ last_name │ hire_date  │
├────────────┼───────────┼────────────┤
│ Charlie    │ Brown     │ 2021-01-10 │
│ Greg       │ Miles     │ 2023-04-15 │
│ Hannah     │ White     │ 2021-09-05 │
└────────────┴───────────┴────────────┘

Q14. Employees in either the IT or Finance department.
┌────────────┬───────────┬───────────┐
│ first_name │ last_name │ dept_name │
├────────────┼───────────┼───────────┤
│ Charlie    │ Brown     │ Finance   │
│ Fiona      │ Hall      │ Finance   │
│ Bob        │ Smith     │ IT        │
│ Diana      │ Prince    │ IT        │
│ Greg       │ Miles     │ IT        │
└────────────┴───────────┴───────────┘

Q15. Employees whose email ends with '@corp.com'.
┌────────────┬────────────────────┐
│ first_name │       email        │
├────────────┼────────────────────┤
│ Alice      │ alice.j@corp.com   │
│ Bob        │ bob.s@corp.com     │
│ Charlie    │ charlie.b@corp.com │
│ Diana      │ diana.p@corp.com   │
│ Fiona      │ fiona.h@corp.com   │
│ Greg       │ greg.m@corp.com    │
│ Hannah     │ hannah.w@corp.com  │
│ Ian        │ ian.s@corp.com     │
│ Julia      │ julia.a@corp.com   │
└────────────┴────────────────────┘

Q16. Salary > 60,000 AND located in New York.
┌────────────┬───────────┬────────┬──────────┐
│ first_name │ last_name │ salary │ location │
├────────────┼───────────┼────────┼──────────┤
│ Alice      │ Johnson   │ 75000  │ New York │
└────────────┴───────────┴────────┴──────────┘

Q17. Employees in descending order of salary.
┌────────────┬───────────┬─────────┐
│ first_name │ last_name │ salary  │
├────────────┼───────────┼─────────┤
│ Diana      │ Prince    │ 99000.0 │
│ Fiona      │ Hall      │ 85000   │
│ Alice      │ Johnson   │ 75000   │
│ Bob        │ Smith     │ 71500   │
│ Julia      │ Adams     │ 70000   │
│ Charlie    │ Brown     │ 66700   │
│ Ian        │ Scott     │ 56000   │
│ Hannah     │ White     │ 50000   │
│ Greg       │ Miles     │ 49500.0 │
└────────────┴───────────┴─────────┘

Q18. Number of employees in each department.
┌───────────────┬────────────────┐
│   dept_name   │ employee_count │
├───────────────┼────────────────┤
│ Customer Care │ 0              │
│ Finance       │ 2              │
│ HR            │ 2              │
│ IT            │ 3              │
│ Legal         │ 1              │
│ Marketing     │ 0              │
│ Operations    │ 1              │
│ Procurement   │ 0              │
│ R&D           │ 0              │
│ Sales         │ 0              │
└───────────────┴────────────────┘

Q19. Average salary department-wise.
┌────────────┬────────────┐
│ dept_name  │ avg_salary │
├────────────┼────────────┤
│ HR         │ 62500.0    │
│ IT         │ 73333.33   │
│ Finance    │ 75850.0    │
│ Operations │ 56000.0    │
│ Legal      │ 70000.0    │
└────────────┴────────────┘

Q20. Departments where the average salary exceeds 70,000.
┌───────────┬────────────┐
│ dept_name │ avg_salary │
├───────────┼────────────┤
│ IT        │ 73333.33   │
│ Finance   │ 75850.0    │
└───────────┴────────────┘

Q21. Number of employees on each project.
┌────────────────────┬────────────────┐
│    project_name    │ employee_count │
├────────────────────┼────────────────┤
│ Payroll System     │ 2              │
│ Website Upgrade    │ 3              │
│ Recruitment Drive  │ 2              │
│ Ad Campaign        │ 0              │
│ New CRM Tool       │ 0              │
│ Compliance Portal  │ 1              │
│ Inventory System   │ 1              │
│ AI Research        │ 0              │
│ Customer Feedback  │ 0              │
│ Procurement System │ 0              │
└────────────────────┴────────────────┘

Q22. Departments with more than 3 employees.

Q23. Sum of all salaries department-wise.
┌────────────┬──────────────┐
│ dept_name  │ total_salary │
├────────────┼──────────────┤
│ HR         │ 125000       │
│ IT         │ 220000.0     │
│ Finance    │ 151700       │
│ Operations │ 56000        │
│ Legal      │ 70000        │
└────────────┴──────────────┘

Q24. Distinct department IDs present in Employees.
┌─────────┐
│ dept_id │
├─────────┤
│ 1       │
│ 2       │
│ 3       │
│ 5       │
│ 6       │
└─────────┘

Q25. Employee names with their year of hire.
┌────────────┬───────────┬───────────┐
│ first_name │ last_name │ hire_year │
├────────────┼───────────┼───────────┤
│ Alice      │ Johnson   │ 2020      │
│ Bob        │ Smith     │ 2019      │
│ Charlie    │ Brown     │ 2021      │
│ Diana      │ Prince    │ 2018      │
│ Fiona      │ Hall      │ 2017      │
│ Greg       │ Miles     │ 2023      │
│ Hannah     │ White     │ 2021      │
│ Ian        │ Scott     │ 2020      │
│ Julia      │ Adams     │ 2019      │
└────────────┴───────────┴───────────┘

Q26. Employees grouped by year of hire.
┌───────────┬───────────┐
│ hire_year │ employees │
├───────────┼───────────┤
│ 2017      │ 1         │
│ 2018      │ 1         │
│ 2019      │ 2         │
│ 2020      │ 2         │
│ 2021      │ 2         │
│ 2023      │ 1         │
└───────────┴───────────┘

Q27. Employees hired in the last 90 days.

Q28. Years of experience of every employee.
┌────────────┬───────────┬────────────┬──────────────────┐
│ first_name │ last_name │ hire_date  │ years_experience │
├────────────┼───────────┼────────────┼──────────────────┤
│ Diana      │ Prince    │ 2018-07-12 │ 8                │
│ Fiona      │ Hall      │ 2017-11-01 │ 8                │
│ Bob        │ Smith     │ 2019-05-20 │ 7                │
│ Alice      │ Johnson   │ 2020-03-15 │ 6                │
│ Julia      │ Adams     │ 2019-12-18 │ 6                │
│ Charlie    │ Brown     │ 2021-01-10 │ 5                │
│ Hannah     │ White     │ 2021-09-05 │ 5                │
│ Ian        │ Scott     │ 2020-11-20 │ 5                │
│ Greg       │ Miles     │ 2023-04-15 │ 3                │
└────────────┴───────────┴────────────┴──────────────────┘

Q29. All employees with their department names (INNER JOIN).
┌────────────┬───────────┬────────────┐
│ first_name │ last_name │ dept_name  │
├────────────┼───────────┼────────────┤
│ Alice      │ Johnson   │ HR         │
│ Bob        │ Smith     │ IT         │
│ Charlie    │ Brown     │ Finance    │
│ Diana      │ Prince    │ IT         │
│ Fiona      │ Hall      │ Finance    │
│ Greg       │ Miles     │ IT         │
│ Hannah     │ White     │ HR         │
│ Ian        │ Scott     │ Operations │
│ Julia      │ Adams     │ Legal      │
└────────────┴───────────┴────────────┘

Q30. All departments with employees, including empty departments (LEFT JOIN).
┌───────────────┬────────────┬───────────┐
│   dept_name   │ first_name │ last_name │
├───────────────┼────────────┼───────────┤
│ Customer Care │            │           │
│ Finance       │ Charlie    │ Brown     │
│ Finance       │ Fiona      │ Hall      │
│ HR            │ Alice      │ Johnson   │
│ HR            │ Hannah     │ White     │
│ IT            │ Bob        │ Smith     │
│ IT            │ Diana      │ Prince    │
│ IT            │ Greg       │ Miles     │
│ Legal         │ Julia      │ Adams     │
│ Marketing     │            │           │
│ Operations    │ Ian        │ Scott     │
│ Procurement   │            │           │
│ R&D           │            │           │
│ Sales         │            │           │
└───────────────┴────────────┴───────────┘

Q31. Employees and the projects they work on (three-table join).
┌────────────┬───────────┬───────────────────┬─────────────────┐
│ first_name │ last_name │   project_name    │ hours_allocated │
├────────────┼───────────┼───────────────────┼─────────────────┤
│ Alice      │ Johnson   │ Recruitment Drive │ 50              │
│ Bob        │ Smith     │ Website Upgrade   │ 120             │
│ Charlie    │ Brown     │ Payroll System    │ 100             │
│ Diana      │ Prince    │ Website Upgrade   │ 80              │
│ Fiona      │ Hall      │ Payroll System    │ 150             │
│ Greg       │ Miles     │ Website Upgrade   │ 60              │
│ Hannah     │ White     │ Recruitment Drive │ 40              │
│ Ian        │ Scott     │ Inventory System  │ 90              │
│ Julia      │ Adams     │ Compliance Portal │ 110             │
└────────────┴───────────┴───────────────────┴─────────────────┘

Q32. Projects with the total hours allocated.
┌───────────────────┬─────────────┐
│   project_name    │ total_hours │
├───────────────────┼─────────────┤
│ Website Upgrade   │ 260         │
│ Payroll System    │ 250         │
│ Compliance Portal │ 110         │
│ Recruitment Drive │ 90          │
│ Inventory System  │ 90          │
└───────────────────┴─────────────┘

Q33. Employees working on more than one project.

Q34. All projects handled by the Finance department.
┌────────────────┬────────────┐
│  project_name  │ start_date │
├────────────────┼────────────┤
│ Payroll System │ 2023-01-01 │
└────────────────┴────────────┘

BONUS: SELF JOIN -- each employee alongside their manager. The syllabus
┌───────────────┬───────────────┐
│   employee    │    manager    │
├───────────────┼───────────────┤
│ Alice Johnson │ No manager    │
│ Bob Smith     │ Diana Prince  │
│ Charlie Brown │ Fiona Hall    │
│ Diana Prince  │ No manager    │
│ Fiona Hall    │ No manager    │
│ Greg Miles    │ Diana Prince  │
│ Hannah White  │ Alice Johnson │
│ Ian Scott     │ No manager    │
│ Julia Adams   │ No manager    │
└───────────────┴───────────────┘

Constraint tests: each statement below must be rejected (tools/data-science/run_sql_labs.py runs the same ones).

-- CHECK salary > 0
INSERT INTO Employees (emp_id, first_name, last_name, email, phone, hire_date, job_title, salary, dept_id, manager_id) VALUES (111, 'Test', 'User', 't@corp.com', '111-222-3333', '2024-01-01', 'Tester', -5000, 1, NULL);
Error: stepping, CHECK constraint failed: salary > 0 (19)

-- FOREIGN KEY dept_id
UPDATE Employees SET dept_id = 99 WHERE emp_id = 102;
Error: stepping, FOREIGN KEY constraint failed (19)

-- CHECK phone format
INSERT INTO Employees (emp_id, first_name, last_name, email, phone, hire_date, job_title, salary, dept_id, manager_id) VALUES (112, 'Bad', 'Phone', 'b@corp.com', 'not-a-phone', '2024-01-01', 'Tester', 50000, 1, NULL);
Error: stepping, CHECK constraint failed: phone LIKE '___-___-____' (19)

-- UNIQUE dept_name
INSERT INTO Departments (dept_id, dept_name, location) VALUES (11, 'HR', 'Boston');
Error: stepping, UNIQUE constraint failed: Departments.dept_name (19)

Section D is the joins section, and the self join (each employee with their manager) is the one most likely to appear in a viva. It is the last query, labelled BONUS.

Questions 5 and 9 are among the constraint tests at the end: the negative salary breaks the CHECK, and department 99 does not exist, so the foreign key refuses it.

Three questions print only their label, and rightly. Question 27: the sample employees were hired between 2017 and 2023, so none in the last 90 days. Question 33: in the sample data each employee works on one project. Question 28's years of experience are counted to 4 October 2026.

Diana Prince's salary shows as 99000.0 after question 8's 10% raise, not 99000. In binary floating point 90000 × 1.10 is 99000.00000000001, so SQLite keeps it as a real number. Oracle's NUMBER is decimal and stores exactly 99000.

Experiment 3, Section E — PL/SQL

1. Question

On the Employee database, write PL/SQL procedures, a cursor and triggers: the six questions of section E, question 2 reconstructed.

2. Aim

Write stored procedures, a cursor, row-level and statement-level triggers and a function in Oracle PL/SQL.

3. Steps

1. A procedure that displays an employee's details. Question 1.
2. A procedure that prints the salary band. Question 2, reconstructed.
3. A cursor over the top 10 rows. Question 3.
4. A procedure that adds a bonus. Question 4.
5. A row-level trigger for the minimum salary. Question 5.
6. A statement-level trigger that blocks weekend changes. Question 6.
7. A function, for comparison. Not set, but Unit 5 lists functions beside procedures.

4. Programme

-- =====================================================================
-- Course 5 (DBMS) Lab -- Experiment 3, Section E: PL/SQL Programming
-- Syllabus source: page 37
--
-- *** NOT EXECUTED IN VERIFICATION ***
-- PL/SQL is Oracle-specific. No Oracle instance is available in the
-- environment where these labs were checked, and SQLite cannot run PL/SQL, so
-- tools/run_sql_labs.py deliberately skips this file. Everything below is
-- written to Oracle syntax and desk-checked by hand -- run it on your college's
-- Oracle installation (SQL*Plus or SQL Developer) before relying on it.
--
-- Two notes before you start:
--   * Triggers are NOT listed in syllabus Unit 5, but questions 5 and 6 below
--     require them, and so do the course objective and the activities. See
--     SYLLABUS-REVIEW.md finding D2.
--   * Question 2 is missing from the official list -- only its tail survives,
--     "If yes, print 'High Salary'; Otherwise print 'Standard Salary'". It is
--     reconstructed below. See finding D4.
--
-- Run SET SERVEROUTPUT ON first, or DBMS_OUTPUT.PUT_LINE prints nothing --
-- the single most common reason a PL/SQL block "does not work" in a lab exam.
-- =====================================================================

SET SERVEROUTPUT ON;

-- ---------------------------------------------------------------------
-- Q1. Procedure GetEmpInfo: take emp_id as input, display name, salary and
--     department.
-- Step 1: A procedure that displays an employee's details
-- ---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE GetEmpInfo (p_emp_id IN NUMBER)
IS
    v_first_name Employees.first_name%TYPE;   -- %TYPE inherits the column type,
    v_last_name  Employees.last_name%TYPE;    -- so the code survives a schema
    v_salary     Employees.salary%TYPE;       -- change
    v_dept_name  Departments.dept_name%TYPE;
BEGIN
    SELECT e.first_name, e.last_name, e.salary, d.dept_name
      INTO v_first_name, v_last_name, v_salary, v_dept_name
      FROM Employees e
      JOIN Departments d ON e.dept_id = d.dept_id
     WHERE e.emp_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('Employee  : ' || v_first_name || ' ' || v_last_name);
    DBMS_OUTPUT.PUT_LINE('Salary    : ' || TO_CHAR(v_salary, '999,999.99'));
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);

EXCEPTION
    -- SELECT INTO raises NO_DATA_FOUND when it matches nothing, and
    -- TOO_MANY_ROWS when it matches more than one. Handle both.
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with id ' || p_emp_id);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one employee matched id ' || p_emp_id);
END;
/

-- Call it:
--   EXEC GetEmpInfo(101);

-- ---------------------------------------------------------------------
-- Q2. [RECONSTRUCTED -- the official question text is missing; only its tail
--     survives as "If yes, print 'High Salary'; Otherwise print 'Standard
--     Salary'".]
--     Reconstructed as: check whether a given employee's salary exceeds
--     60,000 and print the appropriate message.
-- Step 2: A procedure that prints the salary band
-- ---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE CheckSalaryBand (p_emp_id IN NUMBER)
IS
    v_salary Employees.salary%TYPE;
    v_name   VARCHAR2(101);
    c_threshold CONSTANT NUMBER := 60000;
BEGIN
    SELECT salary, first_name || ' ' || last_name
      INTO v_salary, v_name
      FROM Employees
     WHERE emp_id = p_emp_id;

    IF v_salary > c_threshold THEN
        DBMS_OUTPUT.PUT_LINE(v_name || ': High Salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_name || ': Standard Salary');
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with id ' || p_emp_id);
END;
/

-- ---------------------------------------------------------------------
-- Q3. Display the top 10 rows of the Emp table by job and salary.
--     Uses an explicit cursor -- the syllabus lists iterative control, and
--     cursor FOR loops are the standard way to walk a result set.
-- Step 3: A cursor over the top 10 rows
-- ---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE TopTenEmployees
IS
    CURSOR c_top IS
        SELECT emp_id, first_name, last_name, job_title, salary
          FROM Employees
         ORDER BY job_title ASC, salary DESC
         FETCH FIRST 10 ROWS ONLY;      -- Oracle 12c and later
        -- Oracle 11g and earlier:
        --   SELECT * FROM (SELECT ... ORDER BY job_title, salary DESC)
        --    WHERE ROWNUM <= 10;
    v_count NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD('ID', 8) || RPAD('Name', 25) ||
                         RPAD('Job Title', 22) || 'Salary');
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 65, '-'));

    -- A cursor FOR loop opens, fetches and closes the cursor for you.
    FOR rec IN c_top LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.emp_id, 8) ||
            RPAD(rec.first_name || ' ' || rec.last_name, 25) ||
            RPAD(rec.job_title, 22) ||
            TO_CHAR(rec.salary, '999,999.99'));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Rows displayed: ' || v_count);
END;
/

-- ---------------------------------------------------------------------
-- Q4. Stored procedure GiveBonus: take a department id, a designation and a
--     bonus amount, and add the bonus to the salary of every employee in that
--     department holding that designation.
-- Step 4: A procedure that adds a bonus
-- ---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE GiveBonus (
    p_dept_id     IN NUMBER,
    p_designation IN VARCHAR2,
    p_bonus       IN NUMBER)
IS
    v_rows_updated NUMBER;
BEGIN
    IF p_bonus <= 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Bonus must be greater than zero');
    END IF;

    UPDATE Employees
       SET salary = salary + p_bonus
     WHERE dept_id = p_dept_id
       AND UPPER(job_title) = UPPER(p_designation);

    v_rows_updated := SQL%ROWCOUNT;   -- implicit cursor attribute

    IF v_rows_updated = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No employee matched department ' || p_dept_id ||
                             ' with designation ' || p_designation);
        ROLLBACK;
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_rows_updated || ' employee(s) received a bonus of '
                             || p_bonus);
        COMMIT;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

-- Call it:
--   EXEC GiveBonus(2, 'IT Analyst', 5000);

-- ---------------------------------------------------------------------
-- Q5. Trigger: prevent inserting an employee with a salary below 30,000.
--
--     NOTE: triggers are not in syllabus Unit 5 -- see review finding D2.
--     BEFORE INSERT so the row is rejected before it is ever written.
--     FOR EACH ROW makes it a row-level trigger, giving access to :NEW.
-- Step 5: A row-level trigger for the minimum salary
-- ---------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_min_salary
BEFORE INSERT OR UPDATE OF salary ON Employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 30000 THEN
        RAISE_APPLICATION_ERROR(
            -20002,
            'Salary cannot be less than 30,000. Attempted: ' || :NEW.salary);
    END IF;
END;
/

-- Test:
--   INSERT INTO Employees VALUES (120, 'Low', 'Paid', 'low@corp.com',
--       '111-222-3333', SYSDATE, 'Intern', 25000, 1, NULL);
--   -- expect ORA-20002

-- ---------------------------------------------------------------------
-- Q6. Trigger: block any insert, update or delete on the employee table at
--     the weekend.
--
--     This is a STATEMENT-level trigger (no FOR EACH ROW) -- the restriction
--     is about when the statement runs, not about any particular row.
--
--     TO_CHAR(SYSDATE,'DY') returns an abbreviated day name in the session's
--     language. Comparing against 'SAT'/'SUN' therefore breaks under a
--     different NLS_DATE_LANGUAGE; passing the language explicitly is safer.
-- Step 6: A statement-level trigger that blocks weekend changes
-- ---------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_no_weekend_changes
BEFORE INSERT OR UPDATE OR DELETE ON Employees
DECLARE
    v_day VARCHAR2(3);
BEGIN
    v_day := TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');

    IF v_day IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(
            -20003,
            'Changes to the Employees table are not allowed at the weekend ('
            || v_day || ')');
    END IF;
END;
/

-- ---------------------------------------------------------------------
-- BONUS: a FUNCTION, since Unit 5 lists functions alongside procedures and
-- the difference is a standard viva question.
--
--   Procedure: performs an action; called as a statement (EXEC p;)
--   Function : returns a value; called inside an expression (SELECT f() ...)
-- Step 7: A function, for comparison
-- ---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_annual_salary (p_emp_id IN NUMBER)
RETURN NUMBER
IS
    v_salary Employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM Employees WHERE emp_id = p_emp_id;
    RETURN v_salary * 12;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;
/

-- Because it is a function, it can be used inside a query:
--   SELECT first_name, get_annual_salary(emp_id) AS annual FROM Employees;

5. Execution and Results

The distinction between questions 5 and 6 is the point. Question 5 is about individual rows, so it needs FOR EACH ROW and :NEW.salary. Question 6 is about when the statement runs, so it is statement-level and has no :NEW at all. Be ready to explain why in the viva.

Lab exam tips

1. SET SERVEROUTPUT ON before any PL/SQL. Without it DBMS_OUTPUT produces nothing and the code looks broken when it is not. This is the most common lab-exam failure.

2. End PL/SQL blocks with / on its own line.

3. Create the tables and insert the sample data first, then test each query. A query cannot be marked if the schema does not exist.

4. Test constraints deliberately. Try the negative salary and show that it is rejected — examiners give marks for demonstrating that a constraint works.

5. Format your output. Use column aliases (AS headcount) and ORDER BY. A readable result reads as a correct one.

6. Comment each query with the question number it answers.

7. Watch the dialect. Confirm whether your lab uses Oracle, MySQL or PostgreSQL before writing date functions or row limits.

8. Expect a viva. "Why a LEFT JOIN here?", "what happens if I drop this constraint?", "why is this trigger BEFORE and not AFTER?"