Punjab College of Technical Education
BADDOWAL (LUDHIANA)
BACHELORES OF COMPUTER APPLICATIONS
(Session 2023-2026)
PRACTICAL FILE
DATABASE MANAGEMENT SYSTEM
UGCA1925
Submitted to: Submitted by:
Ms. Pratiksha Divish Bansal
Roll no.:
2322173
Contents
SERIAL
EXPERIMENT SIGNATURE
NO.
Used of CREATE, ALTER,
1 RENAME and DROP statement in the
database tables.
Use of INSERT INTO, DELETE and
UPDATE statement in the database
2 tables (relations).
3
Use of simple select statement
4
Use of select query on two relations.
5
Use of nesting of queries.
6
Use of aggregate functions.
7
Use of substring comparison.
8
Use of order by statement.
Consider the following schema for a
Library
Database: BOOK (Book_id, Title,
Publisher_Name,
Pub_Year) BOOK_AUTHORS
(Book_id,
Author_Name) PUBLISHER (Name,
Address, Phone) BOOK_COPIES
9 (Book_id, Branch_id, Noof_Copies)
BOOK_LENDING (Book_id,
Branch_id, Card_No, Date_Out,
Due_Date)
LIBRARY_BRANCH (Branch_id,
Branch_Name,
Address) Write SQL queries to 1.
Retrieve details of all books in the
library_id, title, name of publisher,
authors, number of copies in each
branch, etc. 2. Get the particulars of
borrowers who have borrowed more
than 3 books between Jan 2018 to June
2018 3.
Delete a book in the BOOK table.
Update the
contents of other tables to reflect this
data manipulation operation. 4.
Partition the BOOK table based on
year of publication. Demonstrate its
working with a simple query. 5.
Create a view of all books and its
number of copies that are currently
available in the Library.
Consider the following schema for
Order Database:
SALESMAN (Salesman_id, Name,
City,
Commission) CUSTOMER
(Customer_id,
Cust_Name, City, Grade, Salesman_id)
ORDERS
(Ord_No, Purchase_Amt, Ord_Date,
Customer_id, Salesman_id) Write SQL
queries to 1. Count the customers with
10 grades above Amritsar’s average. 2.
Find the name and numbers of all
salesmen who had more than one
customer. 3. List all salesmen and
indicate those who have and don’t have
customers in their cities (Use UNION
operation.) 4. Create a view that finds
the salesman who has the customer with
the highest order of a day. 5.
Demonstrate the DELETE operation by
removing salesman with id 1000. All his
orders must also be deleted.
Write a PL/SQL code to add two
numbers and display the result. Read
11 the numbers during run time.
Write a PL/SQL code to find the sum of
12 the first 10 natural numbers using while
and for loop.
Write a program to create a trigger
which will convert the name of a student
13 to uppercase before inserting or
updating the name column of the student
table.
Write a PL/SQL block to count the
number of rows affected by an update
14 statement using
SQL%ROWCOUNT
Write a PL/SQL block to increase the
15 salary of all doctors by 1000.
Experiment: 1
Used of CREATE, ALTER, RENAME and DROP statement in the
database tables (relations).
CREATE TABLE Employee (
Emp_ID INT,
Name VARCHAR(50),
Department VARCHAR(30)
);
ALTER TABLE Employee ADD Salary NUMBER(8,2);
RENAME Employee TO Company_Employee;
DROP TABLE Company_Employee;
Output:
Experiment: 2
Use of INSERT INTO, DELETE and UPDATE statement in the
database tables (relations).
#Code(INSERT INTO statement in the database tables)
INSERT INTO Employee (Emp_ID, Name, Department, Salary)
VALUES (101, 'Ravi', 'HR', 35000);
#Code(DELETE statement in the database tables)
DELETE FROM Employee
WHERE Emp_ID = 101;
#Code(UPDATE statement in the database tables)
UPDATE Employee
SET Salary = 40000
WHERE Emp_ID = 101;
Output:
Experiment: 3
Use of simple select statement.
-- Simple SELECT
SELECT * FROM Employee;
-- Select specific columns
SELECT Name, Salary FROM Employee;
-- Select with a condition (WHERE)
SELECT * FROM Employee WHERE Department = 'HR'
Output:
Experiment: 4
Use of select query on two relations.
CREATE TABLE Department (
Dept_ID INT,
Dept_Name VARCHAR(30));
CREATE TABLE Employee (
Emp_ID INT,
Name VARCHAR(50),
Dept_ID INT);
INSERT INTO Department VALUES (1, 'HR');
INSERT INTO Department VALUES (2, 'IT');
INSERT INTO Employee VALUES (101, 'Ravi', 1);
INSERT INTO Employee VALUES (102, 'Anjali', 2);
SELECT E.Emp_ID, E.Name, D.Dept_Name
FROM Employee E
JOIN Department D ON E.Dept_ID = D.Dept_ID;
Output:
Experiment: 5
Use of nesting of queries.
CREATE TABLE Employee (
Emp_ID INT,
Name VARCHAR(50),
Salary NUMBER(8,2),
Dept_ID INT
);
INSERT INTO Employee VALUES (101, 'Ravi', 35000, 1);
INSERT INTO Employee VALUES (102, 'Anjali', 42000, 2);
INSERT INTO Employee VALUES (103, 'Meena', 28000, 1);
SELECT Name, Salary
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Output:
Experiment: 6
Use of aggregate functions.
1. COUNT()
SELECT COUNT(*) FROM Employee;
2. SUM()
CREATE TABLE Employee (
Emp_ID INT,
Name VARCHAR(50),
Salary NUMBER(8,2),
Dept_ID INT
);
INSERT INTO Employee VALUES (101, 'Ravi', 35000, 1);
INSERT INTO Employee VALUES (102, 'Anjali', 42000, 2);
INSERT INTO Employee VALUES (103, 'Meena', 28000, 1);
SELECT Name, Salary
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
SELECT SUM(Salary) FROM Employee;
3. AVG()
SELECT AVG(Salary) FROM Employee;
4. MAX()
SELECT MAX(Salary) FROM Employee;
5. MIN()
SELECT MIN(Salary) FROM Employee;
Output:
Experiment: 7
Use of substring comparison.
SELECT Name
FROM Employee
WHERE SUBSTR(Name, 1, 2) = 'Ra';
Output:
Experiment: 8
Use of order by statement.
SELECT Name SELECT Name
FROM Employee FROM Employee
ORDER BY Name;(IN ASCENDING ORDER) ORDER BY Name;(IN DESCENDING ORDER)
Output:
Experiment: 9
Consider the following schema for a Library Database:
BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Branch_id, No-of_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH (Branch_id, Branch_Name, Address)
CREATE TABLE "Library_Branch" ( INSERT INTO LIBRARY_BRANCH (Branch_id,
Branch_id NUMBER PRIMARY KEY, Branch_Name, Address)
Branch_Name VARCHAR2(100), VALUES (1, 'Central Library', '123 Main St');
Address VARCHAR2(255) INSERT INTO LIBRARY_BRANCH (Branch_id,
);CREATE TABLE "BOOK" ( Branch_Name, Address)
Book_id NUMBER PRIMARY KEY, VALUES (2, 'North Branch', '456 North Ave');
Title VARCHAR2(255), INSERT INTO LIBRARY_BRANCH (Branch_id,
Publisher_Name VARCHAR2(100), Branch_Name, Address)
Pub_Year NUMBER VALUES (3, 'South Branch', '789 South Rd');
);CREATE TABLE "BOOK_AUTHORS" ( INSERT INTO BOOK_COPIES (Book_id, Branch_id,
Book_id NUMBER, No_of_Copies)
Author_Name VARCHAR2(100), VALUES (101, 1, 5);
FOREIGN KEY (Book_id) INSERT INTO BOOK_COPIES (Book_id, Branch_id,
REFERENCES "BOOK" (Book_id) No_of_Copies)
);CREATE TABLE "PUBLISHER" ( VALUES (101, 2, 3);
Name VARCHAR2(100) PRIMARY KEY, INSERT INTO BOOK_COPIES (Book_id, Branch_id,
Address VARCHAR2(255), No_of_Copies)
Phone VARCHAR2(15) VALUES (102, 2, 2);
);CREATE TABLE "LIBRARY_BRANCH" ( INSERT INTO BOOK_COPIES (Book_id, Branch_id,
Branch_id NUMBER PRIMARY KEY, No_of_Copies)
Branch_Name VARCHAR2(100), VALUES (103, 3, 4);
Address VARCHAR2(255) INSERT INTO BOOK (Book_id, Title,
);CREATE TABLE "BOOK_COPIES" ( Publisher_Name, Pub_Year)
Book_id NUMBER, VALUES (101, 'The Great Adventure', 'Fiction
CREATE TABLE "Library_Branch" ( Press', 2020);
Branch_id NUMBER PRIMARY KEY, INSERT INTO BOOK (Book_id, Title,
Branch_Name VARCHAR2(100), Publisher_Name, Pub_Year)
Address VARCHAR2(255) ); VALUES (102, 'Learning SQL', 'Tech Books',
CREATE TABLE "BOOK" ( 2021);
Book_id NUMBER PRIMARY KEY, INSERT INTO BOOK (Book_id, Title,
Title VARCHAR2(255), Publisher_Name, Pub_Year)
Publisher_Name VARCHAR2(100), VALUES (103, 'Database Design 101', 'Data
Pub_Year NUMBER Books', 2022);
); INSERT INTO BOOK_COPIES (Book_id, Branch_id,
No_of_Copies)
CREATE TABLE "BOOK_AUTHORS" ( VALUES (101, 1, 5);
Book_id NUMBER, INSERT INTO BOOK_COPIES (Book_id, Branch_id,
Author_Name VARCHAR2(100), No_of_Copies)
FOREIGN KEY (Book_id) REFERENCES "BOOK" VALUES (101, 2, 3);
(Book_id) INSERT INTO BOOK_COPIES (Book_id, Branch_id,
); No_of_Copies)
VALUES (102, 2, 2);
CREATE TABLE "PUBLISHER" ( INSERT INTO BOOK_COPIES (Book_id, Branch_id,
1. Retrieve details of all books in the library_id, title, name of publisher,
authors, number of copies in each branch, etc.
SELECT
B.Book_id,
B.Title,
B.Publisher_Name,
BA.Author_Name,
BC.Branch_id,
LB.Branch_Name,
BC.No_of_Copies
FROM
BOOK B
LEFT JOIN
BOOK_AUTHORS BA ON B.Book_id = BA.Book_id
LEFT JOIN
BOOK_COPIES BC ON B.Book_id = BC.Book_id
LEFT JOIN
LIBRARY_BRANCH LB ON BC.Branch_id = LB.Branch_id
ORDER BY
B.Book_id, LB.Branch_id;
Output:
2. Get the particulars of borrowers who have borrowed more than 3 books
between Jan 2018 to June 2018
SELECT
Card_No,
COUNT(*) AS Books_Borrowed
FROM
BOOK_LENDING
WHERE
Date_Out BETWEEN TO_DATE('20-JAN-2018', 'DD-MON-YYYY')
AND TO_DATE('29-JAN-2018', 'DD-MON-YYYY')
GROUP BY
Card_No
HAVING
COUNT(*) > 3;
Output:
3. Delete a book in the BOOK table. Update the contents of other tables to
reflect this data manipulation operation.
DELETE FROM BOOK_LENDING WHERE Book_id = 101;
DELETE FROM BOOK_COPIES WHERE Book_id = 101;
DELETE FROM BOOK_AUTHORS WHERE Book_id = 101;
DELETE FROM BOOK WHERE Book_id = 101;
COMMIT;
Output:
4. Partition the BOOK table based on year of publication. Demonstrate its
working with a simple query.
CREATE TABLE BOOK_PARTITIONED (
Book_id INT PRIMARY KEY,
Title VARCHAR2(255),
Publisher_Name VARCHAR2(255),
Pub_Year INT
)
PARTITION BY RANGE (Pub_Year) (
PARTITION p_2010_2015 VALUES LESS THAN (2016),
PARTITION p_2016_2020 VALUES LESS THAN (2021),
PARTITION p_2021_and_beyond VALUES LESS THAN
(MAXVALUE)
);
Output:
5. Create a view of all books and its number of copies that are currently
available in the Library
CREATE VIEW Available_Books AS
SELECT
b.Book_id,
b.Title,
b.Publisher_Name,
b.Pub_Year,
bc.Branch_id,
bc.No_of_Copies - NVL(SUM(bl.Card_No), 0) AS
Available_Copies
FROM
BOOK b
JOIN
BOOK_COPIES bc ON b.Book_id = bc.Book_id
LEFT JOIN
BOOK_LENDING bl ON b.Book_id = bl.Book_id AND
bc.Branch_id = bl.Branch_id
GROUP BY
b.Book_id, b.Title, b.Publisher_Name,
b.Pub_Year, bc.Branch_id, bc.No_of_Copies;
SELECT * FROM Available_Books;
Output:
Experiment: 10
Consider the following schema for Order Database: SALESMAN
(Salesman_id, Name, City, Commission) CUSTOMER
(Customer_id, Cust_Name, City, Grade, Salesman_id) ORDERS
(Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
CREATE TABLE SALESMAN ( Purchase_Amt NUMBER(10,2),
Salesman_id NUMBER PRIMARY KEY, Ord_Date DATE,
Name VARCHAR2(50), Customer_id NUMBER,
City VARCHAR2(50), Salesman_id NUMBER,
Commission NUMBER(5,2) CONSTRAINT fk_customer_order
FOREIGN KEY (Customer_id)
);
REFERENCES
CREATE TABLE CUSTOMER ( CUSTOMER(Customer_id),
Customer_id NUMBER PRIMARY KEY, CONSTRAINT fk_salesman_order
FOREIGN KEY (Salesman_id)
Cust_Name VARCHAR2(50),
REFERENCES
City VARCHAR2(50),
SALESMAN(Salesman_id)
Grade NUMBER(3),
);
Salesman_id NUMBER,
CONSTRAINT fk_salesman_customer FOREIGN KEY (Salesman_id)
REFERENCES SALESMAN(Salesman_id)
);
CREATE TABLE ORDERS (
Ord_No NUMBER PRIMARY KEY,
Output:
1. Count the customers with grades above Amritsar’s average.
SELECT COUNT(*) AS Count_Above_Avg
FROM CUSTOMER
WHERE Grade > (
SELECT AVG(Grade)
FROM CUSTOMER
WHERE LOWER(City) = 'amritsar'
);
Output:
2. Find the name and numbers of all salesmen who had more than
one customer.
SELECT S.Salesman_id, S.Name, COUNT(C.Customer_id) AS
Num_Customers
FROM SALESMAN S
JOIN CUSTOMER C ON S.Salesman_id = C.Salesman_id
GROUP BY S.Salesman_id, S.Name
HAVING COUNT(C.Customer_id) > 1;
Output:
3. List all salesmen and indicate those who have and don’t have
customers in their cities.
SELECT s.SALESMAN_ID, s.name AS salesman_name, s.city
AS salesman_city,
'Has Customer in City' AS status
FROM Salesman s
JOIN Customer c ON s.SALESMAN_ID = c.SALESMAN_ID AND
s.city = c.city
UNION
SELECT s.SALESMAN_ID, s.name AS salesman_name, s.city
AS salesman_city,
'No Customer in City' AS status
FROM Salesman s
WHERE NOT EXISTS (
SELECT 1
FROM Customer c
WHERE c.SALESMAN_ID = s.SALESMAN_ID AND c.city = s.city
);
Output:
4. Create a view that finds the salesman who has the customer with
the highest order of a day.
DELETE FROM Orders
WHERE SALESMAN_ID = 1000;
DELETE FROM Salesman
WHERE SALESMAN_ID = 1000;
CREATE VIEW TopSalesmanPerDay AS
SELECT o.ORD_DATE, o.PURCHASE_AMT AS amount,
c.CUSTOMER_ID, c.CUST_NAME AS customer_name,
s.SALESMAN_ID, s.name AS salesman_name
FROM Orders o
JOIN Customer c ON o.CUSTOMER_ID = c.CUSTOMER_ID
JOIN Salesman s ON c.SALESMAN_ID = s.SALESMAN_ID
WHERE (o.ORD_DATE, o.PURCHASE_AMT) IN (
SELECT ORD_DATE, MAX(PURCHASE_AMT)
FROM Orders
GROUP BY ORD_DATE
);
Output:
5. Demonstrate the DELETE operation by removing salesman with id
1000. All his orders must also be deleted.
DELETE FROM Orders
WHERE SALESMAN_ID = 1000;
DELETE FROM Salesman
WHERE SALESMAN_ID = 1000;
Output:
Experiment: 11
Write PL/SQL code to add two numbers and display the result.
Read the number during run time.
DECLARE
a NUMBER;
b NUMBER;
sum NUMBER;
BEGIN
a := &Enter_First_Number;
b := &Enter_Second_Number;
sum := a + b;
DBMS_OUTPUT.PUT_LINE('Sum is: ' || sum);
END;
/
Output:
Experiment: 12
Write a PL/SQL code to find the sum of the first 10 natural
numbers using while and for loop.
DECLARE
i NUMBER := 1;
sum NUMBER := 0;
BEGIN
WHILE i <= 10 LOOP
sum := sum + i;
i := i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum using WHILE: ' || sum);
END;
/ DECLARE
sum NUMBER := 0;
BEGIN
FOR i IN 1..10 LOOP sum := sum + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum using FOR: ' || sum);
END; /
Output:
Experiment: 13
Write a program to create a trigger which will convert the name
of a student to uppercase before inserting or updating the name
column of the student table.
CREATE TABLE studenttt (
student_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
age NUMBER(3),
course VARCHAR2(50));
INSERT INTO studenttt (student_id, name, age, course)
VALUES (1, 'john doe', 20, 'Computer Science');
COMMIT;
SELECT * FROM studenttt;
CREATE TABLE studenttt (
student_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
age NUMBER(3),
course VARCHAR2(50));
INSERT INTO studenttt (student_id, name, age, course)
VALUES (1, 'john doe', 20, 'Computer Science');
COMMIT;
SELECT * FROM studenttt;
DESC studenttt;
CREATE OR REPLACE TRIGGER upper_name_trigger
BEFORE INSERT OR UPDATE ON studenttt
FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;
/
INSERT INTO studenttt (student_id, name, age, course)
VALUES (2, 'alice walker', 22, 'Physics');
SELECT * FROM studenttt;
Output:
Experiment: 14
Write a PL/SQL block to increase the salary of all doctors by 1000.
CREATE TABLE DOCTORS (
Employee_id INT PRIMARY KEY,
Doctor_Name VARCHAR2(255),
Job_title VARCHAR2(100),
Doctor_Salary NUMBER
);
INSERT INTO DOCTORS (Employee_id, Doctor_Name, Job_title,
Doctor_Salary)
VALUES (1, 'Dr. John Smith', 'DOCTOR', 50000);
INSERT INTO DOCTORS (Employee_id, Doctor_Name, Job_title,
Doctor_Salary)
VALUES (2, 'Dr. Emily Johnson', 'DOCTOR', 55000);
SELECT * FROM DOCTORS;
UPDATE DOCTORS
SET Doctor_Salary = 70000
WHERE Employee_id = 2;
INSERT INTO DOCTORS (Employee_id, Doctor_Salary)
VALUES (3, 75000); -- Use a new unique Employee_id like 3
DROP TABLE DOCTORS;
CREATE TABLE DOCTORS (
Employee_id INT PRIMARY KEY,
Doctor_Salary NUMBER
);
BEGIN
INSERT INTO DOCTORS (Employee_id, Doctor_Salary)
VALUES (2, 70000);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Duplicate entry for
Employee_id 2');
END;
Output:
Experiment: 15
Write a PL/SQL block to increase the salary of all doctors by 1000.
CREATE TABLE Doctor (
DOCTOR_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
SALARY NUMBER(10, 2)
);
INSERT INTO Doctor VALUES (1, 'Dr. Ayesha', 50000);
INSERT INTO Doctor VALUES (2, 'Dr. Rohan', 55000);
INSERT INTO Doctor VALUES (3, 'Dr. Mehta', 60000);
COMMIT;
BEGIN
UPDATE Doctor
SET SALARY = SALARY + 1000;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Salaries of all doctors have
been increased by 1000.');
END;
SELECT * FROM Doctor;
Output:
