SQL Practical File
Name:
Class:
Subject: Database Management System
Practical No.: 1
Introduction to SQL
SQL (Structured Query Language) is used to manage and manipulate
relational databases. - DDL: Data Definition Language – Create, Alter, Drop
tables. - DML: Data Manipulation Language – Insert, Update, Delete records.
- DQL: Data Query Language – Select, Where, Order By. - DCL: Data Control
Language – Grant, Revoke. - TCL: Transaction Control – Commit, Rollback.
Practical Exercises
Original Exercises
1. Creating a Database
CREATE DATABASE CollegeDB;
Output: Database CollegeDB created.
2. Creating Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Gender VARCHAR(10),
Department VARCHAR(30)
);
Output: Table Students created successfully.
3. Inserting Data
INSERT INTO Students VALUES
(1, 'Nisha', 20, 'Female', 'CSE'),
(2, 'Rahul', 21, 'Male', 'ECE'),
(3, 'Anjali', 19, 'Female', 'IT'),
(4, 'Vikram', 22, 'Male', 'ME');
Output: 4 rows inserted.
4. Displaying Records
SELECT * FROM Students;
Sample Output: Table displaying StudentID, Name, Age, Gender,
Department.
5. Updating Records
UPDATE Students
SET Age = 21
WHERE Name = 'Nisha';
Output: Age updated for Nisha.
6. Deleting Records
DELETE FROM Students
WHERE StudentID = 4;
Output: 1 row deleted (Vikram).
7. Using WHERE & ORDER BY
SELECT * FROM Students
WHERE Gender = 'Female'
ORDER BY Age DESC;
Output: Displays female students sorted by age descending.
8. Aggregate Functions
SELECT COUNT(*) AS TotalStudents FROM Students;
SELECT AVG(Age) AS AverageAge FROM Students;
Output: TotalStudents: 3, AverageAge: 20
9. Inner Join
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(30),
HOD VARCHAR(50)
);
INSERT INTO Departments VALUES
(1, 'CSE', 'Dr. Sharma'),
(2, 'ECE', 'Dr. Gupta'),
(3, 'IT', 'Dr. Mehta');
SELECT [Link], [Link], [Link]
FROM Students
INNER JOIN Departments
ON [Link] = [Link];
Output: Students’ names with their corresponding HOD.
10. Subquery Example
SELECT Name FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);
Output: Displays student name with maximum age.
11. View Example
CREATE VIEW FemaleStudents AS
SELECT Name, Department FROM Students
WHERE Gender='Female';
Output: View FemaleStudents created.
12. Index Example
CREATE INDEX idx_studentname
ON Students(Name);
Output: Index created for faster search on Name.
Additional Exercises
1. Create Courses Table
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
Duration VARCHAR(20),
Department VARCHAR(30)
);
Output: Table Courses created.
2. Insert Courses Data
INSERT INTO Courses VALUES
(101, 'Database Management', '6 Months', 'CSE'),
(102, 'Digital Electronics', '4 Months', 'ECE'),
(103, 'Networking', '5 Months', 'IT');
Output: 3 rows inserted.
3. Select Specific Columns
SELECT Name, Department FROM Students;
Output: Displays Name and Department only.
4. DISTINCT Departments
SELECT DISTINCT Department FROM Students;
Output: Displays unique departments.
5. LIKE Operator
SELECT * FROM Students
WHERE Name LIKE 'A%';
Output: Students whose name starts with ‘A’.
6. BETWEEN Operator
SELECT * FROM Students
WHERE Age BETWEEN 20 AND 22;
Output: Students aged 20 to 22.
7. ORDER BY Descending
SELECT * FROM Students
ORDER BY Age DESC;
Output: Students sorted by age descending.
8. COUNT with GROUP BY
SELECT Department, COUNT(*) AS StudentCount
FROM Students
GROUP BY Department;
Output: Number of students per department.
9. Inner Join Students & Courses
SELECT [Link], [Link]
FROM Students
INNER JOIN Courses
ON [Link] = [Link];
Output: Students with their course names.
10. ALTER Students Table
ALTER TABLE Students
ADD Email VARCHAR(50);
Output: Column Email added successfully.
Conclusion
This practical file covers database creation, table creation, DML operations,
select queries, ordering, aggregate functions, joins, subqueries, views,
indexes, and additional SQL concepts like DISTINCT, LIKE, BETWEEN, GROUP
BY, and ALTER TABLE. It provides a complete understanding of SQL
operations for managing a college database.
