Data Base Management System
Course Code: E2UC302B
Lab File
For
BACHELOR OF
ENGINEERING & TECHNOLOGY
SCHOOL OF COMPUTER SCIENCE AND ENGINEERING
GALGOTIAS UNIVERSITY, GREATER NOIDA
UTTAR PRADESH
Student Name: Ayush Singh
Admission No:24SCSE1420165
Semester : III
List of Experiments
1 Draw an E-R diagram and convert entities and relationships to a relation table for a
given scenario.(Two assignments shall be carried out i.e., consider two different scenarios (e.g. bank, College)
2 Implementation of DDL commands of SQL with suitable examples.
(a)Create table (b) Alter table (c) Drop Table
3 Implementation of DML commands of SQL with suitable examples.
(a)Insert table (b) Update table (c) Delete Table
4 Implementation of different types of functions with suitable examples. Number
Function
Aggregate Function
Character Function
Conversion Function
Date Function
5 Implementation of different types of operators in SQL.
6 Perform the following:
a. Creating Tables (With and Without Constraints (Key/Domain)
b. Creating Tables (With Referential Integrity Constraints)
7 For a given set of relation schemes, create tables and perform the following Queries: a.
Simple Queries
b. Queries with Aggregate functions (Max/Min/Sum/Avg/Count)
c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions
8 For a given set of relation schemes, create tables and perform the following Queries:
a. Inner Join
b. Outer Join
c. Natural Join
9 For a given set of related tables perform the following: -
a. Creating Views
b. Dropping views
c. Selecting from a view
10 Implementation of Group by & Having Clause, Order by Clause, Indexing.
11 Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a cursor
to select the five highest-paid employees from the table.
12 For a given set of related tables perform the following:
a. Begin Transactions
b. End Transaction
13 For a given set of related tables perform the following:
a. Create roles
b. Assign Privileges
c. Revoke Privileges
14 Write a Pl/SQL program using a FOR loop to insert ten rows into a database table.
15 Perform the following: Inserting/Updating/Deleting Records in a Table, Saving (Commit) and Undoing (rollback)
Experiment 1
Aim: Consider following databases and draw ER diagram and convert entities and
relationships to relation table for a given scenario.
1. COLLEGE DATABASE:
STUDENT (USN, SName, Address, Phone, Gender) SEMSEC (SSID, Sem, Sec) CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
2. COMPANY DATABASE:
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo) DEPARTMENT (DNo, DName, MgrSSN,
MgrStartDate) DLOCATION (DNo,DLoc) PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours).
College Database: E-R Diagram
Mapping entities and relationships to relation table(Schema Diagram)
COMPANY DATABASE: E-R Diagram
Experiment 2
AIM: Implementation of DDL commands of SQL with suitable examples. (a) Create table (b) Alter
table (c) Drop Table.
1. Create Table:
Purpose: To create a new table (relation) in the database.
Syntax:
CREATE TABLE table_name (
column1 datatype(size),
column2 datatype(size),
...);
Example: Create an EMPLOYEE table:
CREATE TABLE EMPLOYEE(Emp_noNUMBER(5),E_nameVARCHAR2(20),E_address VARCHAR2(50),
E_ph_no CHAR (10),Dept_no NUMBER (3),Dept_name VARCHAR2(20),Job_id CHAR (5), Salary NUMBER (10,2));
[Link] Table:
Purpose: To modify an existing table structure.
(a) Add a new column
Purpose: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE table_name ADD column_name data_type(size);
Example: ALTER TABLE EMPLOYEEADD (HIREDATE DATE);
(b) Modify data type or column size
Purpose: This is used to change the width as well as data type of fields of existing relations.
Syntax: ALTER TABLE table_name
MODIFY (column_name new_data_type(size));
Example: ALTER TABLE EMPLOYEE MODIFY (Job_id VARCHAR2(10));
(c) Rename a column
Purpose: This is used to change the name of fields in existing relations.
Syntax: ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
Example: ALTER TABLE EMPLOYEE
RENAME COLUMN Emp_no TO E_no;
(d) Drop a column
Purpose: This is used to remove any field of existing relations.
Syntax: ALTER TABLE table_name
DROP COLUMN column_name;
Example: ALTER TABLE EMPLOYEE
DROP COLUMN E_address;
3. DROP TABLE
Purpose: To delete the table and its data permanently.
Syntax: DROP TABLE table_name;
Example: DROP TABLE EMPLOYEE;
Experiment 3
AIM: Implementation of DML commands of SQL with suitable
examples. (a)Insert table (b) Update table (c) Delete Table
1. INSERT INTO: This is used to add records into a relation. These are three type of INSERT INTO queries which are as
a) Inserting a single record
Syntax: INSERT INTO < relation/table name> (field_1,field_2……field_n)VALUES (data_1,data_2, data_n); Example:
SQL>INSERT INTO student (sno,sname,class,address)VALUES (1,’Ravi’,’[Link]’,’Palakol’);
b) Inserting a single record
Syntax: INSERT INTO < relation/table name>VALUES (data_1,data_2, ....... data_n);
Example: SQL>INSERT INTO student VALUES (1,’Ravi’,’[Link]’,’Palakol’);
c) Inserting all records from another relation
Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n FROM relation_name_2 WHERE field_x=data;
Example: SQL>INSERT INTO std SELECT sno,sname FROM student WHERE name = ‘Ramu‘;
d) Inserting multiple records
Syntax: INSERT INTO relation_name field_1,field_2, .... field_n) VALUES
(&data_1,&data_2, ....... &data_n);
Example: SQL>INSERT INTO student (sno, sname, class,address) VALUES (&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101 Enter value for name: Ravi Enter value for class: [Link] Enter value for name: Palakol
2. UPDATE-SET-WHERE: This is used to update the content of a record in a relation.
Syntax: SQL>UPDATE relation name SET Field_name1=data,field_name2=data, WHERE field_name=data;
Example: SQL>UPDATE student SET sname = ‘kumar’ WHERE sno=1;
3. DELETE-FROM: This is used to delete all the records of a relation but it will retain the structure of that relation.
a) DELETE-FROM: This is used to delete all the records of relation.
Syntax: SQL>DELETE FROM relation_name;
Example: SQL>DELETE FROM std;
b) DELETE -FROM-WHERE: This is used to delete a selected record from a relation.
Syntax: SQL>DELETE FROM relation_name WHERE condition;
Example: SQL>DELETE FROM student WHERE sno = 2;
Experiment 3
Aim: Implementation of different types of functions with suitable examples.
(i)Number Function
(ii)Aggregate Function
(iii)Character Function
(iv)Conversion Function
(v)Date Function
1. Number Functions
Purpose:-Number functions operate on numeric data and return numeric values.
Syntax: SELECT function_name(number, [arguments]) FROM dual;
Examples:
(i)ABS() – Returns absolute value
SELECT ABS(-15) FROM dual;
(ii) ROUND() – Rounds a number
SELECT ROUND(45.678, 2) FROM dual;
(iii) MOD() – Returns remainder
SELECT MOD(20, 6) FROM dual;
2. Aggregate Functions
Purpose: Perform calculations on a group of rows.
Syntax: SELECT aggregate_function(column_name)
FROM table_name;
Examples:
(i) COUNT():- SELECT COUNT(*) FROM EMPLOYEE;
(ii) SUM():- SELECT SUM(Salary) FROM EMPLOYEE;
(iii) AVG():-SELECT AVG(Salary) FROM EMPLOYEE;
(iv) MAX() / MIN():- SELECT MAX(Salary), MIN(Salary) FROM EMPLOYEE;
3. Character Functions
Purpose: Manipulate character (string) data.
Syntax: SELECT function_name(character_value) FROM dual;
Examples:
(i) UPPER():-
SELECT UPPER('database') FROM dual;
(ii) LOWER():-
SELECT LOWER('SQL') FROM dual;
(iii) LENGTH():-
SELECT LENGTH('ORACLE') FROM dual;
(iv) SUBSTR():-
SELECT SUBSTR('COMPUTER', 1, 4) FROM dual;
4. Conversion Functions
Purpose: Convert one data type into another.
Syntax: SELECT conversion_function(expression) FROM dual;
Examples:
(i) TO_CHAR():-
SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') FROM dual;
(ii)TO_DATE():-
SELECT TO_DATE('25-12-2025', 'DD-MM-YYYY') FROM dual;
(iii) TO_NUMBER():-
SELECT TO_NUMBER('1234') FROM dual;
5. Date Functions
Purpose: Perform operations on date values.
Syntax: SELECT date_function(date_value, [arguments]) FROM dual;
Examples:
(i) SYSDATE
SELECT SYSDATE FROM dual;
(ii) ADD_MONTHS()
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
(iii) MONTHS_BETWEEN()
(iv) SELECT MONTHS_BETWEEN(SYSDATE, '01-JAN-2025') FROM dual;
(v) NEXT_DAY()
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;
Experiment 5
Aim: Implementation of different types of operators in SQL.
1. Arithmetic Operators
Used to perform mathematical operations.
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
Syntax:
SELECT expression FROM table_name;
Example:
SELECT Salary + 5000 AS New_Salary FROM EMPLOYEE;
2. Relational (Comparison) Operators
Used to compare two values.
Operator Description
= Equal to
!= or <> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
Syntax:
SELECT * FROM table_name WHERE condition;
Example:
SELECT * FROM EMPLOYEE WHERE Salary > 40000;
3. Logical Operators
Used to combine multiple conditions.
Operator Description
AND All conditions must be true
OR Any condition must be true
Operator Description
NOT Negates a condition
Syntax:
SELECT * FROM table_name WHERE condition1 AND condition2;
Example:
SELECT * FROM EMPLOYEE
WHERE Dept_no = 10 AND Salary > 30000;
4. Special Operators
Used for specific conditional checks.
(a) IN
SELECT * FROM EMPLOYEE WHERE Dept_no IN (10, 20);
(b) BETWEEN
SELECT * FROM EMPLOYEE WHERE Salary BETWEEN 30000 AND 60000;
(c) LIKE
SELECT * FROM EMPLOYEE WHERE E_name LIKE 'R%';
(d) IS NULL
SELECT * FROM EMPLOYEE WHERE E_address IS NULL;
5. Set Operators
Used to combine results of two or more SELECT statements.
Operator Description
UNION Combines results (removes duplicates)
UNION ALL Combines results (keeps duplicates)
INTERSECT Common records
MINUS Records in first query but not in second
Syntax:
SELECT column FROM table1
UNION
SELECT column FROM table2;
Example:
SELECT Dept_no FROM EMPLOYEE
UNION
SELECT Dept_no FROM DEPARTMENT;
6. Assignment Operator
Used in UPDATE statements.
Syntax:
UPDATE table_name SET column = value WHERE condition;
Example:
UPDATE EMPLOYEE SET Salary = Salary + 2000 WHERE Dept_no = 20;
Experiment.6
Aim: Perform the following:
a. Creating Tables (With and Without Constraints (Key/Domain)
b. Creating Tables (With Referential Integrity Constraints) For a given EMPLOYEE tables
Perform the Following
1. Creating Views (With and Without Check Option),
2. Selecting from a View
3. Dropping Views,
SOLUTION:
SQL> CREATE TABLE EMPLOYEE ( SSN VARCHAR2 (20) PRIMARY KEY, FNAME VARCHAR2
(20), LNAME VARCHAR2 (20), ADDRESS VARCHAR2 (20), SEX CHAR (1), SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN), DNO REFERENCES DEPARTMENT (DNO));
SQL> DESC EMPLOYEE;
Name Null? Type
SSN NOT NULL VARCHAR2(20)
FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
SEX CHAR(1)
SALARY NUMBER(38)
SUPERSSN VARCHAR2(20)
DNO NUMBER(38)
Insert Employee Records
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSECE01', 'JOHN', 'SCOTT', 'BANGALORE', 'M', 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSCSE01', 'JAMES', 'SMITH', 'BANGALORE', 'M', 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSCSE02', 'HEARN', 'BAKER', 'BANGALORE', 'M', 700000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSCSE03', 'EDWARD', 'SCOTT', 'MYSORE', 'M', 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSCSE04', 'PAVAN', 'HEGDE', 'MANGALORE', 'M', 650000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSCSE05', 'GIRISH', 'MALYA', 'MYSORE', 'M', 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSCSE06', 'NEHA', 'SN', 'BANGALORE', 'F', 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSACC01', 'AHANA', 'K', 'MANGALORE', 'F', 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSACC02', 'SANTHOSH', 'KUMAR', 'MANGALORE', 'M', 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSISE01', 'VEENA', 'M', 'MYSORE', 'M', 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)
VALUES ('RNSIT01', 'NAGESH', 'HR', 'BANGALORE', 'M', 500000);
Creating View
The query that defines the sales_staffview references only rows in department 5. Furthermore, the
CHECK OPTION creates the view with the constraint (named sales_staff_cnst) that INSERT and
UPDATE statements issued against the view cannot result in rows that the view cannot select.
1. Creating Views (With and Without Check Option)
SQL> CREATE VIEW sales_staff AS 2 SELECT fname, ssn, dno 3 4 5 created. FROM
employee WHERE dno =5 WITH CHECK OPTION CONSTRAINT sales_staff_cnst; View
2. Selecting from a View
SQL> select * from sales_staff;
3. Drop View
SQL>DROP VIEW sales_staff;
Experiment 7
Aim: For a given set of relation schemes, create tables and perform the
following Queries:
a. Simple Queries
b. Queries with Aggregate functions (Max/Min/Sum/Avg/Count)
c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions
Solution:
TABLE CREATION
EMPLOYEE Table
CREATE TABLE EMPLOYEE (Emp_id NUMBER PRIMARY KEY,Emp_name VARCHAR2(20),Dept_no NUMBER,
Salary NUMBER,Hire_date DATE);
Insert Sample Data
INSERT INTO EMPLOYEE VALUES (101, 'Ravi', 10, 50000, DATE '2022-01-10');
INSERT INTO EMPLOYEE VALUES (102, 'Amit', 20, 45000, DATE '2021-03-15');
INSERT INTO EMPLOYEE VALUES (103, 'Neha', 10, 60000, DATE '2020-07-20');
INSERT INTO EMPLOYEE VALUES (104, 'Suman', 30, 55000, DATE '2023-02-05');
INSERT INTO EMPLOYEE VALUES (105, 'Pavan', 20, 48000, DATE '2022-11-18');
a) SIMPLE QUERIES
(i) Display all employees
SELECT * FROM EMPLOYEE;
Output:
Emp_id Emp_name Dept_no Salary Hire_date
101 Ravi 10 50000 10-JAN-22
102 Amit 20 45000 15-MAR-21
103 Neha 10 60000 20-JUL-20
104 Suman 30 55000 05-FEB-23
105 Pavan 20 48000 18-NOV-22
(ii) Employees from Dept 10
SELECT Emp_name, Salary
FROM EMPLOYEE
WHERE Dept_no = 10;
Ouput:
Emp_name Salary
Ravi 50000
Neha 60000
b) AGGREGATE FUNCTION QUERIES
SELECT
MAX(Salary) AS Max_Salary,
MIN(Salary) AS Min_Salary,
SUM(Salary) AS Total_Salary,
AVG(Salary) AS Avg_Salary,
COUNT(*) AS Total_Employees
FROM EMPLOYEE;
Output:
Max_Salary Min_Salary Total_Salary Avg_Salary Total_Employees
60000 45000 258000 51600 5
c) AGGREGATE FUNCTIONS WITH GROUP BY AND HAVING
(i) Department-wise Average Salary
SELECT Dept_no, AVG(Salary) AS Avg_Salary
FROM EMPLOYEE
GROUP BY Dept_no;
Output:
Dept_no Avg_Salary
10 55000
20 46500
30 55000
(ii) Departments with Avg Salary > 50000
SELECT Dept_no, AVG(Salary) AS Avg_Salary
FROM EMPLOYEE
GROUP BY Dept_no
HAVING AVG(Salary) > 50000;
Output:
Dept_no Avg_Salary
10 55000
30 55000
d) QUERIES USING FUNCTIONS
1. Date Functions
(i) Years of Experience
SELECT Emp_name,
FLOOR(MONTHS_BETWEEN(SYSDATE, Hire_date)/12) AS Experience
FROM EMPLOYEE;
Output:
Emp_name Experience
Ravi 3
Amit 4
Neha 5
Suman 2
Pavan 2
2. String Functions
SELECT
UPPER(Emp_name) AS Name_Upper,
LENGTH(Emp_name) AS Name_Length,
SUBSTR(Emp_name,1,3) AS Short_Name
FROM EMPLOYEE;
Output:
Name_Upper Name_Length Short_Name
RAVI 4 Rav
AMIT 4 Ami
NEHA 4 Neh
SUMAN 5 Sum
PAVAN 5 Pav
3. Math (Numeric) Functions
SELECT Emp_name,
Salary,
ROUND(Salary/12,2) AS Monthly_Salary,
MOD(Salary,10000) AS Salary_Mod
FROM EMPLOYEE;
Output:
Emp_name Salary Monthly_Salary Salary_Mod
Ravi 50000 4166.67 0
Amit 45000 3750.00 5000
Neha 60000 5000.00 0
Suman 55000 4583.33 5000
Pavan 48000 4000.00 8000
Experiment:8
Aim: For a given set of relation schemes, create tables and perform the
following Queries:
a. Inner Join
b. Outer Join
c. Natural Join
Solution:
RELATION SCHEMES:
EMPLOYEE
EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
DEPARTMENT
DEPARTMENT (Dept_no, Dept_name, Location)
[Link] CREATION
CREATE TABLE DEPARTMENT
Column Name Data Type Description
Emp_id NUMBER(5) Employee ID (Primary Key)
Emp_name VARCHAR2(20) Employee Name
Dept_no NUMBER(3) Department Number
Salary NUMBER(10) Employee Salary
CREATE TABLE DEPARTMENT (
Dept_no NUMBER(3) PRIMARY KEY,
Dept_name VARCHAR2(20),
Location VARCHAR2(20));
CREATE TABLE EMPLOYEE
Column Name Data Type Description
Dept_no NUMBER(3) Department Number (Primary Key)
Dept_name VARCHAR2(20) Department Name
Location VARCHAR2(20) Department Location
CREATE TABLE EMPLOYEE (
Emp_id NUMBER(5) PRIMARY KEY,
Emp_name VARCHAR2(20),
Dept_no NUMBER(3),
Salary NUMBER(10),
FOREIGN KEY (Dept_no) REFERENCES DEPARTMENT(Dept_no));
[Link] Data
DEPARTMENT Table Data
Dept_no Dept_name Location
10 IT Bangalore
Dept_no Dept_name Location
20 HR Mysore
30 Sales Mangalore
INSERT INTO DEPARTMENT VALUES (10, 'IT', 'Bangalore');
INSERT INTO DEPARTMENT VALUES (20, 'HR', 'Mysore');
INSERT INTO DEPARTMENT VALUES (30, 'Sales', 'Mangalore');
EMPLOYEE Table Data
Emp_id Emp_name Dept_no Salary
101 Ravi 10 50000
102 Amit 20 45000
103 Neha 10 60000
104 Pavan NULL 40000
INSERT INTO EMPLOYEE VALUES (101, 'Ravi', 10, 50000);
INSERT INTO EMPLOYEE VALUES (102, 'Amit', 20, 45000);
INSERT INTO EMPLOYEE VALUES (103, 'Neha', 10, 60000);
INSERT INTO EMPLOYEE VALUES (104, 'Pavan', NULL, 40000);
3. JOIN Queries
a) INNER JOIN
SELECT E.Emp_id, E.Emp_name, D.Dept_name
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D
ON E.Dept_no = D.Dept_no;
b) OUTER JOIN (LEFT OUTER JOIN)
SELECT E.Emp_id, E.Emp_name, D.Dept_name
FROM EMPLOYEE E
LEFT OUTER JOIN DEPARTMENT D
ON E.Dept_no = D.Dept_no;
c) NATURAL JOIN
SELECT Emp_id, Emp_name, Dept_name
FROM EMPLOYEE
NATURAL JOIN DEPARTMENT;
Experiment.9
Aim: For a given set of related tables perform the following: -
a. Creating Views
b. Dropping views
c. Selecting from a view
Solution:
RELATION SCHEMES
EMPLOYEE
EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
TABLE CREATION
CREATE TABLE EMPLOYEE (
Emp_id NUMBER(5) PRIMARY KEY,
Emp_name VARCHAR2(20),
Dept_no NUMBER(3),
Salary NUMBER(10),
FOREIGN KEY (Dept_no) REFERENCES DEPARTMENT(Dept_no));
Insert Data
INSERT INTO EMPLOYEE VALUES (101, 'Ravi', 10, 50000);
INSERT INTO EMPLOYEE VALUES (102, 'Amit', 20, 45000);
INSERT INTO EMPLOYEE VALUES (103, 'Neha', 10, 60000);
INSERT INTO EMPLOYEE VALUES (104, 'Suman', 30, 55000);
Emp_id Emp_name Dept_no Salary
101 Ravi 10 50000
102 Amit 20 45000
103 Neha 10 60000
104 Suman 30 55000
DEPARTMENT
DEPARTMENT (Dept_no, Dept_name, Location)
TABLE CREATION
CREATE TABLE DEPARTMENT (
Dept_no NUMBER(3) PRIMARY KEY,
Dept_name VARCHAR2(20),
Location VARCHAR2(20));
Insert Data
INSERT INTO DEPARTMENT VALUES (10, 'IT', 'Bangalore');
INSERT INTO DEPARTMENT VALUES (20, 'HR', 'Mysore');
INSERT INTO DEPARTMENT VALUES (30, 'FINANCE', 'Mangalore');
TABLE DATA (FOR REFERENCE)
DEPARTMENT
Dept_no Dept_name Location
10 IT Bangalore
20 HR Mysore
30 FINANCE Mangalore
a) CREATING VIEWS
Syntax
CREATE VIEW view_name AS
SELECT column_list
FROM table_name
WHERE condition;
Example 1: Create a View for Employee Details with Department Name
CREATE VIEW EMP_DEPT_VIEW AS
SELECT E.Emp_id, E.Emp_name, D.Dept_name, [Link]
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.Dept_no = D.Dept_no;
b) SELECTING FROM A VIEW
Syntax
SELECT * FROM view_name;
Example
SELECT * FROM EMP_DEPT_VIEW;
c) DROPPING A VIEW
Syntax
DROP VIEW view_name;
Example
DROP VIEW EMP_DEPT_VIEW;
OUTPUT (VIEW RESULT TABLE)
Emp_id Emp_name Dept_name Salary
101 Ravi IT 50000
103 Neha IT 60000
102 Amit HR 45000
Experiment:10
Aim: Implementation of Group by & Having Clause, Order by Clause, Indexing.
1. GROUP BY & HAVING Clause
Purpose:
• GROUP BY is used to group rows sharing a column value.
• HAVING is used to filter groups after aggregation (unlike WHERE, which filters rows before grouping).
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition_on_aggregate;
Example: Using EMPLOYEE table:
//Department-wise total salary
SELECT Dept_no, SUM(Salary) AS Total_Salary
FROM EMPLOYEE
GROUP BY Dept_no;
//Departments with total salary > 100000
SELECT Dept_no, SUM(Salary) AS Total_Salary
FROM EMPLOYEE
GROUP BY Dept_no
HAVING SUM(Salary) > 100000;
Result Table (GROUP BY)
Dept_no Total_Salary
10 110000
20 45000
30 55000
Result Table (HAVING)
Dept_no Total_Salary
10 110000
2. ORDER BY Clause
Purpose:
• Sorts the result set by one or more columns.
• Default order is ascending (ASC). Use DESC for descending.
Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;
Example:
//Sort employees by salary ascending
SELECT Emp_name, Salary
FROM EMPLOYEE
ORDER BY Salary ASC;
//Sort employees by salary descending
SELECT Emp_name, Salary
FROM EMPLOYEE
ORDER BY Salary DESC;
Result Table (ASC)
Emp_name Salary
Amit 45000
Ravi 50000
Suman 55000
Neha 60000
Result Table (DESC)
Emp_name Salary
Neha 60000
Suman 55000
Ravi 50000
Amit 45000
3. INDEXING
Purpose:
• Speeds up retrieval of rows from a table.
• Can be created on one or more columns.
Syntax:
//Create index
CREATE INDEX index_name ON table_name(column_name);
//Drop index
DROP INDEX index_name;
Example:
// Create index on Emp_name
CREATE INDEX idx_empname ON EMPLOYEE(Emp_name);
Result Table (After Index Creation)
Index Name Table Name Column Name Status
idx_empname EMPLOYEE Emp_name Created Successfully
//Drop index
DROP INDEX idx_empname;
Result Table (After Index Drop)
Index Name Table Name Column Name Status
idx_empname EMPLOYEE Emp_name Dropped Successfully
Experiment:11
Aim: Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a cursor to
select the five highest-paid employees from the table.
Solution:
EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID)
Create Table
CREATE TABLE EMPLOYEE (EMPNO INTEGER PRIMARY KEY,
NAME VARCHAR(20), SALARY NUMBER(7,2),
DESIGNATION VARCHAR(10), DEPTID INTEGER);
PL/SQL Cursor
DECLARE
//Define cursor to select top 5 highest-paid employees
CURSOR top_5_salary_cur IS
SELECT EmpNo, Name, Salary, Designation, DeptID
FROM EMPLOYEE
ORDER BY Salary DESC
FETCH FIRST 5 ROWS ONLY; -- For Oracle 12c and above
//Record to hold cursor row
emp_record top_5_salary_cur%ROWTYPE;
BEGIN
//Open cursor
OPEN top_5_salary_cur;
//Loop through each row
LOOP
FETCH top_5_salary_cur INTO emp_record;
EXIT WHEN top_5_salary_cur%NOTFOUND;
//Display employee details
DBMS_OUTPUT.PUT_LINE('EmpNo: ' || emp_record.EmpNo ||
', Name: ' || emp_record.Name ||
', Salary: ' || emp_record.Salary ||
', Designation: ' || emp_record.Designation ||
', DeptID: ' || emp_record.DeptID);
END LOOP;
//Close cursor
CLOSE top_5_salary_cur;
END;
Output
EmpNo Name Salary Designation DeptID
103 Neha 60000 Manager 10
104 Suman 55000 Lead 30
101 Ravi 50000 Developer 10
105 Amit 48000 Developer 20
106 Pavan 45000 HR 20
Experiment:12
Aim: For a given set of related tables perform the following:
a. Begin Transactions
b. End Transaction
Solution:
Tables:
1. EMPLOYEE(EmpNo, Name, Salary, DeptID)
2. DEPARTMENT(DeptID, DeptName)
Employee
Emp_id Emp_name Dept_no Salary
101 Ravi 10 50000
102 Amit 20 45000
103 Neha 10 60000
104 Suman 30 55000
Department
Dept_no Dept_name Location
10 IT Bangalore
20 HR Mysore
30 FINANCE Mangalore
a) BEGIN TRANSACTION
Purpose:
• To start a transaction so that multiple SQL statements can be executed as a single unit of work.
• Ensures atomicity; if something fails, changes can be rolled back.
Syntax (SQL): BEGIN TRANSACTION;
Example: BEGIN TRANSACTION;
//Insert a new employee
INSERT INTO EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
VALUES (105, 'Pavan', 20, 48000);
//Update salary of an employee
UPDATE EMPLOYEE
SET Salary = 52000
WHERE Emp_id = 101;
//Check data before committing
SELECT * FROM EMPLOYEE;
b) END TRANSACTION
Purpose:
• To commit changes permanently or rollback if an error occurs.
Syntax:
//Commit changes
COMMIT;
//Rollback changes
ROLLBACK;
Example:
//Commit all changes made in the transaction
COMMIT;
// OR, if something goes wrong
ROLLBACK;
Experiment:13
Aim: For a given set of related tables perform the following:
a. Create roles
b. Assign Privileges
c. Revoke Privileges
Related Tables
EMPLOYEE
Emp_id Emp_name Dept_no Salary
101 Ravi 10 50000
102 Amit 20 45000
103 Neha 10 60000
104 Suman 30 55000
DEPARTMENT
Dept_no Dept_name Location
10 IT Bangalore
20 HR Mysore
30 FINANCE Mangalore
a) CREATE ROLES
Purpose:
• Roles group multiple privileges to simplify access control.
Syntax
CREATE ROLE role_name;
Example
//Create a role for HR operations
CREATE ROLE hr_role;
//Create a role for IT operations
CREATE ROLE it_role;
b) ASSIGN PRIVILEGES
Purpose:
• Grant specific privileges (SELECT, INSERT, UPDATE, DELETE) to a role or user.
Syntax:
GRANT privilege ON table_name TO role_name;
GRANT role_name TO user_name;
Example
//Grant privileges to HR role
GRANT SELECT, UPDATE ON EMPLOYEE TO hr_role;
//Grant role to user
GRANT hr_role TO hr_user;
//Grant privileges to IT role
GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE TO it_role;
GRANT it_role TO it_user;
c) REVOKE PRIVILEGES
Purpose:
• Remove privileges or roles from a user or role.
Syntax:
REVOKE privilege ON table_name FROM role_name;
REVOKE role_name FROM user_name;
Example
//Revoke SELECT privilege from HR role
REVOKE SELECT ON EMPLOYEE FROM hr_role;
//Revoke role from user
REVOKE hr_role FROM hr_user;
Experiment:14
Aim: Write a Pl/SQL program using a FOR loop to insert ten rows into a database table.
Solution:
Table Used
EMPLOYEE Table
Column Name Data Type
Emp_id NUMBER
Emp_name VARCHAR2
Dept_no NUMBER
Salary NUMBER
CREATE TABLE EMPLOYEE (
Emp_id NUMBER PRIMARY KEY,
Emp_name VARCHAR2(20),
Dept_no NUMBER,
Salary NUMBER);
PL/SQL Program Using FOR Loop
BEGIN
//Loop from 1 to 10
FOR i IN 1..10 LOOP
INSERT INTO EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
VALUES (i, 'Emp'||i, MOD(i,3)+1, 30000 + (i*1000));
END LOOP;
//Commit the transaction
COMMIT;
DBMS_OUTPUT.PUT_LINE('10 rows inserted successfully into EMPLOYEE table.');
END;
/
Resulting EMPLOYEE Table After Execution:
Emp_id Emp_name Dept_no Salary
1 Emp1 2 31000
2 Emp2 3 32000
3 Emp3 1 33000
4 Emp4 2 34000
Emp_id Emp_name Dept_no Salary
5 Emp5 3 35000
6 Emp6 1 36000
7 Emp7 2 37000
8 Emp8 3 38000
9 Emp9 1 39000
10 Emp10 2 40000
Experiment:15
Aim: Perform the following: Inserting/Updating/Deleting Records in a Table, Saving (Commit) and
Undoing (rollback)
Table Used: EMPLOYEE
Emp_id Emp_name Dept_no Salary
101 Ravi 10 50000
102 Amit 20 45000
103 Neha 10 60000
CREATE TABLE EMPLOYEE (
Emp_id NUMBER PRIMARY KEY,
Emp_name VARCHAR2(20),
Dept_no NUMBER,
Salary NUMBER);
[Link] RECORDS
INSERT INTO EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
VALUES (104, 'Suman', 30, 55000);
INSERT INTO EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
VALUES (105, 'Pavan', 20, 48000);
//Check inserted data
SELECT * FROM EMPLOYEE;
Output:
Emp_id Emp_name Dept_no Salary
101 Ravi 10 50000
102 Amit 20 45000
103 Neha 10 60000
104 Suman 30 55000
105 Pavan 20 48000
2. UPDATE RECORDS
UPDATE EMPLOYEE
SET Salary = Salary + 5000
WHERE Emp_id = 101;
//Check updated data
SELECT * FROM EMPLOYEE;
Output (Salary of Emp_id 101 updated):
Emp_id Emp_name Dept_no Salary
101 Ravi 10 55000
102 Amit 20 45000
Emp_id Emp_name Dept_no Salary
103 Neha 10 60000
104 Suman 30 55000
105 Pavan 20 48000
3. DELETE RECORDS
DELETE FROM EMPLOYEE
WHERE Emp_id = 105;
//Check remaining data
SELECT * FROM EMPLOYEE;
Output (Emp_id 105 deleted):
Emp_id Emp_name Dept_no Salary
101 Ravi 10 55000
102 Amit 20 45000
103 Neha 10 60000
104 Suman 30 55000
4. COMMIT
COMMIT;
Output:
• All changes (INSERT, UPDATE, DELETE) are permanently saved.
• No undo possible after commit.
5. ROLLBACK
Example: rollback before commit
INSERT INTO EMPLOYEE (Emp_id, Emp_name, Dept_no, Salary)
VALUES (106, 'Veena', 20, 50000);
ROLLBACK;
//Check table data
SELECT * FROM EMPLOYEE;
Output (Rollback undone insertion of Emp_id 106):
Emp_id Emp_name Dept_no Salary
101 Ravi 10 55000
102 Amit 20 45000
103 Neha 10 60000
104 Suman 30 55000