Views.sql

CREATE OR REPLACE VIEW ACTIVE_DELIVERIES AS
SELECT
D.DELIVERY_ID,
D.AGENT_ID,
DA.AGENT_NAME,
D.ORDER_NO,
O.AMOUNT,
D.STATUS,
D.DEPARTURE_DATE,
D.ESTIMATED_TIME_MINUTES
FROM
DELIVERY D
JOIN
DELIVERY_AGENT DA ON D.AGENT_ID = DA.AGENT_ID
JOIN
ORDERS O ON D.ORDER_NO = O.ORDER_ID
WHERE
D.STATUS = 'In Progress';


CREATE OR REPLACE VIEW AS
SELECT
C.CUSTOMER_ID,
C.CUSTOMER_NAME,
C.ADDRESS,
O.ORDER_ID,
O.ORDER_STATUS,
O.AMOUNT
FROM
CUSTOMER C
JOIN
ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;



CREATE OR REPLACE VIEW AS
SELECT
R.RESTAURANT_ID,
R.R_NAME,
SUM(RE.TOTALSALE) AS TOTAL_REVENUE
FROM
RESTAURANTS R
JOIN
REVENUE RE ON R.RESTAURANT_ID = RE.R_ID
GROUP BY
R.RESTAURANT_ID, R.R_NAME;



CREATE OR REPLACE VIEW AS
SELECT
O.ORDER_ID,
O.CUSTOMER_ID,
I.ITEM_NO,
I.ITEM_NAME,
OI.QUANTITY,
OI.PRICE
FROM
ORDERS O
JOIN
ORDERITEMS OI ON O.ORDER_ID = OI.O_NO
JOIN
ITEMS I ON OI.ITEM_NO = I.ITEM_NO;



CREATE OR REPLACE VIEW AS
SELECT
I.ITEM_NO,
I.ITEM_NAME,
R.RESTAURANT_ID,
R.R_NAME AS RESTAURANT_NAME,
RI.PRICE,
RI.AVAILIBILITY_STATUS,
RI.LASTUPDATED
FROM
ITEMS I
JOIN
RESTAURANT_ITEMS RI ON I.ITEM_NO = RI.ITEM_NUMBER
JOIN
RESTAURANTS R ON RI.RESTAURANT_ID = R.RESTAURANT_ID



CREATE OR REPLACE VIEW TOP_RATED_RESTAURANTS AS
SELECT
R.RESTAURANT_ID,
R.R_NAME AS RESTAURANT_NAME,
AVG(RE.RATING) AS AVERAGE_RATING
FROM
RESTAURANTS R
JOIN
ORDERS O ON R.RESTAURANT_ID = O.CUSTOMER_ID
JOIN
REVIEW RE ON O.ORDER_ID = RE.ORDER_ID
GROUP BY
R.RESTAURANT_ID, R.R_NAME
HAVING
AVG(RE.RATING) >= 4


CREATE OR REPLACE VIEW DISCOUNTED_ORDERS AS
SELECT
O.ORDER_ID,
C.CUSTOMER_NAME,
R.DISCOUNTCODE,
R.PAYABLE_AMOUNT,
R.TAX
FROM
ORDERS O
JOIN
CUSTOMER C ON O.CUSTOMER_ID = C.CUSTOMER_ID
JOIN
REGULATIONS R ON O.ORDER_ID = R.O_NO
WHERE
R.DISCOUNTCODE IS NOT NULL;


CREATE OR REPLACE VIEW AS
SELECT
R.RESTAURANT_ID,
R.R_NAME AS RESTAURANT_NAME,
COUNT(O.ORDER_ID) AS TOTAL_ORDERS
FROM
RESTAURANTS R
JOIN
ITEMS I ON R.RESTAURANT_ID = I.RESTAURANT_ID
JOIN
ORDERITEMS OI ON I.ITEM_NO = OI.ITEM_NO
JOIN
ORDERS O ON OI.O_NO = O.ORDER_ID
GROUP BY
R.RESTAURANT_ID, R.R_NAME;


CREATE OR REPLACE VIEW ACTIVE_PROMOTIONS AS
SELECT
P.promo_id,
R.R_NAME AS RESTAURANT_NAME,
PD.promo_name,
P.promo_code,
PD.activationDate,
PD.lastDate
FROM
PROMOTIONS P
JOIN
RESTAURANTS R ON P.r_id = R.RESTAURANT_ID
JOIN
PROMOTIONS_DETAILS PD ON P.promo_code = PD.promo_code
WHERE
PD.lastDate >= SYSDATE;


CREATE OR REPLACE VIEW AGENT_PERFORMANCE AS
SELECT
DA.AGENT_ID,
DA.AGENT_NAME,
COUNT(D.DELIVERY_ID) AS TOTAL_DELIVERIES
FROM
DELIVERY_AGENT DA
LEFT JOIN
DELIVERY D ON DA.AGENT_ID = D.AGENT_ID
WHERE
D.STATUS = 'Delivered'
GROUP BY
DA.AGENT_ID, DA.AGENT_NAME