PRACTICAL – 1

Create a panda’s series from a dictionary of values and a ndarray.

import pandas as p
import numpy as np
arr=[Link]([5,10,15,20])
s1=[Link](arr,index=['a','b','c','d'])
print(s1)
s2=[Link]({'Jan':31,'Feb':28,'Mar':31})
print(s2)
[Link]='Days'
[Link]='Months'
print(s2)

Output:
a 5
b 10
c 15
d 20
dtype: int32
Jan 31
Feb 28
Mar 31
dtype: int64
Months
Jan 31
Feb 28
Mar 31
Name: Days, dtype: int64

1
 PRACTICAL – 2
Given a Series, print all the elements that are above the 75th
percentile.

import pandas as p
s=[Link]([75,65,95,89,96,76])
[Link]=['a','b','c','d','e','f']
print(s)
print("\nPercentile above 75\n")
print(s[s>75])

Output:

a 75
b 65
c 95
d 89
e 96
f 76
dtype: int64

Percentile above 75

c 95
d 89
e 96
f 76
dtype: int64

2
 PRACTICAL – 3
Create a Data Frame quarterly sales where each row contains the
item category, item name, and expenditure. Print total expenditure.

import pandas as p
a=[['Stationary','Notebook',600],
['Bakery','Bread',120],
['Stationary','Compass Box',140],
['Bakery','Cupcake',200],
['Dairy','Cheese',450]]
d1=[Link](a,columns=['Item Category','Item name','Expenditure'])
print(d1)
print("Total Expenditure=",d1['Expenditure'].sum())

Output:
Item Category Item name Expenditure
0 Stationary Notebook 600
1 Bakery Bread 120
2 Stationary Compass Box 140
3 Bakery Cupcake 200
4 Dairy Cheese 450

Total Expenditure= 1510

3
 PRACTICAL – 4
Create a data frame for examination result and display row labels,
column labels, data types of each column and the dimensions.

import pandas as p
a=[['Manan',85,67,92,89],
['Harshil',95,77,60,56],
['Gautam',96,94,96,99],
['Leena',93,92,96,99],
['Akruti',80,81,90,85]]
d1=[Link](a,columns=['Name','BS','ECO','ACC','IP'])
d1['Total']=d1['BS']+d1['ECO']+d1['ACC']+d1['IP']
print(d1)
print([Link])
print([Link])
print([Link])
print([Link])

Output:

Name BS ECO ACC IP Total

0 Manan 85 67 92 89 333
1 Harshil 95 77 60 56 288
2 Gautam 96 94 96 99 385
3 Leena 93 92 96 99 380
4 Akruti 80 81 90 85 336
RangeIndex(start=0, stop=5, step=1)
Index(['Name', 'BS', 'ECO', 'ACC', 'IP', 'Total'], dtype='object')
Name object
BS int64
ECO int64
ACC int64
IP int64
Total int64
dtype: object
(5, 6)

4
 PRACTICAL – 5
Create a data frame for examination result and display maximum
marks from each subject and maximum total marks.

import pandas as p
a=[['Manan',85,67,92,89],
['Harshil',95,77,60,56],
['Gautam',96,94,96,99],
['Leena',93,92,96,99],
['Akruti',80,81,90,85]]
d1=[Link](a,columns=['Name','BS','ECO','ACC','IP'])
d1['Total']=d1['BS']+d1['ECO']+d1['ACC']+d1['IP']
print(d1)
print("Maximum Marks in BS:",d1['BS'].max())
print("Maximum Marks in ECO:",d1['ECO'].max())
print("Maximum Marks in ACC:",d1['ACC'].max())
print("Maximum Marks in IP:",d1['IP'].max())
print("Maximum Total:",d1['Total'].max())

Output:

Name BS ECO ACC IP Total

0 Manan 85 67 92 89 333
1 Harshil 95 77 60 56 288
2 Gautam 96 94 96 99 385
3 Leena 93 92 96 99 380
4 Akruti 80 81 90 85 336

Maximum Marks in BS: 96

Maximum Marks in ECO: 94
Maximum Marks in ACC: 96
Maximum Marks in IP: 99
Maximum Total: 385

5
 PRACTICAL – 6
Create a series by using list and display index labels, index data
type, series values and series dimension.

import pandas as p
num=[5,10,15,20,25,30]
s=[Link](index=['a','b','c','d','e','f'],data=num)
print(s)
print([Link])
print([Link])
print([Link])
print([Link])

Output:

a 5
b 10
c 15
d 20
e 25
f 30
dtype: int64
Index(['a', 'b', 'c', 'd', 'e', 'f'], dtype='object')
object
[ 5 10 15 20 25 30]
(6,)

6
 PRACTICAL – 7
Create two series by using list and display first 3 values from series –
1 and last two values from series – 2.
import pandas as p
s=[Link]([10,20,30,40],index=[1,2,3,4])
print(s)
s1=[Link]([300,400,500,600],index=[3,4,5,6])
print(s1)
print([Link](3))
print([Link](2))
Output:
1 10
2 20
3 30
4 40
dtype: int64
3 300
4 400
5 500
6 600
dtype: int64
1 10
2 20
3 30
dtype: int64
5 500
6 600
dtype: int64

7
 PRACTICAL – 8
Create two series by using list and perform mathematical and vector
operations on series.

import pandas as p
s=[Link]([10,20,30,40],index=[1,2,3,4])
print(s)
s1=[Link]([300,400,500,600],index=[3,4,5,6])
print(s1)
#Mathematical operations
print(s+s1)
print(s1-s)
print(s*s1)
print(s1/s)
#Vector operations
print(s+20)
print(s1*3)
print(s1/2)
print(s>20)
print(s<=30)
print(s1[s1<500])

Output:

1 10
2 20
3 30
4 40
dtype: int64
3 300
4 400
5 500
6 600
dtype: int64

8
1 NaN
2 NaN
3 330.0
4 440.0
5 NaN
6 NaN
dtype: float64
1 NaN
2 NaN
3 270.0
4 360.0
5 NaN
6 NaN
dtype: float64
1 NaN
2 NaN
3 9000.0
4 16000.0
5 NaN
6 NaN
dtype: float64
1 NaN
2 NaN
3 10.0
4 10.0
5 NaN
6 NaN
dtype: float64
1 30
2 40
3 50
4 60
dtype: int64
3 900
4 1200

9
5 1500
6 1800
dtype: int64
3 150.0
4 200.0
5 250.0
6 300.0
dtype: float64
1 False
2 False
3 True
4 True
dtype: bool
1 True
2 True
3 True
4 False
dtype: bool
3 300
4 400
dtype: int64

10
 PRACTICAL – 9
Create two Dataframes and perform binary operations.

import pandas as p
d=[Link]({'Column-1':[5,10,15,20],'Column-2':[10,20,30,40]})
d1=[Link]({'Column-1':[50,100,150,200],'Column-
2':[8,10,12,16]})
print(d)
print(d1)
#Binary operations
print([Link](d1))
print([Link](d1))
print([Link](d1))
print([Link](d1))
print([Link](d1))
print([Link](d1))
print([Link](d1))
print([Link](d1))
print(d['Column-1'].add(d1['Column-1']))
print(d['Column-1'].rsub(d1['Column-2']))

Output:

Column-1 Column-2
0 5 10
1 10 20
2 15 30
3 20 40

11
 Column-1 Column-2
0 50 8
1 100 10
2 150 12
3 200 16
Column-1 Column-2
0 55 18
1 110 30
2 165 42
3 220 56
Column-1 Column-2
0 55 18
1 110 30
2 165 42
3 220 56
Column-1 Column-2
0 -45 2
1 -90 10
2 -135 18
3 -180 24
Column-1 Column-2
0 45 -2
1 90 -10
2 135 -18
3 180 -24
Column-1 Column-2
0 250 80
1 1000 200
2 2250 360
3 4000 640
Column-1 Column-2
0 250 80
1 1000 200
2 2250 360
3 4000 640

12
 Column-1 Column-2
0 0.1 1.25
1 0.1 2.00
2 0.1 2.50
3 0.1 2.50
Column-1 Column-2
0 10.0 0.8
1 10.0 0.5
2 10.0 0.4
3 10.0 0.4
0 55
1 110
2 165
3 220
Name: Column-1, dtype: int64
0 3
1 0
2 -3
3 -4
dtype: int64

13
 PRACTICAL – 10
Create a dataframe and perform iterations on rows and columns.

import pandas as p,numpy as np

array=[Link]([['A',72],['B',92],['C',86],['D',76],['E',99],['F',97]])
d1=[Link](array,columns=['Name','IP'])
print(d1)
for row,rowseries in [Link]():
print("RowIndex:",row)
print(rowseries)
print(d1)
for col,colseries in [Link]():
print("ColIndex:",col)
print(colseries)

Output:

Name IP
0 A 72
1 B 92
2 C 86
3 D 76
4 E 99
5 F 97

RowIndex: 0
Name A
IP 72
Name: 0, dtype: object

14
RowIndex: 1
Name B
IP 92
Name: 1, dtype: object

RowIndex: 2
Name C
IP 86
Name: 2, dtype: object

RowIndex: 3
Name D
IP 76
Name: 3, dtype: object

RowIndex: 4
Name E
IP 99
Name: 4, dtype: object

RowIndex: 5
Name F
IP 97
Name: 5, dtype: object

Name IP
0 A 72
1 B 92
2 C 86
3 D 76
4 E 99
5 F 97

15
ColIndex: Name
0 A
1 B
2 C
3 D
4 E
5 F
Name: Name, dtype: object

ColIndex: IP
0 72
1 92
2 86
3 76
4 99
5 97
Name: IP, dtype: object

16
 PRACTICAL – 11
Create a dataframe using list of dictionaries and perform selection
and slices operations on rows and columns.

import pandas as p
L= [{'Name':'Oreo','Qty':30,'Price':85},
{'Name':'Hazelnut','Qty':20,'Price':90},
{'Name':'Bournville','Qty':10,'Price':80}]
d=[Link](L)
d['Total']=d['Qty']*d['Price']
print(d)
print(d['Total'])
print([Link][2])
print([Link][:,[0,3]])
print([Link][:,0:3])
print([Link][:,3:4])
print([Link][0:2,0:3])

Output:

Name Qty Price Total

0 Oreo 30 85 2550
1 Hazelnut 20 90 1800
2 Bournville 10 80 800
0 2550
1 1800
2 800
Name: Total, dtype: int64
Name Bournville
Qty 10
Price 80
Total 800
Name: 2, dtype: object

17
 Name Total
0 Oreo 2550
1 Hazelnut 1800
2 Bournville 800
Name Qty Price
0 Oreo 30 85
1 Hazelnut 20 90
2 Bournville 10 80
Total
0 2550
1 1800
2 800
Name Qty Price
0 Oreo 30 85
1 Hazelnut 20 90

18
 PRACTICAL – 12
Create a dataframe and display first two rows and last three rows.

import pandas as p,numpy as np

array=[Link]([['A',72],
['B',92],
['C',86],
['D',76],
['E',99],
['F',97]])
d1=[Link](array,columns=['Name','IP'])
print(d1)
print([Link]()) #Bydefault first 5 rows
print([Link]()) #Bydefault last 5 rows
print([Link](2))
print([Link](3))

Output:

Name IP
0 A 72
1 B 92
2 C 86
3 D 76
4 E 99
5 F 97
Name IP
0 A 72
1 B 92
2 C 86
3 D 76
4 E 99
Name IP
1 B 92
2 C 86
19
3 D 76
4 E 99
5 F 97
Name IP
0 A 72
1 B 92
Name IP
3 D 76
4 E 99
5 F 97

20
 PRACTICAL – 13
Create a Python program to insert students’ result records in csv file
‘STUD_RES.csv’ and ‘STUD_TOTAL.csv’.
import pandas as p
i=0
d1=[Link]({'GRNO':[],'NAME':[],'BS':[],'ECO':[],'ACC':[],'IP':[]}
)
print(d1)
while True:
grno=int(input("Enter grno:"))
name=input("Enter name:")
bs=int(input("Enter marks of Business Studies:"))
eco=int(input("Enter marks of Economics:"))
acc=int(input("Enter marks of Accountancy:"))
ip=int(input("Enter marks of Informatics Practices:"))
[Link][i]=[grno,name,bs,eco,acc,ip]
c=input("Do you want to add more rows?")
if c!='y' and c!='Y':
break
i=i+1
d1['TOTAL']=d1['BS']+d1['ECO']+d1['ACC']+d1['IP']
print(d1)
d1.to_csv("D:\\STUD_RES.csv")
d1.to_csv("D:\\STUD_TOTAL.csv",columns=['GRNO','NAME','TOTA
L'])

Output:

Empty DataFrame
Columns: [GRNO, NAME, BS, ECO, ACC, IP]
Index: []

21
Enter grno:1
Enter name:Shyam
Enter marks of Business Studies:79
Enter marks of Economics:89
Enter marks of Accountancy:65
Enter marks of Informatics Practices:45
Do you want to add more rows?y

Enter grno:2
Enter name:Tesla
Enter marks of Business Studies:95
Enter marks of Economics:98
Enter marks of Accountancy:100
Enter marks of Informatics Practices:100
Do you want to add more rows?y

Enter grno:3
Enter name:Billy
Enter marks of Business Studies:99
Enter marks of Economics:92
Enter marks of Accountancy:93
Enter marks of Informatics Practices:99
Do you want to add more rows?n

GRNO NAME BS ECO ACC IP TOTAL

0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

22
 PRACTICAL – 14
Create a Python program to display students’ result records from
csv file ‘STUD_RES.csv’.

import pandas as p
d1=p.read_csv("D:\\STUD_RES.csv")
print(d1)
#Specific columns
d2=p.read_csv("D:\\STUD_RES.csv",usecols=['GRNO','NAME','IP'])
print(d2)
#Specific rows
d3=p.read_csv("D:\\STUD_RES.csv",nrows=2)
print(d3)
#Specific rows and columns
d4=p.read_csv("D:\\STUD_RES.csv",nrows=2,usecols=['GRNO','NAM
E','IP'])
print(d4)
#without header
d5=p.read_csv("D:\\STUD_RES.csv",header=None,skiprows=1)
print(d5)
#Without Index
d6=p.read_csv("D:\\STUD_RES.csv",index_col=0)
print(d6)
#New Column names
d7=p.read_csv("D:\\STUD_RES.csv",skiprows=1,names=['gr','sname','b
s','eco','acc','ip','total'])
print(d7)

Output:

Unnamed: 0 GRNO NAME BS ECO ACC IP TOTAL

0 0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0
2 2 3.0 Billy 99.0 92.0 93.0 99.0 383.0
23
 GRNO NAME IP
0 1.0 Shyam 45.0
1 2.0 Tesla 100.0
2 3.0 Billy 99.0

Unnamed: 0 GRNO NAME BS ECO ACC IP TOTAL

0 0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0

GRNO NAME IP
0 1.0 Shyam 45.0
1 2.0 Tesla 100.0

0 1 2 3 4 5 6 7
0 0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0
2 2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

GRNO NAME BS ECO ACC IP TOTAL

0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

gr sname bs eco acc ip total

0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

24
 PRACTICAL – 15
Create a Python program to modify students’ result records in csv
file ‘STUD_RES.csv’.

import pandas as p
import os
d1=p.read_csv("D:\\STUD_RES.csv",index_col=0)
print(d1)
flag=False
gr=int(input("Enter GRNO to modify:"))
for i,j in [Link]():
if j['GRNO']==gr:
name=input("Enter name:")
bs=int(input("Enter marks of Business Studies:"))
eco=int(input("Enter marks of Economics:"))
acc=int(input("Enter marks of Accountancy:"))
ip=int(input("Enter marks of Informatics Practices:"))
total=bs+eco+acc+ip
[Link][i]=[gr,name,bs,eco,acc,ip,total]
flag=True
if flag==False:
print("GRNO not found for modifiction")
else:
[Link]("D:\\STUD_RES.csv")
d1.to_csv("D:\\STUD_RES.csv")
print(d1)

Output:

GRNO NAME BS ECO ACC IP TOTAL

0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 2.0 Tesla 95.0 98.0 100.0 100.0 393.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

25
Enter GRNO to modify:2

Enter name:Elon

Enter marks of Business Studies:96

Enter marks of Economics:93

Enter marks of Accountancy:99

Enter marks of Informatics Practices:98

GRNO NAME BS ECO ACC IP TOTAL
0 1.0 Shyam 79.0 89.0 65.0 45.0 278.0
1 2.0 Elon 96.0 93.0 99.0 98.0 386.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

26
 PRACTICAL – 16
Create a Python program to delete students’ result record from csv
file ‘STUD_RES.csv’.

import pandas as p
import os
d1=p.read_csv("D:\\STUD_RES.csv",index_col=0)
print(d1)
flag=False
gr=int(input("Enter GRNO to delete:"))
for i,j in [Link]():
if j['GRNO']==gr:
d1=[Link](i)
flag=True
if flag==False:
print("GRNO not found for Deletion")
else:
[Link]("D:\\STUD_RES.csv")
d1.to_csv("D:\\STUD_RES.csv")
print(d1)

Output:

GRNO NAME BS ECO ACC IP TOTAL

0 1.0 Mukesh 80.0 85.0 90.0 95.0 350.0
1 2.0 Elon 96.0 93.0 99.0 98.0 386.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

Enter GRNO to delete:10

GRNO not found for Deletion

27
 GRNO NAME BS ECO ACC IP TOTAL
0 1.0 Mukesh 80.0 85.0 90.0 95.0 350.0
1 2.0 Elon 96.0 93.0 99.0 98.0 386.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

Enter GRNO to delete:1

GRNO NAME BS ECO ACC IP TOTAL
1 2.0 Elon 96.0 93.0 99.0 98.0 386.0
2 3.0 Billy 99.0 92.0 93.0 99.0 383.0

28
 PRACTICAL – 17
Python program to create line chart on comparison of scores of
batsmen of two matches.

#Data Visualisation
import [Link] as plt
x1=['Virat','Rohit','Rahul']
y1=[100,135,35]
[Link](x1,y1,'g--',label='First Match')
x2=['Virat','Rohit','Rahul']
y2=[140,60,75]
[Link](x2,y2,label='Second Match')
[Link]('Batsmen')
[Link]('Runs')
[Link]('Score Comparison')
[Link]()
[Link](True)
[Link]("D:\\[Link]")
[Link]()

29
30
 PRACTICAL – 18
Python program to create line chart (four sub plots) on given integer
values.

import [Link] as plt,numpy as np

t=[Link](5)#[0 1 2 3 4]
s=[5,10,15,25,20]
s1=[2,4,6,10,8]

[Link](2,2,2) #Rows,Columns,Graph No.

[Link](t,s,'m:')
[Link]('Chart - 1')
[Link]('First chart')
[Link]('Items-1')

[Link](2,2,1)
[Link](t,[2,4,6,8,10],'r-.')
[Link]('Chart - 2')
[Link]('Items-2')
[Link]('Second chart')
[Link](True) #To show gridlines
plt.subplots_adjust(hspace=0.75,wspace=0.75)
#hspace(heightspace)=space between two rows
#wspace(widthspace)=space between two columns
[Link](2,2,3)
[Link](t,s1,'y--')
[Link]('Items-3')
[Link]('Chart-3')
[Link]('Third chart')

[Link](2,2,4)
[Link](t,[22,38,12,6,7],'r-')
[Link]('Items-4')
[Link]('Chart- 4')
[Link]('Fourth chart')
[Link](True)

[Link]("D:\\[Link]")
[Link]()

31
32
 PRACTICAL – 19
Python program to create Bar chart (Vertical) on comparison of
Temperature and Humidity of different cities of India.

import [Link] as plt

City=["Delhi","Mumbai","Kolkata","Srinagar"]
Temp=[22.5,35.5,39.5,7.9]
[Link](City,Temp,color='y',label='Temperature',width=0.25)
#Vertical bar chart
City1=["Agra","Pune","Chennai","Jammu"]
Temp1=[27.5,32.5,29.5,9.2]
[Link](City1,Temp1,color='m',label='Humidity',width=0.75)
[Link]('City')
[Link]('Temperature / Humidity')
[Link]('Citywise temperature')
[Link]()
[Link]("D:\\[Link]")
[Link]()

33
34
 PRACTICAL – 20
Python program to create Bar chart (Horizontal) on comparison of
usage of programming language.

import [Link] as plt

Language=['C','C++','Java','Python']
x_pos=[2,3,0,1]
usage=[7,13,30,20]
[Link](x_pos,usage,color='g',
label='Programming language usage',height=0.5)
[Link](x_pos,Language)
[Link]('Usage')
[Link]('Programming Language usage')
[Link]()
[Link]("d:\\[Link]")
[Link]()

35
36
 PRACTICAL – 21
Python program to create histogram on students’ marks frequency.

import [Link] as plt

stud_data=[5,25,15,45,55,35,14,39,59,60,2,10]
[Link](stud_data,bins=[0,15,20,30,40,50,60],
weights=[7,5,10,5,2,7,3,6,1,1,1,1],edgecolor="b",facecolor='c')
[Link]("Histogram for students' data")
[Link]("Mark range")
[Link]("Frequency")
[Link]("Marks Distribution")
[Link]("D:\\[Link]")
[Link]()

37
38
 PRACTICAL – 22
Python program to create Line chart, Bar chart (Horizontal and
vertical) and Histogram on person’s height and weight frequency
data stored in DataFrame.

import [Link] as plt,pandas as p

dict1={'Name':['Paul','Jason','Joe','James','Kane'],
'Height':[152,202,160,167,180],
'Weight':[30,121,59,91,200]}
df1=[Link](dict1)
print(df1)
df1.set_index('Name',inplace=True)
print(df1)
[Link](kind='line')
[Link]("d:\\25_Line.jpeg")
[Link]()
[Link](kind='bar')
[Link]("d:\\25_barv.jpeg")
[Link]()
[Link](kind='barh')
[Link]("d:\\25_barh.jpeg")
[Link]()
[Link](kind='hist',
bins=[0,30,60,90,120,150,180,210])
[Link]("d:\\25_hist.jpeg")
[Link]()

39
Output
Name Height Weight
0 Paul 152 30
1 Jason 202 121
2 Joe 160 59
3 James 167 91
4 Kane 180 200
Height Weight
Name
Paul 152 30
Jason 202 121
Joe 160 59
James 167 91
Kane 180 200

40
41
42
 PRACTICAL – 23
Create following table STORE in MYSQL and solve the queries
given below:

TABLE: STORE
PID PNAME DEPARTMENT QTY PRICE
1 PEN STATIONARY 1000 5
2 PENCIL STATIONARY 1000 4
3 WHEAT GROCERY 500 30
FLOUR
4 CORN GROCERY 500 45
FLOUR
5 UDAD DAL GROCERY 250 160
6 CHANA DAL GROCERY 250 180
7 DAIRY MILK BAKERY 1000 10
CHOCOLATE
8 PARLE BAKERY 1000 5
BISCUIT
9 ERASER STATIONARY 1000 3
10 WAFERS BAKERY 1000 10

Create table store (pid int primary key, pname varchar(20), department varchar(20),
qty int, price int);

Insert into store values (1,’PEN’,’STATIONARY’,1000,5);

1. To display products in ascending order of pname.

SELECT * FROM STORE ORDER BY PNAME;

2. To display products in descending order of department.

SELECT * FROM STORE ORDER BY DEPARTMENT DESC;

3. To display products of STATIONARY department.

SELECT * FROM STORE WHERE DEPARTMENT = ‘STATIONARY’;

4. To display products whose price is more than 50.

SELECT * FROM STORE WHERE PRICE>50;

5. To display PNAME and DEPARTMENT whose qty is greater than 500.

SELECT PNAME, DEPARTMENT FROM STORE WHERE QTY>500;

43
 PRACTICAL – 24
Consider the table STORE and solve the queries given below:

TABLE: STORE
PID PNAME DEPARTMENT DOP QTY PRICE
1 PEN STATIONARY 2022-12-31 1000 5
2 PENCIL STATIONARY 2020-01-20 1000 4
3 WHEAT GROCERY 2022-12-01 500 30
FLOUR
4 CORN GROCERY 2021-06-06 500 45
FLOUR
5 UDAD DAL GROCERY 2021-05-13 250 160
6 CHANA DAL GROCERY 2021-03-16 250 180
7 DAIRY MILK BAKERY 2021-11-06 1000 10
CHOCOLATE
8 PARLE BAKERY 2021-10-03 1000 5
BISCUIT
9 ERASER STATIONARY 2020-05-20 1000 3
10 WAFERS BAKERY 2020-05-21 1000 10

1. To update information of WAFERS by increasing qty 500.

UPDATE STORE SET QTY=QTY+500 WHERE PNAME=’WAFFER’;

2. To display products’ information whose PNAME starts with ‘P’.

SELECT * FROM STORE WHERE PNAME LIKE ‘P%’;

3. To count total number of products department wise.

SELECT DEPARTMENT, COUNT(DEPARTMENT) FROM STORE
GROUP BY DEPARTMENT;

4. To display information about maximum price department wise.

SELECT DEPARTMENT, MAX(PRICE) FROM STORE GROUP BY
DEPARTMENT;

5. To display PNAME and their total amount as qty*price.

SELECT PNAME, QTY*PRICE “TOTAL AMOUNT” FROM STORE;

6. To display first five characters of all products’ name.

SELECT LEFT(PNAME,5) FROM STORE;

7. To display product details purchased in 2021.

SELECT * FROM STORE WHERE YEAR(DOP)=2021;

44
 PRACTICAL – 25
Create following table STUDENT in MYSQL and solve the queries
given below:
TABLE: STUDENT
S_ID NAME MARKS
1 ABHAY 83
2 KUNAL 82
3 HARSHIL 95
4 SWAPNIL 77
5 AKSHAR 65

Create table student (S_ID int primary key, NAME varchar (20), MARKS int);
Insert into STUDENT values (1, ’ABHAY’, 83);

1. To display students’ data who got marks more than 80.

SELECT * FROM STUDENT WHERE MARKS>80;

2. To display maximum marks of student.

SELECT MAX (MARKS) FROM STUDENT;

3. To display minimum marks of student.

SELECT MIN (MARKS) FROM STUDENT;

4. To display sum of marks of entire class.

SELECT SUM (MARKS) FROM STUDENT;

5. To display average marks of entire class.

SELECT AVG (MARKS) FROM STUDENT;

6. To display students data in descending order of marks.

SELECT * FROM STUDENT ORDER BY MARKS DESC;

45
 PRACTICAL – 26

Create following table CUSTOMER in MYSQL and solve the

queries given below:
TABLE: CUSTOMER
C_ID NAME COUNTRY
1 ALISHA INDIA
2 PRIYA INDIA
3 GEETA USA
4 SEEMA INDIA
5 AXITA AUSTRALIA

Create table CUSTOMER (C_ID int primary key, NAME varchar (20),
COUNTRY varchar (20));
Insert into CUSTOMER values (1, ‘ALISHA’, ‘INDIA’);

1. To count total customers country wise.

SELECT COUNTRY, COUNT (COUNTRY) FROM CUSTOMER GROUP
BY COUNTRY;

2. To display customers’ data whose name starts with ‘A’.

SELECT * FROM CUSTOMER WHERE NAME LIKE ‘A%’;

3. To modify customers’ country name to NEW ZEALAND whose country is

AUSTRALIA.
UPDATE CUSTOMER SET COUNTRY=’NEW ZEALAND’ WHERE
COUNTRY=’AUSTRALIA’;

4. Insert a new column PHONE which can store phone number of every
customers.
ALTER TABLE CUSTOMER ADD PHONE BIGINT;

5. Display all customers in ascending order of name.

SELECT * FROM CUSTOMER ORDER BY NAME;

46
 PRACTICAL – 27
Create following tables PERSONAL and JOB in MYSQL and solve
the queries given below:
Table: Personal
Empno Name dobirth Native Hobby
123 Amit 1965-01-23 Delhi Music
127 Manoj 1976-12-12 Mumbai Writing
124 Abhai 1975-08-11 Allahabad Music
125 Vinod 1977-04-04 Delhi Sports
128 Abhay 1974-03-10 Mumbai Gardening
129 Ramesh 1981-10-28 Pune Sports

Create table Personal (Empno int primary key, Name varchar(20), dobirth date,
Native varchar(20), Hobby varchar(15));

insert into personal values(129,’Ramesh’,’1981-10-28’,’Pune’,’Sports’);

Table: Job
Sno Area App_date Salary Retd_date Dept
123 Agra 2006-01-25 5000 2026-01-25 Marketing
127 Mathura 2006-12-22 16000 2026-12-22 Finance
124 Agra 2007-08-19 10500 2027-08-19 Marketing
125 Delhi 2004-04-14 8500 2018-04-14 Sales
128 Pune 2008-03-13 7500 2028-03-13 Sales

Create table job ( Sno int references personal(empno), Area varchar(15), App_date
date, salary int, Retd_date date, Dept varchar(15));

insert into job values(123,’Agra’,’2006-01-25’,5000,

’2026-01-25’,’Marketing’);

1. Show empno, name and salary of those who have Sports as hobby.
Select [Link], [Link], [Link] from personal,
job where [Link]=job. sno and [Link]='Sports';

2. Show number of employees area wise.

select area, count(area) from job group by area;

3. Show youngest employee from each native place.

select native,max(dobirth) from personal group by native;

47
 4. Show sno, name, hobby and salary in descending order of salary.
Select [Link], [Link] ,[Link], [Link] from
personal, job where [Link]=[Link] order by [Link]
desc;

5. Show the hobbies of those whose name pronounces as ‘Abhay’.

select hobby from personal where name like 'Abha%';

6. Show the appointment date and the native place of those whose name
starts with ‘A’ or ends in ‘d’.
select job.App_date, [Link] from personal, job where
[Link]=[Link] and ([Link] like 'A%' or
[Link] like '%d');

7. Show the salary expense with suitable column heading of those who
shall retire after 20-jan-2020
select salary as "salary expense" from job where retd_date >
'2020-01-20';

8. Show additional burden on the company in case salary of employees

having hobby as sports, is increased by 10%.
SELECT [Link]+[Link]*10/100 as “burden” FROM
personal,job where [Link]='Sports' and
[Link]=[Link];

9. Show the hobby of which there are 2 or more than 2 employees.

select hobby,count(hobby) from personal group by hobby having
count(hobby)>=2;

10. Show how many employees may retire today if maximum length of
service is more than 10 years.
select count(*) from job where (year(now())-year(app_date))>10;

11. Show the names and date of birth of those employees who have served
for more than 11 yrs. as on date.
Select [Link], [Link] from job,personal where
[Link]=[Link] and year(now())-year(app_date)>11;

12. Show empno, name and Increased salary of the employee by 5% of their
present salaries with hobby as Music and completed at least 3 yrs. of
service.
select [Link],[Link],[Link]+ [Link]*5/100
from personal,job where [Link]= 'Music' and
(year(now())year(job.app_date))>3 and [Link]=[Link];

48