ZENITH PUBLIC SCHOOL

Affiliated to C.B.S.E. NEW DELHI, +2 Level

Affiliation No.: 330882

COMPUTER SCIENCE PROJECT

(2024-25)

Submitted by : AYUSH KESHRI(XII)

Roll no.:

Teacher’s Signature

Examiner’s Signature Principal’s Signature

 INDEX
PROGRAM DATE SIGN

Program to read and display file content line by 27-12-2024

line with each word separated by “ #”
Program to read the content of file and display 27-12-2024
the total number of consonants, uppercase,
vowels and lowercase characters.
Program to read the content of file line by line 27-12-2024
and write it to another file except for the lines
contains “a” letter in it.
Program to store students’ details like 27-12-2024
admission number, roll number, name and
percentage in a dictionary and display
information on the basis of admission number.
Program to create binary file to store Roll no 27-12-2024
and Name, Search any Roll no and display name
if Roll no found otherwise “Roll no not found”

Program to create binary file to store Roll no, 27-12-2024

Name and Marks and update marks of entered
Roll no.
Program to generate random number 1-6, 27-12-2024
simulating a dice.
Program to implement Stack in Python using 27-12-2024
List.
Create a CSV file by entering user-id and 27-12-2024
password, read and search the password for
given user- id.
To write SQL-Queries for the following 27-12-2024
Questions based on the given table.
To write SQL-Queries for the following 27-12-2024
Questions based on the given table.
To write Queries for the following Questions 27-12-2024
based on the given two table.
Program to connect with database and store 27-12-2024
record of employee and display records.
Program to connect with database and search 27-12-2024
employee number in table employee and
display record, if empno not found display
appropriate message.
Perform all the operations (Insert, Update, 27-12-2024
Delete, Display) with reference to table
‘students’ through MySQL-Python connectivity.
 Program 1:Program to read and display file content line by
line with each word separated by “ #”

PYTHON SOURCE CODE:

# Program to read and display file content line by line with each word #separated
by "#"

# Open the file in read mode

file_name = “program1.txt"
try:
with open(file_name, "r") as file:
print("File content with words separated by '#':")
for line in file:
# Remove newline characters and split words
words = line.strip().split()
# Join words with '#'
modified_line = " # ".join(words)
print(modified_line)
except FileNotFoundError:
print("File not found. Please check the file name and try again.")
Note : If content of file is-
Zenith Public School is Best.
Teachers of this school are amazing.
OUTPUT:
File content with words separated by '#':
Zenith # Public # School # is # Best.
Teachers # of # this # school # are # amazing.
 Program 2:Program to read the content of file and display
the total number of consonants, uppercase,
vowels and lowercase characters.

PYTHON SOURCE CODE:

# Program to count vowels, consonants, uppercase and lowercase letters

file_name = “program1.txt" # File name

vowels = "aeiouAEIOU"
consonants = "bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ"
vowel_count = consonant_count = uppercase_count = lowercase_count = 0

try:
with open(file_name, "r") as file:
content = file.read()
for char in content:
if char in vowels:
vowel_count += 1
elif char in consonants:
consonant_count += 1
if char.isupper():
uppercase_count += 1
if char.islower():
lowercase_count += 1

print(f"Vowels: {vowel_count}")
print(f"Consonants: {consonant_count}")
print(f"Uppercase: {uppercase_count}")
print(f"Lowercase: {lowercase_count}")

except FileNotFoundError:
print("File not found. Please check the file name.")

Note : If content of file is-

Zenith Public School is Best.
Teachers of this school are amazing.
OUTPUT:
Vowels: 20
Consonants: 34
Uppercase: 5
Lowercase: 49
 Program 3:Program to read the content of file line by line
and write it to another file except for the lines
contains “a” letter in it.
PYTHON SOURCE CODE:
# Program to read the content of a file line by line and write to another file except for
#lines containing "a"

input_file = "input.txt" # Source file

output_file = "output.txt" # Destination file

try:
with open(input_file, "r") as infile, open(output_file, "w") as outfile:
for line in infile:
if "a" not in line: # Skip lines containing "a"
outfile.write(line)

print(f"Content written to {output_file} excluding lines with 'a'.")

except FileNotFoundError:
print("File not found. Please check the file name.")

Note : If input file is-

jaldi kar kl subha punvail nikalne ka h.
kya gunda banega re tu.
The red bird flies over the green hill.
OUTPUT:
Content written to output.txt excluding lines with 'a'.
Output file:
The red bird flies over the green hill.
 Program 4: Program to store students’ details like
admission number, roll number, name and
percentage in a dictionary and display
information on the basis of admission
number.
PYTHON SOURCE CODE:
# Program to store student details in a dictionary and display info by admission
#number

students = {}

# Input the number of students

n = int(input("Enter the number of students: "))

# Adding student details to the dictionary

for i in range(n):
print(f"\nEnter details for student {i+1}:")
admission_no = input("Admission Number: ")
roll_no = input("Roll Number: ")
name = input("Name: ")
percentage = float(input("Percentage: "))
students[admission_no] = {
"Roll Number": roll_no,
"Name": name,
"Percentage": percentage
}

# Searching for a student's details by admission number

search_admission_no = input("\nEnter the admission number to search: ")

if search_admission_no in students:
print("\nStudent Details:")
for key, value in students[search_admission_no].items():
print(f"{key}: {value}")
else:
print("Admission number not found!")
OUTPUT:
Enter the number of students: 4

Enter details for student 1:

Admission Number: A100
Roll Number: 1
Name: PRIYANSHU KUMAR
Percentage: 92

Enter details for student 2:

Admission Number: A101
Roll Number: 2
Name: AYUSH RAJ
Percentage: 89.4

Enter details for student 3:

Admission Number: A102
Roll Number: 3
Name: AYUSH KESHRI
Percentage: 89.4

Enter details for student 4:

Admission Number: A103
Roll Number: 4
Name: ARPIT KR. MANDAL
Percentage: 89.2

Enter the admission number to search: A101

Student Details:
Roll Number: 2
Name: AYUSH RAJ
Percentage: 89.4
 Program 5:Program to create binary file to store Roll no and
Name, Search any Roll no and display name if
Roll no found otherwise “Roll no not found”

PYTHON SOURCE CODE:

import pickle

# Function to create and store data in a binary file

def create_file(filename):
n = int(input("Enter the number of students: "))
with open(filename, "wb") as file:
for _ in range(n):
roll_no = input("Enter Roll Number: ")
name = input("Enter Name: ")
data = {"Roll No": roll_no, "Name": name}
pickle.dump(data, file)
print("Data successfully written to the file.")

# Function to search for a roll number in the binary file

def search_roll_no(filename):
roll_no_to_search = input("Enter Roll Number to search: ")
found = False
with open(filename, "rb") as file:
try:
while True:
record = pickle.load(file)
if record["Roll No"] == roll_no_to_search:
print(f"Name: {record['Name']}")
found = True
break
except EOFError:
if not found:
print("Roll number not found.")

# Main program
file_name = "students.dat"

print("1. Create File")

print("2. Search Roll Number")
choice = int(input("Enter your choice: "))
if choice == 1:
create_file(file_name)
elif choice == 2:
try:
search_roll_no(file_name)
except FileNotFoundError:
print("File not found. Please create the file first.")
else:
print("Invalid choice.")
OUTPUT:
Creating file:
1. Create File
2. Search Roll Number
Enter your choice: 1
Enter the number of students: 4
Enter Roll Number: 1
Enter Name: PRIYANSHU KUMAR
Enter Roll Number: 2
Enter Name: AYUSH RAJ
Enter Roll Number: 3
Enter Name: AYUSH KESHRI
Enter Roll Number: 4
Enter Name: ARPIT KR. MANDAL
Data successfully written to the file.
Searching for a roll no.:
1. Create File
2. Search Roll Number
Enter your choice: 2
Enter Roll Number to search: 3
Name: AYUSH KESHRI
 Program 6: Program to create binary file to store Roll no,
Name and Marks and update marks of entered
Roll no.

PYTHON SOURCE CODE:

import pickle

# Function to create and store data in a binary file

def create_file(filename):
n = int(input("Enter the number of students: "))
with open(filename, "wb") as file:
for _ in range(n):
roll_no = input("Enter Roll Number: ")
name = input("Enter Name: ")
marks = float(input("Enter Marks: "))
data = {"Roll No": roll_no, "Name": name, "Marks": marks}
pickle.dump(data, file)
print("Data successfully written to the file.")

# Function to update marks of a given Roll No

def update_marks(filename):
roll_no_to_update = input("Enter Roll Number to update marks: ")
found = False
temp_records = []

with open(filename, "rb") as file:

try:
while True:
record = pickle.load(file)
if record["Roll No"] == roll_no_to_update:
new_marks = float(input(f"Enter new marks for {record['Name']}: "))
record["Marks"] = new_marks
found = True
temp_records.append(record)
except EOFError:
pass

if found:
with open(filename, "wb") as file:
for record in temp_records:
pickle.dump(record, file)
print("Marks updated successfully.")
else:
print("Roll number not found.")

# Main program
file_name = "students.dat"

print("1. Create File")

print("2. Update Marks")
choice = int(input("Enter your choice: "))

if choice == 1:
create_file(file_name)
elif choice == 2:
try:
update_marks(file_name)
except FileNotFoundError:
print("File not found. Please create the file first.")
else:
print("Invalid choice.")

OUTPUT:
Creating file: Updating marks:
1. Create File 1. Create File
2. Update Marks 2. Update Marks
Enter your choice: 1 Enter your choice: 2
Enter the number of students: 4 Enter Roll Number to update marks: 4
Enter Roll Number: 1 Enter new marks for ARPIT KR. MANDAL: 446
Enter Name: PRIYANSHU KUMAR Marks updated successfully.
Enter Marks: 460
Enter Roll Number: 2
Enter Name: AYUSH RAJ
Enter Marks: 447
Enter Roll Number: 3
Enter Name: AYUSH KESHRI
Enter Marks: 447
Enter Roll Number: 4
Enter Name: ARPIT KR. MANDAL
Enter Marks: 447
Data successfully written to the file.
 Program 7: Program to generate random number 1-6, simulating
a dice.

PYTHON SOURCE CODE:

import random

def roll_dice():
return random.randint(1, 6)

while True:
print("=" * 55)
print("*********************** Rolling Dice ************************")
print("=" * 55)

dice_value = roll_dice()
print(f"\nYeh aya: {dice_value}", end=" ")
if dice_value == 6:
print("...... Wah bhai wah!!!! 🎉")
else:
print("...... Kya gunda banega re tu!")

# Asking if the user wants to roll again

choice = input("\nAur Khelega? (Y/N): ").strip().lower()
if choice != 'y':
print("\nChal bhag yha se nhi khelega to!")
print("=" * 55)
break
OUTPUT:
=======================================================
*********************** Rolling Dice ************************
=======================================================

Yeh aya: 5 ...... Kya gunda banega re tu!

Aur Khelega? (Y/N): Y

=======================================================
*********************** Rolling Dice ************************
=======================================================

Yeh aya: 6 ...... Wah bhai wah!!!! 🎉

Aur Khelega? (Y/N): N

Chal bhag yha se nhi khelega to!

=======================================================
Program 8: Program to implement Stack in Python using List.

PYTHON SOURCE CODE:

# Stack implementation using a list
class Stack:
def __init__(self):
self.stack = []

# Push element onto the stack

def push(self, item):
self.stack.append(item)
print(f"{item} pushed onto stack.")

# Pop element from the stack

def pop(self):
if self.is_empty():
print("Stack is empty. Cannot pop.")
else:
item = self.stack.pop()
print(f"{item} popped from stack.")
return item

# Peek at the top element of the stack

def peek(self):
if self.is_empty():
print("Stack is empty. Nothing to peek.")
else:
print(f"Top of the stack is: {self.stack[-1]}")
return self.stack[-1]

# Check if the stack is empty

def is_empty(self):
return len(self.stack) == 0

# Display the stack

def display(self):
if self.is_empty():
print("Stack is empty.")
else:
print("Stack elements (top to bottom):")
for item in reversed(self.stack):
print(item)
# Main program
stack = Stack()

while True:
print("\n1. Push\n2. Pop\n3. Peek\n4. Display\n5. Exit")
choice = int(input("Enter your choice: "))

if choice == 1:
element = input("Enter element to push: ")
stack.push(element)
elif choice == 2:
stack.pop()
elif choice == 3:
stack.peek()
elif choice == 4:
stack.display()
elif choice == 5:
print("Exiting program. Goodbye!")
break
else:
print("Invalid choice. Please try again.")
OUTPUT:
1. Push
2. Pop
3. Peek
4. Display
5. Exit
Enter your choice: 1
Enter element to push: 10
10 pushed onto stack.

1. Push
2. Pop
3. Peek
4. Display
5. Exit
Enter your choice: 1
Enter element to push: 20
20 pushed onto stack.
1. Push 1. Push
2. Pop 2. Pop
3. Peek 3. Peek
4. Display 4. Display
5. Exit 5. Exit
Enter your choice: 1 Enter your choice: 4
Enter element to push: 30 Stack elements (top to bottom):
30 pushed onto stack. 30
20
10
1. Push
2. Pop
3. Peek 1. Push
4. Display 2. Pop
5. Exit 3. Peek
Enter your choice: 1 4. Display
Enter element to push: 40 5. Exit
40 pushed onto stack. Enter your choice: 5
Exiting program. Goodbye!

1. Push
2. Pop
3. Peek
4. Display
5. Exit
Enter your choice: 2
40 popped from stack.

1. Push
2. Pop
3. Peek
4. Display
5. Exit
Enter your choice: 3
Top of the stack is: 30
 Program 9: Create a CSV file by entering user-id and
password, read and search the password for
given user- id.

PYTHON SOURCE CODE:

import csv

# Function to create and add user data to a CSV file

def create_csv_file(filename):
n = int(input("Enter the number of users: "))
with open(filename, "w", newline="") as file:
writer = csv.writer(file)
writer.writerow(["User ID", "Password"]) # Header row
for _ in range(n):
user_id = input("Enter User ID: ")
password = input("Enter Password: ")
writer.writerow([user_id, password])
print("CSV file created successfully.")

# Function to search for a password by user ID

def search_password(filename):
search_user_id = input("Enter User ID to search: ")
with open(filename, "r") as file:
reader = csv.reader(file)
next(reader) # Skip the header row
for row in reader:
if row[0] == search_user_id:
print(f"Password for User ID '{search_user_id}' is: {row[1]}")
return
print("User ID not found.")

# Main program
file_name = "user_data.csv"

print("1. Create CSV File")

print("2. Search Password")
choice = int(input("Enter your choice: "))

if choice == 1:
create_csv_file(file_name)
elif choice == 2:
try:
search_password(file_name)
except FileNotFoundError:
print("File not found. Please create the file first.")
else:
print("Invalid choice.")
OUTPUT:
Creating CSV file:
1. Create CSV File
2. Search Password
Enter your choice: 1
Enter the number of users: 2
Enter User ID: ayushkeshri
Enter Password: ayush@keshri
Enter User ID: ayushraj
Enter Password: takliya
CSV file created successfully.
Searching for password:
1. Create CSV File
2. Search Password
Enter your choice: 2
Enter User ID to search: ayushraj
Password for User ID 'ayushraj' is: takliya
 Program 10: To write SQL-Queries for the following
Questions based on the given table

Roll Name Gender Age Dept DOA Fees

no.
1111 Arun M 24 COMPUTER 1997-01-10 120
22. 2 Ankit M 21 HISTORY 1998-03-24 200
3. 3 Anu F 20 HINDI 1996-12-12 300
4. 4 Bala M 19 NULL 1999-07-01 400
5. 5 Charan M 18 HINDI 1997-09-05 250
6. 6 Deepa F 19 HISTORY 1997-06-27 300
7. 7 Dinesh M 22 COMPUTER 1997-02-25 210
88 8 Usha F 23 NULL 1997-07-31 200

a) Write a Query to Create a new database in the name of

"STUDENTS”
ANSWER- mysql> CREATE DATABASE STUDENTS;
b) Write a Query to Open the database "STUDENTS”
ANSWER- mysql> USE STUDENTS;
c) Write a Query to create the above table called: Info
ANSWER- mysql> CREATE TABLE Info (
-> Roll_no int Primary key,
-> Name varchar(10),
-> Gender varchar(3),
-> Age int,
-> Dept varchar(15),
-> DOA date,
-> Fees int);
d) Write a Query to list all the existing database names.
ANSWER- mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| hotel_sunset |
| information_schema |
| mysql |
| performance_schema |
| report_card |
| STUDENTS |
| sys |
+--------------------+

e) Write a Query to List all the tables that exists in the current
database.
ANSWER- mysql> SHOW TABLES;
+--------------------+
| Tables_in_students |
+--------------------+
| Info |
+--------------------+

f) Write a Query to insert all the rows of above table into Info table.
ANSWER-
INSERT INTO Info Values(1,’Arun’,’M’,24,’COMPUTER’,’1997-01-10’,120);
INSERT INTO Info Values(2,’Ankit’,’M’,21,’HISTORY’,’1998-03-24’,200));
INSERT INTO Info Values(3,’Anu’,’F’,20,’HINDI’,’1996-12-12’,300);
INSERT INTO Info Values(4,’Bala’,’M’,19,’NULL’,’1999-07-01’,400);
INSERT INTO Info Values(5,’Charan’,’M’,18,’HINDI’,’1997-09-05’,250);
INSERT INTO Info Values(6,’Deepa’,’F’,19,’HISTORY’,’1997-06-27’,300);
INSERT INTO Info Values(7,’Dinesh’,’M’,22,’COMPUTER’,’1997-02-25’,210);
INSERT INTO Info Values(8,’Usha’,’F’,23,’NULL’,’1997-07-31’,200);
g) Write a Query to display all the details of the Employees from the
above table ‘Info’.
ANSWER-
mysql> SELECT * FROM Info;

OUTPUT:
+---------+--------+--------+------+----------+------------+------+
| Roll_no | Name | Gender | Age | Dept | DOA | Fees |
+---------+--------+--------+------+----------+------------+------+
| 1 | Arun | M | 24 | COMPUTER | 1997-01-10 | 120 |
| 2 | Ankit | M | 21 | HISTORY | 1998-03-24 | 200 |
| 3 | Anu | F | 20 | HINDI | 1996-12-12 | 300 |
| 4 | Bala | M | 19 | NULL | 1999-07-01 | 400 |
| 5 | Charan | M | 18 | HINDI | 1997-09-05 | 250 |
| 6 | Deepa | F | 19 | HISTORY | 1997-06-27 | 300 |
| 7 | Dinesh | M | 22 | COMPUTER | 1997-02-25 | 210 |
| 8 | Usha | F | 23 | NULL | 1997-07-31 | 200 |
+---------+--------+--------+------+----------+------------+------+

h) Write a query to Rollno, Name and Department of the students

from STU table.
ANSWER- mysql> SELECT Roll_no,Name,Dept FROM Info;
OUTPUT:
+---------+--------+----------+
| Roll_no | Name | Dept |
+---------+--------+----------+
| 1 | Arun | COMPUTER |
| 2 | Ankit | HISTORY |
| 3 | Anu | HINDI |
| 4 | Bala | NULL |
| 5 | Charan | HINDI |
| 6 | Deepa | HISTORY |
| 7 | Dinesh | COMPUTER |
| 8 | Usha | NULL |
+---------+--------+----------+
 Program 11: To write SQL- Queries for the following Questions
based on the given table:

Roll Name Gender Age Dept DOA Fees

no.
1111 Arun M 24 COMPUTER 1997-01-10 120
22. 2 Ankit M 21 HISTORY 1998-03-24 200
3. 3 Anu F 20 HINDI 1996-12-12 300
4. 4 Bala M 19 NULL 1999-07-01 400
5. 5 Charan M 18 HINDI 1997-09-05 250
6. 6 Deepa F 19 HISTORY 1997-06-27 300
7. 7 Dinesh M 22 COMPUTER 1997-02-25 210
88 8 Usha F 23 NULL 1997-07-31 200

a) Write a Query to delete the details of Roll number is 8.

ANSWER-
mysql> DELETE FROM Info WHERE Roll_no=8;
Table After deletion:
+---------+--------+--------+------+----------+------------+------+
| Roll_no | Name | Gender | Age | Dept | DOA | Fees |
+---------+--------+--------+------+----------+------------+------+
| 1 | Arun | M | 24 | COMPUTER | 1997-01-10 | 120 |
| 2 | Ankit | M | 21 | HISTORY | 1998-03-24 | 200 |
| 3 | Anu | F | 20 | HINDI | 1996-12-12 | 300 |
| 4 | Bala | M | 19 | NULL | 1999-07-01 | 400 |
| 5 | Charan | M | 18 | HINDI | 1997-09-05 | 250 |
| 6 | Deepa | F | 19 | HISTORY | 1997-06-27 | 300 |
| 7 | Dinesh | M | 22 | COMPUTER | 1997-02-25 | 210 |
+---------+--------+--------+------+----------+------------+------+

b) Write a Query to change the fess of Student to 170 whose Roll

number is 1, if the existing Fess is less than 130.
ANSWER- mysql> UPDATE Info SET Fees=170 WHERE Roll_no=1 AND Fees<130;
UPDATED INFO:
+---------+------+--------+------+----------+------------+------+
| Roll_no | Name | Gender | Age | Dept | DOA | Fees |
+---------+------+--------+------+----------+------------+------+
| 1 | Arun | M | 24 | COMPUTER | 1997-01-10 | 170 |
+---------+------+--------+------+----------+------------+------+
c) Write a Query to add a new column Area of type varchar in
table Info.
ANSWER- mysql> ALTER TABLE Info ADD Area VARCHAR(20);
NEW TABLE-
+---------+--------+--------+------+----------+------------+------+------+
| Roll_no | Name | Gender | Age | Dept | DOA | Fees | Area |
+---------+--------+--------+------+----------+------------+------+------+
| 1 | Arun | M | 24 | COMPUTER | 1997-01-10 | 170 | NULL |
| 2 | Ankit | M | 21 | HISTORY | 1998-03-24 | 200 | NULL |
| 3 | Anu | F | 20 | HINDI | 1996-12-12 | 300 | NULL |
| 4 | Bala | M | 19 | NULL | 1999-07-01 | 400 | NULL |
| 5 | Charan | M | 18 | HINDI | 1997-09-05 | 250 | NULL |
| 6 | Deepa | F | 19 | HISTORY | 1997-06-27 | 300 | NULL |
| 7 | Dinesh | M | 22 | COMPUTER | 1997-02-25 | 210 | NULL |
+---------+--------+--------+------+----------+------------+------+------+
d) Write a Query to Display Name of all students whose Area
Contains NULL.
ANSWER- mysql> SELECT Name FROM Info WHERE Area IS NULL;
OUTPUT-
+--------+
| Name |
+--------+
| Arun |
| Ankit |
| Anu |
| Bala |
| Charan |
| Deepa |
| Dinesh |
+--------+
e) Write a Query to delete Area Column from the table Info.
ANSWER- mysql> ALTER TABLE Info DROP AREA;
OUTPUT-
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

f) Write a Query to delete table from Database.

ANSWER- mysql> DROP TABLE Info;
OUTPUT-
Query OK, 0 rows affected (0.01 sec)
 Program 12:To write Queries for the following Questions
based on the given two table

a) To display the total Unit price of all the products whose Dcode as 102.
ANSWER-
mysql> SELECT SUM(UnitPrice) FROM STOCK GROUP BY Dcode HAVING Dcode=102;
OUTPUT:
+----------------+
| SUM(UnitPrice) |
+----------------+
| 33.00 |
+----------------+

b) To display details of all products in the stock table in descending

order of Stock date.
ANSWER- mysql> SELECT * FROM STOCK ORDER BY StockDate DESC;
OUTPUT:
+------+-----------------+-------+------+-----------+------------+
| Pno | Pname | Dcode | Qty | UnitPrice | StockDate |
+------+-----------------+-------+------+-----------+------------+
| 5009 | Gel pen classic | 103 | 160 | 8.00 | 2022-01-19 |
| 5005 | Ball point pen | 102 | 100 | 10.00 | 2021-03-31 |
| 5002 | Pencil | 101 | 125 | 4.00 | 2021-02-18 |
| 5003 | Gel pen premium | 102 | 150 | 15.00 | 2021-01-01 |
| 5004 | Sharpner | 102 | 60 | 5.00 | 2020-12-09 |
| 5001 | Eraser | 102 | 210 | 3.00 | 2020-03-19 |
| 5006 | Scale | 101 | 200 | 6.00 | 2020-01-01 |
+------+-----------------+-------+------+-----------+------------+
c) To display maximum unit price of products for each dealer
individually asper Dcode from the table Stock.
ANSWER-
mysql> SELECT Dcode,MAX(UnitPrice) FROM STOCK GROUP BY Dcode;
OUTPUT-
+-------+----------------+
| Dcode | MAX(UnitPrice) |
+-------+----------------+
| 101 | 6.00 |
| 102 | 15.00 |
| 103 | 8.00 |
+-------+----------------+
d) To display the Pname and Dname from table stock and dealers.
ANSWER-
mysql> SELECT STOCK.Pname,DEALERS.Dname FROM STOCK JOIN DEALERS ON
STOCK.Dcode = DEALERS.Dcode;
OUTPUT-
+-----------------+----------------------+
| Pname | Dname |
+-----------------+----------------------+
| Pencil | Sakthi Stationeries |
| Scale | Sakthi Stationeries |
| Eraser | Indian Book House |
| Gel pen premium | Indian Book House |
| Sharpner | Indian Book House |
| Ball point pen | Indian Book House |
| Gel pen classic | Classic Stationeries |
+-----------------+----------------------+
 Program 13:Program to connect with database and store
record of employee and display records.
PYTHON SOURCE CODE:
import mysql.connector as mycon

# Connect to MySQL
con = mycon.connect(
host='localhost',
user='root',
password=”admin"
)
cur = con.cursor()

# Create database and table

cur.execute("CREATE DATABASE IF NOT EXISTS ZENITH")
cur.execute("USE ZENITH")
cur.execute("""
CREATE TABLE IF NOT EXISTS employee(
empno INT,
name VARCHAR(20),
dept VARCHAR(20),
salary INT
)
""")
con.commit()

# Menu-driven program
choice = None
while choice != 0:
print("1. ADD RECORD")
print("2. DISPLAY RECORD")
print("0. EXIT")
choice = int(input("Enter Choice: "))

if choice == 1:
# Add employee record
e = int(input("Enter Employee Number: "))
n = input("Enter Name: ")
d = input("Enter Department: ")
s = int(input("Enter Salary: "))
query = "INSERT INTO employee VALUES({}, '{}', '{}', {})".format(e, n, d, s)
cur.execute(query)
con.commit()
print("## Data Saved ##")

elif choice == 2:
# Display employee records
query = "SELECT * FROM employee"
cur.execute(query)
result = cur.fetchall()
print("%10s" % "EMPNO", "%20s" % "NAME", "%15s" % "DEPARTMENT", "%10s" % "SALARY")
for row in result:
print("%10s" % row[0], "%20s" % row[1], "%15s" % row[2], "%10s" % row[3])

elif choice == 0:
# Exit the program
con.close()
print("## Bye!! ##")

else:
print("Invalid Choice! Please try again.")
OUTPUT:
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice: 1
Enter Employee Number: 1
Enter Name: AYUSH KESHRI
Enter Department: HR
Enter Salary: 50000
## Data Saved ##
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice: 2
EMPNO NAME DEPARTMENT SALARY
1 AYUSH KESHRI HR 50000
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice: 0
## Bye!! ##
 Program 14: Program to connect with database and search
employee number in table employee and display
record, if empno not found display appropriate
message.
PYTHON SOURCE CODE:
import mysql.connector as mycon

# Connect to the database

con = mycon.connect(
host='localhost',
user='root',
password=”admin",
database="ZENITH"
)
cur = con.cursor()

# Function to search for an employee by empno

def search_employee():
empno = int(input("Enter Employee Number to Search: "))
query = "SELECT * FROM employee WHERE empno = {}".format(empno)
cur.execute(query)
result = cur.fetchone() # Fetch a single record

if result:
print("\n## Employee Found ##")
print("EmpNo:", result[0])
print("Name:", result[1])
print("Department:", result[2])
print("Salary:", result[3])
else:
print("\n## Employee Not Found ##")

# Menu-driven program
choice = None
while choice != 0:
print("\n1. SEARCH EMPLOYEE")
print("0. EXIT")
choice = int(input("Enter Choice: "))

if choice == 1:
search_employee()
elif choice == 0:
print("## Bye!! ##")
con.close()
else:
print("Invalid Choice! Please try again.")
OUTPUT:
1. SEARCH EMPLOYEE
0. EXIT
Enter Choice: 1
Enter Employee Number to Search: 1

## Employee Found ##
EmpNo: 1
Name: AYUSH KESHRI
Department: HR
Salary: 50000

1. SEARCH EMPLOYEE
0. EXIT
Enter Choice: 0
## Bye!! ##
 Program 15: Perform all the operations (Insert, Update, Delete,
Display) with reference to table ‘students’ through
MySQL-Python connectivity.

PYTHON SOURCE CODE:

import mysql.connector as mycon

# Connect to the database

con = mycon.connect(
host="localhost",
user="root",
password=”admin",
database=”CLASS_XII"
)
cur = con.cursor()

# Create the `student` table if not exists

cur.execute("""
CREATE TABLE IF NOT EXISTS student (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
age INT,
class VARCHAR(10)
)
""")
con.commit()

# Functions for CRUD operations

def insert_record():
roll_no = int(input("Enter Roll Number: "))
name = input("Enter Name: ")
age = int(input("Enter Age: "))
class_name = input("Enter Class: ")
query = "INSERT INTO student (roll_no, name, age, class) VALUES (%s, %s, %s, %s)"
values = (roll_no, name, age, class_name)
cur.execute(query, values)
con.commit()
print("## Record Inserted Successfully ##")

def update_record():
roll_no = int(input("Enter Roll Number to Update: "))
new_name = input("Enter New Name: ")
new_age = int(input("Enter New Age: "))
new_class = input("Enter New Class: ")
query = "UPDATE student SET name = %s, age = %s, class = %s WHERE roll_no = %s"
values = (new_name, new_age, new_class, roll_no)
cur.execute(query, values)
con.commit()
if cur.rowcount > 0:
print("## Record Updated Successfully ##")
else:
print("## Record Not Found ##")

def delete_record():
roll_no = int(input("Enter Roll Number to Delete: "))
query = "DELETE FROM student WHERE roll_no = %s"
cur.execute(query, (roll_no,))
con.commit()
if cur.rowcount > 0:
print("## Record Deleted Successfully ##")
else:
print("## Record Not Found ##")

def display_records():
query = "SELECT * FROM student"
cur.execute(query)
results = cur.fetchall()
if results:
print("\n%10s %20s %10s %10s" % ("ROLL_NO", "NAME", "AGE", "CLASS"))
print("-" * 50)
for row in results:
print("%10s %20s %10s %10s" % (row[0], row[1], row[2], row[3]))
else:
print("## No Records Found ##")

# Menu-driven program
choice = None
while choice != 0:
print("\n1. INSERT RECORD")
print("2. UPDATE RECORD")
print("3. DELETE RECORD")
print("4. DISPLAY RECORDS")
print("0. EXIT")
choice = int(input("Enter Choice: "))

if choice == 1:
insert_record()
elif choice == 2:
update_record()
elif choice == 3:
delete_record()
elif choice == 4:
display_records()
elif choice == 0:
print("## Bye!! ##")
con.close()
else:
print("Invalid Choice! Please try again.")
OUTPUT:
1. INSERT RECORD
2. UPDATE RECORD
3. DELETE RECORD
4. DISPLAY RECORDS
0. EXIT
Enter Choice: 1
Enter Roll Number: 1
Enter Name: PRIYANSHU KUMAR
Enter Age: 19
Enter Class: XII
## Record Inserted Successfully ##

1. INSERT RECORD
2. UPDATE RECORD
3. DELETE RECORD
4. DISPLAY RECORDS
0. EXIT
Enter Choice: 1
Enter Roll Number: 2
Enter Name: AYUSH RAJ
Enter Age: 18
Enter Class: XII
## Record Inserted Successfully ##

1. INSERT RECORD
2. UPDATE RECORD
3. DELETE RECORD
4. DISPLAY RECORDS
0. EXIT
Enter Choice: 2
Enter Roll Number to Update: 1
Enter New Name: PRIYANSHU KUMAR
Enter New Age: 18
Enter New Class: XII
## Record Updated Successfully ##

1. INSERT RECORD
2. UPDATE RECORD
3. DELETE RECORD
4. DISPLAY RECORDS
0. EXIT
Enter Choice: 3
Enter Roll Number to Delete: 2
## Record Deleted Successfully ##
1. INSERT RECORD
2. UPDATE RECORD
3. DELETE RECORD
4. DISPLAY RECORDS
0. EXIT
Enter Choice: 4

ROLL_NO NAME AGE CLASS

--------------------------------------------------
1 PRIYANSHU KUMAR 18 XII

1. INSERT RECORD
2. UPDATE RECORD
3. DELETE RECORD
4. DISPLAY RECORDS
0. EXIT
Enter Choice: 0
## Bye!! ##