DBMS 1 1

E.G.S.
Pillay Engineering College

An Autonomous Institution Affiliated to Anna University | Approved by AICTE
Accredited by NBA [CSE/IT/ECE] | Accredited by NAAC with Grade ‘A’
One among the Top 300 Engineering Colleges in India [NIRF-2022]
Old Nagore Road, Thethi Village, Nagapattinam – 611002, Tamil Nadu, India
Department of Computer Science & Business Systems [CSBS]
LAB MANUAL
1902BS451 - DATABASE MANAGEMENT SYSTEMS
2022-2023 – Even Semester
Course Coordinator
Mr.G. Murugan
Assistant Professor CSBS
E.G.S.Pillay Engineering College
Nagapattinam
hodcsbs@egspec.org | www.egspec.org
1
SYLLABUS DATABASE MANAGEMENT SYSTEMS LAB L T P C
0 0 2 1
PREREQUISITES::
Computer Programming Languages
COURSE OBJECTIVES:
1. Learn to create and use a database
2. Be familiarized with a query language
3. Have hands on experience on DDL Commands
4. Have a good understanding of DML Commands and DCL commands
5. Familiarize advanced SQL queries.
LIST OF EXPERIMENTS:
1 Various Data definition language commands
2 Data manipulation language commands and aggregate functions
3 Set Operations, Joins and Nested Queries using commands
4 Constraints and Views
5 High level programming language extensions Control structures, procedures and functions
6 High level programming language extensions cursors and triggers
7 Normal forms
TOTAL: 35 HOURS
REQUIREMENTS:
Hardware:
Standalone desktops 30 Nos. (or) Server supporting 30 terminals or more.
Software:
Oracle / MySQL/ Sql Server DB2 or Equivalent.
FURTHER READING / CONTENT BEYOND SYLLABUS / SEMINAR :
Under MoU with Oracle Academy, In this programme extensive hands-on training on SQL and PL/SQL will be
given to students during the Lab sessions.
Writing SQL queries for Hierarchical retrieval of data (tree structured data)
Using stored procedures and Functions for implementing object level data security
Application Development using front end tools
Study of Cloud Storage
COURSE OUTCOMES:
After completion of the course, Student will be able to
CO1 Able to understand various queries and their execution
CO2 Populate and query a database using sql DML,DDL,DCL commands
CO3 Declare and enforce integrity constraints on a database
CO4 Programming pl/sql including stored procedures, stored functions, cursors, triggers
CO5 Apply the normalization techniques for development of application software to realistic problems
REFERENCES:
1.http://ilearning.oracle.com
2. http://coursera.org/
3. http://nptel.ac.in/
2
REG NO:8208E22BSR050
NAME:SRIMATHI.J
EX NO: 01 DATA DEFINITION LANGUAGE COMMANDS

(Creation of a database and writing SQL queries to retrieve information from the database)
AIM:-
To study the various DDL commands and implement them on the database.
COMMANDS:-
1. TABLE CREATION:-
SQL> create table stud (sname varchar2(30), sid varchar2(10), sage number(2), sarea
varchar2(20));
Table created.
2.DESCRIPTION OF TABLE:-
SQL> desc stud;
Name Null? Type

------------------------------------------ ---------- ----------------------
SNAME VARCHAR2(30)
SID VARCHAR2(10)
SAGE NUMBER(2)
SAREA VARCHAR2(20)
3.ALTER THE TABLE:-
~MODIFY:-
SQL>alter table stud modify ( sage number(10));
Table altered.
~ ADD:-
SQL> alter table stud add ( sdept varchar2(20));
Table altered.
SQL> desc stud;
Name Null? Type
---------------------------------------------- -------- --------------------------------
SNAME VARCHAR2(30)
SID VARCHAR2(10)
SAGE NUMBER(10)
SAREA VARCHAR2(20)
SDEPT VARCHAR2(20)
3
~ DROP:-
SQL> alter table stud drop ( sdept varchar2(20));
Table altered.
SQL> desc studs;
Name Null? Type
------------------------------------------ ---------- ---------------------------------
SNAME VARCHAR2(30)
SID VARCHAR2(10)
SAGE NUMBER(10)
SAREA VARCHAR2(20)
4.TRUNCATE THE TABLE:-
SQL> truncate table studs;
Table truncated.
SQL> desc studs;
Name Null? Type
---------------------------------------- ------------ ------------------------
SNAME VARCHAR2(30)
SID VARCHAR2(10)
SAGE NUMBER(10)
SAREA VARCHAR2(20)
SDEPT VARCHAR2(20)
5.DROP THE TABLE:-
SQL> drop table studs;
Table dropped.
RESULT:-
Thus the DDL commands have been implemented and the output is verified.
4
NAME:SRIMATHI.J
EX NO: 2 DATA MANIPULATION LANGUAGE COMMANDS
AIM:-
To study the various categories of DML commands such as logical operations, aggregate
functions, string functions , numeric functions, date functions and conversion functions.
THE ORACLE TABLE – DUAL:-

Dual is a small oracle table which consists of only one row and one column and contains
the value X in that column.
INSERT:-
This command is used to insert values into the table.
SELECT:-
This command is used to display the contents of the table or those of a particular column.
RENAME:-
This command renames the name of the table.
1.OPERATORS:-
ARITHMETIC OPERATOR:-
Various operations such as addition, multiplication, subtraction and division can be
performed using the numbers available in the table.
DISTINCT:-
This keyword is used along with select keyword to display unique values from the
specified column. It avoids duplicates during display.
CONCATENATION OPERATOR:-
This combines information from two or more columns in a sentence according to the format
specified.
LOGICAL OPERATORS:-
~AND : -
The oracle engine will process all rows in a table and displays the result only when all of the
conditions specified using the AND operator are specified.
~OR :-
The oracle engine will process all rows in a table and displays the result only when any of the
conditions specified using the OR operators are satisfied.
~NOT :-
The oracle engine will process all rows in a table and displays the result only when none of the
conditions specified using the NOT operator are specified.
~BETWEEN :-
In order to select data that is within a range of values, the between operator is used. (AND
should be included).
5
PATTERN MATCH LIKE PREDICATE : -
The use of like predicate is that it allows the comparison of one string value with another string
value, which is not identical. This is achieved by using wildcard characters which are % and _.
The purpose of % is that it matches any string and _ matches any single character.
IN AND NOT IN PREDICATE :-

The arithmetic operator = compares a single value to another single value. In case a value
needs to be compared to a list of values then the in predicate is used.The not in predicate is the
opposite of the in predicate. This will select all the rows whose values do not match all of the
values in the list.
2.NUMERIC FUNCTIONS:-
~ABS:-
It returns the absolute value of ‘n’.
~POWER:-
It returns m raised to nth power. n must be an integer else an error is returned.
~ROUND: -
It returns n rounded to m places right of the decimal point. If m is omitted, n is rounded to zero
places. m must be an integer.
~SQRT:-
It returns square root of n. n should be greater than zero.
3.STRING FUNCTIONS:-
~LOWER:-
It returns char with letters in lower case.
~INITCAP:-
It returns char with the first letter in upper case.
~UPPER: -
It returns char with all letters forced to upper case.
~SUBSTR:-
It returns a portion of char beginning at character m, exceeding up to n characters. If n is
omitted result is written up to the end character. The 1 st position of char is one.
~LENGTH: -
It returns the length of char
~LTRIM:-
It removes characters from the left of char with initial characters removed up to the 1 st
character not in set.
~RTRIM:-
It returns char with final characters removed after the last character not in the set. Set is optional.
It defaults to spaces.
~LPAD:-
It returns char1, left padded to length n with the sequence of characters in char2. char2
defaults to blanks.
6
~RPAD:-
It returns char1, right padded to length n with the characters in char2, replicated as many
times as necessary. If char2 is omitted, it is padded with blanks.
4.AGGREGATE FUNCTIONS:-
~AVG (N):-
It returns average value of n ignoring null values.
~MIN (EXPR):-
It returns minimum value of the expression.
~COUNT (EXPR):-
It returns the number of rows where expression is not null.
~COUNT (*):-
It returns the number of rows in the table including the duplicates and those with null values.
~MAX (EXPR):-
It returns maximum value of the expression.
~SUM(N):-
It returns sum of values of n.
5.DATE FUNCTIONS:-
~SYSDATE:-
The sysdate is a pseudo column that contains the current date and time. It requires no arguments
when selected from the table dual and returns the current date.
~ADD_MONTHS(D,N):-
It returns date after adding the number of months specified with the function.
~LAST_DAY(D):-
It returns the last date of the month specified with the function
~MONTHS_BETWEEN(D1,D2):-
It returns number of months between D1 and D2.
~NEXT_DAY(DATE, CHAR):-
It returns the date of the first week day named by char . char must be a day of the week.
COMMANDS
1.CREATION OF TABLE:-
SQL>create table stud (sname varchar2(30), sid varchar2(10), sage number(10), sarea
varchar2(20), sdept varchar2(20));
Table created.
2.INSERTION OF VALUES INTO THE TABLE:-
SQL> insert into stud values ('ashwin',101,19,'anna nagar','aeronautical');

1 row created.
SQL> insert into stud values ('bhavesh',102,18,'nungambakkam','marine');
1 row created.
7
SQL> insert into stud values ('pruthvik',103,20,'anna nagar','aerospace');
1 row created.
SQL> insert into stud values ('charith',104,20,'kilpauk','mechanical');
1 row created.
SQL> select * from stud;

SNAME SID SAGE SAREA SDEPT
-------------------------- ------- ---------- --------------- -------------------------
ashwin 101 19 anna nagar aeronautical
bhavesh 102 18 nungambakkam marine
pruthvik 103 20 anna nagar aerospace
charith 104 20 kilpauk mechanical
RENAMING THE TABLE ‘STUD’:-
SQL> rename stud to studs;

Table renamed.
ARITHMETIC OPERATION:-
SQL> select sname, sid+100 "stid" from studs;
SNAME stid
------------------------------ ---------
ashwin 201
bhavesh 202
pruthvik 203
charith 204
CONCATENATION OPERATOR:-
SQL> select sname || ' is a ' || sdept || ' engineer. ' AS "PROFESSION" from studs;
PROFESSION
-------------------------------------------------------------------
ashwin is a aeronautical engineer.
bhavesh is a marine engineer.
pruthvik is a aerospace engineer.
charith is a mechanical engineer.
DISPLAY ONLY DISTINCT VALUES:-
SQL> select distinct sarea from studs;

SAREA
--------------------
anna nagar
kilpauk
nungambakkam
8
USING THE WHERE CLAUSE:-
SQL> select sname,sage from studs where sage<=19;
SNAME SAGE
------------------------------- ----------
ashwin 19
bhavesh 18
BETWEEN OPERATOR:-
SQL> select sname,sarea, sid from studs where sid between 102 and 104;
SNAME SAREA SID

--------------------------- ---------------------- ------------
bhavesh nungambakkam 102
pruthvik anna nagar 103
charith kilpauk 104
IN PREDICATE:-
SQL> select sname,sarea , sid from studs where sid in(102,104);
SNAME SAREA SID

----------------------- ----------------------- ----------------
bhavesh nungambakkam 102
charith kilpauk 104
PATTERN MATCHING:-
SQL> select sname, sarea from studs where sarea like '%g%';
SNAME SAREA
--------------------------- -----------------------
ashwin anna nagar
bhavesh nungambakkam
pruthvik anna nagar
LOGICAL AND OPERATOR:_
SQL> select sname ,sid from studs where sid>102 and sarea='anna nagar';
SNAME SID
-------------------------- --------
pruthvik 103
9
LOGICAL OR OPERATOR:-
SQL> select sname ,sid from studs where sid>102 or sarea='anna nagar';
SNAME SID
------------------------------ ----------
ashwin 101
pruthvik 103
charith 104
NOT IN PREDICATE:-
SQL> select sname, sid from studs where sid not in(102,104);
SNAME SID
------------------------ -----------
ashwin 101
pruthvik 103
UPDATING THE TABLE:-
SQL> alter table studs add ( spocket varchar2(20) );

Table altered.
SQL> update studs set spocket=750 where sid=101;
1 row updated.
1 row updated.
1 row updated.
1 row updated.
SQL> select * from studs;
SNAME SID SAGE SAREA SDEPT SPOCKET
---------------- ------------ --------- ------------------- ---------------------- ------------------
ashwin 101 19 anna nagar aeronautical 750
bhavesh 102 18 nungambakkam marine 500
pruthvik 103 20 anna nagar aerospace 250
charith 104 20 kilpauk mechanical 100
AGGREGATE FUNCTIONS :-
~AVG:-
SQL> select avg( spocket ) result from studs;
RESULT
-----------
400
10
~MIN:-
SQL> select min(spocket) result from studs;

RESULT
--------------------
100
~COUNT:-
SQL> select count(spocket) result from studs;

RESULT
-------------
4
SQL> select count(*) result from studs;
RESULT
---------
4
SQL> select count(spocket) result from studs where sarea='anna nagar';
RESULT
-----------
2
~MAX:-
SQL> select max(spocket) result from studs;

RESULT
--------------------
750
~SUM:-
SQL> select sum(spocket) result from studs;

RESULT
----------------------
1600
NUMERIC FUNCTIONS:-
~ABS:-
SQL> select abs(-20) result from dual;

RESULT
------------
20
~POW:-
SQL> select power (2,10) result from dual;

RESULT
11
----------- 1024
~ROUND:-
SQL> select round(15.359,2) result from dual;

RESULT
-----------
15.36
~SQRT:-
SQL> select sqrt (36) result from dual;
RESULT
------------
6
STRING FUNCTIONS:-
~LOWER:-
SQL> select lower('ORACLE') result from dual;

RESULT
------
oracle
~UPPER:-
SQL> select upper('oracle') result from dual;

RESULT
------
ORACLE
~INTICAP:-
SQL> select initcap('Oracle') result from dual;

RESULT
------
Oracle
~SUBSTR:-
SQL> select substr('oracle' ,2 ,5) result from dual;
RESULT
-----
racle
~IPAD:-
SQL> select lpad('oracle',10,'#') result from dual; RESULT

----------
####oracle
12
~RPAD:-
SQL> select rpad ('oracle',10,'^') result from dual;
RESULT
----------
oracle^^^^
DATE FUNCTIONS:-
SQL> select sysdate from dual;

SYSDATE
---------
16-JUL-08
SQL> select sysdate,add_months(sysdate,4) result from dual;
SYSDATE RESULT
--------- ---------
16-JUL-08 16-NOV-08
SQL> select sysdate, last_day(sysdate) result from dual;
SYSDATE RESULT
--------- ---------
16-JUL-08 31-JUL-08
SQL> select sysdate, next_day(sysdate,'sunday') result from dual;
SYSDATE RESULT
--------- ---------
16-JUL-08 20-JUL-08
SQL> select months_between('09-aug-91','11-mar-90') result from dual;
RESULT
------------
16.935484
ORDER BY CLAUSE:-
SQL> select * from emppp56;

NAME ID
----------------------- ----------
x 1
a 2
g 3
d 4
SQL> select * from emppp56 order by name;

NAME ID
------------------------ ----------
a 2
d 4
g 3
x 1
13
GROUP BY CLAUSE:-
SQL> select sarea, sum(spocket) result from studs group by sarea;

SAREA RESULT
-------------------- ------------
anna nagar 1000
nungambakkam 500
kilpauk 100
HAVING CLAUSE:-
SQL> select sarea, sum(spocket) result from studs group by sarea having spocket<600;
SAREA RESULT
-------------------- ------------
nungambakkam 500
kilpauk 100
DELETION:-
SQL> delete from studs where sid=101;

1 row deleted.
SQL> select * from studs;
SNAME SID SAGE SAREA SDEPT SPOCKET

--------------------- --------- ----------- -------------------- --------------- ----------------
bhavesh 102 18 nungambakkam marine 500
pruthvik 103 20 anna nagar aerospace 250
charith 104 20 kilpauk mechanical 100
RESULT:-
Thus the DML commands have been implemented and the output is verified.
14
NAME:SRIMATHI.J
EX NO: 3 JOINS AND NESTED QUERIESCOMMANDS
AIM:-
To study the Set Operations, Joins and Nested Queries using DML commands.
COMMANDS:-
CREATING TABLES FOR PERFORMING SET OPERATIONS:-
TO CREATE PRODUCT TABLE:-
SQL> create table product(prodname varchar2(30), prodno varchar2(10));
Table created.
SQL> insert into product values('table',10001);
1 row created.
SQL> insert into product values('chair',10010);
1 row created.
SQL> insert into product values('desk',10110);
1 row created.
SQL> insert into product values('cot',11110);
1 row created.
SQL> insert into product values('sofa',10010);
1 row created.
SQL> insert into product values('tvstand',11010);
1 row created.
SQL> select * from product;

PRODNAME PRODNO
-------------------------- -------------------
table 10001
chair 10010
desk 10110
cot 11110
sofa 10010
tvstand 11010
15
TO CREATE SALE TABLE:-
SQL> create table sale(prodname varchar2(30),orderno number(10),prodno varchar2(10));
Table created.
SQL> insert into sale values('table',801,10001);
1 row created.
SQL> insert into sale values('chair',805,10010);
1 row created.
SQL> insert into sale values('desk',809,10110);
1 row created.
SQL> insert into sale values('cot',813,11110);
1 row created.
SQL> insert into sale values('sofa',817,10010);
1 row created.
SQL> select * from sale;
PRODNAME ORDERNO PRODNO

------------------------------ ---------------- --------------------
table 801 10001
chair 805 10010
desk 809 10110
cot 813 11110
sofa 817 10010
SET OPERATIONS:-
SQL> select prodname from product where prodno=10010 union select prodname from sale
where prodno=10010;
PRODNAME
------------------------------
chair
sofa
SQL> select prodname from product where prodno=11110 intersect select prodname from
sale where prodno=11110;
16
PRODNAME
------------------------------
cot
CREATING TABLES FOR DOING JOIN AND NESTED QUERY OPERATIONS

TO CREATE SSTUD1 TABLE:-
SQL> create table sstud1 ( sname varchar2(20) , place varchar2(20));
Table created.
SQL> insert into sstud1 values ( 'prajan','chennai');
1 row created.
SQL> insert into sstud1 values ( 'anand','chennai');
1 row created.
SQL> insert into sstud1 values ( 'kumar','chennai');
1 row created.
SQL> insert into sstud1 values ( 'ravi','chennai');
1 row created.
SQL> select * from sstud1;

SNAME PLACE
----------------- --------------------
prajan chennai
anand chennai
kumar chennai
ravi chennai
TO CREATE SSTUD2 TABLE:-
SQL> create table sstud2 ( sname varchar2(20), dept varchar2(10), marks number(10));
Table created.
SQL> insert into sstud2 values ('prajan','cse',700);
1 row created.
SQL> insert into sstud2 values ('anand','it',650);
17
1 row created.
SQL> insert into sstud2 values ('vasu','cse',680);
1 row created.
SQL> insert into sstud2 values ('ravi','it',600);
1 row created.
SQL> select * from sstud2;
SNAME DEPT MARKS

-------------------- -------------- ---------------
prajan cse 700
anand it 650
vasu cse 680
ravi it 600
JOIN OPERATIONS:-
SQL>select sstud1.sname, dept from sstud1 inner join sstud2 on (stud1.sname=sstud2.

name) ;
SNAME DEPT
------------- --------------
anand it
prajan cse
ravi it
SQL> select sstud1.sname, dept from sstud1 join sstud2 on ( sstud1.sname= sstud2.sname);
SNAME DEPT
------------- --------------
anand it
prajan cse
ravi it
SQL> select sstud1.sname, dept from sstud1 left outer join sstud2 on ( sstud1.sname=
sstud2.sname);
SNAME DEPT
------------- ---------------
prajan cse
anand it
ravi it
SQL> select sstud1.sname, dept from sstud1 right outer join sstud2 on ( sstud1.sname=
sstud2.sname);
18
SNAME DEPT
--------------- -------------
prajan cse
anand it
ravi it
SQL> select sstud1.sname, dept from sstud1 full outer join sstud2 on ( sstud1.sname=
sstud2.sname);
SNAME DEPT
----------------- -----------
prajan cse
anand it
ravi it
kumar cse
NESTED QUERIES:-
SQL> select sname from sstud1 where sstud1.sname in ( select sstud2.sname from
2 sstud2 );
SNAME
--------------------
anand
prajan
ravi
SQL> select sname from sstud1 where sstud1.sname not in ( select sstud2.sname from
sstud2 );
SNAME
--------------------
kumar
SQL> select sname from sstud2 where marks > some(select marks from sstud2 where
dept='cse');
SNAME
--------------------
prajan
SQL> select sname from sstud2 where marks >= some (select marks from sstud2 where
dept='cse' );
SNAME
--------------------
prajan
vasu
19
SQL> select sname from sstud2 where marks > any ( select marks from sstud2 where
dept='cse' );
SNAME
--------------------
prajan
SQL> select sname from sstud2 where marks >= any ( select marks from sstud2 where
dept='cse' );
SNAME
--------------------
prajan
vasu
SQL> select sname from sstud2 where marks > all ( select marks from sstud2 where
dept='cse' );
no rows selected
SQL> select sname from sstud2 where marks < all ( select marks from sstud2 where
dept='cse' );
SNAME
--------------------
anand
ravi
SQL> select sname from sstud1 where exists ( select sstud2.sname from sstud2 where
sstud1.sname=sstud2.sname );
SNAME
--------------------
prajan
anand
ravi
SQL> select sname from sstud1 where not exists ( select sstud2.sname from sstud2 where
sstud1.sname=sstud2.sname );
SNAME
--------------------
Kumar
20
SQL> select ename from e234 where exists(select * from e234);
ENAME
--------------------
x
y
z
a
c
SQL> select ename from e234 where not exists(select * from e234);
no rows selected
TO RENAME A COLUMN:-
NAME ID
-------------------- ----------
x 1
a 2
g 3
d 4
SQL> alter table emppp56 rename column name to emp_name;

Table altered.
SQL> desc emppp56;
Name Null? Type

------------------------------------ -------- -------------------------
EMP_NAME VARCHAR2(20)
ID NUMBER
EMP_NAME ID
-------------------- ----------
x 1
a 2
g 3
d 4
JOIN OPERATIONS:-
INNER JOIN/ NATURAL JOIN/ JOIN:-

It is a binary operation that allows us to combine certain selections and a Cartesian product into
one operation.
21
OUTER JOIN:-
It is an extension of join operation to deal with missing information.
LEFT OUTER JOIN:-

It takes tuples in the left relation that did not match with any tuple in the right relation, pads the
tuples with null values for all other attributes from the right relation and adds them to the result of
the natural join.
RIGHT OUTER JOIN:-

It takes tuples in the right relation that did not match with any tuple in the left relation, pads the
tuples with null values for all other attributes from the left relation and adds them to the result of
the natural join.
FULL OUTER JOIN:-

It combines tuples from both the left and the right relation and pads the tuples with null values
for the missing attributes and hem to the result of the natural join.
CREATING TABLES FOR DOING JOIN AND NESTED QUERY OPERATIONS:-
CREATING DEPT TABLE:-
SQL> create table dept(dno number(10),dname varchar(10),loc varchar(10));

Table created.
SQL> insert into dept values(10,'inventory','hyd');

1 row created.
SQL> insert into dept values(20,'finance','bglr');

1 row created.
SQL> insert into dept values(30,'HR','mumbai');

1 row created.
SQL> select * from dept;
DNO DNAME LOC

---------- ---------- ----------
10 inventory hyd
20 finance bglr
30 HR Mumbai
CREATING EMP2 TABLE:-
SQL> create table emp2(eno number(10),ename varchar(10),job varchar(10),Mer(10),dno

number(10));
Table created.
22
SQL> insert into emp2 values(111,'saketh','analyst',444,10);
1 row created.
SQL> insert into emp2 values(222,'sandeep','clerk',333,20);
1 row created.
SQL> insert into emp2 values(333,'jagan','manager',111,10);
1 row created.
SQL> insert into emp2 values(444,'madhu','engineer',222,40);
1 row created.
SQL> select * from emp2;
ENO ENAME JOB MGR DNO

-------- --------------- ------------ ---------- --------
111 saketh analyst 444 10
222 sandeep clerk 333 20
333 jagan manager 111 10
444 madhu engineer 222 40
1. EQUIJOIN:-
A join which contains an equal to ‘=’ operator in this joins condition
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno=d.dno;
ENO ENAME JOB DNAME LOC

------ ----------- ---------- ----------------- -------------
111 saketh analyst inventory hyd
222 sandeep clerk finance bglr
333 jagan manager inventory hyd
USING CLAUSE:-
SQL> select eno,ename,job,dname,loc from emp2 e join dept d using(dno);

------ ------------ ------------ ----------------- -------------
ON CLAUSE:-
SQL> select eno,ename,job,dname,loc from emp2 e join dept d on(e.dno=d.dno);
23
------ --------------- ------------ --------------- ------------
2. NON-EQUIJOIN:-
A join which contains an operator other than equal to ‘=’ in the join condition.
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno>d.dno;

----- ------------------ ------------ -------------- ----------------
222 sandeep clerk inventory hyd
444 madhu engineer inventory hyd
444 madhu engineer finance bglr
444 madhu engineer HR Mumbai
3.SELF JOIN -:
Joining the table itself is called self join.
SQL> select e1.eno,e2.ename,e1.job,e2.dno from emp1 e1,emp2 e2 where e1.eno=e2eno;
ENO ENAME JOB DNO

----- ----------------- --------------- -------------------
444 saketh engineer 10
333 sandeep manager 20
111 jagan analyst 10
222 madhu clerk 40
4.NATURAL JOIN :-
It compares all the common columns.
SQL> select eno,ename,job,dname,loc from emp2 natural join dept;

------ ------------- --------------- -------------- -------------
5. CROSS JOIN:-
This will give the cross product.
SQL> select eno,ename,job,dname,loc from emp2 cross join dept;

------ ------------------ ------------ ---------------- -------------
222 sandeep clerk inventory hyd
24
444 madhu engineer inventory hyd
111 saketh analyst finance bglr
333 jagan manager finance bglr
444 madhu engineer finance bglr
111 saketh analyst HR mumbai
222 sandeep clerk HR mumbai
333 jagan manager HR mumbai
444 madhu engineer HR mumbai
12 rows selected.
6. OUTER JOIN:
It gives the non matching records along with matching records.
6.1 LEFT OUTER JOIN:-

This will display the all matching records and the records which are in left hand side table
those that are in right hand side table.
SQL> select eno,ename,job,dname,loc from emp2 e left outer join dept d on(e.dno=d.dno);
(OR)
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno=d.dno(+);

------- --------------- --------------- ------------ --------------
444 madhu engineer
6.2 Right Outer Join:

This will display the all matching records and the records which are in right hand side
table those that are not in left hand side table.
SQL>select eno,ename,job,dname,loc from emp2 e right outer join dept dno(e.dno=d.dno);

(OR)
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno(+)=d.dno;

------ --------------- ------------- -------------- --------------
6.3 FULL OUTER JOIN:-
This will display the all matching records and the non matching records from both tables.
25
SQL> select eno,ename,job,dname,loc from emp2 e full outer join dept d on(e.dno=
d.dno);

------- ------------- ------------ --------------- -----------------
444 madhu engineer HR Mumbai
RESULT:-
Thus the Set Operations, Joins and Nested Queries using DML commands have been
implemented and the output is verified.
26
NAME:SRIMATHI.J
EX NO: 4 CONSTRAINTS AND VIEWS
AIM:
To create views, synonyms, sequences, indexes and save points using DDL, DML and DCL
statements.
DESCRIPTION:-
Views:-
A database view is a logical or virtual table based on a query. It is useful to think of a
view as a stored query. Views are queried just like tables.
A DBA or view owner can drop a view with the DROP VIEW command.
TYPES OF VIEWS:-
• Updatable views – Allow data manipulation
• Read only views – Do not allow data manipulation
TO CREATE THE TABLE ‘FVIEWS’:-
SQL>create table fviews(name varchar2(20),no number(5),sal number(5), dno number(5));
Table created.
TO INSERT THE VALUES INTO ‘FVIEWS’:-
SQL> insert into fviews values('xxx',1,19000,11);
1 row created.
SQL> insert into fviews values('aaa',2,19000,12);
1 row created.
SQL> insert into fviews values('yyy',3,40000,13);
1 row created.
SQL> select * from fviews;
NAME NO SAL DNO

--------- --------- --------- -------
xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
TO CREATE THE TABLE ‘DVIEWS’:-
27
SQL> create table dviews( dno number(5), dname varchar2(20));
Table created.
TO INSERT THE VALUES INTO ‘DVIEWS’:-
SQL> insert into dviews values(11,'x');
1 row created.
SQL> insert into dviews values(12,'y');
1 row created.
SQL> select * from dviews;
DNO DNAME
--------- -------------
11 x
12 y
CREATING THE VIEW ‘SVIEW’ ON ‘FVIEWS’ TABLE:-
SQL> create view sview as select name,no,sal,dno from fviews where dno=11;
View created.
SQL> select * from sview;
NAME NO SAL DNO

--------- --------- ------------ -----------
xxx 1 19000 11
Updates made on the view are reflected only on the table when the structure of the table and the
view are not similar -- proof
SQL> insert into sview values ('zzz',4,20000,14);
1 row created.
SQL> select * from sview;
NAME NO SAL DNO

---------- ---------- ---------- ----------
Xxx 1 19000 11
28
NAME NO SAL DNO
----------- -------- ------------- ----------
Xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
zzz 4 20000 14
Updates made on the view are reflected on both the view and the table when the structure of the
table and the view are similar – proof
CREATING A VIEW ‘IVIEW’ FOR THE TABLE ‘FVIEWS’:-
SQL> create view iview as select * from fviews;
View created.
SQL> select * from iview;
NAME NO SAL DNO

---------- --------- -------------- ----------------
xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
zzz 4 20000 14
PERFORMING UPDATE OPERATION:-
SQL> insert into iview values ('bbb',5,30000,15);
1 row created.
SQL> select * from iview;
NAME NO SAL DNO

---------- ------ -------- ----------
xxx 1 19000 11
bbb 5 30000 15
NAME NO SAL DNO

---------- -------- ---------- ----------
xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
zzz 4 20000 14
bbb 5 30000 15
CREATE A NEW VIEW ‘SSVIEW’ AND DROP THE VIEW:-
29
SQL> create view ssview( cusname,id) as select name, no from fviews where dno=12;
View created.
SQL> select * from ssview;
CUSNAME ID
-------------------- ---------
Aaa 2
SQL> drop view ssview;
View dropped.
TO CREATE A VIEW ‘COMBO’ USING BOTH THE TABLES ‘FVIEWS’ AND ‘DVIEWS’:-
SQL> create view combo as select name,no,sal,dviews.dno,dname from fviews,dviews where

fviews.dno=dviews.dno;
View created.
SQL> select * from combo;
NAME NO SAL DNO DNAME

---------- -------- --------- -------- --------------
xxx 1 19000 11 x
aaa 2 19000 12 y
TO PERFORM MANIPULATIONS ON THIS VIEW:-
SQL> insert into combo values('ccc',12,1000,13,'x');
insert into combo values('ccc',12,1000,13,'x')
*ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
This shows that when a view is created from two different tables no manipulations can be performed using
that view and the above error is displayed.
Integrity Constraints:-
1.Domain Constraint:-
a)NULL CONSTRAINT
b)NOT NULL CONSTRAINT
c)CHECK CONSTRAINT
30
2. Entity Constraint
a)UNIQUE
b)PRIMARY KEY
c)FOREIGN KEY
1.DOMAIN CONSTRAINT:-
a)NULL CONSTRAINT:-
SQL> create table customer(cname varchar2(20),accno number constraint consn null);
Table created.
SQL> desc customer;
Name Null? Type

------------------------------ ---------------- -----------------------
CNAME VARCHAR2(20)
ACCNO NUMBER
SQL> insert into customer values('&cname',&accno);
Enter value for cname: x

Enter value for accno: 1
old 1: insert into customer values('&cname',&accno)
new 1: insert into customer values('x',1)
1 row created.
SQL> /
Enter value for cname: y
new 1: insert into customer values('y',2)
1 row created.
SQL> insert into customer values('&cname',&accno);
Enter value for cname: z

Enter value for accno: null
new 1: insert into customer values('z',null)
1 row created.
31
SQL> select * from customer;
CNAME ACCNO
-------------------- ----------------
x 1
y 2
z
SQL> update customer set accno=3 where cname='z';
1 row updated.
SQL> select * from customer;
CNAME ACCNO
-------------------- -----------------
x 1
y 2
z 3
b)NOT NULL CONSTRAINT:-
SQL>create table borrower(cname varchar2(20),loan_no number constraint consl not null);
Table created.
SQL> insert into borrower values('&cname',&loan_no);

Enter value for loan_no: 1
old 1: insert into borrower values('&cname',&loan_no)
new 1: insert into borrower values('x',1)
1 row created.
SQL> /
Enter value for loan_no: 2
new 1: insert into borrower values('y',2)
1 row created.
32
SQL> /
Enter value for cname: z
Enter value for loan_no: null
new 1: insert into borrower values('z',null)
insert into borrower values('z',null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."BORROWER"."LOAN_NO")
SQL> /
Enter value for cname: a
Enter value for loan_no:
new 1: insert into borrower values('a',)
insert into borrower values('a',)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select * from borrower;
CNAME LOAN_NO
-------------------- ------------------
x 1
y 2
C) CHECK CONSTRAINT:-
SQL> create table withdraw(cname varchar2(20),balance number constraint consc

check(balance>500));
Table created.
SQL> insert into withdraw values('&cname',&balance);

Enter value for balance: 10000
old 1: insert into withdraw values('&cname',&balance)
new 1: insert into withdraw values('x',10000)
1 row created.
33
SQL> /
new 1: insert into withdraw values('y',200)
insert into withdraw values('y',200)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CONSC) violated
SQL> /
new 1: insert into withdraw values('y',20000)
1 row created.
SQL> select * from withdraw;
CNAME BALANCE
-------------------- ---------------
x 10000
y 20000
SQL> update withdraw set balance=balance-9700 where cname='x';
update withdraw set balance=balance-9700 where cname='x'

*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CONSC) violated
SQL> create table company1(ename varchar2(20),cname varchar2(20),place varchar2(20)

constraint consp check(place in ('chennai','madurai','trichy')));
Table created.
SQL> insert into company1 values('&emane','&cname','&place');
Enter value for ename: x

Enter value for cname: tcs
Enter value for place: chennai
old 1: insert into company1 values('&ename','&cname','&place')
new 1: insert into company1 values('x','tcs','chennai')
1 row created.
34
SQL> /
Enter value for ename: y
Enter value for place: madurai
new 1: insert into company1 values('y','tcs','madurai')
insert into company1 values('y','tcs','madurai')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CONSP) violated
SQL> /
Enter value for place: madurai
new 1: insert into company1 values('y','tcs','madurai')
1 row created.
SQL> /
Enter value for ename: z
Enter value for cname: wipro
Enter value for place: adyar
old 1: insert into company1 values('&emane','&cname','&place')
new 1: insert into company1 values('z','wipro','adyar')
insert into company1 values('z','wipro','adyar')
*
ERROR at line 1:
SQL> select * from company1;
ENAME CNAME PLACE

-------------------- ------------------ ----------------
x tcs chennai
y tcs madurai
SQL> update company1 set place='vadapalani' where ename='x';
update company1 set place='vadapalani' where ename='x'

*
ERROR at line 1:
35
2. ENTITY INTEGRITY CONSTRAINT:-
a)UNIQUE CONSTRAINT:-
SQL> create table company(comp_name varchar2(20) not null unique,location

varchar2(20) not null unique);
Table created.
SQL> desc company;
Name Null? Type

----------------------------- ------------- -----------------------------------------
COMP_NAME NOT NULL VARCHAR2(20)
LOCATION NOT NULL VARCHAR2(20)
SQL> insert into company values('&comp_name','&location');
Enter value for comp_name: tcs

Enter value for location: vadapalani
old 1: insert into company values('&comp_name','&location')
new 1: insert into company values('tcs','vadapalani')
1 row created.
SQL> /
Enter value for comp_name: tcs
Enter value for location: adyar
new 1: insert into company values('tcs','adyar')
insert into company values('tcs','adyar')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C003014) violated
SQL> /
Enter value for comp_name: cts
Enter value for location: vadapalani
new 1: insert into company values('cts','vadapalani')
insert into company values('cts','vadapalani')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C003015) violated
36
SQL> /
Enter value for comp_name: null
Enter value for location: solinganalur
new 1: insert into company values('null','solinganalur')
1 row created.
SQL> /
Enter value for comp_name:
Enter value for location: padi
new 1: insert into company values(','padi')
insert into company values(,'padi')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."COMPANY"."COMP_NAME")
SQL> /
Enter value for comp_name: wipro
Enter value for location:
new 1: insert into company values('wipro','')
insert into company values('wipro','')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."COMPANY"."LOCATION")
SQL> select * from company;
COMP_NAME LOCATION
--------------------- ------------------
tcs vadapalani
null solinganalur
SQL> create table account(accno number constraint consu unique);
Table created.
SQL> insert into account values(&accno);

old 1: insert into account values(&accno)
new 1: insert into account values(1)
1 row created.
37
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
insert into account values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CONSU) violated
SQL> alter table account add(cname varchar2(20));
Table altered.
SQL> desc account;
Name Null? Type

------------------------------------ ------- -----------------------
ACCNO NUMBER
CNAME VARCHAR2(20)
SQL> alter table account add constraint consu unique(cname);
Table altered.
SQL> desc account;
Name Null? Type

------------------------------------- -------- ------------------------
ACCNO NUMBER
CNAME VARCHAR2(20)
38
SQL> select * from account;
ACCNO CNAME
----------- ------------
1
2
3
SQL> update account set cname='y' where accno=1;
1 row updated.
SQL> update account set cname='z' where accno=2;
1 row updated.
SQL> update account set cname='a' where accno=3;
1 row updated.
SQL> select * from account;
ACCNO CNAME
----------- ------------
1 y
2 z
3 a
SQL> create table depositor(cname varchar2(20),accno number,constraint consuni
unique(cname,accno));
Table created.
SQL> insert into depositor values('&cname',&accno);

old 1: insert into depositor values('&cname',&accno)
new 1: insert into depositor values('x',1)
1 row created.
SQL> /
new 1: insert into depositor values('x',2)
1 row created.
39
SQL> /
new 1: insert into depositor values('y',2)
1 row created.
SQL> /
new 1: insert into depositor values('y',2)
insert into depositor values('y',2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CONSUNI) violated
SQL> select * from depositor;
CNAME ACCNO
------------------ -------------
x 1
x 2
y 2
PRIMARY KEY CONSTRAINTS:-
SQL> create table empv(name varchar2(20),id number not null constraint cons_pri
primary key);
Table created.
SQL> desc empv;

Name Null? Type
--------------------------------- -------- --------------------------------
NAME VARCHAR2(20)
ID NOT NULL NUMBER
SQL> insert into empv values('&name',&id);
Enter value for name: john

Enter value for id: 11
old 1: insert into empv values('&name',&id)
new 1: insert into empv values('john',11)
1 row created.
40
SQL> /
Enter value for name: henry
new 1: insert into empv values('henry',11)
insert into empv values('henry',11)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CONS_PRI) violated
SQL> /
Enter value for name: henry
new 1: insert into empv values('henry',12)
1 row created.
SQL> select * from empv;
NAME ID
-------------------- ----------
john 11
henry 12
FOREIGN KEY CONSTRAINTS:-
SQL> create table project(pname varchar2(20),id number not null,constraint cons_fr

foreign key(id) references empv(id));
Table created.
SQL> desc project;
Name Null? Type

------------------------------------ ------- -------------------------------
PNAME VARCHAR2(20)
ID NOT NULL NUMBER
SQL> insert into project values('&pname',&id);
Enter value for pname: banking_system

old 1: insert into project values('&pname',&id)
new 1: insert into project values('banking_system',11)
1 row created.
41
SQL> /
Enter value for pname: banking_system
new 1: insert into project values('banking_system',12)
1 row created.
SQL> /
Enter value for pname: airline
new 1: insert into project values('airline',11)
1 row created.
SQL> /
Enter value for pname: payroll
new 1: insert into project values('payroll',12)
1 row created.
SQL> select * from project;
PNAME ID
-------------------------- --------------
banking_system 11
banking_system 12
airline 11
payroll 12
SQL> select name,empv.id,pname from empv,project where empv.id=project.id;
NAME ID PNAME
------------------------ -------- -----------------
john 11 banking_system
henry 12 banking_system
john 11 airline
henry 12 payroll
TO DISABLE THE RESPECTIVE CONSTRAINT:-
SQL> alter table empv disable constraint cons_pri;
Table altered.
42
Enter value for name: korth

new 1: insert into empv values('korth',12)
1 row created.
NAME ID
----------------------- ---------
john 11
henry 12
korth 12
TO ENABLE THE RESPECTIVE CONSTRAINT:-
SQL> alter table empv enable constraint cons_pri;
alter table empv enable constraint cons_pri

*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.CONS_PRI) - primary key violated
SQL> update empv set id=13 where name='korth';
1 row updated.
NAME ID
-------------------- ----------
john 11
henry 12
korth 13
SQL> alter table empv enable constraint cons_pri;Table altered.

SQL> desc empv;
Name Null? Type

-------------------------- ----------- -------------------------------------
NAME VARCHAR2(20)
ID NOT NULL NUMBER
43
Enter value for name: smith

new 1: insert into empv values('smith',13)
insert into empv values('smith',13)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CONS_PRI) violated
SQL> create table emp1(ename varchar2(15),eid number);
Table created.
SQL> create table dept1(dno number,dname varchar2(15));
Table created.
SQL> alter table emp1 add constraint con_eid primary key(eid);
Table altered.
SQL> alter table dept1 add constraint con_dno primary key(dno);
Table altered.
SQL> alter table emp1 add(dno number);
Table altered.
SQL> desc emp1;
Name Null? Type

--------------------------- ------------ --------------------------------
ENAME VARCHAR2(15)
EID NOT NULL NUMBER
DNO NUMBER
SQL> alter table emp1 add constraint conf_dno foreign key(dno) references dept1(dno);
Table altered.
SQL> desc dept1;
44
Name Null? Type
---------------------------- ------------- -------------------------------------
DNO NOT NULL NUMBER
DNAME VARCHAR2(15)
SQL> insert into emp1 values('&ename',&eid,&dno);

Enter value for eid: 1
Enter value for dno: 1
old 1: insert into emp1 values('&ename',&eid,&dno)
new 1: insert into emp1 values('x',1,1)
insert into emp1 values('x',1,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CONF_DNO) violated - parent key not
found
SQL> insert into dept1 values(&dno,'&dname');

Enter value for dname: it
old 1: insert into dept1 values(&dno,'&dname')
new 1: insert into dept1 values(1,'it')
1 row created.
SQL> /
Enter value for dname: cse
new 1: insert into dept1 values(2,'cse')
1 row created.
SQL> /
Enter value for dname: ece
new 1: insert into dept1 values(3,'ece')
1 row created.
SQL> insert into dept1 values(&dno,'&dname');

Enter value for dname: eee
45
new 1: insert into dept1 values(1,'eee')
insert into dept1 values(1,'eee')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CON_DNO) violated
SQL> select * from dept1;
DNO DNAME
---------- ---------------
1 it
2 cse
3 ece

new 1: insert into emp1 values('x',1,1)
1 row created.
SQL> /
new 1: insert into emp1 values('y',2,1)
1 row created.
SQL> /
Enter value for ename: a
new 1: insert into emp1 values('a',3,1)
1 row created.
SQL> /
Enter value for ename: b
46
new 1: insert into emp1 values('b',4,2)
1 row created.
SQL> /
Enter value for ename: c
new 1: insert into emp1 values('c',5,2)
1 row created.
SQL> /
Enter value for ename: d
new 1: insert into emp1 values('d',6,3)
1 row created.
SQL> /
Enter value for ename: e
new 1: insert into emp1 values('e',6,3)
insert into emp1 values('e',6,3)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CON_EID) violated
SQL> /
Enter value for ename: e
new 1: insert into emp1 values('e',7,3)
1 row created.

ENAME EID DNO
--------------- ---------- ----------
x 1 1
y 2 1
47
a 3 1
b 4 2
c 5 2
d 6 3
e 7 3
7 rows selected.
SQL> select * from dept1;

DNO DNAME
---------- ---------------
1 it
2 cse
3 ece
SQL> select * from emp1,dept1 where emp1.dno=dept1.dno;

ENAME EID DNO DNO DNAME
--------------- ---------- ---------- ---------- ---------------
x 1 1 1 it
y 2 1 1 it
a 3 1 1 it
b 4 2 2 cse
c 5 2 2 cse
d 6 3 3 ece
e 7 3 3 ece
7 rows selected.
SQL> select ename,dname from emp1,dept1 where emp1.dno=dept1.dno;
ENAME DNAME
----------------- ------------------
x it
y it
a it
b cse
c cse
d ece
e ece
7 rows selected.
SQL> select ename,dname from emp1,dept1 where emp1.dno=dept1.dno and dname='it';
ENAME DNAME
--------------- ---------------
x it
y it
a it
48
SQL> select ename,dno,dname from emp1,dept1 where emp1.dno=dept1.dno;
select ename,dno,dname from emp1,dept1 where emp1.dno=dept1.dno

*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> select ename,emp1.dno,dname from emp1,dept1 where emp1.dno=dept1.dno;
ENAME DNO DNAME

--------------- ----------- --------------
x 1 it
y 1 it
a 1 it
b 2 cse
c 2 cse
d 3 ece
e 3 ece
7 rows selected.
Enter value for ename: f

new 1: insert into emp1 values('f',8,4)
insert into emp1 values('f',8,4)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CONF_DNO) violated - parent key not
found
SQL> /
Enter value for ename: f
new 1: insert into emp1 values('f',8,2)
1 row created.
49
ENAME EID DNO

--------------- ---------- ----------
x 1 1
y 2 1
a 3 1
b 4 2
c 5 2
d 6 3
e 7 3
f 8 2
8 rows selected.
RESULT:-
Thus the Views and Constraints have been implemented and the output is verified.
50
NAME:SRIMATHI.J
EX NO: 5 HIGH LEVEL PROGRAMMING LANGUAGE EXTENSIONS

(CONTROL STRUCTURES, PROCEDURES AND FUNCTIONS)
AIM:-
To write a PL/SQL block to create control structures, procedures and functions
PROCEDURE:
FUNCTIONS – SYNTAX:-
create or replace function <function name> (argument in datatype,……) return datatype {is,as}
variable declaration;
constant declaration;
begin
PL/SQL subprogram body;
exception
exception PL/SQL block;
end;
CREATE THE TABLE ‘ITTRAIN’ TO BE USED FOR FUNCTIONS:-
SQL>create table ittrain ( tno number(10), tfare number(10));
Table created.
SQL>insert into ittrain values (1001, 550);

1 row created.
SQL>insert into ittrain values (1002, 600);

1 row created.
SQL>select * from ittrain;

TNO TFARE
--------- ------------
1001 550
1002 600
PROGRAM FOR FUNCTION AND IT’S EXECUTION:-
SQL> create function aaa (trainnumber number) return number is

trainfunction ittrain.tfare % type;
begin
select tfare into trainfunction from ittrain where tno=trainnumber;
return(trainfunction);
end;
/
Function created.
51
SQL> set serveroutput on;
SQL> declare
total number;
begin
total:=aaa (1001);
dbms_output.put_line('Train fare is Rs. '||total);
end;
/
Train fare is Rs.550
PL/SQL procedure successfully completed.
FACTORIAL OF A NUMBER USING FUNCTION — PROGRAM AND EXECUTION:-
SQL> create function itfact (a number) return number is

fact number:=1;
b number;
begin
b:=a;
while b>0
loop
fact:=fact*b;
b:=b-1;
end loop;
return(fact);
end;
/
Function created.
SQL> set serveroutput on;
SQL> declare
a number:=7;
f number(10);
begin
f:=itfact(a);
dbms_ output .put_line(‘The factorial of the given number is’||f);
end;
/
The factorial of the given number is 5040
RESULT:-
Thus the control structures, procedures and functions have been implemented and its
output is verified.
52
REG NO:
8208E22BSR050
EX NO: 6 CURSORS AND TRIGGERS
AIM:-
To write cursor and trigger program using sql.
COMMANDS:-
TO CREATE THE TABLE BILL:-
SQL> create table bill(name varchar2(10), address varchar2(20), city varchar2(20), unit
number(10));
Table created.
SQL> insert into bill values('&name','&addess','&city','&unit');
Enter value for name: yuva

Enter value for addess: srivi
Enter value for city: srivilliputur
Enter value for unit: 100
old 1: insert into bill values('&name','&addess','&city','&unit')
new 1: insert into bill values('yuva','srivi','srivilliputur','100')
1 row created.
SQL> /
Enter value for name: nithya
Enter value for addess: Lakshmi nagar
Enter value for city: sivakasi
new 1: insert into bill values('nithya','Lakshmi nagar','sivakasi','200')
1 row created.
SQL>/
Enter value for name: maya
Enter value for addess: housing board
Enter value for city: sivakasi
new 1: insert into bill values('maya','housing board','sivakasi','300')
1 row created.
53
SQL> /
Enter value for name: jeeva
Enter value for addess: RRR nagar
Enter value for city: sivaganagai
new 1: insert into bill values('jeeva','RRR nagar','sivaganagai','400')
1 row created.
SQL> select * from bill;
NAME ADDRESS CITY UNIT

---------- -------------------- -------------------- -----------------
yuva srivi srivilliputur 100
nithya Lakshmi nagar sivakasi 200
maya housing board sivakasi 300
jeeva RRR nagar sivaganagai 400
SQL>declare
2 cursor c is select* from bill;
3 b bill %ROWTYPE;
4 begin
5 open c;
6 dbms_output.putline(‘Name Address City Unit Amount’);
7 loop
8 fetch c into b;
9 if(c %notfound )then
10 exit;
11 else
12 if(b.unit<=100)
13 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||' '||b.uni t*1);
14 elsif(b.unit>100 and b.unit<=200) then
15 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||' '||b. unit*2);
17 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||' '||b. unit*3);
19 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||' '||b.unit* 4);
20 else
21 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||' '||b.unit* 5);
22 end if;
23 end if;
24 end loop;
25 close c;
26 end;
27 /
54
Name Address city Unit Amount
yuva srivi srivilliputur 100 100
nithya Lakshmi nagar sivakasi 200 400
maya housing board sivakasi 300 900
jeeva RRR nagar sivaganagai 400 1600
TRIGGER:-
DEFINITION:-
A trigger is a statement that is executed automatically by the system as a sideeffect of a

modification to the database. The parts of a trigger are,
Trigger statement:-
Specifies the DML statements and fires the trigger body. It alsospecifies the table to which
the trigger is associated.
Trigger body or trigger action:-
It is a PL/SQL block that is executed when the triggering statement is used.
Trigger restriction:-
Restrictions on the trigger can be achieved
The different uses of triggers are as follows,
To generate data automatically.

To enforce complex integrity constraints.
To customize complex securing authorizations
To maintain the replicate table.
To audit data modifications.
TYPES OF TRIGGERS:-
The various types of triggers are as follows,
Before: It fires the trigger before executing the trigger statement.

After: It fires the trigger after executing the trigger statement.
For each row: It specifies that the trigger fires once per row.
For each statement: This is the default trigger that is invoked. It specifies that the trigger
fires once per statement.
VARIABLES USED IN TRIGGERS:-
:new
:old
These two variables retain the new and old values of the column updated in the database. The
values in these variables can be used in the database triggers for data manipulation
55
SYNTAX:-
create or replace trigger triggername [before/after] {DML statements}
on [tablename] [for each row/statement]
begin
-------------------------
-------------------------
-------------------------
exception
end;
USER DEFINED ERROR MESSAGE:-
The package “raise_application_error” is used to issue the user defined error messages
Syntax: raise_application_error(error number,‘error message‘);
The error number can lie between -20000 and -20999.
The error message should be a character string.
TO CREATE THE TABLE ‘ITEMPLS’:-
SQL> create table itempls (ename varchar2(10), eid number(5), salary number(10));
Table created.
SQL> insert into itempls values('xxx',11,10000);
1 row created.
SQL> insert into itempls values('yyy',12,10500);
1 row created.
SQL> insert into itempls values('zzz',13,15500);
1 row created.
SQL> select * from itempls;
ENAME EID SALARY

---------- --------- --------------
xxx 11 10000
yyy 12 10500
zzz 13 15500
56
TO CREATE A SIMPLE TRIGGER THAT DOES NOT ALLOW INSERT UPDATE AND DELETE
OPERATIONS ON THE TABLE:-
SQL> create trigger ittrigg before insert or update or delete on itempls for each row
2 begin
3 raise_application_error(-20010,'You cannot do manipulation');
4 end;
5/
Trigger created.
ORA-20010: You cannot do manipulation

ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
DELETE OPERATION:-
SQL> delete from itempls where ename='xxx';
delete from itempls where ename='xxx'

*
ERROR at line 1:
UPDATE OPERATION:-
SQL> update itempls set eid=15 where ename='yyy';
update itempls set eid=15 where ename='yyy'

*
ERROR at line 1:
TO DROP THE CREATED TRIGGER:-
SQL> drop trigger ittrigg;
Trigger dropped.
57
TO CREATE A TRIGGER THAT RAISES AN USER DEFINED ERROR MESSAGE AND DOES NOT
ALLOW UPDATION AND INSERTION:-
SQL> create trigger ittriggs before insert or update of salary on itempls for each row
declare
2 triggsal itempls.salary%type;
3 begin
4 select salary into triggsal from itempls where eid=12;
5 if(:new.salary>triggsal or :new.salary<triggsal) then
6 raise_application_error(-20100,'Salary has not been changed');
7 end if;
8 end;
9 /
Trigger created.
INSERT OPERATION:-
SQL> insert into itempls values ('bbb',16,45000);
insert into itempls values ('bbb',16,45000)

*
ERROR at line 1:
ORA-04098: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation
UPDATE OPERATION:-
SQL> update itempls set eid=18 where ename='zzz';
update itempls set eid=18 where ename='zzz'

*
ERROR at line 1:
ORA-04298: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation
.
RESULT: -
Thus the program for cursor and trigger has been implemented using sql is executed and its
output is verified.
58
NAME:SRIMATHI.J
EX NO: 7 NORMAL FORMS
AIM:-
To write normal form 1NF,2NF, 3NF program using sql.
COMMANDS:-
TO CREATE THE TABLE Courses1 :-
SQL> create table courses1 (course varchar2(10), department varchar2(5), lecturer

varchar2(10),textbook varchar2(10));
Table created.
SQL> insert into itempls values(' Relational Databases ', ‘Computer Science’,’ Jeremy Brown’,
‘Database Systems’);
1 row created.
Normalize a table that is in the first normal form (1NF) into a set of tables in the third normal form
(3NF).
Database normalization is a very important concept in database design. Every database designer should
know the rules for differ ent nor mal for ms . In practice, the most important nor mal for m is the
third nor mal for m (3NF). If you want to be a database designer, you should know how to check if a
table is in 3NF. You should also know how to normalize a table that’s not in 3NF into one in 3NF.
As you recall, a table is in 3NF if:

1. It is in second normal form (2NF).
2. All non-prime attributes are directly (non-transitively) dependent on the entire candidate key.
Typically, you normalize a table from 1NF to 3NF in two steps: first you normalize it into 2NF, then you
normalize that into 3NF. In this article, we’ll show you an example of normalization from 1NF through
2NF into 3NF. We’ll also discuss problems with 1NF tables that are solved when you normalize them into
2NF.
Normalization to 2NF
We’ll work with a table named Courses1, which contains information about courses offered at a certain
university: the titles of the courses, the lecturers, the departments, and the recommended textbooks for
each course. The table is in the first normal form, since all column values are atomic. Here’s the table:
Courses1
course department lecturer textbook
Relational Databases Computer Science Jeremy Brown Database Systems
Relational Databases Computer Science Jeremy Brown Introduction to Databases
59
course department lecturer textbook
Cloud Databases Computer Science Jeremy Brown Database Systems
Cloud Databases Computer Science Jeremy Brown BigQuery Manual
Algorithms Computer Science James Cormen Intro to Algorithms
Data Science Mathematics Jane Downing Database Systems
Data Science Mathematics Jane Downing Statistics
Calculus Mathematics John Smith Intro to Calculus
There are several problems with various operations on data in this table:
 INSERT: You can't add a course with no textbooks.
 UPDATE: To change the lecturer for the course Relational Databases, you have to change two
rows.
 DELETE: If you remove the Algorithms course, you also remove the lecturer James Cormen.
These problems come from the fact that this table is not in 2NF. Let’s see why. There are three non-
trivial functional dependencies in this table:
 course, textbook -> lecturer, department: The course and textbook determine the lecturer and
the department.
 course -> lecturer, department: The course determines the lecturer and the department.
 lecturer -> department: The lecturer determines the department.
The candidate key in this table is the set {course, textbook}.

It is not in 2NF, because we have functional dependencies with only a part of the candidate key on the left
hand-side of a dependency. The functional dependency course -> lecturer, department violates the rules for
2NF.
To normalize this, we need to get rid of the functional dependency that violates 2NF: course -
> department, lecturer.
The normalization procedure usually involves decomposing a table into two or more tables that contain the
same information. In this case, we’ll extract the columns of the functional dependency violating 2NF, that
is, course, department, and lecturer, into a separate table, Courses2, with the candidate key {course}. The
remaining column in Courses1 is the textbook column. Of course, we can’t just put it into a new table,
because we’ll lose the course-textbook relationship. So, we will put both columns, course and textbook,
into another table, CourseTextbooks.
To decompose a table into a set of columns, you identify these columns, take the data from those columns
only, and remove the duplicates. For example, we create a table named Courses2 by first taking the
columns course, department, and lecturer from Courses1. We then take the data from these columns and
remove the duplicates. There are two rows with all of the values Relational Databases, Computer Science,
and Jeremy Brown in Courses1, but there is only one row with all of these values in Courses2.
60
Here’s the final result of the split:
Courses2
course department lecturer
Relational Databases Computer Science Jeremy Brown
Cloud Databases Computer Science Jeremy Brown
Algorithms Computer Science James Cormen
Data Science Mathematics Jane Downing
Calculus Mathematics John Smith

CourseTextbooks
course textbook
Relational Databases Database Systems
Relational Databases Introduction to Databases
Cloud Databases Database Systems
Cloud Databases BigQuery Manual
Algorithms Intro to Algorithms
Data Science Database Systems
Data Science Statistics
Calculus Intro to Calculus

You can reconstruct the same information in Courses1: simply join the data
from Courses2 and CourseTextbooks.
Both of these tables are in 2NF. There are no functional dependencies within CourseTextbooks, and the
candidate key is {course, textbook}. The functional dependencies in Courses1 are:
 course -> department, lecturer.

 lecturer -> department.
The candidate key is {course}, and there are no functional dependencies on only a part of a key, so it is in
2NF.
The following problems have been removed in the new database:
 INSERT: You can now add a course with no textbooks. Simply add the course
to Courses2 without adding rows to CourseTextbooks.
 UPDATE: You can change the lecturer for the course Relational Databases by changing just one
row in Courses2.
This problem still persists:
61
 DELETE: If we remove the Algorithms course, we also remove the lecturer James Cormen.
We’ll address this problem in a little.
The Algorithm for Normalizing to 2NF

Here’s the general algorithm for normalizing a table from 1NF to 2NF.
Suppose you have a table R with scheme S which is in 1NF but not in 2NF. Let A -> B be a functional
dependency that violates the rules for 2NF, and suppose that the sets A and B are distinct (A ∩ B = ∅).
 Let C = S – (A U B). In other words:
o A = attributes on the left-hand side of the functional dependency.
o B = attributes on the right-hand side of the functional dependency.
o C = all other attributes.
 We can split R into two parts:
o R1, with scheme C U A.
o R2, with scheme A U B.
 The original relation can be recovered as the natural join of R1 and R2: R = R1 NATURAL JOIN
R2.
Normalization to 3NF
Let’s look again at Courses2. It is in 2NF but not in 3NF.
Courses2
course department lecturer
Relational Databases Computer Science Jeremy Brown
Cloud Databases Computer Science Jeremy Brown
Algorithms Computer Science James Cormen
Data Science Mathematics Jane Downing
Calculus Mathematics John Smith

We have the following functional dependencies in this table:
 course -> lecturer, department.
 lecturer -> department.
The candidate key is {course}.

The table is not in 3NF, because there is a transitive functional dependency from the candidate
key course to department (via course -> lecturer and lecturer -> department). The functional
dependency lecturer -> department violates the rules for 3NF.
To convert Course2 into 3NF, we have to remove this functional dependency. The procedure is similar to
the one we performed for 2NF: we extract the columns lecturer and department into a separate
table, Lecturers. The remaining column in Courses2 is course. We need to keep the course-lecturer
connection, so the other table is Courses3 with columns course and lecturer.
Here is the result:
62
Lecturers
lecturer department
Jeremy Brown Computer Science
James Cormen Computer Science
Jane Downing Mathematics
John Smith Mathematics
Courses3
course lecturer
Relational Databases Jeremy Brown
Cloud Databases Jeremy Brown
Algorithms James Cormen
Data Science Jane Downing
Calculus John Smith

The problems we had in 2NF are now resolved:
 INSERT: You can now add a course with no textbooks. Simply add the course
to Courses3 without adding rows to CourseTextbooks.
 UPDATE: You can change the lecturer for Relational Databases by changing just one row
in Courses3.
 DELETE: Removing the Algorithms course does not remove the lecturer James Cormen.
Get Started With Database Normalization

To normalize a table from 1NF to 3NF, you need to normalize it to 2NF first then to 3NF. In the
normalization process, you decompose a table into multiple tables that contain the same information as the
original table. The normalization process usually removes many problems related to data modification.
RESULT: -
Thus the program study for normal forms has been implemented using sql is executed and its output
is verified.
63
64

DBMS 1 1

Uploaded by

Document Informationclick to expand document informationThis my document

Document Informationclick to expand document information

Copyright:

Available Formats

DBMS 1 1

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS 1 1

Uploaded by

Copyright:

Available Formats

E.G.S.

Pillay Engineering College

Department of Computer Science & Business Systems [CSBS]

CO2 Populate and query a database using sql DML,DDL,DCL commands

CO3 Declare and enforce integrity constraints on a database

EX NO: 01 DATA DEFINITION LANGUAGE COMMANDS

Name Null? Type

EX NO: 2 DATA MANIPULATION LANGUAGE COMMANDS

THE ORACLE TABLE – DUAL:-

IN AND NOT IN PREDICATE :-

2.INSERTION OF VALUES INTO THE TABLE:-

SQL> insert into stud values ('ashwin',101,19,'anna nagar','aeronautical');

SQL> select * from stud;

RENAMING THE TABLE ‘STUD’:-

SQL> rename stud to studs;

SQL> select sname, sid+100 "stid" from studs;

DISPLAY ONLY DISTINCT VALUES:-

SQL> select distinct sarea from studs;

SQL> select sname,sage from studs where sage<=19;

SNAME SAREA SID

SQL> select sname,sarea , sid from studs where sid in(102,104);

SNAME SAREA SID

LOGICAL AND OPERATOR:_

UPDATING THE TABLE:-

SQL> alter table studs add ( spocket varchar2(20) );

SQL> select min(spocket) result from studs;

SQL> select count(spocket) result from studs;

SQL> select max(spocket) result from studs;

SQL> select sum(spocket) result from studs;

SQL> select abs(-20) result from dual;

SQL> select power (2,10) result from dual;

SQL> select round(15.359,2) result from dual;

SQL> select lower('ORACLE') result from dual;

SQL> select upper('oracle') result from dual;

SQL> select initcap('Oracle') result from dual;

SQL> select substr('oracle' ,2 ,5) result from dual;

SQL> select lpad('oracle',10,'#') result from dual; RESULT

SQL> select rpad ('oracle',10,'^') result from dual;

SQL> select sysdate from dual;

SQL> select * from emppp56;

SQL> select * from emppp56 order by name;

SQL> select sarea, sum(spocket) result from studs group by sarea;

SQL> delete from studs where sid=101;

SQL> select * from studs;

SNAME SID SAGE SAREA SDEPT SPOCKET

EX NO: 3 JOINS AND NESTED QUERIESCOMMANDS

CREATING TABLES FOR PERFORMING SET OPERATIONS:-

TO CREATE PRODUCT TABLE:-

SQL> create table product(prodname varchar2(30), prodno varchar2(10));

SQL> insert into product values('table',10001);

SQL> insert into product values('chair',10010);

SQL> insert into product values('desk',10110);

SQL> insert into product values('cot',11110);

SQL> insert into product values('sofa',10010);

SQL> insert into product values('tvstand',11010);

SQL> select * from product;

SQL> create table sale(prodname varchar2(30),orderno number(10),prodno varchar2(10));