Record Program No.
4:
TABLE : WORKER
ECODE NAME DESIG PLEVEL DOJ DOB
11 RadheShyam Supervisor P001 2004-09-12 1981-08-23
12 Chandernath Operator P003 2010-02-22 1987-07-12
13 Fizza Operator P003 2009-06-14 1983-10-14
14 Ameen Ahmed Mechanic P002 2006-08-21 1984-03-13
15 Sanya Clerk P002 2005-12-19 1983-06-09
18 Sarsa Supervisor P001 2010-01-20 1982-02-01
TABLE : PAYLEVEL
PLEVEL PAY ALLOWANCE
P001 26000 12000
P002 22000 10000
P003 12000 6000
SQL COMMANDS:
1. To display the details of all the WORKERS in descending order of their DOJ.
Ans: SELECT * FROM WORKER ORDER BY DOJ DESC;
Output:
ECODE NAME DESIG PLEVEL DOJ DOB
12 Chandernath Operator P003 2010-02-22 1987-07-12
18 Sarsa Supervisor P001 2010-01-20 1982-02-01
13 Fizza Operator P003 2009-06-14 1983-10-14
14 Ameen Ahmed Mechanic P002 2006-08-21 1984-03-13
15 Sanya Clerk P002 2005-12-19 1983-06-09
11 RadheShyam Supervisor P001 2004-09-12 1981-08-23
2. To display the NAME and DESIG of those WORKERS whose PLEVEL is either P001 or
P002.
Ans: SELECT NAME, DESIG FROM WORKER WHERE PLEVEL IN('P001','P002');
Output:
NAME DESIG
RadheShyam Supervisor
Ameen Ahmed Mechanic
Sanya Clerk
Sarsa Supervisor
3. To display the number of workers whose PAY+ALLOWANCE is more than 30000 for
every PLEVEL.
Ans: SELECT COUNT(*) FROM WORKER, PAYLEVEL WHERE WORKER.PLEVEL =
PAYLEVEL.PLEVEL AND PAY+ALLOWANCE >30000 GROUP BY
WORKER. PLEVEL;
Output:
COUNT(*)
2
2
4. To increase the ALLOWANCE by 1000 where the pay is greater than 20000.
Ans: UPDATE PAYLEVEL SET ALLOWANCE =ALLOWANCE+1000
WHERE PAY>20000;
Output:
2 rows affected
5. To display the number of Workers designation wise.
Ans: SELECT COUNT(*),DESIG FROM WORKER GROUP BY DESIG;
Output:
COUNT(*) DESIG
2 Supervisor
2 Operator
1 Mechanic
1 Clerk
Record Program No. 5:
TABLE : SPORTS
Scode Sportsname Participants Prizemoney Scheduledate
101 Carrom 2 5000 2012-01-23
102 Badminton 2 12000 2011-12-12
103 Table Tennis 4 8000 2012-02-14
105 Chess 2 9000 2012-01-01
108 Lawn Tennis 4 25000 2012-03-19
TABLE : COACH
Code Name Scode
1 Ravi 101
2 Mohan 108
3 Sameer 101
4 Shikhar 103
SQL COMMANDS:
1. To display scode, the number of coaches for each scode from the table coach and
display scode in descending order.
Ans. SELECT SCODE, COUNT(*) FROM COACH GROUP BY SCODE DESC;
Output:
Scode Count(*)
108 1
103 1
101 2
2. To display details of those sports and coachname which are having Prizemoney
more than 9000 and coachname ends with ‘n’.
Ans.: SELECT SPORTSNAME, NAME “COACHNAME” FROM SPORTS, COACH WHERE
SPORTS.SCODE=COACH.SCODE AND PRIZEMONEY > 9000 AND
NAME LIKE “%N”;
Output:
Sportsname COACHNAME
Lawn Tennis Mohan
3. To display the contents of the sports table with their coachname whose schedule
date is in the year 2019.
Ans: SELECT SPORTS.*, NAME FROM SPORTS JOIN COACH ON
SPORTS.SCODE=COACH.SCODE AND SCHEDULEDATE>=’2019-01-01’
AND SCHEDULEDATE<=’2019-12-31’;
Output:
None / No output
4. To display number of different participants from the table sports.
Ans: SELECT DISTINCT PARTICIPANTS FROM SPORTS;
Output:
distinct Participants
2
4
5. Increase the Participants by 6 for the sports carom, chess and badminton.
Ans: UPDATE SPORTS SET PARTICIPANTS=PARTICIPANTS+6 WHERE
SPORTSNAME IN (‘CAROM’,’CHESS’,’BADMINTON’);
Output :
3 rows updated
Record Program No. 6
TABLE - TEACHER
T_id Name Age Department Date_of_join Salary Gender
1 Jugal 34 Computer Science 10/01/2017 12000 M
2 Sharmila 31 History 24/03/2008 20000 F
3 Sandeep 32 Mathematics 12/12/2016 30000 M
4 Sangeeta 35 History 01/07/2015 40000 F
5 Rakesh 42 Mathematics 05/09/2007 25000 M
6 Shyam 50 History 27/06/2008 30000 M
7 Shiv Om 44 Computer Science 25/02/2017 21000 M
8 Shalakha 33 Mathematics 31/07/2018 20000 F
TABLE- POSTING
P_id Department Place
1 History Agra
2 Mathematics Raipur
3 Computer Science Delhi
SQL COMMANDS:
1. To display the total number of teachers Department wise.
Ans: SELECT DEPARTMENT, COUNT(*) FROM TEACHER
GROUP BY DEPARTMENT;
Output:
Department Count(*)
Computer Science 2
History 3
Mathematics 3
2. To display the Teacher details who have been posted in “Delhi”.
Ans: SELECT * FROM TEACHER NATURAL JOIN POSTING
WHERE PLACE = “DELHI”;
Output:
Department T_id Name Age Date_of_join Salary Gender P_id Place
Computer 1 Jugal 34 10/01/2017 12000 M 3 Delhi
Science
Computer 7 Shiv Om 44 25/02/2017 21000 M 3 Delhi
Science
3. To display the highest salary being paid in each department.
Ans: SELECT DEPARTMENT, MAX(SALARY) FROM TEACHER GROUP BY
DEPARTMENT;
Output:
Department Max(Salary)
Computer Science 21000
History 40000
Mathematics 30000
4. To display the total salary being paid for male and female separately
Ans: SELECT GENDER, SUM(SALARY) FROM TEACHER GROUP BY
GENDER
Output:
Gender Sum(Salary)
M 1,18,000
F 80,000
5. To increase the salary for the teachers by 10% who have joined in the year
2017 and 2018.
Ans: UPDATE TEACHER SET SALARY=SALARY+SALARY*0.1 WHERE
DATE_OF_JOIN BETWEEN ‘2017-01-01’ AND ‘2018-12-31’;
Output:
3 rows updated
Record Program No. 7
Program :
#Program using mysql connectivity for Student Table
def Display():
cursor.execute("select * from Student")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("%10s"%"AdmnNo.","%15s"%"Name","%9s"%"Gender","%15s"%"DOB",
"%20s"%"Stream","%10s"%"Average")
for row in data:
print("%10s"%row[0],"%15s"%row[1],"%9s"%row[2],"%15s"%row[3],
"%20s"%row[4],"%10s"%row[5])
def Select():
cursor.execute("select count(*) from Student where gender='m' and
stream='commerce'")
data=cursor.fetchall()
print("4. Number of Male Commerce students", data)
def Update():
try:
print("3. Updation of the average")
cursor.execute("update Student set average=average+5 where
stream='Science'")
cursor.execute("select * from Student where stream='Science'")
data=cursor.fetchall()
for row in data:
print(row)
except:
print("Updation not possible since average is exceeding 100")
def Delete():
print("5. Deleting records whose average is <40")
cursor.execute("delete from Student where average<40")
print("After deletion")
Display()
#main program
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="admin",database="sys")
if mycon.is_connected():
print("Successful")
cursor=mycon.cursor()
cursor.execute("create table Student(admno int primary key,sname
varchar(20),gender char(1),dob date, stream varchar(20), average float(5,2)
check(average>=0 and average<=100))")
print("1. Table Created")
cursor.execute("insert into Student values(1023,'aaa','m','2001 -01-01','Science',98)")
cursor.execute("insert into Student values(1045,'bbb','f','2000-11-11',
'Commerce',90)")
cursor.execute("insert into Student values(1010,'ccc','m','2002 -11-21','Science',33)")
cursor.execute("insert into Student values(1012,'ddd','m','2001 -05-11',
'Commerce',89)")
cursor.execute("insert into Student values(1001,'eee','m','2000-12-10',
'Commerce',89)")
print("2. 5 Records Inserted")
Display()
Select()
Update()
Delete()
Output:
Record Program No. 8
#Program using mysql connectivity
def insertevent():
cursor.execute("insert into Event values(101,'Birthday',10, 'C102')")
cursor.execute("insert into Event values(102,'PromotionParty ', 20,'C103')")
cursor.execute("insert into Event values(103,'Engagement', 12,'C102')")
cursor.execute("insert into Event values(104,'Wedding',15, 'C104')")
cursor.execute("insert into Event values(105, 'Birthday', 17, 'C101')")
print("2. Records inserted for Event Table")
cursor.execute("select * from Event")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("%10s"%"Event Id.","%15s"%"EventName","%9s"%"No.of Performers",
"%15s"%"CelebrityId")
for row in data:
print("%10s"%row[0],"%15s"%row[1],"%9s"%row[2],"%15s"%row[3])
def insertcelebrity():
cursor.execute("insert into Celebrity values('C101','Faiz Khan', 991019560,
200000)")
cursor.execute("insert into Celebrity values('C102','Sanjay Kumar',893466448,
250000)")
cursor.execute("insert into Celebrity values('C103','Neera Khan Kapoor',
981165685,300000)")
cursor.execute("insert into Celebrity values('C104','Reena Bhatia',
658777564,100000)")
print("2. Records inserted for Celebrity Table")
cursor.execute("select * from Celebrity")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("%15s"%"Celebrity Id.","%25s"%"Name","%13s"%"Phone No.",
"%15s"%"Feecharged")
for row in data:
print("%15s"%row[0],"%25s"%row[1],"%13s"%row[2],"%15s"%row[3])
def Display():
print("3. Displaying Eventname,Celebrity name amd feecharged who charge more
than 2,00,000")
cursor.execute("select Eventname, Name, Feecharged from Event, Celebrity where
Event.CelebrityId =Celebrity.CelebrityId and Feecharged> 200000")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("%15s"%"Event Name","%25s"%"Name","%15s"%"Feecharged")
for row in data:
print("%15s"%row[0],"%25s"%row[1],"%15s"%row[2])
def Update():
try:
print("4. Increase the Feecharged by 10,000 for the events where Number of
performers is greater than 15")
cursor.execute("update Event, Celebrity set Feecharged = Feecharged+10000
where Event.CelebrityId = Celebrity. CelebrityId and Noperf>15")
cursor.execute("select * from Celebrity")
data=cursor.fetchall()
print("%15s"%"Celebrity Id.","%25s"%"Name","%13s"%"Phone No.",
"%15s"%"Feecharged")
for row in data:
print("%15s"%row[0],"%25s"%row[1],"%13s"%row[2],"%15s"%row[3])
except:
print("Updation is not possible")
def Delete():
print("5. Deleting records from Celebrity whose name starts with R")
cursor.execute("delete from Celebrity where Name like'R%'")
cursor.execute("select * from Celebrity")
data=cursor.fetchall()
print("%15s"%"Celebrity Id.","%25s"%"Name","%13s"%"Phone No.",
"%15s"%"Feecharged")
for row in data:
print("%15s"%row[0],"%25s"%row[1],"%13s"%row[2],"%15s"%row[3])
#main program
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="admin",database="sys")
if mycon.is_connected():
print("Successful")
cursor=mycon.cursor()
cursor.execute("create table Event(Eventid int primary key,Eventname
varchar(20),Noperf int,CelebrityID varchar(5))")
print("1. Table Event created")
cursor.execute("create table Celebrity(CelebrityId varchar(5) primary key, Name
varchar(20), Phone int(20), Feecharged float(10,2))")
print("1. Table Celebrity Created")
# Inserting 5 rows in Event
insertevent()
#Inserting 4 rows in Celebrity
insertcelebrity()
Display()
Update()
Delete()
Output:
Record Program No. 9
Program:
#Program using mysql connectivity
def insertemployee():
for i in range(5):
eno=int(input("Enter the Employee number "))
ename=input("Enter the Employee name ")
desig=input("Enter the designation ")
sal=float(input("Enter the salary "))
lea=float(input("Enter the leave details "))
bon=float(input("Enter the bonus details "))
cursor.execute("insert into Employee values({0},'{1}','{2}',{3},{4},
{5})".format(eno,ename,desig,sal,lea,bon))
print("2. Records inserted for Employee table")
cursor.execute("select * from Employee")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("%10s"%"Emp No.","%15s"%"EmpName","%20s"%"Desig","%15s"%"Salary",
"%20s"%"Leave","%10s"%"Bonus")
for row in data:
print("%10s"%row[0],"%15s"%row[1],"%20s"%row[2],
"%15s"%row[3],"%20s"%row[4],"%10s"%row[5])
def insertinsurance():
for i in range(5):
eno=int(input("Enter the Employee number "))
insid=int(input("Enter the Insurance ID "))
lic=int(input("Enter the LIC Amount "))
cursor.execute("insert into Insurance values({},{},{})".format(eno,insid,lic))
print("2. Records inserted for Insurance table")
cursor.execute("select * from Insurance")
data=cursor.fetchall()
count=cursor.rowcount
print("Rows: ",count)
print("%10s"%"Emp No.","%15s"%"InsuranceId","%9s"%"LICAmt")
for row in data:
print("%10s"%row[0],"%15s"%row[1],"%9s"%row[2])
def Display1():
print("3. Display the total salary of each designation of those employees whose
name starts with R")
cursor.execute("select sum(salary),desig from Employee where ename like 'R%'
group by desig")
data=cursor.fetchall()
print("Employees total salary designation wise based on the name starting with R")
print("%10s"%"Sum(Salary).","%15s"%"Designation")
for row in data:
print("%10s"%row[0],"%15s"%row[1])
def Display2():
print("4. Display the Employee Number and Name who has th e LIC insurance with
the amount 50000.")
cursor.execute("select Employee.empno, ename from Employee, Insurance
where Employee.empno =Insurance.empno and lic=50000")
data=cursor.fetchall()
print("%10s"%"Emp No.","%15s"%"EmpName")
for row in data:
print("%10s"%row[0],"%15s"%row[1])
def Update():
print("5. Increase the salary by 10% for those employees whose designation is
clerk")
cursor.execute("update Employee set salary=salary*1.1 where desig='Clerk'")
cursor.execute("select * from Employee where desig='Clerk'")
data=cursor.fetchall()
print("%10s"%"Emp No.","%15s"%"EmpName","%20s"%"Desig","%15s"%"Salary",
"%20s"%"Leave","%10s"%"Bonus")
for row in data:
print("%10s"%row[0],"%15s"%row[1],"%20s"%row[2],
"%15s"%row[3],"%20s"%row[4],"%10s"%row[5])
#main program
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="admin",database="sys")
if mycon.is_connected():
print("Successful")
cursor=mycon.cursor()
cursor.execute("drop table employee,insurance")
cursor.execute("create table Employee(empno int primary key, ename varchar(20),
desig varchar(20),salary float(8,2),lea int,bon float)")
print("1. Employee Table created")
cursor.execute("create table Insurance(empno int, InsId int primary key, lic int)")
print("1. Insurance table created")
mycon.commit()
insertemployee()
insertinsurance()
Display1()
Display2()
Update()
Output:
Record Program No. 10:
Program:
#Program using text file
def writetxt():
fout=open("Replace.txt","w")
rep='y'
while(rep=='y'):
s=input("Enter the line to store : ")
fout.write(s)
fout.write('\n')
rep=input("Continue ")
fout.close()
def readtxt():
fin=open("Replace.txt")
fout=open("New.txt",'w')
for line in fin:
for i in line:
if i=='a' or i=='A':
print("Writing the line on New.txt",line)
fout.write(line)
break
fin.close()
fout.close()
print("Reading from the file New.txt")
fin=open("New.txt")
for line in fin:
print(line.rstrip('\n'))
#main program
print("\t\t\t Program to read a text file Replace.txt and write on the text file
New.txt")
print("Creating text file")
writetxt()
print("Reading from the file Replace.txt")
readtxt()
Output:
Creating text file
Enter the line to store : This is a text file program
Continue y
Enter the line to store : help function is useful for beginners
Continue y
Enter the line to store : try using functions
Continue y
Enter the line to store : which is helpful
Continue y
Enter the line to store : to learn python programming
Continue y
Enter the line to store : in an efficient way
Continue n
Reading from the file Replace.txt
Writing the line on New.txt This is a text file program
Writing the line on New.txt to learn python programming
Writing the line on New.txt in an efficient way
Reading from the file New.txt
This is a text file program
to learn python programming
in an efficient way
