Session 1
SQL> select * from teacher
2 /
T_NO F_NAME L_NAME SALARY SUPERVISOR JOININGDA BIRTHDATE TITLE
---------- ------------------------- ------------------------- ---------- ------------------------------ --------- --------- ----------
1 faraz ahmad 22000 Arshad Iqbal 25-JAN-10 25-MAY-86 Primary
2 Jaideep Sharma 23000 Asim Zafar 23-JUN-09 04-APR-86 PRT
3 zakir Ali 22000 Asim Zafar 03-DEC-09 24-AUG-87 PGT
4 Shaista Khan 23500 Arshad Iqbal 03-MAY-10 23-JUL-86 PRT
Asma Husain 21300 Aqeel Ahmad 24-MAY-10 20-NOV-84 Primary
Display the name of the teacher(s) who is (are) the youngest among all the teachers.
SQL> select f_name, l_name
2 from teacher
3 where birthdate=(select max(birthdate) from teacher)
4 ;
F_NAME L_NAME
------------------------- -------------------------
zakir Ali
b) Display details of all the teachers who have the same job title as that of ‘Jaideep’
1 select * from teacher
2* where title=(select title from teacher where LOWER(f_name)='jaideep')
SQL> /
T_NO F_NAME L_NAME SALARY SUPERVISOR JOININGDA BIRTHDATE TITLE
---------- ------------------------- ------------------------- ---------- ------------------------------ --------- --------- ----------
2 Jaideep Sharma 23000 Asim Zafar 23-JUN-09 04-APR-86 PRT
4 Shaista Khan 23500 Arshad Iqbal 03-MAY-10 23-JUL-86 PRT
e) Identify all those teachers who are in grade ‘B’.
select distinct *from teacher,payscale
where grade='B'
/
T_NO F_NAME L_NAME SALARY SUPERVISOR JOININGDA BIRTHDATE TITLE GRADE
------ -------------- ------------------------- ---------- ------------------------------ --------- --------- ---------- -----
5 Asma Husain 21300 Aqeel Ahmad 24-MAY-10 20-NOV-84 Primary B
7 naim ahmad 29000 Arshad Iqbal 15-AUG-05 16-MAY-80 PGT B
2 Jaideep Sharma 23000 Asim Zafar 23-JUN-09 04-APR-86 PRT B
1 faraz ahmad 22000 Arshad Iqbal 25-JAN-10 25-MAY-86 Primary B
4 rows selected.
g) Display the names of all teachers who are supervisors.
1 select * from teacher
2* where teacher.supervisor='jaideep'
SQL> /
T_NO F_NAM L_NAME SALARY SUPERVISOR JOININGDA BIRTHDATE TITLE
--------- -------------------- ------------------------- ---------- ------------------------------ --------- --------- ----------
arshi khan 14000 jaideep 12-MAR-09 15-OCT-87 PRT
f) Display the names and numbers of all teachers who are class teachers and are in grade ‘C’.
1 select t_no,f_name
2 from teacher t, payscale p
3 where 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
2 from teacher, class
3 where teacher.t_no=class.t_no(+);
F_NAME L_NAME CLASS_NO
------------------------- ------------------------- ----------
faraz ahmad 5
Jaideep Sharma 8
zakir Ali 9
Shaista Khan 6
Asma Husain 3
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.
Bank database
1 create table acco_master (accno number(10) primary key,name
2* varchar2(10),balance number(10))
SQL> /
Table created.
SQL>insert into acco_master values(&accno,'&name',&balance)
Enter value for accno:111
Enter value for name:faraz
Enter value for balance:200000
SQL>create table acco_trans(accno number(10),trans_date date,deb_cre
varchar2(10),check(deb_cre IN('debit','credit')),amount number(10), process varchar2(10)
check(process IN('yes','no')) foreign key (accno)references acco_master);
Table Created
SQL> insert into acco_trans values(&accno,'sysdate,'&deb_cre',&amount,
'&process');
Enter value for accno:111
Enter value for deb_cre:debit
Enter value for amount:1000
Enter value for process:yes
SESSION 2
Display teacher number, their names, age and grade of all PGT teachers.
SELECT t_no, name, age, grade
FROM teacher_details
WHERE UPPER (title) = ‘PGT’;
b) Create a non-unique index on the foreign key column of the ‘class’ table.
SQL> create index class
2 on class(t_no, room_no);
Index created.
g) Create a non-unique index on the names of teachers in the ‘teachers’ table.
SQL> create index t_name
2 on teacher (f_name, l_name)
3 /
Index created.
h) Drop the index created in (b).
SQL> drop index class
2 /
Index dropped.
j) Display details of all the teachers who are more than 40 years old.
SELECT t_no, name, salary, title, age
FROM teacher_details
WHERE age > 40;
No row selected.
Session 3
(a) Calculate the bonus amount to be given to a teacher depending on the following conditions:
I. if salary > 10000 then bonus is 10% of the salary.
II. if salary is between 10000 and 20000 then bonus is 20% of the salary.
III. if salary is between 20000 and 25000 then bonus is 25% of the salary.
IV. if salary exceeds 25000 then bonus is 30% of the salary.
create or replace procedure bonus_calc integer
(o_t_no IN INTEGER, bonus OUT INTEGER)
is
salary INTEGER
BEGIN
select salary, f_name, l_name,bonus
from teacher
where t_no= o_t_no
IF salary> 10000
then bonus:= salary+ salary* 0.10
END IF
IF salary between 10000 and 20000
then bonus:= salary + salary * 0.20
END IF
IF salary between 20000 and 25000
then bonus:= salary + salary * 0.25
END IF
IF salary between 25000 and 30000
then bonus:= salary + salary * 0.30
END IF
END
Procedure successfully Created!
Exec bonus_calc
SALARY F_NAME L_NAME BONUS
---------- ------------------------- ------------------------- -------------------------
22000 faraz ahmad 5500
23000 Jaideep Sharma 5750
22000 zakir Ali 5500
23500 Shaista Khan 5875
21300 Asma Husain 5325
14000 arshi khan 2800
29000 naim ahmad 8700
(h) Calculate the tax to be paid by all teachers depending on following conditions:
I. if annual salary > 1,00,000 then no tax.
II. if annual salary is between 1,00,001 and 1,50,000 then tax is 20% of the annual salary.
III. if annual salary is between 1,50,001 and 2,50,000 then tax is 30% of the annual salary.
IV. if salary exceeds 2,50,000 then tax is 40% of the annual salary.
create or replace procedure tax_calc integer
(o_t_no IN INTEGER, tax OUT INTEGER)
is
salary INTEGER
BEGIN
select f_name, l_name,tax
from teacher
where t_no= o_t_no
IF salary<100000
salary=salary*12
then tax:=0
END IF
IF salary between 100001 and 150000
then tax:= salary * .20
END IF
IF salary between 150001 and 250000
then tax:= salary * .30
END IF
IF salary >250001
then tax:= salary * .40
* END IF
Procedure created successfully!
Exec tax_calc
T_NO F_NAME L_NAME SALARY TAX
--------- ------------------------- ------------------------- ---------- ----------
1 faraz ahmad 22000 105600
2 Jaideep Sharma 23000 110400
3 zakir Ali 22000 105600
4 Shaista Khan 23500 112800
5 Asma Husain 21300 102240
6 arshi khan 14000 50400
7 naim ahmad 29000 139200
Q3)Implement at least five procedures for the Bank Database system using embedded SQL.
SQL>set serveroutput on
SQL>declare
cursor c_bank is select * from acco_trans;
v_bank c_bank%rowtype;
balance number(5);
begin
open c_bank;
loop
fetch c_bank into v_bank;
exit when c_bank%notfound;
if v_bank.process='no' then
update acco_trans set process='yes' where
accno=v_bank.accno;
if v_bank .deb_cre='credit' then
update acco_master set balance=balance+v_bank.amount
where v_bank.accno=acco_master.accno;
elsif v_bank.deb_cre='debit' then
update acco_master set balance=balance-v_bank.amount
where v_bank.accno=acco_master.accno;
elsif balance<=0 then
dbms_output.put_line('Transaction not possible');
end if;
end if;
end loop;
close c_bank;
end;
SQL>select * from acco_trans;
AccNo Trans_Date Deb_Cre Amt Pro
---------- -------------- ------------ --------- --------------
1012 12-Jan-08 debit 5000 yes
1024 14-Feb-08 credit 100 yes
1987 04-Dec-07 credit 1000 yes
2345 17-Mar-08 credit 20000 yes
Cursor for BANK DATABASE
SQL > create table depositor (accno primary key , cname char(10))
Table created.
SQL > create table borrower (loanno number , cname char(10))
Table created.
SQL > create table loan( loanno number , brname char(10),amt number)
Table created.
SQL > create table acct-t( acctno number , bal number, brname char(10), foreign key (acctno)
references depositor (acctno)
Table created.
SQL > insert into depositor values (&accno , &cname );
Enter value for accno : 101
Enter the value for cname : Alan
SQL > insert into acct-t values( &acctno , &bal , '&brname ');
Enter value for accno : 101
Enter the value for bal : 20000
Enter the value for brname : tvm
SQL > select * from depositor;
ACCNO CNAME
101 Alan
102 Ann
103 Ben
SQL > select * from acct-t;
ACCNO BAL BRNAME
------------- ------------------- ----------------
101 20000 tvm
102 10500 ekm
103 5000 tcr
SQL > create or replace trigger
declare
c varchar2 (20)
begin
if (:new.bal < :old.bal) then
insert into loan values (:new.accno, :new.brname , :old.bal-:new.bal);
select cname into c from depositor where accno = new.accno;
insert into borrower values (:new.accno,c);
endif;
end;
/
Trigger created.
SQL > update acct-t set bal = bal-5000 where acctno=101
1 row updated.
SQL >select * from borrower;
LOANNO CNAME
-------------- --------------------
101 Alan
SQL >select * from loan;
LOANNO BR NAME AMT
101 tvm 15000
SESION 4
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
(rollno NUMBER(5),
s_name VARCHAR2(25),
subject_credits NEW_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)));
SELECT s.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
databases.
CREATE TYPE address_t AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/
CREATE TABLE customers (
custid NUMBER,
address address_tab )
NESTED TABLE address STORE AS customer_addresses;
INSERT INTO customers VALUES (1,
address_tab(
address_t('101 First', 'Redwood Shores', 'CA', '94065'),
address_t('123 Maple', 'Mill Valley', 'CA', '90952')
) );
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/
CREATE TABLE customers (
custid NUMBER,
address address_tab )
NESTED TABLE address STORE AS customer_addresses;
INSERT INTO customers VALUES (1,
address_tab(
address_t('101 First', 'Redwood Shores', 'CA', '94065'),
address_t('123 Maple', 'Mill Valley', 'CA', '90952')
) );
Exercise 9
Q1) Identify the use of large object types in the teacher’s table. Do some queries
using these objects.
Ans
CREATE TABLE message (
msg_id NUMBER(8) NOT NULL PRIMARY KEY,
email_add VARCHAR(200),
name VARCHAR (200),
message CLOB,
posting_time DATE,
sort_key VARCHAR (600));
DECLARE
Image10 BLOB;
image_number INTEGER := 101;
BEGIN
SELECT item_blob INTO image10 FROM lob_table10
WHERE key_value = image_number;
DBMS_OUTPUT.PUT_LINE('Image size
is:'||DBMS_LOB.GETLENGTH(image10));
------
------
------
END;
Exercise 10
Q1) Create a user account “class” and give privileges related to table/view creation,
deletion, updating and dropping.
Ans
CREATE USER class
IDENTIFIED BY pass;
GRANT CREATE TABLE, DROP TABLE, CREATE VIEW, DROP VIEW
TO class;
Q. 2) Create a student account and give permission to this account for only viewing
the information on the relation Class (class_no, t_no, room_no.
Ans
DENY UPDATE, DELETE, INSERT ON employee TO student
GO
CREATE USER student
@Eclass_no int,
@St_no money,
@room_no int
GRANT EXECUTE ON student TO Class
GO
