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.
select * from inven where (dept, location) in (select dept, location) from inven where reordpt > 20);
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);
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