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

d)

select SUPNR, SUPNAME, SUPSTATUS from SUPPLIER WHERE SUPSTATUS > (SELECT SUPSTATUS FROM SUPPLIER WHERE SUPNR = 21);

e)

SELECT SUPNAME FROM SUPPLIER WHERE SUPNR IN (SELECT SUPNR FROM SUPPLIES GROUP BY SUPNR HAVING COUNT(DISTINCT PRODNR) > 5);

NOTE: As per HOMEWORKLIB POLICY I am allowed to answer specific number of questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.