JMIT, RADAUR

JMIT, RADAUR
Database Management System Lab
SESSION : 2022-2026

SUBMITTED TO:- SUBMITTED BY:-

Er. Shilpa RAGHAV DUGGAL
C.S.E. Department 1222210
3 CSE – ‘C’

Department of Computer Science and Engineering

Seth Jai Parkash Mukand Lal Institute of Engineering & Technology
Radaur – 135133 (Yamuna Nagar)
(Affiliated to Kurukshetra University, Kurukshetra, Haryana, India)

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

INDEX
Sr. Topic Date Remark
No
1. Write the queries for Data Definition Language (DDL) in
RDBMS.
2. Write the queries for Data Manipulation Language (DML)
in RDBMS.

3. Write the queries for Data Control Language (DCL) in

RDBMS.
4. Write queries to perform various integrity
constraints on relational database.
5. Create a database and perform the following operations:-
a. Arithmetic and Relational operations
b. Group by and having clauses
c. Like predicate for pattern matching in database
6. Write SQL query using character, number and group functions
7. Write SQL Queries for Relational Algebra.
8. Write SQL queries for extracting data from more than one
table.
9. Write SQL queries for sub queries, nested queries.
10. Write an SQL query for rollback, commit, savepoint.
11. Create Views from an existing table.

12. Write a procedure for computing income tax of a on the basis

of given conditions : -

For this purpose create a table with name , ssn , gross salary
and income tax of the employee.
13. Create trigger for before insertion or update.
14. Write SQL queries to make procedure to find area
of circle and insert in a table.
15. Write a program in PL/SQL using after update or
delete using Triggers.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 1:- Write the queries for Data Definition Language (DDL) in
RDBMS.
SQL> connect
system Connected.
SQL> create table RAGHAV(Name varchar2(20), RollNo number(10), Branch

varchar2(10)); Table created.

SQL> insert into RAGHAV values('&Name', '&RollNo',

'&Branch'); Enter value for name: Sam
Enter value for rollno:
001 Enter value for
branch: CSE
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Sam',
'001', 'CSE')

1 row

created.

SQL> /
Enter value for name:
Ria Enter value for
rollno: 002 Enter value
for branch: IT
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values(Ria, '002',
'IT')

1 row

created.

SQL> /
Enter value for name:
Anu Enter value for
rollno: 003 Enter value
for branch: ME
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Anu',
'003', 'ME')

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR

1 row

created.

SQL> /
Enter value for name:
Arun Enter value for
rollno: 004 Enter value
for branch: EE
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Arun', '004',
'EE')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> /
Enter value for name:
Ram Enter value for
rollno: 005 Enter value
for branch: ECE
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Ram',
'005', 'ECE')

1 row created.

SQL> select * from RAGHAV;

NAME ROLLNO BRANCH

Sam 001 CSE
Ria 002 IT
Anu 003 ME
Arun 004 EE
Ram 005 ECE

SQL> alter table RAGHAV add(MobileNo

number(10)); Table altered.

SQL> select * from RAGHAV;

NAME ROLLNO BRANCH MOBILENO

Sam 001 CSE
Neha 002 IT
Anu 003 ME
Arun 004 EE
Ram 005

ECE SQL> truncate table

RAGHAV; Table

truncated.

SQL> select * from

RAGHAV; no rows
selected

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR

SQL> drop table

RAGHAV; Table
dropped.

SQL> spool off

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 2:-Write the queries for Data Manipulation Language (DML) in

RDBMS
SQL> connect
system Connected.
SQL> create table RAGHAV (Name varchar2(20), RollNo number(10), Branch

varchar2(10)); Table created.

SQL> insert into RAGHAV values('&Name', '&RollNo',

'&Branch'); Enter value for name: Riya
Enter value for rollno: 1
Enter value for branch:
CSE
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Riya', '1',
'CSE')

1 row
created.

SQL> /
Enter value for name:
Siya Enter value for
rollno: 2 Enter value
for branch: IT
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Siya', '2',
'IT')

1 row

created.

SQL> /
Enter value for name:
Aditya Enter value for
rollno: 3 Enter value for
branch: EE
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Aditya',
'3', 'EE')

1 row
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR

created.

SQL> /
Enter value for name:
Abhi Enter value for
rollno: 4 Enter value for
branch: ECE
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Abhi',
'4', 'ECE')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> /
Enter value for name:
Pooja Enter value for
rollno: 5 Enter value for
branch: ME
old 1: insert into RAGHAV values('&Name', '&RollNo',
'&Branch') new 1: insert into RAGHAV values('Pooja',
'5', 'ME')

1 row created.

SQL> update RAGHAV set RollNo=6 where

Name='Pooja'; 1 row updated.

SQL> select * from RAGHAV;

NAME ROLLNO BRANCH

Riya 1 CSE
Siya 2 IT
Aditya 3 EE
Abhi 4 ECE
Pooja 6 ME

SQL> delete from RAGHAV where

Name='Pooja'; 1 row deleted.

SQL> select * from RAGHAV;

NAME ROLLNO BRANCH

Riya 1 CSE
Siya 2 IT
Aditya 3 EE
Abhi 4 ECE

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 3:- Write the queries for Data Control Language (DCL) in RDBMS
SQL> connect
system Connected.
SQL> create user Pual identified by

Marshal; User created.

SQL> connect
Pual ERROR:
ORA-01045: user PUAL lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to

ORACLE. SQL> connect system
Connected.
SQL> grant connect to

Pual; Grant succeeded.

SQL> connect
Pual Connected.
SQL> select * from

tab; no rows

selected

SQL> create table RAGHAV (SNo number(3));

create table RAGHAV (SNo number(3))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect
system Connected.
SQL> grant create table to

Pual; Grant succeeded.

SQL> grant resource to

Pual; Grant succeeded.

Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> connect
Pual;
Connected.
SQL> create table RAGHAV (SNo

number(3)); Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

RAGHAV TABLE

SQL> connect
system Connected.
SQL> revoke resource from

Pual; Revoke succeeded.

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 4:- Write queries to perform various integrity constraints on

relational database
SQL> connect
system Enter
password:
Connected.
SQL> create table RAGHAV (SNo number(3) PRIMARY KEY, Name

varchar(10)); Table created.

SQL> drop table

RAGHAV; Table

dropped.

SQL> create table RAGHAV (SNo number(3) constraint pri_k PRIMARY KEY, Name

varchar(10)); Table created.

SQL> desc RAGHAV

Name Null? Type
SNO NOT NULL NUMBER(3)
NAME VARCHAR2(10)

SQL> alter table RAGHAV drop

constraint pri_k; Table altered.

SQL> desc RAGHAV;

Name Null? Type
SNO NUMBER(3)
NAME VARCHAR2(10)

SQL> select * from

RAGHAV; no rows

selected

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> alter table RAGHAV add primary key(SNO);

Table altered.
SQL> drop table

RAGHAV; Table

dropped.

SQL> alter table RAGHAV add constraint pri_k primary

key(SNO); alter table RAGHAV add constraint pri_k
primary key(SNO)
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table RAGHAV (SNo number(3), Name

varchar(10)); Table created.

SQL> alter table RAGHAV add constraint pri_k primary

key(SNO); Table altered.

SQL> drop table

RAGHAV; Table

dropped.

SQL> create table RAGHAV (SNo number(3), Name varchar(10), primary

key(SNO)); Table created.

SQL> desc RAGHAV;

Name Null? Type
SNO NOT NULL NUMBER(3)
NAME VARCHAR2(10)

SQL> insert into RAGHAV values('&SNO',

'&Name'); Enter value for sno: 1

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Enter value for name: RAGHAV
old 1: insert into RAGHAV values('&SNO',
'&Name') new 1: insert into RAGHAV
values('1', 'RAGHAV')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> /
Enter value for sno:
2 Enter value for
name: B
old 1: insert into RAGHAV values('&SNO',
'&Name') new 1: insert into RAGHAV
values('2', 'B')

1 row
created.
SQL> /
Enter value for sno:
3 Enter value for
name: C
old 1: insert into RAGHAV values('&SNO',
'&Name') new 1: insert into RAGHAV
values('3', 'C')

1 row

created.

SQL> /
Enter value for sno:
4 Enter value for
name: D
old 1: insert into RAGHAV values('&SNO',
'&Name') new 1: insert into RAGHAV
values('4', 'D')

1 row created.

SQL> select * from

RAGHAV; SNO NAME
1 RAGHAV
2B
3C
4D

SQL> insert into RAGHAV values('&SNO',

'&Name');
Enter value for sno: 2

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Enter value for name: F
old 1: insert into RAGHAV values('&SNO',
'&Name') new 1: insert into RAGHAV
values('2', 'F')
insert into RAGHAV values('2', 'F')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004063) violated

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> create table RAGHAV_child(SRNO number(3), Course varchar(10),constraint

fk foreign key(SRNO) references RAGHAV(SNO));

Table created.

SQL> insert into RAGHAV_child values('&SRNO',

'&Course'); Enter value for srno: 2
Enter value for course: CSE
old 1: insert into RAGHAV_child values('&SRNO',
'&Course') new 1: insert into RAGHAV_child
values('2', 'CSE')

1 row

created.

SQL> /
Enter value for srno: 3
Enter value for
course: IT
old 1: insert into RAGHAV_child values('&SRNO',
'&Course') new 1: insert into RAGHAV_child
values('3', 'IT')

1 row

created.

SQL> /
Enter value for srno: 2
Enter value for course:
ME
old 1: insert into RAGHAV_child values('&SRNO',
'&Course') new 1: insert into RAGHAV_child
values('2', 'ME')

1 row created.

SQL> select * from

RAGHAV_child; SRNO
COURSE
2 CSE
3 IT

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
2 ME

SQL> select * from

RAGHAV; SNO NAME

1 RAGHAV
2B
3C
4D

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> insert into RAGHAV_child values('&SRNO',

'&Course'); Enter value for srno: 5
Enter value for course: EE
old 1: insert into RAGHAV_child values('&SRNO',
'&Course') new 1: insert into RAGHAV_child
values('5', 'EE')
insert into RAGHAV_child values('5', 'EE')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.FK) violated - parent key not found

SQL> drop table

RAGHAV_child; Table

dropped.

SQL> drop table RAGHAV;

Table
dropped.
SQL> spool
off

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 5:- Create a database and perform the following operations:-

d. Arithmetic and Relational operations
e. Group by and having clauses
f. Like predicate for pattern matching in database

(RAGHAV)SQL> create table RAGHAV(EmpID number(10), EmpName

varchar2(20), Salary number(10), Bonus number(10));

Table created.

SQL> insert into RAGHAV

values('&EmpID','&EmpName','&Salary','&Bonus'); Enter value
for empid: 1
Enter value for empname:
Ajay Enter value for salary:
1000 Enter value for
bonus: 100
old 1: insert into RAGHAV
values('&EmpID','&EmpName','&Salary','&Bonus') new 1: insert
into RAGHAV values('1','Ajay','1000','100')

1 row

created.

SQL> /
Enter value for empid: 2
Enter value for empname:
Rahul Enter value for salary:
1500 Enter value for bonus:
200
old 1: insert into RAGHAV
values('&EmpID','&EmpName','&Salary','&Bonus') new 1: insert into
RAGHAV values('2','Rahul','1500','200')

1 row

created.

SQL> /
Enter value for empid: 3

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Enter value for empname:
Simran Enter value for salary:
4000
Enter value for bonus: 190
old 1: insert into RAGHAV
values('&EmpID','&EmpName','&Salary','&Bonus') new 1: insert into
RAGHAV values('3','Simran','4000','190')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from RAGHAV;

EMPID EMPNAME SALARY BONUS

1 Ajay 1000 100

2 Rahul 1500 200
3 Simran 4000 190

SQL> select EmpName,Salary,Bonus,(Salary+Bonus) from RAGHAV

where(Salary+Bonus)>1000;

EMPNAME SALARY BONUS (SALARY+BONUS)

Ajay 1000 100 1100

Rahul 1500 200 1700
Simran 4000 190 419
0

SQL> select EmpName,Salary,Bonus,(Salary-Bonus) from RAGHAV where

Salary>1000; EMPNAME SALARY BONUS (SALARY-BONUS)

Rahul 1500 200 1300

Simran 4000 190 381
0

SQL> select EmpID,EmpName from RAGHAV where Salary>1000 AND

Bonus>100; EMPID EMPNAME

2 Rahul
3 Simra

n SQL>

spool off

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from people;

GENDER
femal
e
femal
e
male
femal
e
male
male
male
male

8 rows selected.

SQL> select gender from people GROUP BY

gender; GENDER

male
femal
e

SQL> spool off

SQL> select * from RAGHAV;

EMPID EMPNAME SALARY BONUS

1 Ajay 1000 100

2 Rahul 1500 200
3 Simran 4000 190

SQL> select * from RAGHAV where EmpName like 'S

%'; EMPID EMPNAME SALARY BONUS

3 Simran 4000 190

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select EmpName||Salary from RAGHAV;

EMPNAME||SALARY
Ajay1000
Rahul1500
Simran400
0

SQL> select * from RAGHAV where EmpName like 'S%' or

Salary>1000; EMPID EMPNAME SALARY BONUS

2 Rahul 1500 200

3 Simran 4000 190

SQL> spool off

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 6:- Write SQL query using character, number and group functions
SQL> select * from RAGHAV;

EMPNO ENAME DESIGNATIO SALARY DEPTNO

101pavneet radour 20000 48
102parul karnal 3000 47

SQL> select concat(ename,salary) from

RAGHAV; CONCAT(ENAME,SALARY)
pavneet200
00
parul3000

SQL> select substr(ename,1,3) from

RAGHAV; SUB
---
pa
v
par

SQL> select instr(ename,'v') from

RAGHAV; INSTR(ENAME,'V')
3
0

SQL> select rpad(ename,10,'_') from

RAGHAV; RPAD(ENAME
pavneet
parul

SQL> select lpad(ename,10,'_') from RAGHAV;

LPAD(ENAME
pavneet

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
parul

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select rtrim('avn') as ename from RAGHAV;

ENA
---
av
n
av
n

SQL> select upper(ename) from

RAGHAV; UPPER(ENAME)

PAVNEET
PARUL

SQL> select abs(salary) from

RAGHAV; ABS(SALARY)

20000
3000

SQL> select ceil(salary) from

RAGHAV; CEIL(SALARY)
20000
3000

SQL> select mod(salary,deptno) from

RAGHAV; MOD(SALARY,DEPTNO)
32
39

SQL> select sign(salary) from

RAGHAV; SIGN(SALARY)
1
1

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select sqrt(salary) from RAGHAV;

SQRT(SALARY)
141.421356
54.7722558

SQL> select add_months('13-oct-16',3) from

RAGHAV; ADD_MONTH
13-JAN-17
13-JAN-17

SQL> select count(*) from

RAGHAV; COUNT(*)
2

SQL> select distinct empno from

RAGHAV; EMPNO

102
101

SQL> select max(salary) from

RAGHAV; MAX(SALARY)
20000

SQL> select sum(salary) from

RAGHAV; SUM(SALARY)
23000

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 7:- Write SQL Queries for Relational Algebra.

SQL> select * from

RAGHAV;

ROLLNO NAME AGE

1 Ram 20
2 Shyam 21
3 Ram 19

SQL> -- PROJECTION OPERATION

SQL> select rollno from RAGHAV;

ROLLNO

1
2
3

SQL> -- SELECTION OPERATION

SQL> select name from RAGHAV where rollno = 2;

NAME

Shyam

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> -- RENAME OPERATION

SQL> select rollno as Roll_number from RAGHAV;

ROLL_NUMBER

1
2
3

SQL> select * from

RAGHAV1;

ID NAME

1 Jack
2 Harry
3 Jackson

SQL> select * from

RAGHAV2; ID NAME

3 Jackson
4 Stephen
5 David

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> -- UNION OPERATION

SQL> select * from RAGHAV1 UNION select * from RAGHAV2;

ID NAME

1 Jack
2 Harry
3 Jackson
4 Stephen
5 David

SQL> select * from RAGHAV1 UNION ALL select * from

RAGHAV2; ID NAME

1 Jack
2 Harry
3 Jackson
3 Jackson
4 Stephen
5 David

6 rows selected.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> -- INTERSECTION OPERATION

SQL> select * from RAGHAV1 INTERSECT select * from RAGHAV2;

ID NAME

3 Jackson

SQL> -- MINUS OPERATION

SQL> select * from RAGHAV1 MINUS select * from RAGHAV2;

ID NAME

1 Jack
2 Harry

SQL> select * from

RAGHAV 2 ;

ROLLNO NAME AGE

1 Ram 20
2 Shyam 21
3 Ram 19

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from B;

ROLLNO SUBJECT HOBBY

2 Hindi Singing
3 English Dancing
4 English Writing

SQL> -- CROSS JOIN

SQL> select * from RAGHAV CROSS JOIN B;

ROLLNO NAME AGE ROLLNO SUBJECT HOBBY

1 Ram 20 2 Hindi Singing

1 Ram 20 3 English Dancing
1 Ram 20 4 English Writing
2 Shyam 21 2 Hindi Singing
2 Shyam 21 3 English Dancin
g
2 Shyam 21 4 English Writing
3 Ram 19 2 Hindi Singing
3 Ram 19 3 English Dancing
3 Ram 19 4 English Writing

9 rows selected.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from RAGHAV;

ENO ENAME ADDRESS

1 Ram Delhi
2 Varun Chd
3 Ravi Chd
4 Amrit Delhi

SQL> select * from

dept;

DEPNO NAME ENO

D1 HR 1
D2 IT 2
D3 MRKT 4

SQL> -- NATURAL JOIN

SQL> select ename from RAGHAV NATURAL JOIN dept;

ENAME

Ram
Varu
n
Amrit

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

PROGRAM 8:- Write SQL queries for extracting data from more than one
table
SQL> select * from RAGHAV;
ROLLNO NAME AGE

1 Ram 20
2 Shyam 21
3 Ram 19

SQL> select * from B;

ROLLNO SUBJECT HOBBY

2 Hindi Singing
3 English Dancing
4 English Writing

SQL> -- CROSS JOIN

SQL> select * from RAGHAV CROSS JOIN B;

ROLLNO NAME AGE ROLLNO SUBJECT HOBBY

1 Ram 20 2 Hindi Singing

1 Ram 20 3 English Dancing
1 Ram 20 4 English Writing
2 Shyam 21 2 Hindi Singing
2 Shyam 21 3 English Dancin
g
2 Shyam 21 4 English Writing
3 Ram 19 2 Hindi Singing
3 Ram 19 3 English Dancing
3 Ram 19 4 English Writing
9 rows selected.
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from RAGHAV;

ENO ENAME ADDRESS DEPNO

1 Ram Delhi D1
2 Varun Chd D2
3 Ravi Chd D3
4 Amrit Delhi

SQL> select *from dept;

DEPNO NAME ENO LOCATION

D1 HR 1 Delhi
D2 IT 2 Pune
D3 MRKT 4
Patna
D4 Finance 5

SQL> select ename from RAGHAV NATURAL JOIN

dept; ENAME

Ram
Varu
n

SQL> -- EQUI JOIN

SQL> SELECT ename from RAGHAV, dept where RAGHAV.eno = dept.eno
and RAGHAV.address = dept.location;

ENAME

Ram

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from study;

S_ID C_ID SINCE_YEAR

S1 C1 2016
S2 C2 2017
S1 C2 2017

SQL> -- SELF JOIN

SQL> select t1.s_id from study t1, study t2 where t1.s_id = t2.s_id and
t1.c_id<>t2.c_id;

S_ID

S1
S1

SQL> SELECT * from RAGHAV_;

ROLL_NO NAME ADDRESS AGE

1 HARSH DELHI 18
2 PRATIK BIHAR 19
3 RIYANKA SILIGURI 20
4 DEEP RAMNAGAR 18
5 SAPTARHI KOLKATA 19
6 DHANRAJ BARABAJAR 20
7 ROHIT BALURGHAT 18
8 NIRAJ ALIPUR 19

8 rows selected.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> SELECT * from RAGHAV_course;

COURSE_ID ROLL_NO

1 1
2 2
2 3
3 4
1 5
4 9
5 10
4 11

8 rows selected.

SQL> -- LEFT OUTER JOIN

SQL> select RAGHAV_.name,RAGHAV_course.course_id FROM RAGHAV_ LEFT
JOIN RAGHAV_course ON RAGHAV_course.roll_no = RAGHAV_.roll_no;

NAME COURSE_ID

HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
SAPTARHI 1
NIRAJ
DHANRAJ
ROHIT

8 rows selected.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select RAGHAV_.name, RAGHAV_course.course_id FROM RAGHAV_ RIGHT

JOIN RAGHAV_course ON RAGHAV_course.roll_no = RAGHAV_.roll_no;
NAME COURSE_ID

HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
SAPTARHI 1
5
4
4
8 rows selected.

SQL> -- FULL OUTER JOIN

SQL> SELECT RAGHAV_.name, RAGHAV_course.course_id FROM RAGHAV_ FULL
JOIN RAGHAV_course ON RAGHAV_course.roll_no = RAGHAV_.roll_no;
NAME COURSE_ID

HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
SAPTARHI 1
NIRAJ
DHANRAJ
ROHIT
5
4
4
11 rows
selected. SQL>
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR
spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 9:- Write SQL queries for sub queries, nested queries

SQL> select * from RAGHAV;

NAME ROLLNO MOBILE MARKS

muskie 24 98776657 95
0 6
munni 237 85965634 76
5
pnni 251 98675645 77
3
nidhi 244 85857869 87
8

SQL> select * from RAGHAV where rollno in (select rollno from RAGHAV where

marks>85); NAME ROLLNO MOBILE MARKS

muskie 240 95
987766576
nidhi 244
58578698 87

SQL> create table RAGHAV_bck (name varchar(10), rollno number(10), mobile

number(10), marks number(5));

Table created.

SQL> insert into RAGHAV_bck select * from RAGHAV where rollno in

(select rollno from RAGHAV); 4 rows created.

SQL> select * from RAGHAV_bck;

NAME ROLLNO MOBILE MARKS

muskie 240 987766576 95

munni 237 859656345 76
pnni 251 77
986756453
nidhi 244 87
858578698
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR

SQL> update RAGHAV set marks=marks-5 where rollno in(select rollno from
RAGHAV_bck where rollno>239);

3 rows updated.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from RAGHAV_bck;

NAME ROLLNO MOBILE MARKS

muskie 240 987766576 95

munni 237 859656345 76
pnni 251 77
986756453
nidhi 244
858578698 87

SQL> select * from RAGHAV;

NAME ROLLNO MOBILE MARKS

muskie 240 987766576 90

munni 237 859656345 76
pnni 251 72
986756453
nidhi 244 82
858578698

SQL> delete from RAGHAV where rollno in (select rollno from RAGHAV_bck where
rollno>=240); 3 rows deleted.

SQL> select * from RAGHAV;

NAME ROLLNO MOBILE MARKS
munni 237 59656345 76

SQL> select * from RAGHAV_bck;

NAME ROLLNO MOBILE MARKS

muskie 240 987766576 95

munni 237 859656345 76
pnni 251 77
986756453

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
nidhi 244 87
858578698

SQL>spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 10:- Write an SQL query for rollback, commit, savepoint.

SQL> create table RAGHAV(name varchar(10),roll

number(10)); Table created.

SQL> insert into RAGHAV

values('&name','&roll');

Enter value for name: rahul

Enter value for roll: 1217633

old 1: insert into RAGHAV

values('&name','&roll') new 1: insert into

RAGHAV values('rahul','1217633') 1 row

created.

SQL> /

Enter value for name:

anshul Enter value for

roll: 1217216

old 1: insert into RAGHAV values('&name','&roll')

new 1: insert into RAGHAV

values('anshul','1217216') 1 row created.

SQL> /

Enter value for name:

sahil Enter value for roll:

1217637

old 1: insert into RAGHAV

Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR
values('&name','&roll') new 1: insert into

RAGHAV values('sahil','1217637') 1 row

created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from

RAGHAV; NAME ROLL

rahul 1217633

anshul 1217216

sahil 1217637

SQL> savepoint

a1; Savepoint

created.

SQL> insert into RAGHAV

values('ab','1717152');

1 row created.

SQL> rollback to a1;

Rollback complete.

SQL> select * from

RAGHAV;

NAME ROLL

rahul 1217633

anshul 1217216

sahil 1217637

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 11:- Create Views from an existing table.

SQL> create table RAGHAV(rollno number(10),name varchar(15),branch
varchar(10),marks number(10));

Table created.

SQL> insert into RAGHAV

values('&rollno','&name','&branch','&marks');

Enter value for rollno: 1221

Enter value for name:

sejal Enter value for

branch: CSE Enter value

for marks: 89

old 1: insert into RAGHAV

values('&rollno','&name','&branch','&marks') new 1: insert

into RAGHAV values('1221','sejal','CSE','89')

1 row

created.

SQL> /

Enter value for rollno:

1542 Enter value for

name: mohit Enter

value for branch: IT

Enter value for marks:

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
97

old 1: insert into RAGHAV

values('&rollno','&name','&branch','&marks')

new 1: insert into RAGHAV values('1542','mohit','IT','97')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> /

Enter value for rollno:

1735 Enter value for

name: Vikas Enter value

for branch: MECH Enter

value for marks: 92

old 1: insert into RAGHAV

values('&rollno','&name','&branch','&marks')

new 1: insert into RAGHAV values('1735','Vikas','MECH','92')

1 row created.

SQL> select*from RAGHAV;

ROLLNO NAME BRANCH MARKS

1221 sejal CSE 89

1542 mohit IT 97

1735 Vikas MECH 92

SQL> insert into RAGHAV values(1432,'sahil','CHEMICAL',61);

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select*from RAGHAV;

ROLLNO NAME BRANCH MARKS

1221 sejal CSE 89

1542 mohit IT 97

1735 Vikas MECH 92

1432 sahil CHEMICAL 61

SQL> create table emp1(empid number(10),dept

varchar(15)); Table created.

SQL> insert into emp1

values('&empid','&dept'); Enter value for

empid: 1221

Enter value for dept: CSE

old 1: insert into emp1

values('&empid','&dept') new 1: insert

into emp1 values('1221','CSE')

1 row created.

SQL> /

Enter value for empid:

1542 Enter value for

dept: IT
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR
old 1: insert into emp1

values('&empid','&dept') new 1: insert

into emp1 values('1542','IT')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> /

Enter value for empid: 1432

Enter value for dept:

CHEMICAL

old 1: insert into emp1

values('&empid','&dept') new 1: insert into

emp1 values('1432','CHEMICAL') 1 row

created.

SQL> select*from

emp1; EMPID

DEPT

1221 CSE

1542 IT

1432 CHEMICAL

SQL> select RAGHAV.rollno,RAGHAV.name,RAGHAV.marks,emp1.dept

from RAGHAV,emp1 where RAGHAV.rollno=emp1.empid;

ROLLNO NAME MARKS DEPT

1221 sejal 89 CSE

1542 mohit 97 IT

1432 sahil 61 CHEMICAL

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR

SQL> create view V as select rollno,name from

RAGHAV; View created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select*from

V; ROLLNO

NAME

1221 sejal

1542 mohit

1735 Vikas

1432 sahil

SQL> create view v11 as select RAGHAV.rollno,RAGHAV.name,emp1.dept from

RAGHAV,emp1 where branch='CSE';

View created.

SQL> select*from v11;

ROLLNO NAME DEPT

1221 sejal CSE

1221 sejal IT

1221 sejal CHEMICAL

SQL> create view v23 as select RAGHAV.name,emp1.dept from RAGHAV,emp1

where RAGHAV.rollno=emp1.empid;

View created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select*from v23;

NAME DEPT

sejal CSE

mohit IT

sahil CHEMICAL

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 12:- Write a procedure for computing income tax of a on the

basis of following conditions : -

a) if gross pay<=40000 then I.T. rate is 0%

b) if gross pay>40000 but <=60000 then I.T rate is 10%
c) if gross pay>60000 but <=1000000 then I.T rate is 20%
d) if gross pay>1000000 then I.T rate is 30%

For this purpose create a table with name , ssn , gross salary and income
tax of the employee.
SQL>create table RAGHAV(Name varchar2(20),GrossSalary
number(10),Tax_Percentage number(10));

Table Created.

SQL> create or replace procedure IncT(Name in varchar,GrossSalary in

number)is

gross number(10);

2 income(10);

3 begin

4 gross:=GrossSalary;

5 if gross<=40000 then

6 income:=0;

7 end if;

8 if gross>40000 and gross <60000 then

9 income:=10;

10 end if;

11 if gross>60000 and gross<1000000 then

12 income:=20;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
13 end if;

14 if gross>1000000 then

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

15 income:=30;

16 end if;

17 insert into RAGHAV values(Name,GrossSalary,income);

18 end

; 19 /

Procedure created.

SQL>EXECUTE Inc_CAL('Rahul',40000);

PL/SQL procedure successfully

completed. SQL>EXECUTE

Inc_CAL('Shivam',60000);

PL/SQL procedure successfully

completed. SQL>EXECUTE

Inc_CAL('Rajiv',80000);

PL/SQL procedure successfully

completed. SQL>Select * from

RAGHAV;

Name GrossSalary Tax_Percentage

Rahul 40000 0

Shivam 60000 10

Rajiv 80000 20

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 13:- Create trigger for before insertion or update.

SQL> create table RAGHAV(name varchar2(20),roll_no varchar2(9) not null
primary key, balance varchar2(10));

Table created.

SQL> insert into RAGHAV

values('pradeep','1207233','250');

1 row created.

SQL> insert into RAGHAV

values('rahul','1207239','195');

1 row created.

SQL> insert into RAGHAV

values('neeraj','1207215','400');

1 row created.

SQL> select * from RAGHAV;

NAME ROLL_NO BALANCE

pradeep 1207233 250

rahul 1207239 195
neeraj 1207215 400

SQL> create or replace trigger tri before insert or update on RAGHAV for each row
2 begin
3 if :new.balance<=0 then
4 raise_application_error(-20000,'salary can not be less than zero');
5 end if;
6 end;
7/

Trigger created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> insert into RAGHAV

values
2 ('rohit','1207938','-256');
insert into RAGHAV values
*
ERROR at line 1:
ORA-20000: salary can not be less than
zero ORA-06512: at "SCOTT.TRI", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRI'

SQL> commit;
Commit
complete.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 14:- Write SQL queries to make procedure to find area of circle
and insert in a table.
SQL> create table RAGHAV(radius number(10),area

number(12)); Table created.

SQL> create or replace procedure pro(radius in number) is

2 r number(10);
3 area number(10);
4 begin
5 r:=radius;
6 area:=3.14*r*r;
7 insert into RAGHAV values(radius,area);
8 end
;9/

Procedure created.

SQL> execute

pro(7);

PL/SQL procedure successfully

completed. SQL> select * from

RAGHAV;

RADIUS AREA
7 154

SQL> execute pro(3);

PL/SQL procedure successfully

completed. SQL> select * from

RAGHAV;

RADIUS AREA
7 154
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR
3 28

SQL> spool off;

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

Program 15:- Write a program in PL/SQL using after update or delete using
Triggers.
SQL> create table RAGHAV_q11 (name varchar(10),salary

number(10)); Table created.

SQL> create table RAGHAV_q21 (name

varchar(10),salary number(10)); Table created.

SQL> create trigger q31 after update or delete on RAGHAV_q11 for each row
2 declare
3 oper varchar(10);
4 name varchar(10);
5 salary number(10);
6 begin
7 if updating then oper:='update';
8 end if;
9 if deleting then oper:='delete';
10 end if;
11 name:=:old.name;
12 salary:=:old.salary;
13 insert into RAGHAV_q21 values(name,salary);
14 end;
15 /

Trigger created.

SQL>insert into RAGHAV_q11

values(‘&name’,’&salary’);
Enter value for name:Ashish
Enter value for salary:30000
old 1: insert into RAGHAV_q11
values('&name','&salary') new 1: insert into
RAGHAV_q11 values('Ashish','30000')

1 row

created.

SQL> /
Enter value for name:
Aman Enter value for
salary: 67000
Subject Teacher Name: Er. Shilpa
 JMIT, RADAUR
old 1: insert into RAGHAV_q11
values('&name','&salary') new 1: insert into
RAGHAV_q11 values('Aman','67000')

1 row created.

Subject Teacher Name: Er. Shilpa

 JMIT, RADAUR
Computer Science & Engineering Semester: 5th DBMS

SQL> select * from RAGHAV_q11;

NAME SALARY
Ashish 30000
Aman 67000

SQL> update RAGHAV_q11 set salary=80000

where name='Aman'; 1 row updated.

SQL> select * from
RAGHAV_q21; NAME SALARY

Aman 67000

SQL> select * from

RAGHAV_q11; NAME
SALARY

Ashish 30000
Aman 80000
SQL>spool off;

Subject Teacher Name: Er. Shilpa