ARMY PUBLIC SCHOOL FATEHGARH

ACADEMIC YEAR : 2022-23

COMPUTER SCIENCE
PRACTICAL FILE

ON

PYTHON PROGRAMS & MYSQL

Submitted by: Submitted to:

Mr. Mahesh Upadhyay
ABHAY SINGH CHAUHAN

Roll No.-
 INDEX

P1. WAP to input a year and check whether the year is leap year or not.
P2. Write a function to write data into binary file marks.dat and display the records of students
who scored more than 95 marks.
P3. WAP to input a number and check whether it is prime number or not.
P4. WAP to print Fibonacci series up to n terms, also find sum of series.
P5. Writing to a CSV File with Tab Delimiter.
P6. WAP to print a string and the number of vowels present in it.
P7. Write a program to read a file story.txt and print the contents of file along with number of
vowels present in it.
P8. Write a program to read a file book.txt print the contents of file along with numbers of words
and frequency of word computer in it.
P9. Write a program to read a file Top.txt and print the contents of file along with the number of
lines starting with A.
P10. Write a function DigitSum() that takes a number and returns its digit sum.
P11. Write a recursive function ChkPrime() that checks a number for Prime.
P12. Write a function in Python to count and display number of vowels in text file.
P13. Write a method in python to write multiple line of text contents into a text file mylife.txt.
P14. Write a function in Phyton to read lines from a text file diary.txt, and display only those lines,
which are starting with an alphabet 'P'.
P15. Write a method in Python to read lines from a text file INDIA.TXT, to find and display the
occurrence of the word 'India'. For example, if the content of the file is:
P16. Program demonstrates how to pickle(serialize) dictionary object and write the object into the
binary file.
P17. Program demonstrates how to read data from binary file and unpickle it.
P18. Pickling and unpickling the list object using file.

P19. Write a menu driven program in Python that asks the user to add, display, and search records
of employee stored in a binary file. The employee record contains employee code, name and
salary. It should be stored in a list object. Your program should pickle the object and save it
to a binary file
P20. Write a menu driven program in Python that asks the user to add, display, and search records
of students stored in a binary file. The student record contains roll no, name and test score. It
should be stored in a dictionary object. Your program should pickle the object and save it to a
binary file.
P21. Reading data from a CSV file into a list.
P22. Write a menu based program to demonstrate operation on a stack.
P22. Python –MYSQL Connectivity
P23. Integrate SQL with Python by importing the MySQL module.
P24. Integrate SQL with Python by importing the pymysql module.
P25. MySQL Queries.

P1. # WAP to input a year and check whether the year is leap year or not

y=int(input("Enter the year: "))

if y%400==0:
print("The year is a Century Leap Year")
elif y%100!=0 and y%4==0:
print("Year is a Leap year")
else:
print("Year is not a leap year")

Output
P2. # Write a function to write data into binary file marks.dat and display he records of students who
scored more than 95 marks.

import pickle
def search_95plus():
f = open("marks.dat","ab")
while True:
rn=int(input("Enter the rollno:"))
sname=input("Enter the name:")
marks=int(input("Enter the marks:"))
rec=[]
data=[rn,sname,marks]
rec.append(data)
pickle.dump(rec,f)
ch=input("Wnat more records?Yes:")
if ch.lower() not in 'yes':
break
f.close()
f = open("marks.dat","rb")
cnt=0
try:
while True:
data = pickle.load(f)
for s in data:
if s[2]>95:
cnt+=1
print("Record:",cnt)
print("RollNO:",s[0])
print("Name:",s[1])
print("Marks:",s[2])
except Exception:
f.close()
search_95plus()

Output
P3. # WAP to input a number and check whether it is prime number or not

n=int(input("Enter the number"))

c=1
for i in range(2,n):
if n%i==0:
c=0
if c==1:
print("Number is prime")
else:
print("Number is not prime")

Output

P4. #WAP to print fibonacci series upto n terms, also find sum of series

n=int(input("Enter the number of terms in fibonacci series"))

a,b=0,1

s=a+b

print(a)
print(b)
for i in range(n-2):
print(a+b, end=" ")

a,b=b,a+b

s=s+b

print()

print("Sum of",n,"terms of series =",s)

Output

P5. # Writing to a CSV File with Tab Delimiter

import csv
with open('protagonist.csv', 'w') as file:
writer = csv.writer(file, delimiter = '\t')
writer.writerow(["SN", "Movie", "Protagonist"])
writer.writerow([1, "Lord of the Rings", "Frodo Baggins"])
writer.writerow([2, "Harry Potter", "Harry Potter"])

P6. #WAP to print a string and the number of vowels present in it

st=input("Enter the string")

print("Entered string =",st)
st=st.lower()
c=0
v=['a','e','i','o','u']
for i in st:

if i in v:

c+=1
print("Number of vowels in entered string =",c)

Output

P7. #Write a program to read a file story.txt and print the contents of file along with
number of vowels present in it

f=open("story.txt",'r')
st=f.read()
print("Contents of file :")
print(st)
c=0
v=['a','e','i','o','u']
for i in st:
if i.lower() in v:
c=c+1
print("*****FILE END*****")
print()
print("Number of vowels in the file =",c)

f.close()

Contents of file :
Python is an interpreted, high-level, general-purpose programming language. Created by Guido
van Rossum and first released in 1991.
Python's design philosophy emphasizes code readability.

Its language constructs and object-oriented approach aim to help programmers write clear, logical
code.

*****FILE END*****

Output
Number of vowels in the file = 114
P8. #Write a program to read a file book.txt print the contents of file along with
numbers of words and frequency of word computer in it.

f=open("book.txt","r")

L=f.readlines()

c=c1=0

v=['a','e','i','o','u']

print("Contents of file :")

for i in L:
print(i)
j=i.split()

for k in j:
if k.lower()=="computer":
c1=c1+1
for x in k:
if x .lower() in v:
c+=1
print("*****FILE END*****")

print()

print("Number of vowels in the file =",c)

print("Number of times 'computer' in the file =",c1)

f.close()

Contents of file :
Python is an interpreted, high-level, general-purpose computer programming language. Created
by Guido van Rossum and first released in 1991. Python's design philosophy emphasizes code
readability. Its language constructs and object-oriented approach aim to help programmers write
clear, logical code.
*****FILE END*****
Output
Number of vowels in the file = 92
Number of times 'computer' in the file = 1

P09. #Write a program to read a file Top.txt and print the contents of file along with
the number of lines starting with A

f=open("Top.txt","r")
st=f.readlines()
c=0
print("Contents of file :")
for i in st:
print(i)
if i[0]=="A":
c+=1
print("\n*****FILE END*****")
print()
print("Number of lines starting with 'A' =",c)

Contents of file :
Python is an interpreted, high-level, general-purpose programming language.
Created by Guido van Rossum and first released in 1991.
Python's design philosophy emphasizes code readability.
Its language constructs and object-oriented approach aim to help programmers write clear, logical
code.
*****FILE END*****

Output

Number of lines starting with 'A' = 0

P10. #Write a function DigitSum() that takes a number and returns its digit
sum

def DigitSum(n):
s=0
n=str(n)
for i in n:
s=s+int(i)
return s
n=int(input("Enter the number"))
print("Sum of digits =",DigitSum(n))

Output

P11. #Write a recursive function ChkPrime() that checks a number for

Prime

def ChkPrime(n,i):
while i<n:
if n%i==0:
return False
else:
i+=1
ChkPrime(n,i)
return True
n=int(input("Enter the number"))
if ChkPrime(n,2):
print("Number is prime")
else:
print("Number aren't prime")

Output

P12.# Write a function to count and display number of vowels in text file.

def count_vowels():
infile = open('myfile.txt'','r')
count = 0
data = infile.read()
for letter in data:
if letter in 'aeiouAEIOU':
count += 1

print('Number of vowels are',count)

infile.close()
# Call the count_vowels function.
count_vowels()

Output

P13. # Write a method in python to write multiple line of text contents into a text file
mylife.txt.

def writelines():
outfile = open('myfile.txt','w')
while True:
line = input('Enter line: ')
line += '\n'
outfile.write(line)
choice = input('Are there more lines y/n? ')
if choice == 'n':
break
outfile.close()

# Call the writelines function.

writelines()
Output

P14. # Write a function in Phyton to read lines from a text file diary.txt, and display
only those lines, which are starting with an alphabet 'P'.

If the contents of file is :

I hope you will please write to me from all the cities you visit.
Please accept them with the love and good wishes of your friend.
He never thought something so simple could please him so much.

The output should be:

Please accept them with the love and good wishes of your friend.

def readlines():
file = open('diary.txt','r')
for line in file:
if line[0] == 'P':
print(line)

file.close()

# Call the readlines function.

readlines()

P15.# Write a method in Python to read lines from a text file INDIA.TXT, to find and
display the occurrence of the word 'India'. For example, if the content of the file is:

India is the fastest-growing economy. India is looking for more investments around the globe. The
whole world is looking at India as a great market. Most of the Indians can foresee the heights that
India is capable of reaching.

The output should be 4.

def count_word():
file = open('india.txt','r')
count = 0

for line in file:

words = line.split()
for word in words:
if word == 'India':
 count += 1
print(count)
file.close()

# call the function count_word().

count_word()

Output
4

P16. # Program demonstrates how to pickle(serialize) dictionary object and write the
object into the binary file.

import pickle

phonebook = { 'Deepak':98107593, 'Saksham':92104584, 'John':87989898 }

outfile = open('phonebook.dat', 'wb') #open file in binary mode for writing.
pickle.dump(phonebook, outfile) #serialize the object and writing to file
outfile.close() #close the file

P17.# Program demonstrates how to read data from binary file and unpickle it.

import pickle

infile = open('phonebook.dat', 'rb') #open file in binary mode for reading

phonebook = pickle.load(infile) #reading the oject from file
print(phonebook) #display the phonebook
infile.close() #close the file

Output

{'Deepak': 98107593, 'Saksham': 92104584, 'John': 87989898}

P18.# Pickling and unpickling the list object using file.

import pickle

def write_record():
outfile = open('student.dat','wb') #opening a binary file in write mode
student = [1,'Nisha','XII'] #creating a list object
pickle.dump(student,outfile) #writing to file
 outfile.close() #close the file

def read_record():

infile = open('student.dat','rb') #opening the binary file in read mode

student = pickle.load(infile) #read object from file and unpickle
print(student) #display student record
infile.close() #close the file

write_record()
read_record()

Output

P19.# Write a menu driven program in Python that asks the user to add, display, and
search records of employee stored in a binary file. The employee record contains
employee code, name and salary. It should be stored in a list object. Your program
should pickle the object and save it to a binary file.

import pickle

def set_data():
empcode = int(input('Enter Employee code: '))
name = input('Enter Employee name: ')
salary = int(input('Enter salary: '))
print()
employee = [empcode, name, salary] #create a list
return employee

def display_data(employee):
print('Employee code:', employee[0])
print('Employee name:', employee[1])
print('Salary:', employee[2])
print()

def write_record():
outfile = open('emp.dat', 'ab') #open file in binary mode for writing.
pickle.dump(set_data(), outfile) #serialize the object and writing to file
outfile.close() #close the file

def read_records():
infile = open('emp.dat', 'rb') #open file in binary mode for reading
while True: #read to the end of file.
try:
employee = pickle.load(infile) #reading the oject from file
 display_data(employee) #display the object
except EOFError:
break
infile.close() #close the file

def search_record():
infile = open('emp.dat', 'rb')
empcode = int(input('Enter employee code to search: '))
flag = False

while True: #read to the end of file.

try:
employee = pickle.load(infile) #reading the oject from file
if employee[0] == empcode: #display record if found and set flag
display_data(employee)
flag = True
break

except EOFError:
break

if flag == False:
print('Record not Found')
print()

infile.close() #close the file

def show_choices():
print('Menu')
print('1. Add Record')
print('2. Display Records')
print('3. Search a Record')
print('4. Exit')

def main():
while(True):
show_choices()
choice = input('Enter choice(1-4): ')
print()

if choice == '1':
write_record()

elif choice == '2':

read_records()

elif choice == '3':

search_record()

elif choice == '4':

break

else:
print('Invalid input')
#call the main function.
main()

Output

P20. # Write a menu driven program in Python that asks the user to add, display, and
search records of students stored in a binary file. The student record contains roll no,
name and test score. It should be stored in a dictionary object. Your program should
pickle the object and save it to a binary file.

import pickle

def set_data():
rollno = int(input('Enter roll number: '))
name = input('Enter name: ')
test_score = int(input('Enter test score: '))
print()

#create a dictionary
student = {}
student['rollno'] = rollno
student['name'] = name
student['test_score'] = test_score
return student

def display_data(student):
print('Roll number:', student['rollno'])
print('Name:', student['name'])
print('Test Score:', student['test_score'])
print()

def write_record():
outfile = open('student.dat', 'ab') #open file in binary mode for writing.
pickle.dump(set_data(), outfile) #serialize the object and writing to file
outfile.close() #close the file
def read_records():
infile = open('student.dat', 'rb') #open file in binary mode for reading
while True: #read to the end of file.
try:
student = pickle.load(infile) #reading the oject from file
display_data(student) #display the object
except EOFError:
break

infile.close() #close the file

def search_record():
infile = open('student.dat', 'rb')
rollno = int(input('Enter rollno to search: '))
flag = False

while True: #read to the end of file.

try:
student = pickle.load(infile) #reading the oject from file
if student['rollno'] == rollno: #display record if found and set flag
display_data(student)
flag = True
break

except EOFError:
break
if flag == False:
print('Record not Found')
print()

infile.close() #close the file

def show_choices():
print('Menu')
print('1. Add Record')
print('2. Display Records')
print('3. Search a Record')
print('4. Exit')

def main():
while(True):
show_choices()
choice = input('Enter choice(1-4): ')
print()

if choice == '1':
write_record()

elif choice == '2':

read_records()

elif choice == '3':

search_record()
 elif choice == '4':
break

else:
print('Invalid input')

#call the main function.

main()

Output

P21.# Reading data from a CSV file into a list.

Country,Played,Won,Lost,Tied,No Result
England,746,375,334,9,28
Australia,949,575,331,9,34
South Africa,625,385,216,6,18
West Indies,822,401,381,10,30
New Zealand,772,351,374,7,40
India,987,513,424,9,41

import csv

file = open("odi.csv")
table = csv.reader(file)
for row in table:
print(row)
file.close()

Output
['Country', 'Played', 'Won', 'Lost', 'Tied', 'No Result']
['England', '746', '375', '334', '9', '28']
['Australia', '949', '575', '331', '9', '34']
['South Africa', '625', '385', '216', '6', '18']
['West Indies', '822', '401', '381', '10', '30']
['New Zealand', '772', '351', '374', '7', '40']
['India', '987', '513', '424', '9', '41']

P22. #Write a menu based program to demonstrate operation on a stack.

def isEmpty(stk):
if len(stk)==0:
return True
else:
return False
#----------------------------------------------------------------------

def push(stk,n):
stk.append(n)
#----------------------------------------------------------------------

def pop(stk):
if isEmpty(stk):
print("UNDERFLOW CONDITION")
else:
print("Deleted element:",stk.pop())
#----------------------------------------------------------------------
def peek(stk):
return stk[-1]
#----------------------------------------------------------------------

def display(stk):
if isEmpty(stk):
print("No Element Present")
else:
for i in range(-1,-len(stk)-1,-1):
if i==-1:
print("TOP",stk[i])
else:
print(" ",stk[i])
#----------------------------------------------------------------------
#main
stk=[]
while True:
print("Stack operations")
print("1.PUSH")
print("2.POP")
print("3.PEEK")
print("4.DISPLAY STACK")
print("5.EXIT")
ch=int(input("Enter the choice"))

if ch==1:
n=input("Enter the element to PUSH")
push(stk,n)
print("Element pushed")
elif ch==2:
pop(stk)
elif ch==3:
 if isEmpty(stk):
print("UNDERFLOW CONDITION")
else:
print(peek(stk))
elif ch==4:
display(stk)
elif ch==5:
break
else:
print("INVALID CHOICE ENTERED")
print("THANKS FOR USING MY SERVICES")

P23. #Python –MYSQL Connectivity

import mysql.connector
#----------------------------------------------------------------------
def insert():
cur.execute("desc {}".format(table_name))
data=cur.fetchall()
full_input=" "
for i in data:
print("NOTE: Please enter string/varchar/date values (if any) in quotes")
print("Enter the",i[0],end=" ")
single_value=input()
full_input=full_input+single_value+","
full_input=full_input.rstrip(",")
cur.execute("Insert into {} values({})".format(table_name,full_input))
mycon.commit()
print("Record successfully inserted")
#----------------------------------------------------------------------

def display():
n=int(input("Enter the number of records to display "))
cur.execute("Select * from {}".format(table_name))
 for i in cur.fetchmany(n):
print(i)

#----------------------------------------------------------------------
def search():
find=input("Enter the column name using which you want to find the record ")
print("Enter the",find,"of that record",end=" ")
find_value=input()
cur.execute("select * from {} where {}='{}'".format(table_name,find,find_value))
print(cur.fetchall())

#----------------------------------------------------------------------
def modify():
mod=input("Enter the field name to modify ")
find=input("Enter the column name using which you want to find the record ")
print("Enter the",find,"of that record",end=" ")
find_value=input()
print("Enter the new",mod,end=" ")
mod_value=input()
cur.execute("update {} set {}='{}' where {}='{}'".format(table_name, mod, mod_value, find,
find_value))
mycon.commit()
print("Record sucessfully modified")
#----------------------------------------------------------------------

def delete():
find=input("Enter the column name using which you want to find the record\nNOTE: NO TWO
RECORDS SHOULD HAVE SAME VALUE FOR THIS COLUMN: ")

print("Enter the",find,"of that record",end=" ")

find_value=input()
cur.execute("delete from {} where {}='{}'".format(table_name,find,find_value))
mycon.commit()
print("Record successfully deleted")

#----------------------------------------------------------------------
#__main__
database_name=input("Enter the database ")
my_sql_password=input("Enter the password for MySQL ")
table_name=input("Enter the table name ")
mycon=mysql.connector.connect(host="localhost",user="root",database=database_name,p
asswd=my_sql_password)
cur=mycon.cursor()
if mycon.is_connected():
print("Successfully Connected to Database")
else:
print("Connection Faliled")
while True:
print("\t\t1. Insert Record")
print("\t\t2. Display Record")
print("\t\t3. Search Record")
print("\t\t4. Modify Record")
print("\t\t5. Delete Recod")
print("\t\t6. Exit\n")
 ch=int(input("Enter the choice "))
if ch==1:
insert()
elif ch==2:
display()
elif ch==3:
search()
elif ch==4:
modify()
elif ch==5:
delete()
elif ch==6:
mycon.close()
break
else:
print("Invalid choice entered")

Enter the database School

Enter the password for MySQL CPI@123
Enter the table name EMP

Successfully Connected to Database

1. Insert Record
2. Display Record
3. Search Record
4. Modify Record
5. Delete Recod
6. Exit
Enter the choice 1

NOTE: Please enter string/varchar/date values (if any) in quotes Enter the EMPNO 120
NOTE: Please enter string/varchar/date values (if any) in quotes Enter the EMPNAME "C K Katiyar"
NOTE: Please enter string/varchar/date values (if any) in quotes Enter the DEPT "Computer"
NOTE: Please enter string/varchar/date values (if any) in quotes Enter the DESIGN "Coder"
NOTE: Please enter string/varchar/date values (if any) in quotes Enter the basic 100000
NOTE: Please enter string/varchar/date values (if any) in quotes Enter the CITY "Kannauj"
Record successfully inserted
1. Insert Record
2. Display Record
3. Search Record
4. Modify Record
5. Delete Recod
6. Exit
Enter the choice 2
Enter the number of records to display 10
(111, 'Akash Narang', 'Account', 'Manager', 50000, 'Dehradun')
(112, 'Vijay Duneja', 'Sales', 'Clerk', 21000, 'lucknow')
(113, 'Kunal Bose', 'Computer', 'Programmer', 45000, 'Delhi')
(114, 'Ajay Rathor', 'Account', 'Clerk', 26000, 'Noida')
(115, 'Kiran Kukreja', 'Computer', 'Operator', 30000, 'Dehradun')
(116, 'Piyush Sony', 'Sales', 'Manager', 55000, 'Noida')
(117, 'Makrand Gupta', 'Account', 'Clerk', 16000, 'Delhi')
(118, 'Harish Makhija', 'Computer', 'Programmer', 34000, 'Noida')
(120, ' CKKatiyar', 'Computer', 'Coder', 100000, 'Kannauj')
 1. Insert Record
2. Display Record
3. Search Record
4. Modify Record
5. Delete Recod
6. Exit
Enter the choice 3
Enter the column name using which you want to find the record EMPNO Enter the
EMPNO of that record 120
[(120, 'CKKatiyar', 'Computer', 'Coder', 100000, 'Kannauj')]
1. Insert Record
2. Display Record
3. Search Record
4. Modify Record
5. Delete Recod
6. Exit
Enter the choice 4

Enter the field name to modify basic

Enter the column name using which you want to find the record EMPNAME Enter the
EMPNAME of that CKKatiyar
Enter the new basic 200000
Record sucessfully modified
1. Insert Record
2. Display Record
3. Search Record
4. Modify Record
5. Delete Recod
6. Exit
Enter the choice 5
Enter the column name using which you want to find the record
NOTE: NO TWO RECORDS SHOULD HAVE SAME VALUE FOR THIS COLUMN: EMPNO
Enter the EMPNO of that record 120
Record successfully deleted

24#: Integrate SQL with Python by importing the MySQL module.

import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="tiger",database="School")
if mycon.is_connected()==False:
print("error connecting to database")
cursor=mycon.cursor()
cursor.execute("select *from student10")
data=cursor.fetchall()
for i in data:
print(i)
mycon.close()

35#: Integrate SQL with Python by importing the pymysql module.

import pymysql as pym

mycon=sqltor.connect(host="localhost",user="root",passwd="tiger",database="School")
cursor=mycon.cursor()
cursor.execute("select *from student10")
data=cursor.fetchall()
for i in data:
print(i)
mycon.close()
 MYSQL QUERIES:

25# Create a student table and insert data. Implement the following SQL commands
on the student table:
 ALTER table to add new attributes / modify data type / drop attribute
 UPDATE table to modify data
 ORDER By to display data in ascending / descending order
 DELETE to remove tuple(s)
 GROUP BY and find the min, max, sum, count and average

#Switched to a database
mysql> USE School;
Database changed
#Creating table student
mysql> create table student
-> (ROLLNO INT NOT NULL PRIMARY KEY,
-> NAME CHAR(10),
-> TELUGU CHAR(10),
-> HINDI CHAR(10),
-> MATHS CHAR(10));
Query OK, 0 rows affected (1.38 sec)
#Inserting values into table
mysql> insert into student
-> values(101,"student1",50,51,52),
-> (102,"student2",60,61,62),
-> (103,"student3",70,71,72),
-> (104,"student4",80,81,82),
-> (105,"student5",90,91,92),
-> (106,"student6",40,41,42),
-> (107,"student7",63,64,65);
Query OK, 7 rows affected (0.24 sec)
Records: 7 Duplicates: 0 Warnings: 0
#Adding new attribute computers
mysql> alter table student
-> add (computers char(10));
Query OK, 0 rows affected (1.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

#Describing table

mysql> desc student;

+------------+----------+-------+------+---------+-------+
| Field | Type | Null | Key| Default | Extra |
+------------+----------+-------+-----+-----------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
| computers| char(10) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
6 rows in set (0.21 sec)
#Modifying the datatype

mysql> alter table student

-> modify column computers varchar(10);
Query OK, 7 rows affected (2.38 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
| computers | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.11 sec)

#Droping a attribute
mysql> alter table student
-> drop column computers;
Query OK, 0 rows affected (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 0

#Describing table
mysql> desc student;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.14 sec)

#UPDATE DATA TO MODIFY DATA

#ACTUAL DATA
mysql> select *from student;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 101 | student1 | 50 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)
#UPDATE THE MARKS FOR ATTRIBUTE TELUGU FOR THE STUDENT101

mysql> UPDATE STUDENT

-> SET TELUGU=99
-> WHERE ROLLNO=101;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#DATA IN THE TABLE AFTER UPDATING

mysql> SELECT *FROM STUDENT;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 101 | student1 | 99 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)
#ORDER BY DESCENDING ORDER
mysql> SELECT *FROM STUDENT
-> ORDER BY HINDI DESC;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 105 | student5 | 90 | 91 | 92 |
| 104 | student4 | 80 | 81 | 82 |
| 103 | student3 | 70 | 71 | 72 |
| 107 | student7 | 63 | 64 | 65 |
| 102 | student2 | 60 | 61 | 62 |
| 101 | student1 | 99 | 51 | 52 |
| 106 | student6 | 40 | 41 | 42 |
+--------+----------+--------+-------+-------+
7 rows in set (0.05 sec)

#ORDER BY ASCENDING ORDER

mysql> SELECT *FROM STUDENT
-> ORDER BY HINDI ASC;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 106 | student6 | 40 | 41 | 42 |
| 101 | student1 | 99 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 107 | student7 | 63 | 64 | 65 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)
#DELETING A TUPLE FROM THE TABLE
mysql> DELETE FROM STUDENT
-> WHERE ROLLNO=101;
Query OK, 1 row affected (0.14 sec)
mysql> SELECT *FROM STUDENT;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
6 rows in set (0.06 sec)

#ORDER BY BRANCH
#ACTUAL DATA
mysql> SELECT *FROM STUDENT;
+--------+--------+----------+--------+-------+-------+
| ROLL |BRANC |NAME | TELUGU| HINDI|MATHS|
+--------+--------+----------+--------+-------+-------+
| 102 | MPC | student2 | 60 | 61 | 62 |
| 103 | BIPC | student3 | 70 | 71 | 72 |
| 104 | BIPC | student4 | 80 | 81 | 82 |
| 105 | BIPC | student5 | 90 | 91 | 92 |
| 106 | BIPC | student6 | 40 | 41 | 42 |
| 107 | MPC | student7 | 63 | 64 | 65 |
+--------+--------+----------+--------+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT BRANCH,COUNT(*)

-> FROM STUDENT
-> GROUP BY BRANCH;
+--------+----------+
| BRANCH | COUNT(*) |
+--------+----------+
| MPC | 2|
| BIPC | 4|
+--------+----------+
2 rows in set (0.01 sec)

#min, max, sum, count and average

mysql> SELECT MIN(TELUGU) "TELUGU MIN MARKS"
-> FROM STUDENT;
+------------------+
| TELUGU MIN MARKS |
+------------------+
| 40 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(TELUGU) "TELUGU MAX MARKS"
-> FROM STUDENT;
+------------------+
| TELUGU MAX MARKS |
+------------------+
| 90 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT SUM(TELUGU) "TELUGU TOTAL MARKS"
-> FROM STUDENT;
+--------------------+
| TELUGU TOTAL MARKS |
+--------------------+
| 403 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(ROLLNO)
-> FROM STUDENT;
+---------------+
| COUNT(ROLLNO) |
+---------------+
| 6|
+---------------+
1 row in set (0.01 sec)
mysql> SELECT AVG(TELUGU) "TELUGU AVG MARKS"
-> FROM STUDENT;
+-------------------+
| TELUGU AVG MARKS |
+-------------------+
| 67.16666666666667 |
+-------------------+