PRACTICAL FILE

COMPUTER SCIENCE
SESSION-: 2024-25

NAME: GAGAN BANSAL CLASS: XII CONFIDENT

BOARD ROLL NO: —

SCHOOL: KIIT World School

 INDEX
S. Title P Teacher’
n g s
o n signature
o
1 Write the queries for the following:-
a)Write a query to create a new database in the name of SCHOOL.
b)Write a query to open the database SCHOOL.
c) Write a query to create the table named STUDENT with the
following structure:
Field Data Type/size Constraint
Rollno Int Primary key
Name Varchar(25)
Class Char(5)
Section Char(1)
Gender Char(1)
Fees Int

d) Write a query to insert all records in the given table.

e) Write a query to display the roll number, name, and class from the
student table.
f) Write a query to display the names of Female students.
g) Write a query to change the fees of students to 170 whose roll number is 1.
h) Write a query to add a new column DOB of data type date.
i) Write a query to delete the details of students where the name starts with A.
2 To write queries based on the below tables stock and dealer:-
Create the table and insert the data as specified below:

a) To display the total unit price of all the products whose dcode is 102.
b) To display the product name of those items whose name contains “e” as
second alphabet in their name.
c) To display the details of all the products in the stock table in
descending order of stock date.
d) To display the maximum unit price of products for each dcode.
e) To display product name and dealer name from table stock and dealers.
3. Consider the following tables ITEM and CUSTOMER. Write SQL query for the
following statements:

a) To display the details of those customers whose city is Delhi.

b) To display the details of item whose price is in the range of 35000 to 55000(
Both values included).
c) To display the customer name, city from the table customer, and Item name
and price from the table item, with their corresponding matching I_ID.
d) To decrease the price of all the items by 2% in the table item.
e) To add another column to the item table named date_of_manuf.

4. Consider the following tables INTERIORS and ARRIVALS. Write SQL queries based
on the following tables:
 a)To show all information about the baby cot’s from the interiors table.
b)To list item name and type of those items, in which dateofstock is before 22/01/02
from the interiors table in descending order of itemname.
c) To display itemname and date stock of OFFICE TABLE items, in which the
discount is more than 15 from the interiors table.
d)To count the number of rows, whose type is “Double Bed” from the INTERIORS
table.
e)To insert a new row in the Arrivals table with the following data:
14,”True Indian”,”Office table”,”28/03/03”,15000,20
5 Consider the following tables ACTIVITY and COACH. Write SQL queries for the
following:

a)To display the names of all activities with their Acodes in descending order.
b)To display sum of prizemoney for the activities played in each of the stadium
separately.
c)To display the activity name and coach name from the above tables.
d)To display the content of all the activities for which scheduledate is earlier than
2004-01-01 in ascending order of participantsnum.
6. Kabir wants to write a program in Python to insert the following record in the table
name STUDENT in MySQL database, SCHOOL:
● Rno(Roll no)-integer
● Name-string
● DOB(Date of birth)-Date
● Fee-float
 Note the following to establish connectivity between Python and MySQL:
⮚ Username-root
⮚ Password-tiger
⮚ Host-localhost

The values of fields rno, name, DOB and fee are to be accepted by the user. Help
Kabir to write the program in Python.
7 The table employee contains the following structure:
● E-code-string
● E_name-string
● Sal-integer
● City-string
To display all the records from the employee table in Python.
Note the following to establish connectivity between Python and MySQL:
Username-root
Password-root
The table exists in a MySQL database named emp.
8 The table employee contains the following structure:
● E-code-string
● E_name-string
● Sal-integer
● City-string
To delete the record from thew table with Ecode as “E101”.
Note the following to establish connectivity between Python and MySQL:
Username-root
Password-root
The table exists in a MySQL database named emp.
9 The table employee contains the following structure:
● E-code-string
● E_name-string
● Sal-integer
● City-string
To increase the salary of the employee from the table with Ecode “E101” by 500 and
make the changes permanent.
Note the following to establish connectivity between Python and MySQL:
Username-root
Password-root
The table exists in a MySQL database named emp.
1 Amit wants to write a program in Python to display the details of the records where
0 marks>75 in the table name STUDENT in MySQL database, SCHOOL:
● Rno(Roll no)-integer
● Name-string
● Class-integer
● Marks-integer
Note the following to establish connectivity between Python and MySQL:
 ⮚ Username-root
⮚ Password-tiger
⮚ Host-localhost
Help Amit to write the program in Python.

SOLUTIONS

Q.NO SOLUTION

1 CREATE DATABASE SCHOOL;

USE SCHOOL;
CREATE TABLE STUDENT (
Rollno INT PRIMARY KEY,
Name VARCHAR(25),
Class CHAR(5),
Section CHAR(1),
Gender CHAR(1),
Fees INT
);
INSERT INTO STUDENT (Rollno, Name, Class, Section, Gender, Fees) VALUES
(1, 'John Doe', '10A', 'A', 'M', 150),
(2, 'Jane Smith', '10B', 'B', 'F', 150),
(3, 'Alice Johnson', '11A', 'A', 'F', 160),
(4, 'Bob Brown', '12A', 'C', 'M', 170);
SELECT Rollno, Name, Class FROM STUDENT;
SELECT Name FROM STUDENT WHERE Gender = 'F';
UPDATE STUDENT SET Fees = 170 WHERE Rollno = 1;
ALTER TABLE STUDENT ADD DOB DATE;
DELETE FROM STUDENT WHERE Name LIKE 'A%';

2 CREATE TABLE STOCK (

Pno INT PRIMARY KEY,
Pname VARCHAR(50),
Dcode INT,
Qty INT,
 UnitPrice INT,
StockDate DATE
);
INSERT INTO STOCK (Pno, Pname, Dcode, Qty, UnitPrice, StockDate) VALUES
(5005, 'Ball point pen', 102, 100, 10, '2021-03-31'),
(5003, 'Gel pen premium', 102, 150, 15, '2021-01-01'),
(5002, 'Pencil', 101, 125, 4, '2021-02-18'),
(5006, 'Scale', 101, 200, 6, '2020-01-01'),
(5001, 'Eraser', 102, 210, 3, '2020-03-19'),
(5004, 'Sharpner', 102, 60, 5, '2020-12-09'),
(5009, 'Gel pen classic', 103, 160, 8, '2022-01-19');
CREATE TABLE DEALERS (
Dcode INT PRIMARY KEY,
Dname VARCHAR(50)
);
INSERT INTO DEALERS (Dcode, Dname) VALUES
(101, 'Sakthi Stationeries'),
(103, 'Classic Stationeries'),
(102, 'Indian Book House');
SELECT SUM(UnitPrice * Qty) AS TotalUnitPrice
FROM STOCK
WHERE Dcode = 102;
SELECT Pname
FROM STOCK
WHERE Pname LIKE '_e%';
SELECT *
FROM STOCK
ORDER BY StockDate DESC;
SELECT Dcode, MAX(UnitPrice) AS MaxUnitPrice
FROM STOCK
GROUP BY Dcode;
SELECT S.Pname, D.Dname
FROM STOCK S
JOIN DEALERS D ON S.Dcode = D.Dcode;

3 CREATE TABLE ITEM (

i_ID VARCHAR(10) PRIMARY KEY,
ItemName VARCHAR(50),
Manufacturer VARCHAR(50),
Price INT
);
INSERT INTO ITEM (i_ID, ItemName, Manufacturer, Price) VALUES
('PC01', 'Personal Computer', 'ABC', 35000),
('LC05', 'Laptop', 'ABC', 55000),
('PC03', 'Personal Computer', 'XYZ', 32000),
('PC06', 'Personal Computer', 'COMP', 37000),
('LC03', 'Laptop', 'PQR', 57000);
CREATE TABLE CUSTOMER (
C_ID INT PRIMARY KEY,
CustomerName VARCHAR(50),
City VARCHAR(50),
I_ID VARCHAR(10),
FOREIGN KEY (I_ID) REFERENCES ITEM(i_ID)
);
INSERT INTO CUSTOMER (C_ID, CustomerName, City, I_ID) VALUES
 (1, 'N Roy', 'Delhi', 'LC03'),
(6, 'H Singh', 'Mumbai', 'PC03'),
(12, 'R Pandey', 'Delhi', 'PC06'),
(15, 'C Sharma', 'Delhi', 'LC03'),
(16, 'K Agarwal', 'Banglore', 'PC01');
SELECT *
FROM CUSTOMER
WHERE City = 'Delhi';
SELECT *
FROM ITEM
WHERE Price BETWEEN 35000 AND 55000;
SELECT C.CustomerName, C.City, I.ItemName, I.Price
FROM CUSTOMER C
JOIN ITEM I ON C.I_ID = I.i_ID;
UPDATE ITEM
SET Price = Price * 0.98;
ALTER TABLE ITEM
ADD date_of_manuf DATE;

4 CREATE TABLE INTERIORS (

NO INT PRIMARY KEY,
ITEMNAME VARCHAR(50),
TYPE VARCHAR(50),
DATEOFSTOCK DATE,
PRICE INT,
DISCOUNT INT
);
INSERT INTO INTERIORS (NO, ITEMNAME, TYPE, DATEOFSTOCK, PRICE, DISCOUNT)
VALUES
(1, 'Red rose', 'Double Bed', '2002-02-23', 32000, 15),
(2, 'Soft touch', 'Baby cot', '2002-01-20', 9000, 10),
(3, 'Jerry\'s home', 'Baby cot', '2002-02-19', 8500, 10),
(4, 'Rough wood', 'Office Table', '2002-01-01', 20000, 20),
(5, 'Comfort zone', 'Double Bed', '2002-01-12', 15000, 20),
(6, 'Jerry look', 'Baby cot', '2002-02-24', 700, 19),
(7, 'Lion king', 'Office Table', '2002-02-20', 16000, 20);
CREATE TABLE ARRIVALS (
NO INT PRIMARY KEY,
ITEMNAME VARCHAR(50),
TYPE VARCHAR(50),
DATEOFSTOCK DATE,
PRICE INT,
DISCOUNT INT
);
INSERT INTO ARRIVALS (NO, ITEMNAME, TYPE, DATEOFSTOCK, PRICE, DISCOUNT)
VALUES
(11, 'While wood', 'Double Bed', '2003-03-23', 20000, 20),
(12, 'James 007', 'Sofa', '2003-02-20', 15000, 15),
(13, 'Tom look', 'Baby cot', '2003-02-21', 7000, 10);
SELECT *
FROM INTERIORS
WHERE TYPE = 'Baby cot';
SELECT ITEMNAME, TYPE
FROM INTERIORS
WHERE DATEOFSTOCK < '2002-01-22'
 ORDER BY ITEMNAME DESC;
SELECT ITEMNAME, DATEOFSTOCK
FROM INTERIORS
WHERE TYPE = 'Office Table' AND DISCOUNT > 15;
SELECT COUNT(*) AS DoubleBedCount
FROM INTERIORS
WHERE TYPE = 'Double Bed';
INSERT INTO ARRIVALS (NO, ITEMNAME, TYPE, DATEOFSTOCK, PRICE, DISCOUNT)
VALUES
(14, 'True Indian', 'Office table', '2003-03-28', 15000, 20);

5 CREATE TABLE ACTIVITY (

Acode INT PRIMARY KEY,
ActivityName VARCHAR(50),
Stadium VARCHAR(50),
ParticipantsNum INT,
PrizeMoney INT,
ScheduleDate DATE
);
INSERT INTO ACTIVITY (Acode, ActivityName, Stadium, ParticipantsNum, PrizeMoney,
ScheduleDate) VALUES
(1001, 'Relay 100 x 4', 'Star Annex', 16, 10000, '2004-01-23'),
(1002, 'High jump', 'Star Annex', 10, 12000, '2003-12-12'),
(1003, 'Shot Put', 'Super Power', 12, 8000, '2004-02-14'),
(1005, 'Long Jump', 'Star Annex', 12, 9000, '2004-01-01'),
(1008, 'Discuss Throw', 'Super Power', 10, 15000, '2004-03-19');
CREATE TABLE COACH (
Pcode INT PRIMARY KEY,
Name VARCHAR(50),
Acode INT,
FOREIGN KEY (Acode) REFERENCES ACTIVITY(Acode)
);
INSERT INTO COACH (Pcode, Name, Acode) VALUES
(1, 'Ahmad Hussain', 1001),
(2, 'Ravinder', 1008),
(3, 'Janila', 1001),
(4, 'Naaz', 1003);
SELECT Acode, ActivityName
FROM ACTIVITY
ORDER BY Acode DESC;
SELECT Stadium, SUM(PrizeMoney) AS TotalPrizeMoney
FROM ACTIVITY
GROUP BY Stadium;
SELECT A.ActivityName, C.Name AS CoachName
FROM ACTIVITY A
JOIN COACH C ON A.Acode = C.Acode;
SELECT *
FROM ACTIVITY
WHERE ScheduleDate < '2004-01-01'
ORDER BY ParticipantsNum ASC;

6 import mysql.connector
from mysql.connector import Error
 def insert_student(rno, name, dob, fee):

connection = mysql.connector.connect(
host='localhost',
database='SCHOOL',
user='root',
password='tiger'
)

if connection.is_connected():
cursor = connection.cursor()

insert_query = """INSERT INTO STUDENT (Rollno, Name, DOB, Fee)

VALUES (%s, %s, %s, %s)"""
record = (rno, name, dob, fee)

cursor.execute(insert_query, record)
connection.commit()

print("Record inserted successfully into STUDENT table")

except Error as e:
print("Error while connecting to MySQL", e)

if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")

rno = int(input("Enter Roll Number: "))

name = input("Enter Name: ")
dob = input("Enter Date of Birth (YYYY-MM-DD): ")
fee = float(input("Enter Fee: "))

insert_student(rno, name, dob, fee)

7 import mysql.connector

connection = mysql.connector.connect(
host='localhost',
user='root',
password='root',
database='emp'
)

cursor = connection.cursor()

query = "SELECT * FROM employee"

cursor.execute(query)

records = cursor.fetchall()
 for record in records:
print(record)

cursor.close()
connection.close()

8 import mysql.connector

connection = mysql.connector.connect(
host='localhost',
user='root',
password='root',
database='emp'
)

cursor = connection.cursor()

delete_query = "DELETE FROM employee WHERE E-code = 'E101'"

cursor.execute(delete_query)

connection.commit()

print("Record deleted successfully")

except mysql.connector.Error as error:

print("Failed to delete record from MySQL table: {}".format(error))

if cursor:
cursor.close()
if connection:
connection.close()

9 import mysql.connector
connection = mysql.connector.connect(
host='localhost',
user='root',
password='root',
database='emp'
)
cursor = connection.cursor()
update_query = """
UPDATE employee
SET Sal = Sal + 500
WHERE E-code = 'E101'
"""

cursor.execute(update_query)

connection.commit()

print("Salary updated successfully")

 except mysql.connector.Error as error:
print("Failed to update record in MySQL table: {}".format(error))

if cursor:
cursor.close()
if connection:
connection.close()

10 import mysql.connector

def get_students_with_high_marks():

connection = mysql.connector.connect(
host='localhost',
user='root',
password='tiger',
database='SCHOOL'
)

cursor = connection.cursor()

query = "SELECT * FROM STUDENT WHERE Marks > 75"

cursor.execute(query)

result = cursor.fetchall()

if result:
print("Records with marks greater than 75:")
for row in result:
print(f"Roll No: {row[0]}, Name: {row[1]}, Class: {row[2]}, Marks: {row[3]}")
else:
print("No records found with marks greater than 75.")

except mysql.connector.Error as err:

print(f"Error: {err}")

cursor.close()
connection.close()
print("MySQL connection is closed.")

get_students_with_high_marks()