Quiz #3 - SQL

Problem #1: Assuming the donor table shown, what output would be produced from the following code? Explain the results.

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       500 John Smith
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       400 Susan Jones
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98           Susan Jones
23456 Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92       100 Amy Costa
33333 Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92        50 John Adams
34567 Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98        50 Amy Costa

SQL> run
  1  SELECT name, stadr, city, DECODE(state,'MA', yrgoal*2,
  2                                         'RI', yrgoal*3,
  3                                               yrgoal*4) altered_goal, yrgoal
  4* from donor


Problem #2: Use the donor table shown and tell me the output that would be produced. Explain the results.

SQL> select state, count(contact), sum(yrgoal)
  2  from donor
  3  group by state;


Problem #3: Use the inventory table shown and tell me the output that would be produced. Explain the results.

SQL> select * from inven;

ITEM ITEMNAME           ONHAND   ONORDER   REORDPT      COST     PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon        24        30        40         8     12.99 BK BY X100
1212 Heidi                  12        25        25        10     14.99 BK CH X112
1234 Adven Reddy Fox         5         0        10         9     14.75 BK CH X100
2121 Teddy Bear              5        20        40        15     19.95 TY CH X115
2222 Building Blocks         4         0        15        48     51.99 TY CH Z200
2345 Doll House              2         5        10        45     55.98 TY CH Z212
3333 Basketball             24        25        50        14     17.99 SP BK Y200
3456 Net/Hoop               12         0        25        25     27.95 SP BK Y200

8 rows selected.

SQL> describe inven;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ITEMNO                                   VARCHAR2(4)
 ITEMNAME                                 VARCHAR2(15)
 ONHAND                                   NUMBER(5)
 ONORDER                                  NUMBER(5)
 REORDPT                                  NUMBER(5)
 COST                                     NUMBER(6,2)
 PRICE                                    NUMBER(6,2)
 DEPT                                     CHAR(2)
 ITEMCLASS                                CHAR(2)
 LOCATION                                 VARCHAR2(4)

SQL> select itemclass, sum(price), avg(price)
  2  from inven
  3  where onorder > 0
  4  group by itemclass;


Problem #4: use the inven table above and tell me the output that would be produced. Explain the results.

SQL> select dept, sum(cost)
  2  from inven
  3  group by dept
  4  having sum(cost) > 30;


Problem #5: Use the inven table above and tell me the output that would be produced. Explain the results.

SQL> select dept, sum(onhand), sum(onorder)
  2  from inven
  3  where cost > 10
  4  group by dept
  5  having sum(onhand) > 25
  6  order by sum(onhand);
More data:

SQL> desc invcust;
 Name                            Null?    Type
 ------------------------------- -------- ----
 CUSTID                                   VARCHAR2(5)
 CUSTNAME                                 VARCHAR2(20)
 STADR                                    VARCHAR2(5)
 APT                                      VARCHAR2(5)
 CITY                                     VARCHAR2(15)
 STATE                                    CHAR(2)
 ZIP                                      VARCHAR2(5)
 PASTDUE                                  NUMBER(6,2)
 CURRDUE                                  NUMBER(6,2)
 CRLIMIT                                  NUMBER(6,2)
 DATEFST                                  DATE
 SLSREP                                   VARCHAR2(4)

SQL> select * from invcust;

CUSTI CUSTNAME             STADR           APT   CITY            ST ZIP     PASTDUE   CURRDUE
----- ------------------ ------------- ----- --------------- -- ----- --------- ---------
  CRLIMIT DATEFST   SLSR
--------- --------- ----
11111 Susan Ash          123 Elm St          Fall River      MA 02720         0         0
     1000 05-NOV-97 1111

12121 Richard Davis      24 West St          Fall River      MA 02720         0       100
      500 10-DEC-98 1212

12345 Linda Anderson     45 Main St    A#3   Seekonk         MA 02771       100         0
      500 14-OCT-98 1111

SQL> desc orderz
 Name                            Null?    Type
 ------------------------------- -------- ----
 ORDNO                                    VARCHAR2(6)
 CUSTID                                   VARCHAR2(5)
 ORDATE                                   DATE

SQL> select * from orderz;

ORDNO  CUSTI ORDATE
------ ----- ---------
000001 11111 10-JUN-99
000002 12121 10-JUN-99
000003 12345 10-JUN-99

SQL> desc ordline
 Name                            Null?    Type
 ------------------------------- -------- ----
 ORDNO                                    VARCHAR2(6)
 ITEMNO                                   VARCHAR2(4)
 NUMORD                                   NUMBER(3)

SQL> select * from ordline;

ORDNO  ITEM    NUMORD
------ ---- ---------
000001 1212         1
000001 2121         1
000001 2345         1
000002 1111         3
000002 3333         1
000003 3333         2
000003 3456         1

7 rows selected.
Problem #6: Use the tables above and write a query that would show all of the orders and all of the line items within the order. On the line you should also show the name of the item and the name of the customer.

Problem #7: What would this generate?
select * from inven where (dept, location) in
(select dept, location) from inven where reordpt > 20);

Problem #8: What would this generate?
select * from inven where dept in
(select dept from inven where price - cost > 10)
and location in
(select location from inven where price - cost > 10);

Problem #9: Explain the code and these results?
  1  select donor.idno, name, yrgoal, driveno
  2  from donor, donation
  3  where donor.idno = donation.idno and driveno in
  4  (select donation.driveno from donation, drive
  5  where donation.driveno = drive.driveno
  6* and lastyear > 5000)
SQL> /

IDNO  NAME                YRGOAL DRI
----- --------------- ---------- ---
11111 Stephen Daniels        500 100
23456 Susan Ash              100 100
33333 Nancy Taylor           150 300