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.

Answer 1

Data Setup :

     SUPNR SUPNAME    SUPADDRESS SUPCITY     SUPSTATUS
---------- ---------- ---------- ---------- ----------
         1 Van        22A        CCU                25
         2 Ram        13B        IXB                12
         3 Jon        D-22       SFO                21
         4 Kan        J5         LND                23
         5 Ras        K-12       PRS                18
         6 Dev        H-55       SYD                16
        21 Res        M-72       BRU                19
         8 Sim        22C        DEL                26
         1 Mar        3D         KAB                13
        10 Hel        H15        BJG                 9

10 rows selected.

SQL> select * from supplies;

     SUPNR     PRODNR PURCHASE_PRICE DELIV_PERIOD
---------- ---------- -------------- ------------
         2         22             25            2
         2        221            110            2
         2        222            110            2
         2        224            230            2
         2        225            290            2
         2        225           2505            2
         5        223           1102            2
         4        229            320            2
         4        224             55            2
         3        223            505            2
         4        229            502            2
         6        224            201            2

12 rows selected.

=============================================================

7d )

SQL> Select SUPNR, SUPNAME, SUPSTATUS from SUPPLIER where SUPSTATUS > (select SUPSTATUS from SUPPLIER where SUPNR = 21);

     SUPNR SUPNAME     SUPSTATUS
---------- ---------- ----------
         1 Van                25
         3 Jon                21
         4 Kan                23
         8 Sim                26

SQL>

========================================================================

Answers :

7e)

SQL> select SUPNAME from SUPPLIER where SUPNR in ( select SUPNR from SUPPLIES group by SUPNR having count(PRODNR) > 5);

SUPNAME
----------
Ram