Quiz #2 - 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);