Oracle Quiz - week 3

You are not running these - you are figuring out the solution.

BLDG                 OFFICE
-------------------- ------
K Building           #112
K Bldg               115
H                    #114

SQL> SELECT SUBSTR(bldg,1,1)||SUBSTR(office,-3,3)
  2 FROM bldgofc;

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.