DBMS-TRIGGERS

                                    TRIGGERS

 AIM:

To perform High-level language extension with triggers (Student mark list).

SYNTAX:

CREATE[OR REPLACE]TRIGGER[schema.]trigger

{BEFORE|AFTER|INSTEAD OF}

{DELETE

|INSERT

|UPDATE[OF column[,column].....]}

|OR {DELETE

|INSERT

|UPDATE[OF column,[,column]....]}]...

ON[schema.]{table|view}

REFERENCING(OLD[AS]old

|NEW[AS]new...]

FOR EACH{ROW|STATEMENT}{WHEN(condition)}]

DESCRIPTION:

            The details about the students mark are being in the stu table with attributes.

            Name

            Rollno

            Mark1

            Mark2

            Mark3

The stu1 table consists of attributes,

            Rollno

            Total

            Average

            Result

The details of the first table are being given in the prompt by the user.  The total, average are processed for currently processed row in the stu table using after insert on trigger the values are placed in the table stu1.

PROGRAM:

create or replace trigger st_trig

after insert on stu

for each row

 declare

 total number;

 average number;

 result varchar2(10);

 begin

 total:=(:new.m1+:new.m2+:new.m3);

 average:=total/3;

 if(:new.m1>50 and :new.m2>50 and :new.m3>50) then

 result:='pass';

 else

result:='fail';

 end if;

 insert into stu1 values(:new.rollno,total,average,result);

 end;

SQL> /

Trigger created.

SQL> desc stu;

 Name                                      Null?    Type

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

 NAME                                                 VARCHAR2(20)

 ROLLNO                                             NUMBER

 M1                                                        NUMBER

 M2                                                        NUMBER

 M3                                                        NUMBER

SQL> desc stu1;

 Name                                      Null?    Type

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

 ROLLNO                                              NUMBER

 TOTAL                                                 NUMBER

 AVERAGE                                           NUMBER

 RESULT                                               VARCHAR2(10)

SAMPLE OUTPUT:

BEFORE TRIGGER CREATION:

SQL> select * from stu;

no rows selected

SQL> select * from stu1;

no rows selected

AFTER TRIGGER CREATION:

SQL> insert into stu values('&name',&rollno,&m1,&m2,&m3);

Enter value for name: GOWTHAM

Enter value for rollno: 1212

Enter value for m1: 100

Enter value for m2: 89

Enter value for m3: 90

old   1: insert into stu values('&name',&rollno,&m1,&m2,&m3)

new   1: insert into stu values('GOWTHAM',1212,100,89,90)

1 row created.

SQL> select * from stu;

NAME                     ROLLNO         M1         M2         M3

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

GOWTHAM               1212             100         89         90

GOKILA                     1213               89         67         55

RAMYA                     1214               45         67         78

SQL> select * from stu1;

    ROLLNO      TOTAL    AVERAGE RESULT

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

      1212             279             139.5           pass

      1213             211             105.5           pass

      1214             190               95               fail

RESULT:

Thus the high-level language extension with triggers has been performed for generating the students mark list.

DBMS-FUNCTIONS and PROCEDURES

EX.NO:5                                FUNCTIONS AND PROCEDURE

AIM:

            To write PL/SQL(Functions)and to understand stored procedures in SQL.

FUNCTION:

            A function is a subprogram that computes a value. The syntax for creating a function is given below

Create or replace function<function_name>[argument]

Return datatype is

(local declaration)

begin

            (executable statements)

[Exception]

            (exception handlers)

end

PROCEDURE:

CREATE [ORREPLACE] PROCEDURE PROCEDURENAME

                        [PARAMETER[IN/OUT/IN/IN OUT] DATATYPE

                        [:=/DEFAULT EXPRESSION]

                        [(PARAMETER)]

                        IS/AS

                        DECLARATION

                        BEGIN

                        PL/SQL CODES

                        [EXCEPTION]

                        END

Sample Output

           *****FUCTION USING FOR STATEMENT*****

  SQL>create  or replace function fact(a number)return number as

  2  i number;

  3  f number;

  4  begin

  5  f:=1;

  6  for i in 1..a

  7  loop

  8  f:=f*i;

  9  end loop;

 10  return f;

 11* end fact;

SQL> /

Function created.

*********FUNCTION USING WHILE STATEMENT *************

SQL> create or replace function fact(a number) return number as

  2   i number;

  3   f number;

  4   begin

  5   f:=1;

  6   i:=1;

  7   while (i<=a)

  8   loop

  9   f:=f*i;

 10   i:=i+1;

 11   end loop;

 12   return f;

 13*  end fact;

 14  /

Function created.

SQL>begin

  2  dbms_output.put_line('the factorial='||fact(&a));

  3* end;

SQL> /

Enter value for a: 4

old   2: dbms_output.put_line('the factorial='||fact(&a))

new   2: dbms_output.put_line('the factorial='||fact(4));

the factorial=24

PL/SQL procedure successfully completed.

*****PROCEDURE TO FIND WHETHER A GIVEN NUMBER IS ODD OR EVEN*********

SQL> declare

  2  n number;

  3  begin

  4  n:=&n;

  5  if(mod(n,2)=0)then

  6  dbms_output.put_line(n||'is even');

  7  else

  8  dbms_output.put_line(n||'is odd');

  9  end if;

 10  end;

 11  /

Enter value for n: 3

old   4: n:=&n;

new   4: n:=3;

3is odd

PL/SQL procedure successfully completed.

**********PROCEDURE TO DISPLAY 1-10 USING WHILE*******

  1   declare

  2   n number;

  3   i number;

  4   begin

  5   n:=10;

  6   i:=1;

  7   while (i<=n)

  8   loop

  9   dbms_output.put_line(i);

 10   i:=i+1;

 11   end loop;

 12*  end;

SQL> /

1

2

3

4

5

6

7

8

9

10

PL/SQL procedure successfully completed.

***********Procedure to display some numbers lesser than given number**********

  1  declare

  2  num number;

  3  i number;

  4  begin

  5  num:=&num;

  6  i:=1;

  7  loop

  8  dbms_output.put_line(i);

  9  exit when(i>num);

 10  i:=i+1;

 11  end loop;

 12* end;

SQL> /

Enter value for num: 4

old   5: num:=&num;

new   5: num:=4;

1

2

3

4

5

PL/SQL procedure successfully completed.

RESULT: Thus the functions and stored procedures are executed in SQL.

DBMS-VIEWS

EX.NO:4                                                VIEWS

AIM:

To study and create View commands

VIEWS:

In SQL, a view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

                                                      ************

                                                              VIEWS

                                                       ************

                      TABLE 1 CREATION

                      ***************

                     

`                    SQL> create table aa(name varchar2(20),book number(10),edition number(20),price number(20),

         ISBN number(20));

                      Table created.

                      SQL> insert into aa values('&name',&number,&edition,&price,&ISBN);

                      Enter value for name: bb

                      Enter value for number: 23

                      Enter value for edition: 2001

                      Enter value for price: 12

                      Enter value for isbn: 23435

                      old   1: insert into aa values('&name',&number,&edition,&price,&ISBN)

                      new   1: insert into aa values('bb',23,2001,12,23435)

                      1 row created.

                      SQL> /

                      Enter value for name: cc

                      Enter value for number: 55

                      Enter value for edition: 342

                      Enter value for price: 76

                      Enter value for isbn: 687478

                      old   1: insert into aa values('&name',&number,&edition,&price,&ISBN)

                      new   1: insert into aa values('cc',55,342,76,687478)

                      1 row created.

                      SQL> /

                      Enter value for name: dd

                      Enter value for number: 2

                      Enter value for edition: 1233

                      Enter value for price: 123

                      Enter value for isbn: 53616578

                      old   1: insert into aa values('&name',&number,&edition,&price,&ISBN)

                      new   1: insert into aa values('dd',2,1233,123,53616578)

                      1 row created.

                      SQL> /

                      Enter value for name: ee

                      Enter value for number: 21

                      Enter value for edition: 1111

                      Enter value for price: 111

                      Enter value for isbn: 12435798

                      old   1: insert into aa values('&name',&number,&edition,&price,&ISBN)

                      new   1: insert into aa values('ee',21,1111,111,12435798)

                      1 row created.

                      TABLE 2 CREATION

                      *****************

                      SQL> create table qq(name varchar2(20),book number(10),author varchar(20),publisher varchar2(20),

                      ISBN number(20));

                      Table created.

                      SQL> select * from aa;

                      NAME                       BOOK    EDITION      PRICE       ISBN

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

                      bb                           23       2001         12      23435

                      cc                           55        342         76     687478

                      dd                            2       1233        123   53616578

                      ee                           21       1111        111   12435798

                      SQL> insert into qq values('&name','&author',&number,'&publisher',&ISBN);

                      Enter value for name: bb

                      Enter value for author: 21

                      Enter value for number: 23

                      Enter value for publisher: dfd

                      Enter value for isbn: 573568

                      old   1: insert into qq values('&name','&author',&number,'&publisher',&ISBN)

                      new   1: insert into qq values('bb','21',23,'dfd',573568)

                      1 row created.

                      SQL> /

                      Enter value for name: cc

                      Enter value for author: 43

                      Enter value for number: 55

                      Enter value for publisher: fg

                      Enter value for isbn: 65839

                      old   1: insert into qq values('&name','&author',&number,'&publisher',&ISBN)

                      new   1: insert into qq values('cc','43',55,'fg',65839)

                      1 row created.

                      SQL> /

                      Enter value for name: ee

                      Enter value for author: 44

                      Enter value for number: 21

                      Enter value for publisher: dfd

                      Enter value for isbn: 1235798

                      old   1: insert into qq values('&name','&author',&number,'&publisher',&ISBN)

                      new   1: insert into qq values('ee','44',21,'dfd',1235798)

                      1 row created.

                      SQL> /

                      Enter value for name: oo

                      Enter value for author: 87

                      Enter value for number: 34

                      Enter value for publisher: gfh

                      Enter value for isbn: 6358379

                      old   1: insert into qq values('&name','&author',&number,'&publisher',&ISBN)

                      new   1: insert into qq values('oo','87',34,'gfh',6358379)

                      1 row created.

                      SQL> select * from qq;

                      NAME                 BOOK    AUTHOR           PUBLISHER

                      -------------------- ---------- -------------------- --------------------          ISBN

                      ----------

                      bb                           21 23                   dfd

                          573568

                      cc                           43 55                   fg

                          65839

                      ee                           44 21                   dfd

                        1235798

                      NAME                 BOOK     AUTHOR        PUBLISHER     ISBN

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

                      oo                           87 34                   gfh            6358379

                     

                      CREATE VIEW STATEMENT

                      **********************

                      SQL>create view ww as select book,name,publisher from qq where ISBN=573568

                      View created.

                      SQL> select * from ww;

                           BOOK NAME                 PUBLISHER

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

        21 bb                   dfd

                     

                      UPDETE VIEW STATEMENT

                      **********************

                      SQL> update ww set publisher='qwa'where book=21;

                     

                     

                      1 row updated.

                      SQL> select * from ww;

                      BOOK NAME                 PUBLISHER

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

          21 bb                   qwa

                      SQL> create view wq as select name,ISBN,publisher from qq where book>21

                      View created.

                      SQL> select * from wq;

                      NAME                       ISBN PUBLISHER

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

                      cc                        65839 fg

                      ee                      1235798 dfd

                      oo                      6358379 gfh

                      SQL> create view ss as select name,book from aa union select name,book from qq;

                      View created.

                      SQL> select * from ss;

                      NAME                       BOOK

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

                      bb                           21

                      bb                           23

                      cc                           43

                      cc                           55

                      dd                            2

                      ee                           21

                      ee                           44

                      oo                           87

                      8 rows selected.

                      COMPLEX VIEW

                      *************

                      SQL> create view er as select author,name,ISBN from qq where book>43;

                      View created.

                      SQL> select * from er;

                      AUTHOR               NAME                       ISBN

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

                      21                   ee                      1235798

                      34                   oo                      6358379

                      SQL>select name from(select * from qq where publisher='fg')where ISBN=65839;

                      NAME

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

                      Cc

DROP VIEW

*************

SQL> drop view er;

View dropped

                     

Result

           

            Thus the view creation  commands are executed successfully.

DBMS-JOINS

************************

                                                            JOINS

                                                ************************

            SQL> create table emp(name varchar2(20),salary number(10));

            Table created.

            SQL> insert into emp values('&name',&salary);

            Enter value for name: ashu

            Enter value for salary: 10000

            old   1: insert into emp values('&name',&salary)

            new   1: insert into emp values('ashu',10000)

           

            1 row created.

            SQL> /

            Enter value for name: asma

            Enter value for salary: 1200

            old   1: insert into emp values('&name',&salary)

            new   1: insert into emp values('asma',1200)

            1 row created.

            SQL> /

            Enter value for name: asif

            Enter value for salary: 2000

            old   1: insert into emp values('&name',&salary)

            new   1: insert into emp values('asif',2000)

            1 row created.

            SQL> /

            Enter value for name: arif

            Enter value for salary: 1000

            old   1: insert into emp values('&name',&salary)

            new   1: insert into emp values('arif',1000)

            1 row created.

            SQL> /

            Enter value for name: niyas

            Enter value for salary: 3000

            old   1: insert into emp values('&name',&salary)

            new   1: insert into emp values('niyas',3000)

            1 row created.

            SQL> select * from emp;

            NAME                     SALARY

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

            ashu                      10000

            asma                       1200

            asif                       2000

            arif                       1000

            niyas                      3000

            SQL> create table emp1(name varchar2(20),empid number(10));

            Table created.

            SQL> insert into emp1 values('&name',&empid);

            Enter value for name: fathi

            Enter value for empid: 12

            old   1: insert into emp1 values('&name',&empid)

            new   1: insert into emp1 values('fathi',12)

            1 row created.

            SQL> /

            Enter value for name: sumi

            Enter value for empid: 32

            old   1: insert into emp1 values('&name',&empid)

            new   1: insert into emp1 values('sumi',32)

            1 row created.

            SQL> /

            Enter value for name: priya

            Enter value for empid: 11

            old   1: insert into emp1 values('&name',&empid)

            new   1: insert into emp1 values('priya',11)

            1 row created.

            SQL> /

            Enter value for name: wahab

            Enter value for empid: 10

            old   1: insert into emp1 values('&name',&empid)

            new   1: insert into emp1 values('wahab',10)

            1 row created.

            SQL> /

            Enter value for name: sweety

            Enter value for empid: 09

            old   1: insert into emp1 values('&name',&empid)

            new   1: insert into emp1 values('sweety',09)

            1 row created.

            SQL>/

            Enter value for name: asma

            Enter value for empid: 1200

            old   1: insert into emp1 values('&name',&empid)

            new   1: insert into emp1 values('asma',1200)

            1 row created.

            SQL> select * from emp1;

            NAME                      EMPID

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

            fathi                        12

            sumi                         32

            priya                        11

            wahab                        10

            sweety                        9

            asma                       1200

            6 rows selected.

            NATURAL JOIN

            ************

            SQL>select emp.name,salary from emp,emp1 where emp.name=emp1.name

            NAME                     SALARY

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

            asma                       1200

            LEFT OUTER JOIN

            ****************

            SQL>select emp.name,salary from emp left outer join emp1 on emp.name=emp1.name

            NAME                     SALARY

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

            asma                       1200

            asif                       2000

            arif                       1000

            niyas                      3000

            ashu                      10000

            RIGHT OUTER JOIN

            *****************

            SQL>select emp1.name,empid from emp right outer join emp1 on emp.name=emp1.name

            NAME                      EMPID

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

            asma                       1200

            sweety                        9

            sumi                         32

            wahab                        10

            fathi                        12

            priya                        11

            6 rows selected.

 

            FULL JOIN

            ***********

            SQL>select emp1.name,emp.name,emp1.empid,salary from emp full join emp1 on emp.name=emp1.name

            NAME                 NAME                      EMPID     SALARY

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

            asma                 asma                       1200       1200

                             asif                                  2000

                             arif                                  1000

                             niyas                                 3000

                              ashu                                 10000

            sweety                                             9

            sumi                                              32

            wahab                                             10

            fathi                                             12

            priya                                             11

            10 rows selected.

RESULT:

Thus the nested queries and join operations are executed and verifiedin DBMS.

DBMS-Nested Queries

EX.NO:3                                                         NESTED QUERIES AND JOINS

AIM:

            To execute nested queries and join commands in SQL.

NESTED QUERIES :

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

JOINS:

        Join  is a query in which data is returned from two or more tables.

How the join will be performed:

Step 1:  Make the Cartesian product of the given tables.

Step 2:   Check for the equality on common attributes for the given tables.

Natural join:

        It returns the matching rows from the table that are being joined.

Syntax:

       >select <attribute> from TN where TN1.attribute=TN2.attribute.

Inner join:

       It returns the matching rows from the table that are being joined.

Syntax:

       >select <attribute> from TN1 innerjoin TN2 on TN1.attribute=TN2.attribute.

Left  outer join:

        It returns all the rows from the table1 even when they are unmatched.

Syntax:

1.      select <attribute> from TN1 left outer join TN2 on TN1.attribute=TN2.attribute.

       2.   select <attribute> from TN where TN1.attribute(+)=TN2.attribute.

Right outer join:

        It returns all the rows from the table2 even when they are unmatched.

Syntax:

1.      select <attribute> from TN1 right outer join TN2 on TN1.attribute=TN2.attribute.

       2.   select <attribute> from TN where TN1.attribute=(+)TN2.attribute.

Full join:

       It is the combination of both left outer and right outer join.

Syntax:

       >select <attribute> from TN1 full join TN2 on TN1.attribute=TN2.attribute.

Sample Output

                                        

NESTED QUERIES  - Table Creation

SQL> create table emp_det(eno number(3) not null, ename varchar2(25),

            address varchar2(30),

            basic_sal number(12,2),job_status varchar2(15),dno number(3));

            Table created.

            SQL> create table pro_det(pno number(3) not null,pname varchar2(30),

            no_of_staff number(3));

            Table created.

            SQL> create table work_in(pno number(3),eno number(3),pjob char(12));

            Table created.

            SQL> desc emp_det;

            Name                                      Null?    Type

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

             ENO                                       NOT NULL NUMBER(3)

             ENAME                                                      VARCHAR2(25)

             ADDRESS                                                  VARCHAR2(30)

             BASIC_SAL                                               NUMBER(12,2)

             JOB_STATUS                                            VARCHAR2(15)

             DNO                                                            NUMBER(3)

            SQL> desc pro_det;

            Name                                      Null?    Type

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

            PNO                                       NOT NULL NUMBER(3)

            PNAME                                                      VARCHAR2(30)

            NO_OF_STAFF                                         NUMBER(3)

            SQL> desc work_in;

            Name                                      Null?    Type

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

            PNO                                                NUMBER(3)

            ENO                                                NUMBER(3)

            PJOB                                               CHAR(12)

            SQL> insert into emp_det values(&eno,'&ename','&address',&basic_sal,

            '&job_status',&dno);

            Enter value for eno: 1

            Enter value for ename: SaravanaKumar

            Enter value for address: GandhiNagar

            Enter value for basic_sal: 8000

            Enter value for job_status: Manager

            Enter value for dno: 10

            old   1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

            '&job_status',&dno)

            new   1: insert into emp_det values(1,'SaravanaKumar','GandhiNagar',

            8000,'Manager',10)

            1 row created.

            SQL> /

            Enter value for eno: 2

            Enter value for ename: Mahendran

            Enter value for address: RainbowColony

            Enter value for basic_sal: 5000

            Enter value for job_status: Supervisor

            Enter value for dno: 10

            old   1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

            '&job_status',&dno)

            new   1: insert into emp_det values(2,'Mahendran','RainbowColony',5000,

            'Supervisor',10)

            1 row created.

            SQL> /

            Enter value for eno: 3

            Enter value for ename:Rajkumar

            Enter value for address: EastCoastRoad

            Enter value for basic_sal: 10000

            Enter value for job_status: Professor

            Enter value for dno: 2

            old   1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

            '&job_status',&dno)

            new   1: insert into emp_det values(3,'RajKumar','EastCoastRoad',10000,

            'Professor',2)

            1 row created.

            SQL> /

            Enter value for eno: 4

            Enter value for ename: Shirley

            Enter value for address: KKnagar

            Enter value for basic_sal: 8000

            Enter value for job_status: AsstManager

            Enter value for dno: 3

            old   1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

            '&job_status',&dno)

            new   1: insert into emp_det values(4,'Shirley','KKnagar',8000,

            'AsstManager',3)

            1 row created.

            SQL> alter table emp_det modify(ename varchar2(15), address varchar2(15));

            Table altered.

            SQL> select * from emp_det;

                   ENO ENAME           ADDRESS          BASIC_SAL JOB_STATUS       DNO

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

             1 SaravanaKumar   GandhiNagar           8000 Manager            10

             2 Mahendran       RainbowColony         5000 Supervisor         10

             3 RajKumar        EastCoastRoad        10000 Professor           2

            4 Shirley         KKnagar               8000 AsstManager          3

            SQL> insert into pro_det values(&pno,'pname',&no_of_staff);

            Enter value for pno: 1

            Enter value for no_of_staff: 2

            old   1: insert into pro_det values(&pno,'pname',&no_of_staff)

            new   1: insert into pro_det values(1,'pname',2)

            1 row created.

            SQL> /

            Enter value for pno: 2

            Enter value for no_of_staff: 3

            old   1: insert into pro_det values(&pno,'pname',&no_of_staff)

            new   1: insert into pro_det values(2,'pname',3)

            1 row created.

            SQL> /

            Enter value for pno: 3

            Enter value for no_of_staff: 1

            old   1: insert into pro_det values(&pno,'pname',&no_of_staff)

            new   1: insert into pro_det values(3,'pname',1)

            1 row created.

            SQL> select * from pro_det;

                   PNO PNAME                          NO_OF_STAFF

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

                1 pname                                    2

                2 pname                                    3

                3 pname                                    1

            SQL>update pro_det set pname='DBMS' where pno=1;

            1 row updated.

            SQL> update pro_det set pname='COMPILER' where pno=2;

            1 row updated.

            SQL>update pro_det set pname='C' where pno=3;

            1 row updated.

            SQL> select * from Pro_det;

             PNO PNAME                          NO_OF_STAFF

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

         1 DBMS                                     2

         2 COMPILER                                 3

         3 C                                        1

            SQL> insert into work_in values(&pno,&eno,'&pjob');

            Enter value for pno: 1

            Enter value for eno: 1

            Enter value for pjob: Programmer

            old   1: insert into work_in values(&pno,&eno,'&pjob')

            new   1: insert into work_in values(1,1,'Programmer')

            1 row created.

            SQL> /

            Enter value for pno: 2

            Enter value for eno: 1

            Enter value for pjob: Analyst

            old   1: insert into work_in values(&pno,&eno,'&pjob')

            new   1: insert into work_in values(2,1,'Analyst')

            1 row created.

            SQL> /

            Enter value for pno: 1

            Enter value for eno: 2

            Enter value for pjob: Analyst

            old   1: insert into work_in values(&pno,&eno,'&pjob')

            new   1: insert into work_in values(1,2,'Analyst')

            1 row created.

            SQL> /

            Enter value for pno: 2

            Enter value for eno: 2

            Enter value for pjob: Programmer

            old   1: insert into work_in values(&pno,&eno,'&pjob')

            new   1: insert into work_in values(2,2,'Programmer')

            1 row created.

            SQL> select * from work_in;

           

       PNO        ENO PJOB

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

         1          1 Programmer

         2          1 Analyst

         1          2 Analyst

         2          2 Programmer

            NESTED QUERIES

            (i) SQL> select ename from emp_det where dno not in(select dno from

            emp_det where ename = 'SaravanaKumar');

            ENAME

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

            RajKumar

            Shirley

 

            (ii)SQL> select ename, dno from emp_det where dno = (select dno from

            emp_det where ename = 'RajKumar');

            ENAME                  DNO

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

            RajKumar                 2

            (iii)SQL> select ename from emp_det where eno in(select eno from work_in

             where pno = (select pno from

            pro_det where pname = 'DBMS')) order by ename;

            ENAME

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

            Mahendran

            SaravanaKumar

            (iv)SQL> select ename, basic_sal from emp_det where dno = 2 and

            basic_sal>(select max(basic_sal) from

            emp_det where dno = 10) order by ename;

            ENAME            BASIC_SAL

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

            RajKumar             10000

            (v)SQL> select pno,pname from pro_det where exists(select pno from

            work_in where work_in.pno = pro_det.

            pno);

       PNO PNAME

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

         1 DBMS

         2 COMPILER

           

            (vi)SQL>select ename, job_status,basic_sal from emp_det where (dno,basic_sal)

             in (select dno,basic_sal

            from emp_det where ename ='RajKumar');

            ENAME           JOB_STATUS       BASIC_SAL

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

            RajKumar        Professor            10000

            (vii)SQL>select * from emp_det where basic_sal=(select max(basic_sal) from emp_det);

       ENO ENAME           ADDRESS          BASIC_SAL JOB_STATUS             DNO

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

         3 RajKumar        EastCoastRoad        10000 Professor                2

            (viii)SQL>select max(basic_sal) from emp_det where basic_sal< (select max(basic_sal)

             from emp_det);

            MAX(BASIC_SAL)

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

          8000

            (ix)SQL> select * from emp_det where basic_sal < (select avg(basic_sal) from emp_det);

             ENO ENAME           ADDRESS          BASIC_SAL JOB_STATUS             DNO

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

              2    Mahendran       RainbowColony      5000               Supervisor              10