Project2  PL/SQL

Project2  PL/SQL
In this project, you will perform some common database operations. The schema of the database is exactly the same as the one you used in Project 1. You can use the scripts in Project 1 to create tables and import the data. The numbers should be accurate to 2 decimal places. (For instance, 90, 90.5 and 90.50 are all acceptable. But 90.555 is unacceptable.) You MUST use PL/SQL for this homework. You should use your Purdue Oracle account to create and test the queries. Submit your answers via Blackboard. Company(CompId, CompName, Address) Recruiter(RecId, RecName) Student(StudentId, StudentName, SchoolId, BirthDate, Grade) Job(JobId, CompId, JobNum, JobTitle, Salary, OfferYear) School(SchoolId, SchoolName, Address) Internship(StudentId, CompId, RecId, OfferYear) JobApplication(JobId, StudentId, ApplicationDate)
Create a file named project2_YourEmailAlias.sql. The first line of this file should be: set serveroutput on size 32000 Your file should contain code to create and run six procedures: Your file should look something like this:
/* create the procedure */ create or replace procedure pro_AvgGrade as /* declarations */ begin /* code */ end;
/* actually run the procedure */ begin pro_AvgGrade; end;
create or replace procedure pro_DispInternSummary as begin /*code*/ end; begin pro_DispInternSummary; end;
Procedures: 1- pro_AvgGrade: Generate a report about the average score of courses ordered by school. Sort the result by the school name. The average score for each school should be marked with an X under the corresponding bin. You should create bins of fixed length of 10 for the average score between the minimum and maximum average scores for school. The first bin should be the minimum average score rounded down with Base 10. The last bin should be the maximum average score rounded up with Base 10. For instance, if the minimum average score is 53, the range of the first bin should be f 50,<= 60}. If the maximum average score is 89, the range of the last bin should be { 80,<= 90}. There will be another 2 bins between them { 60,<= 70},{ 70,<= 80}. If the minimum average score is 50 and the maximum average score is 70, then the bins should be { 40,<= 50},{ 50,<=60},{ 60,<= 70}. Remember that the bin length is fixed. However, the number of bins depends on the minimum and maximum average scores for departments.
SCHOOLNAME AVGGRADE: 70, <=80 80,<=90 ------------------- ------------- ------------- School1 X School2 X School3 X
2- pro_DispInternSummary: Generate a histogram for the number of internships of the students. Include all discrete integer values in the range from min(numberOfInternships) to max(numberOfInternships). Mark the statistical median on the graph (in the sample output below, the median is number 2 ). [Aside: Make sure you know how the median is computed. What if the size of your input set is even?] Only mark the median if it is an integer value. In other words, if the median is a fraction, e.g., 3.5, the median need not be marked on the histogram.
numberOfInternships | #student 0 | 2 1 | 2 2 | 1 <--median 3 | 3 4 | 1

3- pro_AddIntern: Write a procedure to add a student's internship. The input parameters will be as follows: (StudentName, CompName, RecName, OfferYear). You can assume that the company and the recruiter exist in the database. You do not need to consider duplicate names. The result will be a new record in the database.
4- pro_DispCompany: Write a procedure to generate the information of companies. The information contains the Company name, Company address, Number of students interned, School where most internships come from. Average grade of students interned. More than one school can have the most number of internships. If more than one school have the highest number of internships, use ‘/’ to concatenate them based on alphabetical order. Average student grade should be truncated to two decimal points. Companies should be sorted based on the company name.
CompanyName Address NumOfStundentInerns School AverageGrade -------------------- ----------- ------------------------------- -------- ------------------- Company1 Address1 3 school1/school2 3.45 Company2 Address2 0
5- pro_SearchStudent: Write a procedure to generate the information of a student based on the student id provided as an input by the user. The student information contains the Student id, Student name, School name, Grade, Number of internships, Number of job applications.
Enter Value for StudentID: 1 (NOTE: The user input '1') StudentId StudentName School Grade NumOfInternships NumOfJobApp ----------- ------------------ --------- -------- ------------------------ --------------------- 1 Student1 School1 3.66 3 0
6- pro_SearchRecuiter: Write a procedure to generate the recruiter information based on the recruiter’s name provided as input by the user. The recruiter information contains the Recruiter id, Recruiter name, School with the most interns. Number of Internships per company, Average student grade per company
Enter Value for RecName: recuiter1 (NOTE: The user input ' recuiter1 ') RecID: 1 RecName: recuiter1 School with most interns: School1/School2 CompanyName NumberOfInterns AverageStudentGrade -------------------- ------------------------ ------------------------------ Company1 3 3.44 Company2 4 3.55
Submission instructions:
Please submit via Blackboard the following: 1. Your PL/SQL script containing the 6 PL/SQL procedures(project2_YourEmailAlias.sql) 2. A README file containing your first name, last name and your Purdue email address.