1. Create a PL/SQL program block that determines the top students with respect to GPA.

Assume that the database has four tables. Student(SSN, SName, DOB, Major) , Grade(SSN, CNo, Grade(0,1,2,3,4)) and Course table(CNo,CName, Credit Hour), Prerequisite(CNo, PreCNo);

Student and couse data ae given in the following SQL statements

a. Accept a number n as user input with SQL*Plus telling top n%.

b. In a loop get the SName and GPA of the top n% people with respect to GPA.

c. Store the SName and GPA in the TOP_GPA table.

d. Assume that two people may have the same GPA. If one is listed then the other must also be listed.

e. At least one student need to be reported.

(fraction is rounded up at 5 and rounded off below 4) If there are number of students having the same GPA the cut off line should not be in the middle of these students. In other word if you include one of them you must include all of them.

Report top 5%, 30%, 50% students in term of GPA

2. Report all course names and their prerequisite course names.

Reference for problem #2 in A1

Hierarchical Queries in Oracle SQL

SELECT ename, empno, mgr

from emp

ENAME EMPNO MGR

--------------- ---------- ----------

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

FORD 7902 7566

SMITH 7369 7902

BLAKE 7698 7839

ALLEN 7499 7698

WARD 7521 7698

MARTIN 7654 7698

TURNER 7844 7698

JAMES 7900 7698

CLARK 7782 7839

MILLER 7934 7782

KINGS empno is the prior empno. If we build the entire hierarch -- we have:

scott@ORA8I.WORLD> select lpad(' ',level*2,' ')||ename ename, empno, mgr

2 from emp

3 START WITH MGR IS NULL

4 CONNECT BY PRIOR EMPNO = MGR

5 /

ENAME EMPNO MGR

--------------- ---------- ----------

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

FORD 7902 7566

SMITH 7369 7902

BLAKE 7698 7839

ALLEN 7499 7698

WARD 7521 7698

MARTIN 7654 7698

TURNER 7844 7698

JAMES 7900 7698

CLARK 7782 7839

MILLER 7934 7782

14 rows selected.

The tree is displayed with the children indented under their parents by using padding with a number of spaces proportional to LEVEL.

The pseudo-column level returns the depth of the hierarchy. The first level is the root:

SELECT ENAME

FROM EMP

CONNECT BY PRIOR EMPNO = MGR

START WITH ENAME = 'JONES';

ENAME

----------

JONES

SCOTT

ADAMS

FORD

SMITH

3. Design a trigger in PL/SQL based on the problem above.

If user wants to add new record into Grade table without meeting the prerequisite of that course the action should be denied and report the missing pre-required course names.

CREATE TABLE STUDENT

(SID NUMBER(9),

SNAME VARCHAR2(30),

DATA_BIRTH DATE,

MAJOR VARCHAR2(30)

)

/

CREATE TABLE GRADE

(SID NUMBER(9),

CNo VARCHAR2(6),

SCORE NUMBER(2)

)

/

CREATE TABLE COURSE

(Cno VARCHAR2(6),

CTITLE VARCHAR(30),

CREDIT_HOUR NUMBER(2)

)

/

CREATE TABLE PREREQUISITE

(CNO VARCHAR2(6),

PRECNO VARCHAR2(6)

)

/

INSERT INTO STUDENT

VALUES (111, 'S1', '01-NOV-1993', 'CS')

/

INSERT INTO STUDENT

VALUES (222, 'S2', '16-JAN-1993', 'CS')

/

INSERT INTO STUDENT

VALUES (333, 'S3', '21-FEB-1993', 'MATH')

/

INSERT INTO STUDENT

VALUES (444, 'S4', '02-MAR-1993', 'MATH')

/

INSERT INTO STUDENT

VALUES (555, 'S5', '02-MAR-1993', 'CS')

/

INSERT INTO COURSE

VALUES ('C1', 'CS1', 4)

/

INSERT INTO COURSE

VALUES ('C2', 'CS2', 3)

/

INSERT INTO COURSE

VALUES ('C3', 'CS3', 2)

/

INSERT INTO COURSE

VALUES ('C4', 'CS4', 4)

/

INSERT INTO PREREQUISITE

VALUES ('C2', 'C1')

/

INSERT INTO PREREQUISITE

VALUES ('C3', 'C1')

/

INSERT INTO PREREQUISITE

VALUES ('C4', 'C2')

/

INSERT INTO PREREQUISITE

VALUES ('C4', 'C3')

/

INSERT INTO GRADE

VALUES (111, 'C1', 4)

/

INSERT INTO GRADE

VALUES (111, 'C2', 4)

/

INSERT INTO GRADE

VALUES (222, 'C1', 4)

/

INSERT INTO GRADE

VALUES (333, 'C1', 3)

/

INSERT INTO GRADE

VALUES (333, 'C2', 2)

/

INSERT INTO GRADE

VALUES (444, 'C2', 3)

/

INSERT INTO GRADE

VALUES (444, 'C1', 3)

/

INSERT INTO GRADE

VALUES (555, 'C1', 3)

/