Oracle Quiz - week 3
You are not running these - you are figuring out the solution.
Problem #1: Show the results of the query below and explain your answer.
Data in the table bldgofc:
BLDG OFFICE
-------------------- ------
K Building #112
K Bldg 115
H #114
SQL> SELECT SUBSTR(bldg,1,1)||SUBSTR(office,-3,3)
2 FROM bldgofc;
Problem #2: Show the results of the query below and explain your answer.
Data in the table ofbl:
BLDGOFC
--------------------
Building K #112
#115 K
Office K#114
SQL> SELECT SUBSTR(bldgofc,instr(bldgofc,'#')+1,3)
2 FROM ofbl;
Data in the table donor:
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
Problem #3: Show the results of the query below and explain your answer. Use the donor table.
SQL> SELECT SUBSTR(contact,ROUND(MOD(yrgoal,7),0),5)
2 FROM donor;
Problem #4: Show the results of the query below and explain your answer. Use the donor table.
SQL> select contact, count(*)
2 from donor
3 group by contact
4 order by contact;
Problem #5: Show the results of the query below and explain your answer. Use the donor table.
SQL> select state, count(*)
2 from donor
3 where datefst !='04-MAR-92'
4 group by state
5 having state = 'MA';
You are not running these, I just want you to show me the code. Use the where clause for the connections.
Use the donor data described in this set of notes: http://www.pgrocer.net/Cis50/multiple.html
Problem #6: Show all the donors who gave to drive 200. Include the drive number and drive
name in the results.
Problem #7: Show all the donors with their donations where the donations were greater
than 25.
Use the inventory data described in this set of notes:
http://www.pgrocer.net/Cis50/relorder.html
Problem #8: Show the orders and their line items where the price is greater than 15.
Include the customer name and the item name.
Problem #9: Show the orders and their line items and the sales rep number/code and their
name.
Problem #10: Write the SQL that takes a piece of information from all of the tables.