INDEX

Sl.No Name of the program Page No

1 E R diagram for train services in a railway station

2 E R diagram for hospital administration

3 DDL commands and DML commands

4 Constraints

5 Operators

6 Clauses

7 Functions

8 Relationship between tables

9 Joins

10 View

11 Implicit cursors

12 Explicit cursors

13 Triggers

14 Function(PL/SQL)

15 Report
 DDL COMMANDS

CREATE

The CREATE command is used to create the table with the columns and its corresponding
data types.

Syntax:

CREATE TABLE < table name> (col1 datatype(size), col2 datatype(size), col3
datatype(size), ….., coln datatype(size));

SQL> create table emp5 (empno number(3), ename varchar2(10), job varchar2(10), sal
number(6), comm number(4));

Table created.

ALTER

The ALTER command is used to modify the existing column data type or add a new column
to existing table and also add the constraints to the existing table.

SYNTAX 1 : to add a column

ALTER TABLE <table name> ADD(col1 datatype(size), col2 datatype(size), col3

datatype(size), ….., coln datatype(size));

SQL> ALTER table emp5 add(fname varchar2(10));

Table altered.

SYNTAX 2 : to modify a column

ALTER TABLE < table name> MODIFY(col1 datatype(size), col2 datatype(size), col3
datatype(size), ….., coln datatype(size));

SQL> alter table emp5 modify(empno number(5));

Table altered.
DESC

The DESC command is used to give the description of the table.

SYNTAX:

DESC <table name>;

SQL> desc emp5;

Name Null? Type

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

EMPNO NOT NULL NUMBER(5)

ENAME VARCHAR2(10)

JOB VARCHAR2(10)

SAL NUMBER(6)

COMM NUMBER(4)

FNAME VARCHAR2(10)

DROP

The DROP command is used to drop the table.

SYNTAX

DROP TABLE < table name>;

SQL> drop table emp2;

Table dropped.
 DML COMMANDS
INSERT

Insert command is used to insert the rows of data into the table.

Syntax:

Insert into <tablename> values(&col1,&col2,……,&coln);

SQL> insert into emp3 values(&empno,'&empname','&job',&sal,&comm);

Enter value for empno: 101

Enter value for empname: aba

Enter value for job: clerk

Enter value for sal: 10000

Enter value for comm: 1000

old 1: insert into emp3 values(&empno,'&empname','&job',&sal,&comm)

new 1: insert into emp3 values(101,'aba','clerk',10000,1000)

1 row created.

DELETE

This command is used to delete the rows of data from the table.

Syntax:

Delete from <tablename> where <condition>;

SQL> delete from emp3 where empno=101;

1 row deleted.

UPDATE

This command is used to update the rows in the table.

Syntax:

Update <tablename> set <expression> where <condition>;

SQL> update emp3 set sal=8000 where empno=121;

1 row updated.

SELECT

This command is used to select the rows and columns from the table.

Syntax1:

Select col1,col2,….,coln from <tablename>;

Syntax2:

Select * from <tablename>;

SQL> select * from emp3;

EMPNO ENAME JOB SAL COMM

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

1 aaa manager 15000 1500

2 bbb supervisor 12000 1000

3 ccc clerk 8000 1000

4 ddd clerk 8400 900

5 eee salesman 10000 2000

6 ddd salesman 9500 1000

7 fff manager 11000 1500

8 ggg clerk 7500 750

9 hhh clerk 8000 800

10 iii salesman 9800 900

11 jjj manager 16000 1600

122 aas clerk 8000 800

12 rows selected.
SQL> select empno,ename,job from emp3;

EMPNO ENAME JOB

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

1 aaa manager

2 bbb supervisor

3 ccc clerk

4 ddd clerk

5 eee salesman

6 ddd salesman

7 fff manager

8 ggg clerk

9 hhh clerk

10 iii salesman

11 jjj manager

122 aas clerk

12 rows selected.
 Constraints
Constraints are the rules that are used to control the invalid entry in a column. There are 6
constraints:

1. Primary key
2. Null
3. Not null
4. Unique
5. Check
6. Foreign key

Primary key

It defines the column as a mandatory or we can say that the column cannot be left blank or
not null.

The values entered must not be repeated.

Syntax: create table student1(stno number(5) primary key, sname varchar2(20));

SQL> insert into student1 values(&stno,'&sname');

Enter value for stno:

Enter value for sname: aaa

old 1: insert into student1 values(&stno,'&sname')

new 1: insert into student1 values(,'aaa')

insert into student1 values(,'aaa')

ERROR at line 1:

ORA-00936: missing expression

SQL> /

Enter value for stno: 1

Enter value for sname: aaa

old 1: insert into student1 values(&stno,'&sname')

new 1: insert into student1 values(1,'aaa')

1 row created.
SQL> /

Enter value for stno: 1

Enter value for sname: bbb

old 1: insert into student1 values(&stno,'&sname')

new 1: insert into student1 values(1,'bbb')

insert into student1 values(1,'bbb')

ERROR at line 1:

ORA-00001: unique constraint (SYSTEM.SYS_C004045) violated

NULL

If in a record any field that is created as null means it is not having any value.

SQL> create table product(pno number(4) ,pname varchar2(15) null);

Table created.

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

Enter value for pno: 1

Enter value for pname:

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

new 1: insert into product values(1,'')

1 row created.

Not null

This constraint ensures that the user always types the value for that column.

SQL> create table product1(pno number(4) ,pname varchar2(15) not null);

Table created.

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

Enter value for pno: 1

Enter value for pname:

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

new 1: insert into product1 values(1,'')

insert into product1 values(1,'')

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SYSTEM"."PRODUCT1"."PNAME")

Unique

The unique key ensures that the information in columns must not be repeated.

SQL> create table product2(pno number(4) unique ,pname varchar2(15));

Table created.

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

Enter value for pno: 1

Enter value for pname: aaa

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

new 1: insert into product2 values(1,'aaa')

1 row created.

SQL> /

Enter value for pno: 2

Enter value for pname: aaa

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

new 1: insert into product2 values(2,'aaa')

insert into product2 values(2,'aaa')

*

ERROR at line 1:

ORA-00001: unique constraint (SYSTEM.SYS_C004047) violated

Check

This ensures that when data is entered, the data in the column is limited to specific values.

SQL> create table department(dno number(5), dname varchar2(15) check(dname

in(‘maths','physics','stat','comp')));

Table created.

SQL> insert into department values(&dno,'&dname');

Enter value for dno: 1

Enter value for dname: comp

old 1: insert into department values(&dno,'&dname')

new 1: insert into department values(1,'comp')

1 row created.

SQL> /

Enter value for dno: 2

Enter value for dname: chem

old 1: insert into department values(&dno,'&dname')

new 1: insert into department values(2,'chem')

insert into department values(2,'chem')

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.SYS_C004048) violated

Foreign key

This key represents relationship between tables. A foreign key is a column whose
values are derived from the primary key of the other table.

SQL> create table department1(dno number(4) primary key,dname varchar2(10));

Table created.

SQL> create table employee2(eno number(4) primary key, ename varchar2(15), dno
number(4) references department1);

Table created.

SQL> insert into department11 values(&dno,'&dname');

Enter value for dno: 1

Enter value for dname: comp

old 1: insert into department11 values(&dno,'&dname')

new 1: insert into department11 values(1,'comp')

1 row created.

SQL> /

Enter value for dno: 2

Enter value for dname: physics

old 1: insert into department11 values(&dno,'&dname')

new 1: insert into department11 values(2,'physics')

1 row created.

SQL> /

Enter value for dno: 3

Enter value for dname: maths

old 1: insert into department11 values(&dno,'&dname')

new 1: insert into department11 values(3,'maths')

1 row created.

SQL> /

Enter value for dno: 4

Enter value for dname: stat

old 1: insert into department11 values(&dno,'&dname')

new 1: insert into department11 values(4,'stat')

1 row created.

SQL> insert into employee11 values(&eno,'&ename',&dno);

Enter value for eno: 101

Enter value for ename: aaa

Enter value for dno: 1

old 1: insert into employee11 values(&eno,'&ename',&dno)

new 1: insert into employee11 values(101,'aaa',1)

1 row created.

SQL> /

Enter value for eno: 102

Enter value for ename: bbb

Enter value for dno: 2

old 1: insert into employee11 values(&eno,'&ename',&dno)

new 1: insert into employee11 values(102,'bbb',2)

1 row created.
SQL> /

Enter value for eno: 103

Enter value for ename: ccc

Enter value for dno: 3

old 1: insert into employee11 values(&eno,'&ename',&dno)

new 1: insert into employee11 values(103,'ccc',3)

1 row created.

SQL> /

Enter value for eno: 104

Enter value for ename: ddd

Enter value for dno: 4

old 1: insert into employee11 values(&eno,'&ename',&dno)

new 1: insert into employee11 values(104,'ddd',4)

1 row created.

SQL> select e.eno,e.ename,d.dname from employee11 e,department11 d

where(d.dno=e.dno);

ENO ENAME DNAME

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

101 aaa comp

102 bbb physics

103 ccc maths

104 ddd stat

 OPERATORS

Arithmetic Operators

SQL> select sal+comm from emp3;

SAL+COMM

----------

16500

13000

9000

9300

12000

10500

12500

8250

8800

10700

17600

11 rows selected.

SQL> select sal-500 from emp3;

SAL-500

----------

14500

11500
 7500

7900

9500

9000

10500

7000

7500

9300

15500

SQL> select sal*.2 from emp3;

SAL*.2

----------

3000

2400

1600

1680

2000

1900

2200

1500

1600

1960

3200

11 rows selected.
SQL> select sal/4 from emp3;

SAL/4

----------

3750

3000

2000

2100

2500

2375

2750

1875

2000

2450

4000

11 rows selected.

Relational Operators

SQL> select * from emp3 where sal>10000;

EMPNO ENAME JOB SAL COMM

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

1 aaa manager 15000 1500

2 bbb supervisor 12000 1000

7 fff manager 11000 1500

 11 jjj manager 16000 1600

4 rows selected.

SQL> select * from emp3 where sal>=13000;

EMPNO ENAME JOB SAL COMM

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

1 aaa manager 15000 1500

11 jjj manager 16000 1600

2 rows selected.

SQL> select * from emp3 where sal<9000;

EMPNO ENAME JOB SAL COMM

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

3 ccc clerk 8000 1000

4 ddd clerk 8400 900

8 ggg clerk 7500 750

9 hhh clerk 8000 800

4 rows selected.

SQL> select * from emp3 where sal<=8000;

EMPNO ENAME JOB SAL COMM

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

3 ccc clerk 8000 1000

8 ggg clerk 7500 750

9 hhh clerk 8000 800

3 rows selected.

SQL> select * from emp3 where sal=8000;

EMPNO ENAME JOB SAL COMM

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

3 ccc clerk 8000 1000

9 hhh clerk 8000 800

2 rows selected.

SQL> select * from emp3 where job!='clerk';

EMPNO ENAME JOB SAL COMM

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

1 aaa manager 15000 1500

2 bbb supervisor 12000 1000

5 eee salesman 10000 2000

6 ddd salesman 9500 1000

7 fff manager 11000 1500

10 iii salesman 9800 900

11 jjj manager 16000 1600

7 rows selected.

Logical operators

SQL> select empno,ename,sal from emp3 where sal>10000 and sal<13000;

EMPNO ENAME SAL

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

2 bbb 12000
 7 fff 11000

7 rows selected.

SQL> select empno,ename,job from emp3 where job='clerk' or job='manager';

EMPNO ENAME JOB

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

1 aaa manager

3 ccc clerk

4 ddd clerk

7 fff manager

8 ggg clerk

9 hhh clerk

11 jjj manager

7 rows selected.

SQL> select empno,ename,job from emp3 where job in ('supervisor','manager');

EMPNO ENAME JOB

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

1 aaa manager

2 bbb supervisor

7 fff manager

11 jjj manager

SQL> select empno,ename,job from emp3 where job not in ('supervisor','manager');

 EMPNO ENAME JOB

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

3 ccc clerk

4 ddd clerk

5 eee salesman

6 ddd salesman

8 ggg clerk

9 hhh clerk

10 iii salesman

7 rows selected.

SQL> select empno,ename,sal from emp3 where sal between 10000 and 15000;

EMPNO ENAME SAL

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

1 aaa 15000

2 bbb 12000

5 eee 10000

7 fff 11000

SQL> select * from emp3 where job in ('manager', 'clerk');

EMPNO ENAME JOB SAL COMM

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

1 aaa manager 15000 1500

3 ccc clerk 8000 1000

4 ddd clerk 8400 900

7 fff manager 11000 1500

 8 ggg clerk 7500 750

9 hhh clerk 8000 800

6 rows selected.

SQL> select * from emp3 where job not in ('manager','clerk');

EMPNO ENAME JOB SAL COMM

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

2 bbb supervisor 12000 1000

5 eee salesman 10000 2000

6 ddd salesman 9500 1000

10 iii salesman 9800 900

SQL> select * from emp3 where sal between 9000 and 13000;

EMPNO ENAME JOB SAL COMM

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

2 bbb supervisor 12000 1000

5 eee salesman 10000 2000

6 ddd salesman 9500 1000

7 fff manager 11000 1500

10 iii salesman 9800 900

SQL> select empno,ename,job from emp3 where job like 's%';

EMPNO ENAME JOB

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

2 bbb supervisor

5 eee salesman
 6 ddd salesman

10 iii salesman

SQL> select * from emp3 where job not like 's%';

EMPNO ENAME JOB SAL COMM

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

1 aaa manager 15000 1500

3 ccc clerk 8000 1000

4 ddd clerk 8400 900

7 fff manager 11000 1500

8 ggg clerk 7500 750

9 hhh clerk 8000 800

6 rows selected.
 SQL CLAUSES

The Group By Clause

The GROUP BY clause is used in conjunction with the aggregate functions to group the
result-set by one or more columns.

It supports the following functions

Avg(),max(),min(),sum()

 Avg() : this function returns the average value.

SQL> select job,avg(sal) from emp3 group by job;

JOB AVG(SAL)

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

salesman 9766.66667

clerk 7975

manager 14000

supervisor 12000

 Max(): it returns the maximum value of the given data.

SQL> select job,max(sal) from emp3 group by job;

JOB MAX(SAL)

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

salesman 10000

clerk 8400

manager 16000

supervisor 12000
  Min() : it returns the minimum value of the given data.

SQL> select job,min(sal) from emp3 group by job;

JOB MIN(SAL)

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

salesman 9500

clerk 7500

manager 11000

supervisor 12000

 Sum(): it returns the sum of the given data.

SQL> select job,sum(sal) from emp3 group by job;

JOB SUM(SAL)

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

salesman 29300

clerk 31900

manager 42000

supervisor 12000

Having Clause

The HAVING CLAUSE is used to specify certain conditions, rows retrieved by using
GROUP BY CLAUSE. This clause should be preceded by GROUP BY CLAUSE.

SQL> select count(*),job from emp3 group by job having count(*)>=2;

COUNT(*) JOB

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

3 salesman

4 clerk

3 manager

Distinct Clause

This clause is used to retrieve the unique values from a specified table.

SQL> select distinct job from emp3;

JOB

----------

salesman

clerk

manager

supervisor

WHERE CLAUSE

This clause is used to retrieve the values where a particular condition is given.

SQL> select * from emp3 where job='clerk';

EMPNO ENAME JOB SAL COMM

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

3 ccc clerk 8000 1000

4 ddd clerk 8400 900

 8 ggg clerk 7500 750

9 hhh clerk 8000 800

Order By Clause

This clause is used to retrieve the rows either in ascending or descending order from a given
table.

SQL> select ename from emp3 order by ename desc;

ENAME

----------

jjj

iii

hhh

ggg

fff

eee

ddd

ddd

ccc

bbb

aaa

11 rows selected.

SQL> select empno,ename,sal from emp3 order by sal;

 EMPNO ENAME SAL

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

8 ggg 7500

9 hhh 8000

3 ccc 8000

4 ddd 8400

6 ddd 9500

10 iii 9800

5 eee 10000

7 fff 11000

2 bbb 12000

1 aaa 15000

11 jjj 16000

11 rows selected.
 SQL FUNCTIONS

Group Functions

1. avg (): it returns all average values of a particular column.

SQL>select avg(sal) from emp3;

AVG(SAL)

---------

9920

2. Min(): it returns a minimum value of the expression.

SQL> select min(sal) from emp3;

MIN(SAL)

----------

7500

3. Max(): it returns a miximum value of the expression.

SQL> select max(sal) from emp3;

MAX(SAL)
----------
15000

4. Count() : it displays no of rows in the table.

SQL> select count(empno) from emp3;

COUNT(EMPNO)

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

10

5. Count(*) : it displays the number of rows in table including duplicates with null
values.

SQL> select count(*) from emp3;

COUNT(*)
----------
10
Sum(): it returns the sum of values of the given expression.

SQL> select sum(sal) from emp3;

SUM(SAL)

----------

99200

SQL> select sum(comm) from emp3;

SUM(COMM)

----------

11350

Character Functions:

Lower(): it returns all the characters in lower case.

SQL> select lower(ename) from emp3;

LOWER(ENAM

----------

aaa

bbb

ccc

ddd

eee

ddd

fff

ggg

hhh

iii

10 rows selected.

Upper() : it returns all the characters in upper case.

SQL> select upper(ename) from emp3;

UPPER(ENAM

----------

AAA

BBB

CCC

DDD

EEE

DDD
FFF

GGG

HHH

III

10 rows selected.

Initcap(): it returns the string with first letter in upper case.

SQL> select initcap(ename) from emp3;

INITCAP(EN

----------

Aaa

Bbb

Ccc

Ddd

Eee

Ddd

Fff

Ggg

Hhh

Iii

10 rows selected.

Length() : it returns the length of characters in the given string.

SQL> select length(job) from emp3;

LENGTH(JOB)

-----------

10

10 rows selected.

Substr() : it returns the character beginning at position m and n characters long.

SQL> select substr('supervisor',3,4) from dual;

SUBS

----

Perv

NUMERIC FUNCTIONS

Abs() : It returns the absolute value of the given values.

SQL> select abs(-3) from dual;

ABS(-3)

----------

Power(): power(m,n)

It returns m raised to nth power.

SQL> select power(2,3) from dual;

POWER(2,3)

----------

Round() : The ROUND() function is used to round a numeric field to the number of
decimals specified.

SQL> select round(14.4587,3) from dual;

ROUND(14.4587,3)

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

14.459

Sqrt() : It returns the square root of a given value.

SQL> select sqrt(9) from dual;

SQRT(9)

----------

Ceil(): this function returns the smallest integer value that is greater than or equal to a
number.

SQL> select ceil(18.75) from dual;

CEIL(18.75)

-----------

19

SQL> select ceil(13.42) from dual;

CEIL(13.42)

-----------

14

Floor(): this function returns the greatest integer value that is lesser than or equal to a
number.

SQL> select floor(13.65) from dual;

FLOOR(13.65)

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

13

SQL> select floor(12.11) from dual;

FLOOR(12.11)

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

12

Mod() : The SQL MOD() function returns the remainder from a division.

SQL> select mod(5,3) from dual;

MOD(5,3)

----------

2
Date Functions

Sysdate() : it returns the current system date.

SQL> select sysdate from dual;

SYSDATE

---------

20-SEP-16

Add_months() : returns the date date plus integer months.

SQL> select add_months('12-apr-16',3) from dual;

ADD_MONTH

---------

12-JUL-16

Last_day() : it returns the last day of the month for the given date.

SQL> select last_day('12-feb-16') from dual;

LAST_DAY(

---------

29-FEB-16

Months_between() : this calculates the number of months between two dates and returns the
difference as a number.

SQL> select months_between('19-jan-16','20-sep-14') from dual;

MONTHS_BETWEEN('19-JAN-16','20-SEP-14')

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

15.9677419
 RELATIONSHIP BETWEEN TABLES

SQL> create table emp17(eno number(4) primary key, ename varchar2(15),sal

number(5),dno number(3));

Table created.

SQL> insert into emp17 values(&eno,'&ename',&sal,&dno);

Enter value for eno: 1

Enter value for ename: aaa

Enter value for sal: 10000

Enter value for dno: 10

old 1: insert into emp17 values(&eno,'&ename',&sal,&dno)

new 1: insert into emp17 values(1,'aaa',10000,10)

1 row created.

SQL> /

Enter value for eno: 2

Enter value for ename: bbb

Enter value for sal: 15000

Enter value for dno: 10

old 1: insert into emp17 values(&eno,'&ename',&sal,&dno)

new 1: insert into emp17 values(2,'bbb',15000,10)

1 row created.

SQL> select * from emp17;

ENO ENAME SAL DNO

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

1 aaa 10000 10

2 bbb 15000 10
 3 ccc 14000 11

4 ddd 20000 11

SQL> create table dept17(dno number(3) primary key, dname varchar2(15));

Table created.

SQL> insert into dept17 values(&dno,'&dname');

Enter value for dno: 10

Enter value for dname: payments

old 1: insert into dept17 values(&dno,'&dname')

new 1: insert into dept17 values(10,'payments')

1 row created.

SQL> /

Enter value for dno: 11

Enter value for dname: purchase

old 1: insert into dept17 values(&dno,'&dname')

new 1: insert into dept17 values(11,'purchase')

1 row created.

SQL> select * from dept17;

DNO DNAME

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

10 payments

11 purchase

SQL> select eno,ename,dname from emp17 e,dept17 d where e.sal<=15000 and

e.dno=d.dno;
 ENO ENAME DNAME

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

1 aaa payments

2 bbb payments

3 ccc purchase

SQL> select eno,ename,dname from emp17 e, dept17 d where e.dno=d.dno and

d.dname='payments';

ENO ENAME DNAME

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

1 aaa payments

2 bbb payments

SQL> select eno,ename,dname,sal from emp17 e,dept17 d where e.sal>=20000 and

e.dno=d.dno;

ENO ENAME DNAME SAL

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

4 ddd purchase 20000

SQL> select eno,ename,dname from emp17 e, dept17 d where e.ename='bbb' and

e.deptno=d.deptno;

ENO ENAME DNAME

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

2 bbb payments
 JOINS

SQL> create table emp(eno number(4) primary key,ename varchar2(15),deptno number(3));

Table created.

SQL> insert into emp values (&eno,'&ename',&deptno);

Enter value for eno: 1

Enter value for ename: aaa

Enter value for deptno: 101

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(1,'aaa',101)

1 row created.

SQL> /

Enter value for eno: 2

Enter value for ename: bbb

Enter value for deptno: 102

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(2,'bbb',102)

1 row created.

SQL> /

Enter value for eno: 3

Enter value for ename: ccc

Enter value for deptno: 103

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(3,'ccc',103)

1 row created.

SQL> /

Enter value for eno: 4

Enter value for ename: ddd

Enter value for deptno: 101

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(4,'ddd',101)

1 row created.

SQL> /

Enter value for eno: 5

Enter value for ename: eee

Enter value for deptno: 102

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(5,'eee',102)

1 row created.

SQL> /

Enter value for eno: 6

Enter value for ename: fff

Enter value for deptno: 103

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(6,'fff',103)

1 row created.

SQL> /

Enter value for eno: 7

Enter value for ename:

Enter value for deptno: 101

old 1: insert into emp values(&eno,'&ename',&deptno)

new 1: insert into emp values(7,'',101)

1 row created.

SQL> select * from emp;

ENO ENAME DEPTNO

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

1 aaa 101

2 bbb 102

3 ccc 103

4 ddd 101

5 eee 102

6 fff 103

7 101

7 rows selected.SQL> create table dept(deptno number(3) primary key, dname varchar2(10));

Table created.

SQL> insert into dept values(&deptno,'&dname');

Enter value for deptno: 101

Enter value for dname: maths

old 1: insert into dept values(&deptno,'&dname')

new 1: insert into dept values(101,'maths')

1 row created.

SQL> /

Enter value for deptno: 102

Enter value for dname: stat

old 1: insert into dept values(&deptno,'&dname')

new 1: insert into dept values(102,'stat')

1 row created.

SQL> /

Enter value for deptno: 103

Enter value for dname: comp

old 1: insert into dept values(&deptno,'&dname')

new 1: insert into dept values(103,'comp')

1 row created.

Enter value for deptno: 104

Enter value for dname:

old 1: insert into dept values(&deptno,'&dname')

new 1: insert into dept values(104,'')

1 row created.

SQL> select * from dept;

DEPTNO DNAME

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

101 maths

102 stat

103 comp

104

SQL> select emp.eno,emp.ename,dept.dname from emp left join dept on

dept.deptno=emp.deptno;
ENO ENAME DNAME

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

7 maths

4 ddd maths

1 aaa maths

5 eee stat

2 bbb stat

6 fff comp

3 ccc comp

7 rows selected.

SQL> select emp.eno,emp.ename,dept.dname from emp right join dept on

dept.deptno=emp.deptno;

ENO ENAME DNAME

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

1 aaa maths

2 bbb stat

3 ccc comp

4 ddd maths

5 eee stat

6 fff comp

7 maths

8 rows selected.

SQL> select emp.eno,emp.ename,dept.dname from emp right join dept on

dept.deptno=emp.deptno order by dname;

ENO ENAME DNAME

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

3 ccc comp

6 fff comp

1 aaa maths
 4 ddd maths

7 maths

2 bbb stat

5 eee stat

8 rows selected.

SQL> select emp.eno,emp.ename,dept.dname from emp full join dept on

dept.deptno=emp.deptno order by dname;

ENO ENAME DNAME

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

3 ccc comp

6 fff comp

1 aaa maths

4 ddd maths

7 maths

2 bbb stat

5 eee stat

8 rows selected.

SQL> select emp.eno,emp.ename,dept.dname from emp inner join dept on

dept.deptno=emp.deptno order by dname;

ENO ENAME DNAME

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

6 fff comp

3 ccc comp

7 maths

1 aaa maths

4 ddd maths
 2 bbb stat

5 eee stat

7 rows selected.

SQL> select eno,ename,dname from emp e, dept d where e.ename='bbb' and

e.deptno=d.deptno;

ENO ENAME DNAME

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

2 bbb stat
 VIEW
SQL> create view e1 as select * from emp;

View created.

SQL> select * from e1;

ENO ENAME DEPTNO

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

1 aaa 101

2 bbb 102

3 ccc 103

4 ddd 101

5 eee 102

6 fff 103

7 101

7 rows selected.

SQL> create view d1 as select * from dept;

View created.

SQL> select * from d1;

DEPTNO DNAME

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

101 maths

102 stat

103 comp

104
 IMPLICIT CURSORS
SQL> Declare

2 veno emp17.eno%type;

3 begin

4 veno:=&eno;

5 delete from emp17 where eno=veno;

6 if sql%found then

7 DBMS_OUTPUT.PUT_LINE('record deleted');

8 Else

9 DBMS_OUTPUT.PUT_LINE('no such employee');

10 end if;

11 commit;

12 end;

13 /

Enter value for eno: 10

old 4: veno:=&eno;

new 4: veno:=10;

Deleting

no such employee

PL/SQL procedure successfully completed.

SQL> /

Enter value for eno: 202

old 4: veno:=&eno;

new 4: veno:=202;

Deleting
record deleted

PL/SQL procedure successfully completed.

BEFORE DELETING THE RECORD

SQL> select * from emp17;

ENO ENAME SAL DNO

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

1 aaa 12000 20

2 bbb 16000 20

3 ccc 16000 21

4 ddd 20000 21

111 abb 23000 22

202 www 11000 25

6 rows selected.

AFTER DELETING THE RECORD

SQL> select * from emp17;

ENO ENAME SAL DNO

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

1 aaa 12000 20

2 bbb 16000 20

3 ccc 16000 21

4 ddd 20000 21

111 abb 23000 22

 EXPLICIT CURSORS

SQL> Declare

2 Cursor c1 is select eno,ename,sal from emp17;

3 vno emp17.eno%type;

4 vname emp17.ename%type;

5 vsal emp17.sal%type;

6 Begin

7 Open c1;

8 Loop

9 Fetch c1 into vno,vname,vsal;

10 Exit when c1%notfound;

11 Dbms_output.put_line(vno);

12 Dbms_output.put_line(vname);

13 Dbms_output.put_line(vsal);

14 End loop;

15 Close c1;

16 End;

17 /
1

aaa

12000

bbb

16000

ccc

16000

ddd

20000

111

abb

23000

202

www

11000

PL/SQL procedure successfully completed.

 TRIGGERS
set serveroutput on;

SQL> CREATE OR REPLACE TRIGGER t3

2 BEFORE

3 INSERT OR

4 UPDATE OF sal, dno OR

5 DELETE

6 ON emp17

7 BEGIN

8 CASE

9 WHEN INSERTING THEN

10 DBMS_OUTPUT.PUT_LINE('Inserting');

11 WHEN UPDATING('sal') THEN

12 DBMS_OUTPUT.PUT_LINE('Updating salary');

13 WHEN UPDATING('dno') THEN

14 DBMS_OUTPUT.PUT_LINE('Updating department ID');

15 WHEN DELETING THEN

16 DBMS_OUTPUT.PUT_LINE('Deleting');

17 END CASE;

18 END;

19 /

Trigger created.
SQL> select * from emp17;

ENO ENAME SAL DNO

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

1 aaa 11000 10

2 bbb 16000 10

3 ccc 15000 11

4 ddd 20000 11

111 abb 23000 12

SQL> update emp17 set sal=sal+1000 where sal<=15000;

Updating salary

2 rows updated.

SQL> delete emp17 where sal>=20000;

Deleting

2 rows deleted.

SQL> insert into emp17 values(201,'www',20000,15);

Inserting

1 row created.

SQL> select * from emp17;

ENO ENAME SAL DNO

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

1 aaa 12000 10

2 bbb 16000 10

3 ccc 16000 11

201 www 20000 15

SQL>rollback ;

Rollback complete.

SQL> update emp17 set dno=dno+10 where dno<=15;

Updating department ID

6 rows updated.

SQL> select * from emp17;

ENO ENAME SAL DNO

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

1 aaa 12000 20

2 bbb 16000 20

3 ccc 16000 21

4 ddd 20000 21

111 abb 23000 22

202 www 11000 25

6 rows selected.
 FUNCTION
CREATE OR REPLACE FUNCTION totalemp

RETURN number IS

total number(2) := 0;

BEGIN

SELECT count(*) into total

FROM emp;

RETURN total;

END;

Function created.

SQL> select totalemp from dual;

TOTALEMP

----------