INDEX

SNO DATE QUESTION SIGN

1 PROGRAM 1) Write code to create a Series object
that stores the initial budget allocated for the four
quarters of the year
2 PROGRAM 2)Write code to create Series objects
from lists named Acc,BSt,Eco,Eng,IP containing
marks of 5 students in five subjects.
3 PROGRAM 3)Create and display a series P1
containing prices of 4 stationery items as given
below
4 PROGRAM 4) Consider two series objects staff and
salaries that store the number of no. of people in
various office branches and salaries distributed in
these branches respectively.
5 PROGRAM 5) Write a program to create a dataframe
from a list containing dictionaries of the sales
performance of four zonal offices.

6 PROGRAM 6) Create and display a DataFrame from

a 2D dictionary , Emp , which stores the salary of 4
employees as inner dictionary for two years

7 PROGRAM 7)Consider the following dataframe

student: Write code to create above dataframe and
perform given operations.
8 PROGRAM 8) Consider the following dataframe Item
Write a program to create a dataframe Item as given
above and perform given operations.
9 PROGRAM 9) Write a program to create a dataframe
df1 containing sales of three years as yr1,yr2,yr3
and perform given operations.
10 PROGRAM 10) Write a program to create a dataframe
stu containing Name, marks of Term-I and Term-II.
Make rno as the index. Now extract the data column-
wise from the dataframe stu.
11 PROGRAM 11) Write a program to plot a bar chart for
the following set of data :
Weight measurements for small order of french –
fries
78 72 69 55 65 40
a)Create a simple bar chart from the above data in
green colour and suitable labels for axes
12 PROGRAM 12) Given a dataframe df1 as shown
below: Write code to create :
a) A scatter chart b) A line chart
13 PROGRAM 13): Write a program to (i) create a line
chart from first 10 terms of Fibonacci series using
linestyle as dashdot and colour as brown.(ii) create a
line chart with solid line and colour as red
14 PROGRAM 14): Write a program to create a scatter
chart from available quantity of Nestle chocolates as
10,11,14,20,23 in 5 shops in blue colour.
15 PROGRAM 15): Write a python program to plot the
speed of a passenger train as shown in the figure
given below
16 PROGRAM 16): Write the code in pandas to create
the following dataframes :
Write the commands to do the following operations
on the dataframes given above
17 PROGRAM 17) Given a table showing meters, speed,
weight of a product. Create a dataframe from the data
and then create scatter charts
18 PROGRAM 18) Given below is the score of 3 teams in
5 IPL matches. Write a program to create a dataframe
from the above and then plot a bar chart using
different colours, legend, title. Save the contents of
the dataframe to a csv file named
‘scores.csv’
19 PROGRAM 19) Navya has started an online business
of stationery items. A list stores the number of
orders in last 6 months as [500,250,235,432,320,540].
Write a program to plot this data on a horizontal bar
chart. Use different colours and also label the x-axis
and y-axis
20 PROGRAM 20) Consider the table given below: Now
write a program to plot a bar chart from the medals
won by the four countries. Make sure that bars are
separately visible and should be in different colours.
Also use suitable title, labels for x-axis and y-axis.

21 SQL QUERIES

PROGRAM 1) Write code to create a Series object that stores the initial
budget allocated for the four quarters of the year :
Qtr1, Qtr2, Qtr3 and Qtr4. Display the first two values from the above
series.
Also arrange the series in descending order of values and display it.
ANSWER:
import pandas as pd
quarter=[50000,56000,76000,72000]
ser1=pd.Series(quarter,index=['Qtr1','Qtr2','Qtr3','Qtr4'])
print(ser1) print("First two values") print(ser1.head(2))
print("Series in descending order")
ser2=ser1.sort_values(ascending=False) print(ser2)

OUTPUT:
Qtr1 50000
Qtr2 56000
Qtr3 76000
Qtr4 72000
dtype: int64 First
two values
Qtr1 50000 Qtr2
56000 dtype:
int64
Series in descending order
Qtr3 76000
Qtr4 72000
Qtr2 56000 Qtr1
50000
dtype: int64
PROGRAM 2)Write code to create Series objects from lists named
Acc,BSt,Eco,Eng,IP containing marks of 5 students in five subjects.
Assign the index as RollNo1, RollNo2, RollNo3, RollNo4, RollNo5.
Create series objects containing the total and average marks and
display it.

ANSWER:
import pandas as pd Acc=[45,67,80,56,34]
BSt=[65,70,76,92,95]
Eco=[80,67,45,46,74]
Eng=[75,57,89,95,99] IP=[89,78,87,90,98]
rn=['RollNo1','RollNo2','RollNo3','RollNo4','RollNo5']
ser1=pd.Series(Acc, index=rn)
ser2=pd.Series(BSt,index=rn)
ser3=pd.Series(Eco,index=rn)
ser4=pd.Series(Eng,index=rn) ser5=pd.Series(IP,index=rn)
tot=ser1+ser2+ser3+ser4+ser5
tot.index=rn #find total print("Total
marks of the students") print(tot)
av=tot/5 #find average
av.index=rn
print("Average marks of the students") print(av)
OUTPUT:
Total marks of the students
RollNo1 354
RollNo2 339
RollNo3 377
RollNo4 379
RollNo5 400 dtype:
int64
Average marks of the students
RollNo1 70.8
RollNo2 67.8
RollNo3 75.4
RollNo4 75.8 RollNo5 80.0 dtype: float64 PROGRAM 3)Create and
display a series P1 containing prices of 4 stationery items as given
below : [“Pencils”,”Notebooks”,”Scales”,”Erasers”].
The names will become indices of the series P1.
Create and display another series P2 containing quantity of the 4
items.
Now create and display a series TOTAL that contains total price as
price*qty.
Display the second and third values from the total series.
ANSWER:
import pandas as pd
Stationery=['Pencils','Notebooks','Scales','Erasers']
P1=pd.Series([20,45,15,10],index=Stationery)
P2=pd.Series([17,12,30,22],index=Stationery)
print("Prices of stationery items") print(P1)
print("Quantity of stationery items")
print(P2)
TOTAL=P1*P2
print("Total prices of stationery items")
print(TOTAL)
print(TOTAL[1:3])

OUTPUT:
Prices of stationery items
Pencils 20
Notebooks 45
Scales 15
Erasers 10
dtype: int64
Quantity of stationery items
Pencils 17
Notebooks 12
Scales 30
Erasers 22
dtype: int64
Total prices of stationery items
Pencils 340
Notebooks 540
Scales 450
Erasers 220
dtype: int64
Notebooks 540
Scales 450
dtype: int64
PROGRAM 4) Consider two series objects staff and salaries that store
the number of no. of people in various office branches and salaries
distributed in these branches respectively.
Display data of salary above 400000 from the series.
Write a program to create another Series object that stores average
salary per branch and then
Create a DataFrame object from these Series objects.

ANSWER:
import pandas as pd
import numpy as np
staff=pd.Series([20,36,44])
sal= pd.Series([279000,396800,563000])
print(sal[sal>400000])
avg= sal/ staff
org={'people':staff,'Amount': sal ,'Average':avg}
print(org)
dt1=pd.DataFrame(org)
print(dt1)

OUTPUT:
2 563000
dtype: int64
{'people': 0 20
1 36
2 44
dtype: int64, 'Amount': 0 279000
1 396800
2 563000
dtype: int64, 'Average': 0 13950.000000
1 11022.222222
2 12795.454545
dtype: float64}
people Amount Average
0 20 279000 13950.000000
1 36 396800 11022.222222
2 44 563000 12795.454545
PROGRAM 5) Write a program to create a dataframe from a list
containing dictionaries of the sales performance of four zonal offices.
Zone names should be the row labels.
Add a new column City with values as New Delhi.
Rename the column Target as New_Target

ANSWER:
import pandas as pd
zoneA={'Target':56000,'Sales':58000}
zoneB={'Target':76000,'Sales':48000}
zoneC={'Target':32000,'Sales':65000}
zoneD={'Target':52000,'Sales':84000}
sales=[zoneA,zoneB,zoneC,zoneD]
saleDf=pd.DataFrame(sales,index= ['zoneA','zoneB','zoneC','zoneD'])
print(saleDf)
saleDf['City']='New Delhi'
print(saleDf)
saleDf.rename(columns={'Target':'New_Target'} ,inplace=True)
print(saleDf)

OUTPUT:
Target Sales
zoneA 56000 58000
zoneB 76000 48000
zoneC 32000 65000
zoneD 52000 84000
Target Sales City
zoneA 56000 58000 New Delhi
zoneB 76000 48000 New Delhi
zoneC 32000 65000 New Delhi
zoneD 52000 84000 New Delhi
New_Target Sales City
zoneA 56000 58000 New Delhi
zoneB 76000 48000 New Delhi
zoneC 32000 65000 New Delhi
zoneD 52000 84000 New Delhi
PROGRAM 6) Create and display a DataFrame from a 2D dictionary ,
Emp , which stores the salary of 4 employees as inner dictionary for
two years, as shown below:
Sal={‘yr1’:{‘E101’:34500, ‘E102’:56000, ‘E103’:47000,‘E104’:49000},
‘yr2’:{‘E101’:38500, ‘E102’:58000, ‘E103’:46500,‘E104’:55000}}
Display the data of those employees whose sales is below 45000 in
second year.
Add a new row containing details of yr3
Change the data of E101 as [35000,45000] Display
the salary of all employees in first year
ANSWER:
import pandas as pd
Sal={'yr1':{'E101':34500, 'E102':56000,'E103':47000,'E104':49000},
'yr2':{'E101':38500, 'E102':58000, 'E103':46500,'E104':55000}}
dfsales=pd.DataFrame(Sal)
dt1=dfsales[dfsales['yr2']>40000]
print("Data of employees whose sales is below 45000 in second year")
print(dt1['yr2'])
print("NEW ROW ADDED")
dfsales.loc['E105']=[55500,57000]
print(dfsales)
print("CHANGES DONE IN ROW 1")
dfsales.loc['E101']=[35000,45000]
print(dfsales)
print("SALARY IN FIRST YEAR")
print(dfsales['yr1'])

Output
Data of employees whose sales is below 45000 in second year
E102 58000
E103 46500
E104 55000
Name: yr2, dtype: int64
NEW ROW ADDED
yr1 yr2
E101 34500 38500
E102 56000 58000
E103 47000 46500
E104 49000 55000
E105 55500 57000
CHANGES DONE IN ROW 1
 yr1 yr2
E101 35000 45000
E102 56000 58000
E103 47000 46500
E104 49000 55000
E105 55500 57000
SALARY IN FIRST YEAR
E101 35000
E102 56000
E103 47000
E104 49000
E105 55500
Name: yr1, dtype: int64
PROGRAM 7)Consider the following dataframe student:
Rno Name Fees
Id1 1001 Ram 5000
Id2 1002 Shyam 6000
Id3 1003 Meera 5000
Id4 1004 Seeta 6000
a. Write code to create above dataframe
b. Write a code to change the data of id3 with (1005,Ramesh,8000)
c. Write code to change the rno of id1 with 115.
d. Write code to display values of two columns name and fees only
ANSWER:
#program=7
import pandas as pd
d1={'Rno':[1001,1002,1003],
'Name':['Ram','Shyam','Meera'],
'Fees':[5000,6000,5000]}
student=pd.DataFrame(d1,index=['Id1','Id2','Id3'])
print("Values of dataframe student")
print(student)
print("After changing the values of id3")
student.iloc[2]=[1005,'Ramesh',8000]
print(student)
print("After changing the rno of id1")
student.iat[0,0]=115
print(student)
print("Values of name and fees column")
print(student.loc[:,'Name':'Fees'])

OUTPUT:
Values of dataframe student
Rno Name Fees
Id1 1001 Ram 5000
Id2 1002 Shyam 6000
Id3 1003 Meera 5000
After changing the values of id3
Rno Name Fees
Id1 1001 Ram 5000
Id2 1002 Shyam 6000
Id3 1005 Ramesh 8000
After changing the rno of id1
Rno Name Fees
Id1 115 Ram 5000
Id2 1002 Shyam 6000
Id3 1005 Ramesh 8000
Values of name and fees column
Name Fees
Id1 Ram 5000
Id2 Shyam 6000
Id3 Ramesh 8000
PROGRAM 8) Consider the following dataframe Item
Fcode Fname Price
Nestle F01 Chocolate 50
Parle F02 Biscuit 45
Britannia F03 Biscuit 30

a. Write a program to create a dataframe Item as given above.

b. Add a column orders having values 500,600,700 respectively.
c. To add a new row as 'Amul'=['F04','Chocolate',50,70]
d. To display the content of last two rows.
e. To modify the price of Nestle as 60
ANSWER:
#program=8
import pandas as pd
d1={'Fcode':['F01','F02','F03'],
'FName':['Chocolate','Biscuit','Biscuit'],
'Price':[ 50,45,30]}
Item=pd.DataFrame(d1,index=['Nestle','Parle','Britannia'])
print("Values of dataframe Item")
print(Item)
print("After adding column orders")
Item['orders']=[500,600,700]
print(Item)
print("After adding a new row")
Item.loc['Amul']=['F04','Chocolate',50,70]
print(Item)
print("The content of last two rows")
print(Item.iloc[2:])
print("Modify the price of Nestle as 60")
Item.iloc[0,2]=60
print(Item)

OUTPUT:
Values of dataframe Item
Fcode FName Price
Nestle F01 Chocolate 50
Parle F02 Biscuit 45
Britannia F03 Biscuit 30
After adding column orders
Fcode FName Price orders
Nestle F01 Chocolate 50 500
Parle F02 Biscuit 45 600
Britannia F03 Biscuit 30 700
After adding a new row
Fcode FName Price orders
Nestle F01 Chocolate 50 500
Parle F02 Biscuit 45 600
Britannia F03 Biscuit 30 700
Amul F04 Chocolate 50 70
The content of last two rows
Fcode FName Price orders
Britannia F03 Biscuit 30 700
Amul F04 Chocolate 50 70
Modify the price of Nestle as 60
Fcode FName Price orders
Nestle F01 Chocolate 60 500
Parle F02 Biscuit 45 600
Britannia F03 Biscuit 30 700
Amul F04 Chocolate 50 70
PROGRAM 9) Write a program to create a dataframe df1 containing
sales of three years as yr1,yr2,yr3. Make names of three salesmen
as 'Arjun','Rajan','Mahesh' .Now extract the data row-wise from the
dataframe df1.
ANSWER:
import pandas as pd
dic1={'yr1':[52000,24500,36500],
'yr2':[45000,52000,75500],
'yr3':[89000,24000,11500] }
df1=pd.DataFrame(dic1,index=['Arjun','Rajan','Mahesh'])
for (r1,r2 )in df1.iterrows():
print("\nROW INDEX",r1)
print("CONTAINS")
i=0
for i in r2:
print(i,end='\t')

OUTPUT:

ROW INDEX Arjun

CONTAINS
52000 45000 89000
ROW INDEX Rajan
CONTAINS
24500 52000 24000
ROW INDEX Mahesh
CONTAINS
36500 75500 11500
PROGRAM 10) Write a program to create a dataframe stu containing
Name, marks of Term-I and Term-II. Make rno as the index. Now
extract the data column-wise from the dataframe stu.

ANSWER:
import pandas as pd
dic1={'Name':['Ram','Priya','Sumit'],
'Term-I':[45,52,75],
'Term-II':[89,40,75] }
stu=pd.DataFrame(dic1,index=['Rno1','Rno2','Rno3'])
print("DataFrame contains")
print(stu)
for (c1,c2 )in stu.iteritems():
print("\nCOLUMN INDEX",c1)
print("CONTAINS")
i=0
for val in c2:
print(val)

OUTPUT:
DataFrame contains
Name Term-I Term-II
Rno1 Ram 45 89
Rno2 Priya 52 40
Rno3 Sumit 75 75

COLUMN INDEX Name

CONTAINS
Ram
Priya
Sumit

COLUMN INDEX Term-I

CONTAINS
45
52
75

COLUMN INDEX Term-II

CONTAINS
89
40
75
PROGRAM 11) Write a program to plot a bar chart for the following set
of data :
Weight measurements for small order of french –fries
78 72 69 55 65 40
a)Create a simple bar chart from the above data in green colour and
suitable labels for axes
b) Create a line chart for the above data marker as diamond

ANSWER:
import matplotlib.pyplot as p1
import numpy as np
x=np.arange(6)
Weight=[78,72,69,55,65,40]
#Bar chart
p1.bar(x,Weight,width=0.25, color='red')
#Line chart
p1.plot(x,Weight, color='green', marker='d')
p1.xlabel("sno of French Fries")
p1.ylabel("Weight (in gms)")
p1.title("Measurement of French Fries")
p1.show()
PROGRAM 12) Given a dataframe df1 as shown below:
2000 2010 2020
A 52 450 890
B 24 520 240
C 36 755 115
Write code to create :
a) A scatter chart from the 2000 and 2010 columns of df1
b) A line chart from the 2010 and 2020 columns of df1
c) ANSWER:
#program=12
import matplotlib.pyplot as p1
import pandas as pd
dic1={2000:[52,24,36],
2010:[450,520,755],
2020:[890,240,115] }
df1=pd.DataFrame(dic1,index=['A','B','C'])
print(df1)
p1.plot(df1.index,df1[2010] , color='green')
p1.plot(df1.index,df1[2020], color='red')
p1.show()
p1.scatter(df1.index,df1[2000] , color='blue')
p1.scatter(df1.index,df1[2010], color='pink')
p1.show()
PROGRAM 13): Write a program to (i) create a line chart from first 10
terms of Fibonacci series using linestyle as dashdot and colour as
brown.(ii) create a line chart with solid line and colour as red
containing the squareroot of the 10 terms of Fibonacci series.
ANSWER:
#program=13
#fibonnaci series chart
import matplotlib.pyplot as py
import math
import numpy as np
x=[0,1,1,2,3,5,8,13,21,34]
z=np.sqrt(x)
y=range(1,11)
py.plot(y,x,'brown',linestyle='dashdot')
py.plot(y,z,'red',linestyle='solid')
py.show()

OUTPUT:
PROGRAM 14): Write a program to create a scatter chart from
available quantity of Nestle chocolates as 10,11,14,20,23 in 5 shops in
blue colour.

ANSWER:

import matplotlib.pyplot as py
import numpy as np
x=[10,11,14,20,23]
y=range(0,21,5)
py.scatter(y,x,color='blue')
py.show()

OUTPUT:
PROGRAM 15): Write a python program to plot the speed of a
passenger train as shown in the figure given below:

ANSWER:
#program=15
import matplotlib.pyplot as plt
import numpy as np
x = np.arange(1, 5)
plt.plot(x, x*1.5, label='Normal')
plt.plot(x, x*3.0, label='Fast')
plt.plot(x, x/3.0, label='Slow')
plt.legend()
plt.show()
PROGRAM 16): Write the code in pandas to create the following
dataframes :
df1 df2
mark1 mark2 mark1 mark2
0 10 50 20 25
1 30 20 15 30
2 40 45 20 30
3 40 70 50 30
Write the commands to do the following operations on the dataframes
given above :
(i) To add dataframes df1 and df2.
(ii) To subtract df2 from df1
(iii) To rename column mark1 as marks1in both the dataframes df1
and df2.
(iv) To change index label of df1 from 0 to zero and from 1 to one.
ANSWER:
#program=16
import numpy as np
import pandas as pd
df1 = pd.DataFrame({'mark1':[30,40,15,40],
'mark2':[20,45,30,70]})
df2 = pd.DataFrame({'mark1':[10,20,20,50],
'mark2':[15,25,30,30]})
print(df1)
print(df2)
print(df1.add(df2)) #i
print(df1.subtract(df2)) #ii
df1.rename(columns={'mark1':'marks1'}, inplace=True) #iii
df1.rename(index = {0: "zero", 1:"one"}, inplace = True)#iv
print(df1)

OUTPUT
mark1 mark2
0 30 20
1 40 45
2 15 30
3 40 70
mark1 mark2
0 10 15
1 20 25
2 20 30
3 50 30
 mark1 mark2
0 40 35
1 60 70
2 35 60
3 90 100
mark1 mark2
0 20 5
1 20 20
2 -5 0
3 -10 40
marks1 mark2
zero 30 20
one 40 45
2 15 30
3 40 70
PROGRAM 17) Given a table showing meters, speed, weight of a
product. Create a dataframe from the data and then create scatter
charts from:
a) speed and meters columns of dataframe
b) meters and weight columns of dataframe
meters speed weight
0 122 10 0.2
1 150 15 0.3
2 100 20 0.1
3 230 18 0.85
4 300 19 0.01

ANSWER:
#program=17
import pandas as pd
import matplotlib.pyplot as p1
#creating dataframe
s={'speed':[10,15,20,18,19],
'meters':[122,150,100,230,300],
'weight':[0.2,0.3,0.1,0.85,0.01]}
dataf1=pd.DataFrame(s)
print(dataf1)
p1.scatter(dataf1['speed'],dataf1['meters'])
p1.show()
p1.scatter(dataf1['meters'],dataf1['weight'])
p1.show()

OUTPUT
speed meters weight
0 10 122 0.20
1 15 150 0.30
2 20 100 0.10
3 18 230 0.85
4 19 300 0.01
PART b:
PROGRAM 18) Given below is the score of 3 teams in 5 IPL matches.
Write a program to create a dataframe from the above and then plot a
bar chart using different colours, legend, title. Save the contents of the
dataframe to a csv file named ‘scores.csv’
Match1 Match2 Match3 Match4 Match5
TeamA 200 307 240 153 150
TeamB 182 147 210 142 233
TeamC 350 322 371 195 240

ANSWER:
#PROGRAM=18
import pandas as pd
import matplotlib.pyplot as p1
#creating dataframe
score={'Match1':[200,182,350],
'Match2':[307,147,322],
'Match3':[240,210,371],
'Match4':[153,142,195],
'Match5':[150,233,240]}
dfscore=pd.DataFrame(score,index=['TeamA','TeamB','TeamC'])
print(dfscore)
#saving data to a csv file
dfscore.to_csv('scores.csv')
#plotting bar chart
dfscore.plot(kind='bar')
p1.xlabel('TEAMS')
p1.ylabel('SCORES OF 5 MATCHES')
p1.show()
OUTPUT:
Match1 Match2 Match3 Match4 Match5
TeamA 200 307 240 153 150
TeamB 182 147 210 142 233
TeamC 350 322 371 195 240
PROGRAM 19) Navya has started an online business of stationery
items. A list stores the number of orders in last 6 months as
[500,250,235,432,320,540]. Write a program to plot this data on a
horizontal bar chart. Use different colours and also label the x-axis
and y-axis.

ANSWER:
#PROGRAM==19

import pandas as pd
import matplotlib.pyplot as plt
#creating list
months=['Jan','Feb','March','April','May','June']
orders=[500,250,235,432,320,540]
plt.title("Monthly orders")
#creating horizontal bar chart
plt.barh(months,orders,color=['r','b','g','m','c','k'])
plt.xlabel("orders")
plt.ylabel("months")
plt.show()

OUTPUT:
PROGRAM 20) Consider the table given below:
Country Gold Silver Bronze Total
Australia 80 59 59 198
England 45 45 46 136
India 26 30 20 76
Canada 15 20 27 62

Now write a program to plot a bar chart from the medals won by the
four countries. Make sure that bars are separately visible and should
be in different colours. Also use suitable title, labels for x-axis and
yaxis. Save the contents of the dataframe to a csv file named
‘countries.csv’

ANSWER:
import numpy as np
import matplotlib.pyplot as plt
plt.figure(figsize=(10,7))
medals=('Gold','Silver','Bronze','Total')
Australia=[80, 59, 59, 198]
England=[45, 45, 46, 136]
India=[26, 30, 20, 76]
Canada =[15, 20, 27, 62]
x=np.arange(len(medals))
plt.bar(medals, Australia,width=0.15)
plt.bar(x+0.15,England,width=0.15)
plt.bar(x+0.30,India,width=0.15)
plt.bar(x+0.45,Canada,width=0.15)
plt.legend()
plt.xlabel("Medal type")
plt.ylabel("Tally of four countries")
plt.show()

OUTPUT:
CONSIDER THE TABLES CLASSDETAILS AND
STUDENT AND WRITE THE SQL QUERIES FOR THE
FOLLOWING:-
SRNO RNO NAME FNAME CLASS PHNO FEES DOB
21312 21 ANANYA MOHIT IX-A 9986778144 2500 2004-1-1
12541 12 NISHA ARUN IX-B 9988744186 2600 2004-02-02
14581 14 NAVISHA GAGAN X-A 8523694171 3500 2003-01-01
14785 5 ARUNA INDER X-B 9656565232 3600 2002-04-04
21451 6 ANVITA PANKAJ XI-A 8565556565 4500 2001-02-22
21552 23 TEENA ROHIT XII-A 8965556321 5000 2002-05-12
19635 41 DIVYA ARJUN XI-B 9854114141 4600 2001-06-13
14578 14 VARNITA AMAN X-B 9452145213 3600 2003-10-23
19852 12 UDISHA NISHANT XI-A 9452012365 4500 2001-12-25
14852 52 ISIKA MOHAN XII-A 9756321452 5000 2001-11-24
19635 6 PRERNA YOGESH XI-B 9652341234 4600 2002-11-18

Q.1. DISPLAY NAME AND ROLLNO. OF THE STUDENTS OF CLASS X-A.

ANS:- SELECT NAME,RNO
FROM STUDENT
WHERE CLASS=’X-A’;
OUTPUT:-
RNO NAME
21 ANANYA

Q.2. DISPLAY NAME AND PHONE NO OF THE STUDENTS WHOSE ROLL NUMBER IS
BELOW 10.
ANS:- SELECT NAME,PHNO
FROM STUDENT
WHERE RNO<10;

Q.3. DISPLAY NAME, ROLLNO AND CLASS OF THE STUDENTS WHOSE FEES IN THE
RANGE OF 2000 AND 3000.
SELECT NAME,RNO,CLASS FROM STUDENT
WHERE FEES>=2000 AND FEES<=3000;
OR
SELECT NAME,RNO,CLASS FROM STUDENT
WHERE FEES BETWEEN 2000 AND 3000;

Q.4. DISPLAY ALL THE DETAILS OF THE STUDENTS OF CLASS XI.

ANS:- SELECT SRNO,RNO,NAME,FNAME,PHNO,FEES,DOB FROM STUDENT WHERE
CLASS=’XI-A’ OR CLASS=’XI-B’;
OR
SELECT * FROM STUDENT WHERE CLASS=’XI-A’ OR CLASS=’XI-B’;

Q.5. DISPLAY ALL THE DETAILS OF THE STUDENTS OF CLASS XI AND XII.
SELECT * FROM STUDENT
WHERE CLASS IN(‘XI-A’,’XI-B’,’XII-A’,’XII-B’);

Q.6. DISPLAY SRNO, NAME OF THE STUDENTS WHOSE FATHER’S NAME END WITH
‘A’
SELECT SRNO,NAME FROM STUDENT WHERE FNAME LIKE ‘%a’;

Q.7. DISPLAY ROLLNO, NAME, FEES OF THE STUDENTS WHOSE NAME BEGIN WITH
S OR N.
SELECT RNO,NAME,FEES
FROM STUDENT
WHERE NAME LIKE ‘S%’ OR NAME LIKE ‘N%’;

Q.8. DISPLAY THE DETAILS OF THE STUDENTS IN ASCENDING ORDER OF SRNO.

SELECT *
FROM STUDENT
ORDER BY SRNO ASC;

Q.9. DISPLAY THE DETAILS OF THE STUDENTS BORN IN YEAR 2001.

SELECT *
FROM STUDENT
WHERE YEAR(DOB)=2001;

Q.10:- DISPLAY NAME, FATHER’S NAME AND FEES OF THE STUDENTS IN

DESCENDING ORDER OF FEES.

SELECT NAME,FNAME,FEES
FROM STUDENT
ORDER BY FEES DESC;

Q.11. DISPLAY TOTAL NO. OF STUDENTS WHOSE FEE IS ABOVE 3000

SELECT COUNT(*)
FROM STUDENT
WHERE FEE>3000;

Q.12. DISPLAY TOTAL NO OF STUDENTS IN EACH CLASS

SELECT CLASS,COUNT(*)
FROM STUDENT
GROUP BY CLASS;

Q.13.DISPLAY HIGHEST AND LOWEST FEES OF THE STUDENTS

SELECT MAX(FEES),MIN(FEES)
FROM STUDENT;

Q.14. DISPLAY DETAILS OF STUDENTS BORN IN MAY.

SELECT *
FROM STUDENT
WHERE MONTH(DOB)=5;

Q.15. DISPLAY FATHER’S NAME AND PHONE NO. OF CLASS XII STUDENTS
SELECT FNAME,PHNO
FROM STUDENT
WHERE CLASS LIKE ‘XII%’;