GRADE 12 : PRACTICAL 17

Create the tables given below, and perform the given queries 

ORDERS

ITEM

CREATE LAB17;

USE LAB17;

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 ,

    OrderDate DATE,

    CustomerName VARCHAR(50),

    ItemID INT,

    Quantity INT,

    PRIMARY KEY(OrderID,ItemID),

    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-17', 'Neha Sinha', 102, 2),

(202, '2025-07-17', 'Neha Sinha', 103, 1),

(203, '2025-07-18', 'Isha Verma', 104, 1),

(204, '2025-07-17', 'Kanye West', 103, 1);

1. Show each customer's total bill amount

SELECT OrderID,CustomerName,sum(Quantity*Price) as Total

FROM Orders NATURAL JOIN Item GROUP BY OrderId,CustomerName;

2. Display items where Total quantity ordered per item(across orders) is more than 1

SELECT itemid,itemname,sum(quantity) FROM item NATURAL JOIN orders

GROUP BY itemid,itemname having sum(quantity)>1;

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

SELECT orderid,orderdate,customername,itemname FROM orders NATURAL JOIN item

WHERE orderdate='2025-07-17';

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

SELECT orderid,itemname,quantity FROM orders NATURAL JOIN item

ORDER BY quantity DESC;

5. Decrease the stock for pen by 5

 UPDATE item SET StockQty=StockQty-5 WHERE

 Itemname='Pen';

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

SELECT Category, COUNT(*) as No_of_Items, Avg(Price)

FROM item GROUP BY Category ORDER BY Avg(Price) DESC;

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

 SELECT * FROM item WHERE ItemName LIKE '_a%';

 

8. Find the item with the maximum price

SELECT itemName, price FROM item WHERE price =

(SELECT max(price) FROM item);

9. Show the most recently placed order

SELECT * FROM orders WHERE orderdate= (SELECT max(orderdate)

FROM orders);

10. Delete order entries where pencil box is bought.

DELETE FROM orders WHERE ItemId=(SELECT ItemID FROM item WHERE

itemname='pencil box');