Write in SQL

Problems:

1. (PL/SQL Programming) Consider the table EMPLOYEE with attributes ID, Name, and Hours, and the table PAYINFO with attributes Regular, Overtime, and Cutoff, defined and populated by the following script:

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE EMPLOYEE

(

ID CHAR(9),

Name VARCHAR2(12),

Hours NUMBER(3),

CONSTRAINT PK_EMPLOYEE

PRIMARY KEY (ID)

);

INSERT INTO EMPLOYEE VALUES ('717222144', 'Dolenz', 30);

INSERT INTO EMPLOYEE VALUES ('013331902', 'Jones', 50);

INSERT INTO EMPLOYEE VALUES ('591342771', 'Nesmith', 65);

INSERT INTO EMPLOYEE VALUES ('822182889', 'Tork', 25);

SELECT * FROM EMPLOYEE;

DROP TABLE PAYINFO;

CREATE TABLE PAYINFO

(

Regular NUMBER(4,2),

Overtime NUMBER(4,2),

Cutoff NUMBER(2)

);

INSERT INTO PAYINFO VALUES (15.00, 18.50, 40);

SELECT * FROM PAYINFO;

COMMIT;

Write a script file Problem1.sql containing an anonymous PL/SQL block that will do the following:

First, report three values found in the PAYINFO table and store them in variables. (You may assume that the PAYINFO table contains only one record.) Next, output the ID and Name of each employee in the EMPLOYEE table and their total pay, computed as:

Regular * Hours if Hours <= Cutoff

(Regular * Cutoff) + (Overtime * (Hours Cutoff)) if Hours > Cutoff

(In other words, each employee is paid at the regular hourly rate for hours up to the cutoff value, and is paid at the higher overtime rate for any hours beyond the cutoff value.) Output each employees information on a separate line. For the sample data given above, the output should be:

Values are 15, 18.5, 40

717222144 Dolenz 450

013331902 Jones 785

591342771 Nesmith 1062.5

822182889 Tork 375

Of course, this is just an example your PL/SQL block should work in general, not just for the given sample data.

2. (Triggers) Consider the MAJOR and STUDENT tables defined by the following script, which also populates the MAJOR table:

DROP TABLE MAJOR CASCADE CONSTRAINTS;

DROP TABLE STUDENT CASCADE CONSTRAINTS;

CREATE TABLE STUDENT

(

ID CHAR(7),

Name VARCHAR2(20),

Major VARCHAR2(3)

CHECK (Major in ('CS', 'IT', 'SE')),

CONSTRAINT PK_STUDENT

PRIMARY KEY (ID)

);

CREATE TABLE MAJOR

(

Name CHAR(2)

PRIMARY KEY,

Count NUMBER

);

INSERT INTO MAJOR VALUES ('CS', 0);

INSERT INTO MAJOR VALUES ('IT', 0);

INSERT INTO MAJOR VALUES ('SE', 0);

SELECT * FROM MAJOR;

COMMIT;

The Count attribute of MAJOR should store a count of how many students have declared that major that is, the number of records in STUDENT with that Major. Your task is to write three triggers that will maintain the value of the Count attribute in MAJOR as changes are made to the STUDENT table.

Write a script file Problem2.sql containing definitions of the following three triggers:

1. The first trigger should fire whenever a user INSERTS a row into STUDENT. The trigger will increase the value of Count in the MAJOR table by one in the row corresponding to the Major in the new row of STUDENT.

2. The second trigger will fire when a user attempts to UPDATE one or more rows of STUDENT. The trigger will simply cancel the UPDATE and display an error message stating that no updates are permitted to the STUDENT table.

3. The third trigger should fire when a user DELETES one or more rows from STUDENT. This trigger will update the values of Count in the MAJOR table for all affected rows to make sure that they are correct, by decreasing the value of Count by one each time a row with that Major is removed from STUDENT.

The first and third triggers will have to be row-level triggers; the second should be a statement-level trigger.

Run the script to define your triggers and test them to make sure they work by doing a few INSERTS, UPDATES, and DELETES on the STUDENT table.

Remarks:

1. You may cut and paste the scripts I have supplied into SQLDeveloper to set up the tables so that you can test your scripts, but your submitted answers should include only the code you have written to solve the problems not any of my code. Be sure that your submitted script files are in a plain text form that can be cut and pasted into SQLDeveloper for testing.