You are
going to use PL/SQL (Oracle's procedural extension to SQL) to write a few
functions and procedures to process data. The result of the work should be a
file named your_login.sql which contains all the PL/SQL statements you
used to handle the data processing tasks listed below. Your grade depends on
how you use the PL/SQL statements and the result of running the resulting .sql
file.
Due date: Sept 22nd at 11:59pm.
You
will use the same schema as you did for Project #2.
Do not forget to create the PKs and FKs to test your code. This step is
necessary to build the PL/SQL procedures and functions but it will NOT be
included in your your_login.sql.
Your
task:
1. Create
the 5 tables.
2. Execute
the file (data.sql)
to insert the test data into the tables you created in the previous step.
Refer to Step 1.
Important: The first line of your .sql file should be the following:
set serveroutput on size 32000
... otherwise don't come complaining to me when dbms_output isn't printing anything out. :)
Generate a report that lists, for each department, the faculty members in that department. The output should be modeled as follows. Sort by the department name; also sort faculty members by their name.
Department: Computer Sciences Total faculty members: 3 ------------- 1. Alice 2. Bob 3. Joe Department: ECE Total faculty members: 2 ------------- 1. Joyce 2. Sam Department: Management Total faculty members: 0 ------------- ...
Generate a report that contains statistics about faculty. Print out the student to faculty ratio (integer-truncate the students for the faculty ratio). Print out the average number of classes per faculty (truncate to 1 decimal place). Then, print out the number of students that each faculty is teaching; omit faculty with no students. Sort by faculty name.
Student to faculty ratio: 20:1 Average number of classes per faculty: 1.3 Faculty Name # Students --------------- ---------- Bob 15 Joe 30 ...
Generate a report about the number of classes a student is in. The report should be as follows:
# classes
Student Name 0 1 2 >=3
------------ --- --- --- ---
Joe X
Mary X
Random X
...
Generate a histogram for the ages of students. Include all discrete integer values in the range min(age) .. max(age). Mark the statistical median on the graph (in this case, it's 19). [Aside: Do you really know the definition of median? What if the size of your input set is even? Be careful on this one.] An example report is as follows:
age 17 | 1 x 18 | 10 xxxxxxxxxx 19 | 5 xxxxx <-- median 20 | 2 xx 21 | 8 xxxxxxxx 22 | 3 xxx 23 | 0 24 | 1 x
Write a procedure to insert a new class, given a faculty member's name. The input will be as follows: fname_in,
cname_in, meets_at_in, room_in. The result will be a new class added. Assume there are no duplicate fname's in the
faculty table. Execute this procedure to add the class CS100, meeting at 530 in A001, being taught by S.Boling. Do a
select * from class before and after this procedure is run.
Your task: