PROGRAM-11
SQL COMMANDS:
1. To display the 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 ORDER 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 data 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
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 ('Carrom', 'Chess', 'Badminton');
OUTPUT:
3 rows update
PROGRAM:11
TABLE: SPORTS
SCOD SPORTNAME PARTICIPANTS PRIZEMONEY SCHEDULE_DATE
E
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-09
TABLE: COACH
CODE NAME SCODE
1 RAVI 101
2 MOHAN 108
3 SAMEER 101
4 SHIKAR 103
Program 12
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 Science 1 Jugal 34 2017-01-10 12000 M 3 Delhi
Computer Science 7 Shiv Om 44 2017-02-25 21000 M 3 Delhi
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 118000
F 80000
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
PROGRAM 12
TABLE TEACHER
I_id Name Age Department Date-of-join Salary Gender
1 Jugal 34 Computerscience 2017-01-10 12000 M
2 Sharmila 32 History 2008-03-24 20000 F
3 Sandeep 32 Mathematics 2016-12-12 30000 M
4 Sangeetha 35 History 2015-07-01 40000 F
5 Rakesh 42 Mathematics 2007-09-05 25000 M
6 Shyam 50 History 2008-06-27 30000 M
7 Shivom 44 Computerscience 2017-02-25 21000 M
8 Shalakha 33 Mathematics 2018-07-31 20000 F
TABLE POSTING
P_id Department Place
1 History Agra
2 Mathematics Raipur
3 Computerscienc Delhi
e
________________________________________________________________________________
FACTORIAL
PROGRAM DEFINITION:
Write a program in Python to find the factorial for a given number.
OUTPUT:
Please enter any number to find factorial: 6
The factorial of 6 is: 720
PROGRAM 13
def factorial(num):
fact=1
for i in range(1, num+1):
fact=fact*i
return fact
number=int(input("Please enter any number to find factorial: "))
result=factorial(number)
print("The factorial of", number ,"is:", result)
_______________________________________________________________________________________
PALINDROME
PROGRAM DEFINITION:
Write a program in Python to find given string is palindrome or not.
OUTPUT:
Enter string:RACECAR
The given string is Palindrome
PROGRAM 14
def isPalindrome(str):
for i in range(0, int(len(str)/2)):
if str[i]!=str[len(str)-i-1]:
return False
return True
s=input("Enter string:")
ans = isPalindrome(s)
if (ans):
print("The given string is Palindrome")
else:
print("The given string is not a Palindrome")
PROGRAM 15
PROGRAM DEFINITION:
To write a program using functions to create a text file”story.txt”,read lines from the text file “story.txt”
and display those words whose length is less than 4 character.
File Contents:
this is the sample text file
created for the lab program exercise
OUTPUT:
words less than 4 are
is
the
for
the
lab
PROGRAM 15
def fourletterwords():
L=[]
x=open ("story.txt")
print("words less than 4 are")
for i in x:
L=i.split()
for a in L:
if len(a)<4:
print(a)
fourletterwords()
ARITHMETIC OPERATION
PROGRAM DEFINITION:
Write a menu driven Python Program to perform Arithmetic operations (+,-*, /) based on the user’s choice.
PROGRAM:16
print("1. Addition")
print("2. Subtraction")
print("3. Multiplication")
print("4. Division")
opt=int(input("Enter your choice:"))
a=int(input("Enter the First Number:"))
b=int(input("Enter the Second Number:"))
if opt==1:
c=a+b
print("The Addition of two number is:",c)
elif opt==2:
c=a-b
print("The Subtraction of two number is:",c)
elif opt==3:
c=a*b
print("The Multiplication of two number is:",c)
elif opt==4:
if b==0:
print("Enter any other number other than 0")
else:
c=a/b
print("The Division of two number is:",c)
else:
print("Invalid Option")
OUTPUT:
1. Addition
2. Subtraction
3. Multiplication
4. Division
Enter your choice:4
Enter the First Number:557
Enter the Second Number:8
The Division of two number is: 69.625
PROGRAM 17
PROGRAM DEFINITION:
Integrate SQL with Python by importing the MySQL module to search an employee using the eno,if it is present
in table display the record.
PROGRAM 17
import mysql.connector as mc
mycon=mc.connect(host='localhost',user='root',
password='Shan@p6iya',database="sys")
if mycon.is_connected( ):
print("Py->Sql connected")
eno=int(input("Enter num:"))
mcursor=mycon.cursor( )
mcursor.execute("select * from doctor")
allrow=mcursor.fetchall( )
for row in allrow:
if row[0]==eno:
print(row)
mycon.commit( )
mycon.close( )
OUTPUT:
Py->Sql connected
Enter num:109
(109, 'K George', 'MEDICINE', 'M', 9)
PROGRAM-18
PROGRAM DEFINITION:
To create table for company,customer and execute the given commands using SQL
TABLE:COMPANY
TABLE:CUSTOMER
1. To display those company name which are having price less than 30000.
ANS: SELECT company.NAME AS CompanyName
FROM customer, company
WHERE company.CID = customer.CID
AND customer.PRICE < 30000;
2. To display the name of the companies in reverse alphabetical order.
ANS: SELECT DISTINCT NAME FROM COMPANY ORDER BY NAME DESC;
3. To increase the price by 1000 for those customer whose name starts with ‘S’
ANS:UPDATE CUSTOMER SET PRICE=PRICE+1000 WHERE NAME LIKE ‘S%’;
4. To add one more column total price with decimal (10,2) to the table customer
ANS:ALTER TABLE CUSTOMER ADD TOTALPRICE DECIMAL(10,2);
SELECT * FROM CUSTOMER;
5. To display the details of company where product name as mobile.
ANS:SELECT * FROM COMPANY WHERE PRODUCTNAME=’MOBILE’;
6. To delete the details of company where CID as 111.
ANS: DELETE FROM COMPANY WHERE CID=111;
PROGRAM-19
PROGRAM DEFINITION:
To create table for student and execute the given commands using SQL
TABLE:STUDENT
1. To display the details of the students.
ANS: SELECT * FROM STUDENT;
2. To update the total marks of a student.
ANS:UPDATE STUDENT SET TOT=380 WHERE ADMNO =1005;
3. To display the students count group wise.
Ans: Select Grp,COUNT(*) from STUDENT GROUP BY Grp;
4. To display the minimum, maximum and sum of total marks.
Ans: select MIN(Tot),MAX(Tot),SUM(Tot)from STUDENT;
5. To add new attribute grade, modify data type and drop the attribute grade
ANS: i)ALTER TABLE STUDENT ADD GRADE CHAR(10);
ii) ALTER TABLE STUDENT DROP GRADE;
6. TO Delete the student row.
ANS: DELETE FROM STUDENT WHERE ADMNO=1002;
PROGRAM-20
PROGRAM DEFINITION:
To fetch, update and delete the data of MYSQL using Python.
i)Create 2 Tables
Employee-Empno,Name,Desig,Salary,Leave,Bonus
Insurance-Empno,LIC
ii)Insert 5 records in both the tables by accepting the values of the attributes from the user.
iii)Display the total salary of each designation of those emp whose name starts ‘R’.
iv)Display the Empno and name who has LIC insurance.
v)Update the salary by 10% for those employee whose Desig is Clerk.
OUTPUT:
PROGRAM 20:
PROGRAM 21
PROGRAM DEFINITION:
Program to connect python with MYSQL using database connectivity and perform the following
operation on data in database:
Insert, Fetch, Update and Delete the data.
i)Create a table student with admno, sname,gender,dob,stream,average.
ii)Insert 5 records into the students table by accepting from the user.
iii)Increase the marks by 5 for those students who belong to science stream.
iv)Display the number of male student who belong to commerce stream
v)Delete the records of those students whose average <40.
OUTPUT:
Successful
1. Table created
2. 5 Records Inserted
Rows: 5
AdmnNo. Name Gender DOB Stream Average
1001 eee m 2000-12-10 Commerce 98.0
1010 ccc m 2002-11-21 Science 33.0
1012 ddd m 2001-05-11 Commerce 89.0
1023 aaa m 2001-01-01 Science 98.0
1045 bbb f 2000-11-11 Commerce 90.0
4. Number of Male Commerce students 2
3. Updation of the average
Updation not possible since the average is exceeding 100
5. Deleting the records whose average is < 40
After Deletion
Rows: 4
AdmnNo. Name Gender DOB Stream Average
1001 eee m 2000-12-10 Commerce 98.0
1012 ddd m 2001-05-11 Commerce 89.0
1023 aaa m 2001-01-01 Science 98.0
1045 bbb f 2000-11-11 Commerce 90.0
PROGRAM 21:
PROGRAM 22
PROGRAM DEFINITION:
Program to connect Python with MYSQL using database connectivity and perform the following
operations on data in database:
i) Create 2 tables
Event -EventID,Eventname,NumPerformaers,CelebrityID
Celebrity-CelebrityID,Name,Phone,Feecharged
ii)Insert 5 records in both the table
iii)Display the Eventname,Name of celebrity an Feecharged for those celebrities who charge more than
200000.
iv)Increase the Feecharged by 10000 for the events whose number of Performers is >15
PROGRAM 23:
PROGRAM DEFINITION:
To write a Python program to read from the file Exam.txt and display all the lines which ends with
the word “health”.
OUTPUT:
PROGRAM 23:
LIST MANIPULATION
PROGRAM DEFINITION:
To write an interactive Python Program to
i)Accept a list of elements and exchange first half with second half
ii)Accept a list of words and display number of palindromes present in it.
OUTPUT:
PROGRAM 24:
