Question

7. Using the provided schema of a Purchase Order Administration database, write the following queries in SQL.

(In the schema, bold attributes are primary keys and italicized attributes are foreign keys.)

SUPPLIER (SUPNR, SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS)

SUPPLIES (SUPNR, PRODNR, PURCHASE_PRICE, DELIV_PERIOD)

PRODUCT (PRODNR, PRODNAME, PRODTYPE, AVAILABLE_QUANTITY)

PO_LINE (PONR, PRODNR, QUANTITY)

PURCHASE_ORDER (PONR, PODATE, SUPNR)

7d) Write a nested SQL query to retrieve the supplier number, supplier name, and supplier status of each supplier who has a higher supplier status than supplier number 21.

7e) Write a nested SQL query using the keyword IN to retrieve the supplier name of each supplier who supplies more than five products.

7f) Write an SQL query that retrieves all pairs of suppliers who supply the same product, along with their product purchase price if applicable.

7g) Create a view, SUPPLIEROVERVIEW, which retrieves for each supplier the supplier number, the supplier name, and the total quantities ordered. Once created, query this view to retrieve suppliers for which the total ordered quantity exceeds 30.

Answer 1

Answer of

7d) Write a nested SQL query to retrieve the supplier number, supplier name, and supplier status of each supplier who has a higher supplier status than supplier number 21.

query - SELECT S1.SUPNR, S1.SUPNAME, S1.SUPSTATUS FROM SUPPLIER S1, SUPPLIER S2 WHERE S1.SUPSTATUS  > S2.SUPSTATUS  AND S2.SUPNR = 21;

Answer of

7e) Write a nested SQL query using the keyword IN to retrieve the supplier name of each supplier who supplies more than five products.

query - SELECT SUPNAME FROM SUPPLIER WHERE SUPNR IN (SELECT SUPNR FROM PURCHASE_ORDER WHERE COUNT(PONR) > 5 GROUP BY SUPNR )

Answer of

7f) Write an SQL query that retrieves all pairs of suppliers who supply the same product, along with their product purchase price if applicable.

query -

SELECT P.PRODNR, P.PRODNAME, S1.SUPNR, S1.SUPNAME, S.PURCHASE_PRICE

FROM PRODUCT P, SUPPLIES S, SUPPLIER S1

WHERE S.PRODNR IN (SELECT PRODNR FROM SUPPLIES WHERE COUNT(SUPNR) > 1 GROUP BY PRODNR) AND S1.SUPNR = S.SUPNR AND P.PRODNR = S.PRODNR;

Answer of

7g) Create a view, SUPPLIEROVERVIEW, which retrieves for each supplier the supplier number, the supplier name, and the total quantities ordered. Once created, query this view to retrieve suppliers for which the total ordered quantity exceeds 30.

query -

First create view

CREATE VIEW [SUPPLIEROVERVIEW] AS

SELECT S1.SUPNR, S1.SUPNAME, SUM(PO.QUANTITY) AS TOTQTY

FROM SUPPLIER S1, PO_LINE PO, PURCHASE_ORDER PR

WHERE PR.SUPNR = S1.SUPNR AND PR.PONR= PO.PONR GROUP BY PO.PONR;

query this view to retrieve suppliers for which the total ordered quantity exceeds 30.

SELECT * FROM SUPPLIEROVERVIEW WHERE TOTQTY > 30;