SRI SAIRAM ENGINEERING COLLEGE

SAI LEO NAGAR,WEST TAMBARAM,

CHENNAI-44

LAB MANUAL

20AMPT301 DATABASE MANAGEMENT SYSTEMS

LABORATORY

II YEAR / III SEM

BATCH:2024 – 2028

B.TECH ARTIFICIAL INTELLIGENCE AND DATA SCIENCE

ACADEMIC YEAR: 2025– 2026

 INDEX

EXP NO LIST OF EXPERIMENTS PAGE NO

1a DATA DEFINITION LANGUAGE 3

DATA MANIPULATION LANGUAGE

1b 7

1c TRANSACTION CONTROL LANGUAGE (TCL) COMMANDS 11

2 DATABASE QUERYING -SIMPLE QUERIES, NESTED QUERIES, 13

SUB QUERIES AND JOINS

3 VIEWS, SYNONYMS, SEQUENCES, INDEXES 37

IMPLICIT AND EXPLICIT CURSORS

4 47

5i BASIC PL/SQL PROGRAM 50

5a SUM OF 2 NUMBERS 52

SERIES OF NUMBERS (FOR LOOP)

5b 53

SERIES OF NUMBERS (WHILE LOOP)

5c 54

5d DEBIT FROM ACCOUNT 55

5e SUM OF A SERIES 57

5f FACTORIAL OF A NUMBER 58
5g REVERSING A STRING 59

5h FIBONACCI SERIES 60

5j ARITHMETIC OPERATIONS 61

DIPLAY SPECIFIC ATTRIBUTE FROM TABLE

5k 62

5l SIMPLE IF STATEMENT 63

5m IF ELSE STATEMENT 65

5n GREATEST OF 3 NUMBERS 66

5ii PROCEDURES 67

5iii FUNCTIONS 69

6a TRIGGERS ROW LEVEL TRIGGER 71

6b STATEMENT LEVEL TRIGGER 73

7 EXCEPTIONS 77

8 DATABASE DESIGN USING ER MODELING , 79

NORMALIZATIONAND IMPLEMENTATION FOR ANY
APPLICATION

9 MINI PROJECT(BANKING SYSTEM) 84

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Ex.No:1a DATA DEFINITION LANGUAGE

AIM:
To implement all Data Definition Language Commands.
DDL commands are:
(1) Create
(2) Describe
(3)Alter
(4)Add
(5)Modify
(6)Truncate
(7)Drop

(1) Create: Syntax:

This DDL command is used to create a table.create table <table name>(fieldname 1

datatype( size ),fieldname 2datatype(size)….fieldname n datatype(size));

(2) Describe:
This DDL command is used to describe the table structure (displays the fields and their types).
Syntax:
desc <table name>;

(3) Alter:
This DDL command is used to modify the already existing table, but we cannot change or
rename the table.

(i) Add:
This DDL command is used to add the column to an existing table.
Syntax:
alter table <table name> add (fieldname 1 datatype(size),fieldname2 datatype(size)…);

(ii) Modify:
It is used to modify column in already existing table.
Syntax:
alter table <table name> modify ( column datatype(size)…);

(4) Truncate:
This DDL command is used to delete data in the table.
Syntax:
truncate table <tablename>;

(5) Drop:
This DDL command is used to drop a table.
Syntax:
drop table <tablename>;

OUTPUT:-

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 1

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
*CREATE
SQL> create table emp1(ename varchar(7),eno number(5),addr varchar(15),pho numb er(7),dept number(5));
Table created.

*DESCRIBE
SQL> desc empl1;
Name Type
ENAME VARCHAR2(7)
ENO NUMBER(5)
ADDR VARCHAR2(15)
PHO NUMBER(7)
DEPT NUMBER(5)

*ALTER (add entry)

SQL> alter table emp1 add(salary number(8,2)); Table altered.

SQL> desc emp1;

Name Type
ENAME VARCHAR2(7)
ENO NUMBER(5)
ADDR VARCHAR2(15)
PHO NUMBER(7)
DEPT NUMBER(5)
SALARY NUMBER(8,2)

*ALTER (modify entry)

SQL> alter table emp1 modify(ename varchar(8)); Table altered.

SQL> desc emp1;

Name Type
ENAME VARCHAR2(8)
ENO NUMBER(5)
ADDR VARCHAR2(15)
PHO NUMBER(7)
DEPT NUMBER(5)
SALARY NUMBER(8,2)

*TRUNCATE

SQL> truncate table emp1; Table truncated.

*DROP

SQL> drop table emp1; Table dropped.

SQL> desc emp1;

ERROR:
ORA-04043: object emp1 does not exist

RESULT:
Thus the Data Definition Language Commands operation has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 2

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Ex.No:1b DATA MANIPULATION LANGUAGE

AIM:
To implement all DML commands (1)Insert (2) Select (3)Update

(4)Delete

(1) Insert:
This DML command is used to insert the details into the table.
Syntax:
insert into tablename values (‘&field1’, ‘&field2’,… );

(2) Select:
This command is used to show the details present in a table.
Syntax:
select * from <table name >;

(3) Update:
This command is used to change a value of field in a row.
Syntax:
update <tablename> set < field=’new value’> where
<field=’oldvalue’>;

(4) Delete:
This command is used to delete a row in table.
Syntax:
delete from <tablename> where< fieldname=’value’>;

OUTPUT:-

SQL> create table emp2(empno number(5),empname varchar(7),age number(3),job varchar(20) ,deptno

number(5),salary number(8,2));

Table created.

SQL> desc emp2;

Name Type
EMPNO NUMBER(5)
EMPNAME VARCHAR2(7)
AGE NUMBER(3)
JOB VARCHAR2(8)
DEPTNO NUMBER(5)
SALARY NUMBER(8,2)

*INSERT (single entry)

SQL> insert into emp2 values(100,'John',36,'Manager',455,17500); 1 row created.
SQL> select * from emp2;
EMPNO EMPNAME AGE JOB DEPTNO SALARY
100 John 36 Manager 455 17500

*INSERT (multiple entries)

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 3

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
SQL> insert into emp2 values(&empno,&empname,&age,&job,&deptno,&salary); Enter value for empno:
101
Enter value for empname: 'Smith' Enter value for age: 29
Enter value for job: 'Clerk' Enter value for deptno:
43 Enter value for salary: 1200 1 row created.

SQL> /
Enter value for empno: 102 Enter value for
empname: 'A' Enter value for age: 32
Enter value for job: 'Assistant Manager' Enter value for deptno: 43
Enter value for salary: 13500 1 row created.

SQL> /
Enter value for empno: 103 Enter value for
empname: 'B' Enter value for age: 37
Enter value for job: 'General Manager' Enter value for deptno: 355
Enter value for salary: 19000 1 row created.

SQL> /
Enter value for empno: 104 Enter value for
empname: 'E' Enter value for age: 28
Enter value for job: 'Clerk' Enter value for deptno:
35 Enter value for salary: 1500 1 row created.

SQL> /
Enter value for empno: 105 Enter value for
empname: 'F' Enter value for age: 38
Enter value for job: 'General Manager' Enter value for deptno: 40
Enter value for salary: 19000 1 row created.

SQL> select * from emp2;

EMPNO EMPNAME AGE JOB DEPTNO SALARY

100 John 36 Manager 455 17500

101 Smith 29 Clerk 43 1200
102 A 32 Assistant Manager 43 13500
103 B 37 General Manager 355 19000
104 E 28 Clerk 35 1500
105 F 38 General Manager 40 19000
6 rows selected.

*SELECT (specific attributes)

SQL> select empno,salary from emp2;
EMPNO SALARY

100 17500
101 1200
102 13500
103 19000
104 1500
105 19000
6 rows selected.

*SELECT (specific attributes on condition)

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 4
 20CSPL402 DATABASE MANAGEMENT SYSTEMS
SQL> select empno,salary from emp2 where salary>15000;
EMPNO SALARY

100 17500
103 19000
105 19000
6 rows selected.

*SELECT (all attributes) SQL> select * from

emp2;
EMPNO EMPNAME AGE JOB DEPTNO SALARY

100 John 36 Manager 455 17500

101 Smith 29 Clerk 43 1200
102 A 32 Assistant Manager 43 13500
103 B 37 General Manager 355 19000
104 E 28 Clerk 35 1500
105 F 38 General Manager 40 19000
6 rows selected.

*UPDATE (all entries)

SQL> update emp2 set salary=salary+500; 6 rows updated.

SQL> select * from emp2;

EMPNO EMPNAME AGE JOB DEPTNO SALARY

100 John 36 Manager 455 18000

101 Smith 29 Clerk 43 1700
102 A 32 Assistant Manager 43 14000
103 B 37 General Manager 355 19500
104 E 28 Clerk 35 2000
105 F 38 General Manager 40 19500
6 rows selected.

*UPDATE (specific entries)

SQL> update emp2 set salary=salary+500 where age>35; 3 rows updated.

SQL> select * from emp2;

EMPNO EMPNAME AGE JOB DEPTNO SALARY

100 John 36 Manager 455 18500

101 Smith 29 Clerk 43 1700
102 A 32 Assistant Manager 43 14000
103 B 37 General Manager 355 20000
104 E 28 Clerk 35 2000
105 F 38 General Manager 40 20000
6 rows selected.

*DELETE (specific entries)

SQL> delete from emp2 where deptno=43; 2 rows deleted.

SQL> select * from emp2;

EMPNO EMPNAME AGE JOB DEPTNO SALARY

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 5

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
100 John 36 Manager 455 18500
103 B 37 General Manager 355 20000
104 E 28 Clerk 35 2000
105 F 38 General Manager 40 20000
4 rows selected.

*DELETE (specific entries) SQL> delete from emp2;

4 rows deleted.

SQL> select * from emp2; no rows selected

RESULT:
Thus the DML Commands operation has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 6

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex: No: 1c TRANSACTION CONTROL LANGUAGE (TCL) COMMANDS

COMMIT command

COMMIT command is used to permanently save any transaction into the database. Following is

commit command's syntax,

COMMIT;

ROLLBACK command
This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to
a savepoint in an ongoing transaction.

Following is rollback command's syntax, ROLLBACK TO

savepoint_name;

SAVEPOINT command

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever
required.

Following is savepoint command's syntax, SAVEPOINT

savepoint_name;

Example:

INSERT INTO class VALUES(5, 'Rahul'); COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5'; SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

The resultant table will look like,

id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris
7 Bravo

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 7

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

ROLLBACK TO B;

SELECT * FROM class;

id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris

ROLLBACK TO A;

SELECT * FROM class;

id name
1 Abhi
2 Adam
4 Alex
5 Abhijit

RESULT:
Thus the TCL Commands operation has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 8

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex: No: 02 DATABASE QUERYING -SIMPLE QUERIES, NESTED QUERIES, SUB

QUERIES AND JOINS

Implementation of Simple Queries

AIM:

To performing insertion, deletion, modifying, altering, updating and viewing records based on
conditions.

ALGORITHM:

STEP 1: Start the DBMS

STEP 2: Connect to the database (DB)

STEP 3: Create the table with its essential attributes.

STEP 4: Insert the record into table based on some condition using WHERE CLAUSE

STEP 5: Update the existing records into the table based on some condition

STEP 6: Delete the records in to the table based on some condition

STEP 7: Use commit for permanently save the records

STEP 8: Stop the program

DRL-DATA RETRIEVAL IMPLEMENTING ON SELECT COMMANDS

Command:
SQL> CREATE TABLE EMP(
EMPNO NUMBER (4),
ENAME VARCHAR2 (10),
JOB VARCHAR2(20),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(8,2),
DEPTNO NUMBER(3)
);
Table created.

SQL> INSERT INTO EMP

VALUES(7369,'SMITH','CLERK',5001,'17-DEC-80','8000',200); 1 row
created.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 9

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

SQL> INSERT INTO EMP

VALUES(7499,'ALLEN','SALESMAN',5002,'20-FEB-80','3000',300); 1
row created.

SQL> INSERT INTO EMP

VALUES(7521,'WARD','SALESMAN',5003,'22-FEB-80','5000',500); 1
row created.

SQL> INSERT INTO EMP VALUES(7566,'JONES','MANAGER',5002,'02-APR-

85','75000',200); 1 row created.

SQL> INSERT INTO EMP VALUES(7566,'RAJA','OWNER',5000,'30-APR-

75',NULL,100); 1 row created.

SQL> INSERT INTO EMP VALUES(7566,'KUMAR','COE',5002,'12-JAN-

87','55000',300); 1 row created.

SQL> INSERT INTO EMP VALUES(7499,'RAM KUMAR','SR.SALESMAN',5003,'22-JAN-

87','12000.55',200); 1 row created.

SQL> INSERT INTO EMP VALUES(7521,'SAM

KUMAR','SR.SALESMAN',5003,'22-JAN-75','22000',300); 1 row created.

THE SELECT STATEMENT SYNTAX WITH ADDITIONAL CLAUSES:

Select [ Distinct / Unique ] ( *columnname [ As alias}, ….]
From tablename
[ where condition ]
[ Group BY group _by_expression ]

[Having group_condition ]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

-
7369 SMITH CLERK 5001 17-DEC-80 8000 200
7499 ALLEN SALESMAN 5002 20-FEB-80 3000 300
7521 WARD SALESMAN 5003 22-FEB-80 5000 500
7566 JONES MANAGER 5002 02-APR-85 75000 200
7566 RAJA OWNER 5000 30-APR-75 100
7566 KUMAR COE 5002 12-JAN-87 55000 300
7499 RAM KUMAR SR.SALESMAN 5003 22-JAN-87 12000.55 200
7521 SAM KUMAR SR.SALESMAN 5003 22-JAN-75 22000 300
8 rows selected.
BY USING SELECTED COLUNMS
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP;

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 10

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
7566 JONES MANAGER 75000
7566 RAJA OWNER
7566 KUMAR COE 55000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMARSR.SALESMAN 22000
8 rows
selected.
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE
SAL=5000;
EMPNO ENAME JOB SAL

7521 WARD SALESMAN 5000

BY USING BETWEEN / NOT / IN / NULL / LIKE

BETWEEN Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL IN(1000.5,75000);

EMPNO ENAME JOB SAL
7566 JONES MANAGER 75000
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL NOT IN (1000.5,75000);
EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
7566 KUMAR COE 55000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMARSR.SALESMAN 22000
6 rows
selected.
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL IS NULL;
EMPNO ENAME JOB SAL
7566 RAJA OWNER

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 11

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

7521 SAM KUMAR SR.SALESMAN 22000

7 rows selected.

LIKE Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL LIKE 55000;
EMPNO ENAME JOB SAL

7566 KUMAR COE 55000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'S%';

EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7521 SAM KUMARSR.SALESMAN 22000
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%R';
EMPNO ENAME JOB SAL

7566 KUMAR COE 55000

7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMARSR.SALESMAN 22000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%U%';

EMPNO ENAME JOB SAL
-
7566 KUMAR COE 55000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMAR SR.SALESMAN 22000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%A%';

EMPNO ENAME JOB SAL

7499 ALLEN SALESMAN 3000

7521 WARD SALESMAN 5000
7566 RAJA OWNER
7566 KUMAR COE 55000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMAR SR.SALESMAN 22000
6 rows selected.
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%LL%';
EMPNO ENAME JOB SAL

7499 ALLEN SALESMAN 3000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE

'%E%';
EMPNO ENAME JOB SAL
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%U%A%';

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 12

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
EMPNO ENAME JOB SAL
-
7566 KUMAR COE 55000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMAR SR.SALESMAN 22000
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'R ';//3_

EMPNO ENAME JOB SAL

7566 RAJA OWNER

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'R_J_';

EMPNO ENAME JOB SAL

7566 RAJAOWNER

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '_M%';

EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '_M';

no rows selected

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE

' R';
EMPNO ENAME JOB SAL

7566 KUMAR COE 55000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE
'K R'; _

EMPNO ENAME JOB SAL

7566 KUMAR COE 55000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME NOT LIKE
'R_J_';

EMPNO ENAME JOB SAL

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 13

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

7499 ALLEN SALESMAN 3000

7521 WARD SALESMAN 5000
7566 JONES MANAGER 75000
7566 KUMAR COE 55000
RAM
7499 KUMAR SR.SALESMAN 12000.55
SAM
7521 KUMAR SR.SALESMAN 22000

7 rows selected.

RELATIONAL OPERATOR

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL=55000;

EMPNO ENAME JOB SAL

7566 KUMAR COE 55000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL!=55000;
EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
7566 JONES MANAGER 75000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMAR SR.SALESMAN 22000
6 rows selected.
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL<>55000;
EMPNO ENAMEJOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
7566 JONES MANAGER 75000
7499 RAM KUMARSR.SALESMAN12000.55
7521 SAM KUMARSR.SALESMAN 22000
6 rows selected.
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL>55000;
EMPNO ENAME JOB SAL

7566 JONES MANAGER 75000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL<55000;

EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
7499 RAM KUMAR SR.SALESMAN 12000.55

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 14

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

7521 SAM KUMARSR.SALESMAN 22000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL<=55000;
EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
7566 KUMAR COE 55000
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMARSR.SALESMAN 22000
6 rows selected.

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL>=55000;

EMPNO ENAME JOB SAL

7566 JONES MANAGER 75000

7566 KUMAR COE 55000
AND / OR
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB='SR.SALESMAN'
AND SAL=22000;
EMPNO ENAME JOB SAL

7521 SAM KUMARSR.SALESMAN 22000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB='SR.SALESMAN'
OR SAL=22000;
EMPNO ENAME JOB SAL

7499 RAM KUMAR SR.SALESMAN 12000.55

7521 SAM KUMAR SR.SALESMAN 22000

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP

WHERE SAL=5000 AND (JOB='SR.SALESMAN' OR JOB='SALESMAN');
EMPNO ENAME JOB SAL

7521 WARD SALESMAN 5000

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 15

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP ORDER BYENAME;

EMPNO ENAME JOB SAL

7499 ALLEN SALESMAN 3000

7566 JONES MANAGER 75000
7566 KUMAR COE 55000
7566 RAJA OWNER
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMARSR.SALESMAN 22000
7369 SMITH CLERK 8000
7521 WARD SALESMAN 5000
8 rows
selected.

SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY ENAME DESC;

EMPNO ENAME JOB SAL

7521 WARD SALESMAN 5000

7369 SMITH CLERK 8000
7521 SAM KUMAR SR.SALESMAN 22000
7499 RAM KUMAR SR.SALESMAN 12000.55
7566 RAJA OWNER
7566 KUMAR COE 55000
7566 JONES MANAGER 75000
7499 ALLEN SALESMAN 3000
8 rows selected.
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY ENAME ASC;
EMPNO ENAME JOB SAL

7499 ALLEN SALESMAN 3000

7566 JONES MANAGER 75000
7566 KUMAR COE 55000
7566 RAJA OWNER
7499 RAM KUMAR SR.SALESMAN 12000.55
7521 SAM KUMARSR.SALESMAN 22000
7369 SMITH CLERK 8000
7521 WARD SALESMAN 5000
8 rows
selected.
TOP

// TOP clause is not in oracle instead of that ROWNUM

Syntax
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 16

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ROWNUM <4;
EMPNO ENAME JOB SAL

7369 SMITH CLERK 8000

7499 ALLEN SALESMAN 3000
7521 WARD SALESMAN 5000
SQL> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE
ENAME;
EMPNO ENAME JOB SAL

7499 ALLEN SALESMAN 3000

7369 SMITH CLERK 8000
7521 WARD SALESMAN 5000

Syntax:

Ex:DISTINCT

SQL> SELECT DISTINCT JOB FROM EMP; JOB

CLERK SALESMAN SR.SALESMAN MANAGER COE

OWNER
6 rows selected.
ROWNUM <4 ORDER BY
USING ALTER

This can be used to add or remove columns and to modify the precision of the
datatype.
a) ADDING COLUMN
Syntax:
alter table <table_name> add <col datatype>;
Ex:
SQL> DESC EMP;
Name Null? Type
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(8,2)
DEPTNO NUMBER(3)
SQL> alter table EMP add TAX number;

Table altered.
SQL> DESC EMP;
Name Null? Type

EMPNO NUMBER(4)

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 17

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(8,2)
DEPTNO NUMBER(3)
TAX NUMBER

b) REMOVING COLUMN

Syntax:
alter table <table_name> drop <col datatype>;

Ex:

SQL> alter table EMP drop column TAX;

Table altered.

SQL> DESC EMP;

Name Null? Type

EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(8,2)
DEPTNO NUMBER(3)
c) INCREASING OR DECREASING PRECISION OF A COLUMN

Syntax:

alter table <table_name> modify <col datatype>;

Ex:

SQL> alter table EMP modify DEPTNO number(5);

Table altered.

SQL> DESC EMP;

Name Null? Type
-
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)

HIREDATE DATE
SAL NUMBER(8,2)
DEPTNO NUMBER(5)

d) MAKING COLUMN UNUSED

Syntax:

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 18

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
alter table <table_name> set unused column <col>;
Ex:
SQL> alter table EMP set unused column DEPTNO;
Table altered.
SQL> DESC EMP;
Name Null? Type

EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(8,2)

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL

7369 SMITH CLERK 5001 17-DEC-80 8000

7499 ALLEN SALESMAN 5002 20-FEB-80 3000
7521 WARD SALESMAN 5003 22-FEB-80 5000
9 rows selected.
Method 1

GENERAL INSERT COMMAND:

SQL> INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SALARY)

VALUES(1111,'RAMU','SALESMAN',5063,'12-JAN-87','5643.55');
1 row created.

Method 2

WITHOUT SPECIFY THE COLUMNS DETAILS

SQL> INSERT INTO Emp
VALUES(1111,'RAMU','SALESMAN',5063,'12-
JAN-87','5643.55'); 1 row created.

Method 3

INSERTING DATA INTO SPECIFIEDCOLUMNS

SQL> INSERT INTO EMP(EMPNO,ENAME,JOB)
VALUES(1111,'RAMU','SALESMAN'); 1 rowcreated.

Method 4

BY CHANGE THE ORDER OF COLUMNS

SQL> INSERT INTO EMP(salary,EMPNO,ENAME,JOB)

VALUES(35000,1111,'RAMU','SALESMAN'); 1 row created.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SALARY

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 19

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
7369 SMITH CLERK 5001 17-DEC-80 8000
7499 ALLEN SALESMAN 5002 20-FEB-80 3000
7521 WARD SALESMAN 5003 22-FEB-80 5000
7566 JONES MANAGER 5002 02-APR-85 75000
7566 RAJA OWNER 5000 30-APR-75
7566 KUMAR COE 5002 12-JAN-87 55000
RAM SR.SALESM
7499 KUMAR AN 5003 22-JAN-87 12000.55
SAM SR.SALESM
7521 KUMAR AN 5003 22-JAN-75 22000
SAM SR.SALESM
7521 KUMAR AN 5003 22-JAN-75 22000
1111 RAMU SALESMAN 5063 12-JAN-87 5643.55
1111 RAMU SALESMAN 5063 12-JAN-87 5643.55
1111 RAMU SALESMAN
1111 RAMU SALESMAN 35000
13 rows
selected.

Method 5

INSERT WITH SELECT

Using this we can insert existing table data to another table in a single trip. But the
table structure should be same.

Syntax:

Insert into <table1> select * from <table2>;

Ex:

SQL> DESC EMP

Name Null?Type

EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
DAT
HIREDATE E
SALARY NUMBER(8,2)

SQL> create table

EMPLOYEE(EMP_NO,EMP_NAME,EMP_JOB,HR,HIREDATE,SA

LARY) as select * from EMP where 1 = 2;

Table created.

SQL> DESC EMPLOYEE

Name Null? Type

EMP_NO NUMBER(4)
EMP_NAME VARCHAR2(10)
EMP_JOB VARCHAR2(20)
HR NUMBER(4)
DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 20
 20CSPL402 DATABASE MANAGEMENT SYSTEMS
HIREDATE DATE
SALARY NUMBER(8,2)
SQL> SELECT * FROM
EMPLOYEE; no rows selected

SQL> insert into EMPLOYEE select * from

EMP; 13 rows created.
SQL> SELECT * FROM EMPLOYEE;
EMP_NAM
EMP_NO E EMP_JOB HR HIREDATE SALARY

7369 SMITH CLERK 5001 17-DEC-80 8000

7499 ALLEN SALESMAN 5002 20-FEB-80 3000
7521 WARD SALESMAN 5003 22-FEB-80 5000
7566 JONES MANAGER 5002 02-APR-85 75000
7566 RAJA OWNER 5000 30-APR-75
7566 KUMAR COE 5002 12-JAN-87 55000
7499 RAM KUMAR SR.SALESMAN 5003 22-JAN-87 12000.55
7521 SAM KUMARSR.SALESMAN 5003 22-JAN-75 22000
7521 SAM KUMARSR.SALESMAN 5003 22-JAN-75 22000
1111 RAMU SALESMAN 5063 12-JAN-87 5643.55
1111 RAMU SALESMAN 5063 12-JAN-87 5643.55
1111 RAMU SALESMAN
1111 RAMU SALESMAN 35000
13 rows selected.

GROUP BY

Using group by, we can create groups of related information. Columns used in
select must be used with group by; otherwise it was not a group by expression.
Ex:
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL DE

7369 SMITH CLERK 500117-DEC-80 8000 200

7499 ALLEN SALESMAN 500220-FEB-80 3000 300
7521 WARD SALESMAN 5003 22-FEB-80 5000 500
7499 RAM KUMARSR.SALESMAN 5003 22-JAN-87 12000.55200
7500
7566 JONES MANAGER 5002 02-APR-85 0 200

7521 SAM KUMARSR.SALESMAN 5003 22-JAN-75 22000 300

6 rows selected.

SQL> select job from EMP group by job;

JOB
CLERK
SALESMAN
SR.SALESMAN

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 21

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
MANAGER
SQL> select job,SUM(SAL) from EMP group by job;
JOB SUM(SAL)

CLERK 8000
SALESMAN 8000
SR.SALESMAN 34000.55
MANAGER 75000
HAVING
This will work as where clause which can be used only with group by because of
absence of where clause in group by.
SQL> select deptno,job,sum(sal) Total_Salary_Of_Each_Dept
from emp group by deptno,job having sum(sal) > 3000;

DEPTNO JOB TOTAL_SALARY_OF_EACH_DEPT

200 MANAGER 75000

200 SR.SALESMAN 12000.55
200 CLERK 8000
500 SALESMAN 5000
300 SR.SALESMAN 22000

SQL> select deptno,job,sum(sal) Total_Salary_of_Each_Dept from emp

group by deptno,job
having sum(sal) > 3000
order by job;
DEPTNO JOB TOTAL_SALARY_OF_EACH_DEPT
---------- ----------------- ------------------------
200 CLERK 8000
200 MANAGER 75000
500 SALESMAN 5000
200 SR.SALESMAN 12000.55
300 SR.SALESMAN 22000
USING DELETE

SQL> select * from EMP;

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

-
1001 RAM CLERK 5001 17-DEC-84 8000 301

1002 SAM MANAGER 5001 11-JAN-81 85000 301

1003 SAMU SALESMAN 5003 09-FEB-82 8000 302
1004 RAMU SR.SALESMAN 5002 22-JUN-85 45000 303
SQL> DELETE EMP WHERE
ENAME='SAM';
1 row
deleted.
SQL> select * from EMP;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

1001 RAM CLERK 5001 17-DEC-84 8000 301

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 22

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
1003 SAMU SALESMAN 5003 09-FEB-82 8000 302
1004 RAMU SR.SALESMAN 5002 22-JUN-85 45000 303
SQL> DELETE EMP WHERE ENAME
LIKE 'R ';
1 row
deleted.

SQL> select * from EMP;

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

1003 SAMU SALESMAN 5003 09-FEB-82 8000 302

1004 RAMU SR.SALESMAN 5002 22-JUN-85 45000 303

SQL> DELETE FROM EMP WHERE

ENAME='SAMU'; 1 row deleted.
TO DELETE ALL RECORDS
SQL> DELETE FROM
EMP; 1 row deleted.
DELETE DUPLICATE ROWS
SQL> SELECT * FROM myTBL;
NAME MARK
--------- ----------
RAM 101
RAM 101
SAM 102
SAM 102
RAMU
RAMU
SAMU 103
SAMU 103
SAMU 103
TAM
RAJA 555
KAJA 123
12 rows selected.
SQL> delete from myTBL t1
where t1.rowid > (select min(t2.rowID) from myTBL
t2 where t1.name = t2.name and t1.mark = t2.mark);
4 rows deleted.

SQL> SELECT * FROM

myTBL;
NAME MARK

RAM 101
SAM 102
RAMU
SAMU 103
TAM
RAJA 555
KAJA 123
8 rows

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 23

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
selected.

Using UPDATE

SQL> select * from EMP;

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
-
1001 RAM CLERK 5001 17-DEC-84 8000 301
1002 SAM MANAGER 5001 11-JAN-81 85000 301
SALESMA
1003 SAMU N 5003 09-FEB-82 8000 302
SQL> UPDATE EMP SET SAL = 55555,JOB =
'SR.MANAGER' WHERE ENAME LIKE 'R '; 1 row
updated.
SQL> select * from EMP;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
- - -

1001 RAM
SR.MANAGE
R 5001 17-DEC-84 55555 301

1002 SAM MANAGER 5001 11-JAN-81 85000 301

1003 SAMU SALESMAN 5003 09-FEB-82 8000 302
SQL> UPDATE EMP SET SAL = 55555,JOB = 'SR.MANAGER';
3 rows

updated.
SQL> select * from EMP;

EMPNO ENAME JOB DEPTNO

MGR HIREDATE SAL

SR.MANAGE
1001 RAM R 301
5001 17-DEC-84 55555
SR.MANAGE
1002 SAM R 301
5001 11-JAN-81 55555
1003 SAMU
SR.MANAGE
R 5003 09-FEB-82 55555 302

Implementation of Subqueries

A Subquery or Inner query or a Nested query is a query within another SQL query and
embedded within the WHERE clause.

Subqueries are most frequently used with the SELECT statement. The basic syntax is as
follows −

SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE column_name OPERATOR

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 24

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

Consider the CUSTOMERS table having the following records −

+ + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;

This would produce the following result.

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+ + + + + +

Subqueries with the INSERT Statement

INSERT INTO CUSTOMERS_BKP

SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement

UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

This would impact two rows and finally CUSTOMERS table would have the following
records.

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 25

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
+ + + + + +
| 1 | Ramesh | 35 | Ahmedabad | 125.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

Subqueries with the DELETE Statement

DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

This would impact two rows and finally the CUSTOMERS table would have the following
records.

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 26

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
JOINS

AIM:
To write and execute queries in SQL.

DEFINITION:
(1) INNER JOIN/ NATURAL JOIN/ JOIN:
It is a binary operation that allows us to combine certain selections and
a Cartesian product into one operation.
(2) OUTER JOIN:
It is an extension of join operation to deal with missing information.
(i) 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.
(ii) 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.
(iii) 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 them to the result of
the natural join.

OUTPUT:

Table created.

SQL> select * from categ1;

CATEID DESCRIPTION

101 Fiction
102 IT
103 Journal
104 General
105 Non-Fiction
Table created.

SQL> select * from catal1;

ID TITLE PUBID CATEID PRICE

1001 Science-Fiction 2001 101 3500

1002 Sandstime 2003 105 450
1003 Truth 2004 103 150
1004 DBMS 2006 102 350
1005 Autography 2008 103 1000
1006 Sportsplus 2010 104 200
6 rows selected.

Table created.

SQL> select * from publshr1;

PUBID NAME CITY COUNTRY

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 27

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

2001 Foreign San Francisco America

2002 Lakshmi Delhi India
2004 Brooks New York America
2005 Prakash Chennai India
2009 Shanthi Chennai India
2006 Ellie Harrison England
6 rows selected.

+) SQL> select title,name fromcatal1,publshr1 where catal1.pubid=publshr1.pubid;

TITLE NAME

Science-Fiction Foreign
Truth Brooks
Sportsplus Ellie

+) SQL> select catal1.*,categ1.* from catal1,categ1 where catal1.price>1000 and

catal1.cateid =categ1.cateid;

ID TITLE PUBID CATEID PRICE CATEID DESCRIPTION

1001 Science-Fiction 2001 101 3500 101 Fiction

+) SQL> select title,name from catal1 c,publshr1 p where c.pubid=p.pubid;

TITLE NAME

Science-Fiction Foreign
Truth Brooks
Sportsplus Ellie

*INNER/NATURAL JOIN

SQL>select title,name from catal1 inner join publshr1 on catal1.pubid=publshr1.pubid;

TITLE NAME

Science-Fiction Foreign
Truth Brooks
Sportsplus Ellie

*LEFT OUTER JOIN

SQL>select title,name from catal1 left outer join publshr1 on catal1.pubid=
publshr1.pubid;

TITLE NAME

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 28

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Science-Fiction Foreign
Truth Brooks
Sportsplus Ellie
DBMS
Sandstime
Autography
6 rows selected.

*RIGHT OUTER JOIN

SQL> select title,name from catal1 right outer join publshr1 on

catal1.pubid=publshr1.pubid;

TITLE NAME
Science-Fiction Foreign
Truth Brooks
Sportsplus Ellie
Shanthi
Prakash
Lakshmi
6 rows selected.

*FULL OUTER JOIN

SQL> select title,name from catal1 full outer join publshr1 on catal1.pubid=
pubshr1.pubid;

TITLE NAME
Science-Fiction Foreign
Lakshmi
Truth Brooks
Prakash
Shanthi
Sportsplus Ellie
DBMS
Sandstime
Autography
9 rows selected.

+) SQL> select catal1.*,categ1.* fromcatal1,categ1 where catal1.cateid=categ1.cateid;

ID TITLE PUBID CATEID PRICE CATEID DESCRIPTION

1001 Science-Fiction 2001 101 3500 101 Fiction

1002 Sandstime 2003 105 450 105 Non-Fiction
1003 Truth 2004 103 150 103 Journal
1004 DBMS 2006 102 350 102 IT
1005 Autography 2008 103 1000 103 Journal
1006 Sportsplus 2010 104 200 104 General
6 rows selected.
DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 29
 20CSPL402 DATABASE MANAGEMENT SYSTEMS

+) SQL> select title,price,cateid,name,country,city from catal1,publshr1 where

catal1.pubid=publshr1.pubid;

TITLE PRICE CATEID NAME COUNTRY CITY

Science-Fiction 3500 101 Foreign America San Francisco

Truth 150 103 Brooks America New York
Sportsplus 200 104 Ellie England Harrison

RESULT:
Thus the Database querying nested queries & joins operation has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 30

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:3 VIEWS, SYNONYMS, SEQUENCES, INDEXES

AIM:
To create views Synonyms, Sequence, Indexes, Save point for the
table and perform operations on it.
DEFINITION:
A view is an object that gives the user the logical view of data from the underlying
table. Any relation that is not part of the logical model but is made visible to the user
as a virtual relation is called a view. They are generally used to avoid duplication of
data.

Views are created for the following reasons,

· Data simplicity
· To provide data security
· Structural simplicity (because view contains only limited number of rows and
colmns)
i) VIEW:-is an imaginary table.
Syntax:
Create view <viewname> as select * from <tablename> where
fieldname=’value’;

ii) SEQUENCE:- Automatic generation of primary or unique key integervalue.

Syntax:
Create sequence <seq_name>increment by n start with n [maxvalue n]
[Minvalue n] [Cycle/no cycle] [Cache/no cache].

If you want to see the next current available value from the sequence apply the
following
Query: Select seq1.currval from dual;

If you want to see the next value from the sequence apply the following
Query: Select seq1.nextval from dual;
iii) SYNONYM: Which is used as an alias name (alternative name) for a table, view
or sequence.

Syntax: CREATE synonym<synonym_name>for<table_name>

iv) INDEXES: Improves the performance of the queries.

OUTPUT:

VIEWS

*PARENT TABLE

Table created.

SQL> select * from res33;

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 31
 20CSPL402 DATABASE MANAGEMENT SYSTEMS
COURSE SUBJECT MARKS ROLLNO

BE CS6202 80 1
BE PH6151 62 1
BE CS6202 91 2
ME EE6252 72 3
ME MC3124 55 3
BE CY6251 84 1
BE CS2126 72 5
BE MA6301 96 6
8 rows selected.

*CREATE VIEW
SQL> create view resv44 as select rollno,marks from res33;
View created.

SQL> select * from resv44;

ROLLNO MARKS

1 80
1 62
2 91
3 72
3 55
1 84
5 72
6 96
8 rows selected.

*SELECT
SQL> select marks from resv44;

MARKS

80
62
91
72
55
84
72
96
8 rows selected.

*UPDATE
SQL> update resv44 set marks=60 where rollno=2;
1 row updated.

SQL> select * from resv44;

ROLLNO MARKS

1 80

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 32

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
1 62
2 60
3 72
3 55
1 84
5 72
6 96
8 rows selected.

SQL> select * from res33;

COURSE SUBJECT MARKS ROLLNO

BE CS6202 80 1
BE PH6151 62 1
BE CS6202 60 2
ME EE6252 72 3
ME MC3124 55 3
BE CY6251 84 1
BE CS2126 72 5
BE MA6301 96 6
8 rows selected.

SQL> update resv44 set marks=marks+2;

8 rows updated.

SQL> select * from resv44;

ROLLNO MARKS

1 82
1 64
2 62
3 74
3 57
1 86
5 74
6 98
8 rows selected.

SQL> select * from res33;

COURSE SUBJECT MARKS ROLLNO

BE CS6202 82 1
BE PH6151 64 1
BE CS6202 62 2
ME EE6252 74 3
ME MC3124 57 3
BE CY6251 86 1

BE CS2126 74 5
BE MA6301 98 6
8 rows selected.

*INSERT

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 33

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
SQL> insert into resv44 values(7,92);
1 row created.

SQL> select * from resv44;

ROLLNO MARKS

1 80
1 62
2 60
3 72
3 55
1 84
5 72
6 96
7 92
9 rows selected.

SQL> select * from res33;

COURSE SUBJECT MARKS ROLLNO

BE CS6202 80 1
BE PH6151 62 1
BE CS6202 91 2
ME EE6252 72 3
ME MC3124 55 3
BE CY6251 84 1
BE CS2126 72 5
BE MA6301 96 6
92 7
8 rows selected.

*DELETE
SQL> delete from resv44 where rollno=3;
2 rows deleted.

SQL> select * from resv44;

ROLLNO MARKS

1 80
1 62
2 60
1 84
5 72
6 96
7 90

7 rows selected.

SQL> select * from res33;

COURSE SUBJECT MARKS ROLLNO

BE CS6202 80 1
BE PH6151 62 1

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 34

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
BE CS6202 91 2
BE CY6251 84 1
BE CS2126 72 5
BE MA6301 96 6
92 7

SQL> delete from resv44;

7 rows deleted.

SQL> select * from resv44;

no rows selected

SQL> select * fromres33;

no rows selected

*DROP
SQL> drop view resv44;
View dropped.

SQL> select * from resv44;

select * from resv44

ERROR at line 1:
ORA-00942: table or view does not exist

SEQUENCE

SQL> create table ssi(id number(2),name varchar(5));

Table created.

*CYCLE
*CREATE
SQL> create sequence seq34 minvalue 1 maxvalue 3 start with 1 increment by 1 cycle
cache 2;
Sequence created.

*INSERT
SQL> insert into ssivalues(seq34.nextval,'A');
1 row created.
SQL> insert into ssivalues(seq34.nextval,'B');
1 row created.
SQL> insert into ssivalues(seq34.nextval,'C');
1 row created.

SQL> select *from ssi;

ID NAME
1 A
2 B
3 C

SQL> insert into ssivalues(seq34.nextval,'a');

1 row created.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 35

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
SQL> insert into ssivalues(seq34.nextval,'b');
1 row created.
SQL> insert into ssivalues(seq34.nextval,'d');
1 row created.

SQL> select *from ssi;

ID NAME
1 A
2 B
3 C
1 a
2 b
3 d
6 rows selected.

*CURRENT VALUE
SQL> select seq34.currval from dual;
CURRVAL
3
*NEXT VALUE
SQL> select seq34.nextval from dual;
NEXTVAL
1

SQL> drop sequence seq34;

Sequence dropped.

SQL> select seq34.nextval from dual;

select seq34.nextval from dual
ERROR at line 1:
ORA-02289: sequence does not exist

*NO CYCLE
SQL> create table ssi2(id number(2), name varchar(5));
Table created.

*CREATE
SQL> create sequence seq34 minvalue 1 maxvalue 3 start with 1 increment by 1
nocycle cache 5;
Sequence created.

*INSERT
SQL> insert into ssi2 values(seq34.nextval,'r');
1 row created.
SQL> insert into ssi2 values(seq34.nextval,'e');
1 row created.
SQL> insert into ssi2 values(seq34.nextval,'a');
1 row created.

SQL> select *from ssi2;

ID NAME
1 r
2 e

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 36

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
3 a

SQL> insert into ssi2 values(seq34.nextval,'r');

ERROR at line 1:
ORA-08004: sequence SEQ34.NEXTVAL exceeds MAXVALUE and cannot be
instantiated
SQL> insert into ssi2 values(seq34.currval,'r');
1 row created.
SQL> insert into ssi2 values(seq34.currval,'h');
1 row created.

SQL> select *from ssi2;

ID NAME
1 r
2 e
3 a
3 r
3 h

SYNONYM

*CREATE
SQL> create synonym syn34 for ssi;
Synonym created.

*INSERT
SQL> insert into syn34 values(&id,'&name');
Enter value for id: 4
Enter value for name: e
1 row created.

SQL> /
Enter value for id: 5
Enter value for name: r
1 row created.

SQL> select *from ssi;

ID NAME
1 A
2 B
3 C
1 a
2 b
3 d
4 e
5 r
8 rows selected.

SQL> select *fromsyn34;

ID NAME
1 A

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 37

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
2 B
3 C
1 a
2 b
3 d
4 e
5 r
8 rows selected.

*UPDATE
SQL> update syn34 set id=id+2;
8 rows updated.

SQL> select *from syn34;

ID NAME
3 A
4 B
5 C
3 a
4 b
5 d
6 e
7 r
8 rows selected.
SQL> update syn34 set id=id+1 where id=7;
8 rows updated.

SQL> select *fromsyn34;

ID NAME
3 A
4 B
5 C
3 a
4 b
5 d
6 e
8 r
8 rows selected.

SQL> select *from ssi;

ID NAME
3 A
4 B
5 C
3 a
4 b
5 d
6 e
8 r
8 rows selected.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 38

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
*DELETE
SQL> delete from syn34 where id=5;
2 rows deleted.

SQL> select *from syn34;

ID NAME
3 A
4 B
3 a
4 b
6 e
8 r
6 rows selected.

SQL> select *from ssi;

ID NAME
3 A
4 B
3 a
4 b
6 e
8 r

6 rows selected.

SQL> delete from syn34;

6 rows deleted.

SQL> select *fromsyn34;

no rows selected

SQL> select *fromssi;

no rows selected

SQL> drop synonym syn34;

Synonym dropped.

INDEX

*CREATE
SQL> create index ind34 on ssi(id,name);
Index created.

*ALTER
SQL> alter index ind34 rename to ind56;
Index altered.

*DROP
SQL> drop index ind56;
Index dropped.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 39

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

RESULT:
Thus the views Synonyms, Sequence, Indexes operation has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 40

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex: No: 04 IMPLICIT AND EXPLICIT CURSORS

A cursor is a pointer to this context area. PL/SQL controls the context area through a
cursor. A cursor holds the rows (one or more) returned by a SQL statement.

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is

executed, when there is no explicit cursor for the statement. Programmers cannot control the
implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE
and DELETE) is issued, an implicit cursor is associated with this statement.

Attributes such as
%FOUND,
%ISOPEN,
%NOTFOUND
%ROWCOUNT.
Example
Select * from customers;

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+ + + + + +

The following program will update the table and increase the salary of each customer by
500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected −

DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;

6 customers selected

PL/SQL procedure successfully completed.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 41

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context
area. An explicit cursor should be defined in the declaration section of the PL/SQL Block.
It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −

Declaring the cursor for initializing the memory

CURSOR c_customers IS
SELECT id, name, address FROM customers;

Opening the cursor for allocating the memory

OPEN c_customers;

Fetching the cursor for retrieving the data

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the cursor to release the allocated memory

CLOSE c_customers;
EXAMPLE

DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota

4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 42

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

RESULT:
Thus the Implicit and Explicit Cursors has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 43

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5(i) BASIC PL/SQL PROGRAM

AIM:
To study and implement PL/SQL using simple programs.

STRUCTURE OF PL/SQL:
declare
<<declar active statements>>;
begin
<<executable statements>>;
exception
<<exception handling>>;
end;

SQL BLOCK STRUCTURE:

DECLARE:
Declarations of memory variables used later.

BEGIN:
SQL executable statements for manipulating table data.

EXCEPTIONS:
SQL and/or PL/SQL code to handle errors that may crop up
During the execution of the above code block.

END;

1. Conditional Control:
A sequence of statements can be executed based on some condition
using IF.

Syntax:
If<condition> then <Action>
Else <Action>
End if;

2. Iterative Control:
A sequence of statements can be executed any number of times using
loop constructs.

a) Simple loop

Syntax:
Loop
Statements;
End loop;

b) While loop

Syntax:
DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 44
 20CSPL402 DATABASE MANAGEMENT SYSTEMS
While<condition>
Loop
Statements;
End loop;

c) For loop

Syntax:
For counter in[reverse] lower bound .. upper bound
Loop
statements
End loop;

RESULT:
Thus the BASIC PL/SQL PROGRAM has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 45

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5a SUM OF 2 NUMBERS

AIM

To study and implement the PL/SQL for adding two numbers and displaying the
sum.

ALGORITHM

*Declare a,b and c.

*Get input of a and b.
*Do arithmetic addition operation between a and b.
*And store the value in c.
*Display the sum as output.

PROGRAM

declare
a number(3);
b number(3);
c number(3);
begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line('Sum='||c);
end;

OUTPUT

Enter value for a: 34

Enter value for b: 52
Sum=86
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for adding two numbers has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 46

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5b SERIES OF NUMBERS (FOR LOOP)

AIM

To study and implement the PL/SQL to print a series of ‘n’ numbers using for
loop.

ALGORITHM

*Declare i and n.
*Get input of n.
*Initialize the value of i=1.
*Inside a for loop of i in n, print the value of i.
*Display output.

PROGRAM

declare
i number(2):=1;
n number(3):=&n;
begin
for i in 1..n
loop
dbms_output.put_line(i);
end loop;
end;

OUTPUT

Enter value for n: 6

1
2
3
4
5
6
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL to print a series of ‘n’ numbers has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 47

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5c SERIES OF NUMBERS (WHILE LOOP)

AIM

To study and implement the PL/SQL to print a series of ‘n’ numbers using while
loop.

ALGORITHM

*Declare i and n.
*Get input of n.
*Initialize the value of i=1.
*Inside a while loop with condition i<=n, print the value of i.
*Increment the value of i.
*Display output.

PROGRAM

declare
i number(2):=1;
n number(3):=&n;
begin
while(i<=n)
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

OUTPUT

Enter value for n: 15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
PL/SQL procedure successfully completed.
RESULT:Thus the PL/SQL to print a series of ‘n’ numbers using whileloop has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 48

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5d DEBIT FROM ACCOUNT

AIM

To study and implement the PL/SQL for debiting an amount from an entry in a
table by this program

ALGORITHM

*Declare accno,balance,debit,minbal as 500.

*Get input of accno and debit from user .
*Debit the amount from entry in the table account33 using select command.
*Using if condition, update bal attribute in table only if it is greater than minbal.
*Else print that the account is too low to debit.
*End the program.

PROGRAM

declare
accno number(5);
balance number(5);
debit number(5);
minbal number(5);
begin
minbal:=500;
accno:=&accno;
debit:=&debit;
select bal into balance from account33 whereaccno=Ano;
balance:=balance-debit;
if(balance>minbal)then
update account33 set bal=balance where accno=Ano;
else
dbms_output.put_line('Your account is too low to debit');
end if;
end;

OUTPUT

SQL> create table account33(Ano number(2),name varchar(5),bal number(7));

Table created.

SQL> select * from account33;

ANO NAME BAL

1 A 5000
2 B 1000
3 C 4000
4 D 5000

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 49

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Enter value for accno: 1

Enter value for debit: 2000
PL/SQL procedure successfully completed.

SQL> select * from account33;

ANO NAME BAL

1 A 3000
2 B 1000
3 C 4000
4 D 5000

Enter value for accno: 2

Enter value for debit: 2000
Your account is too low to debit
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for debiting an amount from an entry in atable has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 50

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5e SUM OF A SERIES

AIM

To study and implement the PL/SQL to determine the sum of a series of ‘n’
numbers.

ALGORITHM

*Declare n,i and x.

*Get input of n.
*Initialize the values of i=1 and x=0.
*Inside a simple loop, calculate the sum of numbers upto ‘n’.
*Display the sum as output.

PROGRAM

declare
n number(3):=&n;
i number(3):=1;
x number(3):=0;
begin
loop
x:=x+i;
exit when i=n;
i:=i+1;
end loop;
dbms_output.put_line('Sum = '||x);
end;

OUTPUT

Enter value for n: 6

Sum = 21
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL to determine the sum of a series of ‘n’numbers has been executed

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 51

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5f FACTORIAL OF A NUMBER

AIM

To study and implement the PL/SQL to determine the factorial of the given
number.

ALGORITHM

*Declare n,i and x.

*Get input of n.
*Initialize the values of i=1 and x=1.
*Inside a while loop, calculate the product of numbers upto ‘n’.
*Display the factorial as output.

PROGRAM

declare
n number(3):=&n;
i number(3):=1;
x number(3):=1;
begin
while(i<=n)
loop
x:=x*i;
i:=i+1;
end loop;
dbms_output.put_line('Factorial = '||x);
end;

OUTPUT
Enter value for n: 5
Factorial = 120
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL to determine the factorial of the given number has been executed

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 52

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5g REVERSING A STRING

AIM

To study and implement PL/SQL for reversing a string using the for loop.

ALGORITHM

*Get the input string.

*Find the length of the string.
*Extract the characters one by one from the end of the string.
*Concatenate the extracted characters.
*Display the concatenated reversed string.
*Stop the program.

PROGRAM

declare
b varchar2(10):='&b';
c varchar2(10);
l number(2);
i number(2);
g number(2);
d varchar2(10);
begin
l:=length(b);
g:=l;
for i in 1..l
loop
c:=substr(b,g,1);
g:=g-1;
d:=d||c;
end loop;
dbms_output.put_line('Reversed string is:');
dbms_output.put_line(d);
end;

OUTPUT

Enter value for b: hello

Reversed string is
olleh
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for reversing a string using the for loop has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 53

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5h FIBONACCI SERIES

AIM:

To study and implement PL/SQL for Fibonacci series.

ALGORITHM:

*Initialise a,b,c,n where a=-1,b=1,c=0.

*Use ’n’ for ‘for’loop
*Add a,b and store it in c.
*Exchange b to a and c to b.
*Get output from c.

PROGRAM :

declare
a number(3);
b number(3);
c number(3);
n number(3);
i number(3);
begin
n:=&n;
c:=0;
b:=1;
a:=-1;
for i in 1..n
loop
c:=a+b;
a:=b;
b:=c;
dbms_output.put_line(c);
end loop;
end;

OUTPUT

Enter value for n: 6

0
1
1
2
3
5
PL/SQL procedure successfully completed.

RESULT:Thus the PL/SQL for Fibonacci series has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 54

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5i ARITHMETIC OPERATIONS

AIM

To study and implement the PL/SQL for arithmetic operations.

ALGORITHM

*Declare a,b,c,d,e,f,g.
*Get input of a and b.
*Do arithmetic operations between a and b.
*And store it in c,d,e,f,g.
*Display output.

PROGRAM

declare
a number(10);
b number(10);
c number(10);
d number(10);
e number(10);
f number(10,2);
g number(10);
begin
a:=&a;
b:=&b;
c:=a+b;
d:=a-b;
e:=a*b;
f:=a/b;
g:=a mod b;
dbms_output.put_line('Addition of a and b is '||c);
dbms_output.put_line('Subtraction of a and b is '||d);
dbms_output.put_line('Multiplication of a and b is '||e);
dbms_output.put_line('Division of a and b is '||f);
dbms_output.put_line('Modulus of a and b is '||g);
end;

OUTPUT

Enter value for a: 9

Enter value for b: 2
Addition of a and b is 11
Subtraction of a and b is 7
Multiplication of a and b is 18
Division of a and b is 4.5
Modulus of a and b is 1
PL/SQL procedure successfully completed.
RESULT:Thus the PL/SQL for arithmetic operations has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 55

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5j DIPLAY ENTIRE ROW FROM TABLE

AIM

To study and implement the PL/SQL for displaying an entire row from a table.

ALGORITHM

*Declare a variable cus of rowtype of table customers33.

*Copy the values of an entry from the table using select command.
*Using cus variable, access the attributes name, id and grade.
*Display output of the name and grade.

PROGRAM

declare
cus customers33%rowtype;
begin
select * into cus from customers33 where id=5;
dbms_output.put_line('ID='||cus.id);
dbms_output.put_line('Name='||cus.name);
dbms_output.put_line('Grade='||cus.grade);
end;

OUTPUT

SQL> create table customers33(id number(3),name varchar(5),grade char(3));

Table created.

SQL> select * from customers33;

ID NAME GRADE

1 AA S
3 BB E
5 CC B

ID=5
Name=CC
Grade=B
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for displaying an entire row from a table has been executed
successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 56

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5k DIPLAY SPECIFIC ATTRIBUTE FROM TABLE

AIM

To study and implement the PL/SQL for displaying a particular attribute from a
table.

ALGORITHM

*Declare a,b,c,d,e,f,g.
*Get input of a and b.
*Do arithmetic operations between a and b.
*And store it in c,d,e,f,g.
*Display output.

PROGRAM

declare
vsal number(6); rid
number(6):=11;
begin
select salary into vsal from emp33 where id=rid;
dbms_output.put_line(vsal);
dbms_output.put_line('The employee '||rid||’ has salary ‘||vsal);
end;

OUTPUT

SQL> create table emp33(id number(3),name varchar(5),salary number(5));

Table created.

SQL> select * from emp33;

ID NAME SALARY

9 AA 5000
10 BB 10000
11 CC 3000

3000
The employee 11 has salary 3000.
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for displaying a particular attribute from atable has been executed
successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 57

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5l SIMPLE IF STATEMENT

AIM

To study and implement the PL/SQL for implementing the simple if statement in
this program.

ALGORITHM

*Declare i,n and j.

*Get input of n.
*Initialize values of i=1 and j=2.
*Using for loop of i in n, give condition to print only the even numbers.
*Display the output.

PROGRAM

declare
i number(2):=1;
n number(3):=&n;
j number(3):=2;
begin
dbms_output.put_line('Printing even nos.');
for i in 1..n
loop
if((i mod j)=0)then
dbms_output.put_line(i);
end if;
end loop;
end;

OUTPUT

Enter value for n: 10

Printing even nos.
2
4
6
8
10
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for implementing the simple if statement has been executed
successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 58

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5m IF ELSE STATEMENT

AIM

To study and implement the PL/SQL for implementing the if else statement in
this program.

ALGORITHM

*Declare a variable c for choice.

*Get input of c.
*If its value is 1, print RED.
*Else print the colour BLUE.

PROGRAM

declare
c number(3);
begin
dbms_output.put_line('Colours');
dbms_output.put_line('1.Red');
dbms_output.put_line('2.Blue');
dbms_output.put_line('Your choice:');
c:=&c;
if(c=1)then
dbms_output.put_line('RED');
else
dbms_output.put_line('BLUE');
end if;
end;

OUTPUT
Enter value for c: 2
Colours
1.Red
2.Blue
Your choice:
BLUE
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL for implementing the if else statement has been executed
successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 59

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5n GREATEST OF 3 NUMBERS

AIM

To study and implement the PL/SQL to find the greatest of three numbers using if
else statement.

ALGORITHM

*Declare a,b,c,.
*Get input of a,b and c.
*Using if else condition compare the values of a,b and c.
*Display the respective output.

PROGRAM

declare
a number(3);
b number(3);
c number(3);
begin
a:=&a;
b:=&b;
c:=&c;
if(a>b)and(a>c)then
dbms_output.put_line('a is the greatest');
elsif(b>c)then
dbms_output.put_line('b is the greatest');
else
dbms_output.put_line('c is the greatest');
end if;
end;

OUTPUT

Enter value for a: 8

Enter value for b: 5
Enter value for c: 11
c is the greatest
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL to find the greatest of three numbers using ifelse statement has been executed
successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 60

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Ex.No:5(ii) PROCEDURES

AIM

To implement procedures using PL/SQL.

PROCEDURE

A procedure is a stored sub-program that performs a specific function.

Syntax:
Create or replace procedure <procedure name> (parameter-1,parameter-2)
[local declarations]
Begin
<<executable statements>>
Exception
<<exception handling>>
End;

PROGRAM

set serveroutput on;

create or replace procedure proc(no number) is
num c.sno%type;
m1 c.mark1%type;
m2 c.mark2%type;
p number(10);
begin
select sno,mark1,mark2 into num,m1,m2 from c where sno=no;
p:=(m1+m2)/2;
update c set percent=p where sno=no;
dbms_output.put_line(‘stud no:’||num);
dbms_output.put_line(‘percentage:’||p);
dbms_output.put_line(‘Table updated’);
end proc;

OUTPUT

SQL> create table c(sno number(5),mark1 number(10),mark2 number(10),percent

number(10));
Table created.

SQL> insert into cvalues('9','52','52','');

1 row created.
SQL> insert into c values('27','95','95',’');
1 row created.
SQL> insert into cvalues('8',’65',’21’,’’);
1 row created.

SQL> select * from c;

SNO MARK1 MARK2 PERCENT

9 52 52
27 95 95
8 65 21

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 61

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

/
Procedure created.

SQL>exec proc(27);
stud no:27
percentage:95
Table updated.

PL/SQL procedure successfully completed.

SQL>select * from c;

SNO MARK1 MARK2 PERCENT

9 52 52
27 95 95 95
8 65 21

RESULT:
Thus the PL/SQL has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 62

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:5(iii) FUNCTIONS

AIM

To implement functions using PL/SQL.

FUNCTION
Syntax:
It is stored sub-program that computes a value.

Create or replace function <function-name>(parameter1,parameter2) is

returntype is
[local declarations]
Begin
<<executable statements>>
Exception
<<exception handling>>
End;

PROGRAM

SQL>set serveroutput on;

SQL> create or replace function fun1(no number) return number
is num c.sno%type;
m1 c.mark1%type;
m2 c.mark2%type;
p number;
begin
select sno,mark1,mark2 into num,m1,m2 from c where sno=no;
p:=(m1+m2)/2;
return p;
end fun1;

OUTPUT

SQL> create table c(sno number(5),mark1 number(10),mark2 number(10));

Table created.

SQL> insert into c values('9','52','52');

1 row created.

SQL> insert into c values('27','95','95');

1 row created.

SQL> insert into cvalues('8',’65',’21’);

1 row created.

SQL> select * from c;

SNO MARK1 MARK2

9 52 52
27 95 95
DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 63
 20CSPL402 DATABASE MANAGEMENT SYSTEMS
8 65 21

/
Function created.

SQL>select fun1(8) from c where sno=8;

FUN1(8)

65

RESULT:
Thus the PL/SQL has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 64

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No: 6 TRIGGERS

AIM

To write a trigger for database model.

TRIGGER

It is a statement that system executes automatically as a slide effect of modification

to the database. A database trigger is a stored procedure that is associated with a table.

Syntax:

Create or replace trigger[triggername]

[before/after][insert/update/delete]on <table name>
[for each statement or row] [when <condition>]

Types:

Before
After
For each row
For each statement

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 65

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Ex.No:6a ROW LEVEL TRIGGER

AIM

To implement a row level trigger.

ALGORITHM

*Start the program.

*Create a trigger.
*Check each row and print a row updated or not.
*Stop the program.

PROGRAM

create trigger trg1 after update on student for each row

begin
dbms_output.put_line(‘1 row updated’);
end;

OUTPUT

SQL> select * from student;

REGNO NAME DEPT ADDRESS

102 kiran it paris

103 hari ece trichy
104 kish it pune

/
Trigger Created

SQL> update student set address=’chennai’ where dept=’it’;

1 row updated
1 row updated
2 rows updated

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 66

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Ex.No:6b STATEMENT LEVEL TRIGGER

AIM

To create a Statement level trigger.

ALGORITHM

* Start a program.
* Create a trigger that executes after updation on table emp.
*Check the table on the whole and print 1 row updated.
*Stop the program.

PROGRAM

create trigger trg2 after update on emp

begin
dbms_output.put_line(‘1 row updated’);
end;

OUTPUT

SQL> select * from emp;

EMPNO SAL
102 4000
103 5000
104 4000

/
Trigger Created

SQL> update emp set sal=’10000’ where sal=4000;

1 row updated

2 rows updated

RESULT:
Thus the row level trigger & Statement level trigger has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 67

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:7 EXCEPTIONS

AIM:
To study and implement exceptions for No data found.

ALGORITHM:

Declare temp.
Select gid and save it in temp from geeks
Where gname='GeeksforGeeks'.
When no data found throw an error.
Display output.

PROGRAM:

DECLARE
temp varchar(20);

BEGIN
SELECT g_id into temp from geeks where g_name='GeeksforGeeks';

exception
WHEN no_data_found THEN
dbms_output.put_line('ERROR');
dbms_output.put_line('there is no name as');
dbms_output.put_line('GeeksforGeeks in geeks table');
end;

OUTPUT:
ERROR
there is no name as GeeksforGeeks in the geeks table.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 68

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

AIM:
To study and implement exceptions for Too many rows.
ALGORITHM:
Declare temp.
Raise an exception as select.
select gname and save it to temp.
Throw an exception when too many rows.
Display output.

PROGRAM:

DECLARE
temp varchar(20);

BEGIN

-- raises an exception as SELECT

-- into trying to return too many rows
SELECT g_name into temp from geeks;
dbms_output.put_line(temp);

EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('error trying to SELECT too many rows');

end;
OUTPUT:
Error trying to select too many rows.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 69

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

AIM:
To study and implement exceptions for value error.
ALGORITHM:
Declare temp.
select gname=suraj from geeks and store in temp.
display the temp
throw exception "value error.
Display the output.

PROGRAM:

DECLARE
temp number;

BEGIN
SELECT g_name into temp from geeks where g_name='Suraj';
dbms_output.put_line('the g_name is '||temp);

EXCEPTION
WHEN value_error THEN
dbms_output.put_line('Error');
dbms_output.put_line('Change data type of temp to varchar(20)');

END;
OUTPUT:
Error.Change data type of temp to varchar.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 70

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

AIM:
To study and implement exceptions for Zero divide.
ALGORITHM:
declare a=10,b=0.
divide a/b.
throw exception as divided by zero.
display exception output.

PROGRAM:

DECLARE
a int:=10;
b int:=0;
answer int;

BEGIN
answer:=a/b;
dbms_output.put_line('the result after division is'||answer);

exception
WHEN zero_divide THEN
dbms_output.put_line('dividing by zero please check the values again');
dbms_output.put_line('the value of a is '||a);
dbms_output.put_line('the value of b is '||b);
END;
OUTPUT:
Dividing by zero please check the values again
the value of a is 10
the value of b is 0

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 71

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

AIM:
To study and implement exceptions for unnamed system exceptions.
ALGORITHM:
Declare exp exception
Declare i=10.
for i = 1 to n print i*i
if i*i =36
then raise exception
else print Welcome to GeeksforGeeks.

PROGRAM:
DECLARE
exp exception;
pragma exception_init (exp, -20015);
n int:=10;

BEGIN
FOR i IN 1..n LOOP
dbms_output.put_line(i*i);
IF i*i=36 THEN
RAISE exp;
END IF;
END LOOP;

EXCEPTION
WHEN exp THEN
dbms_output.put_line('Welcome to GeeksforGeeks');

END;
OUTPUT:
1
4
9
16
25
36
Welcome to GeeksforGeeks

RESULT:
Thus the exceptions for Zero divide & exceptions for unnamed system exceptions has been executed
successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 72

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

Ex.No:8 Database Design using ER modeling, normalization and Implementation for any
application

The waterfall model can be applied to database design. The steps can be summarized as
follows:

Requirements specification -> Analysis -> Conceptual design -> Implementation

Design-> Physical Schema Design and Optimisation

Figure 1: A basic example of a requirements document

The requirements document can then be analyzed and turned into a basic data set (as shown
in Figure 2) which can be converted into a conceptual model. The result of the conceptual
design phase is a conceptual data model (Figure 3), which provides little information about
how the database system will eventually be implemented. The conceptual data model is
merely a high-level overview of the database system.

Figure 2: A Database Data Set is the Result of analyzing the Information from the
Requirements Phase. The Primary Keys are Underlined.

Figure 3: A Normalized Entity-Relationship model (ERD) in Crow’s Foot Notation is an

Example of a Conceptual Data Model and provides no information of how the database
system will eventually be implemented

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 73

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

In the implementation design phase, the conceptual data model is translated into a ‘logical’
representation of the database system. The logical data model conveys the “logical
functioning and structure” of the database and describes ‘how the data is stored’ (e.g., what
tables are used, what constraints are applied) but is not specific to any DBMS. The logical
database model is a lower-level conceptual model, which must be translated to a physical
design.

1. Create the Database Tables

The tables come directly from the information contained in the Data Dictionary. The
following blocks of code each represent a row in the data dictionary and are executed one
after another. The blocks of “create table” code contain the details of all the data items
(COMPANY, SUPPLIER, PURCHASES, EMPLOYEE, etc), their attributes (names, ages,

costs, numbers, and other details), the Relationships between the data items, the Keys and
Data Integrity Rules. All of this information is already detailed in the Data Dictionary, but
now we are converting it and implementing it in a physical database system.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 74

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 75

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

2. Populate the tables

Use SQL statements to populate each table with specific data (such as employee names,
ages, wages etc).

3. Query the database.

Write SQL statements to obtain information and knowledge about the company, e.g. how
many employees are there, total profit etc.

RESULT:
Thus the Database Design using ER modeling, normalization has been executed successfully.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 76

 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Ex.No.9 Mini Project(Banking System)

AIM:
To Connect Oracle Database from Visual Basic 6.0 for Banking System

PROCEDURE:

1. Start the process.

2. Create the table using oracle,SQL and insert some tuples in it.
3. Commit the table and exit.
4. For ODBC connectivity, select control panel -> administrative tools->ODBC data
sources, select drivers menu, select Microsoft ODBC driver, click ok.
5. In VB 6.0, choose project menu, under that references.
6. Select Microsoft DAO 3.51 object library and Microsoft DAO 3.6 object library
7. Create forms for the project for the appropriate requirements specified.
8. Execute and terminate the project.

CODING:

GENERAL:
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

INSERT:
Private Sub Command1_Click()
Set rs = New ADODB.Recordset
rs.Open "select * from emp890", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs(0) = Val(Text1.Text)
rs(1) = Text2.Text
rs.Update
rs.Close
MsgBox "record created"
End Sub

DELETE:
Private Sub Command2_Click()
Set rs = New ADODB.Recordset
rs.Open "select * from emp890 where eno=" & Val(Text1.Text), conn, adOpenDynamic,
adLockOptimistic
rs.Delete
Text1.Text = ""
MsgBox "deleted"
rs.Close
End Sub

VIEW:
Private Sub Command3_Click()
Set rs = New ADODB.Recordset

rs.Open "select * from emp890", conn, adOpenDynamic, adLockOptimistic

rs.MoveFirst
With rs
Text1.Text = rs(0)
DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 77
 20CSPL402 DATABASE MANAGEMENT SYSTEMS
Text2.Text = rs(1)
End With
rs.Close
End Sub

CLEAR:
Private Sub Command3_Click()
Text1.Text = ""
Text2.Text = ""
End Sub

EXIT:
Private Sub Command4_Click()
unload me
End Sub

BACK END:

FORM DESIGN:

INSERTING A TUPLE:

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 78

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 79

 20CSPL402 DATABASE MANAGEMENT SYSTEMS

RESULT:
Thus the Oracle Database from Visual Basic 6.0 for Banking System has been
executed successfully.

87

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE Page 80