Class 12 CS Code 083 SQL Solved Practical File Programs

Are you preparing your Class 12 Computer Science Practical File? Here’s a complete collection of SQL Solved Practical File Programs strictly based on the latest CBSE syllabus for Code 083. This resource is designed to help students complete their practical files with accuracy and ease while also preparing for board practical exams.

These solved SQL programs cover all essential database topics such as:

• Creating databases and tables
• Inserting, updating, and deleting records
• Using SELECT queries with WHERE, GROUP BY, HAVING, ORDER BY
• Applying aggregate functions like COUNT, SUM, AVG, MIN, and MAX
• Joining tables and using constraints

Each program includes the SQL query, expected output, and clear explanations, making it easier for students to understand the logic and apply it in their own files and exams.

Whether you’re working on your CBSE Practical File, revising for the final practical exam, or learning SQL fundamentals, this collection will help you practice the right way and score better.

Program 1: Create a database ExamsMantra in MYSQL and thereafter, create a table Employee with the following records:

ENO	NAME	DOJ	DOB	GENDER	DEPT
1001 1002 1003 1004 1005	Reena Sahir Morjina Zubair Seema	2013-09-02 2013-02-03 2012-12-03 2014-06-09 2013-11-18	1991-09-01 1987-03-20 1986-11-14 1985-06-23 1987-03-31	F M F M F	Production NULL Admin Sales Admin

Thereafter, write a program that performs the following tasks:

• Display all data from the table
• Display the record of employee whose ENO is accepted from the user
• Change the department of employee, Morjina to HR
• Delete the records of employees who have joined in the year 2012

-- Create the database
CREATE DATABASE ExamsMantra;

-- Use the database
USE ExamsMantra;

-- Create the Employee table
CREATE TABLE Employee (
    ENO INT PRIMARY KEY,
    NAME VARCHAR(50),
    DOJ DATE,
    DOB DATE,
    GENDER CHAR(1),
    DEPT VARCHAR(30)
);

-- Insert records
INSERT INTO Employee VALUES
(1001, 'Reena', '2013-09-02', '1991-09-01', 'F', 'Production'),
(1002, 'Sahir', '2013-02-03', '1987-03-20', 'M', NULL),
(1003, 'Morjina', '2012-12-03', '1986-11-14', 'F', 'Admin'),
(1004, 'Zubair', '2014-06-09', '1985-06-23', 'M', 'Sales'),
(1005, 'Seema', '2013-11-18', '1987-03-31', 'F', 'Admin');

import mysql.connector

# Connect to MySQL
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",  # Replace with your actual password
    database="ExamsMantra"
)
cursor = conn.cursor()

# 1. Display all data
print("All Employee Records:")
cursor.execute("SELECT * FROM Employee")
for row in cursor.fetchall():
    print(row)

# 2. Display employee by ENO
eno = int(input("\nEnter Employee Number to search: "))
cursor.execute("SELECT * FROM Employee WHERE ENO = %s", (eno,))
result = cursor.fetchone()
if result:
    print("Employee Record Found:", result)
else:
    print("No employee found with ENO", eno)

# 3. Update department of Morjina to HR
cursor.execute("UPDATE Employee SET DEPT = 'HR' WHERE NAME = 'Morjina'")
conn.commit()
print("\nUpdated Morjina's department to HR.")

# 4. Delete employees who joined in 2012
cursor.execute("DELETE FROM Employee WHERE YEAR(DOJ) = 2012")
conn.commit()
print("Deleted employees who joined in 2012.")

# Final Display
print("\nFinal Employee Table:")
cursor.execute("SELECT * FROM Employee")
for row in cursor.fetchall():
    print(row)

# Close connection
cursor.close()
conn.close()

Program 2: Consider the following Student table and write queries for performing the following tasks:

Roll_Num	Student_Name	Course_Name	Duration	Fee	Batch_Prefer
1 2 3 4 5 6	Bhaskar Dhyani Dakshinesh Saumit Raj Lovepreet Singh Aditya Jayant Jenny Fernandis	Web Development Machine Learning Office Tools Mobile App Python Programming Office Tools	3 4 NULL 3 6 4	20000 25000 50000 18000 45000 40000	Morning Evening Evening Morning Evening Evening

(i) Display the total number of records in the table.
Ans. SELECT COUNT(*) FROM student;

(ii) Display the average fee given by students.
Ans. SELECT AVG(Fee) FROM student;

(iii) Display the total duration of all courses.
Ans. SELECT SUM (Duration) FROM Student;

(iv) Display the count of records for each batch preference.
Ans. SELECT COUNT(*), Batch_prefer FROM student GROUP BY Batch _Prefer;

(v) Display the minimum and maximum fee paid by the student.
Ans. SELECT MIN (Fee), MAX (Fee) FROM student;

(vi) Display the average fee of records whose duration is known.
Ans. SELECT AVG(Fee) FROM student WHERE Duration IS NOT NULL;

Program 3: Consider the table TimeTable given below:

EventID	EName	EDate	ETime	EType
1001 1002 1003 1004 1005 1006	Appointment with Maple Birthday Rashmi Play-The Hamlet Board Meeting Flight-Mumbai Submit Tender	2023-11-10 2023-10-01 2023-09-30 2023-11-18 2023-07-11 2023-08-03	11 am 7 am 7 pm 2 pm 10:30 am 12 am	Official Personal Personal Official Official Official

Write SQL statements to perform the following tasks:

(i) Display the records in ascending order of event date.
Ans. SELECT FROM Schedule ORDER BY EDate;

(ii) Display name, date and time of personal events.
Ans. SELECT EName, EDate, ETime FROM Schedule WHERE Etype ‘Personal’;

(iii) Change the time of event named ‘Birthday Rashmi’ to 12 AM
Ans. UPDATE Schedule SET ETime ’12 AM’ WHERE EName ‘Birthday Rashmi’;

(iv) Add another attribute in the table with following specifications:
Attribute Name: Remarks
Data Type: varchar(50)
Ans. ALTER TABLE Schedule ADD Remarks varchar(50);

(v) Add the value of Remark as ‘Collect Tickets’ for the event id 1005.
Ans. UPDATE Schedule SET Remarks = ‘Collect Tickets’ WHERE EventId=1005;

(vi) Delete the events that are scheduled before November 2023.
Ans: DELETE FROM Schedule WHERE EDate < ‘2023-11-01’;

(vii) Remove the attribute Remarks from the table.
Ans. ALTER TABLE Schedule DROP Remarks;

Program 4: Consider the tables ‘Passengers’ and ‘Trains’ given below:

PNR	TNO	PNAME	GENDER	AGE	TRAVELDATE
P001 P002 P003 P004 P005 P006	112300 918273 650912 301582 403822 135790	AVIRAJ PRAGYA RAMESH SOUMIL SNEHA HEMA	M F M M F F	21 49 75 25 33 NULL	2023-10-10 2023-09-30 2023-09-20 2023-11-07 2023-12-25 2024-01-18

TNO	TName	Departure	Arrival
112300 135799 918273 403822	Centenary Express Passenger Jammu Mail Sushant Mail	Delhi Delhi Amritsar Ranchi	Mumbai Alwar Jammu Delhi

Write SQL statements to perform the following tasks:

(i) Display the names of trains that depart from Delhi
Ans. SELECT TName FROM Trains WHERE Departure = “Delhi”;

(ii) Display the Passenger Names, date of travel, and corresponding Train Names for all passengers.
Ans. SELECT PName, TravelDate, TName FROM Passengers P, Trains T WHERE P.TNO = T.TNO;

(iii) Display the count of male and female passengers.
Ans. SELECT Gender, COUNT(*) FROM Passengers GROUP BY Gender HAVING Gender IN (‘M’, ‘F’);

(iv) Display the name of the passenger whose age is not known.
Ans. SELECT PNAME FROM Passengers WHERE AGE IS NULL;

(v) Change the Arrival station of Sushant Mail to Patna
Ans. UPDATE Trains SET Arrival = “Patna” WHERE TName = “Sushant Mail”;

(vi) Delete the record of passengers who have travelled in the year 2023.
Ans. DELETE FROM Passengers WHERE TRAVELDATE LIKE “2023%”;