CS3CO39: Database Management System Laboratory Experiment no- 1

Experiment Title: Introduction to different databases software and Page 1 of 3

Installation of Various Databases.

1. Objective (s):
INTRODUCTION TO DIFFERENT DATABASES SOFTWARE
AND INSTALLATION OF VARIOUS DATABASES.

2. Theory:
1. Oracle Database:

Description: Oracle Database is a comprehensive, scalable, and secure relational

database management system.

Installation Steps:

1. Download Oracle Database:

 Visit the official Oracle website (https://www.oracle.com/database/).
 Choose the edition and version that suits your requirements.
 Download the installation file.
2. Install Oracle Database:
 Run the installer and follow the on-screen instructions.
 Choose installation options, including database edition, configuration,
and administrative settings.
 Create a new database or configure with an existing one during the
installation process.
3. Configuration:
 Set up database parameters like character set, memory allocation, and
administrator credentials.
 Configure the listener, which manages communication between clients
and the database.
4. Post-Installation Tasks:
 Verify the installation and test the connection to the Oracle Database.
 Optionally, install SQL*Plus or SQLcl for command-line interaction.

2. MySQL:

Description: MySQL is an open-source relational database management system

known for its speed, reliability, and ease of use.

Installation Steps:

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 1
Experiment Title: Introduction to different databases software and Page 2 of 3
Installation of Various Databases.

1. Download MySQL:
 Visit the official MySQL website (https://dev.mysql.com/downloads/).
 Choose the MySQL Community Server edition.
 Download the appropriate installer for your operating system.
2. Install MySQL:
 Run the installer and follow the installation wizard.
 Choose setup type, configure server options, and set the root password.
 Complete the installation process.
3. Configuration:
 Optionally, configure MySQL as a Windows service or start it
manually.
 Use MySQL Workbench or the command-line client to connect to the
server and perform initial configurations.
4. Post-Installation Tasks:
 Verify the installation by executing basic SQL commands.
 Secure MySQL by setting up user accounts, permissions, and
removing default accounts.

3. SQL Server:

Description: Microsoft SQL Server is a comprehensive relational database

management system with strong integration into the Microsoft ecosystem.

Installation Steps:

1. Download SQL Server:

 Visit the official Microsoft SQL Server download page
(https://www.microsoft.com/en-us/sql-server/sql-server-downloads).
 Choose the edition and version that suits your needs.
 Download the installer.
2. Install SQL Server:
 Run the installer and follow the installation wizard.
 Choose installation options, including feature selection and instance
configuration.
 Configure server authentication mode and set up SQL Server
administrators.
3. Configuration:
 Configure server and instance settings such as collation, authentication
mode, and data directories.
 Optionally, configure additional features like SQL Server Agent, Full-
Text Search, etc.
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 1
Experiment Title: Introduction to different databases software and Page 3 of 3
Installation of Various Databases.

4. Post-Installation Tasks:
 Verify the installation by connecting to the SQL Server using SQL
Server Management Studio (SSMS).
 Create databases, tables, and perform basic queries to test
functionality.

3. Software used:
Oracle Database (Recommended) or
MySQL
4. Solution: Installed Oracle 11g XE
5. Some Sample questions:
1. What is the primary purpose of a database management system
(DBMS)?
2. Can you name two examples of relational database management systems
(RDBMS) and briefly describe their characteristics?
3. What is the key difference between SQL and NoSQL databases?
4. What steps are typically involved in the installation of database software
on a computer?
5. Why is it important to consider the hardware and software requirements
before installing a database management system?

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 2
Experiment Title: Apply SQL SELECT statements on Page 1 of 4
“Employees” table.

1. Objective (s):
APPLY SQL SELECT STATEMENTS ON “EMPLOYEES” TABLE.
1. Select the content of the columns named "LastName" and "FirstName" from
the”Employees” table.
2. Create a query to display all the contents of table "Employees".
3. Select only the different (distinct) values of manager_id in a table.
4. Display the firstname concatenated with Lastname from table “Employees” with
column name NAME.
5. Select only the employee with the first name equal to "Steven" AND the last
name equal to "King":
6. Select only the employee with the first name equal to " David" OR the first
name equal to " Nancy":
7. Select only the employee with the first name equal to "David" AND the last
name equal to "Austin" OR to "Lee":
8. Select the complete information about employee whose manager id is 103.
9. Display information about employee whose salary is more than 12000.
10. Display information about employee whose salary is less than 10000.
11. Display information about employee whose salary is in between 10000 and
30000.
12. Display information about employee with first_name in lower case.
13. Display information about employee with last_name in upper case.
14. Select the complete information about employee whose manager id is 100 and
department_id is 90
15. Display the employee id, last_name, email, hire date ,salary. Order the query in
ascending order by Salary.
16. Display the employee id,first_name, email, hire date ,salary,manager id. Order
the query in descending order by manager id.
17. Create a query to display all the contents of table "Employees" hire date
between '13-jan 93' and '17-aug-94'.
18.Create a query to display hire date, first_name, email,job_id hire date between
21-SEP-89 and 05 FEB-98. Order the query in descending order by hire date
from employees table.

2. Theory:
SQL Basics: The structure queries language is a language that enable to create and
operate on relational database, which are sets of related information stored in tables.
SQL has clearly established itself as the standard relational database language.

PROCESSING CAPABILITY OF SQL:

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 2
Experiment Title: Apply SQL SELECT statements on Page 2 of 4
“Employees” table.

The various capability of SQL are:

1. DATA DEFINITION LANGUAGE(DDL):

The SQL DDL provides commands for defining relations schemas, deleting relations,
creating index and modifying relations schemas.

2. INTERACTIVE DATA MANIPULATION LANGUAGE(DML):

The SQL DML includes the queries language based on both the relational algebra and
the tuples relational calculas. It includes also command to insert, delete and modifying
in the database.

3. EMBEDDED DATA MANIPULATION LANGUAGE:

The embedded form of SQL is designed for use within general purpose programming
language such as pl/1, Cobol, Fortran, Pascal, and C.

4. VIEW DEFINITION:
The SQL DDL also includes commands for defining views

5. AUTHORIZATION:
The SQL DDL includes command for specifying access rights to relation and views.

6. INTEGRITY:
The SQL provides forms of integrity checking. Future products and standard of SQL are
likely to include enhanced features for integrity checking.

The basic structure of SQL expressions consists of

THREE CLAUSES:
• SELECT
• FROM
• WHERE
A typical SQL query has the form Select a, b, c, d.........
From a1, b1, c1.........
Where p;
1. SELECT CLAUSE:
select branch-name from loan; it will select all branch-name from the loan table

2. WHERE CLAUSE:
select loan-number from loan
where amount between 8000 and 9000
it will select all loan-number from loan where amount is between the 8000 and 9000

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 2
Experiment Title: Apply SQL SELECT statements on Page 3 of 4
“Employees” table.

3. FROM CLAUSE:
select customer-name,borrower.loan-number,amount from borrower, loan
where borrower.loan-no=loan.loan-no

RETRIEVING DATA:

(a) Retrieving all records:

SQL> select *from Gaurav;

(b) Retrieving specific columns:

SQL> select rollno,branch,name from Gaurav;

(c) Printing with user defined headings:

SQL> select rollno as rno, branch as stream, name as stdname from Vikas_kapoor;

3. Software used:
1. Oracle Database (Recommended) or
2. MySQL

4. Solution:
1. select last_name, first_name from Employees

2. Select *
from Employees

3. Select distinct manager_id from Employees

4. SELECT first_name||''||last_name as name from Employees

OR
SELECT concat(first_name,last_name)as name from Employees

5. Select first_name, last_name from employees

where first_name='Steven'and last_name='King'

6. Select first_name, last_name from employees

where first_name='David'or first_name='Nancy'

7. Select first_name, last_name from employees

where first_name='David'and last_name='Austin'or last_name='Lee'

8. Select *

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 2
Experiment Title: Apply SQL SELECT statements on Page 4 of 4
“Employees” table.

from employees
where manager_id=103

9. Select * from employees

where salary>12000

10. Select * from employees

where salary<10000
11. Select * from employees
where salary between 10000 and 30000

12. Select lower(first_name) from employees

13. Select upper(last_name) from employees

14. Select * from employees

where manager_id=100 and department_id=90

15. Select employee_id,last_name,email,hire_date,salary from employees

order by salary asc

16. Select employee_id,last_name,email,hire_date,salary,manager_id from

employees
order by manager_id desc

17. Select * from employees

where hire_date between '13-jan-93' and '17-Aug-94'

18. Select * from employees

where hire_date between '21-sep-89' and '5-feb-98' order by hire_date desc

5. Some Sample questions:

1. Explain the purpose of the SQL SELECT statement. How does it retrieve
data from a table?
2. What is the significance of the WHERE clause in a SELECT statement?
3. How is the ORDER BY clause used in a SELECT statement?
4. Explain the difference between the WHERE and HAVING clauses in
SQL.
5. How can you limit the number of rows returned in a SELECT statement?

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 1 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

1. Objective (s):
APPLY FOLLOWING COMPOUND CONDITION AND USE RELATIONAL OPERATORS (IN,
BETWEEN, LIKE, NULL, NOT NULL ETC) IN SQL STATEMENTS ON “EMPLOYEES” AND
“DEPARTMENT” TABLE.

1. Display the last name and salary for any employee whose salary is not in the range of 5,000 to
1,000?

2. Display the last name and department number of all employees in departments 60 or 100 in
ascending alphabetical order by name.

3. To display the last name and salary of employees who earn between 5,000 and 12,000 and are in
department 60 or 90. Label the columns Employee and Monthly Salary.

4. Create a report to display the last name, salary, and commission of all employees who
earn commissions. Sort data in descending order of salary and commissions.

5. Display the last name, job id, and salary for all employees whose job is IT-programmer or finance
manager and whose salary is not equal to 2,500, 3,500, or 7,000.

6. Display the last name, salary, and commission for all employees whose commission amount is
20%.

7. Display the employee number, last name, salary, and salary increased by 15.5% (expressed as a
whole number) for each employee. Label the column New Salary.

8. Modify above query to add a column that subtracts the old salary from the new salary. Label the
column Increase.

9. Create a query that produces the following for each employee: employee last name and
salary is 3 times. Label the column Dream Salaries.

10. Display the last name and manager id of those employees whose manager id is not 103.
11. Create a query to display the contents of those employees whose manager id is in the range of 100
and 103.
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 2 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

12. Create a query to display the contents of those employees whose commission pct
is null.

13. Create a query to display the contents of those employees whose manager
id is not null fromdepartment table.

14. Create a query to see a listing of all rows for which manager _id is not 200.

Aggregate functions

15. Display sum of salary of employees working in the department 60.

16. Compute the difference between minimum and maximum salary.

17. How many different job id are stored in the relation employees.

18. How many tuples are stored in the relation employees? Also find the no. of
records in manager_idattribute.

String matching queries

19. Display the content of the employee from employees table whose first name end
with letter n.

20. Display the content of the employee from employees table whose first name start
with letter A.

21. Display the content of the employee from employees table whose first name
start with A end withletter r.

22. Display the content of the employee from employees table whose first name
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 3 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

start with letter Aand it is 5 characters long.

23. Display the content of the employee from employees table whose first name has
substring ‘ar’.

24. Display the content of the employee from employees table whose first name end
with letter ‘ara’.

25. Display the content of the employee from department table whose first name
has substring ‘tr’and manager id is either 200 or 1700.

26. Display the content like employee_id,length of the

last_name,manager_id,department_id,and firstletter if the first name should be
capital.),hire_date from employees.

27. Display the last name, first name, manager id and email of those employees
whose email containsexactly one character appears between E and S.

28. Display all employee last names in which the third letter of the name is a.

29. Display the last name of all employees who have both an a and an e in their last
name.

30. Write a query that displays the last name (with the first letter uppercase and all
otherletters lowercase) and the length of the last name for all employees whose
name starts with the letters J, A, or M.. Sort the results by the employees’ last
names.

2. Theory:
2.1 SPECIAL OPERATORS
In / not in – used to select a equi from a specific set of values
Any - used to compare with a specific set of values
Between / not between – used to find between the ranges
Like / not like – used to do the pattern matching

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 4 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

2.2 USING LOGICAL OPERATORS (AND, OR, NOT)

SQL> select * from Gaurav where(branch='CSE' AND marks=729);

2.3 COMPUND CONDITIONS

SQL> select * from Gaurav where(branch='cse' OR marks>750);

2.4 USING BETWEEN AND

SQL> select * from Gaurav

where marks between 730 and 800;

2.5 Using IN Function SQL> select * from Gaurav where marks in(729,760,782);
2.6 USING LIKE OPERATOR

1). percent:

SQL> select * from Gaurav where name like '%h%';

2). underscore:

SQL> select * from Gaurav where marks like '8 ';

2.7 IS NULL

SQL> select * from Gaurav where stdid is null;

2.8 AGGREGATE FUNCTIONS / GROUP FUNCTIONS

A group function returns a result based on group of rows.
1. avg –
Example: select avg (total) from student;
2. max –
Example: select max (percentagel) from student;
3. min –
Example: select min (marksl) from student;
4. sum –
Example: select sum(price) from product;
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 5 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

COUNT FUNCTION
In order to count the number of rows, count function is used.
1. count(*) – It counts all, inclusive of duplicates and nulls.
Example: select count(*) from student;
2. count(col_name)– It avoids null value.
Example: select count(total) from order;
3. count (distinct col_name) – It avoids the repeated and null values.
Example: select count (distinct ordid) from order.

3. Software used:
1. Oracle Database (Recommended) or
2. MySQL

4. Solution:
String matching
1. select * from employees where first_name like'%n'
2. select * from employees where first_name like'A%'
3. select * from employees where first_name like'A%r'
4. select * from employees where first_name like'A_ '
5. select * from employees where first_name like'%ar%'
6. select * from employees where first_name like'%ara'

Selecting rows with conditional restrictions

a. <> or ! = Not equal to
b. Between =Used to check if an attribute is within a range
c. In = Used to check if an attribute value matches any value within a list
d. IS NULL / IS NOT NULL =Used to check if an attribute is NULL / is not
NULL

7. select * from employees where manager_id in (100,103)

8. select * from employees where commission_pct is null

(use departments table for below mentioned queries)

9. select * from departments where manager_id is not null;
10. select * from departments
where department_name like '%tr%' and manager_id=200 or manager_id=1700

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
5
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 6 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

11. If you want to see a listing of all rows for which manager _id is not 200, the
query would look like:
12. select * from departments where not(manager_id=200)

use table employees

aggregate functions
13. select count(manager_id) from employees

14. select count(distinct(manager_id)) from employees

15. select employee_id,first_name,last_name,(salary+salary*.1)as newsalary from

employees

16. select employee_id,last_name,manager_id from employees

where (manager_id=100 or manager_id=103)and department_id=60

17. select employee_id,last_name,manager_id,department_id,first_name,hire_date

from employees
order by department_id asc,hire_date desc

18. select
employee_id,length(last_name),manager_id,department_id,initcap(first_name),hire_dat
e from employees

19. select first_name,last_name,manager_id,job_id,email from employees

where email like '%E_S%'

20. To display the last name and salary for any employee whose salary is not in the
range of 5,000 to 1,000
select last_name,salary from employees
where salary not between 5000 and 10000

21. Display the last name and department number of all employees in departments
60 or 100 in ascending alphabetical order by name.
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
6
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 7 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

select last_name,department_id from employees

where department_id=60 or department_id=100 order by last_name asc

22. To display the last name and salary of employees who earn between 5,000 and
12,000 and are in department 60 or 90. Label the columns Employee and Monthly
Salary

select last_name as employee,salary as monthly_salary from employees

where (salary between 5000 and 12000) and (department_id=60 or department_id=90)

23. The employee department needs a report that displays the last name and hire
date for all employees who were hired in 1994.

24. Create a report to display the last name, salary, and commission of all
employees who earn commissions. Sort data in descending order of salary and
commissions.

select last_name,salary,commission_pct from employees

where commission_pct is not null
order by salary desc, commission_pct desc

String matching query use of like operator

25. Display all employee last names in which the third letter of the name is a.

select last_name from employees

where last_name like ' a%'

26. Display the last name of all employees who have both an a and an e in their last
name. select last_name
from employees
where last_name like '%a%e%'

27. Display the last name, job id, and salary for all employees whose job is IT-
programmer or finance manager and whose salary is not equal to 2,500, 3,500, or 7,000.

select last_name,job_id,salary from employees

Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
7
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 8 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

where (job_id='IT_PROG' or job_id='FI_MGR') and salary not in(2500,3500,7000)

28. display the last name, salary, and commission for all employees whose
commission amount is 20%.

select last_name,salary,commission_pct from employees

where commission_pct=.2

29. display the employee number, last name, salary, and salary increased by 15.5%
(expressed as a whole number) for each employee. Label the column New Salary.

select employee_id,last_name,round(salary+salary*.155) as new_salary from

employees

30. Modify above query to add a column that subtracts the old salary from the new
salary. Label the column Increase.

select employee_id,last_name,salary,(round(salary+salary*.155)-salary)as increase

from employees

31. Write a query that displays the last name (with the first letter uppercase and all
other letters lowercase) and the length of the last name for all employees whose name
starts with the letters J, A, or M.. Sort the results by the employees’ last names.

select initcap(last_name),length(last_name) from employees

where last_name like 'A%' or last_name like 'J%' or last_name like 'M%' order by
last_name desc
33. Find the length of employment for each employee. For each employee, display
the last name and calculate the number of months between today and the date on which
the employee was hired. Label the column MONTHS_WORKED.

SELECT last_name,hire_date,(trunc(sysdate) - hire_date)as months_worked FROM

employees;
(not sure about above query)

34. Create a query that produces the following for each employee: employee last
name and salary is 3 times. Label the column Dream Salaries.
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
8
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 9 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

SELECT last_name, (salary*3)as dream_salary FROM employees;

Aggregate functions

35. Display sum of salary of employees working in the department 60.

SELECT sum(salary)
FROM employees where department_id=60;

36. Compute the difference between minimum and maximum salary.

SELECT max(salary),min(salary), (max(salary)-min(salary)) FROM employees

37. how many different job id are stored in the relation employees.

SELECT count(distinct(job_id)) FROM employees

38. How many tuples are stored in the relation employees. Also find the no. of
records in manager_id attribute.

SELECT count(*),count(manager_id) FROM employees

39. display the last name and manager id of those employees whose manager id is
not 103. SELECT employee_id,last_name,manager_id
FROM employees
where not(manager_id=100)

40. (use table job_history)

SELECT employee_id,start_date,job_id FROM job_history
where (department_id=50 or department_id=100) and job_id='ST_CLERK'

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
9
CS3CO39: Database Management System Laboratory Experiment no- 3
Experiment Title: Apply Following Compound condition and Page 10 of 10
use relational operators (IN, BETWEEN, LIKE, NULL, NOT
NULL etc) in SQL statements on “Employees” and “Department”
table.

4. Some Sample questions:

1. Explain the purpose of the IN and BETWEEN operators in SQL.
2. How does the LIKE operator work in SQL?
3. When and how do you use the IS NULL and IS NOT NULL conditions
in SQL?
4. Discuss the usage of aggregate functions in SQL.
5. Explain the purpose of the GROUP BY clause in SQL.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
10
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 1 of 11
altering tables, deleting data.

1. Objective (s):
CREATING NEW TABLES, ADDING DATA, UPDATING DATA, ALTERING
TABLES, DELETING DATA.
2. Theory:
2.1 3.1 CREATING NEW TABLES
DDL COMMAND
It is used to communicate with database. DDL is used to: o Create an object o Alter the
structure of an object o To drop the object created.
The commands used are: Create, Alter, Drop, Truncate

SQL Commands:
CREATE TABLE
It is used to create a table
Syntax: Create table tablename (column_name1 data_ type constraints, column_name2
data_ type constraints …)
Example: Create table Emp ( EmpNo number(5), EName VarChar(15), Job Char(10)
constraint un unique, DeptNo number(3) CONSTRAINT FKey2 REFERENCES
DEPT(DeptNo));

Create table stud (sname varchar2(20) not null, rollno number(10) not null,dob date not
null);
Rules: 1. Oracle reserved words cannot be used.
3. Underscore, numerals, letters are allowed but not blank space.
3. Maximum length for the table name is 30 characters.
4. 2 different tables should not have same name.
5. We should specify a unique column name.
6. We should specify proper data type along with width.
7. We can include “not null” condition when needed. By default it is „null‟.

DML COMMAND
DML commands are the most frequently used SQL commands and is used to query and
manipulate the existing database objects. Some of the commands are Insert, Select,
Update, Delete

Insert Command: This is used to add one or more rows to a table. The values are
separated by commas and the data types char and date are enclosed in apostrophes. The
values must be entered in the same order as they are defined.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 2 of 11
altering tables, deleting data.

Select Commands: It is used to retrieve information from the table. it is generally

referred to as querying the table. We can either display all columns in a table or only
specify column from the table.

Update Command: It is used to alter the column values in a table. A single column may
be updated or more than one column could be updated.

Delete command: After inserting row in a table we can also delete them if required. The
delete command consists of a from clause followed by an optional where clause.

ALTER TABLE Alter command is used to:

1. Add a new column.
2. Modify the existing column definition.
3. To include or drop integrity constraint.

Syntax: alter table tablename add/modify (attribute datatype(size));

Example: 1. Alter table emp add (phone_no char (20));

2. Alter table emp modify(phone_no number (10));

3. ALTER TABLE EMP ADD CONSTRAINT Pkey1 PRIMARY KEY (EmpNo);

DROP TABLE

It will delete the table structure provided the table should be empty.

Example: drop table prog20; Here prog20 is table name

TRUNCATE TABLE If there is no further use of records stored in a table and the
structure has to be retained then the records alone can be deleted.
Syntax: TRUNCATE TABLE <TABLE NAME>;

Example: Truncate table stud;

3.2 INTEGRITY CONSTRAINT

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 3 of 11
altering tables, deleting data.

An integrity constraint is a mechanism used by oracle to prevent invalid data entry into
the table. It has enforcing the rules for the columns in a table. The types of the integrity
constraints are: a)
Domain Integrity b) Entity Integrity c) Referential Integrity

a) Domain Integrity This constraint sets a range and any violations that take place
will prevent the user from performing the manipulation that caused the breach. It
includes:

Not Null constraint: While creating tables, by default the rows can have null value .the
enforcement of not null constraint in a table ensure that the table contains values.
Principle of null values: o Setting null value is appropriate when the actual value is
unknown, or when a value would not be meaningful. o A null value is not equivalent to
a value of zero. o A null value will always evaluate to null in any expression. o When a
column name is defined as not null, that column becomes a mandatory i.e., the user has
to enter data into it. o Not null Integrity constraint cannot be defined using the alter
table command when the table contain rows.
Check Constraint: Check constraint can be defined to allow only a particular range of
values .when the manipulation violates this constraint, the record will be rejected.
Check condition cannot contain sub queries.

b) Entity Integrity Maintains uniqueness in a record. An entity represents a table

and each row of a table represents an instance of that entity. To identify each row in a
table uniquely we need to use this constraint.
There are 2 entity constraints:
Unique key constraint It is used to ensure that information in the column for each record
is unique, as with telephone or drivers license numbers. It prevents the duplication of
value with rows of a specified column in a set of column. A column defined with the
constraint can allow null value. If unique key constraint is defined in more than one
column i.e., combination of column cannot be specified. Maximum combination of
columns that a composite unique key can contain is 16.
Primary Key Constraint A primary key avoids duplication of rows and does not allow
null values. It can be defined on one or more columns in a table and is used to uniquely
identify each row in a table. These values should never be changed and should never be
null. A table should have only one primary key. If a primary key constraint is assigned
to more than one column or combination of column is said to be composite primary key,
which can contain 16 columns.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 4 of 11
altering tables, deleting data.

c) Referential Integrity It enforces relationship between tables. To establish parent-

child relationship between 2 tables having a common column definition, we make use
of this constraint. To implement this, we should define the column in the parent table as
primary key and same column in the child table as foreign key referring to the
corresponding parent entry.
Foreign key A column or combination of column included in the definition of
referential integrity, which would refer to a referenced key.
Referenced key It is a unique or primary key upon which is defined on a column
belonging to the parent table.

DOMAIN INTEGRITY
Example: Create table cust(custid number(6) not null, name char(10)); Alter table cust
modify (name not null);

CHECK CONSTRAINT
Example: Create table student (regno number (6), mark number (3) constraint b check
(mark >=0 and mark <=100)); Alter table student add constraint b2 check
(length(regno<=4));

ENTITY INTEGRITY
a) Unique key constraint
Example: Create table cust(custid number(6) constraint uni unique, name char(10));
Alter table cust add(constraint c unique(custid));
b) Primary Key Constraint
Example: Create table stud(regno number(6) constraint primary key, name char(20));

Queries:
Q1. Create a table called EMP with the following structure.

Name Type
---------- ----------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
DEPTNO NUMBER(3)
SAL NUMBER(7,2)
Allow NULL for all columns except ename and job. Solution:
1. Understand create table syntax.
2. Use the create table syntax to create the said tables.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 5 of 11
altering tables, deleting data.

3. Create primary key constraint for each table as understand from logical table
structure.

Ans: SQL> create table emp(empno number(6),ename varchar2(20)not null,job

varchar2(10) not null, deptno number(3),sal number(7,2));
Table created.

Q2: Add a column experience to the emp table. experience numeric null allowed.

Solution:
1. Learn alter table syntax.
2. Define the new column and its data type.
3. Use the alter table syntax.

Ans: SQL> alter table emp add(experience number(2)); Table altered.

Q3: Modify the column width of the job field of emp table.
Solution:
1. Use the alter table syntax.
2. Modify the column width and its data type.

Ans: SQL> alter table emp modify(job varchar2(12));

Table altered. SQL> alter table emp modify(job varchar(13)); Table altered.

Q4: Create dept table with the following structure.

Name Type
------------ --------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(10)
LOC VARCHAR2(10)
Deptno as the primarykey
Solution:
1. Understand create table syntax.
2. Decide the name of the table.
3. Decide the name of each column and its data type.
4. Use the create table syntax to create the said tables.
5. Create primary key constraint for each table as understand from logical table
structure.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
5
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 6 of 11
altering tables, deleting data.

Ans: SQL> create table dept(deptno number(2) primary key,dname varchar2(10),loc

varchar2(10));

Table created.

Q5: create the emp1 table with ename and empno, add constraints to check the empno
value while entering (i.e) empno > 100.
Solution:
1. Learn alter table syntax.
2. Define the new constraint [columns name type]
3. Use the alter table syntax for adding constraints.

Ans: SQL> create table emp1(ename varchar2(10),empno number(6)

constraint ch check(empno>100)); Table created.

Q6: drop a column experience to the emp table.

Solution:
1. Learn alter table syntax.
Use the alter table syntax to drop the column.
Ans: SQL> alter table emp drop column experience; Table altered.

Q7: Truncate the emp table and drop the dept table
Solution: 1. Learn drop, truncate table syntax.
Ans: SQL> truncate table emp; Table truncated.

SQL> drop table dept; Table dropped.

Thus the data definition language commands was performed and implemented
successfully

Creation of table with constraints:

SQL> create table Gaurav

( empid int constraint v1 primary key , epmnm char(20) constraint v2 unique,

desig char(20) default 'clerk', dept char(20) constraint v3 check(dept in('edp','fin')),

salary int constraint v4 not null); Table created.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
6
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 7 of 11
altering tables, deleting data.

3.3 ALTER TABLE

(a) Adding column : (ADD clause)

SQL> alter table Gaurav add (marks int);

Table altered.

(b) Adding multiple columns clause) :

SQL> alter table Gaurav

add (fav_sub char(20),stdid int); Table altered.
(c) Changing column width :(MODIFY clause)

SQL> alter table Gaurav modify branch char(10);

Table altered.

(d) Dropping column :(DROP clause)

SQL> alter table Gaurav drop column stdid;

Table altered

(e) Adding NOT NULL : (MODIFY clause)

SQL> alter table Gaurav modify (rollno int not null);

Table altered.

(f) Dropping NOT NULL : (DROP clause)

SQL> alter table Gaurav modify (rollno int not null);

Table altered

(g) Adding check constraint : (ADD clause)

SQL> alter table Gaurav

add constraint v11 check(branch in('it','csc')); Table altered.
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
7
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 8 of 11
altering tables, deleting data.

(h) Dropping check constraint : (DROP clause)

SQL> alter table Gaurav drop constraint v11;

Table altered.

(i) Adding Primary key :

SQL> alter table Gaurav

add constraint v11 primary key(name);

Table altered

(j) Removing Primary Key :

SQL> alter table Gaurav drop constraint v11;

Table altered.

(k) Dropping a primary key that have a dependent table:

SQL> alter table Gaurav drop primary key cascade;

Table altered.

(l) Adding Foreign Key :

SQL> alter table Gaurav

add constraint v11 foreign key(rollno) references employee;

Table altered.

(m) Dropping Foreign Key :

SQL> alter table Gaurav drop constraint v11;

Table altered.

3.4 DELETING RECORDS

Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
8
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 9 of 11
altering tables, deleting data.

(a) Delete (SINGLE record)

SQL> delete from Gaurav where rollno=7006;

1 row deleted.

(b) Delete (MULTIPLE record)

SQL> delete from Gaurav where branch='CSE';

rows deleted.

(c) Delete (ALL records) USING TRUNCATE

SQL> truncate table Gaurav; Table truncated.
SQL> select *from Gaurav; no rows selected
(d) Delete (ALL records) USING DELETE
SQL> delete from Gaurav; 1 rows deleted.
SQL> select *from Gaurav; no rows selected
(e) Deleting using sub query

SQL> delete from Gaurav

where rollno=(select rollno from Gaurav where name='Sudhir'); 1 row deleted.
DROPPING TABLE:
3.5 UPDATING TABLES(MODIFY)

(a) Updating without where clause:

SQL> update Gaurav set name='Goru';

4 rows updated.

(b) Updating with where clause:

SQL> update Gaurav

set name='dada' where rollno=6302; 1 row updated.
3. Software used:
1. Oracle Database (Recommended) or
2. MySQL
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
9
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 10 of 11
altering tables, deleting data.

4. Solution:
1. Create statement CREATE TABLE products ( prod_id NUMBER(4),
prod_name VARCHAR2(30), stock_qty NUMBER(15,3)
);

2. Insert command
insert into products values(02,'keyboard',15);

3. Crating a table from another table create table emp

as select* from employees;

4. create table empnew

as select employee_id,first_name, last_name,email from employees;

Modifying tables
5. ALTER TABLE products ADD price NUMBER;
6. insert into products(price) values(1000);

7. Update command update products

set price=1000
where prod_name='mouse';

8. delete command (delete specific rows)

delete from products where prod_name='mouse';

9. Add specification attribute to the table.

alter table products ADD specification VARCHAR(20);

10. add values into specification attributes. update products

set specification='hcl' where prod_name='mouse';

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
10
CS3CO39: Database Management System Laboratory Experiment no- 4
Experiment Title: Creating new tables, adding data, updating data, Page 11 of 11
altering tables, deleting data.

5. Some Sample questions:

1. How do you create a new table in a relational database?

2. Describe the process of adding new records to a table?
3. How do you update existing data in a table? Provide the basic syntax for the
UPDATE statement and illustrate its usage with an example.
4. Discuss the role of the ALTER TABLE statement in SQL. Provide an example
of using this statement to modify the structure of an existing table.
5. Explain the process of deleting data from a table using the DELETE statement.
Provide the basic syntax and a practical example.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
11
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 1 of 8

1. Objective (s):
NUMERIC FUNCTIONS IN SQL
1. Find the absolute values of -52,52

2. Find the Smallest integer greater than, or equal to 9.8, -32.85, 0, 5 using
Ceiling function.
3. Find the trigonometric cosine -3.14159
4. Find the exponential of the 1.
5. Find the largest integer greater than, or equal to 9.8, -32.85, 137 using Floor
function.
6. Find the natural logarithm of the 2.7
7. Find the logarithm of the 8 of 64, 3 of 27,
2 of 1024, 2 of 88. Find Modulus of (14,5),
(8,2.5), (-64,7)
9. Find Power of power(2,10), power(3,3),power(5,3),power(2,-3)
10. Find the round off values of these numbers up to given precision value
round of 12345 up to -2 precision, round of 12345.54321 up to 2
precision value.
11. Find the sign values of -2.3, 0, 47
12. Find the sin value of 1.57079 where this number in radians.
13. Find the tangent of 1.57079633/2 where this number in radians.
14. Find the truncate value of 123.456 to 2 digits of precision.
15. Find the Square root of 64, 49, 5
Date format conversion
16. Find the current date and time using SQL function.
17. Add 3,-2 number of months to current date
18. Extract the year, month and day from the current date.
19. Find the last day of the month for the date.
20. Find the months between (1)'19-dec-2002' and '19-mar-2003'
(2) '19-dec-2002','19-mar-2002'
2. Theory:
ORACLE FUNCTION
Function is a group of code that accepts zero or more arguments and both return
one or more results. Both are used to manipulate individual data items.
Operators differ from functional in that they follow the format of function name
(arg..). An argument is a user defined variables or constants. Most operators
accept at most 2 arguments while the structure of functions permit to accept 3 or
more arguments. Function can be classifies into single row function and group
functions.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 2 of 8

Single Row functions A single row function or scalar function returns only one
value for every row queries in table. Single row function can appear in a select
command and can also be included in a where clause. The single row function
can be broadly classified as, o Date Function o Numeric Function o Character
Function o Conversion Function o Miscellaneous Function The example that
follows mostly uses the symbol table “dual”. It is a table, which is automatically
created by oracle along with the data dictionary.

Date Function They operate on date values and produce outputs, which also
belong to date data type except for months, between, date function returns a
number.

Group Functions A group function returns a result based on group of rows

SQL Commands:
Date function
1. Add_month
This function returns a date after adding a specified date with specified number
of months.
Syntax: Add_months(d,n); where d-date n-number of months
Example: Select add_months(sysdate,2) from dual;
2. last_day
It displays the last date of that month.
Syntax: last_day (d); where d-date
Example: Select last_day („1-jun-2009‟) from dual;
3. Months_between
It gives the difference in number of months between d1 & d2.
Syntax: month_between (d1,d2); where d1 & d2 -dates

Example: Select month_between („1-jun-2009‟,‟1-aug-2009‟) from dual;

4. next_day
It returns a day followed the specified date.
Syntax: next_day (d,day);
Example: Select next_day (sysdate,‟wednesday‟) from dual
5. round
This function returns the date, which is rounded to the unit specified by the
format model.
Syntax : round (d,[fmt]); where d- date, [fmt] – optional. By default date will be
rounded to the nearest day

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 3 of 8

Example: Select round (to_date(„1-jun-2009‟,‟dd-mm-yy‟),‟year‟) from dual;

Select round („1-jun- 2009‟,‟year‟) from dual;

NUMERICAL
Command

FUNCTIONS
Query Output

Abs(n) Select abs(-15) from dual; 15

Ceil(n) Select ceil(55.67) from dual; 56
Exp(n) Select exp(4) from dual; 54.59
Floor(n) Select floor(100.2) from dual;100
Power(m,n) Select power(4,2) from dual; 16
Mod(m,n) Select mod(10,3) from dual; 1
Round(m,n) Select round(100.256,2) from 100.26
Trunc(m,n) dual; Select trunc(100.256,2) 100.23
Sqrt(m,n) from dual; Select sqrt(16) from 4
dual;

CHARACTER FUNCTIONS

Command Query Output

initcap(char); lower (char); upper (char); ltrim (char,[set]);
rtrim (char,[set]); replace (char,search string, replace string); select
initcap(“hello”) from dual; select lower („HELLO‟) from dual; select upper
(„hello‟) from dual; select ltrim („cseit‟, „cse‟) from dual; select rtrim („cseit‟,
„it‟) from dual; select replace(„jack and jue‟,„j‟,„bl‟) from dual; Hello hello
HELLO
it cse
black and blue

substr (char,m,n); select substr („information‟, 3, 4) from dual; Form

Conversion function

1. to_char()
Syntax: to_char(d,[format]);

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 4 of 8

This function converts date to a value of varchar type in a form specified by date
format. If format is negelected then it converts date to varchar2 in the default
date format.
Example: select to_char (sysdate, ‟dd-mm-yy‟) from dual;
2. to_date()
Syntax: to_date(d,[format]);
This function converts character to date data format specified in the form
character. Example: select to_date(„aug 15 2009‟,‟mm-dd-yy‟) from dual;
Miscellaneous Functions
1. uid – This function returns the integer value (id) corresponding to the
user currently logged in.
Example: select uid from dual;
2. user – This function returns the logins user name.
Example: select user from dual;
3. nvl – The null value function is mainly used in the case where we want
to consider null values as zero.
Syntax; nvl(exp1, exp2)

If exp1 is null, return exp2. If exp1 is not null, return exp1.

Example: select custid, shipdate, nvl(total,0) from order;

4. vsize: It returns the number of bytes in expression.

Example: select vsize(„tech‟) from dual;

Example Queries a). add_months:

SQL> select sysdate, add_months('9-april-2005',4) from dual;

SYSDATE ADD_MONTH

08-APR-05 09-AUG-05

b). last_day:
SQL> select sysdate,last_day('9-april-2005') from dual;

SYSDATE LAST_DAY

08-APR-05 30-APR-05
c). months_between:
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 5 of 8

SQL> select sysdate, months_between(sysdate,'02-nov-1985')from dual;

SYSDATE MONTHS_BETWEEN(SYSDATE,'02-NOV-1985')

08-APR-05 233.22555

d). next_day:
SQL> select sysdate,next_day('9-april-2005','monday')from dual;

SYSDATE NEXT_DAY(

08-APR-05 11-APR-05

e) Ceil:
SQL> select ceil(months_between(sysdate,'02-nov-1985'))from dual;

CEIL(MONTHS_BETWEEN(SYSDATE,'02-NOV-1985'))

234

f) Floor:
SQL> select floor(months_between(sysdate,'02-nov-1985'))from dual;

g) Mod:
SQL> select mod(10,7) from dual;

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

i) Sqrt:
SQL> select sqrt(10) from dual;

j) Abs:
SQL> select abs(10)from dual;

4.2 ORDERING RECORDS

a). ascending:
SQL> select * from Gaurav order by name asc;

b). descending:
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
5
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 6 of 8

SQL> select * from Gaurav order by name desc;

c). concat:
SQL> select name || ',' || branch from Gaurav;

d). initcap:
SQL> select initcap(name) from Gaurav;

e). lower:
SQL> select lower(name) from Gaurav;

f). upper:
SQL> select upper(name) from Gaurav;
3. Software used:
Oracle Database (Recommended) or
MySQL
4. Solution:
Numeric Functions
1. ABS
select abs(-52) negative,abs(52) positive from dual;
2. Ceil
select ceil(9.8),ceil(-32.85),ceil(0),ceil(5) from dual;
3. cos
select cos(-3.14159) from dual;
4. EXP
select exp(1) "e" from dual;
5. floor
select floor(9.8),floor(-32.85),floor(137) from dual;
6. LN
select ln(2.7) from dual;
7. LOG
select log(8,64),log(3,27),log(2,1024),log(2,8) from dual;
8. Mod
select mod(14,5),mod(8,2.5),mod(-64,7) from dual;
9. power
select power(2,10), power(3,3),power(5,3),power(2,-3) from dual;
10. round
select round(12345,-2) test1,round(12345.54321,2) test2 from dual;
11. sign
select sign(-2.3),sign(0),sign(47) from dual;
12. sin

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
6
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 7 of 8

select sin(1.57079) from dual;

13. select sqrt(64),sqrt(49),sqrt(5) from dual;

14. tan
select tan(1.57079633/2) "45_degrees" from dual;
15. trunk
select trunc(123.456,2) pos, trunc(123.456,-1) neg
from dual;

Date format conversion

1. ADD_months
select sysdate,ADD_months(sysdate,3) plus_3,ADD_months(sysdate,-2)
minus_2 from dual;
2. Current_date
select sysdate,current_date,sessiontimezone from dual;
3. Current_timestamp
select sysdate,current_timestamp,sessiontimezone from dual;
4. extract year,month and day from date
select sysdate,extract(year from sysdate) year,extract(month from
sysdate) month,extract(timezone_hour from systimestamp)
TZH,extract(day from sysdate) day
from dual;
5. last_day
select sysdate,last_day(sysdate) end_of_month,last_day(sysdate)+1 next_month
from dual;
6. months_between
select months_between('19-dec-2002','19-mar-2003')
test1,months_between('19-dec-2002','19- mar-2002') test2
from dual;
7. next_day
select next_day('01-jan-2014','monday') "1st monday",next_day('01-feb-
2014','tuesday')+7 "2nd tuesday"
from dual;
5. Some Sample questions:
1. What is the purpose of the SQL ABS() function, and how is it used? Provide
an example.
2. Explain the usage of the SQL ROUND() function. When would you use it,
and what does it do?

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
7
CS3CO39: Database Management System Laboratory Experiment no- 5
Experiment Title: Numeric Functions in SQL Page 8 of 8

3. Explain the usage of the SQL ROUND() function. When would you use it,
and what does it do?
4. Explain the SQL SQRT() function. When would you use it, and what does it
return?
5. What is the purpose of the SQL POWER() function, and how is it used?
Provide an example.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
8
CS3CO39: Database Management System Laboratory Experiment no- 6
Experiment Title: Sub-Queries Page 1 of 4

1. Objective (s):
SUB-QUERIES
Single-Row Sub-queries
1. Find the last_ name, first _name, salary of the employee with the highest
salary from employeestable.
2. Find the names and salary of the employees who work in the Accounting
department from employees table.
Multiple row Sub-queries
3. Lists all of the employees who work for the same departments as John does from
employees table.
4. Find the highest - paid employee of each department from employees table.
5. Lists the names of employees who work with John (same department) using
EXISTS operator fromemployees table.
Scalar Sub-queries
6. List the city name, country code, and if the city is in India, use CASE
expression with a sub-query to return the country code for India from the
COUNTRIES table.
7. Find the employee name, department, and the highest salary in that
department whose last_name start with letter R.
8. Find the department names and their manager names for all departments that
are in United States or Canada from EMPLOYEES, DEPARTMENTS and
COUNTRIES table.
9. Find the country id, city, state province from location table also sorts the
city names by theircountry name order.
Sub queries in other DML statements
10. Update the salary of employees to the maximum salary in the corresponding
department of theEMPLOYEES table.
11. Delete the records of employees whose salary is below the average salary in
the department ofEMPLOYEES table.
12. Insert record to the table employee _archive using Subquery.
13. Specify a Subquery in the VALUES clause of the INSERT statement.
14. Delete records of those employees whose department_id is 10 from
employees and filter fromSubquery, whose department_id<20.

2. Theory:
NESTED QUERIES
Nested Queries: Nesting of queries one within another is known as a nested
query.
Sub Queries: The query within another is known as a sub query. A statement
containing sub query is called parent statement. The rows returned by sub query
are used by the parent statement.
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 6
Experiment Title: Sub-Queries Page 2 of 4

Types
1. Sub queries that return several values Sub queries can also return more
than one value. Such results should be made use along with the operators in and
any.
2. Multiple queries Here more than one sub query is used. These multiple
sub queries are combined by means of „and‟ & „or‟ keywords
3. Correlated sub query A sub query is evaluated once for the entire parent
statement whereas a correlated Sub query is evaluated once per row processed
by the parent statement.

Nested Queries:
Example: select ename, eno, address where salary >(select salary from employee
where ename
=‟jones‟);
1. Subqueries that return several values
Example: select ename, eno, from employee where salary <any (select salary
from employee where deptno =10‟);
2. Correlated subquery
Example: select * from emp x where x.salary > (select avg(salary) from emp
where deptno
=x.deptno);

Sample Queries:
Q1: Display all employee names and salary whose salary is greater than
minimum salary of the company and job title starts with ‗M‘.
Solution: 1. Use select from clause. 2. Use like operator to match job and in
select clause to get the result.
Ans: SQL> select ename,sal from emp where sal>(select min(sal) from emp
where job like 'A%');

Q2: Issue a query to display information about employees who earn more than
any employee in dept 1.
Ans: SQL> select * from emp where sal>(select max(sal) from emp where
empno=1);
3. Software used:
Oracle Database (Recommended) or
MySQL
4. Solution:
1. select last_name,first_name,salary from employees
where salary=(select max(salary) from employees);

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 6
Experiment Title: Sub-Queries Page 3 of 4

2. select last_name,first_name,salary from employees

where department_id=(select department_id from departments where
department_name='Accounting');

3. select last_name,first_name,department_id from employees

where department_id in(select department_id
from employees where first_name='John');

4. select last_name,department_id,salary from employees e1

where salary=(select max(salary) from employees e2
where e1.department_id=e2.department_id) order by department_id;

5. select last_name,first_name,department_id from employees e1

where exists(select * from employees e2
where first_name='John' AND e1.department_id=e2.department_id);

6. select city,country_id,(case
when country_id IN(select country_id from countries
where country_name='India') Then 'Indian'
Else 'Non-Indian' END) "India?"
from locations where city like 'B%';

7. select last_name,department_id, (select max(salary)

from employees sq
where sq.department_id=e.department_id) HSAL from employees e
where last_name like 'R%'

8. select department_name,manager_id, (select last_name from employees e

where e.employee_id=d.manager_id) mgr_name from departments d
where ((Select country_id from locations l where d.location_id=l.location_id)
in(select country_id from countries c
where c.country_name='United States of America' or c.country_name='Canada'))
And d.manager_id is Not Null;

9. select country_id,city,state_province from locations l

order by (select country_name from countries c where l.country_id=c.country_id);

Subqueries in other DML statements

10. update employees e1
set salary=(select max(salary) from employees e2
where e1.department_id=e2.department_id);
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 6
Experiment Title: Sub-Queries Page 4 of 4

11. delete from employees e

where salary<(select avg(salary)from employees where
department_id=e.department_id);

12. insert into employee_archive select * from employees;

13. insert into departments (department_id,department_name) values ((select

max(department_id)+10 from departments),'EDP');

14. insert into (select department_id,department_name from departments where

department_id<20) values(35,'marketing');

5. Some Sample questions:

1. What is a Single-Row Sub-query in SQL? Provide an example of how it can
be used in a SELECT statement.
2. Explain the concept of Multiple-Row Sub-queries. How are they different
from Single-Row Sub-queries? Provide an example.
3. What is a Scalar Sub-query in SQL? Give an example of using a Scalar Sub-
query in a SELECT statement.
4. How can Sub-queries be incorporated into other DML statements like
INSERT, UPDATE, or DELETE? Provide an example for either INSERT or
UPDATE.
5. Define and explain the concept of Nested Queries in SQL. Provide an
example of a nested query within another query.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 1 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

1. Objective (s):

PERFORM OPERATIONS LIKE NATURAL JOIN, EQUIJOIN, LEFT

OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN,

INTERSECTION, UNION, UNION ALL AND MINUS ON GIVEN

RELATIONS.

employee (employee_name, street,city)

ftworks (employee_name, branch_name,salary)

2. Theory:
Relating Data through Join Concept

The purpose of a join concept is to combine data spread across tables. A join is
actually performed by the „where‟ clause which combines specified rows of
tables.

Syntax; select columns from table1, table2 where logical expression;

Types of Joins 1. Simple Join 2. Self Join 3. Outer Join 4. Inner Join

1. Simple Join
a) Equi-join: A join, which is based on equalities, is called equi-join.
b) Non Equi-join: It specifies the relationship between

Self join: Joining of a table to itself is known as self-join. It joins one row in a
table to another. It can compare each row of the table to itself and also with
other rows of the same table

Outer Join: It extends the result of a simple join. An outer join returns all the
rows returned by simple join as well as those rows from one table that do not
match any row from the table. The symbol (+) represents outer join.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 2 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

Inner join: Inner join returns the matching rows from the tables that are being
joined

Simple Join

a) Equi-join Example: select * from item, cust where item.id=cust.id;

b) Non Equi-join Example: select * from item, cust where item.id<cust.id;

Self join Example: select * from emp x ,emp y where x.salary >= (select
avg(salary) from x.emp where x. deptno =y.deptno);
Outer Join Example: select ename, job, dname from emp, dept where
emp.deptno (+) = dept.deptno;

PERFORMING JOINS
Tables used

SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 2 20000

SQL> select * from dept;

DEPTNO DNAME LOC

1 ACCOUNTING NEW YORK

2 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

EQUI-JOIN

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 3 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

Q1: Display the employee details, departments that the departments are same in
both the emp and dept.
Solution: 1. Use select from clause. 2. Use equi join in select clause to get the
result.
Ans: SQL> select * from emp,dept where emp.deptno=dept.deptno;

EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

- - -
1 Mathi AP 1 10000 1 ACCOUNTING NEW YORK
2 Arjun ASP 2 12000 2 RESEARCH DALLAS
3 Gugan ASP 2 20000 2 RESEARCH DALLAS
4 Karthik AP 1 15000 1 ACCOUNTING NEW YORK

LEFTOUT-JOIN
Tables used

SQL> select * from stud1;

Regno Name Mark2 Mark3 Result

101 john 89 80 pass
102 Raja 70 80 pass
103 Sharin 70 90 pass
104 sam 90 95 pass
Q3: Display the Student name and grade by implementing a left outer join.

Ans: SQL> select stud1.name,grade from stud1 left outer join stud2 on
stud1.name=stud2.name;

RIGHTOUTER-JOIN
Q4: Display the Student name, register no, and result by implementing a right
outer join.
Ans: SQL> select stud1.name, regno, result from stud1 right outer join stud2 on
stud1.name = stud2.name;
Name Regno Result
John 101 pass
raj 102 pass
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 4 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

sam 103 pass

sharin 104 pass

Rollno Name Mark1 Mark2 Total

1 sindu 90 95 185
2 arul 90 90 180
FULLOUTER-JOIN
Q5: Display the Student name register no by implementing a full outer join.
Ans: SQL> select stud1.name, regno from stud1 full outer join stud2 on
(stud1.name= stud2.name);

Name Regno

john 101
raj 102
sam 103
sharing 104

SELFJOIN
Q6: Write a query to display their employee names
Ans: SQL> select distinct ename from emp x, dept y where x.deptno=y.deptno;
ENAME

Arjun Gugan Karthik Mathi

Q7: Display the details of those who draw the salary greater than the average
salary. Ans: SQL> select distinct * from emp x where x.sal >= (select avg(sal)
from emp);

Thus the nested Queries and join Queries was performed successfully and
executed.

Elimination of duplicates from the select clause

It prevents retrieving the duplicated values .Distinct keyword is to be used.

Syntax: Select DISTINCT col1, col2 from table name;

Example: Select DISTINCT job from emp;

Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 5 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

Query: Display deptno from the table employee avoiding the duplicated values.

Solution: 1. Use SELECT FROM syntax.

Select should include distinct clause for the deptno. Ans: SQL> select distinct
deptno from emp;

DEPTNO
12

TABLE ALIASES (ALIASES & Sub Queries)

Table aliases are used to make multiple table queries shorted and more readable.
We give an alias name to the table in the „from‟ clause and use it instead of the
name throughout the query.

Q.1)Write a query to display their employee names Ans: SQL> select distinct
ename from emp x, dept y where x.deptno=y.deptno;

ENAME

Arjun Gugan Karthik Mathi

Q2: Display the details of those who draw the salary greater than the average
salary. Ans: SQL> select distinct * from emp x where x.sal >= (select avg(sal)
from emp);

EMPNO ENAME JOB DEPTNO SAL

3 Gugan ASP 2 20000

4 Karthik AP 1 15000
11 kavitha designer 12 17000

GROUP BY CLAUSE And HAVING CLAUSE

GROUP BY CLAUSE
This allows us to use simultaneous column name and group functions.

Example: Select max(percentage), deptname from student group by deptname;

Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
5
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 6 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

HAVING CLAUSE This is used to specify conditions on rows retrieved by

using group by clause.

Example: Select max(percentage), deptname from student group by deptname

having count(*)>=50;

GROUP BY clause:
SQL> select sum(marks) from Gaurav group by stdid;

SUM(MARKS)

2322
1511

HAVING clause:
SQL> select sum(marks) from Gaurav group by stdid
having sum(marks)>2000;

SUM(MARKS)

2322

7.2 ALL CLAUSE

SQL> select all name from Gaurav;

3. Software used:
Oracle Database (Recommended) or
MySQL
4. Solution:
1. Natural join
Select * from employe
natural join
ftworks

2. equijoin
Select * from employe

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
6
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 7 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

join
ftworks using(employee_name);

3. Left outer join

Select e.employee_name,e.street,e.city,f.branch_name,f.salary from employe
e,ftworks f
where e.employee_name=f.employee_name(+);

4. right outer join

Select e.employee_name,e.street,e.city,f.branch_name,f.salary from employe
e,ftworks f
where e.employee_name(+)=f.employee_name;

5. full outer join

Select e.employee_name,e.street,e.city,f.branch_name,f.salary from employe e
FULL OUTER JOIN ftworks f
ON e.employee_name=f.employee_name;

6. Union All
Select employee_name from employe where employee_name like 'A%'
UNION all
select employee_name from ftworks where salary>=50000

7. union
Select employee_name from employe where employee_name like 'A%'
UNION
select employee_name from ftworks where salary>=50000

8. intersect
Select employee_name from employe where employee_name like 'A%'
intersect
select employee_name from ftworks where salary>=50000

9. minus
Select employee_name from employe
minus
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
7
CS3CO39: Database Management System Laboratory Experiment no- 7
Experiment Title: Perform operations like Natural Join, equijoin, Page 8 of 8
left outer join, right outer join, full outer join, intersection, union,
union all and minus on given relations.

select employee_name from ftworks

5. Some Sample questions:

1. Create two tables "employees" and "departments" with appropriate
attributes. Perform a NATURAL JOIN to retrieve information about
employees and their respective departments.
2. Extend the scenario with a third table "salaries" containing attributes
(employee_id, salary). Perform an EQUIJOIN to retrieve employee names
and their salaries.
3. Create tables "students" (student_id, student_name) and "courses"
(course_id, course_name). Perform a LEFT OUTER JOIN to retrieve all
students and their enrolled courses.
4. Extend the scenario with a third table "grades" (student_id, course_id,
grade). Perform a RIGHT OUTER JOIN to retrieve all courses and the
grades associated with them.
5. Create two tables "sales_2022" and "sales_2023" with attributes (product_id,
quantity). Perform a FULL OUTER JOIN to retrieve all product sales
information from both years.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
8
CS3CO39: Database Management System Laboratory Experiment no- 8
Experiment Title: Creating views, modifying views, dropping Page 1 of 5
views, inserting and updating data using views.

1. Objective (s):
CREATING VIEWS, MODIFYING VIEWS, DROPPING VIEWS,
INSERTING AND UPDATING DATA USING VIEWS.
1. Create a view admin_employees with attribute like first_name and
last_name concatenated as name,email, job_id with alias name position of those
employee whose department_id is 10. Use the base table employees.
2. Create a view emp_hire using defined column names employee_id,
employee_name, department_name, hire_date, commission_amt. Use base tables
employees and departments.
3. create a read only views all_locations with attribute like country_id,
country_name, location_id, city. Use the base tables locations and countries
using natural join operation.
4. Modifying views.
5. Dropping an existing view.
6. Using views in queries
Find the total commission paid for each department from emp_hire view for all
commission above
$100.
7. Inserting data through views in base table.
2. Theory:
Views:
A view is the tailored presentation of data contained in one or more table and
can also be said as restricted view to the data‟s in the tables. A view is a “virtual
table” or a “stored query” which takes the output of a query and treats it as a
table. The table upon which a view is created is called as base table.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 8
Experiment Title: Creating views, modifying views, dropping Page 2 of 5
views, inserting and updating data using views.

A view is a logical table based on a table or another view. A view contains no

data of its own but is like a window through which data from tables can be
viewed or changed. The tables on which a view is based are called base tables.
The view is stored as a SELECT statement in the data dictionary

Advantages of a view: a. Additional level of table security. b. Hides data

complexity. c. Simplifies the usage by combining multiple tables into a single
table. d. Provides data‟s in different perspective.

Types of view:
Horizontal -> enforced by where cause
Vertical -> enforced by selecting the required columns

SQL Commands
Creating and dropping view:
Syntax:
Create [or replace] view <view name> [column alias names] as <query> [with
<options> conditions]; Drop view <view name>;
Example: Create or replace view empview as select * from emp; Drop view
empview;
Queries:
Tables used: SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 2 20000
4 Karthik AP 1 15000

Q1: The organization wants to display only the details of the employees those
who are ASP. (Horizontal portioning)
Solution:
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 8
Experiment Title: Creating views, modifying views, dropping Page 3 of 5
views, inserting and updating data using views.

1. Create a view on emp table named managers

2. Use select from clause to do horizontal partioning
Ans: SQL> create view empview as select * from emp where job='ASP';

View created.
SQL> select * from empview;

EMPNO ENAME JOB DEPTNO SAL

- -
2 Arjun ASP 2 12000
3 Gugan ASP 2 20000

Q2: The organization wants to display only the details like empno, empname,
deptno, deptname of the employees. (Vertical portioning)
Solution:
1. Create a view on emp table named general
2. Use select from clause to do vertical partioning
Ans: SQL> create view empview1 as select ename,sal from emp; View created.
Q3: Execute the DML commands on the view created. Ans: SQL> select * from
empview;

EMPNO ENAME JOB DEPTNO SAL

2 Arjun ASP 2 12000
3 Gugan ASP 2 20000

Q4: Drop a view.

Ans: SQL> drop view empview1;
View dropped.

Thus the creation and manipulation of various database objects of the Table
using views was successfully executed.

3. Software used:
Oracle Database (Recommended) or

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 8
Experiment Title: Creating views, modifying views, dropping Page 4 of 5
views, inserting and updating data using views.

MySQL
4. Solution:
Creating Views

1. create view admin_employees AS

select first_name||last_name Name,email,job_id POSITION from employees
where department_id=10;
2. Use of describe
describe admin_employees
3. using defined column names
create view emp_hire
(employee_id,employee_name,department_name,hire_date,commission_amt)
AS select
employee_id,first_name||last_name,department_name,TO_CHAR(hire_d
ate,'DD-MM- YYYY'),salary*nvl(commission_pct,.5) from employees JOIn
departments USING(department_id)

Creating read only views

Such views can be used only in queries ,no DML operations can be performed
on the view.

create view all_locations

as select country_id,country_name,location_id,city from locations natural join
countries
with read only;

Creating constraints on views

Constraints on views are not enforced –they are declarative constraints. To
enforce constraints you must define them on the base tables.
create view emp_details
(employee_no constraint fk_employee_no references employees disable
novalidate,manager_no, phone_number Constraint uq_email unique disable
novalidate,
constraint fk_manager_no foreign key(manager_no) references employees
disable novalidate) as select employee_id,manager_id,phone_number

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 8
Experiment Title: Creating views, modifying views, dropping Page 5 of 5
views, inserting and updating data using views.

from employees where department_id=40

Modifying views
To change the definition of the view,use the create view with the or replace
option. The alter view statement can be used to compile an invalid view or to
add and drop constraints.
create or replace view admin_employees AS
select first_name||' '||last_name Name,email,job_id POSITION from employees
where department_id=10;

Dropping a view drop view test_views; using views in queries

1. select department_name,sum(commission_amt) comm_amt from
emp_hire where commission_amt>100
group by department_name;

inserting data through views in base table create view

2. create view dept_above_250
as select department_id did,department_name from departments
where department_id>250;
3. insert data through views in base table insert into dept_above_250
values(199,'temporary dept');
now check the inserted value into the base table departments with this command
select * from departments where department_id=199;
5. Some Sample questions:
1. What is a database view, and why might you choose to create one?
2. Create a view named "monthly_sales" that displays the product_id,
product_name, and total sales for each product from the "sales" table.
3. Modify the "monthly_sales" view to include only products with sales
exceeding 100 units. Explain the process of altering a view.
4. Drop the "monthly_sales" view from the database. What considerations
should be taken into account before dropping a view?
5. Create an updatable view named "employee_salaries" that displays
employee_id, employee_name, and salary from the "employees" table.
Demonstrate how to update the salary of a specific employee using this
view.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
5
CS3CO39: Database Management System Laboratory Experiment no- 9
Experiment Title: Introduction to PL/SQL Page 1 of 4

1. Objective (s):
INTRODUCTION TO PL/SQL

2. Theory:
The objective of this lab practical is to provide students with hands-on
experience in writing and executing PL/SQL (Procedural Language/Structured
Query Language) code. Students will gain practical knowledge in creating and
managing PL/SQL programs, procedures, functions, and triggers.

Overview of PL/SQL:
PL/SQL (Procedural Language/Structured Query Language) is a powerful
programming language designed specifically for seamless integration with the
Oracle Database. It extends the capabilities of SQL by adding procedural
constructs found in procedural programming languages. PL/SQL allows
developers to create robust and efficient database applications.

Role in Oracle Database:

PL/SQL plays a pivotal role in Oracle databases by enabling the creation of
stored procedures, functions, triggers, and anonymous blocks. It allows for the
encapsulation of business logic, data manipulation, and transaction control
within the database itself. This results in improved performance, maintainability,
and security of database applications.

PL/SQL Block Structure:

A PL/SQL block is the basic unit of code in PL/SQL and consists of three
sections: Declaration, Execution, and Exception Handling.

Declaration Section:

Defines variables, constants, cursors, and other programmatic elements.

Example:

DECLARE
v_employee_name VARCHAR2(50);
v_salary NUMBER;

Execution Section:
 Contains the actual executable code.
 Consists of SQL and PL/SQL statements.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 9
Experiment Title: Introduction to PL/SQL Page 2 of 4

Example:
BEGIN
SELECT employee_name, salary INTO v_employee_name, v_salary FROM
employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name || ',
Salary: ' || v_salary);
END;

Exception Handling Section:

 Catches and handles errors that may occur during execution.
 Ensures graceful error management and recovery.
Example:

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

Key Features of PL/SQL:

Variables:

 PL/SQL supports variables for storing and manipulating data.

 Variables can be of various data types such as VARCHAR2, NUMBER,
DATE, etc.
Example:
v_employee_id NUMBER := 101;

Control Structures:

 PL/SQL provides control structures like IF-THEN-ELSE, CASE, and

LOOP.
 Enables conditional and iterative programming.
Example:

IF v_salary > 50000 THEN

DBMS_OUTPUT.PUT_LINE('High salary employee.');
ELSE
DBMS_OUTPUT.PUT_LINE('Normal salary employee.');
END IF;
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 9
Experiment Title: Introduction to PL/SQL Page 3 of 4

Exception Handling:

 Allows handling of exceptions using the EXCEPTION section.

 Provides specific handlers for different types of errors.
Example:
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
3. Software used:
Oracle Database (Recommended) or
MySQL
4. Solution:
Write following programs in PL/SQL:
1. Print ‘Hello World’ in PL/SQL.
2. learn how to declare a character type variable.
3. Insert data to a table using character type variable.
4. Update the data value of a table.
5. Delete the data value of a table.
Consider the below table for performing DML operations using PL/SQL.
Create the table EMP to see how to perform DML operations in PL/SQL. We
are going to insert the below four records into emp table.

EMP_NAME EMP_NO SALARY MANAGER

BBB 1000 25000 AAA
XXX 1001 10000 BBB
YYY 1002 10000 BBB
ZZZ 1003 7500 BBB
a) Then update the salary of ‘XXX’ to 15000, and
b) delete the employee record ‘ZZZ’.
c) project the details of the employee ‘XXX’.

Solution
DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER;
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 9
Experiment Title: Introduction to PL/SQL Page 4 of 4

l_manager VARCHAR2(250);
BEGIN
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('BBB',1000,25000,'AAA');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('ZZZ',1003,7500,'BBB');
COMMIT;
Dbms_output.put_line('Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line('Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT;
Dbms_output.put_line('Values Deleted');
SELECT emp_name,emp_no,salary,manager INTO
l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE
emp_name='XXX';

Dbms_output.put_line('Employee Detail');
Dbms_output.put_line('Employee Name:'||l_emp_name);
Dbms_output.put_line('Employee Number:'||l_emp_no);
Dbms_output.put_line('Employee Salary:'||l_salary);
Dbms_output.put_line('Employee Manager Name:'||l_manager);
END;

5. Some Sample questions:

1. What is PL/SQL, and how does it differ from SQL?
2. Describe the basic structure of a PL/SQL block.
3. What are the advantages of using PL/SQL in database programming?
4. Explain the concept of variables in PL/SQL. How are they declared and
used?
5. What is exception handling in PL/SQL, and why is it important?

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 10
Experiment Title: Introduction to PL/SQL Triggers and Cursors Page 1 of 5

1. Objective (s):
INTRODUCTION TO PL/SQL TRIGGERS AND CURSORS
2. Theory:
Introduction to TRIGGERS in DBMS:
:A trigger in PL/SQL is a set of instructions that are automatically executed
("triggered") in response to certain events on a particular table or view. These
events can include data manipulation language (DML) events such as INSERT,
UPDATE, DELETE, and database operation events like database startup or
shutdown. Triggers allow developers to enforce business rules, perform
auditing, and automate tasks within the database.

Types of Triggers:
 DML Triggers:

Fired in response to DML (Data Manipulation Language) operations such as

INSERT, UPDATE, and DELETE.
Can be defined at the statement or row level.
Examples:
BEFORE INSERT: Triggered before a new row is inserted.
AFTER UPDATE: Triggered after a row is updated.

 Instead of Triggers:

Used to replace the action of an INSERT, UPDATE, or DELETE statement on a

view.
Allows custom processing or validation before the actual modification of data.
Commonly used with complex views or when dealing with data from multiple
tables.

 System Triggers:

Respond to database events such as database startup, shutdown, or DDL (Data

Definition Language) changes.
Examples:
AFTER STARTUP: Triggered after the database is started.
BEFORE CREATE: Triggered before a new object is created.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
1
CS3CO39: Database Management System Laboratory Experiment no- 10
Experiment Title: Introduction to PL/SQL Triggers and Cursors Page 2 of 5

Structure of a PL/SQL Trigger:

A PL/SQL trigger consists of the following components:

Trigger Name:
A unique identifier for the trigger within the database.

Trigger Timing:
Indicates when the trigger is fired in relation to the triggering event.
BEFORE - Triggered before the triggering event.
AFTER - Triggered after the triggering event.

Trigger Event:
Specifies the event that causes the trigger to fire (e.g., INSERT, UPDATE,
DELETE).

Trigger Body:
 Contains the PL/SQL code to be executed when the trigger is fired.
 Can include conditional statements, SQL queries, and procedure calls.

Introduction to Cursors in DBMS:

A cursor in database management systems (DBMS) is a database object that is
used to traverse the results of a query. It provides a mechanism for both fetching
and updating data row by row, allowing the application to process the result set
sequentially. Cursors are particularly useful in procedural languages like
PL/SQL for processing query results within stored procedures, functions, or
triggers.

Types of Cursors:
There are two main types of cursors:

Implicit Cursor:

Automatically created by the database management system for every

SQL statement that is not associated with an explicitly declared cursor.
 Used for queries where the result set is automatically handled by the
system.
 Example:
DECLARE
Department of Computer Science & Engineering
Faculty of Engineering
Medi-Caps University
2
CS3CO39: Database Management System Laboratory Experiment no- 10
Experiment Title: Introduction to PL/SQL Triggers and Cursors Page 3 of 5

v_employee_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO v_employee_name FROM employees
WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/
Explicit Cursor:

 Explicitly declared by the programmer to manage the result set of a

query.
 Provides more control over the cursor operations and is especially useful
for handling multiple rows.
 Example:
DECLARE
CURSOR employee_cursor IS
SELECT employee_name FROM employees WHERE department_id
= 10;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO v_employee_name;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' ||
v_employee_name);
END LOOP;
CLOSE employee_cursor;
END;
/

Cursor Operations:
Declaration:

The cursor is declared with a DECLARE CURSOR statement,

specifying the SELECT query to retrieve the desired result set.
Opening:

The cursor is opened using the OPEN statement, which associates it with
the result set.
Fetching:

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
3
CS3CO39: Database Management System Laboratory Experiment no- 10
Experiment Title: Introduction to PL/SQL Triggers and Cursors Page 4 of 5

Data is retrieved from the cursor using the FETCH statement. This
operation can be performed iteratively in a loop.
Closing:

The cursor is closed using the CLOSE statement when it is no longer

needed.
Cursor Attributes:
%FOUND:

Returns TRUE if the most recent FETCH statement successfully

retrieved a row.
%NOTFOUND:

Returns TRUE if the most recent FETCH statement did not retrieve a
row.
%ROWCOUNT:

Returns the number of rows fetched so far.

3. Software used:
Oracle Database (Recommended) or
MySQL
4. Solution:
Example of Triggers:
CREATE OR REPLACE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 100000 THEN
DBMS_OUTPUT.PUT_LINE('Warning: High salary update
detected!');
END IF;
END;
/
In this example, the trigger named before_employee_update is fired before each
row in the employees table is updated. It checks if the new salary value is
greater than 100,000 and prints a warning message if true.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
4
CS3CO39: Database Management System Laboratory Experiment no- 10
Experiment Title: Introduction to PL/SQL Triggers and Cursors Page 5 of 5

Triggers are powerful tools, but they should be used judiciously as they
introduce automatic behavior that may impact performance and complexity.
Careful consideration of their purpose and potential impact is crucial when
implementing triggers in a database system.

Example of an Explicit Cursor:

DECLARE
CURSOR employee_cursor IS
SELECT employee_name FROM employees WHERE department_id = 10;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO v_employee_name;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END LOOP;
CLOSE employee_cursor;
END;
/
In this example, the explicit cursor employee_cursor is declared, opened, and
then used to fetch and process the names of employees in a specific department.
The loop continues until there are no more rows to fetch (%NOTFOUND is
TRUE).

5. Some Sample questions:

1. What is the purpose of PL/SQL triggers, and how do they differ from stored
procedures?
2. Explain the types of triggers in PL/SQL. Provide examples for each type.
3. What is a cursor in PL/SQL, and how does it work?
4. How do you handle exceptions in PL/SQL triggers? Provide an example of
using exception handling in a trigger.
5. Describe a practical scenario where you would use a trigger and a cursor in
PL/SQL to achieve a specific outcome.

Department of Computer Science & Engineering

Faculty of Engineering
Medi-Caps University
5