Monday, May 9, 2011

IGNOU MCSL-045 Lab Manual Solution



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')
) );
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




No comments:

Post a Comment