COMPUTER SCIENCE PRACTICAL

FILE

Submitted by:

Student Name: Khush Nayyar

Class: XII – A

Subject: Computer Science (083)

Roll No: 24
 INDEX
[Link] Content Signature
1 WAP to count the number of alphabets, digits,
uppercase, lowercase, spaces, and other
characters (status of a string).
2 WAP to remove all odd numbers from the given list.

3 WAP to enter any number and print the factorial of

this number. Also, check whether this number is a
prime number or not.
4 WAP to find the max, min, and mean of values from
an inputted list.

5 WAP to generate the Fibonacci series.

6 WAP to store students’ information in a dictionary
and display information based on admission no.

7 Write a function roll_D (), that takes 2 parameters- the

no. of(with a default value of 6) of a dice, and the
sides
number
to of generates
roll-and dice random roll values for each dice
rolled.
Print out each roll and then return one string “That’s
all”.
Example roll_D(6, 3)
4
1
6

8 Write a program to swap the content with the next

value, if it is divisible by 7, so that the resultant
array will look like: 3,5,21,6,8,14,3,14.

9 Write a program to display those strings that start with

‘A’ from
the given list.
[Link] Content Signature
10 Write a program to show the sorting of elements of a
list step-by- step.

11 Write a user-defined function to find the greatest

common divisor between two numbers.

12 Write a user-defined function to display the first n prime

numbers.
13 WAP to calculate the income tax of an employee based
on basic salary and total savings inputted by the user.

14 WAP to calculate the following using the concepts of

Python modules:
Area of a Circle
Circumference of a
circle Area of a
rectangle Perimeter
of a rectangle
15 WAP to print the number of occurrences of a substring
into a line using the built-in string function find().

16 Write a program to show and count the number of

words in a text
file ‘[Link]’ that start with the word ‘The’, ‘the’.
17 Consider a binary file “[Link]” containing details
such as empno: ename: salary(separator‘:’). Write a
Python function to display details of those employees
who are earning between 20000 and 40000.

18 WAP to enter the numbers and find Linear Search,

Binary Search, and Lowest Number using array code
with user-defined functions.
19 Write a function to create a copy of the file “[Link]”
named as “[Link]” which should convert the first letter of
the file and the first alphabetic character following a full
stop into upper case.
[Link] Content Signature
20 Anant has been asked to display all the students who
have scored less than 40 for Remedial Classes. Write a
user-defined function to display all those students who
have scored less than 40 from the binary file
“[Link]” and copy their records to a new file
“[Link]”
21 Create a CSV file “Groceries” to store information about
different items existing in a shop. The information is to
be stored for each item code, including name, price, and
quantity. Write a program to accept the data from the
user and store it permanently in a CSV file.

22 Write a menu-driven program implementing user-defined

functions to perform different functions on a CSV file
“student”, such as:
(a) Write a single record to CSV.
(b) Write all the records in one single go onto the CSV.
(c) Display the contents of the CSV file.
23 WAP to add, delete, and display the records
using a list implementation through a stack.

24 Write a Program to show MySQL database connectivity in

Python to insert records in a table and display records.

25 WAP with MySQL and Python connectivity for searching a

record on a condition from a table.

26 WAP with MySQL and Python connectivity for updating a

record on a condition from a table.

27 WAP with MySQL and Python connectivity for deleting a

record on a condition from a table.

28 WAP with MySQL and Python connectivity for showing

records on a condition from a table.
[Link] Content Signature
29 Consider the tables STORE and SUPPLIERS.
Write SQL commands:

i. To display details of all the items in the store

table in ascending order of LastBuy.
ii. To display the details of those items from the
store table whose rate is more than 15 rupees.
iii. To display the details of those items whose
supplier code is 22 or the quantity in store is
more than 110 from the table store.

30 Write SQL commands for (i) to (v) based on a given

relation.

i. To show the books of First publ written by P.

Purohit.
ii. To display the cost of all the books published
for First publ.
iii. To display the BOOK_NAME and price of the books,
more than 3 copies of which have been issued.
iv. To show the total cost of books of each type.
31 v.
WriteTo
SQLshow the details
commands forof
(i)the costliest
to (iv) basedbook.
on a given
relation.

i. To display the details of all the models in the

Model table in ascending order of
DateOfManufacture.
ii. To display the details of those models
manufactured in 2011 and whose Cost is
below 2000.
iii. To show the number of distinct comp_ho from
company table.
iv. Show the company name and contact
[Link] Content Signature
32 Write SQL commands for (i) to (v) based
on a given relation.

i. To display the names of all the silver-colored

cars.
ii. To display name, make and capacity of
cars in descending order of their sitting
capacity.
iii. To display the highest charges at which a
vehicle can be hired from CARDEN.
iv. Display the number of different MAKE from
carden table.
v. Display the car_name where capacity of car is
33 Write SQL commands for (i) to (v) based
on a given relation.

i. Write SQL query to display Pname, Quantity

and Rate for all the orders that are either
Pencil or Pen.
ii. Write SQL query to display the orders
which are not getting any Discount.
iii. Write SQL query to display the Pname,
Quantity and Sale_date for all the orders
whose total cost (Quantity * Rate) is greater
than 500.
iv. Write SQL query to display the orders
whose Rate is in the range 20 to 100.
v. Write SQL query to display the minimum
quantity, maximum quantity and total
 Practical Implementation 1
Program 1: WAP to count the number of alphabets, digits, uppercase, lowercase,
spaces, and other characters (status of a string).

x = input("Enter a string:
") la, ua, d, s, c = 0, 0,
0, 0, 0
# la=lowercase ua=uppercase d=digit s=space c=other characters
for i in x:
if i in
"qwertyuioplkjhgfdsazxcvbnm":
la = la + 1
elif i in "QWERTYUIOPLKJHGFDSAZXCVBNM":
ua = ua + 1
elif i in
"1234567890": d =
d + 1
elif i == "
": s = s
+ 1
else:
c = c +
1 print("No. of:

Output
 Practical Implementation 2
Program 2: WAP to remove all odd numbers from the given list.

n = int(input("Enter number of elements in the

list: ")) mylist = []
for i in range(n):
x = int(input("Enter number:
")) [Link](x)
print("Original list:",
mylist) for x in mylist:
if x % 2 ==
0:
continue
[Link](x)
print("New list:",
mylist)

Output
 Practical Implementation 3
Program 3: Program to enter any number and print factorial of this number. Also
check whether this number is a prime number or not.

x = int(input("Enter a number:
")) b = x
y = x //
2a = 0
for i in range(2, y +
1): if x % i == 0:
a = a +
1 if a == 0:
print("It’s a Prime
no.") else:
print("Its a Composite
no.") f = 1
for z in range(1, b +
1): f = f * z
print("Factorial of", b, "=", f)

Output
 Practical Implementation 4
Program 4: Program to find the max, min and mean of values from an inputted
list find the max, min and mean of values from an inputted list.

n = int(input("Enter number of elements in the

list: ")) l = []
for i in range(n):
x = int(input("Enter number:
")) [Link](x)
if l[0] >
l[1]: ma =
l[0] mi =
l[1]
else:
ma =
l[1] mi
= l[0]
for i in range(2,
len(l)): if l[i] >=
ma:
ma = l[i]
for i in range(2,
len(l)): if l[i] <=
mi:
mi = l[i]

print("Max value:", ma, "\nMin value",

mi) s = 0

Output
 Practical Implementation 5
Program 5: Program to generate Fibonacci series.

x = int(input("Enter no. of terms:

")) print("Fibonacci Series: ")
a = 0
b = 1
c = a +
b
print(a)
print(b)
for i in range(1, x -
1): print(c)
a =
bb =
c

Output
 Practical Implementation 6
Program 6: Program to store student’s information in a dictionary
and display
information on the basis of admission no.

def detail():
while True:
a = int(input("Enter adm no:
")) n = input("Enter name: ")
m = int(input("Enter marks: "))
q = input("Do you want to
continue?(y/n): ") d[a] = [n, m]
if q ==
"n":
break

def show():
x = int(input("Enter adm no to search:
")) y = d[x]
print("Adm no:", x, "Name:", y[0], "Marks:", y[1])

d = {}

while True:
print("[Link] student
detail") print("[Link]
details") print("[Link]")
p = int(input("Enter choice:
")) if p == 1:
detail(
) elif p ==
2:
show()
elif p ==
Output
 Practical Implementation 7
Program 7: Write a function roll_D (), that takes 2 parameters- the no. of sides (with
default value 6) of a dice, and the number of dice to roll-and generates random roll
values for each dice rolled. Print out each roll and then return one
string “That’s all”.

Example roll_D(6, 3)
4
1
6
That’s all

import random

def roll_D(side,
dice): for i in
range(dice):
x = [Link](1,
side) print("Die", i + 1,
":", x)
print("That's all")

a = int(input("Enter no. of sides in a die:

")) b = int(input("Enter no. of die: "))

Output
 Practical Implementation 8
Program 8: Write a program to swap the content with next value, if it is divisible
by 7 so that the resultant array will look like: 3,5,21,6,8,14,3,14.

x = eval(input("Enter a list of numbers:

")) l = len(x)
i = 0
print("Original list:",
x) while i < l:
try:
if x[i] % 7 == 0:
x[i], x[i + 1] = x[i + 1],
x[i] i = i + 2
else:
i = i + 1
except
IndexError:
break
print("New list:",
x)

Output
 Practical Implementation 9
Program 9: Write a program to display those strings that start with ‘A’ or ‘a’
from the given list

l = eval(input("Enter list of names:

")) print("Names starting with A or
a:") for i in l:
if i[0] in
"Aa":
print(i)

Output
 Practical Implementation 10
Program 10: Write a program to show sorting of elements of a list step-by-step.

def bubble_sort(arr,
verbose=False): n = len(arr)
for i in range(n - 1):
for j in range(n - i -
1): if arr[j] > arr[j
+ 1]:
arr[j], arr[j + 1] = arr[j + 1],
arr[j] if verbose:
print(f"Pass {i + 1}:
{arr}") return arr

def insertion_sort(arr,
verbose=False): for i in
range(1, len(arr)):
key =
arr[i] j =
i - 1
while j >= 0 and arr[j] > key:
arr[j + 1] =
arr[j] j -= 1
arr[j + 1] =
key if verbose:
print(f"Pass {i}:
{arr}") return arr

def
_read_list_from_input(
): l = []
a = int(input("Enter no. of elements in a
list: ")) for p in range(a):
b = int(input("Enter number:
")) [Link](b)
return l

def main():
print("[Link] sort")
print("[Link]
sort") print("[Link]")
while True:
x = int(input("Enter choice:
")) if x == 1:
l =
 if x == 2:
l = _read_list_from_input()
print("Original list", l)
insertion_sort(l,
verbose=True)
if x == 3:
break

if name == " main

": main()

Output
 Practical Implementation 11
Program 11: Write a user defined function to find the greatest common divisor
between two numbers.

def hcf(x,
y): if x
> y:
s = y
else:
s = x
for i in range(1, s + 1):
if x % i == 0 and y % i ==
0: ans = i
return ans

a = int(input("Enter 1st number:

")) b = int(input("Enter 2nd
number: ")) print("HCF is",
hcf(a, b))

Output
 Practical Implementation 12
Program 12: Write a user defined function to display first n prime numbers.

def
primes():
p = int(input("Enter number of primes to be
calculated: ")) print("Prime numbers:")
j = 0
x = 2
while j != p:
y = x //
2a = 0
for i in range(2, y +
1): if x % i == 0:
a = a +
1 if a == 0:
print(x)
x = x +
1j = j
+ 1
else:
x = x + 1

primes()

Output
 Practical Implementation 13
Program 13: Program to calculate income tax of an employee based on basic
salary and total savings inputted by the user.

b = int(input("Enter the basic salary:

")) s = int(input("Enter total
savings: "))
if b <= 250000:
tax = 0
elif b <=
500000: if s
> 150000:
s = 150000
tot_income = b - s -
250000 tax = tot_income *
0.05
elif b <=
1000000: if s
> 150000: s =
150000
tot_income_5slab = 500000 - s -
250000 tot_income_20slab = b -
500000
tax = tot_income_5slab * 0.05 + tot_income_20slab *
0.02 else:
if s >
150000: s
= 150000

Output
 Practical Implementation 14
Program 14: WAP to calculates the following using concepts of module:

1. Area of Circle

2. Circumference of a circle

3. Area of rectangle

4. Perimeter of a rectangle

Circle Module:

import math

def area(r):
return [Link] *
(r**2) def
circumference(r):
return 2 * [Link] * r

Rectangle Module:

def area(l,
b): return
l * b

def perimeter(l, b):

return 2 * (l +
b)

Main program:

import Circle
import
Rectangle

while True:
print("[Link] of Circle")
print("[Link] of
Circle") print("[Link] of
Rectangle") print("[Link]
of Rectangle") print("[Link]")
if x == 1:
r = int(input("Enter radius of circle:
")) print("Area of circle:",
[Link](r))
elif x == 2:
r = int(input("Enter radius of circle: "))
print("The circumference is",
[Link](r)) elif x == 3:
l = int(input("Enter length:
")) b = int(input("Enter
width: "))
print("The area is:", [Link](l,
b)) elif x == 4:
l = int(input("Enter length:
")) b = int(input("Enter
width: "))
print("The perimeter of rectangle",
[Link](l, b)) elif x == 5:
brea
k else:
Output
 Practical Implementation 15
Program 15: WAP to print the number of occurrences of a substring into a line using
built in string function find().

x = input("Enter any string: ")

y = input("Enter the sub string to be
searched: ") c = 0
i = 0
while i < len(x):
if [Link](y, i) != -
1: m = [Link](y,
i) i = m + 1
c = c +
1 else:
i = i + 1
print("The substring occurs", c, " times in the string.")

Output
 Practical Implementation 16
Program 16: Write a program to show and count the number of words in a text file
‘[Link]’ which is starting with a word ‘The’, ‘the’.

[Link]:

The sky is
blue. I live in
Delhi.
The flowers are
blooming. It's Sunday
afternoon.

Main Program:

f = open("[Link]",
"r") a = [Link]()
i = 0
while i <= len(a) -
1: c = 0
if a[i][0] in "Tt" and a[i][1] in "hH" and a[i][2] in
"Ee": print(a[i])
for j in a[i]:
if j == "
":
c = c + 1
print("No. of words in this sentence starting with The:",
c + 1) i += 1
print()
[Link]()

Output
 Practical Implementation 17
Program 17: Consider a binary file “[Link]” containing details
such as empno: ename: salary(separator‘:’). Write a python
function to display details of those employees who are earning between
20000 and 40000.

import pickle

f = open("[Link]",
"rb") x = [Link](f)
print("All, records: ",
x) print()
print("Records with salary between 20000 and 40000
are: ") for y in x:
if y["Salary"] >= 20000 and y["Salary"] <=
40000: print(y)
[Link]()

Output
 Practical Implementation 18
Program 18: Write a program to enter the numbers and find Linear Search,
Binary Search, Lowest Number using array code with user defined functions.

def
linear_search(
): l = []
x = int(input("Enter no. of values for list:
")) for i in range(x):
y = int(input("Enter no:
")) [Link](y)
print("Original list:", l)
n = int(input("Enter no. to search:
")) f = 0
p = 0
while p <
len(l): if
l[p] == n:
f = 1
p = p +
1 break
else:
p = p +
1 if f == 1:
print(n, "is present at
position", p) else:
print(n, "not found")

def
binary_search(
): l = []
x = int(input("Enter no. of values for list:
")) for i in range(x):
y = int(input("Enter no:
")) [Link](y)
print("Original list: ",
l) [Link]()
print("Sorted list:", l)
n = int(input("Enter no. to search:
")) mid = len(l) // 2
low = 0
 while l[mid] != n and low <=
high: if n > l[mid]:
low = mid +
1 else:
high = mid - 1
mid = (low + high) // 2

if low > high:

print("Element is at
None") else:
print("Element is at", mid + 1)

def
lowest_no()
: l = []
x = int(input("Enter no. of values for list:
")) for i in range(x):
y = int(input("Enter no:
")) [Link](y)
if l[0] >
l[1]: mi =
l[1]
else:
mi = l[0]
for i in range(2,
len(l)): if l[i] <=
mi:
mi = l[i]
print("Lowest no:",
mi)

while True:
print("[Link] search\[Link] search\[Link] lowest
no.") q = int(input("Enter choice: "))
if q == 1:
linear_search
() elif q == 2:
binary_search
() elif q == 3:
lowest_no(
Output
 Practical Implementation 19
Program 19: Write a function to create a copy of file “[Link]”
named as “[Link]” which should convert the first letter of the file and the
first alphabetic character following a full stop into upper case.

[Link]:

i live in new delhi.i am studying python.i am in class XII

Main code:

def capital():
f1 = open("[Link]", "r")
f2 = open("[Link]",
"w") while True:
line =
[Link]() if
not line:
break
line =
[Link]() line
= [Link]() l
= len(line)
s = ""
s = s +
line[0].upper() i = 1
while i < l:
if line[i] ==
".": s = s +
line[i] i = i
+ 1
if i >= l:
break
s = s +
line[i].upper() else:
s = s +
line[i] i = i + 1
[Link](s
)
[Link]()
[Link]()
Output

[Link]

[Link]
 Practical Implementation 20
Program 20: Anant has been asked to display all the students who have scored less
than 40 for Remedial Classes. Write a user-defined function to display all those
students who have scored less than 40 from the binary file
“[Link]” and copy their records to new file “[Link]”

import pickle

def main():
f = open("[Link]", "rb")
f1 = open("[Link]",
"wb") x = [Link](f)
print("All records:", x)
print("Student to go for extra
classes: ") for y in x:
if y[2] <= 40:
a = y
[Link](a,
f1) print(a)
[Link]()
[Link](
)

main()

Output
 Practical Implementation 21
Create a CSV file “Groceries” to store information about different
items existing in a shop. The information is to be stored w.r.t. each item code,
name, price, and quantity. Write a program to accept the data from the user and
store it permanently in a CSV file.

import csv

def
add_grocery_record(filename="[Link]
v"): code = input("Item code: ").strip()
name = input("Item name:
").strip() price = input("Price:
").strip() quantity =
input("Quantity: ").strip() try:
price =
float(price) except
ValueError:
print("Invalid price; must be a
number.") return
try:
quantity =
int(quantity) except
ValueError:
print("Invalid quantity; must be an
integer.") return

header = ["code", "name", "price",

"quantity"] row = [code, name, price,
quantity]

write_header =
False try:
with open(filename, "r", newline="")
as f: pass
except
FileNotFoundError:
write_header = True

with open(filename, "a", newline="")

as f: writer = [Link](f)
if write_header:
[Link](heade
r)
[Link](row)
print("Record appended to", filename)
Output

Code:

CSV File:
 Practical Implementation 22
Write a menu-driven program implementing user-defined functions to perform
different functions on a CSV file “student”, such as:
(a) Write a single record to CSV.
(b) Write all the records in one single go onto the CSV.
(c) Display the contents of the CSV file.

import csv

CSV_FILE = "[Link]"
FIELDNAMES = ["id", "name", "age", "grade"]

def write_single_record(record,
filename=CSV_FILE): write_header = False
try:
with open(filename, "r", newline="",
encoding="utf-8"): pass
except
FileNotFoundError:
write_header = True

with open(filename, "a", newline="", encoding="utf-8")

as f: writer = [Link](f)
if write_header:
[Link](FIELDNAM
ES)
if isinstance(record, dict):
[Link]([[Link](k, "") for k in
FIELDNAMES]) else:
[Link](record)

def write_all_records(records, filename=CSV_FILE):

with open(filename, "w", newline="", encoding="utf-8")
as f: writer = [Link](f)
[Link](FIELDNAM
ES) for rec in records:
if isinstance(rec, dict):
[Link]([[Link](k, "") for k in
FIELDNAMES]) else:
def
display_contents(filename=CSV_FIL
E): try:
with open(filename, "r", newline="", encoding="utf-8")
as f: reader = [Link](f)
for row in
reader:
print(row)
except FileNotFoundError:
print(filename, "not
found")

def _input_single_record():
rid = input("Student id:
").strip() name = input("Name:
").strip()
age = input("Age: ").strip()
grade = input("Grade:
").strip() return [rid, name,
age, grade]

def main():
while True:
print("\n1. Write single record")
print("2. Write all records
(overwrite)") print("3. Display
contents")
print("4. Exit")
choice = input("Enter choice:
").strip() if choice == "1":
rec =
_input_single_record()
write_single_record(rec)
print("Record written")
elif choice == "2":
n = int(input("How many records to write?
")) records = []
for _ in range(n):
[Link](_input_single_recor
d())
write_all_records(records
) print("All records
written")
elif choice == "3":
display_contents
()
Output

Code:

CSV File:
 Practical Implementation 23
WAP to add, delete, and display the records using a list implementation through
a stack.

STACK = []

def push(record):
[Link](recor
d)

def pop():
if not STACK:
print("Stack is
empty") return None
return [Link]()

def
display_stack(
): if not
STACK:
print("Stack is
empty") return
for i, rec in enumerate(reversed(STACK),
1): print(f"{i}: {rec}")

def _input_record():
rid = input("Record id:
").strip() data = input("Data:
").strip() return [rid, data]

def main():
while True:
print("\n1. Push (add)
record") print("2. Pop
(delete) record") print("3.
Display stack") print("4.
Exit")
choice = input("Enter choice:
").strip() if choice == "1":
rec =
_input_record()
push(rec)
 elif choice ==
"2": popped =
pop()
if popped is not None:
print("Popped:",
popped)
elif choice ==
"3":
display_stack()
elif choice ==
"4":
brea
k else:
print("Invalid choice")

Output
 Practical Implementation 24
Write a Program to show MySQL database connectivity in Python to insert
records in a table and display records.

import [Link]

def insert_student(id_, name, age, grade, table="students",

db_config=None): if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
sql = f"INSERT INTO {table} (id, name, age, grade) VALUES (%s, %s,
%s,
%s)"
[Link](sql, (int(id_), name, int(age),
grade)) [Link]()
[Link]
()
[Link]()

def display_students(table="students",
db_config=None): if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
[Link](f"SELECT id, name, age, grade FROM
{table}") for row in [Link]():
print(row
)
[Link]
()
[Link]()

def create_students_table(table="students",
db_config=None): if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
sql = (
f"CREATE TABLE IF NOT EXISTS
 [Link](sq
l) [Link]()
[Link]()
[Link]()

def _prompt_db_config():
host = input("DB host [localhost]: ").strip() or
"localhost" user = input("DB user: ").strip()
password = input("DB password:
").strip() database = input("DB name:
").strip()
return {"host": host, "user": user, "password": password,
"database": database}

def main():
cfg =
_prompt_db_config()
while True:
print("\n1. Insert
student") print("2.
Display students")
print("3. Exit")
choice = input("Enter choice:
").strip() if choice == "1":
id_ = input("id: ").strip()
name = input("name:
").strip() age = input("age:
").strip() grade =
input("grade: ").strip()
insert_student(id_, name, age, grade,
db_config=cfg) print("Inserted")
elif choice == "2":
display_students(db_config=c
fg)
elif choice ==
"3": break
else:
print("Invalid")
Output

Code:

SQL Table:
 Practical Implementation 25
WAP with MySQL and Python connectivity for searching a record on a condition
from a table.

import [Link]

def search_students_by_age(min_age, table="students",

db_config=None): if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
sql = f"SELECT id, name, age, grade FROM {table} WHERE age
>= %s" [Link](sql, (min_age,))
rows =
[Link]()
[Link]()
[Link]()
return rows

def _prompt_db_config():
host = input("DB host [localhost]: ").strip() or
"localhost" user = input("DB user: ").strip()
password = input("DB password:
").strip() database = input("DB name:
").strip()
return {"host": host, "user": user, "password": password,
"database": database}

def main():
cfg =
_prompt_db_config()
while True:
print("\n1. Search students by minimum
age") print("2. Exit")
ch = input("Enter choice:
").strip() if ch == "1":
a = int(input("Minimum age: "))
rows = search_students_by_age(a,
db_config=cfg) for r in rows:
print(r
) elif ch ==
"2":
brea
k else:
print("Invalid")
Output

Code:

SQL Table:
 Practical Implementation 26
WAP with MySQL and Python connectivity for updating a record on a condition
from a table.

import [Link]

def update_student_grade(student_id, new_grade,

table="students", db_config=None):
if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
sql = f"UPDATE {table} SET grade = %s WHERE id
= %s" [Link](sql, (new_grade,
student_id)) [Link]()
affected =
[Link]
[Link]()
[Link]()
return affected

def _prompt_db_config():
host = input("DB host [localhost]: ").strip() or
"localhost" user = input("DB user: ").strip()
password = input("DB password:
").strip() database = input("DB name:
").strip()
return {"host": host, "user": user, "password": password,
"database": database}

def main():
cfg =
_prompt_db_config()
while True:
print("\n1. Update student
grade") print("2. Exit")
ch = input("Enter choice:
").strip() if ch == "1":
sid = input("Student id:
").strip() newg = input("New
grade: ").strip()
affected = update_student_grade(sid, newg,
db_config=cfg) print("Rows affected:", affected)
elif ch ==
"2": break
Output

Code:

SQL Table:
 Practical Implementation 27
WAP with MySQL and Python connectivity for deleting a record on a condition
from a table.

import [Link]

def delete_student_by_id(student_id, table="students",

db_config=None): if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
sql = f"DELETE FROM {table} WHERE id =
%s" [Link](sql, (student_id,))
[Link]()
affected =
[Link]
[Link]()
[Link]()
return affected

def _prompt_db_config():
host = input("DB host [localhost]: ").strip() or
"localhost" user = input("DB user: ").strip()
password = input("DB password:
").strip() database = input("DB name:
").strip()
return {"host": host, "user": user, "password": password,
"database": database}

def main():
cfg =
_prompt_db_config()
while True:
print("\n1. Delete student by
id") print("2. Exit")
ch = input("Enter choice:
").strip() if ch == "1":
sid = input("Student id: ").strip()
affected = delete_student_by_id(sid,
db_config=cfg) print("Rows affected:",
affected)
elif ch ==
"2": break
else:
print("Invalid")
Output

Code:

SQL Table:
 Practical Implementation 28
WAP with MySQL and Python connectivity for showing records on a condition
from a table.

import [Link]

def show_students_with_grade(grade_value, table="students",

db_config=None): if db_config is None:
db_config = _prompt_db_config()
conn =
[Link](**db_config)
cursor = [Link]()
sql = f"SELECT id, name, age, grade FROM {table} WHERE grade
= %s" [Link](sql, (grade_value,))
rows =
[Link]()
[Link]()
[Link]()
return rows

def _prompt_db_config():
host = input("DB host [localhost]: ").strip() or
"localhost" user = input("DB user: ").strip()
password = input("DB password:
").strip() database = input("DB name:
").strip()
return {"host": host, "user": user, "password": password,
"database": database}

def main():
cfg =
_prompt_db_config()
while True:
print("\n1. Show students with specific
grade") print("2. Exit")
ch = input("Enter choice:
").strip() if ch == "1":
g = input("Grade value: ").strip()
rows = show_students_with_grade(g,
db_config=cfg) for r in rows:
print(r
) elif ch ==
"2":
brea
k else:
print("Invalid")
Output

Code:
 Practical Implementation 29
Consider the following tables, Store and Suppliers. Write SQL commands.

Table: Store

Table: Suppliers

Creating Tables:
Store:
Suppliers:

Inserting Values:
Store:

Suppliers:

Tables:
Store:

Suppliers:
SQL Queries:
i. To display details of all the items in the store table in ascending order
of Lastbuy

ii. To display details of those items from store table whose rate is more than
15 rupees.

iii. To display the details of those items whose supplier code is 22 or quantity
in store is more than 110 from table store.
 Practical Implementation 30
Write SQL commands for (i) to (v) based on a given relation.

Table: Books

Table: Issued

Creating Tables:
Books:
Issued:

Inserting Values:
Books:

Issued:

Tables:
Books:

Issued:
SQL Queries:
i. To show the books of First publ written by P. Purohit.

ii. To display the cost of all the books published for First publ.

iii. To display the BOOK_NAME and price of the books, more than 3 copies of which
have been issued.

iv. To show the total cost of books of each type.

v. To show the details of the costliest book.
 Practical Implementation 31
Write SQL commands for (i) to (iv) based on a given relation.
Table: Company

Table: Model

Creating Tables:
Company:

Model:
Inserting Values:

Company:

Model:

Tables:

Company:

Model:
SQL Queries:

i. To display the details of all the models in the Model table in

ascending order of DateOfManufacture.

ii. To display the details of those models manufactured in 2011 and

whose Cost is below 2000.

iii. To show the number of distinct comp_ho from the company table.

iv. Show the company name and contact person from the company
table where comp_ho is okhla
 Practical Implementation 32
Write SQL commands for (i) to (v) based on a given relation.
Table: Carden

Creating Table:

Inserting Values:

Table:
SQL Queries:

i. Display the car_name where capacity of car is 4.

ii. To display the names of all the silver-colored cars.

iii. To display name, make and capacity of cars in descending order of

their sitting capacity.

iv. To display the highest charges at which a vehicle can be hired

from CARDEN.

v. Display the number of different MAKE from carden table.

 Practical Implementation 33
Write SQL commands for (i) to (v) based on a given relation.
Table: Orders

Creating Table:

Inserting Values:

Table:
SQL Queries:

i. Write SQL query to display the minimum quantity, maximum quantity

and total quantity of Orders.

ii. Write SQL query to display Pname, Quantity and Rate for all the orders
that are either Pencil or Pen.

iii. Write SQL query to display the orders which are not getting any Discount.

iv. Write SQL query to display the Pname, Quantity and Sale_date for all
the orders whose total cost (Quantity * Rate) is greater than 500.

v. Write SQL query to display the orders whose Rate is in the range 20 to 100.