For both Parts 1 and 2 submit a separate .sql file.

Part 1. PL/SQL Programming (40 points) 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', 85, 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, 83 );

INSERT INTO STUDENT VALUES ( '888', '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 students 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 students 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

888 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.