DBMS Manual by mannu7799

VIEWS: 380 PAGES: 175

More Info
									DBMS                             LAB MANUAL   0

               A Helpful Hand




       DEPARTMENT OF COMPUTER SCIENCE
              AND ENGINEERING
DBMS                                                       LAB MANUAL    1

   How to Write and execute sql, pl/sql commands/programs:

   1). Open your oracle application by the following navigation
   Start->all programs->Oracle Database 10g Express Edition
   ->Run SQL Command Line

   2). You will be asked for user name, password.
   You have to enter user name, pass word.

   3). Upon successful login you will get SQL prompt (SQL>).
   In two ways you can write your programs:
   a) directly at SQL prompt (or)
   b) in sql editor.

   If you type your programs at sql prompt then screen will look
   like follow:
   SQL> SELECT ename,empno,
   2 sal from
   3 emp;
   where 2 and 3 are the line numbers and rest is the command
   /program……

   to execute above program/command you have to press ‘/’ then
   enter.

   Here editing the program is somewhat difficult; if you want to
   edit the previous command then you have to open sql editor
   (by default it displays the sql buffer contents). By giving ‘ed’ at
   sql prompt.(this is what I mentioned as a second method to
   type/enter the program).
   in the sql editor you can do all the formatting/editing/file
   operations directly by selecting menu options provided by it.


   To execute the program which saved; do the following
   SQL> @ programname.sql (or)
   SQL> Run programname.sql
   Then press ‘\’ key and enter.
DBMS                                                             LAB MANUAL   2
   To save the day`s session ;do the following
   SQL>commit;

   This how we can write, edit and execute the sql command and
   programs.

   Always you have to save your programs in your own logins.

   Background Theory

   Oracle workgroup or server is the largest selling RDBMS product.it is
   estimated that the combined sales of both these oracle database product
   account for aroud 80% of the RDBMSsystems sold worldwide.
   These products are constantly undergoing change and evolving. The
   natural language of this RDBMS product is ANSI SQL,PL/SQL a superset
   of ANSI SQL.oracle 8i and 9i also under stand SQLJ.

   Oracle corp has also incorporated a full-fledged java virtual machine
   into its database engine.since both executable share the same memory
   space the JVM can communicate With the database engine with ease
   and has direct access to oracle tables and their data.

   SQL is structure query language.SQL contains different data types those
   are
   1. char(size)
   2. varchar(size)
   3. varchar2(size)
   4. date
   5. number(p,s)            //** P-PRECISION          S-SCALE **//
   6. number(size)
   7. raw(size)
   8. raw/long raw(size)


   Different types of commands in SQL:

   A).DDL commands: - To create a database objects
   B).DML commands: - To manipulate data of a database objects
   C).DQL command: - To retrieve the data from a database.
   D).DCL/DTL commands: - To control the data of a database…
DBMS                                                           LAB MANUAL   3
   DDL commands:

   1. The Create Table Command: - it defines each column of the table
   uniquely. Each column has minimum of three attributes, a name , data
   type and size.

   Syntax:

   Create table <table name> (<col1> <datatype>(<size>),<col2>
   <datatype><size>));

   Ex:
   create table emp(empno number(4) primary key, ename char(10));

   2. Modifying the structure of tables.
   a)add new columns

   Syntax:

   Alter table <tablename> add(<new col><datatype(size),<new
   col>datatype(size));

   Ex:
   alter table emp add(sal number(7,2));

   3. Dropping a column from a table.

   Syntax:
   Alter table <tablename> drop column <col>;

   Ex:
   alter table emp drop column sal;

   4. Modifying existing columns.

   Syntax:
   Alter table <tablename> modify(<col><newdatatype>(<newsize>));

   Ex:
   alter table emp modify(ename varchar2(15));
DBMS                                                               LAB MANUAL   4
   5. Renaming the tables

   Syntax:

   Rename <oldtable> to <new table>;

   Ex:
   rename emp to emp1;

   6. truncating the tables.

   Syntax:

   Truncate table <tablename>;

   Ex:

   trunc table emp1;

   7. Destroying tables.

   Syntax:

   Drop table <tablename>;

   Ex:

   drop table emp;

   DML commands:

   8. Inserting Data into Tables: - once a table is created the most
   natural thing to do is load this table with data to be manipulated later.

   Syntax 1:

   insert into <tablename> (<col1>,<col2>…..<col n>) values(<val 1>,
   <val 2>…….<val n>);

   Syntax 2:

   insert into <tablename> values(&<col1>,&<col2>……,&<col n>);
DBMS                                                                 LAB MANUAL   5
   Syntax 3:

   insert into <tablename> values(<val 1>,<val 2>…….,<val n>);

   Ex 1:

   Insert into skc (sname,rollno,class,dob,fee_paid)
   values(‘sri’,’104B’,’cse’,’27-feb-05’,10000.00);

   Ex 2:

   insert into skc values(&sname,&roll no,&class);
   enter sname:’sri’
   enter roll no:’104B’
   enter class:’cse’
   1 row created.

   Ex 3:

   insert into skc values(‘sri’,’104B’,cse’,’27-feb-05’,10000.00);

   9. Delete operations.

   a) remove all rows

   Syntax:

   delete from <tablename>;

   b) removal of a specified row/s

   Syntax:

   delete from <tablename> where <condition>;

   10. Updating the contents of a table.

   a) updating all rows

   Syntax:
   Update <tablename> set <col>=<exp>,<col>=<exp>;
DBMS                                                        LAB MANUAL   6
   b) updating seleted records.

   Syntax:
   Update <tablename> set <col>=<exp>,<col>=<exp>
   where <condition>;


   11. Types of data constrains.

   a) not null constraint at column level.

   Syntax:

   <col><datatype>(size)not null

   b) unique constraint

   Syntax:

   Unique constraint at column level.
   <col><datatype>(size)unique;

   c) unique constraint at table level:

   Syntax:

   Create table
   tablename(col=format,col=format,unique(<col1>,<col2>);

   d) primary key constraint at column level

   Syntax:

   <col><datatype>(size)primary key;

   e) primary key constraint at table level.

   Syntax:

   Create table tablename(col=format,col=format
   primary key(col1>,<col2>);
DBMS                                                              LAB MANUAL   7
   f) foreign key constraint at column level.

   Syntax:

   <col><datatype>(size>) references <tablename>[<col>];

   g) foreign key constraint at table level

   Syntax:

   foreign key(<col>[,<col>]) references
   <tablename>[(<col>,<col>)

   h) check constraint

   check constraint constraint at column level.

   Syntax: <col><datatype>(size) check(<logical expression>)

   i) check constraint constraint at table level.

   Syntax: check(<logical expression>)

   DQL Commands:

   12. Viewing data in the tables: - once data has been inserted into a
   table, the next most logical operation would be to view what has been
   inserted.

   a) all rows and all columns

   Syntax:
   Select <col> to <col n> from tablename;
   Select * from tablename;

   13. Filtering table data: - while viewing data from a table, it is rare
   that all the data from table will be required each time. Hence, sql must
   give us a method of filtering out data that is not required data.

   a) Selected columns and all rows:
   Syntax:
   select <col1>,<col2> from <tablename>;
DBMS                                                           LAB MANUAL   8
   b) selected rows and all columns:
   Syntax:
   select * from <tablename> where <condition>;

   c) selected columns and selected rows
   Syntax:
   select <col1>,<col2> from <tablename> where<condition>;

   14. Sorting data in a table.

   Syntax:
   Select * from <tablename> order by <col1>,<col2> <[sortorder]>;

   DCL commands:

   Oracle provides extensive feature in order to safeguard information
   stored in its tables from unauthoraised viewing and damage.The rights
   that allow the user of some or all oracle resources on the server are
   called privileges.

   a) Grant privileges using the GRANT statement
   The grant statement provides various types of access to database
   objects such as tables,views and sequences and so on.

   Syntax:
   GRANT <object privileges>
   ON <objectname>
   TO<username>
   [WITH GRANT OPTION];

   b) Reoke permissions using the REVOKE statement:
   The REVOKE statement is used to deny the Grant given on an object.

   Syntax:
   REVOKE<object privilege>
   ON
   FROM<user name>;
DBMS                                                                                 LAB MANUAL   9
                                WEEK-1
   CREATING,ALTERING AND DROPPING TABLES AND INSERTING ROWS INTO
   A TABLE (USE CONSTRAINTS WHILE CREATING TABLES) EXAMPLES USING
                   SELECT COMMAND .

   EXAMPLE 1:
   CREATING A STUDENT RELATION TABLE WITH ALL DATATYPES:

   SQL> create table student252(
   sid number(5),
   sname varchar(20),
   sbranch char(5),
   dob date,
   spercent number(3,2));

   Table created.
   RELATIONAL SCHEMA FOR STUDENT RELATION :
   SQL> desc student252;
   Name                                      Null?                  Type
   ----------------------------------------- -------- ----------------------------
   SID                                                            NUMBER(5)
   SNAME                                                           VARCHAR2(20)
   SBRANCH                                                         CHAR(5)
   DOB                                                            DATE
   SPERCENT                                                       NUMBER(5,2)

   INSERT THE RECORDS INTO STUDENT RELATION:

   METHOD 1:
   SQL>Insert into
   Student252(sid,sname,sbranch,dob,spercent) values(104,‘sri’,,’cse’,’27-
   feb-05’,70);
   1 row created.

   METHOD 2:
   SQL>Insert into
   Student252 values(104,‘sri’,,’cse’,’27-feb-05’,70);
   1 row created.
DBMS                                                                                   LAB MANUAL   10
   METHOD 3:
   SQL>Insert into
   Student252(sid,sname,sbranch,dob,spercent)
   values(&sid, &sname,&sbranch,&dob,&spercent);
   1 row created.

   METHOD 4:
   SQL>Insert into
   Student252(sid,sname,sbranch,dob,spercent)
   values(&sid, ‘&sname’,’&sbranch’,’&dob’,&spercent);
   1 row created.

   QUERY THE TABLE VALUES:

   ALL ROWS AND ALL COLUMNS:

   SQL> select * from student252;
   SID SNAME                  SBRANCH DOB                          SPERCENT
   ------ --------------- --------------------- --------------- --------------------
   130 ravi                             it       30-1-95                    60
   131       teja                      cse 21-07-87                         55
   129 kiran                           mech 12-05-92                         60
   104 sri                              cse 30-07-90                        70
   133 sajith                          eee 12-06-89                         55
   137 ram                             ece 07-07-85                         40




                            WEEK 2 (cont…1)
   1) Creation, altering and dropping tables and inserting rows into a table
   (use constraints while creating tables) examples using SELECT command.
   MODIFYING THE STRUCTURE OF TABLE

   ADDING A NEW COLUMN

   SQL> ALTER TABLE Emp252
        ADD (age number(3),
              phno number(10));

   Table altered.
DBMS                                                             LAB MANUAL   11
   MODIFYING EXISTING COLUMN

   SQL> ALTER TABLE Emp252
        MODIFY (phno varchar(20));

   Table altered.

   DROPING A COLUMN

   SQL> ALTER TABLE Emp252
        DROP COLUMN phno;

   Table altered.

   QUERY FOR THE TABLE VALUES
   SQL> SELECT *
         FROM Emp252;

   ENO ENAME                ESAL DEPTNO                   AGE
   ----- -------------------- ---------- ---------- ----------
   30 ravi                     51000          3
   31 teja                     31000          2
   29 kiran                     31200          1
   45 allen                     41000          3
   33 sajith                    51000          4
   46 geetha                     11000          4
   90 veena                      16000          3
   85 pragna                     61000          1
   84 harsha                     91000          3
   40 sanjeev                     1500         13

   10 rows selected.

   UPDATING ENTIRE COLUMN

   SQL> UPDATE Emp252
        SET age=18;

   10 rows updated.

   QUERY THE TABLE VALUES
   SQL> SELECT *
         FROM Emp252;
DBMS                                                             LAB MANUAL   12
   ENO ENAME                 ESAL DEPTNO AGE
   ----- -------------------- ---------- ---------- ----------
   30 ravi                     51000          3       18
   31 teja                    31000           2        18
   29 kiran                     31200          1       18
   45 allen                    41000          3        18
   33 sajith                   51000          4        18
   46 geetha                     11000          4       18
   90 veena                      16000          3       18
   85 pragna                     61000          1       18
   84 harsha                     91000          3       18
   40 sanjeev                     1500         13        18

   10 rows selected.

   RENAMING THE TABLE:

   SQL> RENAME Emp252
        TO Emp1252;

   Table renamed.

   SELECTING THE TABLE VALUES
   SQL> SELECT *
         FROM Emp1252;


   Example 3

   CREATING A DEPARTMENT RELATION TABLE

   CREATING A DEPARTMENT TABLE

   SQL> CREATE TABLE Dept252(
             dname VARCHAR(10),
             dno CHAR(5),
             dloc VARCHAR(25));

   Table created.
DBMS                                                                                 LAB MANUAL   13
   DESCRIBE A STUDENT TABLE

   SQL> desc Dept252;
   Name                                      Null? Type
   ----------------------------------------- -------- ----------------------------
   DNAME                                               VARCHAR2(10)
   DNO                                                 CHAR(5)
   DLOC                                                VARCHAR2(25)

   DROPING THE TABLE
   SQL> DROP TABLE Dept252;

   Table dropped.

                                               WEEK 3 (cont…1)

       1) Creation, altering and dropping tables and inserting rows into a table
       (use constraints while creating tables) examples using SELECT
       command.

   CREATING A TABLE WITH KEY CONSTRAINTS

   Example 1

   CREATING A TABLE WITH ‘UNIQUE ‘, ‘NOT NULL’, ‘CHECK’ AND
   ‘DEFAULT’ CONSTRAINT:

   SQL> CREATE TABLE emp252
       (eid NUMBER(5) UNIQUE,
        ename VARCHAR(10) DEFAULT(‘UNKNOWN’),
        age NUMBER(3) NOT NULL,
        esal NUMBER(7) CHECK(esal > 1000));

   Table created.

   INSERTING RECORDS INTO TABLE:

   SQL> INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal);
   Enter value for eid: 1
   Enter value for ename: 'ravi'
   Enter value for age: 18
   Enter value for esal: 10000
DBMS                                                      LAB MANUAL   14
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (1, 'ravi', 18, 10000)

   1 row created.

   SQL> /
   Enter value for eid: 2
   Enter value for ename: 'teja'
   Enter value for age: 18
   Enter value for esal: 20000
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (2, 'teja', 18, 20000)

   1 row created.

   SQL> /
   Enter value for eid: 3
   Enter value for ename: 'kiran'
   Enter value for age: 19
   Enter value for esal: 25000
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (3, 'kiran', 19, 25000)

   1 row created.

   SQL> /
   Enter value for eid: 4
   Enter value for ename: 'srinivas'
   Enter value for age: 19
   Enter value for esal: 30000
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (4, 'srinivas', 19, 30000)

   1 row created.

   SQL> /
   Enter value for eid: 1
   Enter value for ename: 'alan'
   Enter value for age: 19
   Enter value for esal: 29000
DBMS                                                      LAB MANUAL   15
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (1, 'alan', 19, 29000)
   INSERT INTO emp252 VALUES (1, 'alan', 19, 29000)
   [SHOWING AN ERROR WHILE VIOLATING UNIQUE KEY
   CONSTRAINT]
   *
   ERROR at line 1:
   ORA-00001: unique constraint (SYSTEM.SYS_C003875) violated

   SQL> /
   Enter value for eid: 7
   Enter value for ename: 'dravid'
   Enter value for age: null
   Enter value for esal: 100000
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (7, 'dravid', null, 100000)
   INSERT INTO emp252 VALUES (7, 'dravid', null, 100000)
    [SHOWING AN ERROR AS NOT NULL KEY CONSTRAINT IS
   VIOLATED]                       *
   ERROR at line 1:
   ORA-01400: cannot insert NULL into ("SYSTEM"."EMP230"."AGE")


   SQL> /
   Enter value for eid: 8
   Enter value for ename: 'sachin'
   Enter value for age: 35
   Enter value for esal: 100
   old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
   new 1: INSERT INTO emp252 VALUES (8, 'sachin', 35, 100)
   INSERT INTO emp252 VALUES (8, 'sachin', 35, 100)
   *
   [NOT ALLOWING AS IT VOILATES CHECK CONSTRAINT FOR esal >
   1000 VALUE]
   ERROR at line 1:
   ORA-02290: check constraint (SYSTEM.SYS_C003874) violated
DBMS                                                                                 LAB MANUAL   16
   Example 2

   CREATING A TABLE WITH ‘PRIMARY KEY’ CONSTRAINT:

   SQL> CREATE TABLE mdept252
       (dno NUMBER(5),
        dname CHAR(10),
        dloc VARCHAR(10),
        PRIMARY KEY (dno));

   Table created.

   SQL> desc mdept252;
   Name                                Null? Type
   ----------------------------------------- -------- ----------------------------
   DNO                                 NOT NULL NUMBER(5)
   DNAME                                         CHAR(10)
   DLOC                                        VARCHAR2(10)

   INSERTING RECORDS INTO MASTER DEPARTMENT TABLE:

   SQL> INSERT INTO mdept252 VALUES (&dno, &dname, &dloc);
   Enter value for dno: 1
   Enter value for dname: 'ravi'
   Enter value for dloc: 'hyd'
   old 1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
   new 1: INSERT INTO mdept252 VALUES (1, 'ravi', 'hyd')

   1 row created.

   SQL> /
   Enter value for dno: 1
   Enter value for dname: 'teja'
   Enter value for dloc: 'sec'
   old 1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
   new 1: INSERT INTO mdept252 VALUES (1, 'teja', 'sec')
   INSERT INTO mdept252 VALUES (1, 'teja', 'sec')
   *
   ERROR at line 1:
   ORA-00001: unique constraint (SYSTEM.SYS_C003876) violated
DBMS                                                       LAB MANUAL   17



   SQL> /
   Enter value for dno: null
   Enter value for dname: 'sajithulhuq'
   Enter value for dloc: 'kmm'
   old 1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
   new 1: INSERT INTO mdept252 VALUES (null, 'sajithulhuq', 'kmm')
   INSERT INTO mdept252 VALUES (null, 'sajithulhuq', 'kmm')
                     *
   ERROR at line 1:
   ORA-01400: cannot insert NULL into ("SYSTEM"."MDEPT230"."DNO")

   ADDING A PRIMARY KEY TO AN EXISTING TABLE:

   SQL> ALTER TABLE student252 ADD PRIMARY KEY (sid);

   Table altered.

   SQL> ALTER TABLE emp252 ADD PRIMARY KEY (eid);
   ALTER TABLE emp252 ADD PRIMARY KEY (eid)
               *
   [ GIVING AN ERROR AS ONE TABLE CAN HAVE A SINGLE PRIMARY
   KEY AT COLUMN LAVEL]
   ERROR at line 1:
   ORA-02261: such unique or primary key already exists in the table

   Example 3

   CREATING A TABLE WITH ‘FORIEGN KEY’ CONSTRAINT:

   SQL> CREATE TABLE detailemp252
       (eid NUMBER(5) REFERENCES mdept230 (dno),
        ename VARCHAR(10),
        esal NUMBER(7));

   Table created.
DBMS                                                            LAB MANUAL   18
   INSERING RECORDS INTO DETAIL EMPLOYEE TABLE:

   SQL> INSERT INTO detailemp252 VALUES (2, 'ravi', 50000);
   INSERT INTO detailemp252 VALUES (2, 'ravi', 50000)
   *
   ERROR at line 1:
   ORA-02291: integrity constraint (SYSTEM.SYS_C003877) violated - parent
   key not
   found


   SQL> INSERT INTO detailemp252 VALUES (1, 'teja', 60000);

   1 row created.

   SQL> DELETE FROM mdept252 where dno=1;
   DELETE FROM mdept252 where dno=1
   *
   ERROR at line 1:
   ORA-02292: integrity constraint (SYSTEM.SYS_C003877) violated - child
   record
   found

   SQL> SELECT * FROM detailemp252;

       EID ENAME                 ESAL
   ---------- ---------- ----------
          1 teja          60000

   SQL> SELECT * FROM mdept252;

        DNO DNAME DLOC
   ---------- ---------- ----------
          1 ravi      hyd
DBMS                                                      LAB MANUAL   19
   Exercise

   CREATING A CUSTOMER TABLE USING CONSTRAINTS :

   SQL> CREATE TABLE cust252
       (cnum NUMBER(5),
        cname VARCHAR(10),
        state VARCHAR(10) DEFAULT ('ap'),
        phno NUMBER(5),
        CONSTRAINT cnum_pkkey PRIMARY KEY (cnum));

   Table created.

   SQL> INSERT INTO cust252 VALUES (&cnum, &cname, &state, &phno);
   Enter value for cnum: 1
   Enter value for cname: 'ravi'
   Enter value for state: 'bihar'
   Enter value for phno: 001
   old 2: (&cnum, &cname, &state, &phno)
   new 2: (1, 'ravi', 'bihar', 001)

   1 row created.

   SQL> /
   Enter value for cnum: 2
   Enter value for cname: 'teja'
   Enter value for state: 'up'
   Enter value for phno: 007
   old 2: (&cnum, &cname, &state, &phno)
   new 2: (2, 'teja', 'up', 007)

   1 row created.

   SQL> /
   Enter value for cnum: 2
   Enter value for cname: 'yama'
   Enter value for state: 'ap'
   Enter value for phno: 006
   old 2: (&cnum, &cname, &state, &phno)
   new 2: (2, 'yama', 'ap', 006)
DBMS                                                        LAB MANUAL   20
   INSERT INTO cust252 VALUES
   *
   ERROR at line 1:
   ORA-00001: unique constraint (SYSTEM.CNUM_PKKEY) violated


   SQL> /
   Enter value for cnum: 4
   Enter value for cname: 'huu'
   Enter value for state: 'ap'
   Enter value for phno: 101
   old 2: (&cnum, &cname, &state, &phno)
   new 2: (4, 'huu', 'ap', 101)

   1 row created.

   SQL> SELECT * FROM cust252;

       CNUM CNAME STATE                          PHNO
   ---------- ---------- ---------- ----------
          1 ravi      bihar             1
          2 teja      up               7
          4 huu        ap             101

   CREATING AN ITEM TABLE USING CONSTRAINTS:

   SQL> CREATE TABLE itm252
       (ino NUMBER(3),
        iname VARCHAR(10),
        iprice NUMBER(4,3),
        qtyonhand VARCHAR(5),
        CONSTRAINT itm252_ino_pkkey PRIMARY KEY (ino),
        CONSTRAINT itm230_qtyoh_chk CHECK (qtyonhand>1));

   Table created.

   SQL> INSERT INTO itm252 VALUES (&ino, &iname, &iprice,
   &qtyonhand);
   Enter value for ino: 1
   Enter value for iname: 'rubber'
DBMS                                                          LAB MANUAL   21
   Enter value for iprice: 3.50
   Enter value for qtyonhand: 3
   old 1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice,
   &qtyonhand)
   new 1: INSERT INTO itm252 VALUES (1, 'rubber', 3.50, 3)

   1 row created.

   SQL> /
   Enter value for ino: 1
   Enter value for iname: 'pencil'
   Enter value for iprice: 1.00
   Enter value for qtyonhand: 3
   old 1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice,
   &qtyonhand)
   new 1: INSERT INTO itm252 VALUES (1, 'pencil', 1.00, 3)
   INSERT INTO itm252 VALUES (1, 'pencil', 1.00, 3)
   *
   ERROR at line 1:
   ORA-00001: unique constraint (SYSTEM.ITM230_INO_PKKEY) violated




   SQL> /
   Enter value for ino: 2
   Enter value for iname: 'powder'
   Enter value for iprice: 3.00
   Enter value for qtyonhand: 0
   old 1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice,
   &qtyonhand)
   new 1: INSERT INTO itm252 VALUES (2, 'powder', 3.00, 0)
   INSERT INTO itm252 VALUES (2, 'powder', 3.00, 0)
   *
   ERROR at line 1:
   ORA-02290: check constraint (SYSTEM.ITM230_QTYOH_CHK) violated
DBMS                                                                                 LAB MANUAL   22
    1* CREATE TABLE invoice252(ivnno NUMBER(5), itemno NUMBER(5),
   qty NOT NULL, CONSTRAINT invoice252_ivnno_pkkey PRIMARY
   KEY(ivnno), CONSTRAINT FOREIGN KEY(itemno) REFERENCES
   cust252)

   SQL> desc cust252;
   Name                                Null? Type
   ----------------------------------------- -------- ----------------------------
   CNUM                                        NOT NULL NUMBER(5)
   CNAME                                        VARCHAR2(10)
   STATE                                        VARCHAR2(10)
   PHNO                                         NUMBER(5)

   CREATING A INVOICE TABLE USING CONSTRAINTS:

   SQL> CREATE TABLE invoice252
         (ivnno NUMBER(5),
          itemno NUMBER(5),
          qty NUMBER(5) NOT NULL,
          CONSTRAINT invoice252_ivnno_pkkey PRIMARY KEY (ivnno),
          CONSTRAINT fk_inv252 FOREIGN KEY (itemno) REFERENCES
   cust252 (cnum))

   Table created.

   SQL> CREATE TABLE invitm252
         (invno NUMBER(5),
          itmno NUMBER(5),
          qty NUMBER(5) NOT NULL,
          CONSTRAINT invitm252_invno_itmno_pkkey PRIMARY KEY
   (invno, itmno));

   Table created.
DBMS                                                                                    LAB MANUAL         23
                                                 WEEK 4

          2) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT
          EXISTS, UNIQUE, INTERSECT, Constraints.
          Example: select the rollno and name of the student who secured 4th rank
          in the class


   TABLE DEFINITIONS

   SQL> CREATE TABLE Customer (
             cust_no     NUMBER(4)                PRIMARY KEY,
             last_name   VARCHAR2(20),
             first_name  VARCJHAR2(20)            NOT NULL,
             address1    VARCHAR2(20),
             address2    VARCHAR2(20),
             city        VARCHAR2(3),
             state       VARCHAR2(20),
             pin         VARCHAR2(6),
             birth_date  DATE,
             status      VARCHAR2(1),
                     CHECH (status IN (‘V’, ‘I’, ‘A’))
        );

   Table created.

   Insert the following data:

   1 row created.

   CUST     LAST      FIRST     ADDRESS1        ADDRESS2    CITY   STATE       PIN      BIRTH     STATUS
   NO       NAME      NAME                                                              DATE
   1001     UDUPI     RAJ       UPENDRABAUG     NEAR        UDPP   KARNARATA   576101   12-       A
                                                KALPANA                                 DEC-62
   1002     KUMAR     RAJ                                                                         A
   1003     BAHADUR   RAJ       SHANTHI VILLA   NEAR        UDP    KARNATAKA   576101   1-AUG-    V
                                                MALLIKA                                 70
   1004     SIMON     FELIX     M-J-56          ALTOBETIM   PJM    GOA         403002   12-FEB-   A
                                                                                        71
   1005     KUTTY     RAJAN     A1 TRADERS      NEAR RLY    KNR    KERALA      67001    9-JUN-    A
                                                STATION                                 71
   1006     PAI       SHILPA    12/4B           POLICE      MNG    KARNATAKA   574154   11-       I
                                                QUARTERS                                DEC-70
   1007     JAIN      RAKSHIT   BOSCO           R.K PLAZA   BNG    KARNATAKA   576201   1-JAN-    A
                                                                                        71
DBMS                                                                                      LAB MANUAL          24
   QUERIES

       1) To list all the fields from the table Customer.
                  SEELCT *
                  FROM Customer;

       2) To list the first name, last name.
                   SELECT first_name, last_name
                   FROM Customer;

       3) To list the first name and last name of persons in Karnataka.
                   SELECT first_name, last_name
                   FROM Customer
                   WHERE state = ‘KARNATAKA’;

       4) To list all the columns for invalid persons.
                  SELECT *
                  FROM Customer
                  WHERE status = ‘I’;

       5) To list the names of active customers.
                   SELECT first_name, last_name
                   FROM Customer
                   WHERE status = ‘A’;

       6) To list the name and address using concatenation.
                   SELECT first_name || ‘ ‘ || last_name, address1 || ‘,’ || address2 || ‘,’ || city || ‘,’
                   || state || ‘-‘ || pin
                   FROM Customer;

       7) To select records where the pin code has not been entered.
                 SELECT *
                 FROM Customer
                 WHERE pin IS NULL;

       8) To select the single occurrence of any value from the table.
                 SELECT DISTINCT state
                 FROM Customer;

       9) To select rows of valid customers from Karnataka.
                 SELECT *
                 FROM Customer
                 WHERE state = ‘KARNATAKA’
                 AND status = ‘V’;
DBMS                                                                                  LAB MANUAL   25
       10) To select rows of customers from Karnataka or Kerala.
                  SELECT *
                  FROM Customer
                  WHERE state = KARNATAKA’
                  OR state = ‘KERALA’;

       11) To sort the customer data in the alphabetic order of state.
                  SELECT state, first_name, last_name, pin
                  FROM Customer
                  ORDER BY state;

       12) To sort in the descending order.
                   SELECT state, first_name, last_name, pin
                   FROM Customer
                   ORDER BY state DESC;

       13) To sort the customer data, state wise and within state by the last name.
                   SELECT state, first_name, last_name, pin
                   FROM Customer
                   ORDER BY state, last_name;

       14) To retrieve records of Karnataka customers who are valid.
                   SELECT *
                   FROM Customer
                   WHERE UPPER(state) = ‘KARNATAKA’
                   AND UPPER(status) = ‘V’;

       15) To retrieve records of Karnataka/Kerala customers.
                   SELECT *
                   FROM Customer
                   WHERE UPPER(state) = ‘KARNATAKA’
                   OR UPPER(state) = ‘KERALA’;

       16) To retrieve records of Karnataka/Kerala customers who are active.
                   SELECT *
                   FROM Customer
                   WHERE (UPPER(state) = ‘KARNATAKA’
                    OR UPPER(state) = ‘KERALA’)
                   AND UPPER(status) = ‘A’;

       17) To retrieve records of Karnataka customers with pin code 576101.
                   SELECT *
                   FROM Customer
                   WHERE LOWER(state) = ‘karnataka’
                   AND pin = ‘576101’;
DBMS                                                                           LAB MANUAL    26
       18) To retrieve rows where the state name begins with K and followed by any other
           character.
                   SELECT first_name, last_name, state
                   FROM Customer
                   WHERE state LIKE ‘K%’;

       19) To retrieve rows where the first name contains the word RAJ embedded in it.
                  SELECT first_name, last_name, state
                  FROM Customer
                  WHERE first_name LIKE ‘%RAJ%’;

       20) To retrieve rows where the address2 contains the word UDUPI or UDIPI in which
          the 3rd character may be anything.
                  SELECT first_name, last_name, state
                  FROM Customer
                  WHERE address2 LIKE ‘UD_PI’;

       21) To retrieve rows where the cust_no has data representing any value between 1003
          and 1005, both numbers included.
                  SELECT *
                  FROM Customer
                  WHERE cust_no BETWEEN 1003 AND 1005;

       22) To retrieve rows of persons born after 9-JAN-70 and before 1-AUG-96.
                  SELECT *
                  FROM Customer
                  WHERE birth_date BETWEEN ’10-JAN-70’ AND ’31-JUL-96’;

       23) To retrieve rows where the city has data which is equal to UDP or MNG or BNG or
          PJM or MAR.
                  SELECT *
                  FROM Customer
                  WHERE city IN (‘UDP’, ‘MNG’, ‘BNG’, ‘PJM’, ‘MAR’);




   TABLE DEFINITIONS

   SQL> CREATE TABLE Emp (
                  emp_no     NUMBER,
                  emp_name   VARCHAR(20),
                  join_date  DATE,
                  join_basic NUMBER(7, 2),
                  PRIMARY KEY (emp_no)
          );
   Table created.
DBMS                                                                            LAB MANUAL   27

   Insert the following data:

   EMP NO      EMP NAME          JOIN DATE   JOIN BASIC
   1001        Subhas bose       01-JUN-96   3000
   1002        Nadeem shah       01-JUN-96   2500
   1003        Charles babbage   01-JUN-96   3000
   1004        Shreyas kumar     01-JUL-96   2500
   1005        George boole      01-JUL-96   2800


   SQL> CREATE TABLE Salary (
                emp_no NUMBER,
                basic         NUMBER(7, 2),
                commission    NUMBER(7, 2),
                deduction     NUMBER(7, 2),
                salary_date   DATE,
                FOREIGN KEY (emp_no) REFERENCES Emp
         );

   Table created.

   Insert the following data:

   EMP NO      BASIC     COMMISSION    DEDUCTION     SALARY
                                                     DATE
   1001        3000      200           250           30-JUN-96
   1002        2500      120           200           30-JUN-96
   1003        3000      500           290           30-JUN-96
   1004        2500      200           300           30-JUN-96
   1005        2800      100           250           30-JUN-96
   1001        3000      200           250           31-JUL-96
   1002        2500      120           200           31-JUL-96
   1003        3000      500           290           31-JUL-96
   1004        2500      200           300           31-JUL-96
   1005        2800      100           150           31-JUL-96


   QUERIES

       1) To sum the salary of each employee.
                SELECT emp_no, SUM(basic)
                FROM salary
                GROUP BY emp_no;

       2) To sum the salary of each employee and sort it on the sum of basic.
                SELECT emp_no, SUM(basic)
                FROM salary
                GROUP BY emp_no
                ORDER BY SUM(basic);
DBMS                                                                            LAB MANUAL   28
       3) To sum the salary of each employee and sort it in descending order on the sum of
          basic.
                 SELECT emp_no, SUM(basic)
                 FROM salary
                 GROUP BY emp_no
                 ORDER BY SUM(basic) DESC;

       4) To sum the salary of each employee and sort it in descending order on the sum of
          basic. Display name also
                  SELECT s.emp_no, e.emp_name, SUM(s.basic)
                  FROM salary s, emp e
                  WHERE s.emp_no = e.emp_no
                  GROUP BY s.emp_no, e.emp_no
                  ORDER BY SUM(s.basic) DESC;

       5) To group the data by average salary of each employee.
                 SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
                 FROM salary s, emp e
                 WHERE s.emp_no = e.emp_no
                 GROUP BY s.emp_no, e.emp_no
                 ORDER BY AVG(s.basic);

       6) To group the basic by month.
                 SELECT TO_CHAR(salary_date, ‘MONTH’) “MONTH”, SUM(basic)
                 “TOTAL BASIC”
                 FROM salary
                 GROUP BY TO_CHAR(salary_date, ‘MONTH’);

       7) To group the data by average salary of each employee and display where average
          basic is more than 2000..
                  SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
                  FROM salary s, emp e
                  WHERE s.emp_no = e.emp_no
                  GROUP BY s.emp_no, e.emp_no
                  HAVING AVG(s.basic) >= 2000
                  ORDER BY AVG(s.basic);


   SUBQUERIES

       8) To list the employees who earn less than the average salary.
                   SELECT *
                   FROM salary
                   WHERE basic < (SELECT AVG(basic)
                                  FROM salary);
DBMS                                                                              LAB MANUAL         29
       9) To list the employees whose deduction is 150.
                    SELECT *
                    FROM salary
                    WHERE emp_no IN (SELECT emp_no
                                        FROM salary
                                        WHERE deduction = 150);
       10) To list the names of employees and salary details, whose basic is less than the average
           salary.
                    SELECT s.*, e.emp_name
                    FROM salary s, emp e
                    WHERE s.emp_no = e.emp_no
                    AND s.basic < (SELECT AVG(basic)
                                    FROM salary);



                                              WEEK 5


       2) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT
          EXISTS, UNIQUE, INTERSECT, Constraints. Example: select the
          rollno and name of the student who secured 4th rank in the class.

       3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and
          MIN), GROUP BY, HAVING and Creation and Dropping of Views.

       4) Queries using Conversions, functions (to_char, to_num, and
          to_date), string function (Conactenation, lpad, rpad, ltrim, rtrim,
          lower, upper, initcap, length, substr, and instr), date functions
          (sysdate, next_day, add_months, last_day, months_between, least,
          greatest, trunk, round, to_char, to_date).

   TABLE DEFINITIONS

   SQL> CREATE TABLE Emp (
             emp_no     NUMBER,
             emp_name   VARCHAR(20),
             join_date  DATE,
             join_basic NUMBER(7, 2),
             PRIMARY KEY (emp_no)
        );

   Table created.
DBMS                                                                                LAB MANUAL   30
   Insert the following data:

   EMP NO     EMP NAME          JOIN DATE   JOIN BASIC
   1001       Subhas bose       01-JUN-96   3000
   1002       Nadeem shah       01-JUN-96   2500
   1003       Charles babbage   01-JUN-96   3000
   1004       Shreyas kumar     01-JUL-96   2500
   1005       George boole      01-JUL-96   2800



   SQL> CREATE TABLE Salary (
             emp_no      NUMBER,
             basic       NUMBER(7, 2),
             commission NUMBER(7, 2),
             deduction   NUMBER(7, 2),
             salary_date DATE,
             FOREIGN KEY (emp_no) REFERENCES Emp
        );

   Table created.

   Insert the following data:

   EMP NO     BASIC    COMMISSION     DEDUCTION     SALARY
                                                    DATE
   1001       3000     200            250           30-JUN-96
   1002       2500     120            200           30-JUN-96
   1003       3000     500            290           30-JUN-96
   1004       2500     200            300           30-JUN-96
   1005       2800     100            250           30-JUN-96
   1001       3000     200            250           31-JUL-96
   1002       2500     120            200           31-JUL-96
   1003       3000     500            290           31-JUL-96
   1004       2500     200            300           31-JUL-96
   1005       2800     100            150           31-JUL-96




   QUERIES

          11) To sum the salary of each employee.
                    SELECT emp_no, SUM(basic)
                    FROM salary
                    GROUP BY emp_no;

          12) To sum the salary of each employee and sort it on the sum of basic.
                    SELECT emp_no, SUM(basic)
                    FROM salary
                    GROUP BY emp_no
                    ORDER BY SUM(basic);
DBMS                                                                             LAB MANUAL   31
       13) To sum the salary of each employee and sort it in descending order on the sum of
           basic.
                  SELECT emp_no, SUM(basic)
                  FROM salary
                  GROUP BY emp_no
                  ORDER BY SUM(basic) DESC;

       14) To sum the salary of each employee and sort it in descending order on the sum of
           basic. Display name also
                   SELECT s.emp_no, e.emp_name, SUM(s.basic)
                   FROM salary s, emp e
                   WHERE s.emp_no = e.emp_no
                   GROUP BY s.emp_no, e.emp_name
                   ORDER BY SUM(s.basic) DESC;

       15) To group the data by average salary of each employee.
                  SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
                  FROM salary s, emp e
                  WHERE s.emp_no = e.emp_no
                  GROUP BY s.emp_no, e.emp_no
                  ORDER BY AVG(s.basic);

       16) To group the basic by month.
                  SELECT TO_CHAR(salary_date, ‘MONTH’) “MONTH”, SUM(basic)
                  “TOTAL BASIC”
                  FROM salary
                  GROUP BY TO_CHAR(salary_date, ‘MONTH’);

       17) To group the data by average salary of each employee and display where average
           basic is more than 2000..
                   SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
                   FROM salary s, emp e
                   WHERE s.emp_no = e.emp_no
                   GROUP BY s.emp_no, e.emp_no
                   HAVING AVG(s.basic) >= 2000
                   ORDER BY AVG(s.basic);


   SUBQUERIES

       18) To list the employees who earn less than the average salary.
                    SELECT *
                    FROM salary
                    WHERE basic < (SELECT AVG(basic)
                                   FROM salary);
DBMS                                                                               LAB MANUAL        32
       19) To list the employees whose deduction is 150.
                    SELECT *
                    FROM salary
                    WHERE emp_no IN (SELECT emp_no
                                        FROM salary
                                        WHERE deduction = 150);
       20) To list the names of employees and salary details, whose basic is less than the average
           salary.
                    SELECT s.*, e.emp_name
                    FROM salary s, emp e
                    WHERE s.emp_no = e.emp_no
                    AND s.basic < (SELECT AVG(basic)
                                    FROM salary);



                                              WEEK 6

       2) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT
          EXISTS, UNIQUE, INTERSECT, Constraints. Example: select the
          rollno and name of the student who secured 4th rank in the class.

       3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and
          MIN), GROUP BY, HAVING and Creation and Dropping of Views.

       4) Queries using Conversions, functions (to_char, to_num, and
          to_date), string function (Conactenation, lpad, rpad, ltrim, rtrim,
          lower, upper, initcap, length, substr, and instr), date functions
          (sysdate, next_day, add_months, last_day, months_between, least,
          greatest, trunk, round, to_char, to_date).



   TABLE DEFINITIONS

   Branch Schema <branch-name, branch-city, assets>
   Customer Schema <customer-name, customer-street, customer-city>
   Loan Schema <loan-number, branch-name, amount>
   Borrower Schema <customer-name, loan-number>
   Account Scheme <account-number, branch-name, balance>
   Depositor Scheme <customer-name, account-number>
DBMS                                                  LAB MANUAL   33
   BRANCH TABLE

   Branch Name      Branch City       Assets
   Brighton         Brooklyn          7100000
   Downtown         Brooklyn          9000000
   Mianus           Horseneck         400000
   North Town       Rye               3700000
   Perryridge       Horseneck         1700000
   Pownal           Bennington        300000
   Redwood          Palo Alto         2100000
   Round Hill       Horseneck         800000


   CUSTOMER TABLE

   Customer Name    Customer Street   Customer City
   Adams            Spring            Pittsfield
   Brooks           Senator           Brooklyn
   Curry            North             Rye
   Glenn            Sand Hill         Woodside
   Green            Walnut            Stamford
   Hayes            Main              Harrison
   Johnson          Alma              Palo Alto
   Jones            Main              Harrison
   Lindsay          Park              Pittsfield
   Smith            North             Rye
   Turner           Putnam            Stamford
   Williams         Nassau            Princeton

   LOAN TABLE

   Loan Number      Branch Name       Amount
   L-11             Round Hill        900
   L-14             Downtown          1500
   L-15             Perryridge        1500
   L-16             Perryridge        1300
   L-17             Downtown          1000
   L-23             Redwood           2000
   L-93             Mianus            500
DBMS                                                                     LAB MANUAL   34
   BORROWER TABLE

   Customer Name                  Loan Number
   Adams                          l-16
   Curry                          L-93
   Hayes                          L-15
   Jackson                        L-14
   Jones                          L-17
   Smith                          L-11
   Smith                          L-23
   Williams                       L-17

   ACCOUNT TABLE

   Account Number                 Branch Name                  Balance
   A-101                          Downtown                     500
   A-102                          Perryridge                   400
   A-201                          Brighton                     900
   A-215                          Mianus                       700
   A-217                          Brighton                     750
   A-222                          Redwood                      700
   A-305                          Round Hill                   350

   DEPOSITOR TABLE

   Customer Name                                   Account Number
   Hayes                                           A102
   Johnson                                         A-101
   Johnson                                         A-201
   Jones                                           A-217
   Lindsay                                         A-222
   Smith                                           A-215
   Turner                                          A-305

   QUERIES

   1) To list all the fields from the table Customer.

   SELECT branch_name
   FROM Loan;

   2) To list rows after eliminating duplicates.

   SELECT distinct branch_name
   FROM Loan;
DBMS                                                                     LAB MANUAL     35
   3) To explicitly list rows, including duplicates.

   SELECT all branch_name
   FROM Loan;

   4) To list fields after applying arithmetic operations.

   SELECT loan_number, branch_name, amount *100
   FROM Loan;

   5) Find all loan numbers for loans made at the Perryridge branch with loan amounts
   greater than Rs1200.

   SELECT loan_number
   FROM Loan
   WHERE branch_name = ‘Perryridge’
   AND amount > 1200;

   6) Find all loan numbers for loans with loan amounts between Rs90,000 and
   Rs100,000.

   SELECT loan_number
   FROM Loan
   WHERE amount BETWEEN 90000 AND 100000;

   Or

   SELECT loan_number
   FROM Loan
   WHERE amount <= 100000
   AND amount >= 90000;

   7) Find all loan numbers for loans with loan amounts not between Rs90,000 and
   Rs100,000.

   SELECT loan_number
   FROM Loan
   WHERE amount NOT BETWEEN 90000 AND 100000;

   8) For all customers who have a loan from the bank, find their names, loan numbers
   and loan amounts.

   SELECT customer_name, Borrower.loan_number, amount
   FROM Borrower, Loan
   WHERE Borrower.loan_number = Loan.loan_number;

   Or
DBMS                                                                        LAB MANUAL   36

   SELECT customer_name, Borrower.loan_number AS loan_id, amount
   FROM Borrower, Loan
   WHERE Borrower.loan_number = Loan.loan_number;

   9) Find the customer names, loan numbers and loan amounts for all loans at the
   Perryridge branch.

   SELECT customer_name, Borrower.loan_number, amount
   FROM Borrower, Loan
   WHERE Borrower.loan_number = Loan.loan_number
   AND branch_name = ‘Perryridge’;

   Or


   SELECT customer_name, T.loan_number, S.amount
   FROM Borrower AS T, Loan AS S
   WHERE T.loan_number = S.loan_number
   AND branch_name = ‘Perryridge’;

   10) Find the names of all branches that have assets greater than atleast one branch
   located in Brooklyn.

   SELECT DISTINCT T.branch_name
   FROM Branch as T, Branch as S
   WHERE T.assets > S.assets
   AND S.branch_city = ‘Brooklyn’;

   11) Find the names of all customers whose street address includes the substring
   ‘Main’.

   SELECT customer_name
   FROM Customer
   WHERE customer_street LIKE ‘%Main%’;

   12) To list in alphabetic order all customers who have a loan at the Perryridge
   branch.

   SELECT DISTINCT customer_name
   FROM Borrower B, Loan L
   WHERE B.loan_number = L.loan_number
   AND branch_name = ‘Perryridge’
   ORDER BY customer_name;
DBMS                                                                       LAB MANUAL   37
   13) To list the entire loan info in descending order of amont.

   SELECT *
   FROM Loan
   ORDER BY amount DESC, loan_number ASC;

   14) To find all customers having a loan, an account or both at the bank, without
   duplicates.

   (SELECT customer_name
   FROM Depositor)
   UNION
   (SELECT customer_name
   FROM Borrower);



   15) To find all customers having a loan, an account or both at the bank, with
   duplicates.

   (SELECT customer_name
   FROM Depositor)
   UNION ALL
   (SELECT customer_name
   FROM Borrower);

   16) To find all customers having both a loan and an account at the bank, without
   duplicates.

   (SELECT customer_name
   FROM Depositor)
   INTERSECT
   (SELECT customer_name
   FROM Borrower);

   17) To find all customers having a loan, an account or both at the bank, with
   duplicates.

   (SELECT customer_name
   FROM Depositor)
   INTERSECT ALL
   (SELECT customer_name
   FROM Borrower);
DBMS                                                                      LAB MANUAL    38
   18) To find all customers who have an account but no loan at the bank, without
   duplicates.

   (SELECT DISTINCT customer_name
   FROM Depositor)
   EXCEPT
   (SELECT customer_name
   FROM Borrower);

   19) To find all customers who have an account but no loan at the bank, with
   duplicates.

   (SELECT DISTINCT customer_name
   FROM Depositor)
   EXCEPT ALL
   (SELECT customer_name
   FROM Borrower);



   20) Find the average account balance at the Perryridge branch

   SELECT branch_name, AVG(balance)
   FROM Account
   WHERE branch_name = ‘Perryridge’;

   21) Find the average account balance at the each branch

   SELECT AVG(balance)
   FROM Account
   GROUP BY branch_name;

   22) Find the number of depositors for each branch .

   SELECT branch_name, COUNT(DISTINCT customer_name)
   FROM Depositor D, Account A
   WHERE D.account_number = A.account_number
   GROUP BY branch_name;

   23) Find the number of depositors for each branch where average account balance is
   more than Rs 1200.

   SELECT branch_name, COUNT(DISTINCT customer_name)
   FROM Depositor D, Account A
   WHERE D.account_number = A.account_number
   GROUP BY branch_name
   HAVING AVG(balance) > 1200;
DBMS                                                                       LAB MANUAL   39

   24) Find the average balance for all accounts.

   SELECT AVG(balance)
   FROM Account;

   25) Find the number of tuples in the customer relation.

   SELECT COUNT(*)
   FROM Customer;

   26) Find the average balance for each customer who lives in Harrision and has at
   least three accounts.

   SELECT D.customer_name, AVG(balance)
   FROM Depositor D, Account A, Customer C
   WHERE D.account_number = A.account_number
   AND D.customer_name = C.customer_name
   AND C.customer_city = ‘Harrison’
   GROUP BY D.customer_name
   HAVING COUNT(DISTINCT D.account_number) >= 3;

   27) Find all the loan number that appear in loan relation with null amount values.

   SELECT loan_number
   FROM Loan
   WHERE amount IS NULL;

   28) Find all customers who have both a loan and an account at the bank.

   SELECT customer_name
   FROM Borrower
   WHERE customer_street IN (SELECT customer_name
   FROM Depositor);

   29) Find all customers who have both an account and a loan at the Perryridge
   branch

   SELECT DISTINCT B.customer_name
   FROM Borrower B, Loan L
   WHERE B.loan_number L.loan_number
   AND branch_name = ‘Perryridge’
   AND (branch_name, customer_name) IN
   (SELECT branch_name, customer_name
   FROM Depositor D, Account A
   WHERE D.account_number = A.account_number);
DBMS                                                                       LAB MANUAL     40
   or

   SELECT customer_name
   FROM Borrower B
   WHERE EXISTS (SELECT *
   FROM Depositor D
   WHERE D.customer_name = B.customer_name);

   30) Find all customers who do not have a loan at the bank, but do not have an
   account the bank.

   SELECT DISTINCT customer_name
   FROM Borrower
   WHERE customer_name NOT IN
   (SELECT customer_name
   FROM Depositor);



   31) Find the names of customers who do have a loan at the bank, and whose names
   are neither Smith nor Jones.

   SELECT DISTINCT customer_name
   FROM Borrower
   WHERE customer_name NOT IN (‘Smith’, ‘Jones’);

   32) Find the names of all branches that have assets greater than those of at least
   one branch located in Brooklyn.

   SELECT DISTINCT T.branch_name
   FROM Branch AS T, Branch AS S
   WHERE T.assets > S.assets
   AND S.branch_city = ‘Brooklyn’;

   33) Find the names of all branches that have assets greater than that of each branch
   located in Brooklyn.

   SELECT branch_name
   FROM Account
   GROUP BY branch_name
   HAVING AVG(balance) >= ALL (SELECT AVG(balance)
   FROM Account
   GROUP BY branch_name);
DBMS                                                                      LAB MANUAL     41
   34) Find all customers who have an account at all the branches located in Brooklyn.

   SELECT DISTINCT S.customer_name
   FROM Depositor AS D
   WHERE NOT EXISTS ((SELECT branch_name
   FROM Branch
   WHERE branch_city = ‘Brroklyn)
   EXCEPT
   (SELECT R.branch_name
   FROM Depositor AS T, Account AS R
   WHERE T.account_number =
   R.account_number
   AND D.customer_name = t.customer_name));

   35) Find all customers who have at most one account at the Perryridge branch.

   SELECT T.customer_name
   FROM Depositor AS T
   WHERE UNIQUE (SELECT R.customer_name
   FROM Depositor AS R, Account AS A
   WHERE T.customer_name = R.customer_name
   AND R.account_number = A.account_number
   AND A.branch_name = ‘Perryridge’);

   36) Find all customers who have at least two accounts at the Perryridge branch.

   SELECT DISTINCT T.customer_name
   FROM Depositor AS T
   WHERE NOT UNIQUE (SELECT R.customer_name
   FROM Depositor AS R, Account AS A
   WHERE T.customer_name = R.customer_name
   AND R.account_number = A.account_number
   AND A.branch_name = ‘Perryridge’);

   37) Find the average account balance of those branches where the average account
   balance is greater than 1200.

   SELECT branch_name, avg_balance
   FROM (SELECT branch_name, AVG(balance)
   FROM Account
   GROUP BY branch_name)
   AS Branch_avg(branch_name, avg_balance)
   WHERE avg_balance > 1200;
DBMS                                                                       LAB MANUAL      42
   38) Find the maximum across all branches of the total balance at each branch.

   SELECT MAX(tot_balance)
   FROM (SELECT branch_name, SUM(balance)
   FROM Account
   GROUP BY branch_name)
   AS Branch_total(branch_name, tot_balance);

   39) Find the all customers who have an account but no loan at the bank.

   SELECT d-CN
   FROM (Depositor LEFT OUTER JOIN Borrower
   ON Depositor.customer_name = Borrower.customer_name)
   AS db1(d-CN, account_number, b-CN, loan_number)
   WHERE b-CN is null;

   40) Find the all customers who have either an account or a loan (but not both) at the
   bank.

   SELECT customer_name
   FROM (Depositor NATURAL FULL OUTER JOIN Borrower)
   WHERE account_number IS NULL
   OR loan_number IS NULL;


                                          WEEK 7

       5) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT
          EXISTS, UNIQUE, INTERSECT, Constraints. Example: select the
          rollno and name of the student who secured 4th rank in the class.

       6) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and
          MIN), GROUP BY, HAVING and Creation and Dropping of Views.

       7) Queries using Conversions, functions (to_char, to_num, and
          to_date), string function (Conactenation, lpad, rpad, ltrim, rtrim,
          lower, upper, initcap, length, substr, and instr), date functions
          (sysdate, next_day, add_months, last_day, months_between, least,
          greatest, trunk, round, to_char, to_date).
DBMS                                                           LAB MANUAL   43
   DUAL (ORACLE WORK TABLE):

       1) To display system date.
                 SELECT SYSDATE FROM DUAL;

       2) To display arithmetic calculations.
                 SELECT 2*2 FROM DUAL;

       3) To display the logged user.
                 SELECT USER FROM DUAL;

       4) To display system time.
                 SELECT TO_CHAR(SYSDATE, ‘HH:MI:SS’) FROM DUAL;

       5) To display current month.
                 SELECT TO_CHAR(SYSDATE, ‘MONTH’) FROM DUAL;

       6) To display system date in specified format.
                 SELECT TO_CHAR(SYSDATE, ‘DD/MM/YY’) FROM DUAL;

       7) To display system date in specified format.
                 SELECT TO_CHAR(SYSDATE, ‘MM’) FROM DUAL;

       8) To display date arithmetic.
                 SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

       9) To display date arithmetic.
                 SELECT LAST_DAY(SYSDATE) FROM DUAL;

       10) To display date arithmetic.
                  SELECT MONTHS_BETWEEN(SYSDATE, ’01-APR-09’) FROM DUAL;

       11) To display date arithmetic.
                  SELECT NEXT_DAY(SYSDATE, ‘MON’) FROM DUAL;


   GROUP FUNCTIONS:

       12) To display average basic salary of the employees.
                  SELECT SUM(basic) FROM salary;

       13) To display minimum basic salary of the employees.
                  SELECT MIN(basic) FROM salary;

       14) To display maximum basic salary of the employees.
                  SELECT MAX(basic) FROM salary;
DBMS                                                                            LAB MANUAL   44

       15) To display sum of basic salaries of all the employees.
                  SELECT SUM(basic) FROM salary;

       16) To display the number of records in salary table.
                  SELECT COUNT(*) FROM salary;

   STRING FUNCTIONS:
       17) To display a field value after left padding.
                 SELECT LPAD('PAGE-1', 10, '*') FROM DUAL;

       18) To display a field value after left padding.
                 SELECT RPAD('PAGE-1', 10, '*') FROM DUAL;

       19) To display a field value after converting to lower case.
                 SELECT LOWER(‘A’) FROM DUAL;

       20) To display a field value after converting to upper case.
                 SELECT LOWER(‘a’) FROM DUAL;

       21) To display a field value after converting to initial capital case.
                 SELECT INITCAP(‘HOW ARE YOU?’) FROM DUAL;

       22) To display a substring of a field value.
                 SELECT SUBSTR(‘CSE2A’, 4, 2) FROM DUAL;

       23) To display the length of a field value.
                 SELECT LENGTH(’HOW LONG AM I?’) FROM DUAL;

       24) To display a field value after trimming the right side.
                 SELECT RTRIM(‘CSE2A’, ‘2A’) FROM DUAL;

       25) To display a field value after trimming the left side.
                  SELECT LTRIM(‘CSE2A’, ‘CSE’) FROM DUAL;
DBMS                                                                LAB MANUAL   45
                                 WEEK 8 (PL/SQL)


       6) (i) Creation of simple PL/SQL program which includes declaration
          section, executable section and exception handling section ( ex:
          Student marks can be selected from the table and printed for those
          who secured first class and an exception can be raised if no records
          were found).
          (ii) Insert data into student table and use COMMIT, ROLLBACK
          and SAVEPOINT in SQL block.

       7) Develop a program that includes the features NESTED IF, CASE
          and CASE expression. The program can be extended using the
          NULLIF and COALESCE functions.

       8) Program development using WHILE LOOPS, numeric FOR
          LOOPS, nested loops using ERROR handling, BUILT IN exceptions,
          USER defined exceptions, RAISE APPLICATION ERROR.

       9) Program development using creation of procedure, passing
          parameters IN and OUT procedures.

       10)      Program development using creation of stored function, invoke
          functions in SQL statements and write complex functions.

       11)      Program development using creation of package specification,
          package bodies, private objects, package variables and cursors and
          calling stored packages.

       12)    Develop programs using features of parameters in a CURSOR,
          FOR UPDATE CURSOR, WHERE CURRENT of clause and
          CURSOR variables.
DBMS                                                               LAB MANUAL   46

   Syntax to write a sql program


   Declare
   <declaration stmts>
   Begin
   <executable stmts>
   [exception <exceptional stmts>]----- optional
   End;
   /---end of buffer

   Example: 1
   Create a file DBFOR.SQL, to execute the FOR loop and display the variable.

   At SQL Prompt type, ed dbfor to open notepad and type the below program:
   Program
   declare
   cnt number;
   begin
   dbms_output.put_line('This is a demo of FOR loop ');
   for cnt in 1..5 loop
                    dbms_output.put_line('loop number ' || cnt);
   end loop;
   end;
   /
   set serveroutput off

   Save the file and at SQL prompt run as:
   Execution
   SQL>set serveroutput on
   SQL> start dbfor     (press enter) OR
   SQL> @dbfor



   OUTPUT:-
   This ia a demo of FOR loop

   loop number 1

   loop number 2
DBMS                                                                        LAB MANUAL   47
   loop number 3

   loop number 4

   loop number 5




   PS:
   For syntax:
   For <var> in <start_num> .. <endnum> loop
          <statement(s);>
   End loop;

   Example: 2
   Create a file DBREVFOR.SQL, to execute the REVERSE FOR loop and
   display the variable.

   Program
   begin
            dbms_ouput.put_line(‘This is a demo of REVERSE FOR loop’);
            for cnt in reverse 1..10 loop
                    if mod(cnt, 2) = 0 then
                            dbms_output.put_line(‘loop counter ‘ || cnt);
                    end if;
            end loop;
   end;
   /



   OUTPUT:-
   This is a demo of REVERSE FOR loop

   loop   counter   10
   loop   counter   8
   loop   counter   6
   loop   counter   4
   loop   counter   2

   PS:

   Reverse For syntax:
   For <var> in reverse <start_num> .. <endnum> loop
          <statement(s);>
DBMS                                                              LAB MANUAL   48
   End loop;

   Other forms of if syntax are:
   If <condition> then
           <action(s);>
   End if;

   If <condition> then
           <action(s);>
   Else
           <action(s);>
   End if;

   If <condition> then
           <action(s);>
   Elsif <condition> then
           <action(s);>
   else
           <action(s);>
   End if;

   Example: 3
   Create a file DBLOOP.SQL, to execute the LOOP loop and display the
   variable.

   Program
   set serveroutput on
   declare
           cnt number(2) := 0;
   begin
           dbms_ouput.put_line(‘This is a demo of LOOP loop’);
           loop
                  cnt := cnt + 1;
                  exit when cnt > 10;
                  dbms_output.put_line(‘loop counter ‘ || cnt);
           end loop;
   end;
   /
   set serveroutput off
DBMS                                                                             LAB MANUAL      49

   OUTPUT:-
   This is the demo of LOOP loop

   loop counter 1

   loop counter 2

   loop   counter   3
   loop   counter   4
   loop   counter   5
   loop   counter   6
   loop   counter   7
   loop   counter   8
   loop   counter   9
   loop   counter   10



   PS:
   Loop syntax:
   loop
          <statement(s);>
          Exit when <condition>;
   End loop;

   Example: 4
   Create a file DBWHILE.SQL, to execute the WHILE loop and display the
   variable.

   Program
   set serveroutput on
   declare
           cnt number(2) := 1;
   begin
           dbms_ouput.put_line(‘This is a demo of WHILE loop’);
           while cnt <= 10 loop dbms_output.put_line(‘loop counter: ‘ || to_char(cnt, ‘999’));
                  cnt := cnt + 1;
           end loop;
   end;
   /
   set serveroutput off
DBMS                                                            LAB MANUAL   50

   OUTPUT:-
   This is a demo of WHILE loop

   loop counter : 1

   loop counter : 2

   loop   counter   :   3
   loop   counter   :   4
   loop   counter   :   5
   loop   counter   :   6
   loop   counter   :   7
   loop   counter   :   8
   loop   counter   :   9
   loop   counter   :   10

   PS:
   while syntax:
   while <condition> loop
          <statement(s);>
   End loop;

   Example: 4
   Write a program EMPDATA.SQL, to retrieve the employee details of an
   employee whose number is input by the user .

   Program
   -- PROGRAM TO RETRIEVE EMP DETAILS
   set serveroutput on

   prompt Enter Employee Number:
   accept n
   declare
           dname emp.emp_name%type;
           dbasic emp.emp_basic%type;
           ddesig emp.desig%type;
   begin
           select emp_name, basic, design
           into dname, dbasic, ddesig
           from emp
           where emp_no = &n;
           dbms_ouput.put_line(‘Employee Details:);
           dbms_output.put_line(‘Name:        ‘ || dname);
           dbms_output.put_line(‘Basic:       ‘ || dbasic);
           dbms_output.put_line(‘Designation: ‘ || ddesig);
DBMS                                                                LAB MANUAL   51
   end;
   /



   OUTPUT:-
   enter employee number:
   13
   old 9:where eno =&n;

   new 9:where eno=13;

   employee details

   Name:allen

   basic:9500

   desig:mech



   set serveroutput off

   PS:
   Similarly you can use other SQL statements in the PL/SQL block


   Exercises:

       1) Write a PL/SQL code, EX_INVNO.SQL, block for
       inverting a number using all forms of loops.

       ANSWER:-

       declare

       n number(20):=123;

       s number(13):=0;

       d number(3):=1;
DBMS                                                   LAB MANUAL   52
       r number(3):=10;

       begin

       dbms_output.put_line('the number is :' || n);

       while n>0 loop

       d:=mod(n,10);

       s:=(s*r)+d;

       n:=n/r;

       end loop;

       dbms_output.put_line('inverted values' || s);

       end;

       /

       OUTPUT:-

       the number is:123

       inverted value is:321


       2) Write a PL/SQL code, EX_SUMNO.SQL that prints
       the sum of ‘n’ natural numbers.

       ANSWER:-

       prompt enter number:

       accept number n

       declare

       isum number(2):=0;
DBMS                                                   LAB MANUAL   53
       i number;

       n number:=&n;

       begin

       for i in 1..n loop

       isum:=isum+i;

       end loop;

       dbms_output.put_line('sum is ' || isum);

       end;

       /

       OUTPUT:-

       enter the number:7

       sum is 28




       3) Write a PL/SQL code, EX_AREA.SQL, of block to
       calculate the area of the circle for the values of radius
       varying from 3 to 7. Store the radius and the
       corresponding values of calculated area in the table
       AREA_VALUES.

       ANSWER:-

       set serveroutput on

       declare

       area number(5);
DBMS                                               LAB MANUAL   54
       rad number(3);

       pi number(4):=3.14;

       begin

       for rad in 3..7 loop

       area:=pi*rad*rad;

       dbms_output.put_line('area is' || area);

       insert into area_values values(area,rad);

       end loop;

       end;

       /

       OUTPUT:-

   area    is   :27
   area    is   :48
   area    is   :75
   area    is   :108
   area    is   :147



   SQL>select * from area_values;
   area rad
   ____ ____
   27    3
   48    4
   75    5
   108 6
   147 7
DBMS                                                              LAB MANUAL    55
                                WEEK 9 (PL/SQL)

       13)       (i) Creation of simple PL/SQL program which includes
          declaration section, executable section and exception handling
          section ( ex: Student marks can be selected from the table and
          printed for those who secured first class and an exception can be
          raised if no records were found).
          (ii) Insert data into student table and use COMMIT, ROLLBACK
          and SAVEPOINT in SQL block.

       14)     Develop a program that includes the features NESTED IF,
          CASE and CASE expression. The program can be extended using
          the NULLIF and COALESCE functions.

       15)    Program development using WHILE LOOPS, numeric FOR
          LOOPS, nested loops using ERROR handling, BUILT IN exceptions,
          USER defined exceptions, RAISE APPLICATION ERROR.

       16)     Program development using creation of procedure, passing
          parameters IN and OUT procedures.

       17)      Program development using creation of stored function, invoke
          functions in SQL statements and write complex functions.

       18)      Program development using creation of package specification,
          package bodies, private objects, package variables and cursors and
          calling stored packages.

       19)    Develop programs using features of parameters in a CURSOR,
          FOR UPDATE CURSOR, WHERE CURRENT of clause and
          CURSOR variables.
DBMS                                                                  LAB MANUAL   56
   Example: 1
   Create a file (NEWINS.SQL), to insert into a new table, NEWEMP, the
   record of any employee whose number is input by the user.
   1. Create the table NEWEMP <emp_no, emp_name, join_date, basic).
   2. Open an editor and type the following program.

   Program
         prompt Enter Employee Number:
         accept userno number
         declare
                 dno    number(4);
                 dname varchar2(30);
                 ddate date;
                 dbasic number(10);
         begin
                 select emp_no, emp_name, join_date, basic
                 into dno, dname, ddate, dbasic
                 from emp
                 where emp_no = &userno;

                if sql%rowcount > 0
                then
                        insert into newemp
                        values (dno, dname, ddate, dbaisc);
                end if;
          end;
          /
   3. Save the file as NEWINS
   4. Execute the program as
          SQL> start newins



   Example: 2
   Create a file (NEWINS2.SQL), to insert into a new table, NEWEMP, the
   record of any employee whose number is input by the user. Also display on
   the screen the employee details and to handle errors like user entering a
   number which does not exist in the table.

   Program
         prompt Enter Employee Number:
         accept userno number
         declare
                 dno    number(4);
DBMS                                                                                LAB MANUAL   57
                   dname varchar2(30);
                   ddate date;
                   dbasic number(10);

         begin
                   select   emp_no, emp_name, join_date, basic
                   into     dno, dname, ddate, dbasic
                   from     emp
                   where    emp_no = &userno;

                   if sql%rowcount > 0
                   then
                          insert into  newemp
                          values       (dno, dname, ddate, dbasic);

                            dbms_output.put_line(‘Record inserted into NEWEMP’);
                            dbms_output.put_line(DNO || ‘ ‘ || DNAME || ‘ ‘ || DDATE || ‘ ‘ ||
         DBASIC);
              end if;

         exception
                when no_data_found then
                      dbms_output.put_line (‘Record ‘ || &userno || ‘ does not exist’);
         end;
         /


   Example: 3
   Create a file (CALCTAX.SQL), to calculate tax for a specific employee and
   display name and tax.

   Program
         prompt Enter Employee Number:
         accept userno number

         declare
                   tot_basic       number(10, 2);
                   tax             number(10, 2);
                   name            varchar2(30);
         begin
                   select   emp_name, basic
                   into     name, tot_basic
                   from     emp
                   where    emp_no = &userno;

                   if tot_basic = 0 or tot_basic is null
DBMS                                                                           LAB MANUAL   58
                then
                         dbms_output.put_line(‘NO BASIC’);
                elsif tot_basic <= 2000
                then
                         tax := tot_basic * .02;
                         dbms_output.put_line(NAME || ‘ TOTAL BASIC: ‘ || TOT_BASIC);
                         dbms_output.put_line(NAME || ‘ TOTAL TAX: ‘ || TAX);
                else
                         tax := tot_basic * .04;
                         dbms_output.put_line(NAME || ‘ TOTAL BASIC: ‘ || TOT_BASIC);
                         dbms_output.put_line(NAME || ‘ TOTAL TAX: ‘ || TAX);
                end if;

         exception
                when no_data_found then
                      dbms_output.put_line (‘Record ‘ || &userno || ‘ does not exist’);
         end;
         /

   PS:
   EXECPTIONS
   When a program is executed certain errors are automatically recognized and
   certain error situations must be recognized by the program itself. Errors in
   general are referred to as Exceptions.
   Exceptions can be either System defined or User defined.
   Certain system exceptions raise the following flags:
   CURSOR_ALREADY_OPEN – Displayed when the user tries to open a cursor
   that is already open
   DUP_VAL_ON_INDEX – when user tries to insert a duplicate value into a
   unique column
   INVALID_CURSOR – when user references an invalid cursor or attempts an
   illegal cursor operation
   INVALID_NUMBER – when user tries to use something other than a number
   where one is called for
   LOGIN_DENIED – when connect request for user has been denied
   NO_DATA_FOUND – this flag becomes TRUE when SQL select statement
   failed to retrieve any rows
   NOT_LOGGED_ON – user is not connected to ORACLE
   PROGRAM_ERROR – user hits a PL/SQL internal error
   STORAGE_ERROR – user hits a PL/SQL memory error
   TIMEOUT_ON_RESOURCE – user has reached timeout while waiting for an
   Oracle resource
DBMS                                                                  LAB MANUAL     59
   TRANSACTION_BACKED_OUT – a remote server has rolled back the
   transaction
   TOO_MANY_ROWS – the flag becomes TRUE when SQL select statement
   retrieves more than one row and it was supposed to retrieve only 1 row
   VALUE_ERROR – user encounters an arithmetic, conversion, truncation or
   constraint error
   ZERO_DIVIDE – flag becomes TRUE if SQL select statement tries to divide a
   number by 0
   OTHERS – this flag is used to catch any error situations not coded by the
   programmer
   In the exception section and must appear last in the exception section

   User defined exceptions must be declared in the declare section with the
   reserved word, EXCEPTION.

   Syntax for user defined exception:
         <exception-name> EXCEPTION;

   This exception can be brought into action by the command,
         RAISE <exception-name>
   When the exception is raised, processing control is passed to the EXCEPTION
   section of the PL/SQL block.
   The code for the exception must be defined in the EXCEPTION section of the
   PL/SQL block.
         WHEN <exception-name> THEN
                <action>;

   Exercises:

       1) Write a PL/SQL code block that will accept an account number from
          the user and debit an amount of RS2000 from the account. If the
          account has a minimum balance of 500 after amount is debited the
          process should set a freeze on the account by setting the status to F.
          (use table schema Accounts (acno, balance, status)
       2) Write a PL/SQL block of code to achieve the following:
          If the price of the product is >4000 then change the price to 4000. The
          price change is to be recorded in the old price table along with product
          number and date on which the price was last changed.
          (use table schemas Product(pno, price) and Old_Price(pno,
          date_of_change, oldprice)
DBMS                                                              LAB MANUAL    60
                     WEEK 10 (PL/SQL)

       20)       (i) Creation of simple PL/SQL program which includes
          declaration section, executable section and exception handling
          section ( ex: Student marks can be selected from the table and
          printed for those who secured first class and an exception can be
          raised if no records were found).
          (ii) Insert data into student table and use COMMIT, ROLLBACK
          and SAVEPOINT in SQL block.

       21)     Develop a program that includes the features NESTED IF,
          CASE and CASE expression. The program can be extended using
          the NULLIF and COALESCE functions.

       22)    Program development using WHILE LOOPS, numeric FOR
          LOOPS, nested loops using ERROR handling, BUILT IN exceptions,
          USER defined exceptions, RAISE APPLICATION ERROR.

       23)     Program development using creation of procedure, passing
          parameters IN and OUT procedures.

       24)      Program development using creation of stored function, invoke
          functions in SQL statements and write complex functions.

       25)      Program development using creation of package specification,
          package bodies, private objects, package variables and cursors and
          calling stored packages.

       26)    Develop programs using features of parameters in a CURSOR,
          FOR UPDATE CURSOR, WHERE CURRENT of clause and
          CURSOR variables.
DBMS                                                                    LAB MANUAL      61
   Example: 1
   Create a PL/SQL program using cursors, to retrieve first tuple from the
   department relation.
   (use table dept(dno, dname, loc))

   Program
           declare
                 vdno dept.deptno%type;
                 vdname        dept.dname%type;
                 vloc dept.loc%type;
                 cursor        c1 is select * from dept;
                 or // cursor c1 is select * from dept where rowno = 1;
           begin
                 open c1;
                 fetch c1
                 into vdno,vdname,vloc;
                 dbms_output.put_line('vdno = ' ||vdno|| ' vdname = '||vdname||' vloc
   = '||vloc);
                 close c1;
   end;
   /

   PS:
   Cursors are used when the SQL select statement is expected to return more than
   1 row.
   A cursor must be declared and its definition contains a query and is defined in
   the DECLARE section of the program.
   A cursor must be opened before processing and closed after processing.
   (Similar to how files are opened and closed in a C program).

   Syntax to define a cursor:
         CURSOR <CURSOR-NAME> IS <SELECT STATEMENT>

   Syntax to open the cursor:
         OPEN <CURSOR-NAME>

   Syntax to store data in the cursor:
         FETCH <CURSOR-NAME> INTO <VAR1>, <VAR2>, <VAR3>….
                OR
         FETCH <CURSOR-NAME> INTO <RECORD-NAME>
DBMS                                                                      LAB MANUAL     62


   Syntax to close the cursor:
         CLOSE <CURSOR-NAME>


   Example: 2
   Create a PL/SQL program using cursors, to retrieve each tuple from the
   department relation.
   (use table dept(dno, dname, loc))

   Program
          declare
                vdept dept%rowtype;
                cursor          c1 is select * from dept;
          begin
                for vdept in c1 loop
                        dbms_output.put_line('vdno = ' ||vdept.deptno|| ' vdname =
   '||vdept.dname||' vloc = '||vdept.loc);
          end loop;

   end;
   /

   PS:
   The cursor for loop can be used to process multiple records. The advantage of
   cursor for loop is that the loop itself will open the cursor, read the records into
   the cursor from the table until end of file and close the cursor.

   Syntax for cursor FOR LOOP:
         FOR <VARIABLE> IN <CURSOR-NAME> LOOP
                <STATEMENTs>
         END LOOP;
DBMS                                                                    LAB MANUAL   63
   Example: 3
   Create a PL/SQL program using cursors, to display the number, name, salary
   of the three highest paid employees.
   (use table emp(empno, ename,sal))

   Program
        declare
              no emp.empno%type;
              name emp.ename%type;
              salary emp.sal%type;
              cursor c1 is select empno, ename, sal from emp order by sal desc;

         begin
                 open c1;
                 loop
                        fetch c1 into no,name,salary;
                        exit when c1 %notfound;
                        exit when c1 %rowcount >3;
                        dbms_output.put_line(no||name||salary);
                 end loop;
                 close c1;
         end;
         /

   PS:
   Cursors Attributes:
   There are 4 cursor attributes used to provide information on the status of a
   cursor.
   %NOTFOUND – To determine if a row was retrieved
                       Used after FETCH
                       NOTFOUND is TRUE if row is not retrieved
                       NOTFOUND is FALSE if row is retrieved
   %FOUND –                  To determine if a row was retrieved.
                       Used after FETCH
                       FOUND is TRUE if row is retrieved
                       FOUND is FALSE if row is not retrieved
   %ROWCOUNT – To determine the number of rows retrieved
                       ROWCOUNT is 0 when cursor is opened
                       ROWCOUNT returns the number of rows retrieved
   %ISOPEN –                 To determine the cursor is open
DBMS                                                                LAB MANUAL   64
                       ISOPEN is TRUE if a cursor is open
                       ISOPEN is FALSE if a cursor is not open

   Example: 4
   Create a PL/SQL program using cursors, to delete the employees whose
   salary is more than 3000.

   Program
        declare
              vrec emp%rowtype;
        cursor c1 is select * from emp where sal>3000 for update;
        begin
              open c1;
              loop
                      fetch c1 into vrec;
                      exit when c1 %notfound;
                      delete from emp where current of c1;
                      dbms_output.put_line('Record deleted');
              end loop;
              close c1;
        end;
        /

   PS:
   In order to DELETE or UPDATE rows, the cursor must be defined with the
   FOR UPDATE clause.

   Example: 5
   Create a PL/SQL program using cursors, to update the salary of each
   employee by the avg salary if their salary is less than avg salary.

   Program

         declare
               vrec emp%rowtype;
               avgsal number(10,2);
         cursor c1 is select * from emp for update;

         begin
                 select avg(sal) into avgsal from emp;
DBMS                                                                    LAB MANUAL   65
                for vrec in c1 loop
                       if vrec.sal < avgsal then
                              vrec.sal := avgsal;
                              update emp set sal = vrec.sal where current of c1;
                              dbms_output.put_line('Record updated');
                       end if;
                end loop;
         end;
         /
   PS:
   Variable Attributes:
   %TYPE - is used in PL/SQL to declare a variable to be of the same type as a
   previously declared variable or to be of the same type as a column in a table.
          TOTBASIC SALARY.BASIC%TYPE;
   will declare TOTBASIC of the same type as BASIC column from the table
   SALARY.

   %ROWTYPE – declares a variable which is actually a record which has the
   same structure as a row from a table.
          SALREC SALARY%ROWTYPE;
   will declare SALREC as a record variable equivalent to the row from the table
   SALARY.

    Example: 6
   Create a PL/SQL program using cursors, to insert into a table, NEWEMP,
   the record of ALL MANAGERS. Also DISPLAY on the screen the NO,
   NAME, JOIN_DATE. Handle any user defined exceptions.
   (use table emp(emp_no, emp_name, join_date, desig))

   Program

         set serveroutput on
         declare
                ctr   number(2) := 2;
                dno number(4);
                dname        varchar2(30);
                ddate date;

                cursor cur_mgr is
                      select emp_no, emp_name, join_date
DBMS                                                                 LAB MANUAL   66
                     from emp
                     where upper(desig) = ‘MGR’;

               no_manager_found         exception;

       begin
               open cur_mgr;
               loop
                     fetch cur_mgr
                     into dno, dname, ddate;

                     exit when cur_mgr%notfound;
                     ctr := ctr + 1;

                  dbms_output.put_line(ctr || ‘Record inserted into
               NEWEMP’);
                  dbms_output.put_line(dno || ‘ ‘ || dname || ‘ ‘ ddate);

                     insert into new emp
                     values (dno, dname, ddate);

               end loop;

               if cur_mgr%rowcount = 0
               then
                      close cur_mgr;
                      raise no_manager_found;
               end if;

               dbms_output.put_line(‘TOTAL number of records’ || ctr);
               close cur_mgr;

               exception
                     when no_manager_found then
                          dbms_output.put_line(‘NO RECORS FOUND’);

       end;
       /
DBMS                                                               LAB MANUAL   67
   Exercises:
     1) Create a PL/SQL program using cursors, to insert into a table,
        NEWEMP, for any designation input by the user from the keyboard.
        Handle any user defined exceptions.

       2) Code a program to calculate Tax for any employee whose number is
          input from the keyboard. Display appropriate error message if data
          does not exist in the table.

                                 WEEK 11 (PL/SQL)

       27)      (i) Creation of simple PL/SQL program which includes
          declaration section, executable section and exception handling
          section ( ex: Student marks can be selected from the table and
          printed for those who secured first class and an exception can be
          raised if no records were found).
                (ii) Insert data into student table and use COMMIT,
          ROLLBACK and SAVEPOINT in SQL block.

       28)     Develop a program that includes the features NESTED IF,
          CASE and CASE expression. The program can be extended using
          the NULLIF and COALESCE functions.

       29)    Program development using WHILE LOOPS, numeric FOR
          LOOPS, nested loops using ERROR handling, BUILT IN exceptions,
          USER defined exceptions, RAISE APPLICATION ERROR.

       30)     Program development using creation of procedure, passing
          parameters IN and OUT procedures.

       31)      Program development using creation of stored function, invoke
          functions in SQL statements and write complex functions.

       32)      Program development using creation of package specification,
          package bodies, private objects, package variables and cursors and
          calling stored packages.

       33)    Develop programs using features of parameters in a CURSOR,
          FOR UPDATE CURSOR, WHERE CURRENT of clause and
          CURSOR variables.
DBMS                                                                LAB MANUAL   68
   Example: 1
   Code a procedure to calculate the sales made to a particular customer.
   { create table trn (itmid number(10),
                cstid number(10),
                trnqty number(10));

   create table itmmast (itmid       number(10),
                      itmprice       number(10,2));

   create table cstmast (   cstid number(10),
                       name varchar2(30));}

   Step 1: Open the editor
   Step 2: Type the code below in a file named, TOTSALES.

   Program
   CREATE OR REPLACE PROCEDURE TOTSALES
        (CID IN CSTMAST.CSTID%TYPE, SAL OUT NUMBER)
   IS
        id    TRN.ITMID%TYPE;
        qty TRN.TRNQTY%TYPE;
        price ITMMAST.ITMPRICE%TYPE;
        sales NUMBER(10, 2) := 0;

           cursor cur_tr is
                 select trn.itmid, trnqty, itmprice
                 from trn, itmmast
                 where trn.cstid = cid
                 and trn.itmid = itmmast.itmid;
   begin
       open cur_tr;
       loop
             fetch cur_tr into id, qty, price;
             if cur_tr%rowcount = 0
             then
                    raise_application_error(-20020, ‘ERREOR!!!THERE IS NO
   DATA’);
             end if;
             exit when cur_tr%notfound;
             sales := sales + qty * price;
DBMS                                                                  LAB MANUAL      69
          end loop;

          close cur_tr;
          sal := sales;
   end;
   /

   Step 3: Save the TOTSALES.SQL file.
   Step 4: Return to SQL Prompt and compile as
   SQL> start TOTSALES; (press enter)
   Step 5: On the screen you will get the message Procedure created. If you have
   errors type
   SQL> show errors
   Step 6: To execute the procedure at SQL prompt type
   SQL> variable sl number
   SQL> execute totsales(2001, :sl)
   SQL> print sl

   PS:
   Procedural Objects

   Groups of SQL and PL/SQL statements can be stored in the database. The code
   stored once in the database can be used by multiple applications. Since the code
   is in the database, which is in the server, processing is faster.

   Procedures and functions are also referred to as sub-programs as they can take
   parameters and be invoked.

   Various types of procedural objects are: Procedures, Functions, Packages.

   Procedures:
   Procedures are sub-programs, which will perform an action and functions are
   subprograms that are generally coded to compute some value.
   The clients execute the procedure or function and the processing is done in the
   server.
   Procedures can receive and return values from and to the caller.
   Communication is passed to a procedure through a parameter and
   communication is passed out of a procedure through a parameter.
   When calling a procedure, the parameters passed can be declared to be IN, OUT
   or IN OUT.
DBMS                                                                    LAB MANUAL     70
   The IN parameter is used to pass values to the procedure being called. It
   behaves like a constant inside the procedure, i.e., cannot be assigned values
   inside the procedure.
   The OUT parameter is used to pass values out of a procedure to the caller of the
   procedure. It behaves like a uninitialized variable inside the procedure.
   The IN OUT parameter is used to pass values to the procedure being called and
   it is used to pass values to the caller of the procedure. The IN OUT variable
   behaves like a regular variable inside the procedure.

   Functions:
   Functions are also a collection of SQL and PL/SQL code which can return a
   value to the caller.
   Unlike procedures, functions can return a value to the caller. This value is
   returned through the use of the RETURN keyword within the function. A
   function can return a single value to the caller. Functions do not allow the OUT
   and IN OUT arguments.

   Packages:
   Packages are groups of procedures, functions, variables and SQL statements in
   a single unit.
   It consists of the package definition/specification and package body.
   A package specification consists of the list of functions, procedures, variables,
   constants, cursors and exceptions that will be available to users of the package.
   A package body consists of the PL/SQL blocks and specifications for all of the
   public objects listed in the package specification. It may also include code that
   is run every time the package is invoked, regardless of the part of the package
   that is executed.
   The name of the package body should be the same as the name of the package
   specification.

   To delete procedural objects:
   SQL> drop procedure <procedure-name>
   SQL> drop function <function-name>
   SQL> drop package<package-name>
DBMS                                                                     LAB MANUAL   71
   Example: 2
   Code a function to return the square of a given number.

   Step 1: Open the editor
   Step 2: Type the code below in a file named, SQR.

   Program
   CREATE OR REPLACE FUNCTION SQR
        (NO NUMBER)
   RETURN NUMBER
   IS
   BEGIN
        return no*no;
   END;
   /

   Step 3: To test the function:
            a. At SQL prompt type:
                SQL> select sqr(10) from dual;
            b. At SQL prompt, type the following
                SQL> variable sq number
                SQL> execute :sq := sqr(10)
            c. In the editor, type the following
                set serveroutput on
                begin
                        dbms_output.put_line(‘Square of 10 is ‘ || sqr(10));
                end;
                /
                set serveroutput off
DBMS                                                               LAB MANUAL   72
   Example: 3
   Code a function to return the net salary of a given employee.

   Program
   CREATE OR REPLACE FUNCTION NETSAL
        (id in salary.emp_no%type)
   RETURN NUMBER
   IS
        netsal salary.basic%type;
   BEGIN
        select sum(basic) + sum(commission) – sum(deduction)
        into netsal
        from salary
        where emp_no = id;
        return (netsal);
   end;
   /

   To test the function:
             At SQL prompt, type
                 SQL> variable sal number
                 SQL> execute :sal := netsal(1001)
                 SQL> print sal

   Example: 4
   Code a package

   Step 1: Open the editor and create the package
   Step 2: Type the code below in a file named, MAHEPACK.

   Program
   CREATE OR REPLACE PACKAGE MAHEPACK
   AS
        function netsal
               (id in salary.emp_no%type)
        return number;
        procedure tax
               (id in salary.emp_no%type, tax out number);
        procedure totsales
               (cid in cstmast.cstid%type, sal out number);
DBMS                                                                  LAB MANUAL   73
   END;
   /

   Step 3: Save the above file and open the editor to create the package body

   Program

   CREATE OR REPLACE PACKAGE BODY MAHEPACK
   AS
       function netsal
              (id in salary.emp_no%type)
       return number
       is
              netsal salary.basic%type;
       begin
              select sum(basic) + sum(commission) – sum(deduction)
              into netsal
              from salary
              where emp_no = id;
              return (netsal);
       end;

          procedure tax
                (id in salary.emp_no%type, tax out number)
          is
                netsalary number(10, 2);
          begin
                netsalary := netsal(id);
                if netsalary < 2000
                then
                       tax := netsalary * 0.02;
                elsif netsalary < 4000
                then
                       tax := netsal * 0.04;
                else
                       tax := netsalary * 0.01;
                end if;
          end;

          procedure totsales
DBMS                                                                 LAB MANUAL   74
                  (cid in cstmast.cstid%type, sal out number)
          is
                  id      TRN.ITMID%TYPE;
                  qty     TRN.TRNQTY%TYPE;
                  price   ITMMAST.ITMPRICE%TYPE;
                  sales   NUMBER(10, 2) := 0;

                  cursor cur_tr is
                        select trn,itmid, trnqty, itmprice
                        from trn, itmmast
                        where trn.cstid = cid
                        and trn.itmid = itmmast.itmid;
          begin
            sales := 0;
            open cur_tr;
            loop
                   fetch cur_tr into id, qty, price;
                   if cur_tr%rowcount = 0
                   then
                          raise_application_error(-20020, ‘ERREOR!!!THERE
   IS NO DATA’);
                   end if;
                   exit when cur_tr%notfound;
                   sales := sales + qty * price;
            end loop;

                  close cur_tr;
                  sal := sales;
          end;
   END;
   /

   Step 4: Save the above file and to create the package, at SQL prompt type
         SQL> start mpack
         SQL> start mpackb

   Step 5: To execute, at SQL prompt type
         SQL> variable tx number
         SQL> execute mahepack.tax(1001, :tx)
         SQL> print tx
DBMS                                                                  LAB MANUAL     75
          Or
          SQL> variable nsal number
          SQL> execute :nsal := mahepack.netsal(1001)
          SQL> print nsal

   PS:
   Show Errors:
   SHOW ERRORS is used to display the line number and error of the most recent
   compilation errors.
   SQL> SHOW ERRORS

   Raise Application Error:
   RAISE_APPLICATION_ERROR procedure is one of Oracles utilities which
   help the user to manage the error conditions in the applications by specifying
   user-defined error numbers and messages.
   It takes 2 input parameters – the error number (which must be between -20000
   and -20999) and the error message to display.
   It terminates the procedure execution, rolls back any effects of the procedure,
   returns any user-specified error number and error message.


                                 WEEK 12 (PL/SQL)

       34)      (i) Creation of simple PL/SQL program which includes
          declaration section, executable section and exception handling
          section ( ex: Student marks can be selected from the table and
          printed for those who secured first class and an exception can be
          raised if no records were found).
                (ii) Insert data into student table and use COMMIT,
          ROLLBACK and SAVEPOINT in SQL block.

       35)     Develop a program that includes the features NESTED IF,
          CASE and CASE expression. The program can be extended using
          the NULLIF and COALESCE functions.

       36)    Program development using WHILE LOOPS, numeric FOR
          LOOPS, nested loops using ERROR handling, BUILT IN exceptions,
          USER defined exceptions, RAISE APPLICATION ERROR.
DBMS                                                                  LAB MANUAL      76
       37)     Program development using creation of procedure, passing
          parameters IN and OUT procedures.

       38)      Program development using creation of stored function, invoke
          functions in SQL statements and write complex functions.

       39)      Program development using creation of package specification,
          package bodies, private objects, package variables and cursors and
          calling stored packages.

       40)    Develop programs using features of parameters in a CURSOR,
          FOR UPDATE CURSOR, WHERE CURRENT of clause and
          CURSOR variables.

   Example: 1
   Write a row trigger to insert the existing values of the salary table into a new
   table when the salary table is updated.
   (Salary < emp_no, basic, commission, deduction, salary_date, department>
   Salaryaud < emp_no, basic, commission, deduction, salary_date, department>)

   Step 1: Open the editor
   Step 2: Type the code below in a file named, TRSAL.

   Program
   CREATE TRIGGER UPDSAL
   BEFORE UPDATE ON SALARY
   FOR EACH ROW
   BEGIN
          insert intosalaryaud
          values (:old.emp_no, :old.basic, :old.commission, :old.deduction,
   :old.salary_date, :old.department);
   END
   /

   Step 3: Save the above file

   Step 4: To create the trigger, at SQL prompt type
         SQL> start trsal
DBMS                                                                      LAB MANUAL   77
   Step 5: To test the trigger, update values in salary table and see if data is
   inserted in salaryaud table.

   Example: 2
   Write a trigger to restrict the user from using the emp table on Tuesday.

   Program
   create or replace trigger tr2
   before insert or update or delete
   on emp
   begin
          if (rtrim(to_char(sysdate, 'day')) = 'tuesday')
          then
                  raise_application_error(-20121, 'Cannot delete on Tuesday');
          end if;
   end;
   /

   Example: 3
   Write a PL/SQL block of code that first inserts a record in an Emp table.
   Update salaries of emp 1001 and emp 1002 by Rs 2000 and Rs 1500. Then
   check to see that the total salary does not exceed Rs 20000. If total salary is
   greater than Rs 20000 then undo the updates made to emp 1001 and emp
   1002.

   Program
   DECLARE
        total_sal     number(9);
   BEGIN
        insert into emp
        values (‘1009’, ‘Ram’, 1000);

         SAVEPOINT no_update;
         update emp
         set sal = sal + 2000
         where emp_id = 1001;

         update emp
         set sal = sal + 1500
         where emp_id = 1002;
DBMS                                                                   LAB MANUAL     78


          select sum(sal)
          into total_sal
          from emp;

          if total_sal > 20000
          then
                  ROLLBACK TO SQVEPOINT no_update;
          end if;

          COMMIT;
   END;
   /

   PS:
   The above program first inserts a record into emp table. It then marks and saves
   the current position in the transaction by using the SAVEPOINT. It updates the
   salaries, if the salaries are exceeding 20000 it rollsback to the save point, ie
   ignores the 2 updates and only commits the insert. If the salaries do not exceed
   20000 then the insert and 2 updates are committed.

   Example: 4

   Write a PL/SQL code of block, to calculate the area of the circle for the
   values of radius varying from 1 to 10. Store the odd radius values and the
   corresponding areas in a table.

   Program
   declare
         pi constant number(4,2) := 3.14;
         radius number(5);
         areaa number(14,2);
    begin
         radius := 1;
         while radius <= 10 loop
               areaa := pi*power(radius,2);
               case
                      when radius = 1
                      then
                            insert into area values (radius, areaa);
DBMS                                                            LAB MANUAL   79
                      when radius = 3
                      then
                             insert into area values (radius, areaa);
                      when radius = 53
                      then
                             insert into area values(radius,areaa);
                      when radius = 7
                      then
                             insert into area values(radius,areaa);
                      when radius = 9
                      then
                             insert into area values(radius,areaa);
                      else
                             dbms_output.put_line(‘EVEN RADIUS, NOT
                INSERTING!!!’);
                end case;
                radius:=radius+1;
          end loop;
   end;
   /
DBMS                                                             LAB MANUAL   80
   Overview of SQL DDL, DML and DCL Commands.

   DDL is Data Definition Language statements. Some examples:
   CREATE - to create objects in the database
   ALTER - alters the structure of the database
   DROP - delete objects from the database
   TRUNCATE - remove all records from a table, including all spaces
   allocated for the records ar
   removed
   COMMENT - add comments to the data dictionary
   GRANT - gives user's access privileges to database
   REVOKE - withdraw access privileges given with the GRANT
   command
   DML is Data Manipulation Language statements. Some examples:
   SELECT - retrieve data from the a database
   INSERT - insert data into a table
   UPDATE - updates existing data within a table
   DELETE - deletes all records from a table, the space for the records
   remain
   CALL - call a PL/SQL or Java subprogram
   EXPLAIN PLAN - explain access path to data
   LOCK TABLE - control concurrency
   DCL is Data Control Language statements. Some examples:
   COMMIT - save work done
   SAVEPOINT - identify a point in a transaction to which you can later
   roll back
DBMS                                                        LAB MANUAL   81
   ROLLBACK - restore database to original since the last COMMIT
   SET TRANSACTION - Change transaction options like what rollback
   segment to use
DBMS                                                                      LAB MANUAL    82

   Basic SQL DDL Commands.

    To practice basic SQL DDL Commands such as CREATE, DROP, etc.

   1. SQL - CREATE TABLE
    Syntax: CREATE TABLE tablename (column_name data_ type
   constraints, …)

   Example:

   INPUT:
   SQL> CREATE TABLE Emp ( EmpNo short CONSTRAINT PKey
   PRIMARY KEY,
   EName VarChar(15),             Job Char(10) CONSTRAINT Unik1 UNIQUE,

   Mgr short CONSTRAINT FKey1 REFERENCES EMP (EmpNo),
   Hiredate Date, DeptNo short CONSTRAINT FKey2 REFERENCES
   DEPT(DeptNo));

   RESULT: Table created.
   SQL>Create table prog20 (pname varchar2(20) not null), doj date not null,dob
   date not null, sex varchar(1) not null, prof1 varchar(20),prof2 varchar(20),salary
   number(7,2) not null);

   RESULT:
   Table created.
   SQL>desc prog20;

   Name                               Null?             Type

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

   PNAME                            NOT NULL            VARCHAR2(20)

   DOJ                               NOT NULL           DATE
   DOB                               NOT NULL           DATE
DBMS                                                               LAB MANUAL   83
   SEX                           NOT NULL              VARCHAR2(1)
   PROF1                                               VARCHAR2(20)

   PROF2                                               VARCHAR2(20)

   SALARY                                              NOT NULL
   NUMBER(7,2)

   2. SQL - ALTER TABLE

   INPUT:
   SQL>ALTER TABLE EMP ADD CONSTRAINT Pkey1 PRIMARY KEY
   (EmpNo);
   RESULT: Table Altered.

   Similarly, ALTER TABLE EMP DROP CONSTRAINT Pkey1;

   3. SQL - DROP TABLE
    – Deletes table structure – Cannot be recovered – Use with caution

   INPUT:
   SQL> DROP TABLE EMP;             Here EMP is table name

   RESULT: Table Dropped.
   4. TRUNCATE          TRUNCATE TABLE <TABLE NAME>;

   Basic SQL DML Commands.

    To practice basic SQL DML Commands such as INSERT, DELETE, etc.

   1. SQL - INSERT INTO
       Syntax: INSERT INTO tablename VALUES (value list)

          Single-row insert

   INSERT INTO S VALUES(‘S3’,’SUP3’,’BLORE’,10)

          Inserting one row, many columns at a time
DBMS                                                          LAB MANUAL   84
   INSERT INTO S (SNO, SNAME) VALUES (‘S1’, ‘Smith’);S1’ Smith’
          Inserting many rows, all/some columns at a time.

       INSERT INTO NEW_SUPPLIER (SNO, SNAME)

        SELECT SNO, SNAME FROM S

        WHERE CITY IN (‘BLORE’,’MADRAS’)




   Other Examples:

   INPUT:
   SQL>Insert into prog values (‘kkk’,’05-may-56’);
   RESULT: 1 row created.

   INPUT:
   SQL>Insert into prog20 values(‘Hema’,’25-sept-01’28-jan-
   85’,’f’,’c’,’c++’,’25000’);

   RESULT: 1 row created.

   INPUT:
   SQL>Insert into prog values(‘&pname’,’&doj’);

   SQL> Insert into prog values('&pname','&doj');
   Enter value for pname: ravi

   Enter value for doj: 15-june-81

   RESULT:
   old 1: Insert into prog values('&pname','&doj')

   new 1: Insert into prog values('ravi','15-june-81')
DBMS                                                       LAB MANUAL   85
   1 row created.

   2. SQL - UPDATE
   Syntax: UPDATE tablename SET column_name =value [ WHERE condition]

   Examples:
   UPDATE S SET CITY = ‘KANPUR’ WHERE SNO=‘S1’
   UPDATE EMP SET SAL = 1.10 * SAL

   SQL> update emp set sal=20000 where empno=7369;
   1 row updated.




   3. SQL - DELETE FROM

       Syntax: DELETE FROM tablename WHERE condition
   Examples:
   DELETE FROM SP WHERE PNO= ‘P1’
   DELETE FROM SP

   INPUT:
   SQL>Delete from emp where empno=7369;
   RESULT: 1 row deleted.

   Basic SQL DCL Commands.

    To practice basic SQL DCL Commands such as COMMIT, ROLLBACK
   etc.

   1. COMMIT
   Save changes (transactional).

   Syntax:

       COMMIT [WORK] [COMMENT 'comment_text']
DBMS                                                             LAB MANUAL   86
       COMMIT [WORK] [FORCE 'force_text' [,int] ]

   FORCE - will manually commit an in-doubt distributed transaction
   force_text - transaction identifier (see the DBA_2PC_PENDING view)
   int - sets a specific SCN.
   If a network or machine failure prevents a distributed transaction from
   committing properly, Oracle will store any commit comment in the data
   dictionary along with the transaction ID.

   INPUT:

   SQL>commit;

   RESULT: Commit complete.
DBMS                                                                LAB MANUAL   87

   2. ROLLBACK

   Undo work done (transactional).
   Syntax:
       ROLLBACK [WORK] [TO [SAVEPOINT]'savepoint_text_identifier'];

       ROLLBACK [WORK] [FORCE 'force_text'];

   FORCE - will manually rollback an in-doubt distributed transaction

   INPUT:

   SQL>rollback;
   RESULT:Rollback complete.

   3. SAVEPOINT
   Save changes to a point (transactional).

   Syntax:

       SAVEPOINT text_identifier

   Example:
    UPDATE employees
    SET salary = 95000
    WHERE last_name = 'Smith';

       SAVEPOINT justsmith;

       UPDATE employees
       SET salary = 1000000;

       SAVEPOINT everyone;

       SELECT SUM(salary) FROM employees;

       ROLLBACK TO SAVEPOINT justsmith;
DBMS             LAB MANUAL   88
       COMMIT;
DBMS                                                                               LAB MANUAL   89
   Writing and Practice of Simple Queries.

    To write simple queries and practice them.

   1. Get the description of EMP table.

   SQL>desc emp;

   RESULT:
   Name                                   Null?                  Type
   --------------------------------    ----------------------- -------------------------
   EMPNO                                 NOT NULL               NUMBER(4)
   ENAME                                                         VARCHAR2(10)
   JOB                                                          VARCHAR2(9)
   MGR                                                          NUMBER(4)
   HIREDATE                                                      DATE
   SAL                                                          NUMBER(7,2)
   COMM                                                          NUMBER(7,2)
   DEPTNO                                                        NUMBER(3)
   AGE                                                          NUMBER(3)
   ESAL                                                         NUMBER(10)

   2. Get the description DEPT table.

   SQL>desc dept;

   RESULT:
   Name                                      Null?             Type
   ---------------------------------   ---------------------   ---------------------------
   DEPTNO                              NOT NULL                NUMBER(2)
   DNAME                                                        VARCHAR2(14)
   LOC                                                         VARCHAR2(13)

   3.List all employee details.

   SQL>select * from emp;

   RESULT:
   EMPNO ENAME                JOB      MGR HIREDATE SAL COMM DEPTNO
   AGE ESAL
DBMS                                                                                  LAB MANUAL        90
   -------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ---
   --------------
   7369 SMITH CLERK                         7902 17-DEC-80 800                  0          20
   25        0
   7499 ALLEN SALESMAN 7698 20-FEB-81 1600                                      300        30
   25        0
   7521 WARD SALESMAN 7698 22-FEB-81 1250                                       500        30
   25        0
   7566 JONES MANAGER 7839 02-APR-81 2975                                       500        20
   25        0
   7698 BLAKE MANAGER 7839 01-MAY-81 2850                                      1400         30
   25       0


   4.List all employee names and their salaries, whose salary lies between
   1500/- and 3500/- both inclusive.


   INPUT
   SQL>select ename from emp where sal between 1500 and 3500;
   RESULT
                    ENAME
                    ----------
                    ALLEN
                    JONES
                    BLAKE
                    CLARK
                    SCOTT
                    TURNER
                    FORD
                    russel
                    greg

   9 rows selected.

   5. List all employee names and their and their manager whose manager is
   7902 or 7566 0r 7789.
   INPUT SQL>select ename from emp where mgr in(7602,7566,7789);
   RESULT
DBMS                                                          LAB MANUAL   91


                 ENAME
                 -------
                 SCOTT
                 FORD

   6. List all employees which starts with either J or T.
   INPUT SQL>select ename from emp where ename like ‘J%’ or ename like
   ‘T%’;

   RESULT:

                 ENAME
                 ---------
                 JONES
                 TURNER
                 JAMES
DBMS                                                               LAB MANUAL   92
   7. List all employee names and jobs, whose job title includes M or P.
   INPUT SQL>select ename,job from emp where job like ‘M%’ or job like
   ‘P%’;

   RESULT:
                 ENAME JOB
                 ---------- ---------
                 JONES MANAGER
                 BLAKE MANAGER
                 CLARK MANAGER
                 KING         PRESIDENT

   8. List all jobs available in employee table.
   INPUT SQL>select distinct job from emp;

   RESULT:
                 JOB
                 ---------
                 ANALYST
                 CLERK
                 MANAGER
                 PRESIDENT
                 SALESMAN
                 assistant
                 clerk

   7 rows selected.

   9. List all employees who belongs to the department 10 or 20.
   INPUT SQL>select ename from emp where deptno in (10,20);

   RESULT:
               ENAME
               ----------
               SMITH
               JONES
               CLARK
               SCOTT
               KING
DBMS                                                               LAB MANUAL   93
               ADAMS
               FORD
               MILLER

   8 rows selected.

   10. List all employee names , salary and 15% rise in salary.
   INPUT SQL>select ename , sal , sal+0.15* sal from emp;

   RESULT:
                      ENAME           SAL              SAL+0.15*SAL
                      ----------   ----------   ------------
                      SMITH          800        920
                      ALLEN         1600        1840
                      WARD          1250        1437.5
                      JONES         2975        3421.25
                      MARTIN         1250       1437.5
                      BLAKE          2850              3277.5
                      CLARK          2450       2817.5
   7 rows selected.

   11. List minimum , maximum , average salaries of employee.
   INPUT SQL>select min(sal),max(sal),avg(sal) from emp;

   RESULT:

                       MIN(SAL) MAX(SAL)           AVG(SAL)
                      --------- ---------- ----------
                            3   5000       1936.94118


   12. Find how many job titles are available in employee table.
   INPUT SQL>select count (distinct job) from emp;

   RESULT:
                          COUNT(DISTINCTJOB)
                          ------------------
                                       7
DBMS                                                             LAB MANUAL    94
   13. What is the difference between maximum and minimum salaries of
   employees in the organization?
   INPUT SQL>select max(sal)-min(sal) from emp;

   RESULT:
                    MAX(SAL)-MIN(SAL)
                    -----------------
                              4997

   14. Display all employee names and salary whose salary is greater than
   minimum salary of the company and job title starts with ‘M’.
   INPUT SQL>select ename,sal from emp where job like ‘M%’ and sal > (select
   min (sal) from emp);

   RESULT
                    ENAME             SAL
                    ----------   ----------
                    JONES           2975
                    BLAKE            2850
                    CLARK            2450

   15. Find how much amount the company is spending towards salaries.
   INPUT SQL>select sum (sal) from emp;

   RESULT
                     SUM(SAL)
                    ---------
                       32928
   16. Display name of the dept. with deptno 20.
   INPUT SQL>select ename from emp where deptno = 20;

   RESULT
                    ENAME
                    ----------
                    SMITH
                    JONES
                    SCOTT
                    ADAMS
DBMS                                                     LAB MANUAL   95
   17. List ename whose commission is NULL.
   INPUT SQL>select ename from emp where comm is null;
                 ENAME
   RESULT                ----------
                      CLARK
                      SCOTT
                      KING
                      ADAMS
                      JAMES
                      FORD
   6 rows selected.
DBMS                                                                 LAB MANUAL      96

   18. Find no.of dept in employee table.
   INPUT SQL>select count (distinct ename) from emp;

   RESULT
               COUNT(DISTINCTENAME

                    --------------------
                                17
   19. List ename whose manager is not NULL.
   INPUT SQL>select ename from emp where mgr is not null;

   RESULT
                     ENAME
                     ----------
                     SMITH
                     ALLEN
                     WARD
                     JONES
                     MARTIN
   5 rows selected.
   Writing Queries using GROUP BY and other clauses.
   To write queries using clauses such as GROUP BY, ORDER BY, etc. and
   retrieving information by joining tables.

   Source tables: emp, dept, programmer, software, study.

   Order by : The order by clause is used to display the results in sorted order.
   Group by : The attribute or attributes given in the clauses are used to form
   groups. Tuples with the same value on all attributes in the group by clause are
   placed in one group.
   Having: SQL applies predicates (conditions) in the having clause after groups
   have been formed, so aggregate function be used.

   1. Display total salary spent for each job category.
   INPUT SQL>select job,sum (sal) from emp group by job;
   RESULT
                  JOB           SUM(SAL)
DBMS                                                             LAB MANUAL   97
                      --------- ----------
                      ANALYST 6000
                      CLERK       23050
                      MANAGER 8275
                      PRESIDENT 5000
                      SALESMAN 5600
                      assistant 2200
                      clerk     2003
   7 rows selected.

   2. Display lowest paid employee details under each manager.
   INPUT SQL>select ename, sal from emp where sal in (select min(sal) from
   emp group by mgr);
   RESULT
                    ENAME          SAL
                    ----------     ----------
                    chai                   3
                    JAMES          950
                    MILLER         1000
                    ADAMS          1100
                    russel         2200
   5 rows selected.

   3. Display number of employees working in each department and their
   department name.
   INPUT SQL> select dname, count (ename) from emp, dept where
   emp.deptno=dept.deptno group by dname;
   RESULT
                    DNAME             COUNT(ENAME)
                    --------------    ------------
                    ACCOUNTING                 3
                    RESEARCH                  5
                    SALES                     9
   4. Display the sales cost of package developed by each programmer.
   INPUT SQL>select pname, sum(scost) from software group by pname;

   RESULT
DBMS                                                       LAB MANUAL   98
                    PNAME                  SUM(SCOST)
                    --------------------   ----------
                    john                   12000
                    kamala                         12000
                    raju                   12333
       3 rows selected.
DBMS                                                              LAB MANUAL     99

   5. Display the number of packages sold by each programmer.
   INPUT SQL>select pname, count(title) from software group by pname;

   RESULT
                     PNAME                COUNT(TITLE)
                     -------------------- ------------
                     john                         1
                     kamala                   1
                     raju                         1
                     ramana                       1
                     rani                         1
                     5 rows selected.
   6. Display the number of packages in each language for which the
   development cost is less than thousand.
   INPUT SQL>select devin, count(title) from software where dcost < 1000 group
   by devin;
   RESULT
                  DEVIN COUNT(TITLE)
                  ---------- ------------
                  cobol               1

   7. Display each institute name with number of students.
   INPUT SQL>select splace, count(pname) from study group by splace;
   RESULT
                    SPLACE               COUNT(PNAME)
                    -------------------- ------------
                    BDPS                         2
                    BITS                     1
                    BNRILLIANI               1
                    COIT                         1
                    HYD                          1
   5 rows selected.

   8. How many copies of package have the least difference between
   development and selling cost, were sold?
DBMS                                                              LAB MANUAL     100
   INPUT SQL>select sold from software where scost – dcost=(select min(scost –
   dcost) from software);
   RESULT
                     SOLD
                     ---------
                          11

   9. Which is the costliest package developed in Pascal.
   INPUT SQL>select title from software where devin = ‘PASCAL’ and dcost =
   (select max(dcost)from software where devin = ‘PASCAL’);
   RESULT
      no rows selected

   10. Which language was used to develop most no .of packages.
   INPUT SQL>select devin, count (*) from software group by devin having
   count(*) = (select max(count(*) ) from software group by devin);
   RESULT
                       DEVIN       COUNT(*)
                       ---------- ----------
                       jsp          2

   11.Who are the male programmers earning below the average salary of
   female programmers?
   INPUT SQL>select pname from programmer where sal < (select avg(sal) from
   programmer where sex = ‘F’) and sex = ‘M’;
   RESULT
                   PNAME
                   --------------------
                   vijay

   12. Display the details of software developed by the male programmers
   earning more than 3000/-.
   INPUT SQL>select programmer.pname, title, devin from programmer,
   software where sal > 3000 and sex = ‘M’ and programmer.pname =
   software.pname;
   RESULT
DBMS                                                               LAB MANUAL   101
                     no rows selected

   13. Display the details of software developed in c language by female
   programmers of pragathi.
   INPUT SQL>select software.pname, title, devin, scost, dcost, sold from
   programmer, software, study where devin = ‘c’ and sex =’F’ and splace =
   ‘pragathi’ and programmer.pname = software.pname and software.pname =
   study.pname;
DBMS                                                              LAB MANUAL   102

   14. Which language has been stated by the most of the programmers as
   proficiency one?
   INPUT SQL>select prof1, count(*) from programmer group by prof1 having
   count (*) = (select max (count (*) ) from programmer group by prof1);

   Writing Nested Queries.

    To write queries using Set operations and to write nested queries.

   Set Operations:
         UNION             -     OR
         INTERSECT               -      AND
         EXCEPT -          -     NOT

   NESTED QUERY:- A nested query makes use of another sub-query to
   compute or retrieve the information.

   1. Find the name of the institute in which the person studied and
   developed the costliest package.
   INPUT SQL>select splace, pname from study where pname = (select pname
   from software where scost = (select max (scost) from software);
   RESULT
                    SPLACE              PNAME
                    ------------        -------------
                    SAHBHARI                    MARY
   2. Find the salary and institute of a person who developed the highest
   selling package.
   INPUT SQL> select study.pname, sal, splace from study, programmer where
   study.pname = programmer.pname and study.pname = (select pname from
   software where scost = (select max (scost) from software));

   RESULT

                         PNAME SAL SPLACE
                     ----------- ------ -----------
DBMS                         LAB MANUAL   103
       MARY   4500 SABHARI
DBMS                                                                 LAB MANUAL      104

   3. How many packages were developed by the person who developed the
   cheapest package.
   INPUT SQL>select pname, count (title) from software where dcost = (select
   min(dcost) from software) group by pname;

   RESULT
                      PNAME             COUNT(TITLE)
                     -------------      ----------------------
                     VIJAY              1

   4. Calculate the amount to be recovered for those packages whose
   development cost has not yet recovered.
   INPUT SQL>select title , (dcost-scost) from software where dcost > scost;

   5. Display the title, scost, dcost, difference of scost and dcost in the
   descending order of difference.
   INPUT SQL> select title, scost, dcost, (scost - dcost) from software descending
   order by (scost-dcost);

   6. Display the details of those who draw the same salary.
   INPUT SQL> select p.pname, p.sal from programmer p, programmer t where
   p.pname <> t.pname and p.sal = t.sal;(or)
   INPUT SQL>select pname,sal from programmer t where pname<>t.pname and
   sal= t.sal;

   Writing Queries using functions.
   AIM: To write queries using single row functions and group functions.

   1. Display the names and dob of all programmers who were born in
   january.
   INPUT SQL>select pname , dob from programmer where to_char
   (dob,’MON’)=’JAN’;

   2. Calculate the experience in years of each programmer and display along
   with programmer name in descending order.
DBMS                                                              LAB MANUAL    105
   INPUT SQL> select pname, round (months_between(sysdate, doj)/12, 2)
   "EXPERIENCE" from programmer order by months_between (sysdate, doj)
   desc;

   3. List out the programmer names who will celebrate their birthdays
   during current month.
   INPUT SQL>select pname from programmer where to_char(dob,’MON’) like
   to_char (sysdate, ‘MON’);

   4. Display the least experienced programmer’s details.
   INPUT SQL>select * from programmer where doj = (select max (doj) from
   programmer);

   5. Who is the most experienced programmer knowing pascal.
   INPUT SQL>select pname from programmer where doj = (select min (doj)
   from programmer);

   6. Who is the youngest programmer born in 1965.
   INPUT SQL> select pname , dob from programmer where dob = (select max
   (dob) from programmer where to_char (dob,'yy') = 65);

   7. In which year, most of the programmers are born.
   INPUT SQL>select to_char (dob , ‘YY’) from programmer group by to_char
   (dob, ‘YY’) having count(*) = (select max (count(*)) from programmer group
   by to_char(dob,’YY’);

   8. In which month most number of programmers are joined.
   INPUT SQL>select to_char (doj,’YY’) from programmer group by to_char
   (doj,’YY’) having count (*) = (select max (count(*)) from programmer group
   by to_char (doj,’YY’);

   9. What is the length of the shortest name in programmer table ?
   INPUT SQL>select length (pname) from programmer where length (pname) =
   select min ( length (pname) from programmer);

   10. Display the names of the programmers whose name contains up to 5
   characters.
DBMS                                                              LAB MANUAL   106
   INPUT SQL>select pname from programmer where length (pname) <=5;

   11. Display all packages names in small letters and corresponding
   programmer names in uppercase letters.
   INPUT SQL>select lower (title), upper (pname) from software;
DBMS                                                                LAB MANUAL   107

   Writing Queries on views.

   AIM: To write queries on views.

   1. Create a view from single table containing all columns from the base
   table.
   SQL>create view view1 as (select * from programmer);

   2. Create a view from single table with selected columns.
   SQL>create a view view2 as (select pname,dob,doj,sex,sal from programmer);

   3. Create a view from two tables with all columns.
   SQL>create view xyz as select * from programmer full natural join software;

   4. Create a view from two tables with selected columns.
   SQL> create view lmn as (select programmer, pname, title, devin from
   programmer, software where sal < 3000 and programmer.pname =
   software.pname);

   5. Check all DML commands with above 4 views.
   INPUT SQL> insert into view1 values (‘ramu’,’12-sep-03’,’28-jan-
   85’,’f’,’dbase’,’oracle’,74000);

         RESULT
         1 row created;

   INPUT SQL>update view1 set salary =50000 where pname like ‘raju’;
         RESULT       1 row updated.

   Note: update command does not works for all queries on views.

   INPUT SQL>delete from view1 where pname like ‘raju’;

         RESULT      1 row deleted.
DBMS                                                         LAB MANUAL    108

   6. Drop views which you generated.
   INPUT SQL>drop view view1;

        RESULT      View dropped;

   INPUT SQL>drop view view2;

        RESULT      View dropped;
   INPUT SQL>drop view xyz;

   Writing PL/SQL block for insertion into a table.

   To write a PL/SQL block for inserting rows into EMPDET table with the
   following Calculations:
   HRA=50% OF BASIC
   DA=20% OF BASIC
   PF=7% OF BASIC
   NETPAY=BASIC+DA+HRA-PF

   INPUT

   DECLARE
       ENO1 empdet.eno%type;
       ENAME1 empdet.name%type;
       DEPTNO1 empdet.deptno%type;
       BASIC1 empdet.basic%type;
       HRA1 empdet.HRA%type;
       DA1 empdet.DA%type;
       PF1 empdet.pf%type;
       NETPAY1 empdet.netpay%type;
   BEGIN
       ENO1:=&ENO1;
       ENAME1:='&ENAME1';
       DEPTNO1:=&DEPTNO1;
       BASIC1:=&BASIC1;
       HRA1:=(BASIC1*50)/100;
       DA1:=(BASIC1*20)/100;
       PF1:=(BASIC1*7)/100;
DBMS                                               LAB MANUAL   109
        NETPAY1:=BASIC1+HRA1+DA1-PF1;

        INSERT INTO EMPDET VALUES (ENO1, ENAME1, DEPTNO1,
   BASIC1, HRA1, DA1, PF1, NETPAY1);
   END;

   RESULT:

   SQL> @BASIC
   Enter value for eno1: 104
   old 11: ENO1:=&ENO1;
   new 11: ENO1:=104;
   Enter value for ename1: SRINIVAS REDDY
   old 12: ENAME1:='&ENAME1';
   new 12: ENAME1:='SRINIVAS REDDY';
   Enter value for deptno1: 10
   old 13: DEPTNO1:=&DEPTNO1;
   new 13: DEPTNO1:=10;
   Enter value for basic1: 6000
   old 14: BASIC1:=&BASIC1;
   new 14: BASIC1:=6000;

   PL/SQL procedure successfully completed.


   SQL>/
   Enter value for eno1: 105
   old 11: ENO1:=&ENO1;
   new 11: ENO1:=105;
   Enter value for ename1: CIRAJ
   old 12: ENAME1:='&ENAME1';
   new 12: ENAME1:='CIRAJ';
   Enter value for deptno1: 10
   old 13: DEPTNO1:=&DEPTNO1;
   new 13: DEPTNO1:=10;
   Enter value for basic1: 6000
   old 14: BASIC1:=&BASIC1;
   new 14: BASIC1:=6000;

   PL/SQL procedure successfully completed.
DBMS                                                                                 LAB MANUAL         110


   SQL> SELECT * FROM EMPDET;
   RESULT
       ENO NAME                             DEPTNO BASIC                  HRA          DA         PF
   NETPAY
   --------- ------------------------------ --------- --------- --------- --------- --------- -------
   ----------------
       101 SANTOSH                              10         5000          2500        1000        350
   8150
       102 SHANKAR                              20         5000          2500        1000        350
   8150
       103 SURESH                               20         5500         2750         1100        385
   8965
       104 SRINIVASA REDDY                      10         6000         3000         1200       420
   9780
       105 CIRAJ                                10         6000         3000         1200       420
   9780

   Writing PL/SQL block for checking armstrong number

   To write a PL/SQL block to check whether given number is Armstrong or
   not.


   INPUT

   DECLARE
       num number(5);
       rem number(5);
       s number(5):=0;
       num1 number(5);
   BEGIN
       num:=&num;
       num1:=num;
       while(num>0)
       loop
             rem:=mod(num,10);
             s:=s+power(rem,3);
             num:=trunc(num/10);
       End loop;
DBMS                                                           LAB MANUAL   111
        if (s=num1)then
               dbms_RESULT.put_line(num1||' IS ARMSTRONG NUMBER ');
        else
               dbms_RESULT.put_line(num1||' IS NOT ARMSTRONG
   NUMBER ');
         End if;
   END;
   /

   RESULT:
   SQL>@arm
   Enter value for num: 153
   old 7: num:=&num;
   new 7: num:=153;
   153 IS ARMSTRONG NUMBER

   PL/SQL procedure successfully completed.

   SQL> /
   Enter value for num: 123
   old 7: num:=&num;
   new 7: num:=123;
   123 IS NOT ARMSTRONG NUMBER

   PL/SQL procedure successfully completed.
   Writing a PL/SQL block for checking a number even or odd.

   AIM: To write a PL/SQL block to check whether a given number is Even
   or Odd.

   INPUT

   DECLARE
       num number(5);
       rem number;
   BEGIN
       num:=&num;
       rem:=mod(num,2);
       if rem=0
       then
DBMS                                                                    LAB MANUAL   112
                 dbms_RESULT.put_line(' Number '||num||' is Even');
          else
                    dbms_RESULT.put_line(' Number '||num||' is Odd');
          end if;
   END;


   RESULT:

   SQL>start even
   Enter value for num: 6
   old 5: num:=&num;
   new 5: num:=6;
   Number 6 is Even

   PL/SQL procedure successfully completed.

   SQL> /
   Enter value for num: 3
   old 5: num:=&num;
   new 5: num:=3;
   Number 3 is Odd
   PL/SQL procedure successfully completed.




   Writing PL/SQL block to find sum of digits of a given number.

   To write a PL/SQL block to find Sum of Digits of a given Number.

   INPUT

   DECLARE
        num number(5);
       rem number(5);
       sm number(5):=0;
DBMS                                                           LAB MANUAL   113
         num1 number(5);
   BEGIN
         num:=&num;
         num1:=num;
         while(num>0)       loop
                rem:=mod(num,10);
                sm:=sm+rem;
                num:=trunc(num/10);
         end loop;
         dbms_RESULT.put_line('SUM OF DIGITS OF '||num1||' IS: '||sm);
   end;
   /
   RESULT:
   SQL> @sum
   INPUT truncated to 2 characters
   Enter value for num: 123
   old 7: num:=&num;
   new 7: num:=123;
   SUM OF DIGITS OF 123 IS: 6
   PL/SQL procedure successfully completed.

   SQL> @sum
   INPUT truncated to 2 characters
   Enter value for num: 456
   old 7: num:=&num;
   new 7: num:=456;
   SUM OF DIGITS OF 456 IS: 15
   PL/SQL procedure successfully completed.
DBMS                                                         LAB MANUAL   114
   Writing PL/SQL block for generating Fibonacci series.
   To write a PL/SQL block to Generate Fibonacci Series
   INPUT

   DECLARE
          num number(5);
         f1 number(5):=0;
         f2 number(5):=1;
         f3 number(5);
         i number(5):=3;
   BEGIN
         num:=&num;
         dbms_RESULT.put_line('THE FIBONACCI SERIES IS:');
         dbms_RESULT.put_line(f1);
         dbms_RESULT.put_line(f2);
         while(i<=num)      loop
                 f3:=f1+f2;
                dbms_RESULT.put_line(f3);
                 f1:=f2;
                f2:=f3;
                i:=i+1;
         end loop;
   END;
   /
   RESULT:
   SQL> start fib
   Enter value for num: 10
   old 8: num:=&num;
   new 8: num:=10;
   THE FIBONACCI SERIES IS:
   0
   1
   1
   2
   3
   5
   8
   13
   21
   34
DBMS                                                        LAB MANUAL   115
   PL/SQL procedure successfully completed.




   Writing PL/SQL block for checking palendrome.

   To write a PL/SQL block to Check the Given String is Palindrome or
   Not.

   INPUT

   DECLARE
         name1 varchar2(20);
         name2 varchar2(20);
         l number(5);
   BEGIN
         name1:='&name1';
         l:=length(name1);
         while l>0 loop
                name2:=name2||substr(name1,l,1);
                l:=l-1;
         end loop;
         dbms_RESULT.put_line('REVERSE OF STRING IS:'||NAME2);
         if(name1=name2) then
                dbms_RESULT.put_line(name1||' IS PALINDROME ');
         else
                dbms_RESULT.put_line(name1||' IS NOT PALINDROME ');
         end if;
   END;
   /
   RESULT
   Enter value for name1: LIRIL
   old 6: name1:='&name1';
   new 6: name1:='LIRIL';
   REVERSE OF STRING IS:LIRIL
   LIRIL IS PALINDROME

   PL/SQL procedure successfully completed.
DBMS                                                                         LAB MANUAL       116
   SQL> /
   Enter value for name1: MADAM
   old 6: name1:='&name1';
   new 6: name1:='MADAM';
   REVERSE OF STRING IS:MADAM
   MADAM IS PALINDROME

   PL/SQL procedure successfully completed.




   Writing PL/SQL block to demonstrate Cursors.

   To write a Cursor to display the list of Employees and Total Salary
   Department wise.

   INPUT
   DECLARE
        cursor c1 is select * from dept;
        cursor c2 is select * from emp;
       s emp.sal%type;

   BEGIN
            for i in c1 loop
                    s:=0;
                    dbms_RESULT.put_line('----------------------------------------------');
                            dbms_RESULT.put_line('Department is :' || i.deptno ||'
   Department name is:' || i.dname);
                    dbms_RESULT.put_line('-------------------------------------------');
                    for j in c2 loop
                            if ( i.deptno=j.deptno) then
                                     s:=s+j.sal;
                                    dbms_RESULT.put_line(j.empno|| ' '|| j.ename || '
   '|| j.sal );
                             end if;
                    end loop;
                    dbms_RESULT.put_line('----------------------------------------------');
                    dbms_RESULT.put_line('Total salary is: '|| s);
                    dbms_RESULT.put_line('----------------------------------------------');
DBMS                                                                                LAB MANUAL   117
          end loop;
   END;



   RESULT:

   SQL> @abc
   ------------------------------------------------------------------------------
   Department is :10 Department name is : ACCOUNTING
   ------------------------------------------------------------------------------
   7782 CLARK 2450
   7839 KING 5000
   7934 MILLER 1300
   -----------------------------------------------------------------------------
   Total salary is: 8750
   -----------------------------------------------------------------------------
   -----------------------------------------------------------------------------
   Department is :20 Department name is:RESEARCH
   ------------------------------------------------------------------------------
   7369 SMITH 800
   7566 JONES 2975
   7788 SCOTT 3000
   7876 ADAMS 1100
   7902 FORD 3000
   -----------------------------------------------------------------------------
   Total salary is: 10875
   ------------------------------------------------------------------------------
   ------------------------------------------------------------------------------
   Department is :30 Department name is:SALES
   ------------------------------------------------------------------------------
   7499 ALLEN 1600
   7521 WARD 1250
   7654 MARTIN 1250
   7698 BLAKE 2850
   7844 TURNER 1500
   7900 JAMES 950
   ------------------------------------------------------------------------------
   Total salary is: 9400
   ------------------------------------------------------------------------------
DBMS                                                                                LAB MANUAL   118
   ------------------------------------------------------------------------------
   Department is :40 Department name is:OPERATIONS
   ------------------------------------------------------------------------------
   ------------------------------------------------------------------------------
   Total salary is: 0
   ------------------------------------------------------------------------------
   PL/SQL procedure successfully completed.




   Writing PL/SQL CURSOR
   To write a Cursor to display the list of employees who are Working as a
   Managers or Analyst.

   INPUT
   DECLARE
        cursor c(jb varchar2) is select ename from emp where job=jb;
        em emp.job%type;
   BEGIN
        open c('MANAGER');
        dbms_RESULT.put_line(' EMPLOYEES WORKING AS MANAGERS
   ARE:');
        loop
               fetch c into em;
               exit when c%notfound;
               dbms_RESULT.put_line(em);
        end loop;
        close c;
DBMS                                                             LAB MANUAL   119
        open c('ANALYST');
        dbms_RESULT.put_line(' EMPLOYEES WORKING AS ANALYST
   ARE:');
        loop
               fetch c into em;
               exit when c%notfound;
               dbms_RESULT.put_line(em);
        end loop;
        close c;
   END;


   RESULT:

   EMPLOYEES WORKING AS MANAGERS ARE:
   JONES
   BLAKE
   CLARK
   EMPLOYEES WORKING AS ANALYST ARE:
   SCOTT
   FORD

   PL/SQL procedure successfully completed.




   Writing PL/SQL CURSOR
   To write a Cursor to display List of Employees from Emp Table in
   PL/SQL block

   INPUT

   DECLARE
       cursor c is select empno, ename, deptno, sal from emp ;
       i emp.empno%type;
       j emp.ename%type;
       k emp.deptno%type;
       l emp.sal%type;
DBMS                                                               LAB MANUAL   120
   BEGIN
          open c;
          dbms_RESULT.put_line('Empno, name, deptno, salary of employees
   are:= ');
          loop
                 fetch c into i, j, k, l;
                 exit when c%notfound;
                 dbms_RESULT.put_line(i||' '||j||' '||k||' '||l);
          end loop;
          close c;
   END;

   RESULT:
   SQL> @EMP
   Empno,name,deptno,salary of employees are:=
   7369   SMITH 20 800
   7499   ALLEN 30 1600
   7521   WARD 30 1250
   7566   JONES 20 2975
   7654   MARTIN 30 1250
   7698   BLAKE 30 2850
   7782   CLARK 10 2450
   7788   SCOTT 20 3000
   7839   KING        10 5000
   7844   TURNER 30 1500
   7876   ADAMS 20 1100
   7900   JAMES 30 950
   7902   FORD       20 3000
   7934   MILLER 10 1300
   PL/SQL procedure successfully completed.




   Writing PL/SQL CURSOR
   To write a Cursor to find employee with given job and deptno.
   INPUT
   DECLARE
DBMS                                                                LAB MANUAL   121
         cursor c1(j varchar2, dn number) is select empno, ename from emp
   where job=j and deptno=dn;
         row1 emp%rowtype;
         jb emp.job%type;
         d emp.deptno%type;
   BEGIN
         jb:='&jb';
         d:=&d;
         open c1(jb,d);
         fetch c1 into row1.empno,row1.ename;
         if c1%notfound then
                 dbms_RESULT.put_line('Employee does not exist');
         else
                 dbms_RESULT.put_line('empno is:'||row1.empno||' ' ||'employee
   name is:'||row1.ename);
         end if;
   END;

   RESULT:
   SQL> @CUR
   Enter value for jb: MANAGER
   old 7: jb:='&jb';
   new 7: jb:='MANAGER';
   Enter value for d: 20
   old 8: d:=&d;
   new 8: d:=20;
   empno is:7566 employee name is:JONES
   PL/SQL procedure successfully completed.

   SQL> /
   Enter value for jb: CLERK
   old 7: jb:='&jb';
   new 7: jb:='CLERK';
   Enter value for d: 40
   old 8: d:=&d;
   new 8: d:=40;
   Employee does not exist
   PL/SQL procedure successfully completed.
DBMS                                                                LAB MANUAL   122




   Writing PL/SQL BLOCK using string functions.
   To write a PL/SQL block to apply String Functions on a given input
   String.

   INPUT
   DECLARE
          a varchar2(20);
          l number(5);
   BEGIN
          a:='&a';
          l:=length(a);
          dbms_RESULT.put_line('Using Lower Function:' || lower(a));
          dbms_RESULT.put_line('Using UPPER Function:' || upper(a));
          dbms_RESULT.put_line('Using Initcap Function:' || initcap(a));
          dbms_RESULT.put_line('Using Substring Function:' || substr(a,l,1));
          dbms_RESULT.put_line('Using Substring Function:' || substr(a,1,3));
          dbms_RESULT.put_line('Using Ltrim function for xxxabcxxxx:' ||
   ltrim('xxxabcxxxx','x'));
          dbms_RESULT.put_line('Using Rtrim function for xxxabcxxxx:'||
   rtrim('xxxabcxxxx','x'));
          dbms_RESULT.put_line('Using Lpad function :'|| lpad(a,l+4,'*'));
          dbms_RESULT.put_line('Using Rpad function :'|| rpad(a,l+4,'*'));
   END;

   RESULT:

   SQL>@STR
   Enter value for a: santosh reddy
   old 5: a:='&a';
   new 5: a:='santosh reddy';
   Using Lower Function:santosh reddy
   Using UPPER Function:SANTOSH REDDY
   Using Initcap Function:Santosh Reddy
   Using Substring Function:y
   Using Substring Function:san
   Using Ltrim function for xxxabcxxxx:abcxxxx
DBMS                                             LAB MANUAL   123
   Using Rtrim function for xxxabcxxxx:xxxabc
   Using Lpad function :****santosh reddy
   Using Rpad function :santosh reddy****

   PL/SQL procedure successfully completed.
   SQL> /
   Enter value for a: UMA SHANKAR
   old 5: a:='&a';
   new 5: a:='UMA SHANKAR';
   Using Lower Function:uma shankar
   Using UPPER Function:UMA SHANKAR
   Using Initcap Function:Uma Shankar
   Using Substring Function:R
   Using Substring Function:UMA
   Using Ltrim function for xxxabcxxxx:abcxxxx
   Using Rtrim function for xxxabcxxxx:xxxabc
   Using Lpad function :****UMA SHANKAR
   Using Rpad function :UMA SHANKAR****

   PL/SQL procedure successfully completed
DBMS                                                                   LAB MANUAL      124




   Writing PL/SQL triggers

   To write a TRIGGER to ensure that DEPT TABLE does not contain
   duplicate of null values in DEPTNO column.

   INPUT

   CREATE OR RELPLACE TRIGGER trig1 before insert on dept for each row
   DECLARE
          a number;
   BEGIN
         if(:new.deptno is Null) then
                raise_application_error(-20001,'error::deptno cannot be null');
         else
                select count(*) into a from dept where deptno=:new.deptno;
                if(a=1) then
                       raise_application_error(-20002,'error:: cannot have duplicate
   deptno');
                end if;
         end if;
   END;

   RESULT:

   SQL> @trigger
   Trigger created.

   SQL> select * from dept;
    DEPTNO DNAME                   LOC
     --------- -------------- -------------
      10 ACCOUNTING NEW YORK
DBMS                                                            LAB MANUAL   125
       20 RESEARCH   DALLAS
       30 SALES    CHICAGO
       40 OPERATIONS BOSTON

   SQL> insert into dept values(&deptnp,'&dname','&loc');
   Enter value for deptnp: null
   Enter value for dname: marketing
   Enter value for loc: hyd
   old 1: insert into dept values(&deptnp,'&dname','&loc')
   new 1: insert into dept values(null,'marketing','hyd')
   insert into dept values(null,'marketing','hyd')
           *
   ERROR at line 1:
   ORA-20001: error::deptno cannot be null
   ORA-06512: at "SCOTT.TRIG1", line 5
   ORA-04088: error during execution of trigger 'SCOTT.TRIG1'

   SQL> /
   Enter value for deptnp: 10
   Enter value for dname: manager
   Enter value for loc: hyd
   old 1: insert into dept values(&deptnp,'&dname','&loc')
   new 1: insert into dept values(10,'manager','hyd')
   insert into dept values(10,'manager','hyd')
           *
   ERROR at line 1:
   ORA-20002: error:: cannot have duplicate deptno
   ORA-06512: at "SCOTT.TRIG1", line 9
   ORA-04088: error during execution of trigger 'SCOTT.TRIG1'

   SQL> /
   Enter value for deptnp: 50
   Enter value for dname: MARKETING
   Enter value for loc: HYDERABAD
   old 1: insert into dept values(&deptnp,'&dname','&loc')
   new 1: insert into dept values(50,'MARKETING','HYDERABAD')

   1 row created.
   SQL> select * from dept;
DBMS                                                                 LAB MANUAL      126
        DEPTNO DNAME                    LOC
        --------- -------------- -------------
         10 ACCOUNTING NEW YORK
         20 RESEARCH              DALLAS
         30 SALES            CHICAGO
         40 OPERATIONS BOSTON
         50 MARKETING HYDE




   Locking Table.
   AIM: To learn commands related to Table Locking

   LOCK       TABLE      Statement     Manually   lock   one   or   more   tables.

   Syntax:
     LOCK TABLE [schema.] table [options] IN lockmode MODE [NOWAIT]

       LOCK TABLE [schema.] view [options] IN lockmode MODE [NOWAIT]

   Options:
      PARTITION (partition)
      SUBPARTITION (subpartition)
      @dblink

   lockmodes:
       EXCLUSIVE
       SHARE
       ROW EXCLUSIVE
       SHARE ROW EXCLUSIVE
       ROW SHARE* | SHARE UPDATE*
DBMS                                                                     LAB MANUAL   127
   If NOWAIT is omitted Oracle will wait until the table is available.

   Several tables can be locked with a single command - separate with commas

   e.g. LOCK TABLE table1,table2,table3 IN ROW EXCLUSIVE MODE;

   Default Locking Behaviour :

   A pure SELECT will not lock any rows.

   INSERT, UPDATE or DELETE's - will place a ROW EXCLUSIVE lock.

   SELECT...FROM...FOR UPDATE NOWAIT - will place a ROW
   EXCLUSIVE lock.

   Multiple Locks on the same rows with LOCK TABLE

   Even when a row is locked you can always perform a SELECT (because
   SELECT does not lock any rows) in addition to this, each type of lock will
   allow additional locks to be granted as follows.

   ROW SHARE = Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks
   to be granted to the locked rows.

   ROW EXCLUSIVE = Allow ROW EXCLUSIVE or ROW SHARE locks to be
   granted to the locked rows.

   SHARE ROW EXCLUSIVE = Allow ROW SHARE locks to be granted to the
   locked rows.

   SHARE = Allow ROW SHARE or SHARE locks to be granted to the locked
   rows.

   EXCLUSIVE = Allow SELECT queries only

   Although it is valid to place more than one lock on a row, UPDATES and
   DELETE's may still cause a wait if a conflicting row lock is held by another
   transaction.

   Generation of Forms using ORACLE FORM BUILDER
DBMS                                                                   LAB MANUAL      128
         To design a form using Oracle Developer 2000

   Introduction

   Use Form Builder to simplify for the creation of data-entry screens, also known
   as Forms. Forms are the applications that connect to a database, retrieve
   information requested by the user, present it in a layout specified by Form
   designer, and allow the user to modify or add information. Form Builder allows
   you to build forms quickly and easily.

   In this Hands-On, you learn how to: Create a Data block for the “Customer”
   table, Create a layout, Use “content” canvas, Use “execute query”, Navigate a
   table, Use next, previous record, Enter query, Manipulate table’s record, Insert,
   Update, Delete and Save record.

   Form Builder Tool

   Open the "Form Builder" tool.

   Welcome window

   You will get the ‘Welcome to the Form Builder’ window. If you don’t want to
   get this window anymore uncheck the ‘Display at startup’ box. You can start
   your work with any of the following options:
   Use the data Block Wizard
   Build a new form manually
   Open an existing form
   Build a form based on a template
   The default is ‘Use the data Block Wizard.’ If you want to build a new form
   manually, click on "Cancel” or check ‘Build a new form manually’ and click
   ‘OK.’

   Connect to database

   In the ‘Object Navigator’ window, highlight "Database Objects." Go to the
   Main menu and choose "File," then "Connect."

   In the ‘Connect’ window, login in as “scott” password “tiger,” then click
   “CONNECT.”
DBMS                                                                    LAB MANUAL      129


   Notice that the box next to ‘Database Objects’ is not empty anymore and it has
   a ‘+’ sign in it. That will indicate that this item is expandable and you are able
   to see its entire objects.

   Click on the ‘+’ sign next to the ‘Database Objects’ to expand all database
   schemas.

   Create a Module

   In the ‘Object Navigator’ window, highlight module1. This is a default name.
   Go to the Main menu and choose “File,” select “Save as” to store the new
   object in the “iself” folder and save it as customer data entry. "c:_de." In this
   example the ‘DE’ abbreviation stands for Data Entry.

   Create a Data Block

   In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the
   "create” icon. The ‘Create’ icon is in the vertical tool bar in the ‘Object
   Navigator’ window. It is a green ‘+’ sign. If you drag your cursor on the icon a
   tooltip will show ‘Create.’

   New Data Block

   In the ‘New Data Block’ window, choose the default option “Data Block
   Wizard” and click "OK."

   Welcome Data Block

   In the ‘Welcome Data Block Wizard’ window click on the “NEXT” icon.

   Type of Data Block

   Select the type of data block you would like to create by clicking on a radio
   button. Select the default option ‘Table or View’ and then click “NEXT” again.

   Selecting Tables

   Click on “browse.” In the ‘Tables’ window, highlight the "cust11” table; then
   click "OK."
DBMS                                                                   LAB MANUAL      130


   Selecting columns for the Data Block Wizard

   To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’
   window. To choose selected columns, click on the one arrow sign. And then
   select all columns, and click “next.”



   Layout Wizard

   End of the Data Block Wizard and beginning of the Layout Wizard
   In the ‘Congratulations’ screen, use the default checkmark radio button (Create
   the data block, then call the Layout Wizard), and click "Finish." You can also
   use the Data Block Wizard to modify your existing data block. Simply select
   the data block in the Object Navigator and click the Data Block Wizard toolbar
   button, or choose ‘Data Block wizard’ from the ‘Tools’ menu.

   Welcome screen

   In the ‘Welcome to the Layout Wizard’ window, click ”Next.”


   Selecting canvas

   In the ‘Layout Wizard’ window, select the "new canvas" option. Canvas is a
   place that you will have your objects such as columns, titles, pictures, etc. If
   you have already had your canvas, select the canvas and then click on the next.
   The following are different types of canvases: Content, Stacked, Vertical
   Toolbar, Horizontal Toolbar, and Tab.

   Think of the ‘Content’ canvas as one flat place to have all your objects. In the
   stacked canvas, you can have multiple layers of objects and it is the same as the
   tab canvas. You use the vertical or horizontal toolbar canvases for your push
   buttons. Check the different types of canvases by clicking on the ‘down arrow’
   box next to the ‘Type’ field. Select "content," then click “Next.”

   Selecting Columns for the Layout Wizard
DBMS                                                                   LAB MANUAL       131
   In the ‘Layout Wizard’ window, select all the columns. These are the columns
   that you want to be displayed on the canvas. Then click “Next.”

   Change your objects appearances

   Change size or prompt if needed. In this window, you can enter a prompt,
   width, and height for each item on the canvas. You can change the
   measurement units. As a default the default units for item width and height are
   points. You can change it to inch or centimeter. When you change size, click
   “Next.”

   Selecting a layout style

   Select a layout style for your frame by clicking a radio button. Select "Form," if
   you want one record at a time to be displayed. Select “Tabular,” if you want
   more than one record at a time to be displayed. Select "Forms," and then click
   “next.”


   Record layout

   Type the "Frame Title" and click "next." Checkmark the ‘Display Scrollbar’
   box when you use multiple records or the ‘Tabular’ option.

   Congratulation Screen

   In the ‘Congratulations’ window, click "Finish."
   You will see the output layout screen.
   Make some window adjustments and then run the form. To run the form, click
   on the ‘Run’ icon. The ‘Run’ icon is on the horizontal toolbar in the
   ‘CUSTOMER_DE’ canvas.

   The object module should be compiled successfully before executing the Form.

   Execute Query

   Click on the "Execute Query" icon below the main menu. If you drag the cursor
   on the toolbar in the ‘Forms Runtime’ window, a tooltip will be displayed and
   you see ‘Execute Query.’
   So to know all your option, drag your cursor to view all the icon descriptions.
DBMS                                                                  LAB MANUAL    132


   Next Record

   Click on the "Next Record" icon to navigate to the next record.

   Previous Record

   Click on the "Previous Record" icon to navigate to the previous record.
   This is an easy way to navigate through the “Customer” table.

   Enter Query

   Click on the "Enter Query" icon to query selected records.

   Insert Record

   Click "Insert Record" to add new customer. All items on the forms will be
   blanked. You can either type all the customer information or duplicate it from
   pervious record.

   Duplicate Record

   To duplicate the previous record, go to the main menu and select the ‘Record’
   sub-menu. A drop down menu will be displayed. Select the ‘Duplicate’ option
   in the sub-menu.

   Apply the changes. Remember in this stage, your record was inserted but not
   committed yet.
    Next and Previous Record

   Click "next record" and "previous record" to navigate through the records and
   the one was added.

   Save transactions

   Click "Save" to commit the insert statement.


   Delete Record
DBMS                                                             LAB MANUAL     133
   Click "Remove Record" to delete the record.

   Lock a Record

   You can also lock the record.

   Exit from Form Runtime

   Exit the FORM Runtime. If you have not committed any transaction, you will
   be prompted to save changes. Click “YES” to save changes.
   Click “OK” for acknowledgement.

   Don’t forget to save the Form.


   RABAD




                         Selecting the type of form to create
DBMS                   LAB MANUAL   134




       Object wizard
DBMS                                                          LAB MANUAL   135




       Selecting the canvas on which data block can be displayed
DBMS                                       LAB MANUAL   136




       Form showing the Employee details
DBMS                                                                   LAB MANUAL      137
   EXPT#28. Generating REPORTS using Oracle Developer 2000
   AIM: To design reports using Oracle Developer 2000

   Introduction
   Tabular report shows data in a table format. It is similar in concept to the idea
   of an Oracle table. Oracle, by default, returns output from your select statement
   in tabular format.

   Hands-on
   In this Hands-On, your client is a stock broker that keeps track of its customer
   stock transactions. You have been assigned to write the reports based on their
   reports layout requirements.
   Your client wants you to create a simple listing report to show list of the stock
   trades by using stocks table for their brokerage company

   Your tasks are:
   1- Write a tabular report.
   2- Apply user layout Format mask.
   3- Run the report.
   4- Test the repot.

   You will learn how to: use report wizard, object navigator, report builder, “date
   model”, property palette, work on query and group box, see report style, use
   tabular style, navigating through report’s record, change the format mask for
   dollar, numeric and date items.

   Open Report Builder tool
   Open the "Report Builder" tool.

   Connect to database
   In the Object Navigator, highlight "Database Objects,” choose "File," then
   select the "Connect" option.
   In the ‘Connect’ window, login as “iself” password schooling, then click
   “CONNECT.”

   Save a report
   In the Object Navigator, highlight the "untitled" report, choose “File,” and
   select the “Save as” option.
   In the ‘Save as’ window, make sure to save the report in the ISELF folder and
   name it "rpt01_stock_history,” report number 1 stock history.
DBMS                                                                  LAB MANUAL      138


   Data Model
   In the Object Navigator, double click on the "Data Model" icon.

   Create SQL box
   In the Data Model window, click on the "SQL Query" icon. Then drag the plus
   sign cursor and click it anywhere in the “Data Model” screen where you wish
   your object to be.

   In the ‘SQL Query Statement’ window, write a query to read all the stocks
   record sorted by their symbol.
   (SQL Query Statement)
   SELECT * FROM stocks
   ORDER BY symbol
   Click “OK.”

   Change SQL box’s name
   In the Data Model window, in the “SQL” box, right click on the ‘Q_1’ and open
   its property palette.
   In its property palette, change the name to Q_STOCKS. Then close the
   window.

   Change GROUP box’s name
   In the Data Model, right click on the group box (G_SYMBOL) and open its
   property palette.
   In the Group property palette, change the name to ‘G_STOCKS,’ and close the
   window.

   Open Report Wizard
   In the Data Model, click on the ‘Report Wizard’ icon on the horizontal tool bar.
   In the Style tab, on the Report Wizard window, type ‘Stock History’ in the Title
   box and choose the report style as ‘Tabular.’
   Notice that when you change the report style a layout of that report will be
   displayed on the screen.
   Choose a different style to display its layout of its report style.

   Data, Fields, Totals, Labels and Template tabs
   Click “NEXT” to go to the Data tab. In the ‘SQL Query Statement’ verify your
   query.
DBMS                                                                    LAB MANUAL     139
   Click “NEXT” to navigate to the Fields tab, select the fields that you would like
   to be display in your report. Select all the columns to be display.
   Click “NEXT” to navigate to Totals tab, select the fields for which you would
   like to calculate totals. We have none in this hands-on exercise.

   Click “NEXT” to open the Labels tab, modify the labels and widths for your
   fields and totals as desired.
   Click “NEXT” again to go to the Template tab, and choose a template for your
   report. Your report will inherit the template’s colors, fonts, line widths, and
   structure.
   Use the default template and click “finish.”

   Running a report
   Now, you should have your output report on the screen.

   Resize an object
   Maximize the output report and format the report layout. To resize an object ,
   select it and drag its handler to the preferred size.

   Move an object
   To move an object, select and drag it while the cursor is on the object.

   This is a simple report.
   Navigate through the output
   To navigate through the output report in the Report Editor - Live Pre-viewer,
   click on the "next page" or "previous page" icon on the horizontal toolbar.
   Do the same with the "first page" or "last page" icon.

   Use the “zoom in” and “zoom out” icon to preview the report.

   Know report’s functions
   To know each icon functionalities, drag your cursor on it and a tooltip will
   display its function.

   Change Format Mask
   To change the "format mask" of a column, the column should be selected. Then
   go to the toolbar and click on the “$” icon, "add decimal place," and the “right
   justify” format to the all currency columns (Todays Low, Todays High, and
   current price)
DBMS                                                                     LAB MANUAL       140
   Select the “traded today” column, and click on the ‘,0’ icon (apply commas),
   and make it right justify.
   Also, you can change any attributes of field by opening its property palette. To
   open an object’s property palette, right click on it and select the Property Palette
   option.
   Right click on the "trade date" column and open its "property palette."
   Change the date "Format Mask" property and make it “year 2000 complaint
   (MM-DD-RR).”




                                Selecting type of report
DBMS                                     LAB MANUAL   141




              Creating reports



       Selecting the format of reports
DBMS                                     LAB MANUAL   142




       Selecting the Table in database
DBMS                                         LAB MANUAL   143




       Selecting the columns in the report
DBMS                                  LAB MANUAL   144




       Modify the labels in a table
DBMS                                               LAB MANUAL   145




       Choose a template to represent the report
DBMS                                                    LAB MANUAL   146




       To specify the completion of report generation
DBMS   LAB MANUAL   147
DBMS                                                      LAB MANUAL   148
   EXPT#29. Providing Security using GRANT and REVOKE.
   AIM: To learn GRANT and REVOKE commands to restrict privileges.

   (1) GRANT Statement
   Grant privileges to a user (or to a user role)

   Syntax:

   Grant System-wide Privs:

       GRANT system_priv(s) TO grantee
        [IDENTIFIED BY password] [WITH ADMIN OPTION]

       GRANT role TO grantee
        [IDENTIFIED BY password] [WITH ADMIN OPTION]

       GRANT ALL PRIVILEGES TO grantee
        [IDENTIFIED BY password] [WITH ADMIN OPTION]

   Grant privs on specific objects:

    GRANT object_priv [(column, column,...)]
     ON [schema.]object
       TO grantee [WITH GRANT OPTION] [WITH HIERARCHY
   OPTION]

    GRANT ALL PRIVILEGES [(column, column,...)]
     ON [schema.]object
       TO grantee [WITH GRANT OPTION] [WITH HIERARCHY
   OPTION]

    GRANT object_priv [(column, column,...)]
     ON DIRECTORY directory_name
       TO grantee [WITH GRANT OPTION] [WITH HIERARCHY
   OPTION]

    GRANT object_priv [(column, column,...)]
     ON JAVA [RE]SOURCE [schema.]object
       TO grantee [WITH GRANT OPTION] [WITH HIERARCHY
   OPTION]
DBMS                                                       LAB MANUAL    149


   grantee:
     user
     role
     PUBLIC

   system_privs:
     CREATE SESSION - Allows user to connect to the database
     UNLIMITED TABLESPACE - Use an unlimited amount of any
   tablespace.
     SELECT ANY TABLE - Query tables, views, or mviews in any schema
     UPDATE ANY TABLE - Update rows in tables and views in any
   schema
     INSERT ANY TABLE - Insert rows into tables and views in any
   schema
     Also System Admin rights to CREATE, ALTER or DROP:
      cluster, context, database, link, dimension, directory, index,
      materialized view, operator, outline, procedure, profile, role,
      rollback segment, sequence, session, synonym, table, tablespace,
      trigger, type, user, view. (full list of system privs)

   object_privs:
     SELECT, UPDATE, INSERT, DELETE, ALTER, DEBUG, EXECUTE,
   INDEX, REFERENCES

   roles:
     SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE,
   IMP_FULL_DATABASE
     SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE,
   DELETE_CATALOG_ROLE
     AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - advanced queuing
     SNMPAGENT - Enterprise Manager/Intelligent Agent.
     RECOVERY_CATALOG_OWNER - rman
     HS_ADMIN_ROLE - heterogeneous services

       plus any user defined roles you have available

   Notes:
DBMS                                                             LAB MANUAL     150
   Several Object_Privs can be assigned in a single GRANT statement
   e.g.
   GRANT SELECT (empno), UPDATE (sal) ON scott.emp TO emma

   WITH HIERARCHY OPTION will grant the object privilege on all subobjects,
   including any created after the GRANT statement is issued.

   WITH GRANT OPTION will enable the grantee to grant those object privileges
   to other users and roles.

   "GRANT ALL PRIVILEGES..." may also be written as "GRANT ALL..."



   (ii) REVOKE Statement
   Revoke privileges from users or roles.

   Syntax:

   Roles:
    REVOKE role FROM {user, | role, |PUBLIC}

   System Privs:
    REVOKE system_priv(s) FROM {user, | role, |PUBLIC}

       REVOKE ALL FROM {user, | role, |PUBLIC}

   Object Privs:
     REVOKE object_priv [(column1, column2..)] ON [schema.]object
        FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS]
   [FORCE]

     REVOKE object_priv [(column1, column2..)] ON [schema.]object
       FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS]
   [FORCE]

     REVOKE object_priv [(column1, column2..)] ON DIRECTORY
   directory_name
         FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS]
   [FORCE]
DBMS                                                                 LAB MANUAL    151


     REVOKE object_priv [(column1, column2..)] ON JAVA [RE]SOURCE
   [schema.]object
        FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS]
   [FORCE]

   key:
     object_privs
     ALTER, DELETE, EXECUTE, INDEX, INSERT,
     REFERENCES, SELECT, UPDATE, ALL PRIVILEGES

     system_privs
     ALTER ANY INDEX, BECOME USER, CREATE TABLE, DROP ANY
   VIEW
     RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY
   TABLE
     plus too many others to list here

     roles
     Standard Oracle roles -
     SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE,
   IMP_FULL_DATABASE
     plus any user defined roles you have available

   FORCE, will revoke all privileges from a user-defined-type and mark it's
   dependent objects INVALID.

   The roles CONNECT, RESOURCE and DBA are now deprecated (supported
   only for backwards compatibility) unless you are still running Oracle 6.0

   Error ORA-01927 "cannot REVOKE privileges you did not grant" - This
   usually means you tried revoking permission from the table owner, e.g.
   Oracle will not allow REVOKE select on USER1.Table1 from USER1 Owners
   of objects ALWAYS have full permissions on those objects. This is one reason
   it makes sense to place tables in one schema and the packaged prodecures used
   to access those tables in a separate schema.
DBMS                                                                 LAB MANUAL     152
                      REFERENCES:

       1. Oracle 9i Release 2 (9.2) SQL Reference,
          www.cs.ncl.ac.uk/teaching/facilities/swdoc/oracle9i/server.920/a96540/t
          oc.htm.

       2. Oracle 9i Release 1 (9.0.1) SQL Reference,
          http://download-
          east.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/toc.htm.

       3. An A-Z Index of Oracle SQL Commands (version 9.2)
          http://www.ss64.com/ora/.

       4. Database Systems Instructor: Prof. Samuel Madden Source: MIT Open
          Courseware (http://ocw.mit.edu).

       5. RDBMS Lab Guide, www.campusconnect.infosys.com
          userid:demo@infosys and passwork:infosys.

       6. Orelly PL/SQL Pocket Reference,
          http://www.unix.org.ua/orelly/oracle/langpkt/index.htm

       7. PL/SQL User's Guide and Reference, Release 2 (9.2)

          http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96624/to
          c.htm.
DBMS                                                                   LAB MANUAL     153

   VIVA VOICE QUESTIONS AND ANSWERS
   1. What is database?
   A database is a logically coherent collection of data with some inherent
   meaning, representing some aspect of real world and which is designed, built
   and populated with data for a specific purpose.

   2. What is DBMS?
   It is a collection of programs that enables user to create and maintain a
   database. In other words it is general-purpose software that provides the users
   with the processes of defining, constructing and manipulating the database for
   various applications.

   3. What is a Database system?
   The database and DBMS software together is called as Database system.

   4. Advantages of DBMS?
   Ø Redundancy is controlled.
   Ø Unauthorised access is restricted.
   Ø Providing multiple user interfaces.
   Ø Enforcing integrity constraints.
   Ø Providing backup and recovery.

   5. Disadvantage in File Processing System?
   Ø Data redundancy & inconsistency.
   Ø Difficult in accessing data.
   Ø Data isolation.
   Ø Data integrity.
   Ø Concurrent access is not possible.
   Ø Security Problems.

   6. Describe the three levels of data abstraction?
   The are three levels of abstraction:
   Ø Physical level: The lowest level of abstraction describes how data are stored.
   Ø Logical level: The next higher level of abstraction, describes what data are
   stored in database and what relationship among those data.
   Ø View level: The highest level of abstraction describes only part of entire
   database.
   7. Define the "integrity rules"
   There are two Integrity rules.
DBMS                                                                      LAB MANUAL    154
   Ø Entity Integrity: States that “Primary key cannot have NULL value”
   Ø Referential Integrity: States that “Foreign Key can be either a NULL value or
   should be Primary Key value of other relation.

   8. What is extension and intension?
   Extension -
   It is the number of tuples present in a table at any instance. This is time
   dependent.
   Intension -
   It is a constant value that gives the name, structure of table and the constraints
   laid on it.

   9. What is System R? What are its two major subsystems?
   System R was designed and developed over a period of 1974-79 at IBM San
   Jose Research Center. It is a prototype and its purpose was to demonstrate that
   it is possible to build a Relational System that can be used in a real life
   environment to solve real life problems, with performance at least comparable
   to that of existing system.
   Its two subsystems are
   Ø Research Storage
   Ø System Relational Data System.

   10. How is the data structure of System R different from the relational
   structure?
   Unlike Relational systems in System R
   Ø Domains are not supported
   Ø Enforcement of candidate key uniqueness is optional
   Ø Enforcement of entity integrity is optional
   Ø Referential integrity is not enforced

   11. What is Data Independence?
   Data independence means that “the application is independent of the storage
   structure and access strategy of data”. In other words, The ability to modify the
   schema definition in one level should not affect the schema definition in the
   next higher level.
   Two types of Data Independence:
   Ø Physical Data Independence: Modification in physical level should not affect
   the logical level.
   Ø Logical Data Independence: Modification in logical level should affect the
DBMS                                                                         LAB MANUAL   155
   view level.
   NOTE: Logical Data Independence is more difficult to achieve
   12. What is a view? How it is related to data independence?
   A view may be thought of as a virtual table, that is, a table that does not really
   exist in its own right but is instead derived from one or more underlying base
   table. In other words, there is no stored file that direct represents the view
   instead a definition of view is stored in data dictionary.
   Growth and restructuring of base tables is not reflected in views. Thus the view
   can insulate users from the effects of restructuring and growth in the database.
   Hence accounts for logical data independence.

   13. What is Data Model?
   A collection of conceptual tools for describing data, data relationships data
   semantics and constraints.

   14. What is E-R model?
   This data model is based on real world that consists of basic objects called
   entities and of relationship among these objects. Entities are described in a
   database by a set of attributes.

   15. What is Object Oriented model?
   This model is based on collection of objects. An object contains values stored in
   instance variables with in the object. An object also contains bodies of code that
   operate on the object. These bodies of code are called methods. Objects that
   contain same types of values and the same methods are grouped together into
   classes.

   16. What is an Entity?
   It is a 'thing' in the real world with an independent existence.

   17. What is an Entity type?
   It is a collection (set) of entities that have same attributes.

   18. What is an Entity set?
   It is a collection of all entities of particular entity type in the database.

   19. What is an Extension of entity type?
   The collections of entities of a particular entity type are grouped together into
   an entity set.
DBMS                                                                       LAB MANUAL       156
   20. What is Weak Entity set?
   An entity set may not have sufficient attributes to form a primary key, and its
   primary key compromises of its partial key and primary key of its parent entity,
   then it is said to be Weak Entity set.

   21. What is an attribute?
   It is a particular property, which describes the entity.

   22. What is a Relation Schema and a Relation?
   A relation Schema denoted by R(A1, A2, …, An) is made up of the relation
   name R and the list of attributes Ai that it contains. A relation is defined as a set
   of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each
   tuple is an ordered list of n-values t=(v1,v2, ..., vn).

   23. What is degree of a Relation?
   It is the number of attribute of its relation schema.

   24. What is Relationship?
   It is an association among two or more entities.

   25. What is Relationship set?
   The collection (or set) of similar relationships.

   26. What is Relationship type?
   Relationship type defines a set of associations or a relationship set among a
   given set of entity types.

   27. What is degree of Relationship type?
   It is the number of entity type participating.

   28. What is DDL (Data Definition Language)?
   A data base schema is specifies by a set of definitions expressed by a special
   language called DDL.

   29. What is VDL (View Definition Language)?
   It specifies user views and their mappings to the conceptual schema.
   30. What is SDL (Storage Definition Language)?
   This language is to specify the internal schema. This language may specify the
   mapping between two schemas.
DBMS                                                                     LAB MANUAL       157


   31. What is Data Storage - Definition Language?
   The storage structures and access methods used by database system are
   specified by a set of definition in a special type of DDL called data storage-
   definition language.

   32. What is DML (Data Manipulation Language)?
   This language that enable user to access or manipulate data as organised by
   appropriate data model.
   Ø Procedural DML or Low level: DML requires a user to specify what data are
   needed and how to get those data.
   Ø Non-Procedural DML or High level: DML requires a user to specify what
   data are needed without specifying how to get those data.

   33. What is DML Compiler?
   It translates DML statements in a query language into low-level instruction that
   the query evaluation engine can understand.

   34. What is Query evaluation engine?
   It executes low-level instruction generated by compiler.

   35. What is DDL Interpreter?
   It interprets DDL statements and record them in tables containing metadata.

   36. What is Record-at-a-time?
   The Low level or Procedural DML can specify and retrieve each record from a
   set of records. This retrieve of a record is said to be Record-at-a-time.

   37. What is Set-at-a-time or Set-oriented?
   The High level or Non-procedural DML can specify and retrieve many records
   in a single DML statement. This retrieve of a record is said to be Set-at-a-time
   or Set-oriented.

   38. What is Relational Algebra?
   It is procedural query language. It consists of a set of operations that take one or
   two relations as input and produce a new relation.
   39. What is Relational Calculus?
   It is an applied predicate calculus specifically tailored for relational databases
   proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.
DBMS                                                                       LAB MANUAL     158


   40. How does Tuple-oriented relational calculus differ from domain-
   oriented relational calculus
   The tuple-oriented calculus uses a tuple variables i.e., variable whose only
   permitted values are tuples of that relation. E.g. QUEL
   The domain-oriented calculus has domain variables i.e., variables that range
   over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

   41. What is normalization?
   It is a process of analysing the given relation schemas based on their Functional
   Dependencies (FDs) and primary key to achieve the properties
   Ø Minimizing redundancy
   Ø Minimizing insertion, deletion and update anomalies.

   42. What is Functional Dependency?
   A Functional dependency is denoted by X Y between two sets of attributes X
   and Y that are subsets of R specifies a constraint on the possible tuple that can
   form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if
   t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X
   component of a tuple uniquely determines the value of component Y.

   43. When is a functional dependency F said to be minimal?
   Ø Every dependency in F has a single attribute for its right hand side.
   Ø We cannot replace any dependency X A in F with a dependency Y A where
   Y is a proper subset of X and still have a set of dependency that is equivalent to
   F.
   Ø We cannot remove any dependency from F and still have set of dependency
   that is equivalent to F.

   44. What is Multivalued dependency?
   Multivalued dependency denoted by X Y specified on relation schema R, where
   X and Y are both subsets of R, specifies the following constraint on any relation
   r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4
   should also exist in r with the following properties
   Ø t3[x] = t4[X] = t1[X] = t2[X]
   Ø t3[Y] = t1[Y] and t4[Y] = t2[Y]
   Ø t3[Z] = t2[Z] and t4[Z] = t1[Z]
   where [Z = (R-(X U Y)) ]
DBMS                                                                    LAB MANUAL    159
   45. What is Lossless join property?
   It guarantees that the spurious tuple generation does not occur with respect to
   relation schemas after decomposition.

   46. What is 1 NF (Normal Form)?
   The domain of attribute must include only atomic (simple, indivisible) values.

   47. What is Fully Functional dependency?
   It is based on concept of full functional dependency. A functional dependency
   X Y is full functional dependency if removal of any attribute A from X means
   that the dependency does not hold any more.

   48. What is 2NF?
   A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in
   R is fully functionally dependent on primary key.

   49. What is 3NF?
   A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the
   following is true
   Ø X is a Super-key of R.
   Ø A is a prime attribute of R.
   In other words, if every non prime attribute is non-transitively dependent on
   primary key.

   50. What is BCNF (Boyce-Codd Normal Form)?
   A relation schema R is in BCNF if it is in 3NF and satisfies an additional
   constraint that for every FD X A, X must be a candidate key.


   51. What is 4NF?
   A relation schema R is said to be in 4NF if for every Multivalued dependency X
   Y that holds over R, one of following is true
   Ø X is subset or equal to (or) XY = R.
   Ø X is a super key.

   52. What is 5NF?
   A Relation schema R is said to be 5NF if for every join dependency {R1, R2,
   ..., Rn} that holds R, one the following is true
   Ø Ri = R for some i.
DBMS                                                                    LAB MANUAL    160
   Ø The join dependency is implied by the set of FD, over R in which the left side
   is key of R.
   53. What is Domain-Key Normal Form?
   A relation is said to be in DKNF if all constraints and dependencies that should
   hold on the the constraint can be enforced by simply enforcing the domain
   constraint and key constraint on the relation.

   54. What are partial, alternate,, artificial, compound and natural key?
   Partial Key:
   It is a set of attributes that can uniquely identify weak entities and that are
   related to same owner entity. It is sometime called as Discriminator.
   Alternate Key:
   All Candidate Keys excluding the Primary Key are known as Alternate Keys.
   Artificial Key:
   If no obvious key, either stand alone or compound is available, then the last
   resort is to simply create a key, by assigning a unique number to each record or
   occurrence. Then this is known as developing an artificial key.
   Compound Key:
   If no single data element uniquely identifies occurrences within a construct,
   then combining multiple elements to create a unique identifier for the construct
   is known as creating a compound key.
   Natural Key:
   When one of the data elements stored within a construct is utilized as the
   primary key, then it is called the natural key.

   55. What is indexing and what are the different kinds of indexing?
   Indexing is a technique for determining how quickly specific data can be found.
   Types:
   Ø Binary search style indexing
   Ø B-Tree indexing
   Ø Inverted list indexing
   Ø Memory resident table
   Ø Table indexing

   56. What is system catalog or catalog relation? How is better known as?
   A RDBMS maintains a description of all the data that it contains, information
   about every relation and index that it contains. This information is stored in a
   collection of relations maintained by the system called metadata. It is also
   called data dictionary.
DBMS                                                                      LAB MANUAL      161
   57. What is meant by query optimization?
   The phase that identifies an efficient execution plan for evaluating a query that
   has the least estimated cost is referred to as query optimization.

   58. What is join dependency and inclusion dependency?
   Join Dependency:
   A Join dependency is generalization of Multivalued dependency.A JD {R1, R2,
   ..., Rn} is said to hold over a relation R if R1, R2, R3, ..., Rn is a lossless-join
   decomposition of R . There is no set of sound and complete inference rules for
   JD.
   Inclusion Dependency:
   An Inclusion Dependency is a statement of the form that some columns of a
   relation are contained in other columns. A foreign key constraint is an example
   of inclusion dependency.

   59. What is durability in DBMS?
   Once the DBMS informs the user that a transaction has successfully completed,
   its effects should persist even if the system crashes before all its changes are
   reflected on disk. This property is called durability.

   60. What do you mean by atomicity and aggregation?
   Atomicity:
   Either all actions are carried out or none are. Users should not have to worry
   about the effect of incomplete transactions. DBMS ensures this by undoing the
   actions of incomplete transactions.
   Aggregation:
   A concept which is used to model a relationship between a collection of entities
   and relationships. It is used when we need to express a relationship among
   relationships.
   61. What is a Phantom Deadlock?
   In distributed deadlock detection, the delay in propagating local information
   might cause the deadlock detection algorithms to identify deadlocks that do not
   really exist. Such situations are called phantom deadlocks and they lead to
   unnecessary aborts.

   62. What is a checkpoint and When does it occur?
   A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the
   DBMS can reduce the amount of work to be done during restart in the event of
   subsequent crashes.
DBMS                                                                   LAB MANUAL      162
   63. What are the different phases of transaction?
   Different phases are
   Ø Analysis phase
   Ø Redo Phase
   Ø Undo phase

   64. What do you mean by flat file database?
   It is a database in which there are no programs or user access languages. It has
   no cross-file capabilities but is user-friendly and provides user-interface
   management.

   65. What is "transparent DBMS"?
   It is one, which keeps its Physical Structure hidden from user.

   66. Brief theory of Network, Hierarchical schemas and their properties
   Network schema uses a graph data structure to organize records example for
   such a database management system is CTCG while a hierarchical schema uses
   a tree data structure example for such a system is IMS.

   67. What is a query?
   A query with respect to DBMS relates to user commands that are used to
   interact with a data base. The query language can be classified into data
   definition language and data manipulation language.

   68. What do you mean by Correlated subquery?
   Subqueries, or nested queries, are used to bring back a set of rows to be used by
   the parent query. Depending on how the subquery is written, it can be executed
   once for the parent query or it can be executed once for each row returned by
   the parent query. If the subquery is executed for each row of the parent, this is
   called a correlated subquery.
   A correlated subquery can be easily identified if it contains any references to
   the parent subquery columns in its WHERE clause. Columns from the subquery
   cannot be referenced anywhere else in the parent query. The following example
   demonstrates a non-correlated subquery.
   E.g. Select * From CUST Where '10/03/1990' IN (Select ODATE From
   ORDER Where CUST.CNUM = ORDER.CNUM)

   69. What are the primitive operations common to all record management
   systems?
   Addition, deletion and modification.
DBMS                                                                      LAB MANUAL     163
   70. Name the buffer in which all the commands that are typed in are stored
   ‘Edit’ Buffer

   71. What are the unary operations in Relational Algebra?
   PROJECTION and SELECTION.

   72. Are the resulting relations of PRODUCT and JOIN operation the
   same?
   No.
   PRODUCT: Concatenation of every row in one relation with every row in
   another.
   JOIN: Concatenation of rows from one relation and related rows from another.

   73. What is RDBMS KERNEL?
   Two important pieces of RDBMS architecture are the kernel, which is the
   software, and the data dictionary, which consists of the system-level data
   structures used by the kernel to manage the database
   You might think of an RDBMS as an operating system (or set of subsystems),
   designed specifically for controlling data access; its primary functions are
   storing, retrieving, and securing data. An RDBMS maintains its own list of
   authorized users and their associated privileges; manages memory caches and
   paging; controls locking for concurrent resource usage; dispatches and
   schedules user requests; and manages space usage within its table-space
   structures.

   74. Name the sub-systems of a RDBMS
   I/O, Security, Language Processing, Process Control, Storage Management,
   Logging and Recovery, Distribution Control, Transaction Control, Memory
   Management, Lock Management

   75. Which part of the RDBMS takes care of the data dictionary? How
   Data dictionary is a set of tables and database objects that is stored in a special
   area of the database and maintained exclusively by the kernel.

   76. What is the job of the information stored in data-dictionary?
   The information in the data dictionary validates the existence of the objects,
   provides access to them, and maps the actual physical storage location.
DBMS                                                                     LAB MANUAL      164
   77. Not only RDBMS takes care of locating data it also
   determines an optimal access path to store or retrieve the data
   76. How do you communicate with an RDBMS?
   You communicate with an RDBMS using Structured Query Language (SQL)

   78. Define SQL and state the differences between SQL and other
   conventional programming Languages
   SQL is a nonprocedural language that is designed specifically for data access
   operations on normalized relational database structures. The primary difference
   between SQL and other conventional programming languages is that SQL
   statements specify what data operations should be performed rather than how to
   perform them.

   79. Name the three major set of files on disk that compose a database in
   Oracle
   There are three major sets of files on disk that compose a database. All the files
   are binary. These are
   Ø Database files
   Ø Control files
   Ø Redo logs
   The most important of these are the database files where the actual data resides.
   The control files and the redo logs support the functioning of the architecture
   itself.
   All three sets of files must be present, open, and available to Oracle for any data
   on the database to be useable. Without these files, you cannot access the
   database, and the database administrator might have to recover some or all of
   the database using a backup, if there is one.

   80. What is an Oracle Instance?
   The Oracle system processes, also known as Oracle background processes,
   provide functions for the user processes—functions that would otherwise be
   done by the user processes themselves
   Oracle database-wide system memory is known as the SGA, the system global
   area or shared global area. The data and control structures in the SGA are
   shareable, and all the Oracle background processes and user processes can use
   them.
   The combination of the SGA and the Oracle background processes is known as
   an Oracle instance
DBMS                                                                    LAB MANUAL      165
   81. What are the four Oracle system processes that must always be up and
   running for the database to be useable
   The four Oracle system processes that must always be up and running for the
   database to be useable include DBWR (Database Writer), LGWR (Log Writer),
   SMON (System Monitor), and PMON (Process Monitor).
   82. What are database files, control files and log files. How many of these
   files should a database have at least? Why?
   Database Files
   The database files hold the actual data and are typically the largest in size.
   Depending on their sizes, the tables (and other objects) for all the user accounts
   can go in one database file—but that's not an ideal situation because it does not
   make the database structure very flexible for controlling access to storage for
   different users, putting the database on different disk drives, or backing up and
   restoring just part of the database.
   You must have at least one database file but usually, more than one files are
   used. In terms of accessing and using the data in the tables and other objects,
   the number (or location) of the files is immaterial.
   The database files are fixed in size and never grow bigger than the size at which
   they were created Control Files
   The control files and redo logs support the rest of the architecture. Any database
   must have at least one control file, although you typically have more than one to
   guard against loss. The control file records the name of the database, the date
   and time it was created, the location of the database and redo logs, and the
   synchronization information to ensure that all three sets of files are always in
   step. Every time you add a new database or redo log file to the database, the
   information is recorded in the control files.
   Redo Logs
   Any database must have at least two redo logs. These are the journals for the
   database; the redo logs record all changes to the user objects or system objects.
   If any type of failure occurs, the changes recorded in the redo logs can be used
   to bring the database to a consistent state without losing any committed
   transactions. In the case of non-data loss failure, Oracle can apply the
   information in the redo logs automatically without intervention from the DBA.
   The redo log files are fixed in size and never grow dynamically from the size at
   which they were created.

   83. What is ROWID?
   The ROWID is a unique database-wide physical address for every row on every
   table. Once assigned (when the row is first inserted into the database), it never
DBMS                                                                   LAB MANUAL      166
   changes until the row is deleted or the table is dropped.
   The ROWID consists of the following three components, the combination of
   which uniquely identifies the physical storage location of the row.
   Ø Oracle database file number, which contains the block with the rows
   Ø Oracle block address, which contains the row
   Ø The row within the block (because each block can hold many rows)
   The ROWID is used internally in indexes as a quick means of retrieving rows
   with a particular key value. Application developers also use it in SQL
   statements as a quick way to access a row once they know the ROWID
   84. What is Oracle Block? Can two Oracle Blocks have the same address?
   Oracle "formats" the database files into a number of Oracle blocks when they
   are first created—making it easier for the RDBMS software to manage the files
   and easier to read data into the memory areas.
   The block size should be a multiple of the operating system block size.
   Regardless of the block size, the entire block is not available for holding data;
   Oracle takes up some space to manage the contents of the block. This block
   header has a minimum size, but it can grow.
   These Oracle blocks are the smallest unit of storage. Increasing the Oracle
   block size can improve performance, but it should be done only when the
   database is first created.
   Each Oracle block is numbered sequentially for each database file starting at 1.
   Two blocks can have the same block address if they are in different database
   files.




   85. What is database Trigger?
   A database trigger is a PL/SQL block that can defined to automatically execute
   for insert, update, and delete statements against a table. The trigger can e
   defined to execute once for the entire statement or once for every row that is
   inserted, updated, or deleted. For any one table, there are twelve events for
   which you can define database triggers. A database trigger can call database
   procedures that are also written in PL/SQL.

   86. Name two utilities that Oracle provides, which are use for backup and
   recovery.
   Along with the RDBMS software, Oracle provides two utilities that you can use
DBMS                                                                    LAB MANUAL     167
   to back up and restore the database. These utilities are Export and Import.
   The Export utility dumps the definitions and data for the specified part of the
   database to an operating system binary file. The Import utility reads the file
   produced by an export, recreates the definitions of objects, and inserts the data
   If Export and Import are used as a means of backing up and recovering the
   database, all the changes made to the database cannot be recovered since the
   export was performed. The best you can do is recover the database to the time
   when the export was last performed.

   87. What are stored-procedures? And what are the advantages of using
   them.
   Stored procedures are database objects that perform a user defined operation. A
   stored procedure can have a set of compound SQL statements. A stored
   procedure executes the SQL commands and returns the result to the client.
   Stored procedures are used to reduce network traffic.

   88. How are exceptions handled in PL/SQL? Give some of the internal
   exceptions' name
   PL/SQL exception handling is a mechanism for dealing with run-time errors
   encountered during procedure execution. Use of this mechanism enables
   execution to continue if the error is not severe enough to cause procedure
   termination.
   The exception handler must be defined within a subprogram specification.
   Errors cause the program to raise an exception with a transfer of control to the
   exception-handler block. After the exception handler executes, control returns
   to the block in which the handler was defined. If there are no more executable
   statements in the block, control returns to the caller.
   User-Defined Exceptions
   PL/SQL enables the user to define exception handlers in the declarations area of
   subprogram specifications. User accomplishes this by naming an exception as
   in the following example:
   ot_failure EXCEPTION;
   In this case, the exception name is ot_failure. Code associated with this handler
   is written in the EXCEPTION specification area as follows:
   EXCEPTION
   when OT_FAILURE then
   out_status_code := g_out_status_code;
   out_msg := g_out_msg;
   The following is an example of a subprogram exception:
   EXCEPTION
DBMS                                                                   LAB MANUAL     168
   when NO_DATA_FOUND then
   g_out_status_code := 'FAIL';
   RAISE ot_failure;
   Within this exception is the RAISE statement that transfers control back to the
   ot_failure exception handler. This technique of raising the exception is used to
   invoke all user-defined exceptions.
   System-Defined Exceptions
   Exceptions internal to PL/SQL are raised automatically upon error.
   NO_DATA_FOUND is a system-defined exception. Table below gives a
   complete list of internal exceptions.

   PL/SQL internal exceptions.
   PL/SQL internal exceptions.

   Exception Name Oracle Error
   CURSOR_ALREADY_OPEN ORA-06511
   DUP_VAL_ON_INDEX ORA-00001
   INVALID_CURSOR ORA-01001
   INVALID_NUMBER ORA-01722
   LOGIN_DENIED ORA-01017
   NO_DATA_FOUND ORA-01403
   NOT_LOGGED_ON ORA-01012
   PROGRAM_ERROR ORA-06501
   STORAGE_ERROR ORA-06500
   TIMEOUT_ON_RESOURCE ORA-00051
   TOO_MANY_ROWS ORA-01422
   TRANSACTION_BACKED_OUT ORA-00061
   VALUE_ERROR ORA-06502
   ZERO_DIVIDE ORA-01476

   In addition to this list of exceptions, there is a catch-all exception named
   OTHERS that traps all errors for which specific error handling has not been
   established.

   89. Does PL/SQL support "overloading"? Explain
   The concept of overloading in PL/SQL relates to the idea that you can define
   procedures and functions with the same name. PL/SQL does not look only at
   the referenced name, however, to resolve a procedure or function call. The
   count and data types of formal parameters are also considered.
DBMS                                                                    LAB MANUAL      169
   PL/SQL also attempts to resolve any procedure or function calls in locally
   defined packages before looking at globally defined packages or internal
   functions. To further ensure calling the proper procedure, you can use the dot
   notation. Prefacing a procedure or function name with the package name fully
   qualifies any procedure or function reference.

   90. Tables derived from the ERD
   a) Are totally unnormalised
   b) Are always in 1NF
   c) Can be further denormalised
   d) May have multi-valued attributes

   (b) Are always in 1NF


   91. Spurious tuples may occur due to
   i. Bad normalization
   ii. Theta joins
   iii. Updating tables from join
   a) i & ii b) ii & iii
   c) i & iii d) ii & iii

   (a) i & iii because theta joins are joins made on keys that are not primary keys.

   92. A B C is a set of attributes. The functional dependency is as follows
   AB -> B
   AC -> C
   C -> B
   a) is in 1NF
   b) is in 2NF
   c) is in 3NF
   d) is in BCNF

   (a) is in 1NF since (AC)+ = { A, B, C} hence AC is the primary key. Since C B
   is a FD given, where neither C is a Key nor B is a prime attribute, this it is not
   in 3NF. Further B is not functionally dependent on key AC thus it is not in 2NF.
   Thus the given FDs is in 1NF.

   93. In mapping of ERD to DFD
   a) entities in ERD should correspond to an existing entity/store in DFD
DBMS                                                                   LAB MANUAL     170
   b) entity in DFD is converted to attributes of an entity in ERD
   c) relations in ERD has 1 to 1 correspondence to processes in DFD
   d) relationships in ERD has 1 to 1 correspondence to flows in DFD

   (a) entities in ERD should correspond to an existing entity/store in DFD

   94. A dominant entity is the entity
   a) on the N side in a 1 : N relationship
   b) on the 1 side in a 1 : N relationship
   c) on either side in a 1 : 1 relationship
   d) nothing to do with 1 : 1 or 1 : N relationship

   (b) on the 1 side in a 1 : N relationship
   95. Select 'NORTH', CUSTOMER From CUST_DTLS Where REGION =
   'N' Order By
   CUSTOMER Union Select 'EAST', CUSTOMER From CUST_DTLS
   Where REGION = 'E' Order By CUSTOMER
   The above is
   a) Not an error
   b) Error - the string in single quotes 'NORTH' and 'SOUTH'
   c) Error - the string should be in double quotes
   d) Error - ORDER BY clause

   (d) Error - the ORDER BY clause. Since ORDER BY clause cannot be used in
   UNIONS

   96. What is Storage Manager?
   It is a program module that provides the interface between the low-level data
   stored in database, application programs and queries submitted to the system.


   97. What is Buffer Manager?
   It is a program module, which is responsible for fetching data from disk storage
   into main memory and deciding what data to be cache in memory.

   98. What is Transaction Manager?
   It is a program module, which ensures that database, remains in a consistent
   state despite system failures and concurrent transaction execution proceeds
   without conflicting.
DBMS                                                                     LAB MANUAL      171
   99. What is File Manager?
   It is a program module, which manages the allocation of space on disk storage
   and data structure used to represent information stored on a disk.

   100. What is Authorization and Integrity manager?
   It is the program module, which tests for the satisfaction of integrity constraint
   and checks the authority of user to access data.

   101. What are stand-alone procedures?
   Procedures that are not part of a package are known as stand-alone because they
   independently defined. A good example of a stand-alone procedure is one
   written in a SQL*Forms application. These types of procedures are not
   available for reference from other Oracle tools. Another limitation of stand-
   alone procedures is that they are compiled at run time, which slows execution.
   102. What are cursors give different types of cursors.
   PL/SQL uses cursors for all database information accesses statements. The
   language supports the use two types of cursors
   Ø Implicit
   Ø Explicit

   103. What is cold backup and hot backup (in case of Oracle)?
   Ø Cold Backup:
   It is copying the three sets of files (database files, redo logs, and control file)
   when the instance is shut down. This is a straight file copy, usually from the
   disk directly to tape. You must shut down the instance to guarantee a consistent
   copy.
   If a cold backup is performed, the only option available in the event of data file
   loss is restoring all the files from the latest backup. All work performed on the
   database since the last backup is lost.
   Ø Hot Backup:
   Some sites (such as worldwide airline reservations systems) cannot shut down
   the database while making a backup copy of the files. The cold backup is not an
   available option.
   So different means of backing up database must be used — the hot backup.
   Issue a SQL command to indicate to Oracle, on a tablespace-by-tablespace
   basis, that the files of the tablespace are to backed up. The users can continue to
   make full use of the files, including making changes to the data. Once the user
   has indicated that he/she wants to back up the tablespace files, he/she can use
   the operating system to copy those files to the desired backup destination.
DBMS                                                                      LAB MANUAL      172
   The database must be running in ARCHIVELOG mode for the hot backup
   option.
   If a data loss failure does occur, the lost database files can be restored using the
   hot backup and the online and offline redo logs created since the backup was
   done. The database is restored to the most consistent state without any loss of
   committed transactions.

   104. What are Armstrong rules? How do we say that they are complete
   and/or sound
   The well-known inference rules for FDs
   Ø Reflexive rule :
   If Y is subset or equal to X then X Y.
   Ø Augmentation rule:
   If X Y then XZ YZ.
   Ø Transitive rule:
   If {X Y, Y Z} then X Z.
   Ø Decomposition rule :
   If X YZ then X Y.
   Ø Union or Additive rule:
   If {X Y, X Z} then X YZ.
   Ø Pseudo Transitive rule :
   If {X Y, WY Z} then WX Z.
   Of these the first three are known as Amstrong Rules. They are sound because it
   is enough if a set of FDs satisfy these three. They are called complete because
   using these three rules we can generate the rest all inference rules.

   105. How can you find the minimal key of relational schema?
   Minimal key is one which can identify each tuple of the given relation schema
   uniquely. For finding the minimal key it is required to find the closure that is
   the set of all attributes that are dependent on any given set of attributes under
   the given set of functional dependency.
   Algo. I Determining X+, closure for X, given set of FDs F
   1. Set X+ = X
   2. Set Old X+ = X+
   3. For each FD Y Z in F and if Y belongs to X+ then add Z to X+
   4. Repeat steps 2 and 3 until Old X+ = X+

   Algo.II Determining minimal K for relation schema R, given set of FDs F
   1. Set K to R that is make K a set of all attributes in R
DBMS                                                                    LAB MANUAL     173
   2. For each attribute A in K
   a. Compute (K – A)+ with respect to F
   b. If (K – A)+ = R then set K = (K – A)+

   106. What do you understand by dependency preservation?
   Given a relation R and a set of FDs F, dependency preservation states that the
   closure of the union of the projection of F on each decomposed relation Ri is
   equal to the closure of F. i.e.,
   ((PR1(F)) U … U (PRn(F)))+ = F+
   if decomposition is not dependency preserving, then some dependency is lost in
   the decomposition.
   107. What is meant by Proactive, Retroactive and Simultaneous Update.
   Proactive Update:
   The updates that are applied to database before it becomes effective in real
   world .
   Retroactive Update:
   The updates that are applied to database after it becomes effective in real world
   .
   Simulatneous Update:
   The updates that are applied to database at the same time when it becomes
   effective in real world .

   108. What are the different types of JOIN operations?
   Equi Join: This is the most common type of join which involves only equality
   comparisions. The disadvantage in this type of join is that there
DBMS                                           LAB MANUAL   174




       EMAIL:     cserockz08@gmail.com

              www.cserockz.com
       Keep Watching for Regular Updates….!!

								
To top