Interested in a PLAGIARISM-FREE paper based on these particular instructions?...with 100% confidentiality?

Order Now

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, 80, 60, 70, 40, 50, 60,70, 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.

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.

Interested in a PLAGIARISM-FREE paper based on these particular instructions?...with 100% confidentiality?

Order Now