Description
Database Processing for Large-Scale Analytics
Part 1
Use a DataFrame in python to define the following queries using the Employee data (employee.csv is attached). You can read it using pandas.read_csv(‘Employee.txt’). Adding optional parameter names=[] will allow you to rename the columns.
a. Find all male employees
b. Find the highest salary for female employees
c. Print out salary groups (individual list of values without applying final aggregation) grouped by middle initial. That is, for each middle initial value, print all of the salaries in that group.
Part 2
Consider the table STUDENT with attributes ID, Name, Midterm, Final, and Homework, and the table WEIGHTS with attributes MidPct, FinPct, and HWPct defined and populated by the following script:
DROP TABLE STUDENT CASCADE CONSTRAINTS;
CREATE TABLE STUDENT(
ID CHAR(3),
Name VARCHAR2(20),
Midterm NUMBER(3,0) CHECK (Midterm>=0 AND Midterm<=100),
Final NUMBER(3,0) CHECK (Final>=0 AND Final<=100),
Homework NUMBER(3,0) CHECK (Homework>=0 AND Homework<=100),
PRIMARY KEY (ID)
);
INSERT INTO STUDENT VALUES ( '445', 'Seinfeld', 86, 90, 99 );
INSERT INTO STUDENT VALUES ( '909', 'Costanza', 74, 72, 86 );
INSERT INTO STUDENT VALUES ( '123', 'Benes', 93, 89, 91 );
INSERT INTO STUDENT VALUES ( '111', 'Kramer', 99, 91, 93 );
INSERT INTO STUDENT VALUES ( '667', 'Newman', 78, 82, 84 );
INSERT INTO STUDENT VALUES ( '889', 'Banya', 50, 65, 50 );
SELECT * FROM STUDENT;
DROP TABLE WEIGHTS CASCADE CONSTRAINTS;
CREATE TABLE WEIGHTS(
MidPct NUMBER(2,0) CHECK (MidPct>=0 AND MidPct<=100),
FinPct NUMBER(2,0) CHECK (FinPct>=0 AND FinPct<=100),
HWPct NUMBER(2,0) CHECK (HWPct>=0 AND HWPct<=100)
);
INSERT INTO WEIGHTS VALUES ( 30, 30, 40 );
SELECT * FROM WEIGHTS;
COMMIT;
Write an anonymous PL/SQL block that will do the following:
First, report the three weights found in the WEIGHTS table. (You may assume that the WEIGHTS table contains only one record.) Next, output the name of each student in the STUDENT table and their overall score, computed as x percent Midterm, y percent Final, and z percent Homework, where x, y, and z are the corresponding percentages found in the WEIGHTS table. (You may assume that x+y+z=100.) Also convert each student’s overall score to a letter grade by the rule 90-100=A, 80-89.99=B, 65-79.99=C, 0-64.99=F, and include the letter grade in the output. Output each student’s information on a separate line. For the sample data given above, the output should be:
Weights are 30, 30, 40
445 Seinfeld 92.1 A
909 Costanza 78.2 C
123 Benes 91 A
111 Kramer 94.2 A
667 Newman 81.2 B
889 Banya 54.5 F
Of course, this is just an example – your PL/SQL block should work in general, not just for the given sample data.
Part 3
Consider the SECTION and ENROLLMENT tables defined by the following script, which also populates the SECTION table;
DROP TABLE ENROLLMENT CASCADE CONSTRAINTS;
DROP TABLE SECTION CASCADE CONSTRAINTS;
CREATE TABLE SECTION(
SectionID CHAR(5),
Course VARCHAR2(7),
Students NUMBER DEFAULT 0,
CONSTRAINT PK_SECTION
PRIMARY KEY (SectionID)
);
CREATE TABLE ENROLLMENT(
SectionID CHAR(5),
StudentID CHAR(7),
CONSTRAINT PK_ENROLLMENT
PRIMARY KEY (SectionID, StudentID),
CONSTRAINT FK_ENROLLMENT_SECTION
FOREIGN KEY (SectionID)
REFERENCES SECTION (SectionID)
);
INSERT INTO SECTION (SectionID, Course) VALUES ( '12345', 'CSC 355' );
INSERT INTO SECTION (SectionID, Course) VALUES ( '22109', 'CSC 309' );
INSERT INTO SECTION (SectionID, Course) VALUES ( '99113', 'CSC 300' );
INSERT INTO SECTION (SectionID, Course) VALUES ( '99114', 'CSC 300' );
COMMIT;
SELECT * FROM SECTION;
The Students attribute of SECTION should store a count of how many students are enrolled in the section – that is, the number of records in ENROLLMENT with that SectionID – and its value should never exceed five (they are very small sections…). Your task is to write two triggers that will maintain the value of the Students attribute as changes are made to the ENROLLMENT table.
Write definitions of the following two triggers:
A. Write a trigger that will fire when a user attempts to INSERT a row into ENROLLMENT. This trigger will check the value of SECTION.Students for the corresponding section. If SECTION.Students is less than 5, then there is still room in the section so allow the insert and update SECTION.Students. If SECTION.Students is equal to 5, then the section is full so it will cancel the INSERT and display an error message stating that the section is full.
You can raise an error using:
raise_application_error(-20102, '[Place your error message here]');
Sample Data:
INSERT INTO ENROLLMENT VALUES ('12345', '1234567');
INSERT INTO ENROLLMENT VALUES ('12345', '2234567');
INSERT INTO ENROLLMENT VALUES ('12345', '3234567');
INSERT INTO ENROLLMENT VALUES ('12345', '4234567');
INSERT INTO ENROLLMENT VALUES ('12345', '5234567');
INSERT INTO ENROLLMENT VALUES ('12345', '6234567');
SELECT * FROM Section;
SELECT * FROM Enrollment;
The last insert should return an error message that looks like:
Error starting at line : 27 in command -
INSERT INTO ENROLLMENT VALUES ('12345', '6234567')
Error report -
SQL Error: ORA-20200: Section is full.
ORA-06512: at "ARASIN.ADDSTUDENT", line 14
ORA-04088: error during execution of trigger 'ARASIN.ADDSTUDENT'
The output from the SELECT queries should look like:
SECTIONID COURSE STUDENTS
--------- ------- ----------
12345 CSC 355 5
22109 CSC 309 0
99113 CSC 300 0
99114 CSC 300 0
SECTIONID STUDENTID
--------- ---------
12345 1234567
12345 2234567
12345 3234567
12345 4234567
12345 5234567
B. Write a trigger that will fire when a user attempts to DELETE one or more rows from ENROLLMENT. This trigger will update the values of SECTION.Students for any affected sections to make sure they are accurate after the rows are deleted, by decreasing the value of SECTION.Students by one each time a student is removed from a section.
Sample Data:
DELETE FROM ENROLLMENT WHERE StudentID = '1234567';
SELECT * FROM Section;
SELECT * FROM Enrollment;
The output from the SELECT queries should look like:
SECTIONID COURSE STUDENTS
--------- ------- ----------
12345 CSC 355 4
22109 CSC 309 0
99113 CSC 300 0
99114 CSC 300 0
SECTIONID STUDENTID
--------- ---------
12345 2234567
12345 3234567
12345 4234567
12345 5234567
