f) Display the names and numbers of all teachers who are class teachers and are in grade ‘C’.

1select t_no,f_name

2from teacher t, payscale p

3where p.grade='C' AND t.salary =(select salary from teacher

4*where salary BETWEEN '10000'AND '17999')

QL> /

T_NO F_NAME

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

Arshi

Display details of all those teachers who are class teachers of classes 1 to 5.

SQL> select f_name,l_name, class_no

2from teacher, class

3where teacher.t_no=class.t_no(+);

F_NAMEL_NAMECLASS_NO

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

farazahmad5

JaideepSharma8

zakirAli9

ShaistaKhan6

AsmaHusain3

Q3)

Design a suitable database system for a bank along with 20 possible queries to the database (the queries should be such that their solution involves subqueries or joins or both). Implement the database and the queries in a commercial DBMS using SQL.

Write a host language block to delete all the rows from the ‘teacher’ table where the salary is less than Rs.5000.

DECLARE

c_t_no teacher.t_no%TYPE;

c_f_name teacher.f_name%TYPE;

c_l_name teacher.l_name%TYPE;

c_salary teacher.salary%TYPE;

CURSOR c1 IS

SELECT t_no,f_name, l_name, salary

FROM teacher;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO c_t_no, c_f_name, c_l_name, c_salary ;

EXIT WHEN NOT c1%FOUND;

UPDATE teacher SET salary = salary * 1.10 WHERE salary < 5000;

END LOOP;

CLOSE c1;

END;

2) Write a host language code to insert the supervisor information from ‘teacher’ table to another table called ‘supervisor’. The new table should have only those records where the job title is ‘supervisor’.

DECLARE

CURSOR c2 IS

SELECT t_no,f_name, l_name, salary

FROM teacher ;

teacher_rec c2%ROWTYPE;

BEGIN

OPEN c2;

FOR teacher_rec IN c2

LOOP

IF teacher_rec.salary > 20000

Teacher_rec.title = “SUPERVISOR”;

ENDIF;

END LOOP;

CLOSE c2;

END;

SESSION 5

1) Write a function that gets the teacher id as parameter and returns the class number associated with that teacher. If the teacher is not a class teacher then give suitable message.

DECLARE

C_id teacher.t_no%TYPE;

C_f_name teacher.f_name%TYPE;

want_id NUMBER := 110;

BEGIN

SELECT t_no, f_name INTO c_t_no, c_f_name from teacher

WHERE t_no = want_id;

DBMS_OUTPUT.PUTLINE ( “teacher : “|| c_t_no ||’ ‘||c_f_name)

EXCEPTION

WHEN INVALID_NUMBER THEN

DBMS_OUTPUT.PUTLINE(want_id || ‘ not a valid teacher id’);

END;

CREATE OR REPLACE TRIGGER new_teacher _id

AFTER INSERT ON teacher

FOR EACH ROW

DECLARE

o_t_no teacher.t_no%TYPE;

o_joiningdate teacher.joiningdate%TYPE;

BEGIN

SELECT t_no_sequence.nextval

INTO o_t_no

FROM dual;

:NEW.t_no := o_t_no;

:NEW.joiningdate := SYSDATE;

END;

Session 6

Find the grade of teachers.

CREATE OR REPLACE FUNCTION get_grade (o_t_no IN NUMBER)

IS o_grade VARCHAR2(20);

BEGIN

SELECT grade INTO o_grade FROM Payscale, teacher

WHERE t_no = o_t_no AND salary between min_limit AND max_limit;

RETURN (o_grade);

END get_grade;

Exercise 8

1) Add a nested table in the teacher relation. Do some queries using nested tables?

Ans.)

CREATE TABLE student_credits

(rollnoNUMBER(5),

s_name VARCHAR2(25),

subject_creditsNEW_TYPE)

NESTED TABLE subject_credits STORE AS new_type_table;

INSERT INTO student_credits

VALUES (100, ‘suman’ , new_table ( new_type (‘english’ , 30),

new_table ( new_type(‘hindi’, 35)));

SELECTs.credit_hours FROM

THE (SELECT subjects_credit FROM student_credits

WHERE s_name = ‘suman’)s

WHERE s.subject_name = ‘english’;

Q2) Create at least two nested tables for both the University and Bank database

systems. Use these tables and enter some data into these relations. Query these