GRADE 12 : PRACTICAL 17

Create the tables given below, and perform the given queries 

ORDERS

ITEM

CREATE TABLE Item (

    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(50),
    Category VARCHAR(30),
    Price DECIMAL(10,2),
    StockQty INT
);

INSERT INTO Item (ItemID, ItemName, Category, Price, StockQty) VALUES

(101, 'Pen', 'Stationery', 10.00, 150),

(102, 'Notebook', 'Stationery', 50.00, 100),

(103, 'Water Bottle', 'Accessories', 120.00, 75),

(104, 'Pencil Box', 'Stationery', 80.00, 60),

(105, 'Backpack', 'Bags', 500.00, 30);

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    OrderDate DATE,

    CustomerName VARCHAR(50),

    ItemID INT,

    Quantity INT,

    FOREIGN KEY (ItemID) REFERENCES Item(ItemID)

);

INSERT INTO Orders (OrderID, OrderDate, CustomerName, ItemID, Quantity) VALUES

(201, '2025-07-15', 'Riya Sharma', 101, 3),

(202, '2025-07-16', 'Aarav Mehta', 105, 1),

(203, '2025-07-17', 'Neha Sinha', 102, 2),

(204, '2025-07-17', 'Aditya Rao', 103, 1),

(205, '2025-07-18', 'Isha Verma', 104, 2);

1. Show each customer's total bill amount

2. Display items where Total quantity ordered per item

3. Show the order id, orderdate , customer name and item name of the items ordered on 17-07-2025

4. List all orders with item names in descending order of quantity

5. Decrease the stock for pen by 5

6. Show categories and number of items in that category sorted by average price descending 

7. Show all items with second letter as 'a'

 

8. Find the item with the maximum price

9. Show the most recently placed order

10. Delete order entries where pencil box is bought.