Question

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:

[email protected]> 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)

/

Answer 1

As per the given data PL/SQL program for following question1:

create table TOP_GPA(SName char(50), GPA number)     #create table for resultant table#

insert into TOP_GPA ( select sname,gpa from(

   select ssn, gpa,dense_rank() over (order by gpa desc) rn from student_gpa

#inserting values to resultant table#

     )

   )

where rn = n%100;

#insert your n% here#