Oracle assignment on functions etc.

First five problems - you are not running these - you are figuring out the solution and showing it to me.

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';

Now we come to a set of problems where you need to write the code and show me the output.
Problem #6: Do a query using single row functions that flip the name so that it reads first middle (if any) last
instead of last/first middle. For example:

Doe/John M should be flipped to be John M Doe

Jones/Mary should be flipped to Mary Jones

Adams-Costa/Susan Ann should be flipped to be Susan Ann Adams-Costa

Langley/M Richard should be flipped to be M Richard Langley

Problem #7: Display a number, the number squared, the number cubed and the square root of the number.

Problem #8: Display a decimal number and then display it rounded and truncated. Use a number where rounded
and truncated will display different values.

Problem #9: Show me todays date and the date of the next Friday.
Problem #10: Show me todays date and in next_day use a number from 1 to 7 instead of Friday. What do you get?
Explain how it works.

Problem #11: Use to_char and extract the month in numbers from the sysdate.

Problem #12: Using a database you created, pick a field and CODE a decode statement that handles
different values in the field differently. Show me a select of the table data you are using.

Problem #13: Calculate a code number in the following way using a table you have created. The
number has to be the third and fourth characters of one of your fields concatenated with the mod
from your numeric field divided by 3, concatenated with the last name from the name field concatenated
with the length of one of your fields.

Problem #14: Using the emp table group on one field and show me the count and the sum of
your numeric field.

Problem #15: Repeat the query above but exclude one or more records as you form the groups.

Problem #16: Repeat the query again but now also exclude one of the groups from the list.

Problem #17: Using emp I want you to eliminate the job of CLERK as you form the groups and I want you to
eliminate department 30 from the groups that you display. I want the display to also be in order
by dept. You should group on dept and you should display the dept and the sum and avg of the salary
for the dept.

Problem #18: Write the script to create the dept table. The structure and data are available at
Oracle tables

Problem #19: Write a nested function and explain its goal (how it works) and show the output.

Problem #20: Write a problem using groups where you exclude records when you form the groups and where you
exclude some groups from the groups you formed. Explain how it works and show the output as always.