SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
SCHOOL OF APPLIED SCIENCE
DATABASE MANAGEMENT SYSTEM
DBMS
LAB MANUAL
SEMESTER : II
COURSE CODE : 24BCACS204
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
DBMS LAB MANUAL
Sl No List of Experiments Hours
1 Cre Create the following table and retrieving records from the table. 2
EMPLOYEES (Employee_Id, First_Name, Last_Name, Email,
Phone_Number, Hire_Date,
Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id)
( a) Find out the employee id, names, salaries of all the employees
( b) List out the employees who works under manager 100
( c) Find the names of the employees who have a salary greater
than or equal to 4800
( d) List out the employees whose last name is ‘AUSTIN’
( e) Find the names of the employees who works in departments
60,70 and 80
( f ) Display the unique Manager_Id.
2 Cre Create the following table and Update records from the table. 2
Create Client_master with the following fields(ClientNO, Name,
Address, City, State, bal_due)
( a ) Insert five records
( b ) Find the names of clients whose bal_due> 5000 .
( c ) Change the bal_due of ClientNO “ C123” to Rs. 5100
( d ) Change the name of Client_master to Client12 .
( e ) Display the bal_due heading as “BALANCE”
3 DE Demonstrate the following commands 2
Rollback and Commit commands
Create Teacher table with the following fields(Name, DeptNo, Date
of joining, DeptName,
Location, Salary)
( a ) Insert five records
( b ) Give Increment of 25% salary for Mathematics Department .
( c ) Perform Rollback command
( d ) Give Increment of 15% salary for Commerce Department
( e ) Perform commit command
4 Demonstrate the on order by and group by clauses 2
Create Sales table with the following fields( Sales No, Salesname,
Branch, Salesamount, DOB)
( a ) Insert five records
( b ) Calculate total salesamount in each branch
( c ) Calculate average salesamount in each branch .
( d ) Display all the salesmen, DOB who are born in the month of
December as day in
character format i.e. 21-Dec-09
( e ) Display the name and DOB of salesman in alphabetical order of the
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
month.
5 Create an Emp table with the following fields and retrieve the data from the 2
table
(EmpNo, EmpName, Job,Basic, DA, HRA,PF, GrossPay, NetPay)
(Calculate DA as 30% of Basic and HRA as 40% of Basic)
( a ) Insert Five Records and calculate GrossPay and NetPay.
( b ) Display the employees whose Basic is lowest in each
department .
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as special
allowances .
( d ) Display the employees whose GrossPay lies between 10,000
& 20,000
( e ) Display all the employees who earn maximum salary .
6 cccConsider the Insurance database given below. 4
PERSON(driver_ID, name, address)
CAR(regno, model,year) ACCIDENT(report_number,accd_date,location)
OWNS(driver_id,regno)
PARTICIPATED(driver_id,regno,report_number,damage_amount)i.
a) Specify the primary keys and foreign keys and enter at least five
tuples for each relation.
b) Update the damage amount for the car with specific regno in
the accident with report number 1025
c) Add a new accident to the database
d) Find the total number of people who owned cars that were
involved in accidents in the year 2018.
e) Find the number of accidents in which cars belonging Wagon R
were involved
7 Employee Database 4
An Enterprise wishes to maintain a database to automate its operations.
Enterprise is divided into
certain departments and each department consists of employees. The
following two tables
describes the automation schemas
Dept (deptno, dname, loc)
Emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
a) Update the employee salary by 15%, whose experience is
greater than 10 years.
b) Delete the employees, who completed 30 years of service.
c) Display the manager who is having maximum number of
employees working under him?
d) Create a view, which contain employee names and their
manager
8 Using Employee Database in question no 6 perform the following queries 2
a) Determine the names of employee, who earn more than their
managers.
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
b) Determine the names of employees, who take highest salary in
their departments.
c) Determine the employees, who are located at the same place.
d) Determine the employees, whose total salary is like the minimum
Salary of any department.
e) Determine the department which does not contain any
employees.
9 Consider the following tables namely “DEPARTMENTS” and 2
“EMPLOYEES” Their
schemas are as follows,
Departments ( dept _no , dept_ name , dept_location );
Employees ( emp_id , emp_name , emp_salary,dept_no);
a) Develop a query to grant all privileges of employees table into
departments table
b) Develop a query to grant some privileges of employees table into
departments table
c) Develop a query to revoke all privileges of employees table from
departments table
d) Develop a query to revoke some privileges of employees table
from departments table
e) Write a query to implement the save point.
10 Using the tables “DEPARTMENTS” and “EMPLOYEES” perform the 2
following
queries
a) Display the employee details, departments that the departments
are same in both the emp
and dept.
b) Display the employee name and Department name by
implementing a left outer join.
c) Display the employee name and Department name by
implementing a right outer join.
d) Display the details of those who draw the salary greater than the
average salary.
1) Create the following table and retrieving records from the table.
EMPLOYEES (Employee_Id, First_Name, Last_Name, Email, Phone_Number,
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Hire_Date,Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id)
( a) Find out the employee id, names, salaries of all the employees
( b) List out the employees who works under manager 100
( c) Find the names of the employees who have a salary greater than or equal to
4800
( d) List out the employees whose last name is ‘AUSTIN’
( e) Find the names of the employees who works in departments 60,70 and 80
( f ) Display the unique Manager_Id.
FOLLOW THE BELOW STEPS:
Step 1: Create the Database
CREATE DATABASE EmployeeDB;
USE EmployeeDB;
Step 2: Create the EMPLOYEES Table
CREATE TABLE EMPLOYEES (
Employee_Id INT PRIMARY KEY,
First_Name VARCHAR(50),
Last_Name VARCHAR(50),
Email VARCHAR(100) UNIQUE,
Phone_Number VARCHAR(20),
Hire_Date DATE,
Job_Id VARCHAR(20),
Salary DECIMAL(10,2),
Commission_Pct DECIMAL(5,2),
Manager_Id INT,
Department_Id INT
);
Step 3: Insert Sample Data
INSERT INTO EMPLOYEES (Employee_Id, First_Name, Last_Name, Email,
Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id,
Department_Id)
VALUES
(101, 'John', 'Doe', '
[email protected]', '1234567890', '2020-06-10',
'DEV', 5000.00, 0.10, 100, 60),
(102, 'Alice', 'Austin', '
[email protected]', '1234567891', '2019-07-
15', 'HR', 4800.00, 0.05, 101, 70),
(103, 'Bob', 'Smith', '
[email protected]', '1234567892', '2021-08-20',
'DEV', 4500.00, 0.08, 100, 80),
(104, 'David', 'Brown', '
[email protected]', '1234567893', '2018-
05-25', 'HR', 5200.00, 0.07, 102, 60),
(105, 'Emma', 'Johnson', '
[email protected]', '1234567894',
'2022-09-30', 'DEV', 4900.00, 0.06, 100, 70),
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
(106, 'Chris', 'Austin', '[email protected]', '1234567895', '2017-10-
11', 'DEV', 4700.00, 0.09, 103, 80);
Step 4: Queries for the Given Questions
(a) Find out the employee id, names, and salaries of all employees
SELECT Employee_Id, First_Name, Last_Name, Salary FROM EMPLOYEES;
(b) List out the employees who work under manager 100
SELECT Employee_Id, First_Name, Last_Name FROM EMPLOYEES WHERE
Manager_Id = 100;
(c) Find the names of the employees who have a salary greater than or equal
to 4800
SELECT First_Name, Last_Name, Salary FROM EMPLOYEES WHERE Salary
>= 4800;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
d) List out the employees whose last name is ‘AUSTIN’
SELECT Employee_Id, First_Name, Last_Name FROM EMPLOYEES WHERE
Last_Name = 'AUSTIN';
(e) Find the names of the employees who work in departments 60, 70, and 80
SELECT First_Name, Last_Name, Department_Id FROM EMPLOYEES
WHERE Department_Id IN (60, 70, 80);
(f) Display the unique Manager_Id
SELECT DISTINCT Manager_Id FROM EMPLOYEES;
2) Create the following table and Update records from the table.
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Create Client_master with the following fields(ClientNO, Name, Address, City, State,
bal_due)
( a ) Insert five records
( b ) Find the names of clients whose bal_due> 5000 .
( c ) Change the bal_due of ClientNO “ C123” to Rs. 5100
( d ) Change the name of Client_master to Client12 .
( e ) Display the bal_due heading as “BALANCE”
Step 1: Creating the Database
CREATE DATABASE ClientDB;
USE ClientDB;
Step 2: Creating the Client_master Table
CREATE TABLE Client_master (
ClientNO VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50),
Address VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50),
bal_due DECIMAL(10,2)
);
NOTE: To view the table use desc (database Name);
Ex: desc ClientDB;
Step 3: Inserting Five Records
INSERT INTO Client_master (ClientNO, Name, Address, City,
State, bal_due) VALUES
('C101', 'Rajesh Kumar', '123, MG Road', 'Bangalore',
'Karnataka', 4500.00),
('C102', 'Suresh Mehta', '56, Gandhi Nagar', 'Mumbai',
'Maharashtra', 7000.00),
('C103', 'Anita Sharma', '89, Park Street', 'Kolkata', 'West
Bengal', 3000.00),
('C104', 'Vinod Gupta', '78, Nehru Street', 'Delhi', 'Delhi',
8200.00),
('C123', 'Meera Nair', '45, Lake View', 'Chennai', 'Tamil Nadu',
4900.00);
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Step 4: Finding Clients with bal_due > 5000
SELECT Name FROM Client_master WHERE bal_due > 5000;
Step 5: Updating bal_due of Client C123 to 5100
UPDATE Client_master SET bal_due = 5100 WHERE ClientNO =
'C123';
Step 6: Renaming Client_master to Client12
ALTER TABLE Client_master RENAME TO Client12;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Step 7: Displaying bal_due as "BALANCE"
SELECT ClientNO, Name, Address, City, State, bal_due AS
"BALANCE" FROM Client12;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
5) Create an Emp table with the following fields and retrieve the data from the table
(EmpNo, EmpName, Job,Basic, DA, HRA,PF, GrossPay, NetPay)
(Calculate DA as 30% of Basic and HRA as 40% of Basic)
( a ) Insert Five Records and calculate GrossPay and NetPay.
( b ) Display the employees whose Basic is lowest in each department .
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as special allowances .
( d ) Display the employees whose GrossPay lies between 10,000 & 20,000
( e ) Display all the employees who earn maximum salary
Step 1: Create the Emp Table
CREATE TABLE Emp (
EmpNo INT PRIMARY KEY,
EmpName VARCHAR(50),
Job VARCHAR(50),
Basic DECIMAL(10,2),
DA DECIMAL(10,2),
HRA DECIMAL(10,2),
PF DECIMAL(10,2),
GrossPay DECIMAL(10,2),
NetPay DECIMAL(10,2)
);
Step 2: Insert Five Records & Calculate GrossPay and NetPay
Formulae:
DA = 30% of Basic
HRA = 40% of Basic
PF = 12% of Basic (Assumed as a standard PF deduction)
GrossPay = Basic + DA + HRA
NetPay = GrossPay - PF
INSERT INTO Emp (EmpNo, EmpName, Job, Basic, DA, HRA, PF, GrossPay,
NetPay)
VALUES
(1, 'Alice', 'Manager', 25000, 25000 * 0.30, 25000 * 0.40, 25000 * 0.12,
(25000 + 25000 * 0.30 + 25000 * 0.40), (25000 + 25000 * 0.30 +
25000 * 0.40 - 25000 * 0.12)),
(2, 'Bob', 'Clerk', 8000, 8000 * 0.30, 8000 * 0.40, 8000 * 0.12, (8000 +
8000 * 0.30 + 8000 * 0.40), (8000 + 8000 * 0.30 + 8000 * 0.40 - 8000
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
* 0.12)),
(3, 'Charlie', 'Analyst', 18000, 18000 * 0.30, 18000 * 0.40, 18000 * 0.12,
(18000 + 18000 * 0.30 + 18000 * 0.40), (18000 + 18000 * 0.30 +
18000 * 0.40 - 18000 * 0.12)),
(4, 'David', 'Clerk', 9000, 9000 * 0.30, 9000 * 0.40, 9000 * 0.12, (9000 +
9000 * 0.30 + 9000 * 0.40), (9000 + 9000 * 0.30 + 9000 * 0.40 - 9000
* 0.12)),
(5, 'Eve', 'HR', 22000, 22000 * 0.30, 22000 * 0.40, 22000 * 0.12, (22000 +
22000 * 0.30 + 22000 * 0.40), (22000 + 22000 * 0.30 + 22000 * 0.40 -
22000 * 0.12));
SELECT * FROM Emp;
Step 3: Display the Employees Whose Basic is Lowest in Each Job (GROUP
BY)
SELECT EmpName, Job, Basic
FROM Emp
WHERE Basic IN (SELECT MIN(Basic) FROM Emp GROUP BY Job);
Step 4: If NetPay is Less Than 10,000, Add Rs. 1200 as Special Allowance
UPDATE Emp
SET NetPay = NetPay + 1200
WHERE NetPay < 10000;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
SELECT * FROM Emp;
Step 5: Display Employees Whose GrossPay Lies Between 10,000 &
20,000
SELECT EmpName, Job, GrossPay
FROM Emp
WHERE GrossPay BETWEEN 10000 AND 20000;
Step 6: Display All Employees Who Earn Maximum Salary
SELECT EmpName, Job, GrossPay
FROM Emp
WHERE GrossPay = (SELECT MAX(GrossPay) FROM Emp);
Summary of SQL Queries Used
1. Create Table: CREATE TABLE Emp (...)
2. Insert Data with Calculations: INSERT INTO Emp (...) VALUES (...)
3. View Table: SELECT * FROM Emp;
4. Find Employee with Lowest Basic in Each Job: WHERE Basic IN
(SELECT MIN(Basic) FROM Emp GROUP BY Job)
5. Increase NetPay for Low Salary Employees: UPDATE Emp SET
NetPay = NetPay + 1200 WHERE NetPay < 10000;
6. Find Employees with GrossPay Between 10,000 & 20,000:
WHERE GrossPay BETWEEN 10000 AND 20000
7. Find Employees with Maximum Salary: WHERE GrossPay =
(SELECT MAX(GrossPay) FROM Emp)
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
3) Demonstrate the following commands
Rollback and Commit commands:
Create Teacher table with the following fields(Name, DeptNo, Date of joining,
DeptName,
Location, Salary)
( a ) Insert five records
( b ) Give Increment of 25% salary for Mathematics Department .
( c ) Perform Rollback command
( d ) Give Increment of 15% salary for Commerce Department
( e ) Perform commit command
Step 1: Create the Teacher Table
CREATE TABLE Teacher (
Name VARCHAR(50),
DeptNo INT,
DateOfJoining DATE,
DeptName VARCHAR(50),
Location VARCHAR(50),
Salary DECIMAL(10,2)
);
Step 2: Insert Five Records
INSERT INTO Teacher (Name, DeptNo, DateOfJoining, DeptName,
Location, Salary)
VALUES
('Alice', 101, '2015-06-10', 'Mathematics', 'Bangalore', 50000),
('Bob', 102, '2017-08-21', 'Commerce', 'Mysore', 45000),
('Charlie', 103, '2018-07-15', 'Mathematics', 'Bangalore', 52000),
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
('David', 104, '2016-05-30', 'Commerce', 'Mangalore', 48000),
('Eve', 105, '2019-09-10', 'Physics', 'Bangalore', 55000);
Step 3: Give 25% Salary Increment for Mathematics Department
UPDATE Teacher
SET Salary = Salary * 1.25
WHERE DeptName = 'Mathematics';
Step 4: Perform ROLLBACK (Undo Salary Changes for Mathematics
Department)
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Step 5: Give 15% Salary Increment for Commerce Department
UPDATE Teacher
SET Salary = Salary * 1.15
WHERE DeptName = 'Commerce';
Step 6: Perform COMMIT (Save Changes Permanently)
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Summary of Commands Used
1. Create Table: CREATE TABLE Teacher (...)
2. Insert Data: INSERT INTO Teacher (...) VALUES (...)
3. View Table: SELECT * FROM Teacher;
4. Update Data: UPDATE Teacher SET Salary = ... WHERE
DeptName = ...;
5. Rollback Changes: ROLLBACK;
6. Commit Changes: COMMIT;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
4) Demonstrate the on order by and group by clauses
Create Sales table with the following fields ( Sales No, Salesname, Branch, Salesamount, DOB)
( a ) Insert five records
( b ) Calculate total salesamount in each branch
( c ) Calculate average salesamount in each branch .
( d ) Display all the salesmen, DOB who are born in the month of December as day in
character format i.e. 21-Dec-09
( e ) Display the name and DOB of salesman in alphabetical order of the month.
Step 1: Create the Sales Table
CREATE TABLE Sales (
SalesNo INT PRIMARY KEY,
SalesName VARCHAR(50),
Branch VARCHAR(50),
SalesAmount DECIMAL(10,2),
DOB DATE
);
Desc sales;
To View the table
Step 2: Insert Five Records
INSERT INTO Sales (SalesNo, SalesName, Branch, SalesAmount,
DOB)
VALUES
(1, 'Alice', 'Bangalore', 50000, '1990-12-21'),
(2, 'Bob', 'Mysore', 45000, '1985-07-10'),
(3, 'Charlie', 'Bangalore', 52000, '1992-04-05'),
(4, 'David', 'Mangalore', 48000, '1988-12-15'),
(5, 'Eve', 'Mysore', 55000, '1995-06-30');
✅ View the Table After Insertion:
SELECT * FROM Sales;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Step 3: Calculate Total SalesAmount in Each Branch (GROUP BY Clause)
SELECT Branch, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Branch;
Step 4: Calculate Average SalesAmount in Each Branch (GROUP BY
Clause)
SELECT Branch, AVG(SalesAmount) AS AvgSales
FROM Sales
GROUP BY Branch;
Step 5: Display Salesmen Born in December in Character Format
SELECT SalesName,
DATE_FORMAT(DOB, '%d-%b-%y') AS FormattedDOB
FROM Sales
WHERE MONTH(DOB) = 12;
SAPTHAGIRI NPS UNIVERSITY SCHOOL OF APPLIED SCIENCE
Step 6: Display Salesmen Name and DOB in Alphabetical Order of the
Month
SELECT SalesName,
DATE_FORMAT(DOB, '%d-%b-%y') AS FormattedDOB
FROM Sales
ORDER BY MONTH(DOB);
Summary of SQL Queries Used
1. Create Table: CREATE TABLE Sales (...)
2. Insert Data: INSERT INTO Sales (...) VALUES (...)
3. View Table: SELECT * FROM Sales;
4. Group by Total SalesAmount: SUM(SalesAmount) GROUP BY
Branch
5. Group by Average SalesAmount: AVG(SalesAmount) GROUP BY
Branch
6. Salesmen Born in December: WHERE MONTH(DOB) = 12
7. Order by Month of DOB: ORDER BY MONTH(DOB)
