Experiment No: 1

Program : Input any number from user and calculate factorial of a number

# Program to calculate factorial of enter ed number

num = int(input("Enter any number : "))
fact = 1
n = num # storing num in n f or printing
while num>1: # loop to iterate fr om n to 2
fact = fact * num
num-=1

print("Factorial of ", n , " is :",fact)

OUTPUT
Enter any number :6
Factorial of 6 is : 720

Page : 1
 Experiment No:2

Program : Input any number from user and check it is Prime no. or not

#Program to input any number from user

#Check it is Prime number of not
import math
num = int(input("Enter any number :" ))
isPrime=True
for i in range(2,int(math.sqrt(num))+1) :
if num % i == 0:
isPrime=False

if isPrime:
print("## Number is Prime ##")
else:
print("## Number is not Prime ##")

OUTPUT
Enter any number :117
## Number is not Prime ##
>>>
Enter any number :119
## Number is not Prime ##
>>>
Enter any number :113
## Number is Prime ##
>>>
Enter any number :7
## Number is Prime ##
>>>
Enter any number :19
## Number is Prime ##

Page : 2
 Experiment No: 3

Program : Write a program to find sum of elements of List recursively

#Program to find sum of elements of list recursively

def findSum(lst,num):
if num==0:
return 0
else:
return lst[num-1]+findSum(lst,num-1)

mylist = [] # Empty List

#Loop to input in list
num = int(input("Enter how many number :"))
for i in range(num):
n = int(input("Enter Element "+str(i+1)+":"))
mylist.append(n) #Adding number to list

sum = findSum(mylist, len(mylist))

print("Sum of List items ",mylist, " is :",sum)

OUTPUT
Enter how many number :6
Enter Element 1:10
Enter Element 2:20
Enter Element 3:30
Enter Element 4:40
Enter Element 5:50
Enter Element 6:60
Sum of List items [10, 20, 30, 40, 50, 60] is : 210

Page : 3
 Experiment No: 4

Program : Write a program to calculate the nth term of Fibonacci series

#Program to find 'n'th term of fibonaccii series

#Fibonacci series : 0,1,1,2,3,5,8,13,21,34,55,89,...
#nth term will be counted from 1 not 0

def nthfiboterm(n):
if n<=1:
return n
else:
return (nthfiboterm(n-1)+n thfiboterm(n-2))

num = int(input("Enter the 'n' term to fi nd in fibonacci :"))

term =nthfiboterm(num)
print(num,"th term of fibonacci series i s :",term)

OUTPUT
Enter the 'n' term to find in fibonacci :10
10 th term of fibonacci series is : 55

Page : 4
 Experiment No: 5

Program : Program to search any word in given string/sentence

#Program to find the occurence of any word in a string

def countWord(str1,word):
s = str1.split()
count=0
for w in s:
if w==word:
count+=1
return count

str1 = input("Enter any sentence :")

word = input("Enter word to search in sentence :")
count = countWord(str1,word)
if count==0:
print("## Sorry! ",word," not present ")
else:
print("## ",word," occurs ",count," times ## ")

OUTPUT

Enter any sentence :my computer your computer our computer everyones computer
Enter word to search in sentence :computer
## computer occurs 4 times ##

Enter any sentence :learning python is fun

Enter word to search in sentence :java
## Sorry! java not present

Page : 5
 Experiment No: 6

Program : Program to read and display file content line by line with each
word separated by „#‟

#Program to read content of file line by line

#and display each word separated by '#'

f = open("file1.txt")

for line in f:
words = line.split()
for w in words:
print(w+'#',end='')
print()
f.close()

NOTE : if the original content of file is:

India is my country
I love python
Python learning is fun

OUTPUT
India#is#my#country#
I#love#python#
Python#learning#is#fun#

Page : 6
 Experiment No: 7

Program : Program to read the content of file and display the total number of
consonants, uppercase, vowels and lower case characters‟

#Program to read content of file

#and display total number of vowels, consonants, lowercase and uppercase characters

f = open("file1.txt")
v=0
c=0
u=0
l=0
o=0
data = f.read()
vowels=['a','e','i','o','u']
for ch in data:
if ch.isalpha():
if ch.lower() in vowels:
v+=1
else:
c+=1
if ch.isupper():
u+=1
elif ch.islower():
l+=1
elif ch!=' ' and ch!='\n':
o+=1
print("Total Vowels in file :",v)
print("Total Consonants in file n :",c)
print("Total Capital letters in file :",u)
print("Total Small letters in file :",l)
print("Total Other than letters :",o)
f.close()
NOTE : if the original content of file is:
India is my country
I love python
Python learning is fun
123@

OUTPUT
Total Vowels in file : 16
Total Consonants in file n : 30
Total Capital letters in file :2
Total Small letters in file : 44
Total Other than letters :4

Page : 7
 Experiment No: 8

Program : Program to create binary file to store Rollno and Name, Search
any Rollno and display name if Rollno found otherwise “Rollno not found”

#Program to create a binary file to store Rollno and name

#Search for Rollno and display record if found
#otherwise "Roll no. not found"

import pickle
student=[]
f=open('student.dat','wb')
ans='y'
while ans.lower()=='y':
roll = int(input("Enter Roll Number :"))
name = input("Enter Name :")
student.append([roll,name])
ans=input("Add More ?(Y)")
pickle.dump(student,f)
f.close()
f=open('student.dat','rb')
student=[]
while True:
try:
student = pickle.load(f)
except EOFError:
break
ans='y'

while ans.lower()=='y':
found=False
r = int(input("Enter Roll number to search :"))
for s in student:
if s[0]==r:
print("## Name is :",s[1], " ##")
found=True
break
if not found:
print("####Sorry! Roll number not found ####")
ans=input("Search more ?(Y) :")
f.close()

Page : 8
OUTPUT
Enter Roll Number :1
Enter Name :Amit
Add More ?(Y)y

Enter Roll Number :2

Enter Name :Jasbir
Add More ?(Y)y

Enter Roll Number :3

Enter Name :Vikral
Add More ?(Y)n

Enter Roll number to search :2

## Name is : Jasbir ##
Search more ?(Y) :y

Enter Roll number to search :1

## Name is : Amit ##
Search more ?(Y) :y

Enter Roll number to search :4

####Sorry! Roll number not found ####
Search more ?(Y) :n

Page : 9
 Experiment No: 9

Program : Program to create binary file to store Rollno,Name and Marks and
update marks of entered Rollno

#Program to create a binary file to store Rollno and name

#Search for Rollno and display record if found
#otherwise "Roll no. not found"

import pickle
student=[]
f=open('student.dat','wb')
ans='y'
while ans.lower()=='y':
roll = int(input("Enter Roll Number :"))
name = input("Enter Name :")
marks = int(input("Enter Marks :"))
student.append([roll,name,marks])
ans=input("Add More ?(Y)")
pickle.dump(student,f)
f.close()
f=open('student.dat','rb+')
student=[]
while True:
try:
student = pickle.load(f)
except EOFError:
break
ans='y'
while ans.lower()=='y':
found=False
r = int(input("Enter Roll number to update :"))
for s in student:
if s[0]==r:
print("## Name is :",s[1], " ##")
print("## Current Marks is :",s[2]," ##")
m = int(input("Enter new marks :"))
s[2]=m
print("## Record Updated ##")
found=True
break
if not found:
print("####Sorry! Roll number not found ####")
ans=input("Update more ?(Y) :")
f.close()
Page : 10
OUTPUT
Enter Roll Number :1
Enter Name :Amit
Enter Marks :99
Add More ?(Y)y

Enter Roll Number :2

Enter Name :Vikrant
Enter Marks :88
Add More ?(Y)y

Enter Roll Number :3

Enter Name :Nitin
Enter Marks :66
Add More ?(Y)n

Enter Roll number to update :2

## Name is : Vikrant ##
## Current Marks is : 88 ##
Enter new marks :90
## Record Updated ##
Update more ?(Y) :y

Enter Roll number to update :2

## Name is : Vikrant ##
## Current Marks is : 90 ##
Enter new marks :95
## Record Updated ##
Update more ?(Y) :n

Page : 11
 Experiment No: 10

Program : 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.

#Program to read line from file and wrwrite it to another line

#Except for those line which containsletter 'a'

f1 = open("file2.txt")
f2 = open("file2copy.txt","w")

for line in f1:

if 'a' not in line:
f2.write(line)
print(“## File Copied Successfully! ## ”)
f1.close()
f2.close()

NOTE: Content of file2.txt

a quick brown fox
one two three four
five six seven
India is my country
eight nine ten
bye!

OUTPUT

## File Copied Successfully! ##

NOTE: After copy content of file2c opy.txt

one two three four
five six seven
eight nine ten
bye!

Page : 12
 Experiment No: 11

Program : Program to create CSV file and store empno,name,salary and

search any empno and display name,salary and if not found appropriate
message.

import csv
with open('myfile.csv',mode='a') as csvfile:
mywriter = csv.writer(csvfile,delimiter=',')
ans='y'
while ans.lower()=='y':
eno=int(input("Enter Employee Number "))
name=input("Enter Employee Name ")
salary=int(input("Enter Employee Salary :"))
mywriter.writerow([eno,name,salary])
print("## Data Saved... ##")
ans=input("Add More ?")
ans='y'
with open('myfile.csv',mode='r') as csvfile:
myreader = csv.reader(csvfile,delimiter=',')
while ans=='y':
found=False
e = int(input("Enter Employee Number to search :"))
for row in myreader:
if len(row)!=0:
if int(row[0])==e:
print("============================")
print("NAME :",row[1])
print("SALARY :",row[2])
found=True
break
if not found:
print("==========================")
print(" EMPNO NOT FOUND")
print("==========================")
ans = input("Search More ? (Y)")

Page : 13
Enter Employee Number 1

Enter Employee Name Amit

Enter Employee Salary :90000

## Data Saved... ##

Add More ?y

Enter Employee Number 2

Enter Employee Name Sunil

Enter Employee Salary :80000

## Data Saved... ##

Add More ?y

Enter Employee Number 3

Enter Employee Name Satya

Enter Employee Salary :75000

## Data Saved... ##

Add More ?n

Enter Employee Number to search :2

============================

NAME : Sunil

SALARY : 80000

Search More ? (Y)y

Enter Employee Number to search :3

============================

NAME : Satya

SALARY : 75000

Search More ? (Y)y

Enter Employee Number to search :4

==========================

EMPNO NOT FOUND

==========================

Search More ? (Y)n

Page : 14
 Experiment No: 12

Program : Program to generate random number 1-6, simulating a dice

# Program to generate random num ber between 1 - 6

# To simulate the dice
import random
import time
print("Press CTRL+C to stop the dice ")
play='y'
while play=='y':
try:
while True:
for i in range(10):
print()
n = random.rand int(1,6)
print(n,end='')
time.sleep(.00001 )
except KeyboardInterrupt:
print("Your Number is :",n)
ans=input("Play More? (Y) :")
if ans.lower()!='y':
play='n'
break

OUTPUT

4Your Number is : 4
Play More? (Y) :y
Your Number is : 3
Play More? (Y) :y
Your Number is : 2
Play More? (Y) :n

Page : 15
 Experiment No: 13

Program 1: Program to implement Stack in Python using List

def isEmpty(S):
if len(S)==0:
return True
else:
return False

def Push(S,item):
S.append(item)
top=len(S)-1

def Pop(S):
if isEmpty(S):
return "Underflow"
else:
val = S.pop()
if len(S)==0:
top=None
else:
top=len(S)-1
return val

def Peek(S):
if isEmpty(S):
return "Underflow"
else:
top=len(S)-1
return S[top]

def Show(S):
if isEmpty(S):
print("Sorry No items in Stack ")
else:
t = len(S)-1
print("(Top)",end=' ')
while(t>=0):
print(S[t],"<==",end=' ')
t-=1
print()

Page : 16
# main begins here
S=[] #Stack
top=None
while True:
print("**** STACK DEMONSTRATION ******")
print("1. PUSH ")
print("2. POP")
print("3. PEEK")
print("4. SHOW STACK ")
print("0. EXIT")
ch = int(input("Enter your choice :"))
if ch==1:
val = int(input("Enter Item to Push :"))
Push(S,val)
elif ch==2:
val = Pop(S)
if val=="Underflow":
print("Stack is Empty")
else:
print("\nDeleted Item was :",val)
elif ch==3:
val = Peek(S)
if val=="Underflow":
print("Stack Empty")
else:
print("Top Item :",val)
elif ch==4:
Show(S)
elif ch==0:
print("Bye")
break

OUTPUT
**** STACK DEMONSTRATION ******
1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :1
Enter Item to Push :10

Cont…
Page : 17
**** STACK DEMONSTRATION ******
1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :1
Enter Item to Push :20

**** STACK DEMONSTRATION ******

1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :1
Enter Item to Push :30

**** STACK DEMONSTRATION ******

1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :4
(Top) 30 <== 20 <== 10 <==

**** STACK DEMONSTRATION ******

1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :3
Top Item : 30

**** STACK DEMONSTRATION ******

1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :2

Deleted Item was : 30

Page : 18
**** STACK DEMONSTRATION ******
1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :4
(Top) 20 <== 10 <==

**** STACK DEMONSTRATION ******

1. PUSH
2. POP
3. PEEK
4. SHOW STACK
0. EXIT
Enter your choice :0
Bye

Page : 19
 Experiment No: 14

Program : Program to take 10 sample phishing email, and find the most
common word occurring

#Program to take 10 sample phishing

#and count the most commonly occ uring word
phishingemail=[
"jackpotwin@lottery.com",
"claimtheprize@mymoney.com
"youarethewinner@lottery.com",
"luckywinner@mymoney.com ,
"spinthewheel@flipkart.com",
"dealwinner@snapdeal.com"
"luckywinner@snapdeal.com"
"luckyjackpot@americanlotter
"claimtheprize@lootolottery.co
"youarelucky@mymoney.com"
]
myd={}
for e in phishingemail:
x=e.split('@')
for w in x:
if w not in myd:
myd[w]=1
else:
myd[w]+=1
key_max = max(myd,key=myd.get)
print("Most Common Occuring word is :",key_max)

OUTPUT

Most Common Occuring word is : mymoney.com

Page : 20
 Experiment No: 15

Program : program to create a list of values inputted by user and sort in

increasing order

Page : 21
 Experiment No: 16

Program : program to print pattern A, AB, ABC

Page : 22
 MySQL Program Experiment No: 17
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii) which are based on tables

TABLE: TRANSACT
TRNO ANO AMOUNT TYPE DOT
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 102 12000 Deposit 2017-11-06

(i) To display details of all transactions of TYPE Withdraw from TRANSACT table
(ii) To display ANO and AMOUNT of all Deposit and Withdrawals done in month of
„May‟ 2017 from table TRANSACT
(iii) To display first date of transaction (DOT) from table TRANSACT for Account having ANO as 102
(iv) To display ANO, ANAME, AMOUNT and DOT of those persons from ACCOUNT and
TRANSACT table who have done transaction less than or equal to 3000
(v) SELECT ANO, ANAME FROM ACCOUNT
WHERE ADDRESS NOT IN ('CHENNAI', 'BANGALORE');
(vi) SELECT DISTINCT ANO FROM TRANSACT
(vii) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT GROUP
BY ANO HAVING COUNT(*)> 1
(viii) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT WHERE
DOT <= '2017-10-01'
Output
(i) Select * from TRANSACT where TYPE=’Withdraw’;
(ii) Select ANO, AMOUNT from TRANSACT where DOT like ‘%-05-%’;
(iii) Select MIN(DOT) from TRANSACT where ANO=102
(iv) Select ANO,T.ANO,ANAME,AMOUNT from ACCOUNT A, TRANSACT T where A.ANO = T.ANO and
AMOUNT<=3000;
(v)
ANO ANAME
103 Ali Reza
105 Simran Kaur
(vi)
ANO
101
103
102
(vii)
(viii)

Page : 23
 MySQL Program Experiment No: 18
Consider the following tables EMP and SALGRADE, write the query for (i) to (vi) and output
for (vii) to (x)
TABLE: EMPLOYEE
ECODE NAME DESIG SGRADE DOJ DOB
101 Vikrant Executive S03 2003-03-23 1980-01-13
102 Ravi Head-IT S02 2010-02-12 1987-07-22
103 John Cena Receptionist S03 2009-06-24 1983-02-24
105 Azhar Ansari GM S02 2009-08-11 1984-03-03
108 Priyam Sen CEO S01 2004-12-29 1982-01-19
TABLE: SALGRADE
SGRADE SALARY HRA
S01 56000 18000
S02 32000 12000
S03 24000 8000
(i) To display details of all employee in descending order of their DOJ
(ii) To display NAME AND DESIG of those employees whose sgrade is either „S02‟ or
„S03‟
(iii) To display NAME, DESIG, SGRADE of those employee who joined in the year 2009
(iv) To display all SGRADE, ANNUAL_SALARY from table SALGRADE [where
ANNUAL_SALARY = SALARY*12]
(v) To display number of employee working in each SALGRADE from table EMPLOYEE
(vi) To display NAME, DESIG, SALARY, HRA from tables EMPLOYEE and SALGRADE
where SALARY is less than 50000
(vii) Select MIN(DOJ), MAX(DOB) from employee;
(viii) Select SGrade,Salary+HRA from SalGrade where Sgrade=‟S02‟
(ix) Select count(distinct sgrade) from employee
(x) Select sum(salary), avg(salary) from salgrade

OUTPUT
(i) SELECT * FROM EMPLOYEE ORDER BY DOJ DESC
(ii) SELECT NAME,DESIG FROM EMPLOYEE WHERE SGRADE IN ('S02','S03')
OR
SELECT NAME,DESIG FROM EMPLOYEE WHERE SGRADE='S02' OR
SGRADE='S03'
(iii) SELECT NAME,DESIG,SGRADE FROM EMPLOYEE WHERE DOJ LIKE '2009%'
(iv) SELECT SGRADE,SALARY*12 ANNUAL_SALARY FROM SALGRADE
(v) SELECT SGRADE,COUNT(*) FROM EMPLOYEE GROUP BY SGRADE
(vi) SELECT NAME,DESIG,SALARY,HRA FROM EMPLOYEE E,SALGRADE S WHERE
E.SGRADE=S.SGRADE AND SALARY<=50000
(vii) MIN(DOJ) MAX(DOB)

2003-03-23 1987-07-22
(viii) SGRADE SALARY+HRA

S02 44000
(ix) COUNT(*)

3
(x) SUM(SALARY) AVG(SALARY)

112000 37333.33
Page : 24
 MySQL Program Experiment No: 19

Consider the following EMPLOYEE table write MYSQL command for (i) to (iv) and Outputs for
(v) to (viii)

EMPNO ENAME DEPT SALARY COMM

1 ANKIT HR 20000 1200
2 SUJEET ACCOUNTS 24000
3 VIJAY HR 28000 2000
4 NITIN SALES 18000 3000
5 VIKRAM SALES 22000 1700

(i) To display the name of employees starting from „V‟ in ascending order of their salary
(ii) To display the details of all SALES dept employee who are earning salary more than 20000
(iii) To count distinct department from the table
(iv) Change the salary of NITIN from 18000 to 20000
(v) To insert a new row in the table Employee
„6‟, „SUMIT‟,‟HR‟, 40000,2000
(vi) Select AVG(COMM) from Employee
(vii) Select ENAME,DEPT from Employee where Dept in(„HR‟,‟ACCOUNTS‟)
(viii) Select ENAME, SALARY+100 NEWSAL from Employee

OUTPUT

(i) select ename from employee where ename like 'V%' order by salary;
(ii) Select * from employee where dept='Sales' and salary>20000;
(iii) select count(distinct dept) from employee;
(iv) update employee set salary=20000 where ename='NITIN';
(v) insert into employee values(6,'SUMIT','HR',40000,2000)
(vi) 1980 (including record inserted in (v))
(vii) ENAME DEPT

ANKIT HR SUJEET
ACCOUNTS VIJAY
HR
(viii) ENAME NEWSAL

ANKIT 20100
SUJEET 24100
VIJAY 28100
NITIN 20100
VIKRAM 22100
SUMIT 40100

Page : 25
 MySQL Program Experiment No: 2 0

Considering the Visitor table data, write the query for (i) to (iv) and output for (v) to (viii)
VisitorID VisitorName Gender ComingFrom AmountPaid
1 Suman F Kanpur 2500
2 Indu F Lucknow 3000
3 Rachana F Haryana 2000
4 Vikram M Kanpur 4000
5 Rajesh M Kanpur 3000
6 Suresh M Allahabad 3600
7 Dinesh M Lucknow
8 Shikha F Varanasi 5000

(i) Write a query to display VisitorName, Coming From details of Female Visitors with Amount
Paid more than 3000
(ii) Write a query to display all coming from location uniquely
(iii) Write a query to insert the following values- 7,
„Shilpa‟,‟F‟,‟Lucknow‟,3000
(iv) Write a query to display all details of visitors in order of their AmountPaid from highest to
lowest
(v) Select VisitorName from Visitor where Gender=‟M‟;
(vi) Select AmountPaid+200 from Visitor where VisitorID=6;
(vii) Select Sum(AmountPaid) from Visitor where comingFrom=‟Kanpur‟;
(viii) Select Count(VisitorName) from Visitor where AmountPaid is NULL;

OUTPUT
(i) Select VisitorName,ComingFrom from Visitor where Gender='F' and
AmountPaid>3000
(ii) Select distinct ComingFrom from Visitor
(iii) insert into visitor values(7,'Shilpa','F','Lucknow',3000)
(iv) Select * from visitor order by AmountPaid desc
(v) VisitorName

Vikram Rajesh
Suresh Dinesh
(vi) AmountPaid+200

3800
(vii) Sum(AmountPaid)

9500
(viii) Count(VisitorName)
1

Page : 26
 MySQL Program Experiment No: 2 1
Consider the table SHOPPE and ACCESSORIES, write the query for (i) to (v) and output for
(vi) to (x)

(i) To display Name and Price of all the Accessories in descending order of their Price
(ii) To display Id and Sname of all the Shoppe location in „Nehru Place‟
(iii) To display Name, Minimum and Maximum Price of each Name from ACCESSORIES table
(iv) To display Name, Price of all Accessories and their respective SName from table SHOPPE and
ACCESSORIES where Price is 5000 or more.
(v) To display all details of accessories where name contains word „Board‟;
(vi) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>5000;
(vii) SELECT AREA,COUNT(*) FROM SHOPPE GROUP BY AREA;
(viii) SELECT AVG(PRICE), MAX(PRICE) FROM ACCESSORIES WHERE PRICE>=10000;
(ix) SELECT NAME, PRICE*.05 DISCOUNT FROM ACCESSORIES WHERE ID IN („S02‟,‟S03‟)
(x) SELECT * FROM SHOPPE S, ACCESSORIES A WHERE S.ID = A.ID AND PRICE>=10000;

OUTPUT

(i) SELECT NAME,PRICE FROM ACCESSORIES ORDER BY PRICE DESC

(ii) SELECT ID,SNAME FROM SHOPPE WHERE AREA='NEHRU PLACE'
(iii) SELECT NAME,MIN(PRICE),MAX(PRICE) FROM ACCESSORIES GROUP BY NAME
(iv) SELECT NAME,PRICE,SNAME FROM SHOPPE S, ACCESSORIES A WHERE S.ID=A.ID
AND PRICE>=5000
(v) SELECT * FROM ACCESSORIES WHERE NAME LIKE „%BOARD%‟
(vi) NAME

Page : 27
 Mother Board LCD
(vii) AREA COUNT(*)

CP 2
GK II 1
Nehru Place 2
(viii) AVG(PRICE) MAX(PRICE)

12500 13000
(ix) NAME DISCOUNT

Keyboard 25
Mother Board 650
Keyboard 20
Hard Disk 225
(x) ID SNAME AREA NO NAME PRICE ID
S01 ABC Computronics CP A01 Mother board 12000 S01
S02 All Infotech media GK II A05 Mother board 13000 S02

Page : 28
 Experiment No: 22

Program : Program to connect with database and store record of employee and
display records.

import mysql.connector as mycon

con = mycon.connect(host='127.0.0.1',user='root',password="admin")
cur = con.cursor()
cur.execute("create database if not exists company")
cur.execute("use company")
cur.execute("create table if not exists employee(empno int, name varchar(20), dept
varchar(20),salary int)")
con.commit()
choice=None
while choice!=0:
print("1. ADD RECORD ")
print("2. DISPLAY RECORD ")
print("0. EXIT")
choice = int(input("Enter Choice :"))
if choice == 1:
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:
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:
con.close()
print("## Bye!! ##")
else:
print("## INVALID CHOICE ##")

Page : 29
OUTPUT

1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :1
Enter Employee Number :1
Enter Name :AMIT
Enter Department :SALES
Enter Salary :9000
## Data Saved ##
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :1
Enter Employee Number :2
Enter Name :NITIN
Enter Department :IT
Enter Salary :80000
## Data Saved ##
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :2
EMPNO NAME DEPARTMENT SALARY
1 AMIT SALES 9000
2 NITIN IT 80000
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :0
## Bye!! ##

Page : 30
 Experiment No: 23

Program : Program to connect with database and search employee number in

table employee and display record, if empno not found display appropriate
message.

import mysql.connector as mycon

con = mycon.connect(host='127.0.0.1',user='root',password="admin",
database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE SEARCHING FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO SEARCH :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
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])
ans=input("SEARCH MORE (Y) :")

OUTPUT

########################################
EMPLOYEE SEARCHING FORM
########################################

ENTER EMPNO TO SEARCH :1

EMPNO NAME DEPARTMENT SALARY
1 AMIT SALES 9000
SEARCH MORE (Y) :y
ENTER EMPNO TO SEARCH :2
EMPNO NAME DEPARTMENT SALARY
2 NITIN IT 80000
SEARCH MORE (Y) :y
ENTER EMPNO TO SEARCH :4
Sorry! Empno not found
SEARCH MORE (Y) :n

Page : 31
 Experiment No: 24

Program : Program to connect with database and update the employee

record of entered empno.

import mysql.connector as mycon

con = mycon.connect(host='127.0.0.1',user='root',password="admin",
database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE UPDATION FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO UPDATE :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
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])
choice=input("\n## ARE YOUR SURE TO UPDATE ? (Y) :")
if choice.lower()=='y':
print("== YOU CAN UPDATE ONLY DEPT AND SALARY ==")
print("== FOR EMPNO AND NAME CONTACT ADMIN ==")
d = input("ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT
TO CHANGE )")
if d=="":
d=row[2]
try:
s = int(input("ENTER NEW SALARY,(LEAVE BLANK IF NOT
WANT TO CHANGE ) "))
except:
s=row[3]
query="update employee set dept='{}',salary={} where empno={}".format
(d,s,eno)
cur.execute(query)
con.commit()
print("## RECORD UPDATED ## ")
ans=input("UPDATE MORE (Y) :")

Page : 32
OUTPUT

########################################
EMPLOYEE UPDATION FORM
########################################

ENTER EMPNO TO UPDATE :2

EMPNO NAME DEPARTMENT SALARY
2 NITIN IT 90000

## ARE YOUR SURE TO UPDATE ? (Y) :y

== YOU CAN UPDATE ONLY DEPT AND SALARY ==
== FOR EMPNO AND NAME CONTACT ADMIN ==
ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT TO CHANGE )
ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE )
## RECORD UPDATED ##
UPDATE MORE (Y) :y

ENTER EMPNO TO UPDATE :2

EMPNO NAME DEPARTMENT SALARY
2 NITIN IT 90000

## ARE YOUR SURE TO UPDATE ? (Y) :y

== YOU CAN UPDATE ONLY DEPT AND SALARY ==
== FOR EMPNO AND NAME CONTACT ADMIN ==
ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT TO CHANGE )SALES
ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE )
## RECORD UPDATED ##
UPDATE MORE (Y) :Y

ENTER EMPNO TO UPDATE :2

EMPNO NAME DEPARTMENT SALARY
2 NITIN SALES 90000

## ARE YOUR SURE TO UPDATE ? (Y) :Y

== YOU CAN UPDATE ONLY DEPT AND SALARY ==
== FOR EMPNO AND NAME CONTACT ADMIN ==
ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT TO CHANGE )
ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE ) 91000
## RECORD UPDATED ##
UPDATE MORE (Y) :Y

ENTER EMPNO TO UPDATE :2

EMPNO NAME DEPARTMENT SALARY
2 NITIN SALES 91000

## ARE YOUR SURE TO UPDATE ? (Y) :N

UPDATE MORE (Y) :N

Page : 33
 Experiment No: 25

Program : Program to connect with database and delete the record of

entered employee number.

import mysql.connector as mycon

con = mycon.connect(host='127.0.0.1',user='root',password="admin",
database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE DELETION FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO DELETE :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
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])
choice=input("\n## ARE YOUR SURE TO DELETE ? (Y) :")
if choice.lower()=='y':
query="delete from employee where empno={}".format(eno)
cur.execute(query)
con.commit()
print("=== RECORD DELETED SUCCESSFULLY! ===")
ans=input("DELETE MORE ? (Y) :")
OUTPUT
########################################
EMPLOYEE DELETION FORM
########################################

ENTER EMPNO TO DELETE :2

EMPNO NAME DEPARTMENT SALARY
2 NITIN SALES 91000

## ARE YOUR SURE TO DELETE ? (Y) :y

=== RECORD DELETED SUCCESSFULLY! ===
DELETE MORE ? (Y) :y
ENTER EMPNO TO DELETE :2
Sorry! Empno not found
DELETE MORE ? (Y) :n

Page : 32
Instruction for Writing Home :
• You have write all home work in Practical Copy only.
• Writing Experiment No. and Question is mandatory
Use blank pen – For Question
Use Blue Pen – For python code / For SQL statement/query
Use Pencil – For Output
• Write in very good handwriting
• Cover your practical file with brown cover/Orange cover
• Please write or paste your name,class,section,Roll no
• Maintain the format as per given below