PRACTICAL ASSIGNMENT – 6

Q-1

import mysql.connector as myscon

connector = myscon.connect(host = "localhost", user = "root",password = "Shrihan@25", database =

"Exam")

mycursor = connector.cursor()

while True:

agreement = input("do you want to input a new record into the table ? : ")

if agreement == "yes":

r = int(input("enter the roll number : "))

n = input("enter the name ")

t = int(input("enter the total marks : "))

mycursor.execute("INSERT INTO STUDENT VALUES ({},'{}',{})".format(r,n,t))

elif agreement == "no":

print("okay gentlemen")

agreement = input("do you want to increment marks of all students by 10% ? : ")

if agreement == "yes":

mycursor.execute("UPDATE STUDENT SET TOTAL = TOTAL + TOTAL*1/10")

elif agreement == "no":

print("okay gentlement ")

agreement = input("do you want to delete the record of a student ? : ")

if agreement == "yes":

rollnumber = int(input("enter the rll number that you want to delete : "))

mycursor.execute("DELETE FROM STUDENT WHERE ROLLNUMBER = {}".format(rollnumber))

elif agreement == "no":

print("Okay gentlement ")

connector.commit()
myinput = input("do you want to once more execute the programme ? : ")

if myinput == "yes":

True

else:

break

agreement = input("do you want to disply details of aLL STUDENTS ? : ")

if agreement == "yes":

mycursor.execute("SELECT * FROM STUDENT ")

for i in mycursor:

print(i)

output

do you want to input a new record into the table ? : yes

enter the roll number : 45

enter the name sangheen mehta

enter the total marks : 56

do you want to increment marks of all students by 10% ? : yes

do you want to delete the record of a student ? : yes

enter the rll number that you want to delete : 54

do you want to once more execute the programme ? : no

do you want to disply details of aLL STUDENTS ? : yes

(21, 'shrihan mittal', 799)

(25, 'shreya salhotra', 639)

(26, 'charles 4', 605)

(45, 'sangheen mehta', 62)

Q-2

import mysql.connector as myscon

connector = myscon.connect(host = "localhost", user = "root",password = "Shrihan@25", database =
"ITEM")

mycursor = connector.cursor()

while True :

s = int(input("Insert an item record press 1 : "))

if s == 1:

a = int(input("enter item number : "))

b = int(input("enter item price : "))

c = str(input("enter item name : "))

mycursor.execute("INSERT INTO ITEM VALUES ({},{},'{}')".format(a,b,c))

else:

pass

s = int(input("Increase price of all items by 10% press 2 : "))

if s == 2:

mycursor.execute("UPDATE ITEM SET PRICE = PRICE + PRICE*1/10")

else:

pass

s = int(input("Delete an item of a given ItemNo press 3 : "))

if s == 3:

a = int(input("enter the item number you want to delete : "))

mycursor.execute("DELETE FROM ITEM WHERE ITEMNO = {}".format(a))

else:

pass

connector.commit()

s = int(input("Display the details of all items whose name starts with ‘C’ press 4 : "))

if s == 4:

mycursor.execute("SELECT * FROM ITEM WHERE NAME LIKE 'C%'")

for i in mycursor:
print(i)

else:

pass

s = int(input("Display the details of all items priced in the range of 100 to 150 press 5 : "))

if s == 5:

mycursor.execute("SELECT * FROM ITEM WHERE PRICE BETWEEN 100 AND 150 ")

agreement = input(("do you want to once more execute the programme ? : "))

if agreement == "yes":

pass

else:

break

connector.commit()

q-3

import mysql.connector as mysqcon

file_object = mysqcon.connect(host="localhost",user = "root",password="Shrihan@25",database =

"EmpDetail")

print("1 : insert a new record : ")

print("2 : change the name of given employee number ")

print("3 : display records of all employees ")

print("4 : search and display employee by name ")

print("5 : dislpay the number of objects in each grade ")

file_cursor = file_object.cursor()

while True:

response = int(input("enter your response : "))

if response == 1:

empno = int(input("enter emp number : "))

empname = input("enter emo name : ")

grade = input("enter emp grade : ")

file_cursor.execute("INSERT INTO EMP(Empno,Empname,Grade) VALUES

({},'{}','{}')".format(empno,empname,grade))

file_object.commit()

elif response == 2:

emp_num = int(input("enter the employee number of the employee of which you want to
change the name : "))

new_name = input("enter new name : ")

file_cursor.execute("UPDATE EMP SET Empname = '{}' WHERE Empno =

{}".format(new_name,emp_num))

file_object.commit()

elif response == 3:

file_cursor.execute("SELECT * FROM EMP")

list_emp = file_cursor.fetchall()

for i in list_emp:

print(i)

elif response == 4:

name = input("enter the name of employee which you want to fetch : ")

file_cursor.execute("SELECT * FROM EMP WHERE Empname = '{}'".format(name))

emp = file_cursor.fetchone()

print(emp)

elif response == 5:

file_cursor.execute("SELECT COUNT(GRADE) FROM EMP GROUP BY GRADE ")

grade = file_cursor.fetchall()

print(grade)

else:

pass

agreement = input("do you want to again start the programme : ")

if agreement == "yes":

pass

else:
break

output :

1 : insert a new record :

2 : change the name of given employee number

3 : display records of all employees

4 : search and display employee by name

5 : dislpay the number of objects in each grade

enter your response : 1

enter emp number : 5

enter emo name : hriday lal

enter emp grade : B

do you want to again start the programme : yes

enter your response : 2

enter the employee number of the employee of which you want to change the name : 3

enter new name : shahrukh khan

do you want to again start the programme : yes

enter your response : 3

(1, 'kaushal kunmar ', 'A')

(2, 'riya sharma', 'B')

(3, 'shahrukh khan', 'C')

(4, 'shaurya lokar', 'B')

(5, 'hriday lal', 'B')

do you want to again start the programme : no

q-4

import mysql.connector as mysqcon

file_obj = mysqcon.connect(host = "localhost",user="root",password = "Shrihan@25",database =
"BANK")

file_cur = file_obj.cursor()

print("1 : insert a new employee record ")

print("2 : deposit amount in an account ")

print("3 : withdraw amount from an account ")

print("4 : search for particular account number ")

while True:

response = int(input("enter your response : "))

if response == 1:

ACC_NO = int(input("enter account number : "))

cust_name = input("enter costumer name : ")

bal = int(input("enter balance : "))

file_cur.execute("INSERT INTO TRANSACTION VALUES

({},'{}',{})".format(ACC_NO,cust_name,bal))

file_obj.commit()

elif response == 2:

acc_no = int(input("enter your account number : "))

am = int(input("enter the amount to be deposited : " ))

file_cur.execute("UPDATE TRANSACTION SET BALANCE = BALANCE + {} WHERE ACCOUNTNO =

{}".format(am,acc_no))

file_obj.commit()

elif response == 3:

account_no = int(input("enter the acount number from which you want to withdraw : "))

withdr = int(input("enter the amount which you want to withdraw : "))

file_cur.execute("UPDATE TRANSACTION SET BALANCE = BALANCE - {} WHERE ACCOUNTNO =

{}".format(withdr,account_no))

file_obj.commit()

elif response == 4:

amo = int(input("enter the amount which you want to be searched : "))

file_cur.execute("SELECT * FROM TRANSACTION WHERE BALANCE = {}".format(amo))

data = file_cur.fetchone()

print("the record which you want to be searched is : ",data)

else:

pass

agreement = input("do you want to run the programme again ? : ")

if agreement == "yes":

pass

else:

break

output :

1 : insert a new employee record

2 : deposit amount in an account

3 : withdraw amount from an account

4 : search for particular account number

enter your response : 1

enter account number : 32443242

enter costumer name : shrihan mittal

enter balance : 32425524

do you want to run the programme again ? : yes

enter your response : 1

enter account number : 22144141

enter costumer name : jaidev sharma

enter balance : 32432

do you want to run the programme again ? : yes

enter your response : 2

enter your account number : 32443242

enter the amount to be deposited : 32443

do you want to run the programme again ? : yes

enter your response : 3

enter the acount number from which you want to withdraw : 22144141

enter the amount which you want to withdraw : 2400

do you want to run the programme again ? : no