A
Practical File Submitted To
Asian School of Business, Noida
as a partial fulfillment of Full-time degree
Bachelor of Computer Applications (BCA)
Affiliated to Ch. Charan Singh University, Meerut
Submitted by:
Submitted to: Name of Student: Naman Rai
Faculty Guide: Prof. Deepali vishnoi Univ. Roll No.: 230398000281
Designation: Assistant Professor(IT) Enrollment No. :ASB/BCA/23/030
Batch: 2023-26
Asian School of Business (ASB)
A2, Sector – 125, Noida
Website: [Link]
1
TABLE OF CONTENT
S. No. Name of Practical Signature
1 SQL – Data Definition and Manipulation
(Create/Alter/Drop/Truncate)
2 Conceptual Design using E–R Model (University
Management System)
3 INSERT, UPDATE and DELETE operations on Employee table
4 Implement PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL,
UNIQUE constraints
5 Advanced Queries: ALL, EXISTS, NOT EXISTS
6 Aggregate functions with GROUP BY and HAVING on Sales
table
7 JOIN operations: INNER, LEFT, RIGHT and conditional JOINs
8 SQL String functions practice on Employees table
9 SQL Numeric functions on Product table
10 SQL Date functions on Orders table
11 PL/SQL: Age Eligibility program
12 PL/SQL: Salary grade using CASE statement
13 PL/SQL: FOR loop display numbers 1 to 10
14 Write a transaction demo with COMMIT and ROLLBACK
15 Create and use Views for summarizing sales data
16 Stored Procedure to insert employee record
17 Create Trigger to log salary changes
18 Normalize a sample unnormalized table to 3NF
19 Perform backup and restore commands description
(theoretical + commands)
20 Design a small inventory database and write sample queries
2
PRACTICAL No. 1: Practicing DDL Commands
Aim: Use SQL commands — CREATE, ALTER, DROP, and TRUNCATE
to define database structure.
Concepts:
Schema creation, modification, and deletion.
1. Create a database named company_db:
CREATE DATABASE company_db;
OUTPUT:
Database company_db created successfully.
2. Create a table named Employee:
USE company_db;
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(30),
Designation VARCHAR(30),
Salary DECIMAL(10,2),
Department VARCHAR(20)
);
OUTPUT:
Table Employee created successfully
3
3. Alter the table Employee:
a) Add a new column Email:
ALTER TABLE Employee
ADD Email VARCHAR(50);
OUTPUT:
Added new column name Email
b) Modify the Salary column to store larger values:
ALTER TABLE Employee
MODIFY Salary DECIMAL(12,2);
OUTPUT:
Table modified for large values
c) Rename the column Department to DeptName:
ALTER TABLE Employee
CHANGE Department DeptName VARCHAR(20);
OUPUT:
Table altered successfully.
4. Truncate the table to remove all rows (keep structure):
TRUNCATE TABLE Employee;
OUTPUT:
All rows deleted; table structure retained.
4
5. Drop the table completely:
DROP TABLE Employee;
OUTPUT:
Table Employee deleted successfully.
5
PRACTICAL No. 2: Conceptual Design using E–R Model
Aim: Draw an ER Diagram for a University Management System showing
entities, attributes, and relationships.
Concepts:
Entities, attributes, relationships, cardinalities, participation, generalization, and aggregation.
Diagram (Description):
Entities:
● Student (StudentID, Name, Course, DOB, Email)
● Department (DeptID, DeptName, Location)
● Faculty (FacultyID, Name, Designation, DeptID)
● Course (CourseID, CourseName, Credits, DeptID)
Relationships:
● Student enrolls in Course (Many-to-Many)
● Faculty teaches Course (One-to-Many)
● Department offers Course (One-to-Many)
6
PRACTICAL No. 3: DML Commands (INSERT, UPDATE,
DELETE)
Aim: Perform INSERT, UPDATE, and DELETE operations on Employee data.
Recreate Employee Table:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(30),
DeptName VARCHAR(20),
Salary DECIMAL(12,2),
City VARCHAR(30)
);
OUTPUT:
Recreated employee table
7. Insert five records:
INSERT INTO Employee VALUES
(101, 'Amit Sharma', 'HR', 75000, 'Delhi'),
(102, 'Priya Singh', 'IT', 60000, 'Mumbai'),
(103, 'Rohan Verma', 'Finance', 50000, 'Delhi'),
(104, 'Sneha Gupta', 'Marketing', 45000, 'Pune'),
(105, 'Vikas Jain', 'IT', 70000, 'Bangalore'),
(106, 'Ritu Mehra', 'HR', 40000, 'Chennai');
OUTPUT:
6 rows inserted successfully
7
8. Display all records:
SELECT * FROM Employee;
OUTPUT:
9. Update Records (Increase salary in IT Dept by 10%):
UPDATE Employee
SET Salary = Salary * 1.10
WHERE EmpID > 0;
OUTPUT:
Increased salary in IT dept by 10%
10. Change designation of EmpID = 104:
UPDATE Employee
SET DeptName = 'Senior Designer'
WHERE EmpID = 104;
8
OUTPUT:
Changed the designation of EmpID=104
11. Delete Operations:
i) Delete EmpID = 105:
DELETE FROM Employee
WHERE EmpID = 105;
OUTPUT:
Deleted EmpID = 105
9
ii) Delete all employees with Salary < 50000:
DELETE FROM Employee
WHERE Salary < 50000;
OUTPUT:
Deleted all employees with Salary < 50000:
10
PRACTICAL No. 4: CONSTRAINS IMPLEMENTATION
Aim: Implement PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL,
UNIQUE constraints.
Create Tables with Constraints:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(30) NOT NULL,
DeptName VARCHAR(20),
Salary DECIMAL(12,2) CHECK (Salary > 0),
City VARCHAR(30),
UNIQUE(EmpName)
);
CREATE TABLE Project (
ProjID VARCHAR(10) PRIMARY KEY,
ProjName VARCHAR(50),
EmpID INT,
Location VARCHAR(30),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);
OUTPUT:
11
Insert into Project Table:
INSERT INTO Project VALUES
('P01', 'ERP System', 102, 'Mumbai'),
('P02', 'HR Portal', 101, 'Delhi'),
('P03', 'SalesApp', 105, 'Bangalore'),
('P04', 'PayrollSys', 103, 'Delhi'),
('P05', 'CRM Module', 107, 'Pune');
12. Subqueries and Logical Operations
i) Employees whose salary is greater than all employees in Finance:
SELECT EmpName
FROM Employees
WHERE Salary > ALL (
SELECT Salary
FROM Employees
WHERE DeptName = 'Finance'
)
LIMIT 0, 1000;
OUTPUT:
12
ii) Employees assigned to at least one project (EXISTS):
SELECT EmpName
FROM Employees E
WHERE EXISTS (
SELECT *
FROM Project P
WHERE [Link] = [Link]
)
LIMIT 0, 1000;
OUTPUT:
iii) Employees not assigned any project (NOT EXISTS):
SELECT EmpName
FROM Employees E
WHERE NOT EXISTS (
SELECT * FROM Project P
WHERE [Link] = [Link]
);
13
OUTPUT:
14
PRACTICAL No. 5: Aggregate Functions and Grouping
Aim:Use aggregate functions and GROUP BY clause to summarize data.
Table: Sales
SaleID ProductName Category Quantity Price SaleDate
1 Pen Stationery 10 5 2025-01-10
2 Notebook Stationery 5 50 2025-01-11
3 Pencil Stationery 20 3 2025-01-12
4 Keyboard Electronics 3 700 2025-01-13
5 Mouse Electronics 6 400 2025-01-13
6 Charger Electronics 4 800 2025-01-14
7 Pen Stationery 15 5 2025-01-15
i) Count total number of sales records:
SELECT COUNT(*) AS Total_Sales FROM Sales;
OUTPUT:
ii) Total quantity sold for each product:
SELECT ProductName, SUM(Quantity) AS Total_Quantity
FROM Sales
GROUP BY ProductName;
15
OUTPUT:
iii) Average price of each product category:
SELECT Category, AVG(Price) AS Avg_Price
FROM Sales
GROUP BY Category;
OUTPUT:
iv) Minimum and Maximum price in each category:
SELECT Category, MIN(Price) AS Min_Price, MAX(Price) AS Max_Price
FROM Sales
GROUP BY Category;
OUTPUT:
16
v) Total sales amount (Quantity × Price) for each category:
SELECT Category, SUM(Quantity * Price) AS Total_Sales
FROM Sales
GROUP BY Category;
OUTPUT:
vi) Categories where total sales amount > ₹2000:
SELECT Category, SUM(Quantity * Price) AS Total_Sales
FROM Sales
GROUP BY Category
HAVING SUM(Quantity * Price) > 2000;
OUTPUT:
17
14. JOIN operations in [Link] these three Tables write the queries of the following
Students
Column Data Type Description
StudentID INT (PK) Unique ID of each student
Name VARCHAR(50) Student’s name
Class VARCHAR(10) Class name
Courses
Column Data Type Description
StudentID INT (FK) Student taking the course
CourseID INT (FK) Course taken by the student
Grade VARCHAR(2) Grade received
Enrollments
Column Data Type Description
StudentID INT (FK) Student taking the course
CourseID INT (FK) Course taken by the student
Grade VARCHAR(2) Grade received
Database and table created and inserted actual data in it
18
I. Display the list of students along with the courses they are enrolled in. (Use INNER
JOIN)
SELECT [Link], [Link], [Link], [Link]
FROM Students s
INNER JOIN Enrollments e ON [Link] = [Link]
INNER JOIN Courses c ON [Link] = [Link];
OUTPUT:
II. Display all students even if they have not enrolled in any course. (Use LEFT JOIN)
SELECT [Link], [Link], [Link], [Link]
FROM Students s
LEFT JOIN Enrollments e ON [Link] = [Link]
LEFT JOIN Courses c ON [Link] = [Link];
OUTPUT:
19
III. Display all courses even if no student has enrolled in them. (Use RIGHT JOIN)
SELECT [Link], [Link], [Link], [Link]
FROM Students s
RIGHT JOIN Enrollments e ON [Link] = [Link]
RIGHT JOIN Courses c ON [Link] = [Link];
OUTPUT:
IV. Display Student Name, Course Name, and Grade for each enrollment. (Use INNER
JOIN)
SELECT [Link] AS StudentName, [Link], [Link]
FROM Enrollments e
INNER JOIN Students s ON [Link] = [Link]
INNER JOIN Courses c ON [Link] = [Link];
OUTPUT:
20
V. Display only those students who have received Grade 'A'. (Use JOIN with WHERE
clauses
SELECT [Link] AS StudentName, [Link], [Link]
FROM Enrollments e
INNER JOIN Students s ON [Link] = [Link]
INNER JOIN Courses c ON [Link] = [Link]
WHERE [Link] = 'A';
OUTPUT:
21
15. SQL String Functions
Employees table
Column Data Type Description
EmpID INT (PK) Employee ID
EmpName VARCHAR(50) Employee Name
Department VARCHAR(30) Department Name
City VARCHAR(30) City Name
Created table Employees and inserted data into it
I. Display all employee names in uppercase.(Use UPPER()).
SELECT EmpName, UPPER(EmpName) AS UpperName
FROM Employees;
OUTPUT:
22
II. Display all employee names in lowercase.(Use LOWER()).
SELECT EmpName, LOWER(EmpName) AS LowerName
FROM Employees;
OUTPUT:
III. Display the length of each employee’s name.(Use LENGTH() or LEN() depending
on SQL).
SELECT EmpName, LENGTH(EmpName) AS NameLength
FROM Employees;
OUTPUT:
IV. Display the first 3 characters of each employee’s name.(Use SUBSTR() /
SUBSTRING())
SELECT EmpName, SUBSTRING(EmpName, 1, 3) AS First3Chars
FROM Employees;
23
OUTPUT:
V. Display employee names without leading and trailing spaces.(Use TRIM())
SELECT EmpName, TRIM(EmpName) AS TrimmedName
FROM Employees;
OUTPUT:
VI. Replace the city name 'Delhi' with 'New Delhi' in the City column.(Use
REPLACE())
SELECT City,
REPLACE(City, 'Delhi', 'New Delhi') AS UpdatedCity
FROM Employees;
OUTPUT:
24
VII. Concatenate employee name with their department in the format:Name -
Department.(Use CONCAT() or || depending on SQL)
SELECT CONCAT(EmpName, ' - ', Department) AS NameDept
FROM Employees;
OUTPUT:
VIII. Display employee names reversed.(Use REVERSE())
SELECT EmpName, REVERSE(EmpName) AS ReversedName
FROM Employees;
OUTPUT:
IX. Display names where employee name starts with 'S'.(Use LIKE 'S%')
SELECT EmpName
FROM Employees
WHERE EmpName LIKE 'S%';
OUTPUT:
25
16 . SQL Numeric Functions
Table: Product
Column Data Type Description
ProductID INT (PK) Unique ID of product
ProductName VARCHAR(50) Name of product
Price DECIMAL(10,2) Price of product
Quantity INT Quantity available
Table created and inserted data in it
I. Display the round value of price for each product.(Use ROUND())
SELECT ProductName, Price, ROUND(Price) AS RoundedPrice
FROM Product;
OUTPUT:
II. Display the ceiling and floor values of price.(Use CEIL() / CEILING() and FLOOR())
SELECT ProductName, Price,
CEILING(Price) AS PriceCeiling,
FLOOR(Price) AS PriceFloor
26
FROM Product;
OUTPUT:
III. Display the square root of the quantity of each product.(Use SQRT())
SELECT ProductName, Quantity, SQRT(Quantity) AS QuantitySqrt
FROM Product;
OUTPUT:
IV. Display the absolute value of (Price - 100) for each product.(Use ABS())
SELECT ProductName, Price, ABS(Price - 100) AS AbsPriceDiff
FROM Product;
OUTPUT:
27
V. Display Price raised to the power 2 for each product.(Use POWER())
SELECT ProductName, Price, POWER(Price, 2) AS PriceSquared
FROM Product;
OUTPUT:
VI. Display the highest and lowest price among all products.(Use MAX() and MIN())
SELECT MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice
FROM Product;
OUTPUT:
VII. Display the total value of inventory (Price * Quantity) for each product.(Use
arithmetic expression)
SELECT ProductName, Price, Quantity, (Price * Quantity) AS TotalValue
FROM Product;
OUTPUT:
28
VIII. Display the average, sum, and count of prices.(Use AVG(), SUM(), COUNT())
SELECT AVG(Price) AS AvgPrice,
SUM(Price) AS TotalPrice,
COUNT(Price) AS PriceCount
FROM Product;
OUTPUT:
17. SQL Date Functions
Table: Orders
Column Data Type Description
OrderID INT (PK) Unique order ID
CustomerName VARCHAR(50) Name of customer
OrderDate DATE Date on which order was placed
DeliveryDate DATE Date on which order was delivered
Amount DECIMAL(10,2) Order amount
Table created and inserted data in it
I. Display the current system date.(Use CURRENT_DATE / GETDATE())
SELECT CURRENT_DATE AS CurrentDate;
29
OUTPUT:
II. Display the OrderDate in the format DD-MM-YYYY.(Use DATE_FORMAT() /
TO_CHAR() depending on SQL)
SELECT OrderID, CustomerName, DATE_FORMAT(OrderDate, '%d-%m-%Y') AS
FormattedOrderDate
FROM Orders;
OUTPUT:
III. · Calculate the number of days taken for delivery for each order.(Use DATEDIFF()
/ DeliveryDate - OrderDate)
SELECT OrderID, CustomerName, OrderDate, DeliveryDate,
DATEDIFF(DeliveryDate, OrderDate) AS DaysToDeliver
FROM Orders;
OUTPUT:
30
IV. Display the month and year of each order.(Use MONTH(), YEAR())
SELECT OrderID, CustomerName, OrderDate,
MONTH(OrderDate) AS OrderMonth,
YEAR(OrderDate) AS OrderYear
FROM Orders;
OUTPUT:
V. Display all orders placed in the month of January.(Use MONTH(OrderDate)=1)
SELECT OrderID, CustomerName, OrderDate
FROM Orders
WHERE MONTH(OrderDate) = 1;
OUTPUT:
VI. Add 7 days to the OrderDate and display the result.(Use DATEADD() / OrderDate
+ INTERVAL 7 DAY).
SELECT OrderID, CustomerName, OrderDate,
DATE_ADD(OrderDate, INTERVAL 7 DAY) AS OrderPlus7Days
FROM Orders;
31
OUTPUT:
18. Write a PL/SQL program to check the age of a student is 18 years or older. If the age is
18 or above, display the message "Student is Eligible", otherwise display "Student is Not
Eligible".
-- Declare a variable
SET @student_age = 19; -- Change this value to test different ages
-- Use IF in a SELECT statement to display message
SELECT
CASE
WHEN @student_age >= 18 THEN 'Student is Eligible'
ELSE 'Student is Not Eligible'
END AS Eligibility;
OUTPUT:
-- Note: This program and its output are based on MySQL Workbench 8.0 CE.
-- The syntax may vary slightly in other versions or database systems.
32
19. Write a PL/SQL program that accepts an employee’s salary and uses a CASE
statement to display the salary grade according to the following conditions:
● If salary is above 50,000, display "High Salary"
● If salary is between 30,000 and 50,000, display "Medium Salary"
● If salary is below 30,000, display "Low Salary"
-- Note: This program is written for MySQL Workbench 8.0 CE
DELIMITER //
CREATE PROCEDURE SalaryGrade(IN emp_salary DECIMAL(10,2))
BEGIN
SELECT
CASE
WHEN emp_salary > 50000 THEN 'High Salary'
WHEN emp_salary >= 30000 AND emp_salary <= 50000 THEN 'Medium Salary'
ELSE 'Low Salary'
END AS Salary_Grade;
END //
DELIMITER ;
-- Example: Call the procedure with a salary value
CALL SalaryGrade(60000);
OUTPUT:
33
20. Write a PL/SQL program using a FOR loop to display numbers from 1 to 10.
-- Note: Program and output based on MySQL Workbench 8.0 CE
DELIMITER //
CREATE PROCEDURE PrintNumbers()
BEGIN
DECLARE i INT DEFAULT 1;
-- Create a temporary table to store numbers
CREATE TEMPORARY TABLE IF NOT EXISTS Numbers(Number INT);
TRUNCATE TABLE Numbers; -- Clear previous data if any
WHILE i <= 10 DO
INSERT INTO Numbers VALUES (i);
SET i = i + 1;
END WHILE;
-- Display all numbers at once
SELECT * FROM Numbers;
DROP TEMPORARY TABLE Numbers; -- Clean up
END //
DELIMITER ;
-- Call the procedure
CALL PrintNumbers();
34
OUTPUT:
35