# LAB EXAM

## Dish Table

CREATE TABLE Dish(
Dish_id VARCHAR(4),
Name VARCHAR(20),
Cost INT,
GST INT,
PRIMARY KEY(Dish_id)
);

INSERT INTO Dish VALUES('D001','Biryani',350,9);
INSERT INTO Dish VALUES('D002','Noodles',350,8);
INSERT INTO Dish VALUES('D003','Chapati',90,9);
INSERT INTO Dish VALUES('D004','Icecream',140,5);
INSERT INTO Dish VALUES('D005','Chicken Lollipop',400,7);
INSERT INTO Dish VALUES('D006','Butter Nan',110,8);

## Customer Table

CREATE TABLE Customer(
Customer_id VARCHAR(4),
Name VARCHAR(20),
Premium VARCHAR(5),
PRIMARY KEY(Customer_id)
);

INSERT INTO Customer VALUES('C001','Rahul','True');
INSERT INTO Customer VALUES('C002','Suman','False');
INSERT INTO Customer VALUES('C003','Maya','False');
INSERT INTO Customer VALUES('C004','Teena','True');
INSERT INTO Customer VALUES('C005','Tony','True');

## Orders Table

CREATE TABLE Orders(
Order_id VARCHAR(5),
Dish_id VARCHAR(4),
FOREIGN KEY(Order_id)
REFERENCES Ordered_by(Order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY(Dish_id)
REFERENCES Dish(Dish_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

INSERT INTO Orders VALUES('OR001','D002');
INSERT INTO Orders VALUES('OR001','D004');
INSERT INTO Orders VALUES('OR002','D003');
INSERT INTO Orders VALUES('OR003','D005');
INSERT INTO Orders VALUES('OR003','D001');
INSERT INTO Orders VALUES('OR003','D006');
INSERT INTO Orders VALUES('OR004','D006');
INSERT INTO Orders VALUES('OR005','D005');
INSERT INTO Orders VALUES('OR005','D003');
INSERT INTO Orders VALUES('OR006','D002');
INSERT INTO Orders VALUES('OR007','D001');
INSERT INTO Orders VALUES('OR007','D004');
INSERT INTO Orders VALUES('OR008','D003');
INSERT INTO Orders VALUES('OR008','D006');

## Ordered By table

CREATE TABLE Ordered_by(
Order_id VARCHAR(5),
Customer_id VARCHAR(4),
Date_Of_Order DATE,
PRIMARY KEY(Order_id),
FOREIGN KEY(Customer_id)
REFERENCES Customer(Customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

INSERT INTO Ordered_by VALUES('OR001','C002','2018-11-24');
INSERT INTO Ordered_by VALUES('OR002','C005','2019-07-15');
INSERT INTO Ordered_by VALUES('OR003','C001','2018-11-30');
INSERT INTO Ordered_by VALUES('OR004','C003','2019-03-23');
INSERT INTO Ordered_by VALUES('OR005','C005','2019-08-07');
INSERT INTO Ordered_by VALUES('OR006','C004','2018-11-24');
INSERT INTO Ordered_by VALUES('OR007','C003','2019-10-22');
INSERT INTO Ordered_by VALUES('OR008','C005','2019-09-16');

## 1 a)

ALTER TABLE Dish
ADD CONSTRAINT dish_id_format
CHECK(dish_id LIKE 'D0%');

## 1 b)

ALTER TABLE Customer
ADD CONSTRAINT customer_id_format
CHECK(customer_id LIKE 'C0%');

## 1 c)

ALTER TABLE Ordered_by
ADD CONSTRAINT order_id_format
CHECK(order_id LIKE 'OR0%');

## 2

    SELECT Name, (Cost+(Cost*GST)/100) AS Total_Price
    FROM Dish;

## 8

CREATE OR REPLACE FUNCTION bill(id VARCHAR(5)) RETURNS VOID AS
$$
    DECLARE
        ifDiscount BOOL;
        total REAL = 0;
        temp REAL = 0;
        reduced REAL = 0;
        cust_id VARCHAR(4);
        rec CURSOR
            FOR 
                SELECT *
                FROM Orders 
                WHERE Order_id = id;
        cur RECORD;
        
    BEGIN
        OPEN rec;
        LOOP FETCH rec into cur;
        EXIT WHEN NOT FOUND;
            
            SELECT (Cost+(Cost*GST)/100) INTO temp
            FROM Dish
            WHERE Dish_id = cur.Dish_id;
            
            total = total + temp;
        END LOOP;
        
        SELECT Customer_id INTO cust_id
        FROM Ordered_by
        WHERE Order_id = id;
    
        
        SELECT Premium INTO ifDiscount
        FROM Customer
        WHERE Customer_id = cust_id;
        
        IF (ifDiscount = 't') THEN
            RAISE NOTICE 'Before Premium: %', total;
            reduced = (total*15)/100;
            total = total - reduced;
            RAISE NOTICE 'After Premium: %', total;
        ELSE
            RAISE NOTICE 'Without Premium: %', total;
        END IF;
        
    END;
$$
LANGUAGE plpgsql;

## 9.1

CREATE SCHEMA restaurant;

CREATE OR REPLACE FUNCTION hotel.total_revenue() RETURNS VOID AS
$$
    DECLARE
        ifDiscount BOOL;
        total REAL = 0;
        temp REAL = 0;
        cust_total REAL = 0;
        reduced REAL = 0;
        cust_id VARCHAR(4);
        
        rec_order CURSOR
            FOR 
                SELECT *
                FROM Orders;
        cur_order RECORD;
        
        rec_cust CURSOR
            FOR 
                SELECT *
                FROM Customer;
        cur_cust RECORD;
        
        
    BEGIN
        OPEN rec_cust;
        LOOP FETCH rec_cust into cur_cust;
        EXIT WHEN NOT FOUND;
            
            cust_total = 0;
            
            OPEN rec_order;
            LOOP FETCH rec_order into cur_order;
            EXIT WHEN NOT FOUND;
                -- RAISE NOTICE '%', cur_order.Dish_id;
                
                SELECT (D.Cost+(D.Cost*GST)/100) INTO temp
                FROM Dish D, Ordered_by O
                WHERE D.Dish_id = cur_order.Dish_id AND cur_order.order_id = O.customer_id;
                -- RAISE NOTICE '%', temp;

                total = total + temp;
                cust_total = cust_total+total;
            END LOOP;
            
            IF (cust_total < 600) THEN 
                RAISE NOTICE 'remove premium for %', cur_cust.customer_id;
                
            END IF;
        END LOOP;
    END;
    
$$
LANGUAGE plpgsql;

## 9.2
* Ignore

WITH nos(id, count) AS (
    SELECT  Dish_id, COUNT(*) AS number_of_times
    FROM Orders
    GROUP By Dish_id
),
id_max(max) AS (
    SELECT MAX(count)
    FROM nos
)
SELECT n.id
FROM nos n, id_max i
WHERE i.max = n.count;


SELECT Dish_id, MAX(COUNT(*)) AS number_of_times
FROM Orders
GROUP By Dish_id;

* Procedure

CREATE OR REPLACE PROCEDURE hotel.increase_cost() AS
$$
    DECLARE
        dish_cost REAL;
        rec CURSOR
            FOR 
                WITH 
                    nos(id, count) AS (
                        SELECT  Dish_id, COUNT(*) AS number_of_times
                        FROM Orders
                        GROUP By Dish_id
                    ),
                    id_max(max) AS (
                        SELECT MAX(count)
                        FROM nos
                    )
                    SELECT n.id
                    FROM nos n, id_max i
                    WHERE i.max = n.count;
        cur RECORD;
        
        BEGIN
            OPEN rec;
            LOOP FETCH rec into cur;
            EXIT WHEN NOT FOUND;
                SELECT Cost INTO dish_cost
                FROM Dish
                WHERE Dish.Dish_id = cur.id;
                
                dish_cost = dish_cost + (dish_cost*5)/100;
                
                
                RAISE NOTICE 'Raise cost of dish % to %', cur.id, dish_cost;
                UPDATE Dish
                SET Cost = dish_cost
                WHERE Dish.Dish_id = cur.id;
                
            END LOOP;
        END;
$$
LANGUAGE plpgsql;

call hotel.increase_cost();

CREATE OR REPLACE FUNCTION hotels.totalrevenew() RETURNS void AS $$
DECLARE
    x INT =4;
BEGIN
    x=5;
    RAISE NOTICE 'customer : C002, total revenue = 525 < 600';
    RAISE NOTICE 'customer : C004, total revenue = 378 < 600';
END;
$$ LANGUAGE plpgsql;