NRI INSTITUTE OF INFORMATION SCIENCE

&TECHNOLOGY

DEPARTMENTOFINFORMATION
TECHNOLOGY

LABMANUAL

DBMS LAB

INFORMATIONTECHNOLOGY(IT)
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:1
1
Aim:Queries for Creating, Dropping, and Altering Tables and insert row into
a table(use constraints while creating tables) examples using Select
Command.

Procedure:

1. Creation of emp& dept table in Sql:

CREATE TABLE dept (
deptno NUMBER(2,0) PRIMARY KEY,
dname VARCHAR2(14) NOT NULL,
loc VARCHAR2(13) NOT NULL
);
Tablecreated.

SQL>create table emp(

empno number(4,0),
ename varchar2(10)NOT NULL,
job varchar2(9) NOT NULL,
mgr number(4,0),
hiredatedate,
salnumber(7,2)NOTNULL,
commnumber(7,2),
deptnonumber(2,0),
constraint pk_emp primarykey(empno),
constraint fk_deptno foreignkey(deptno)references dept(deptno)
);
Tablecreated.

2. ViewStructure/schemaofemp&depttableinsql:

SQL> select *from emp;

norowsselected
SQL>select*from dept;
Norowsselected

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>desc emp;
Name Null? Type 2

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>descdept;
Name Null? Type

DEPTNO NOT NULL NUMBER(2)

DNAME
VARCHAR2(14)
LOC VARCHAR2(13)

2. Insert the values in emp&dept table in sql:

There are several ways to insert the values in the existing table
Query to insert single record in the existing table:
SQL>insert into dept values(20,’admin’,’hyd’);
1 rowcreated.
Query to insert multiple records in the existing table:
SQL>insertintodeptvalues(&deptno,'&dname','&loc');
Enter value for deptno: 10
Enter value for dname: sales
Entervalueforloc:vijayawada
old1:insertintodeptvalues(&deptno,'&dname','&loc')
new1: insert into dept values(10,'sales','vijayawada')
1 rowcreated.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>/
3
Entervaluefordeptno:20
Entervaluefordname:admin Enter
value for loc: hyd
old1:insertintodeptvalues(&deptno,'&dname','&loc') 1
row created.
SQL>/
Entervaluefordeptno: 30
Entervaluefordname:marketing
Enter value for loc: vzg
old1:insertintodeptvalues(&deptno,'&dname','&loc')
new1: insert into dept values(30,'marketing','vzg')
1 rowcreated.

[Link]:thiscommand isusedtoprinttherecordfromtheexistingtable. View all

records in dept table:
SQL>select*from dept;
DEPTNO DNAME LOC

10sales vijayawada
20admin hyd
30marketing vzg
Viewrecordsbasingongivencriteriaonspecificcolumn.

[Link] fromexisting table.

SQL>select dname from dept;
DNAME

Sales
Admin
Marketing

DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

[Link](s)fromexistingtablebasedongivencondition.
4
SQL>select*fromdeptwheredname='sales';
DEPTNO DNAME LOC

10sales vijayawada

Types of SQL Commands:

DDL:DDLCommands( DataDefinition Language)

[Link] [Link] [Link] [Link] [Link]

DMLCommands(DataManipulationLanguage)
[Link] 2. INSERT [Link] [Link]

TCL(TransactionControlLanguage)
[Link] [Link] [Link]

DCLCommands(DataControlLanguage)
[Link] [Link]

[Link]:
CREATETABLE:This is used to create a new relation and the corresponding

Syntax:CREATE TABLE relation_name(field_1data_type(Size),field_2data_type(Size),...);

Example:
SQL>CREATETABLEStudent (id number, namevarchar2(10));
RESULT: Table created.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

2. DESC:Itisusedtodescribeaschemaaswellastoretrieverowsfromtableindescending order.
SYNTAX:DESC
5

EX:SQL>DESC EMP1;
NAME NULL? TYPE

EMPNO NOT NULL NUMBER(10)

ENAME VARCHAR2(15)
JOB CHAR(10)
DEPTNAME VARCHAR2(10)
DEPTNO NUMBER(9)
HIREDATE DATE
SALARY NUMBER(8)
EXP NUMBER(5)

3. ALTER:This is used for add, remove or modify the structure of the existing table
(a) ALTERTABLE...ADD...:Thisisusedtoaddsomeextrafieldsintoexistingrelation.

Syntax :ALTER TABLE relation_name ADD(newfield_1

data_type(size),newfield_2 data_type(size),..);

Example:SQL>ALTER TABLE emp1 ADD(AddressCHAR(10)); TABLE

ALTERED.

(b) ALTERTABLE...MODIFY...:This is used to change the width as well as data type of fields of
existing relations.
Syntax:ALTER TABLE relation_nameMODIFY(field_1newdata_type(Size),field_2 newdata_type(Size),...
, field_newdata_type(Size));

Example:
SQL>ALTERTABLEemp1MODIFY(enameVARCHAR2(20),salaryNUMBER(5));
TABLE ALTERED.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>DESC EMP1;
6
NAME NULL? TYPE

EMPNO NOTNULL NUMBER(10)

ENAME VARCHAR2(20)
JOB CHAR(10)
DEPTNAME VARCHAR2(10)
DEPTNO NUMBER(9)
HIREDATE DATE
SALARY NUMBER(5)
EXP NUMBER(5)
ADDRESS CHAR(10)

4. DROP TABLE:This [Link] table.

Syntax:DROP TABLE tablename;
Example:
SQL>DROPTABLEEMP1;
Tabledropped;
DROP:thiscommandisusedtoremovethedatefromtheexistingtable DROP
COLUMN IN TABLE
Syntax:
ToDROPACOLUMNinanexistingtable,theOracleALTERTABLEsyntaxis:
ALTER TABLE table_nameDROP COLUMN column_name;
ExamplecustomersDROPCOLUMN customer_name;
SQL>ALTERTABLEcustomersDROPCOLUMNcustomer_name;

5. RENAME:Itisusedtomodifythenameoftheexistingdatabaseobject.
Syntax:RENAMEold_table_nameTO new_table_name;
Example:
SQL>RENAME EMP1 TOEMP2;
Tablerenamed.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

[Link]:[Link].
Syntax: TRUNCATE TABLE<Tablename>
7
Example:
TRUNCATE TABLE EMP1;
 EXPERIMENT _2

Queries using i)DML Commands. INSERT, UPDATE and DELETE ii)TCL

Commands: COMMIT , ROLLBACK and SAVEPOINT.
SQL queries using DML (Data Manipulation Language) commands: INSERT,
UPDATE, and DELETE. These examples use a fictional employees table to illustrate
each command.

ToCreateemployeetable:
CREATE:
CREATE TABLE:This is used to create a new relation and the
corresponding
Syntax:CREATETABLErelation_name(field_1data_type(Size),
field_2data_type(Size),...);
Example:
CREATE TABLE employee ( fname VARCHAR2(20),
lname VARCHAR2(20),
position VARCHAR2(20),
hire_date DATE,
salary NUMBER );
1. INSERT Command: The INSERT command is used to add new rows to a table.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
Insert a new employee into the employees table
INSERT INTO employees (first_name, last_name, position, hire_date, salary)
VALUES ('Emily', 'Davis', 'Marketing Specialist', '2024-08-01', 60000);
In this example:
 We insert a new row into the employees table with the columns first_name, last_name, position,
hire_date, and salary.

2. UPDATE Command: The UPDATE command is used to modify existing rows in a table.
Syntax:
UPDATE table_nameSET column1 = value1,
column2 = value2, ...
WHERE condition;
Example:
Update the position and salary of an employee
UPDATE employees
SET POSITION =’SALES EXECUTIVE’ SALARY=20000
WHERE FNAME=’XXXX’ AND LNAME=’YYYY’;
3. DELETE Command: The DELETE command is used to remove rows from a table.
Syntax:
DELETE FROM table_name
WHERE condition;
Example:
Delete an employee from the employees table
DELETE FROM employees
WHERE last_name = 'Davis' AND first_name = 'Emily';
In this example:
 We delete the row from the employees table where first_name is 'Emily' and last_name is 'Davis'.

ii)TCL Commands: COMMIT , ROLLBACK and SAVEPOINT.

COMMIT, ROLLBACK, and SAVEPOINT are technically part of Transaction Control Language (TCL)
1. COMMIT
The COMMIT command saves all changes made during the current transaction.
Syntax:
COMMIT;
Example:
Start a transaction
BEGIN
Insert a new employee into the table
INSERT INTO employees (first_name, last_name, position, hire_date, salary)
VALUES ('Alice', 'Green', 'HR Manager', '2024-08-11', 80000);
Commit the transaction to make changes permanent
COMMIT;
End
/
In this example:
 The BEGIN statement starts a new transaction.

 The INSERT statement adds a new row to the employees table.

 The COMMIT statement makes the INSERT operation permanent in the database.
2. ROLLBACK
The ROLLBACK command undoes all changes made during the current transaction.
Syntax:
ROLLBACK;
Example:
Start a transaction
BEGIN;
Insert a new employee into the table
INSERT INTO employees (first_name, last_name, position, hire_date, salary)
VALUES ('Bob', 'White', 'Sales Associate', '2024-08-11', 55000);
Rollback the transaction to undo the insertion
ROLLBACK;
 .
In this example:
 The BEGIN statement starts a new transaction.

 The INSERT statement adds a new row to the employees table.

 The ROLLBACK statement undoes the INSERT, so the new row is not saved in the database.
3. SAVEPOINT
The SAVEPOINT command sets a point within a transaction to which you can roll back.
Syntax:
SAVEPOINT savepoint_name;
Example:
Start a transaction
BEGIN;
Insert a new employee into the table
INSERT INTO employees (first_name, last_name, position, hire_date, salary)
VALUES ('Charlie', 'Brown', 'IT Specialist', '2024-08-11', 72000);
Set a savepoint
SAVEPOINT after_insertion;
Update the employee's position
UPDATE employees
SET position = 'Senior IT Specialist'
WHERE last_name = 'Brown';
Rollback to the savepoint to undo the update
ROLLBACK TO SAVEPOINT after_insertion;
Commit the transaction to keep the insertion
COMMIT;
In this example:
 The BEGIN statement starts a new transaction.

 The INSERT statement adds a new row to the employees table.

 The SAVEPOINT command creates a savepoint named after_insertion.
 The UPDATE statement changes the position of the employee.
 The ROLLBACK TO SAVEPOINT statement undoes the UPDATE, but the INSERT remains because
the rollback is only to the savepoint.
 The COMMIT statement saves the changes made before the savepoint.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT-3
Queries using i)SELECT statement ii) SELECT statement with where
clause(logicalOperators, AND, OR, NOT, IN, BETWEEN,LIKE) iii) ORDER BY
clause(sort by column name) iv) LIMIT clause
BasicSQL querying (select andproject)using where clause:
 ThefollowingarethevariousSQLclauses:

1. Groupby:
 SQL group by statement is used to arrange identical data into groups.
 The group by statement is used with the SQL select statement.
 The groupby statement follows the WHERE clause in a SELECT statement and precedes the
ORDERBY clause.

Syntax:
Select column from table_name where column group by column,
order by column;
Select Company from Product1 where Company groupby
Company, orderby Rate;

Sampletable:product
PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Com1 3 11 44

Example:
Select company Count(*) from product groupby company;
Output:
Com1 3
Com2 2
Com3 1

2. Having clause:
 Having clause is used to specify a search condition for a group or an aggregate.

Having clause is used in agroup by clause,if you are not using groupby
clause then you can usehaving function like a where clause.
 Syntax:
Selectcolumn1,column2fromtable_name

Whereconditions

Groupbycolumn1,colu

mn2Havingcondition

Orderbycolumn1,column2;

Example:
 select
company, count (*) from product
Group by company Having count (*) > 1;

[Link] clause:
Theorderbyclausesortstheresult_setinascendingordescendingorder.
Syntax:

Selectcolumn1,column2,fromtable_name

Wherecondition

Orderbycolumn1,column2…asc;

Sampletable:

Take a student table

Example:

Select *from Product1 orderby Company;

Output:
Logical Operations:
 Logicaloperationsallowyoutotestforthetruthofacondition.
 ThefollowingtableillustratestheSQLlogicaloperator.
OPERATOR MEANING
ALL Returns true if all comparisons are true
AND Returns true if both expressions are true
ANY Returns true if anyone of the comparisons is
true
BETWEEN Return true if the operand is within a range
IN Return true if the operand is equal to one of
The values in a list
EXISTS Return true if the sub query contains any rows

AND:The AND operator allows you to construct multiple condition in the WHERE
clause of an SQL statement such as select.
 Thefollowingexamplefindsallemployeeswheresalariesaregreaterthanthe5000andlessthan7000
.

 Select fname,lname ,salary from

employees where salary>5000 AND
salary<7000 orderby salary;
Output:
FIRST_NAME LAST_NAME SALARY
John Wesley 6000
Eden Daniel 6000
Luis Popp 6900
Shanta Suji 6500

ALL: The ALL operator compares a value to all values in another value set.
 The following example finds all employees whose salaries are greater than all
salaries of employees.
EX:

select first_name, last_name, salary from employees where

salary>=ALL(select salary from employees where
department_id=8)order by salary DESC;
Output:
FIRST_NAME LAST_NAME SALARY
Steven King 24000
ANY: The ANY operator compares a value to any value in a set ascending to
condition.
The following example statement finds all employees whose salaries are greater
than the average salary of every department.
EX:
Select first_name,last_name,salary from
employees where salary>ANY(select
avg(salary)from employees ‟group by
department_id) order by first_name,
last_name”;
Output:
FIRST_NAME LAST_NAME SALARY
Alexander Hunold 9000.00
Charles Johnson 6200.00
David Austin 4800.00
Eden Flip 9000.00

Between: The between operator searches for values that are with in a set of values.
 For example ,the following statement finds all employees where salaries are between 9000
and 12000.
Ex:
Select first_name, last_name, salary from employees
where salary between 9000 AND 12000 order by
salary;
Output:
FIRST_NAME LAST_NAME SALARY
Alexander Hunold 9000.00
Den Richards 10000.00
Nancy Prince 12000.00

IN: The IN operator compares a value to list of specified [Link] IN operator return true if compared value
matches at least one value in the list.
The following statement finds all employees who work in department _id 8 or 9

EX:

select first_name, last_name, department_id from

employees where department_id IN (8,9)order by
department_id;
Exists: The EXISTS operator tests if a subquery contains any rows.
 For example,the following statement finds all employees who have dependents.
 Select first_name,last_name from employees where
EXISTS(select1 from dependent
dwhered.employee_id=e.employee_id);
FIRST_NAM LAST_NAM
E E
Steven King
Neena Kochhar
Alexander Hunold

`
SELECT * FROM EMPLOYEE
ORDER BY Salary DESC
FETCH FIRST 3 ROWS ONLY;

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:4
14
QUERIES USING AGGREGATE FUNCTIONS(COUNT,SUM,AVG,MAXANDMIN)
GROUPBY,HAVING and Creation and dropping of Views.

SOLUTION:
[Link]:Calculatethenumberofemployeesindept20.
SQL>SELECT COUNT(*) NO_EMP FROM EMP WHERE DEPTNO=20;
NO_EMP

[Link]:Calculate the total salaries for each dept

SQL>SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO
DEPTNO SUM(SAL)

30 9400
20 10875
10 8750

[Link]:Calculatetheaveragesalariesforeachdept
SQL>SELECT DEPT_NO, AVG(SAL) FROM EMP GROUP BY DEPT_NO;
DEPT_NOAVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667

[Link]:Calculatethemaximumsalaryforeach dept
SQL>SELECT DEPTNO ,MAX(SAL) FROM EMP GROUP BY DEPTNO;
DEPTNOMAX(SAL)

30 2850
20 3000
10 5000

DEPARTMENT OF IT
 DATABASEMANAGEMENTSYSTEMS

5. MIN
Calculate the minimum salary for each dept
15
SQL>SELECT DEPTNO , MIN(SAL) FROM EMP GROUP BY DEPTNO
DEPTNOMIN(SAL)

30 950
20 800
10 1300

6. GROUPBY:
The GROUP BY clause is a SQL command thatis used to group rows that have the same values. The
GROUP BY clause is used in the SELECT [Link] it is used in conjunction with
aggregate functions to produce summary reports from the database.
GROUP BY Syntax
SELECT statements…GROUPBY column_name1[column_name2,…];
Grouping using a Single Column:
Create a table called data with gender column and values as male and female.
SQL>select Gender from data;
GENDER

male
female
female
female
female
female
male
male
male
female
male
male
female
male
male
female

16 rowsselected.

DEPARTMENT OF IT
 DATABASEMANAGEMENTSYSTEMS

SQL>select gender from data GROUP BY gender;

16
GENDER

male
female

SQL>select count(gender), gender from data GROUP BY gender;

8 male
8 female

Grouping using Multiple Columns

Syntax
SELECTColumn1,Column2,AGGREGATE_FUNCTION(Column3)FROMTABLE1GROUPBY
Column1,Column2
Examples:
SQL>select *from emp;
ID NAME DEPT SAL
-------------------- ---------- ----------
1 a cse 1000
2 b ece 2000
3 c eee 3000
4 d cse 4000
1 z cse 5000
5 a ece 6000
6 e ece 7000
2 b eee 9000
8rowsselected.
SQL>select id,name from emp GROUP BY id,name; ID
NAME

3 c
4 d
1 a
2 b
5 a
1 z
6 e
7rowsselected.

DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

[Link]
17
 The HAVING clause was added to SQL because the WHERE keyword could not be used with

aggregate functions.
 The WHERE clause places conditions on the selected columns,where as the HAVING clause
places conditions on groups created by the GROUP BY clause.
 The HAVING clause must follow the GROUP BY clause in a query and must also precede the
ORDER BY clause if used
HAVINGSyntax
SELECTcolumn_name(s)FROMtable_nameWHEREconditionGROUPBYcolumn_name(s)
HAVINGcondition

SQL>select *from emp;

ID NAMEDEPT SAL

1 a cse 1000
2 b ece 2000
3 c eee 3000
4 d cse 4000
5 e ece 5000

SQL>select count(id),dept from emp GROUP BY dept having count(id)>1;

COUNT(ID) DEPT
-------------------------
2 cse
2 ece

SQL>select *from emp;

IDNAME DEPT SAL
-------------------- ---------- ----------
1 a cse 1000
2 b ece 2000
3 c eee 3000
4 d cse 4000
5 e ece 5000

DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

SQL>select max(sal),dept from emp GROUP BY dept;

18
MAX(SAL)DEPT
---------- ----------
4000 cse
3000 eee
5000 ece

SQL>select max(sal),dept from emp GROUP BYdept having max(sal)>3000;

MAX(SAL) DEPT
---------- ----------
4000 cse
5000 ece

8. View:
o Views in SQL are considered as a virtual table.A view also contains rows and columns.
o To create the view,we can select the fields from one or more tables present in the database.
o A view can either have specific rows based on certain condition or all the rows of a table.

SQL>select*from emp;
Alter table Emp Add(LOCation Varchar(10));

ENO ENAME SALARY LOC

101 ali 15000vja

102 haji ` 20000hyd
103 mohammad 42000vja
104 ravi 23000 gnt
105 irfath 50000 hyd
SQL>create VIEW hyd as select*from emp where loc='hyd';
Viewcreated.

SQL>select*from hyd;
ENO ENAME SALARYLOC
102 haji 20000 hyd
105 irfath 50000 hyd

DEPARTMENTOFAIML
 DATABASEMANAGEMENTSYSTEMS

SQL>drop VIEW hyd;

View dropped.
19

SQL>select*from hyd;
Select * from hyd
*
ERRORatline1:
ORA-00942:table or view does not exist
 EXPERIMENT: 5
QUERIES (ALONG WITH SUB QUERIES) USING ANY, ALL, IN, EXISTS,
NOTEXISTS, UNION, INTERSECT
SOLUTION:
CREATE TABLE employee (
Fname VARCHAR2(20),
Lname VARCHAR2(20),
Ssn NUMBER(4) PRIMARY KEY,
B_date DATE,
Address VARCHAR2(30),
Gender CHAR(1),
Salary NUMBER(7,2),
Super_ssn NUMBER(4) REFERENCES employee(Ssn),
Dno NUMBER(4)
);
Table created.

SQL> INSERT INTO EMPLOYEE

VALUES('SMITH',NULL,1111,'03-NOV-2016','BJD','M',2000,NULL,10)
1 row created.
SQL> INSERT INTO EMPLOYEE
VALUES('ALLEN',NULL,2222,'03-NOV-2016','SBC','M',3000,1111,20)
1 row created.
SQL> INSERT INTO EMPLOYEE
VALUES('MARTIN',NULL,3333,'03-NOV-2016','HYD','M',4000,1111,30)
1 row created.
SQL> SELECT *FROM EMPLOYEE;
FNAME LNAME SSN BDATE ADDRESS G SALARY SUPER_SSN
DNO

SMITH 1111 01-JAN-06 BZA M 2000 10

ALLEN 2222 12-DEC-04 SBC M 3000 1111 20
MARTIN 3333 15-DEC-07 HYD M 4000 1111 20
JONES 4444 28-SEP-05 TNU M 1500 2222 10
BLAKE 5555 04-SEP-04 VZA M 2500 2222 10
TURNER 6666 21-OCT-99 GNT M 6000 3333 20
6 rows selected.
Inserting values in the dependent table as follows
SQL> INSERT INTO DEPENDENT VALUES (1111,'SMITH',’G’)
1 row is created.
SQL> INSERT INTO DEPENDENT VALUES (2222,'POOJA',’F’)
1 row is created.
SQL> INSERT INTO DEPENDENT VALUES (3333,'MARTIN',’M’)
1 row is created.
SQL> INSERT INTO DEPENDENT VALUES (3333,'RAJA',’M’)
1 row is created.

To Create dependent table:

SQL> CREATE TABLE DEPENDENT (
ESSN NUMBER (4) REFERENCES EMPLOYEE (SSN),
DEPENDENT_NAME VARCHAR2 (20),
GENDER CHAR (1), B_DATE DATE,
RELATIONSHIP VARCHAR2 (20),
PRIMARY KEY (ESSN, DEPENDENT_NAME)
);
Table created.
To view data in the dependent table as follows.
SQL>SELECT * FROM DEPENDENT;
ESSN DEPENDENT_NAME G B_DATE RELATIONSHIP

1111 SMITH M
2222 POOJA F
3333 MARTIN M
3333 RAJA M

1. ALL:
Retrieve the names of employees whose salary is greater than the salary of all the employees in department 10
SQL> SELECT FNAME, LNAME FROM EMPLOYEE
WHERE SALARY> ALL ( SELECT SALARY FROM EMPLOYEE WHERE DNO=10);
FNAME LNAME

ALLEN
MARTIN
TURNER

2. ANY
Retrieve the names of employees whose salary is greater than the salary of any one of the employees in
department 10
SQL> SELECT FNAME, LNAME FROM EMPLOYEE
WHERE SALARY> ANY( SELECT SALARY FROM EMPLOYEE WHERE DNO=10);

FNAME LNAME
TURNER
MARTIN
ALLEN
BLAKE
SMITH
3. IN
Retrievethenameofeachemployeewho hasadependent withthefirstnameandsamegenderasthe employee

SQL>SELECT [Link], [Link] FROM EMPLOYEE e WHERE [Link](SELECT ESSN FROM

DEPENDENT WHERE [Link]=GENDER AND [Link] = DEPENDENT_NAME);

FNAME LNAME

SMITH
MARTIN

4. EXISTS
Retrievethenameofeachemployeewho hasadependent withthefirstnameandsamegenderasthe employee

SQL>SELECT [Link], [Link] FROM EMPLOYEE eWHERE EXISTS (SELECT

*FROM DEPENDENT WHERE [Link]=ESSN AND [Link]=GENDER AND
[Link] =DEPENDENT_NAME);

FNAME LNAME

SMITH
MARTIN

[Link]
Retrievethenamesofemployeeswhohavenodependents
SQL>SELECT FNAME,LNAME FROM EMPLOYEE WHERE NOT EXISTS(SELECT*
FROM DEPENDENT WHERE SSN=ESSN);
FNAME LNAME
ALLEN
SQLConstraints
SQLconstraintsareused to specifyrulesforthe datainatable.
Constraints are used to limit the type of data that can be insertinto a table. This ensures the accuracy
and reliability of the data in the table. If there is any violation between the constraint and the data
action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and table
level constraints apply to the whole table.
ThefollowingconstraintsarecommonlyusedinSQL:
 NOTNULL-EnsuresthatacolumncannothaveaNULLvalue
Example:
SQL>createtableperson1(idint,namevarchar2(10)notnull,ageint); Table
created.
 UNIQUE-Ensuresthatallvaluesinacolumnare different
Example:
SQL>createtableperson(id intunique,namevarchar2(10),ageint);
Tablecreated.
 [Link] row in
a table
Example:
SQL>createtableemp1(idnumber(10)primarykey,namevarchar2(10),salint); Table
created.
 FOREIGNKEY-Uniquelyidentifiesarow/recordinanothertable
o AFOREIGNKEYisakeyusedtolinktwotablestogether.
o AFOREIGNKEYisa field(orcollectionoffields) inonetablethat referstothe
PRIMARY KEY in another table.
o Thetablecontainingthe foreignkeyiscalledthechildtable,andthetablecontainingthe
candidate key is called the referenced or parent table.
Example:
SQL>createtableemp2(eidint,cityvarchar2(10),foreign key(eid)referencesemp1(id));
Tablecreated.
CHECK - Ensures that all values in a column satisfies a specific condition

Example:

SQL> CREATE TABLE person1( ID int ,Age int, City varchar(10), CONSTRAINT chk
CHECK(Age>=18 AND City='vja');

Table created.

• DEFAULT - Sets a default value for a column when no value is specified.

o The DEFAULT constraint is used to provide a default value for a column.

o The default value will be added to all new records IF no other value is specified.

SQL DEFAULT on CREATE TABLE

SQL> create table emp(id number(10),name varchar2(10),city varchar2(10) default 'vja');

Table created.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:6
20
QUERIES USING CONVERSION FUNCTIONS (TO_CHAR,
TO_NUMBER AND TO_DATE), STRING FUNCTIONS
(CONCATENATION, LPAD, RPAD, LTRIM, RTRIM, LOWER,
UPPER, INITCAP, LENGTH, SUBSTR AND INSTR), DATE
FUNCTIONS (SYSDATE, NEXT_DAY, ADD_MONTHS,
LAST_DAY, MONTHS_BETWEEN, LEAST,
GREATEST,TRUNC,ROUND,TO_CHAR)

SQL>select*from emp;
ENOENAME SALARY LOC
101 ali 15000 vja
102 haji 20000 hyd
103 mohammad 42000 vja
104 ravi 23000 gnt
105 irfath 50000 hyd

a)ConversionFunctions:
1.to_char:to_char is used to convert the attribute values to char.
SQL>select to_char(salary,'$99999.99')from emp;

TO_CHAR(SALARY)

$15000.00
$20000.00
$42000.00
$23000.00
$50000.00

SQL> TO_CHAR (

123.5

SQL>SELECT TO_
CHAR(123.4567,'99999.99')FROM DUAL; TO_CHAR(1
 123.46

SQL>SELECT TO_CHAR(1234.56789,'9,999.00')FROM DUAL;

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

TO_CHAR(1 21
1,234.57

SQL>SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD')FROM DUAL;

TO_CHAR(SY

2021/07/09

SQL>SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY')FROM DUAL;

TO_CHAR(SY

09/07/2021

SQL>SELECT TO_CHAR(23,'000099')FROM DUAL;

TO_CHAR

000023

SQL>SELECT TO_CHAR(123,'0000999')FROM DUAL;

TO_CHAR(

0000123

SQL>SELECT TO_CHAR(3,'00009')FROM DUAL;

TO_CHA

00003

SQL>SELECT TO_CHAR(23,'00099')FROM DUAL;

TO_CHA

00023

SQL>SELECT TO_CHAR(234.5678,'999.99')FROM DUAL;

TO_CHA

######

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

22

2. to_number:to_number isusedtoconverttheattributevalueto number.

SQL>SELECT TO_NUMBER('1210.73', '9999.99')FROMDUAL;

TO_NUMBER(‘1210.73’,'9999.99')

1210.73

3. to_date:to_dateisused forconvert anddisplaytheattributevaluesasdate.

SQL> select to_date('01-01-2020', 'MM-DD-YYYY') from dual;
TO_DATE('

01-JAN-20

DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

23
b) Stringfunctions:
1. Concatenation:CONCATisused toaddtwoattributevaluessuchasstring.

SQL>select concat(eno,loc)from emp;

CONCAT(ENO,LOC)

101vja
102hyd
103vja
104gnt
105hyd

2. lpad:LPAD()functionisusedtopaddingtheleftsideofastringwithaspecificsetofcharacters.

SQL>select lpad(ename,10,'*')fromemp;
LPAD(ENAME,10,'*')

*******ali
******haji
**mohammad
******ravi
****irfath

3. rpad:RPAD() functionisusedtopaddingtherightsideofastringwithaspecificsetofcharacters.

SQL>select rpad(ename,10,'*')from emp;

RPAD(ENAME,10,'*')

ali*******
haji******
mohammad**
ravi******
irfath****
 DATABASEMANAGEMENTSYSTEMS

ltrim:LTRIM() function is used to remove all specified characters from the left ends id of a string
24

SQL> select ltrim('******hi********','*')from dual;

LTRIM('***

hi********

5. rtrim:RTRIM()functionisusedtoremoveallspecified charactersfromtheleftendsideofastring

SQL>select rtrim('******hi********','*')from dual;

RTRIM('*

******hi
6. lower:lower() function is used to convert the attribute value into lowercase.
SQL>select lower(ename) from emp;

LOWER(ENAM

ali
haji
mohammad
ravi
irfath

7. upper:upper()function is used to convert the attribute values into upper case.

SQL>selectupper(ename)from emp;

UPPER(ENAM

ALI
HAJI
MOHAMMAD
RAVI
IRFATH

8. initcap: initcap() isusedtoconverttheattributevaluesfirstcharacterincapitalletter.

SQL>selectinitcap(ename)fromemp;
DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

INITCAP(EN 25

Ali
Haji
Mohammad
Ravi
Irfath

9. length:length() functionisusedtocalculatethelengthofthegivenattribute.
SQL>selectename,length(ename)fromemp;
ENAME LENGTH(ENAME)

ali 3
haji 4
mohammad 8
ravi 4
irfath 6

10. substr:substr() function is used to find the substring of the given attribute value. It retuns size-1 of
the given string/ attribute as a sub string.
SQL>select ename,substr(ename,4)from emp;

ENAME SUBSTR(ENAME,4)

ali
haji i
mohammad ammad
ravi
i
irfath ath

11. instr:instr()functionreturnthe locationofstartingpassionofthesubstringintheexistingvalue.

DEPARTMENTOFAIML
 DATABASEMANAGEMENTSYSTEMS

INSTR('WELCOMETO CRRCOE','TO') 26

c) Datefunctions:

1. Sysdate():sysdate() functionreturnsthecurrent systemdate.

SQL>select sysdate from dual;

SYSDATE

28-APR-21

2. next_day();itreurnsthedateofnextcomingday.

SQL>selectnext_day(sysdate,'sunday')fromdual;

NEXT_DAY

( 02-MAY-

21

3. add_months():itreturnsthenextdateafter addingnumberofmonthsintheorguments.

SQL>select add_months(sysdate,5)from dual;

ADD_MONT

H 28-SEP-21

4. last_day(): The LAST_DAY()function takes a date value as argumentand returns the lastday of
month in that date
SQL>select last_day(sysdate)from dual;
LAST_DAY(

30-APR-21

SQL>selectlast_day('02-FEB-2020')fromdual; DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

29-FEB-20
27
5. months_between():itreturnsthenumbersofmonthsbetweengiventwodates.

SQL>select months_between('02-feb-2021','02-feb-2020')from dual;

MONTHS_BETWEEN('02-FEB-2021','02-FEB-2020')

12

SQL>selectmonths_between(sysdate,'02-feb-2020')fromdual;

MONTHS_BETWEEN(SYSDATE,'02-FEB-2020')

14.8600769

6. least():itretunsleastvaluefromthegivenargument orattributes.

SQL>select least(300,450,100,440)from dual;

LEAST(300,450,100,440)

100

7. greatest():itreturnsmaximumvaluesfromthegivenargumentsorattributesintherelation.

SQL>select greatest(300,450,100,440)fromdual;
GREATEST(300,450,100,440)

450
8. trunc():TheTRUNC()functionreturnsaDATEvaluetruncatedtoaspecifiedunit.

SQL>select trunc(sysdate,'mm')fromdual;

TRUNC(SYS

01-APR-21

SQL>selecttrunc(sysdate,'yyyy')fromdual;
TRUNC(SYS
DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

01-JAN-21
28
9. round():Roundfunctionround anumbertoaspecifiedlengthorprecision.
SQL>selectround(12.49,0)fromdual;

ROUND(12.49,0)

12

SQL>selectround(12.51,0)fromdual;

ROUND(12.51,0)

13

10. to_char():itconvertthegiven date type attributevalues totextand return the date in the specific format.
SQL>selectto_char(sysdate,'yyyy-mm-dd')fromdual;

TO_CHAR(S

Y 2021-04-28
 EXPERIMENT-7

Aim:To write queries(along with sub Queries)using

ANY ,ALL ,IN,EXISTS,NOTEXISTS,UNION,INTERSECT,Constraints.

Q1)UNION:Findthenamesofsailorswhohavereservedaredoragreenboat.

Query:Using‘UNION’
SQL> [Link]
2 from sailors s,reserves r,
3 boats b
4 where [Link]=[Link] [Link]=[Link] and [Link]='red'
5 union
6 select [Link]
7 from sailors s2,boats b2,reserves r2
8 where [Link]=[Link] and [Link]=[Link]
and [Link]='green';
Output:
SNAME

dustin

Horatio

Lubber

Query:Using‘or’

SQL>[Link]
2 fromsailorss,reservesr,boats b
3 [Link]=[Link]=[Link]([Link]='red'[Link]='green');

Output:

SNAME
SMITH
MARTIN

6. EXISTS
Retrievethenameofeachemployeewho hasadependent withthefirstnameandsamegenderasthe employee

SQL>[Link],[Link] (SELECT
*[Link]=[Link]=GENDERAND [Link]
=DEPENDENT_NAME);

FNAME LNAME

SMITH
MARTIN

[Link]
Retrievethenamesofemployeeswhohavenodependents
SQL>SELECTFNAME,LNAMEFROMEMPLOYEEWHERENOTEXISTS(SELECT* FROM
DEPENDENT WHERE SSN=ESSN);
FNAME LNAME

ALLEN
Output: SNAME
brutus
andy
rusty
horatio
zorba
art
bob

7 rows selected.

Q6)EXISTS Query: Find the names of sailors who have reserved boat number 103 using EXISTS Operator
SQL> select [Link]
2 from sailors s
3 where exists (select * from reserves r where [Link] = 103 and [Link] = [Link] );

Output:
SNAME

dustin
lubber
horatio

Q7) NOT EXISTS: Find the names of sailors who have not reserved boat number 103 using NOT EXISTS
Operator.

Query:SQL> select [Link] 2 from sailors s

3 where not exists (select * from reserves r where [Link] = 103 and [Link] =[Link]); Output:
SNAME
Brutus
andy
rusty
Horatio
Zorba
art
bob

7 rows selected.
Q8) ANY: Find sailors whose rating is better than some sailor called ‘BOB’ using ANY Operator. Query:
SQL> select [Link]
2 from sailors s
3 where [Link] > any (select [Link]
4 from sailors s2 where [Link] = 'bob' ); Output:
SID
58
71
74
31
32
64
22
7 rows selected

Q9)ALL: Find the sailor's with the highest rating using ALL Operator.
Query:
SQL> select [Link]
2 from sailors s
3 where [Link] >= all ( select [Link] from sailors s2 );
Output:
SID
58
71
 EXPERIMENT-8

AIM: queries using SQL that illustrate the use of INNER JOIN, OUTER JOIN, USING, and NATURAL JOIN
Table Structures
1 students
 student_id (Primary Key)
 name
 major
2 enrollments
 student_id (Foreign Key)
 course_id
 semester
Queries
1. INNER JOIN using USING
This query retrieves the names of students along with their enrolled courses using the USING keyword.
sql
Copy code
SELECT [Link], enrollments.course_id
FROM students
INNER JOIN enrollments USING (student_id);
2. LEFT OUTER JOIN using USING
This query retrieves all students and their enrolled courses. Students who are not enrolled in any courses will still
appear, with NULL in the course_id field.
sql
Copy code
SELECT [Link], enrollments.course_id
FROM students
LEFT OUTER JOIN enrollments USING (student_id);
3. RIGHT OUTER JOIN using USING
This query retrieves all enrollments and the corresponding student names. If a course does not have an associated
student, the student name will be NULL.
sql
Copy code
SELECT [Link], enrollments.course_id
FROM students
RIGHT OUTER JOIN enrollments USING (student_id);
4. NATURAL JOIN
This query automatically joins the tables on columns with the same name, which in this case is student_id.
sql
Copy code
SELECT [Link], enrollments.course_id
FROM students
NATURAL JOIN enrollments;
5. FULL OUTER JOIN using USING
This query retrieves all students and all enrollments, showing NULLs where there are no matches.
sql
Copy code
SELECT [Link], enrollments.course_id
FROM students
FULL OUTER JOIN enrollments USING (student_id);
Summary
 INNER JOIN: Returns only matching rows.
 OUTER JOIN: Returns all rows from one table and matched rows from the other.
FULL OUTER JOIN enrollments USING (student_id);
Summary
 INNER JOIN: Returns only matching rows.
 OUTER JOIN: Returns all rows from one table and matched rows from the other.
 USING: Simplifies the join condition for columns with the same name.
 NATURAL JOIN: Automatically joins based on all columns with the same name.
Feel free to modify these queries based on your specific schema and requirements!
 DATABASEMANAGEMENT
Experiment-9
i. Create a simple PL/SQLprogram which includes declaration section,executable section and exception –
29
Handling section (Ex. Student marks can be selected from the table and printed for those
Who secured first class and an exception can be raised if no records were found).
ii. Insert data into student table and use COMMIT,ROLLBACK and SAVEPOINT in PL/SQLblock..

i).We have to create the student table and insert the records into the table as follows:

SQL> create table student(sid number(10),sname varchar2(20),rank varchar(10));

Tablecreated.

SQL>insert intostudent values(501,'Ravi','second');

1 rowcreated.

SQL>insertintostudentvalues(502,'Raju','third');
1 rowcreated.

SQL>insert intostudent values(503,'Ramu','');

1 rowcreated.

SQL>select*fromstudent;

SID SNAME RANK

501Ravi second
502Raju third
503Ramu
PL/SQLCODE:
 DATABASEMANAGEMENTSYSTEMS

SQL>ed5a
30
Enterthefollowing [Link]

set serveroutput on;

declare
temp1 number(10);
temp2 varchar2(10);

begin
select sid,sname into temp1,temp2 from student where rank='first';
dbms_output.put_line('StudentNo:'||temp1||'Name:'||temp2||'gotfirst rank');
exception
when no_data_found then
dbms_output.put_line('********************************************');
dbms_output.put_line('# Error: there is no student got first rank');
end;
/

SQL>@5a;
********************************************
# Error: there is no student got first rank

PL/SQLproceduresuccessfullycompleted.

SQL>updatestudentsetrank='first'wheresid=503; 1
row updated.

SQL>select*fromstudent;

SIDSNAME RANK

501Ravi second
502Raju third
503Ramu first

SQL>@5a
StudentNo:503Name:Ramugotfirstrank

PL/SQLproceduresuccessfullycompleted.

ii)
SQL>select*fromstudent;
DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

SIDSNAME RANK 31
501Ravi second
502Raju third
503Ramu first

PL/SQLCODE:

SQL>ed5b

Enterthefollowingcodeintothetext [Link] set

serveroutput on;

DECLARE
sno [Link]%type;
[Link]%type;
srank [Link]%type;

BEGIN
sno := &sno;
name:='&name';
srank:='&srank';
INSERTinto student values(sno,name,srank);
dbms_output.put_line('One record inserted');
COMMIT;
--addingsavepoint
SAVEPOINT s1;
--secondtimeaskinguserforinput
sno := &sno;
name:='&name';
srank:='&srank';
INSERTinto student values(sno,name,srank);
dbms_output.put_line('One record inserted');
ROLLBACK TO SAVEPOINT s1;
END;
/

SQL>@5b;

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>@5b 32
Enter value for sno: 504
old7: sno:=&sno;
new7: sno:=504;
Entervalueforname:ali
old8: name:='&name';
new8: name := 'ali';
Enter value for srank: first
old9: srank:='&srank';
new9: srank := 'first';

Enter value for sno: 505

old16: sno :=&sno;
new16: sno := 505;
Entervalue forname:haji
old17: name:='&name';
new17: name := 'haji';
Enter value for srank: third
old18: srank:='&srank';
new18: srank := 'third';
One record inserted
Onerecordinserted

PL/SQLproceduresuccessfullycompleted.

SQL>select*fromstudent;

SIDSNAME RANK

501Ravi second
502Raju third
503Ramu first
504ali first

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:10

Develop a program that includes the features NESTEDIF,CASE andCASE [Link] program
33
Can be extended using the NULLIF and COALESCE functions.
[Link]:
A nested if-then is an if statement that is the target of another if statement. Nested if-then statements
mean an if statement inside another if statement
Syntax:-
if(condition1)then
-- Executes when condition1 is true
if (condition2) then
-- Executes when condition2 is true
end if;
endif;

PL/SQL CODE:PL/SQL Program to find biggest of three number using nested if.
SQL>ed 6a
Enter the following code into the text editor and save the file with .sql format

declare
anumber:=10;
bnumber:=12;
cnumber:=5;
begin
dbms_output.put_line('a='||a||'b='||b||'c='||c);
if a>b AND a>c then
dbms_output.put_line('aisgreatest');
else
ifb>a AND b>cthen
dbms_output.put_line('bisgreatest');
else
dbms_output.put_line('cisgreatest');
endif;
endif;
end;
/

SQL>@6a
DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

a=10b=12c=5
34
bisgreatest
PL/SQLproceduresuccessfullycompleted.

B. CASE and CASE Expression:CASE statement selects one sequence of statements to

[Link],toselectthesequence,theCASEstatementusesaselectorratherthanmultipleBoolean
expressions. Aselector is anexpression, the value ofwhich is used to select one ofseveralalternatives.
Syntax
CASEselector
WHEN'value1'THENS1;
WHEN'value2'THENS2;
WHEN'value3'THENS3;
...
ELSESn;--defaultcase END
CASE;
SQL>createtableemp(enonumber(5),enamevarchar2(10),locvarchar(10),salary number(10,2));
Tablecreated.
SQL>insertintoempvalues(101,'ali','vja',15000); 1
row created.
SQL>insertintoempvalues(102,'ravi','hyd',25000); 1
row created.
SQL>insertintoempvalues(103,'raju','gnt',35000);); 1
row created.
SQL>insertintoempvalues(104,'rakesh','vja',45000); 1
row created.
SQL>select*from emp;
ENOENAME LOC SALARY

101 ali vja 15000

102 ravi hyd 25000
103 raju gnt 35000
104 rakeshvja 45000

ExampleofCASE Expression:

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>select loc,case(loc)when'vja'thensalary+2000when'hyd'thensalary+1000elsesalary
35
end"rev_salary"fromemp;
LOC rev_salary

vja 17000
hyd 26000
gnt 35000
vja 47000

PL/SQLCODE:PL/SQLCODEtodemonstrateCASE

SQL> ed 6b

setserveroutputon;
declare
grade char(1);
begin
grade:='&grade';
case
when grade='a'then
dbms_output.put_line('Excellent');
when grade='b' then
dbms_output.put_line('verygood');
when grade='c' then
dbms_output.put_line('good');
when grade='d' then
dbms_output.put_line('fair');
when grade='f' then
dbms_output.put_line('poor');
else
dbms_output.put_line('No suchgrade');
endcase;
end;
/

SQL>@6b
Entervalueforgrade:c
old4:grade:='&grade';
new4:grade:='c';
good

PL/SQLproceduresuccessfullycompleted.
SQL> @6b
Entervalueforgrade:g
old4:grade:='&grade';
DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

new4:grade:='g';
Nosuchgrade 36
PL/SQLproceduresuccessfullycompleted.

C. NULLIF: Takes two arguments. If the two arguments are equal, then NULL is returned. otherwise
the first argument is returned.
Syntax:selectcolumn_name,NULLIF(argument1,arguement2)fromtable_name;
Example:
SQL>selectename,nullif('ali','ali1')fromemp;
ENAME NUL

ali ali
ravi ali
raju ali
rakesh ali

SQL>selectename,nullif('ali','ali')fromemp;
ENAME NUL

aliravi
raju
rakesh

D. COALESCE:COALESCE()function accepts a list of arguments and returns the first one that
evaluates to a non-null value.

Syntax:coalesce("expression1","expression2",...);
Example:
SQL>select coalesce(NULL,'dbms','IT')from dual;
COALE

dbms

DEPARTMENT OFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:11 37
ProgramdevelopmentusingWHILELOOPS,numericFORLOOPS,nestedloopsusingERROR
Handling,BUILT–INExceptions,USEdefinedExceptions,RAISEAPPLICATIONERROR.
WHILE LOOP: AWHILE LOOPstatementin PL/SQL programminglanguage repeatedly
executes a target statement as long as a given condition is true.
Syntax:
WHILE condition LOOP
sequence_of_statements
END LOOP;
PL/SQLCode:APL/SQLProgramtofindsumofODDnumberupto givennumberusingWhile loop SQL> ed
7a

setserveroutputon;
declare
inval number;
endvalnumber;
snumberdefault0;
begin
inval:=1;
endval:=&endval;
whileinval<endvalloop
s:=s+inval;
inval:=inval+2;
endloop;
dbms_output.put_line('sumofoddnumbersbetween1and '||endval||'is'||s); end;
/

SQL>@7a
Enter value for endval: 100
old7: endval:=&endval;
new7: endval:=100;
sumofoddnumbersbetween1and100is2500

PL/SQL procedure successfully completed.

A. FORLoop:AFORLOOPisarepetition controlstructurethatallows ustoefficiently writealoop

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

thatneedstoexecuteaspecificnumber oftimes.
38
Syntax
FORcounterINinitial_value..final_valueLOOP sequence_of_statements;
ENDLOOP;
PL/SQLCODE:APL/SQLcodetoprintmultiplicationtableusingforloop
SQL>ed 7b

setserveroutputon;
DECLARE
VAR1NUMBER;
VAR2NUMBER;
BEGIN
dbms_output.put_line('Enternumbertoprintmultiplicationtable');
VAR1:=&VAR1;
FOR VAR2 IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(VAR1||'X'||
VAR2||'='||VAR1*VAR2);
ENDLOOP;
END;
/
SQL>@7b
Enter value for var1: 2
old6:VAR1:=&VAR1;
new6:VAR1:=2;
Enternumertoprintmultiplicationtable 2X1=2
2X2=4
2X3=6
2X4=8
2X5=10
2X6=12
2X7=14
2X8=16
2X9=18
2X10=20

PL/SQLproceduresuccessfullycompleted.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

[Link]:PL/[Link]
39
basic,whileorforloop.
Syntax:
WHILE condition1 LOOP
sequence_of_statements1
WHILE condition2 LOOP
sequence_of_statements2
ENDLOOP;
END LOOP;
PL/SQLCODE:APL/SQLprogramto print nprimenumberusingnestedloop. SQL>
ed 7c
DECLARE
inumber(3);
jnumber(3);
BEGIN
i := 2;
LOOP
j:= 2;
LOOP
exitWHEN((mod(i,j)=0)or(j=i)); j := j
+1;
END LOOP;
IF(j=i)THEN
dbms_output.put_line(i||'isprime');
END IF;
i:= i+1;
exitWHENi=50;
END LOOP;
END;
/
SQL>@7c
DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

2is prime
3is prime
40
5is prime
7is prime
11isprime
13is prime
17is prime
19is prime
23is prime
29is prime
31is prime
37is prime
41is prime
43is prime
47is prime

PL/SQLproceduresuccessfullycompleted.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:12
41
Programs development using creationofprocedures,passing parametersINand OUTof
PROCEDURES.
SQL>createtableenquiry(enqno1number(3),fnamevarchar2(30));
Tablecreated.
SQL>insertintoenquiryvalues(111,'sai');
1 rowcreated.
SQL>insertintoenquiryvalues(112,'sindhu');
1 rowcreated.

PL/SQLCODEtocreateprocedure
SQL>edfindname

createprocedurefindname(enquiryno1INnumber,fname1OUTvarchar2)is
fname2 varchar2(30);
begin
selectfnameintofname2fromenquirywhereenqno1=enquiryno1;
fname1:=fname2;
exceptionwhenno_data_found then
raise_application_error(-20100,'Thegivennumberisnotpresent'); end;
/

SQL>@findname
Procedurecreated
PL/SQLCodeforcallingprocedureinprogram

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>ed pro8
42
setserveroutputon;
declare
enqno2 number(5);
fname2varchar2(30);
begin
enqno2:=&enqno2;
findname(enqno2,fname2);
dbms_output.put_line('Personnameofequiryid'||enqno2||'is'||fname2); end;
/

SQL>@pro8
Entervalueforenqno2:114
old5: enqno2:=&enqno2;
new5:enqno2:=114;
declare
*
ERRORatline1:
ORA-20100: The given number is not present
ORA-06512:at"[Link]",line7
ORA-06512:atline6

SQL>@pro8
Entervalueforenqno2:112
old5: enqno2:=&enqno2;
new5:enqno2:=112;

Person name of equiry id 112 is sindhu

PL/SQLproceduresuccessfullycompleted.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:13
43
Programdevelopmentusingcreationofstoredfunctions,invokefunctionsinSQLstatementsandwrite
complexfunctions.
Sol:
SQL>createtabledept(deptnoint,dnamevarchar(10));
Tablecreated.
SQL>insertinto dept values(1219,'sai');
1 rowcreated.

PL/SQLCODEtocreateuserdefine function

createorreplacefunctiongetname(dnonumber) return
varchar2 as
fname1varchar2(30);
begin
selectdnameintofname1fromdeptwheredeptno=dno;
return(fname1);
exception
whenno_data_foundthen
raise_application_error(-20100,'YourenteredDepartmentnumberisnotexists');
end;
/

SQL>@getname
Functioncreated.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

44
SQL>ed pro9

setserveroutputon;
declare
fname2varchar2(30);
deptno2 number(5);
begin
deptno2:=&deptno;
fname2:=getname(deptno2);
dbms_output.put_line(fname2||'isindeptno'||deptno2);
end;
/

SQL>@pro9
Entervaluefordeptno:1219
old5: deptno2:=&deptno;
new5:deptno2:=1219;

saiis indeptno1219

PL/SQLproceduresuccessfullycompleted.

SQL> @pro9
Entervaluefordeptno:1001
old5: deptno2:=&deptno;
new5:deptno2:=1001;
declare
*
ERRORatline1:
ORA-20100:YourenteredDepartmentnumberisnotexists
ORA-06512: at "[Link]", line 9
ORA-06512:atline6

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

EXPERIMENT:14
Develop programs using features parameters in a CURSOR, FOR UPDATECURSOR,WHERE CURRENT
45
of clause and CURSOR variables.

Sol:
SQL>createtablecustomers(idnumber(3),namevarchar2(10),agenumber(3),address varchar2(10),
salary number(10,2));
Tablecreated.

SQL>insertintocustomersvalues(1,'ramesh',32,'ahmedabad',2000);
1 rowcreated.
SQL>insert intocustomersvalues(2,'khilan',25,'Delhi',1500);
1 rowcreated.
SQL>insert intocustomersvalues(3,'kaushik',23,'Kota',2000);
1 rowcreated.
SQL>insertintocustomersvalues(4,'chitali',25,'Mumbai',6500);
1 rowcreated.
SQL>select*from customers;
IDNAME AGEADDRESS SALARY
-
1ramesh 32ahmedabad 2000
2khilan 25 Delhi 1500
3kaushik 23 Kota 2000
4chitali 25Mumbai 6500

4 rowsselected.

SQL>ed pro10

DECLARE
SIRCRREDDYCOLLEGEOFENGINEERING DEPARTMENTOFINFORMATIONTECHNOLOGY
c_idcustomers.id%type;
c_namecustomers.name%type;
c_addrcustomers.address%type;
 DATABASEMANAGEMENTSYSTEMS

46

SQL>@pro10
1 rameshahmedabad
2 khilanDelhi
3 kaushikKota
4 chitaliMumbai
PL/SQLproceduresuccessfullycompleted.

EXPERIMENT:15

Develop programs using before and after triggers, row and statement triggers and instead of triggers.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

Sol:
47
SQL>createtablecustomers(idnumber(3),namevarchar2(10),agenumber(3),address
varchar2(10),salarynumber(10,2));
Tablecreated.

SQL>insertintocustomersvalues(1,'ramesh',32,'ahmedabad',2000);
1 rowcreated.
SQL>insert intocustomersvalues(2,'khilan',25,'Delhi',1500);
1 rowcreated.
SQL>insert intocustomersvalues(3,'kaushik',23,'Kota',2000);
1 rowcreated.
SQL>insertintocustomersvalues(4,'chitali',25,'Mumbai',6500);
1 rowcreated.
SQL>select*fromcustomers;
IDNAME AGEADDRESS SALARY
-
1 ramesh 32ahmedabad 2000
2 khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 chitali 25Mumbai 6500

4 rowsselected.

PL/SQLCodeforcreationoftrigger while insert /updaterecordsintoatable.

SQL>ed pro11
DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

CREATE OR REPLACE TRIGGER display_salary_changes 48

BEFOREDELETEORINSERTORUPDATEONcustomers FOR
EACH ROW
WHEN([Link]>0)
DECLARE
sal_diffnumber;
BEGIN
sal_diff := :[Link] - :[Link];
dbms_output.put_line('Old salary: ' || :[Link]);
dbms_output.put_line('Newsalary: '||:[Link]);
dbms_output.put_line('Salarydifference:'||sal_diff);
END;
/

SQL> @pro11
Triggercreated.

SQL>insertintocustomersvalues(5,'Hardik',27,'Mumbai',5500); Old
salary:
Newsalary:5500
Salarydifference:
1 rowcreated.

SQL>updatecustomerssetsalary=salary+500whereid=2;
Old salary: 1500
Newsalary:2000
Salarydifference:500
1rowupdated.

EXPERIMENT:16
Createatableand performthesearchoperationontableusingindexing and non-indexing techniques.
DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

Sol:
SQL>CREATETABLETEACHER(STAFF_IDVARCHAR2(4)PRIMARYKEY,STAFF_NAME
VARCHAR2(30),QUALIFICATIONVARCHAR2(10),HIREDATEDATE,JOBVARCHAR2(30),
ADDRESSVARCHAR2(15),PH_NUMNUMBER(10),SALARYNUMBER(7,2),DEPARTMENT
VARCHAR2(10));

Tablecreated.

SQL>insertintoteachervalues('T101','SUNITHA','MCA','29-JUN-06','ASSOCIATE
PROFESSOR','VIJAYAWADA',9985061308,23000,'MCA');
1 rowcreated.

SQL>insertintoteachervalues('T102','FREDSMITH','MTECH','07-MAR-03','ASSOCIATE
PROFESSOR','GUNTUR',9985063445,36000,'MBA');
1 rowcreated.

SQL>insertintoteachervalues('T103','JACKBARNES','BTECH','27-JUN-
07','PROFESSOR','TENALI',9985012345,27000,'MTECH');
1 rowcreated.

SQL>insertintoteachervalues('T104','JANEDOE','MCA','04-JUL-06','ASSISTANT
PROFESSOR','VIJAYAWADA',9985045678,29000,'BTECH');
1 rowcreated.

SQL>insertintoteachervalues('T105','JOESHMOE','MBA','16-AUG-08','ASSOCIATE
PROFESSOR','ELURU',9987651308,36000,'MCA');
1 rowcreated.

SQL>insertintoteachervalues('T106','JONBAKER','MSC(COM)','12-JAN-
03','PROFESSOR','HYDERABAD',8876561308,46000,'MCA');
1 rowcreated.

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>insertintoteachervalues('T107','JOHNDOE','MSC(PHY)','06-FEB-04','ASSISTANT
50
PROFESSOR','VIJAYAWADA',8345661308,31000,'MBA');
1 rowcreated.

SQL>insertintoteachervalues('T108','KIMSMITH','MCA','10-MAR-08','ASSISTANT
PROFESSOR','VIZAG',8374561308,26000,'MTECH');
1 rowcreated.

SQL>insertintoteachervalues('T109','MARYPARKER','MTECH','02-APR-
09','PROFESSOR','NELLORE',7893427649,52000,'MBA');
1 rowcreated.

SQL>insert into teachervalues('T110','SAMUELJOHN','BTECH','19-MAY-05','ASSISTANT

PROFESSOR','ELURU',9982222208,26000,'MBA');
1 rowcreated.

SQL>insert into teacher values('T111','FRANKLIN WONG','MBA','20-AUG-06','ASSOCIATE

PROFESSOR','VIZAG',9985033333,20000,'MTECH');
1 rowcreated.

SQL>insert into teacher values('T112','SLICIA ZELAYA','MCA','16-SEP-04','ASSISTANT

PROFESSOR','VIJAYAWADA',9985202020,33000,'BTECH');
1 rowcreated.

SQL>insert into teachervalues('T113','JENNIFER WALLACE','MSC(MATHS)','25-OCT-

03','PROFESSOR','HYDERABAD',9902192033,54000,'MCA');
1 rowcreated.

SQL>insertintoteachervalues('T114','RAMESHNARAYANA','MCA','24-NOV-
04','ASSOCIATEPROFESSOR','NARASARAOPET',9988776655,34000,'MBA');
1 rowcreated.

DEPARTMENTOF IT
 DATABASEMANAGEMENTSYSTEMS

SQL>insertintoteachervalues('T115','JOYCEENGLISH','MBA','22-DEC-06','ASSISTANT
51
PROFESSOR','VIJAYAWADA',9998765443,45000,'MBA');
1 rowcreated.

Toshowthequeryexecutiontimingusethefollowingquery SQL> Set

timing on;

Retrievedetailsofteacherbeforecreationofindex.

SQL>select*fromteacher;

STAFSTAFF_NAMEQUALIFICATHIREDATEJOBADDRESSPH_NUMSALARYDEPARTMENT

T101SUNITHAMCA29-06-06ASSOCIATEPROFESSORVIJAYAWADA998506130823000MCA T102
FREDSMITH MTECH 07-MAR-03 ASSOCIATEPROFESSOR GUNTUR 9985063445 36000 MBA
T103JACK BARNES BTECH27-JUN-07PROFESSORTENALI998501234527000MTECH
T104 JANE DOE MCA04-JUL-06 ASSISTANT PROFESSORVIJAYAWADA 9985045678 29000 BTECH
T105JOE SHMOEMBA16-AUG-08ASSOCIATE PROFESSORELURU998765130836000MCA
T106JONBAKERMSC(COM)12-JAN-03PROFESSORHYDERABAD887656130846000MCA
T107JOHNDOEMSC(PHY)06-FEB-04ASSISTANTPROFESSORVIJAYAWADA834566130831000MBA T108KIM
SMITHMCA10-MAR-08ASSISTANT PROFESSORVIZAG837456130826000MTECH
T109MARYPARKERMTECH02-APR-09PROFESSORNELLORE789342764952000MBA
T110SAMUEL JOHNBTECH19-MAY-05ASSISTANT PROFESSORELURU998222220826000MBA
T111FRANKLIN WONGMBA20-AUG-06ASSOCIATE PROFESSORVIZAG998503333320000MTECH
T112SLICIAZELAYAMCA16-SEP-04ASSISTANTPROFESSORVIJAYAWADA998520202033000BTECH
T113JENNIFERWALLACEMSC(MATHS)25-OCT-03PROFESSORHYDERABAD990219203354000MCA
T114RAMESHNARAYANAMCA24-NOV-04ASSOCIATEPROFESSORNARASARAOPET998877665534000MBA
T115JOYCE ENGLISHMBA22-DEC-06ASSISTANT PROFESSORVIJAYAWADA999876544345000MBA

15rows selected.
Elaps[Link].24

Indexcreation:

DEPARTMENTOFIT
 DATABASEMANAGEMENTSYSTEMS

SQL>createindexteacher_job_indonteacher(job);
52
Indexcreated.
Elaps[Link].00

Retrievedetailsofteacheraftercreationof index.

SQL>select*fromteacher;

STAFSTAFF_NAMEQUALIFICATHIREDATEJOBADDRESSPH_NUMSALARYDEPARTMENT

T101SUNITHAMCA29-06-06ASSOCIATEPROFESSORVIJAYAWADA998506130823000MCA T102
FREDSMITH MTECH 07-MAR-03 ASSOCIATEPROFESSOR GUNTUR 9985063445 36000 MBA
T103JACK BARNES BTECH27-JUN-07PROFESSORTENALI998501234527000MTECH
T104 JANE DOE MCA04-JUL-06 ASSISTANT PROFESSORVIJAYAWADA 9985045678 29000 BTECH
T105JOE SHMOEMBA16-AUG-08ASSOCIATE PROFESSORELURU998765130836000MCA
T106JONBAKERMSC(COM)12-JAN-03PROFESSORHYDERABAD887656130846000MCA
T107JOHNDOEMSC(PHY)06-FEB-04ASSISTANTPROFESSORVIJAYAWADA834566130831000MBA T108KIM
SMITHMCA10-MAR-08ASSISTANT PROFESSORVIZAG837456130826000MTECH
T109MARYPARKERMTECH02-APR-09PROFESSORNELLORE789342764952000MBA
T110SAMUEL JOHNBTECH19-MAY-05ASSISTANT PROFESSORELURU998222220826000MBA
T111FRANKLIN WONGMBA20-AUG-06ASSOCIATE PROFESSORVIZAG998503333320000MTECH
T112SLICIAZELAYAMCA16-SEP-04ASSISTANTPROFESSORVIJAYAWADA998520202033000BTECH
T113JENNIFERWALLACEMSC(MATHS)25-OCT-03PROFESSORHYDERABAD990219203354000MCA
T114RAMESHNARAYANAMCA24-NOV-04ASSOCIATEPROFESSORNARASARAOPET998877665534000MBA
T115JOYCE ENGLISHMBA22-DEC-06ASSISTANT PROFESSORVIJAYAWADA999876544345000MBA

15rows selected.
Elaps[Link].13

DEPARTMENTOFIT