RECORDS

Program Based on
Python
 Set – 1: Python Program to Create
Series
Q - 1] Write a Python Program to create a series that stores area of some
states and then find the biggest and the smallest three areas from the series.
Code:
import pandas as pd
#To make Series
states = ['Gujarat' , 'Maharashtra' , 'Delhi' , 'Rajasthan' , 'Odisha' , 'Tamil Nadu' ,
'Assam' , 'Manipur' , 'Goa' , 'Jammu and Kashmir']
area = [196024 , 307713 , 1484 , 342239 , 155707 , 130058 , 78438 , 22327 ,
3702 , 42241]
states_and_areas = [Link](area , index = states)

#Displaying the Series

print("Series containing States and it's Areas:-")
print(states_and_areas)

#Showing Lowest 3 States

print("States with Lowest Areas in the given Series are:-")
print(states_and_areas.sort_values(ascending = True).head(3))

#Showing Top 3 States

print("States with Highest Areas in the given Series are:-")
print(states_and_areas.sort_values(ascending = False).head(3))
Output:

Q - 2] Create a Series object to store all vowels individually with the index
value of 0, 1, 2,… so on.
Code:
import pandas as pd

#To make Series

vowels = ['A' , 'E' , 'I' , 'O' , 'U']
object = [Link](vowels)
#Displaying the Series
print(object)
Output:

Q - 3] Create a Series object of 20 elements between 50 to 100 with index

number as A, B, C,… so on.
Code:
import pandas as pd
import numpy as np

#Defining Numbers
numbers = [Link](50 , 100 , 20)

#Empty List
alphabets = []

#Filling the List with Letters which is to be kept as Index later

for x in [Link](65 , 85 , 1):
letters = chr(x)
[Link](letters)

#Making of Series
series_of_numbers = [Link](numbers , index = alphabets)

#Displaying Series
print(series_of_numbers)
Output:
 Set – 2: Create Series using
Dictionary
Q - 1] Write a python program to create a Series to store 5 students names
and their percentage using dictionary then print all the elements more than
75%.
Code:
import pandas as pd

#Creating Dictionary to make it a Series later on

name_and_percentage = {'Bhavin' : 67 , 'Dhruv' : 88 , 'Ishita' : 92 , 'Om' : 55 ,
'Stuti' : 71}

#Making of Series
series = [Link](name_and_percentage)

#Displaying students with percentage higher than 75

print("Students having percentage higher than 75%:-")
print (series [series > 75])
Output:

Q - 2] Create two Series object of Class 11 and 12 having three

streams(Science, Commerce and Humanities) and their total number of
students. Write a code to find overall total number of students stream wise.
Code:
import pandas as pd

#Defining data to be used further

streams = ['Science' , 'Commerce' , 'Humanities']
students_11 = [26 , 39 , 11]
students_12 = [25 , 34 , 10]

#Making of both Series

data_11 = [Link](students_11 , index = streams)
data_12 = [Link](students_12 , index = streams)

#Finding total number of students stream wise:-

#Following are three ways to do it --->
print("-----First Method-----")
print (data_11 + data_12)

print("-----Second Method-----")
print(data_11.add(data_12))

print("-----Third Method-----")
print(data_11.radd(data_12))
Output:
Set – 3: Create Series using Scalar
Value
Q - 1] Write a python program to create a Series object that stores budget
allocation of 5 lakh ub each 4 quarter of year 2024.
Code:
import pandas as pd
#Making of quarters which are to be used as index
quarters = ['Quarter 1' , 'Quarter 2' , 'Quarter 3' , 'Quarter 4']
#Making of series and assigning it a name
series = [Link](500000 , index = quarters)
[Link] = "Budget Allocation in Quarters of 2024"
#Displaying the series
print(series)
Output:

Q -2] Write a python code in which total number of 350 students admitted in
Yojana from Year 2017 to 2023.
Code:
import pandas as pd
#Making of years to be used later as index

years = [2017 , 2018 , 2019 , 2020 , 2021 , 2022 , 2023]

#Making Series and assigning it a name

series = [Link](350 , index = years)

[Link] = "Total Number of students admitted in Yojana in every year from
2017"
#Displaying the series

print(series)

Output:

Set – 4: Updating and Modifying

the Series Object
Q - 1] Write a python program to create a Series Object with Employee Name
as Index value and their Salary as value.
(i) Write a code to change Second Employee’s salary to 3,000.
(ii) Change Ram’s Salary to 5,000.
(iii) Increase Bala and Divya’s Salary by 2,000.
(iv) Display Salaries more than 5,000.
Code:
import pandas as pd

#Defining names and their salaries

names = ['Aditya' , 'Bala' , 'Divya' , 'Isha' , 'Lakshman' , 'Navya' , 'Prabhas' , 'Ram' ,
'Sachi' , 'Vrunda']
salaries = [2500 , 1200 , 5300 , 7400 , 4600 , 8800 , 3100 , 6900 , 9000 , 10000]

#Making of series and assigning it a name

series = [Link](salaries , index = names)
[Link] = "Employee's Data"

#Displaying the data

print("Displaying Employee's Data:-")
print(series)

#Answer of (i)
print("-----Answer of (i)------")
series[1] = 3000
print(series)

#Answer of (ii)
print("-----Answer of (ii)-----")
series['Ram'] = 5000
print(series)

#Answer of (iii)
print("-----Answer of (iii)-----")
series['Bala'] = series['Bala'] + 2000
series['Divya'] = series['Divya'] + 2000
print(series)

#Answer of (iv)
print("-----Answer of (iv)-----")
print(series[series > 5000])
Output:

Set – 5: Performing Mathematical

Operations and Attributes
Q - 1] Create two Series S1 and S2. S1 of value 10, 20, 30, 40 with index A,
B, C, D. S2 of value 2, 5, 7 with index A, B, C.
(i) Perform following mathematical operations:-
a) Addition
b) Subtraction
c) Multiplication
d) Division
(ii) Display index value of S1.
(iii) Display data type of S1.
(iv) Display size of S1.
(v) Display the shape of S1.
(vi) Display existence of NaN value of addition of S1 and S2.
Code:
import pandas as pd

#Defining indexes and values to be used

s1_index = ['A' , 'B' , 'C' , 'D']
s1_values = [10 , 20 , 30 , 40]
s2_index = ['A' , 'B' , 'D']
s2_values = [2 , 5 , 7]
#Making and Displaying of series
S1 = [Link](s1_values , index = s1_index)
S2 = [Link](s2_values , index = s2_index)
print("Series 1:-")
print(S1)
print("Series 2:-")
print(S2)

#Answer of (i)
print("-----Answer of (i)-----")
print("Addition:-")
print([Link](S2))
print("Subtraction:-")
print([Link](S2))
print("Multiplication:-")
print([Link](S2))
print("Division:-")
print([Link](S2))

#Answer of (ii)
print("-----Answer of (ii)-----")
print("Indexes of S1 Series are:-")
print([Link])

#Answer of (iii)
print("-----Answer of (iii)-----")
print("Data Type of S1 Series is:-")
print([Link])

#Answer of (iv)
print("-----Answer of (iv)-----")
print("Size of S1 Series is:-")
print([Link])

#Answer of (v)
print("-----Answer of (v)-----")
print("Shape of S1 Series is:-")
print([Link])

#Answer of (vi)
print("-----Answer of (vi)-----")
Added_S1_S2 = S1 + S2
print("Added Series")
print(Added_S1_S2)
print("Checking if the New Series has Nan in it:-")
print(Added_S1_S2.hasnans)
Output:
 Set – 6: Create Dataframe using
Nested List and Dictionary
Q - 1] Create Dataframe using
Bike Name Cost
TVS Jupiter 40,000
Bajaj Discover 50,000
Hero Splender 60,000
(i) Nested List
(ii) Nested Dictionary
(iii) Display the average cost of Bike
(iv) Display the shape, size and dimension of the Dataframe
(v) Display in index value of this Dataframe
(vi) Create a Series named as 2024 having 3 random increased amount
and then create another Dataframe which stores the cost of the Bike
with increased amount.
Code:
import pandas as pd

#Answer of (i)
print("-----Answer of (i)-----")
nested_list = [['Jupiter' , 40000] , ['Discover' , 50000] , ['Splendor' , 60000]]
col_name = ['Bike Name' , 'Cost']
index_name = ['TVS' , 'Bajaj' , 'Hero']
DF_from_list = [Link](nested_list , index = index_name , columns =
col_name)
print(DF_from_list)

#Answer of (ii)
print("-----Answer of (ii)-----")
data = {'Bike Name' : {'TVS' : 'Jupiter' , 'Bajaj' : 'Discover' , 'Hero' : 'Splendor'} ,
'Cost' : {'TVS' : 40000 , 'Bajaj' : 50000 , 'Hero' : 60000}}
DF_from_dict = [Link](data)
print(DF_from_dict)

DF = DF_from_dict
#Answer of (iii)
print("-----Answer of (iii)-----")
print([Link](numeric_only = True))

#Answer of (iv)
print("-----Answer of (iv)-----")
print("Shape of the given dataframe is" , [Link])
print("Size of the given dataframe is" , [Link])
print("Dimensions of the given dataframe is" , [Link])

#Answer of (v)
print("-----Answer of (v)-----")
print("Indexes present in this dataframe are" , [Link])

#Answer of (vi)
print("-----Answer of (vi)-----")
increased_amount = [4000 , 5000 , 6000]
amount_2024 = [Link](increased_amount , index = index_name)
new_cost = DF['Cost'] + amount_2024
new_data = {'Bike Name' : DF['Bike Name'] , 'Cost' : new_cost}
new_DF = [Link](new_data)
print(new_DF)
Output:
 Set – 7: Accessing Rows and
Columns from Database
Q – 1] Create 3 Series as D_ID, D_Salary, D_NOE(Number of Employees) as
each Department ID.
(i) Create a Dataframe named ‘org’.
(ii) Display the Dataframe along with Department ID.
(iii) Display the Dataframe along with Department Name.
(iv) Calculate the Average Salary of each Department.
(v) Change/Set a Department Name as Column Name.
Code:
import pandas as pd

#Making of 3 Series
data_ID = [1 , 2 , 3 , 4 , 5 , 6]
D_ID = [Link](data_ID)
data_Salary = [10000 , 20000 , 30000 , 40000 , 50000 , 60000]
D_Salary = [Link](data_Salary)
data_NOE = [60 , 50 , 40 , 30 , 20 , 10]
D_NOE = [Link](data_NOE)

#Answer of (i)
org = [Link]([D_ID , D_Salary , D_NOE]).T
[Link] = ['D_ID' , 'D_Salary' , 'D_NOE']

#Answer of (ii)
print("-----Answer of (ii)-----")
print(org)

#Answer of (iii)
print("-----Answer of (iii)-----")
org['D_ID'] = ['Admin' , 'Accounts' , 'Management' , 'Production' , 'Marketing' ,
'Sales']
print(org)

#Answer of (iv)
print("-----Answer of (iv)-----")
print("Average Salary of each department are")
print([Link](axis = 1 , numeric_only = True))

#Answer of (v)
print("-----Answer of (v)-----")
[Link](columns = {'D_ID' : 'Department_Name'} , inplace = True)
print(org)
Output:

Set – 8: Filtering Rows and

Columns/Filter Data from
Dataframe
Q – 1] Create a Dataframe as Student, Degree and Percentage.
(i) Display the index and the column.
(ii) Change the index as S1, S2, S3.
(iii) Display the shape and dimensions of Dataframe
(iv) Display all the students information whose percentage is more than
85%.(with and without loc)
(v) Add one more record as S6 and their values.
(vi) Display the degree and percentage of M.C.A. students.
(vii) Count degree wise students.
 (viii) Display the student name and percentage of last three students.
(ix) Display the records or student information as per percentage wise.
(x) Write a python code to display Dataframe values as row wise by using
iter row function. Similarly, columns and their values by using iter
items function.
(xi) Save this Dataframe into [Link] file into your own named folder.
Code:
import pandas as pd

data = {'Student' : ['Amin' , 'Dhairya' , 'Lakshmi' , 'Ravi' , 'Vrushti'] , 'Degree' : ['MBA'

, 'MCA' , 'BCom' , 'BCA' , 'MCom'] , 'Percentage' : [89 , 92 , 75 , 80 , 90]}
df = [Link](data)
print(df)

#Answer of (i)
print("-----Answer of (i)-----")
print("Index of the DataFrame are:-")
print([Link])
print("Columns of the DataFrame are:-")
print([Link])

#Answer of (ii)
print("-----Answer of (ii)-----")
[Link] = ['S1' , 'S2' , 'S3' , 'S4' , 'S5']
print(df)

#Answer of (iii)
print("-----Answer of (iii)-----")
print("Shape of the Dataframe is:-")
print([Link])
print("Dimension of the DataFrame are:-")
print([Link])

#Answer of (iv)
print("-----Answer of (iv)------")
print("Without LOC:-")
print(df[df['Percentage'] > 85])
print("With LOC:-")
print(df[[Link][: , 'Percentage'] > 85])

#Answer of (v)
print("-----Answer of (v)-----")
[Link]['S6' , :] = ['Yash' , 'MCA' , 67]
print(df)

#Answer of (vi)
print("-----Answer of (vi)-----")
print("Without LOC:-")
print(df[[Link][: , 'Degree'] == "MCA"][['Degree' , 'Percentage']])
print("With LOC:-")
print(df[[Link][: , 'Degree'] == "MCA"].loc[: , 'Degree' : 'Percentage'])

#Answer of (vii)
print("-----Answer of (vii)-----")
print([Link]('Degree')['Degree'].count())

#Answer of (viii)
print("-----Answer of (viii)-----")
print(df[['Student' , 'Percentage']].tail(3))

#Answer of (ix)
print("-----Answer of (ix)-----")
print("Ascending Order:-")
print(df.sort_values(by = 'Percentage'))
print("Descending Order:-")
print(df.sort_values(by = 'Percentage' , ascending = False))

#Answer of (x)
print("-----Answer of (x)-----")
print("Using iterrows function:-")
for index , row in [Link]():
print(index , row)
print()
print("Using items function:-")
for index , column in [Link]():
print(index , column)
print()

#Answer of (xi)
df.to_csv(f"Jasjot/[Link]")
Output:
 Set – 9: Programs based on
Matplotlib
Q – 1] Open the csv file and display the bar graph between the average
percentage relative to it’s degree along with title, x and y label, grid and save
it into [Link] file.
Code:
import [Link] as plt
import pandas as pd
#To read and convert the csv file into Dataframe
data = pd.read_csv(f"H:\Informatics Practices\Informatics Practices Practical File\Set
8\Jasjot\[Link]")
#Plotting bar graph from the given data
x = data['Degree']
y = data['Percentage']
[Link](x , y)
#Writing the appropriate information
[Link]("Student's Degrees and Percentages")
[Link]("Degree")
[Link]("Percentage")
[Link]()

#Saving the Bar Graph as an image

[Link]('[Link]')

#Showing the Bar Graph

[Link]()
Output:

Q – 2] Read the csv file

which stores the
student’s record as
Subject, Section A and
Section B. Print a
Multiple Bar Chart
which includes subject
as x data and data
comparison as section
wise. Also display label, titles and other appropriate contents.
Code:
import pandas as pd
import [Link] as plt
import numpy as np
#To read and convert the csv file into Dataframe:-
data = pd.read_csv(f"H:\Informatics Practices\Informatics Practices Practical File\Set
9\[Link]")
#Defining data which is to be used in the Multiple Bar Graph:-
subject = data["Unnamed: 0"]
x_axis = [Link](len(subject))
section_a = data["Section A"]
section_b = data["Section B"]
#Making the Multiple Bar Graph:-
[Link](x_axis - 0.2 , section_a , width = 0.4 , label = "Section A")
[Link](x_axis + 0.2 , section_b , width = 0.4 , label = "Section B")
#Changing the data of x axis to subjects:-
[Link](x_axis , subject)
#Writing the appropriate information:-
[Link]("Marks of Students from Different Sections in various Subjects")
[Link]("Subjects")
[Link]("Marks of Students")
#Function to be used to show the labels in the Multiple Bar Graph:-
[Link]()
#Showing the Bar Graph:-
[Link]()
Output:

RECORDS
Question Based
on MySQL
 Table:COACHING
I NAME AG CITY FEE PHONE
D E
P SAMEER 28 DELHI 4500 98110766
1 0 56
P ARYAN 34 MUMBAI 5400 99113439
2 0 89
P RAM 28 CHENNA 4500 98105935
4 I 0 78
P PREMLAT 36 BHOPAL 6000 99101399
6 A 0 87
P SHIKHA 36 INDORE 3400 99121394
7 0 56
P RADHA 32 DELHI 2300 81106688
8 0 88

Write the queries for needed output:

Q1. To display NAME in descending order whose AGE is more than
23.
Select NAME from COACHING where AGE>23 order by NAME desc;
Q2. To find the average FEE grouped by AGE.
Select average(FEE) from COACHING group by AGE;
Q3. To find the total FEE in which ‘O’ character is available in CITY.
Select sum(FEE) from COACHING where CITY like ‘%o%’;
Q4. To display NAME and number of character available in NAME.
select NAME,length(NAME) from COACHING;

Q5. To increase FEE by 5000 whose AGE is more than 30.

update COACHING set FEE=FEE+5000 where AGE>30;
Q6. To show the maximum and minimum FEE of coaches.
select max(FEE),min(FEE) from COACHING;
Q7. To show NAME and PHONE of coach whose FEE is more than
50000.
select NAME,PHONE from COACHING where FEE>50000;
Q8. What will be the output of the followingqueries:
i. Select NAME,AGE from COACHING where FEE>45000;
OUTPUT:
NAME AG
E
ARYAN 45
PREMLAT 36
A
 ii. Select ID,PHONE from COACHING order by NAME desc;
OUTPUT:
I PHONE
D
P 99113439
2 89
P 99101399
6 87
P 81106688
8 88
P 98105935
4 78
P 98110766
1 56
P 99121394
7 56

iii. Select ID,NAME,AGE,FEE from COACHING where (‘DELHI’ and

‘BHOPAL’) not in CITY;
OUTPUT:
I NAME AG FEE
D E
P ARYAN 34 5400
2 0
P RAM 28 4500
4 0
P SHIKH 36 3400
7 A 0

TABLE:- EMP

Emp Ename DOJ Job Salary Gender

No
101 Raj 1998-08-17 Clerk 34000.00 M
102 Bina 1997-11-24 Manager 75000.00 F
103 Amir 1991-02-27 Salesman 30000.00 M
104 Kuldip 1997-01-23 Salesman 28999.99 M
105 Jatin 1998-12-31 Accountant 55000.00 M
106 Mita 2001-01-01 Clerk 27000.00 F
107 Vimal 2001-10-31 Manager 85000.00 M

1) To insert any 3 entries which are displayed in above table.

ANSWER:-
insert into EMP values(101,’Raj’,’1998-08-17’,’Clerk’,34000.00,’M’);
insert into EMP values(102,’Bina’,’1997-11-24’,’Manager’,75000.00,’F’);
insert into EMP values (103,’Amir’,’1991-02-27’,’Salesman’,30000.00,’M’);

2) To display Employee names, DOJ and salaries for all Employees.

ANSWER:-
select Ename,DOJ,Salary from EMP;

3) To display all jobs available in the table whose salary is 30000.00.

ANSWER:-
select Job from EMP,
where Salary=30000.00;

4) To dispplay deatials of all female employee who are manager.

ANSWER:-
select * from EMP,
where Job=’Manager’ and Gender=’F’;

5) To display employee name, DOJ for all female employee.

ANSWER:-
select Ename,DOJ from EMP,
where Gender=’F’;

TABLE:- PRODUCT

Pid Pname Pprice Pqty DOP

001 Keyboard 800 3 2022-01-01
002 Mouse 380 6 2021-12-23
003 Speaker 1800 3 2022-01-01
004 Headphone 1000 6 2021-10-02
005 Stylus 3000 2 2022-01-19

1) To create the above displayed table as PRODUCT.

ANSWER:-
create table PRODUCT (
Pid int(3) Primary Key,
Pname varchar(10),
Pprice int(5),
Pqty int(2),
DOP date);

2) To display product name and price whose price is grater than

1500.
ANSWER:-
select Pname,Pprice from PRODUCT
where Pprice>1500;

3) To display product id, name and total price of product whose last
letter is ‘e’.
ANSWER:-
select Pid,Pname,Pprice*Pqty from PRODUCT where Pname like’%e’;

4) To display max date of purchase whose price is greater than 500.

ANSWER:-
select Max(DOP) from PRODUCT
where Pprice >500;

5) To display the sum of price whose total price>1500.

ANSWER:-
select Sum(Pprice) from PRODUCT
where Pprice>1500;

TABLE:- ProductE

Pid Pname Manufacturer Price Discount

1001 Talcum LAK 40 0
Powder
1002 Face Wash ABC 45 5
1003 Bath Soap ABC 55 0
1004 Shampoo XYZ 120 10
1005 Face Wash XYZ 95 0

1) To create the above displayed table as ProductE.

ANSWER:-
create table ProductE (
Pid int(5) Primary Key,
Pname varchare(10),
Manufacturer char(3),
Price int(2),
Discount int(2));

2) To display Product name , price Manufaturer-wise.

ANSWER:-
select Pname,Price from ProductE
order by Manufacturer;

3)To display the sum of the price of all products where there is no
discount.
ANSWER:-
select Sum(Price) fro ProductE
where Discount=0;

4) To display total number of manufaturers.

ANSWER:-
select Distinct(Manufacturer) from ProductE;

5)To count the total number of manufacturers.

ANSWER:-
select count(Manufacturer) from ProductE;

JOINING
TABLE: STATIONARY
S_ID StationaryNa Compan Pric
me y e
DP0 Dot pen ABC 10
1
PL0 Pencil XYZ 6
2
ER0 Erasor XYZ 7
5
PL0 Pencil CAM 5
1
GP0 Gel pen ABC 15
2

TABLE: CONSUMER
C_I ConsumerNa Address S_ID
D me
01 Good learner Delhi PL01
06 Write well Mumbai GP0
2
12 Topper Delhi DP0
1
15 Write & draw Delhi PL02
16 Motivation Bangalor PL01
e
Write queries for the needed output:
Q1. To display the details of those consumer whose Address is Delhi.
select * from CONSUMER where Address=‘Delhi’;
Q2. To display the details of stationary whose price is in range of 8
to 15 (both values are included).
select * from STATIONARY where Price>=8 and Price<=15;
Q [Link] display the ConsumerName, Address, Company and Price.
select [Link], [Link], [Link], [Link] from
CONSUMER c,STATIONARY s where c.S_ID=s.S_ID;

[Link] increase the price of all stationary by 2.

update STATIONARY set Price=Price+2;
[Link] display StationaryName, ConsumerName, Address, Company,
Price order by Company.
elect [Link], [Link], [Link], [Link],
[Link] from STATIONARY s,CONSUMER c where s.S_ID=c.S_ID order by
[Link];
What will be the output of the following queries:
i. Select Company,max(Price),count(*) from STATIONARY group by
company;
OUTPUT:
Compan max(Pric count(
y e) *)
ABC 10 4
CAM 5 2
XYZ 7 4

[Link] [Link], [Link], [Link] from

CONSUMER c,STATIONARY s where c.S_ID=s.S_ID;
OUTPUT:
ConsumerNa StationaryNa Price
me me
Good learner Pencil 5
Write well Gel pen 15
Topper Dot pen 10

Write & draw Pencil 6

Motivation Pencil 5
iii. Select c.C_ID, [Link], [Link] from CONSUMER
c,STATIONARY s where c.S_ID=s.S_ID and Company=‘ABC’;
OUTPUT:
C_I Addre Compan
D ss y
06 Mumba ABC
i
12 Delhi ABC

iv. Select [Link],[Link] from CONSUMER c,STATIONARY s

where c.S_ID=s.S_ID and Price<=6;

OUTPUT:
Addre Compan
ss y
Mumba ABC
i
Delhi ABC
Delhi XYZ

v. Select [Link], [Link], [Link], [Link]

from STATIONARY s,CONSUMER c where s.S_ID=c.S_ID and
Address<>’Delhi’;
OUTPUT:
StationaryNa Compan ConsumerNa Address
me y me
Pencil CAM Motivation Bangalor
e
Gel pen ABC Write well Mumbai