Practical questions

Python Pandas and Data Visualization

Q.1- Write a program in python to create the following dataframe named “df” storing the
following details:
Tname Post Subject Salary Dt_join
101 Mr.R K TGT Science 45000 2018-04-01
Sharma
102 Mr. Shiv Sahai TGT Hindi 43000 2018-04-01
103 Ms. Rama PRT English 35000 2018-08-11
104 Ms. Preeti PGT IP 76000 2018-01-07
105 Ms. Nidhi PGT IP 80000 2018-08-11
106 Mr. P K TGT Science 45000 2018-01-07
Khanna
Considering the above dataframe answer the following queries by writing appropriate
command in python pandas.
a) Display Teacher Name,subject and Salary for all TGT teachers .
b) Add a Row for a PRT teacher.(Take your own Data). Print the data frame now.
c) Create a CSV File from the above data frame named Teacher.csv.
d) Add a new column Bonus which is 15% of their Salary. Print the data frame now.
e) Now plot a multi-line chart depicting the Teacher Name on x-axis and their
corresponding Salary and Bonus on y-axis, with appropriate Graph title, x-axis title, y-
axis title, gridlines, legends and color etc.

Handle the above options using a Menu driven program.

SQL Queries with Solution
Q.2- Create the table "Teacher" with above data given in the above data frame and define
first column as 'Tid' ( which is row index label in the df) as primary key,(Bonus column is
not required here). Feed all records in to the table from Tid 101 to 106. Now answer
the following sql queries.
Solution SQL Commands:

Table Creation:
Create table Teacher(Ti d int(3) primary key, TName varchar(20),
Post varchar(5), subject varchar(15),
Salary double(7,2), Dt_Join date);

Record Insertion:
Insert into Teacher values(101,’Mr. R K Sharma’,’TGT’,’Science’,45000, '2018-04-01');
Insert into Teacher values(102,'Mr. Shiv Sahai',’TGT’,’Hindi’,43000, '2018-04-01');
Insert into Teacher values(103,'Ms. Rama', ‘PRT’,’English’,33000, '2018-08-11');
Insert into Teacher values(104,'Ms. Preeti', ‘PGT’,’IP’,76000, '2018-01-07');
Insert into Teacher values(105,'Ms. Nidhi', ‘PGT’,’IP’,80000, '2018-08-11');
Insert into Teacher values(106,'Mr. P K Khanna',’TGT’,’Science’, 47000, '2018-01-07');

a) Count and display how many male teachers are there as TGT.
Select count(*) from Teacher where Post='TGT' and Tname like 'Mr.%';

b) Display number of teachers and their average salary for each post category(But only
for PGT and TGT"
 Select post, count(*) as "No. of Teachers", avg(Salary), from Teacher group by Post
having
Post='TGT' or post='PGT';
OR
Select post, count(*) as "No. of Teachers", avg(Salary), from Teacher group by Post
having
Post in('TGT','PGT');

c) Add a new column as 'Contact_No' in the table Teacher.

Alter table Teacher add contact_no bigint(10);

d) increase the salary of PGTs by 15%.

Update Teacher set Salary=Salary+Salary*0.15 where post="PGT";

e) Display detail of Teachers Seniority wise according to date of joining ( Sr. to Jr.).
Select * from Teacher order by Dt_join asc;

Solution: Q.1- Program Code:

# Creation of Dictionary and Data Frame:

import pandas as pd
import matplotlib.pyplot as plt
dict1={'TName':['Mr. R.K. Sharma','Mr. Shiv Sahai','Ms. Rama','Ms. Preeti','Ms. Nidhi','Mr. P K Khanna'],
'Post':['TGT','TGT','PRT','PGT','PGT','TGT'],
'Subject':['Science','Hindi','English','IP','IP','Science'],
'Salary':[45000,43000,35000,76000,80000,45000],
'dojoin':['2018-04-01','2018-04-01','2018-08-11','2018-01-07','2018-08-11','2018-01-07']}
df=pd.DataFrame(dict1)
print(df)

#a) Display Teacher Name,subject and Salary for all TGT teachers.

print(df.loc[(df.Post=='TGT'),['TName','Subject','Salary']])
OR
print(df.loc[(df['Post']=='TGT'),['TName','Subject','Salary']])

#b) Add a Row for a PGT teacher.(Take your own Data). Print the data frame now.

df.loc[107]=['Mr. K K Singh','PGT','English',78000,'2018-01-07']
print(df)
#c) Create a CSV File from the above data frame named Teacher.csv.

df.to_csv("Teacher.csv",index=False)
print("Data Transferred to csv file successfully.")

#d) Add a new column Bonus which is 15% of their Salary. Print the data frame now.

df['Bonus']=df['Salary']*0.15
print(df)
OR
df['Bonus']=df.Salary*0.15
print(df)

# e) Now plot a multi-line chart depicting the Teacher Name on x-axis and their corresponding Salary
and Bonus
# on y-axis, with appropriate Graph title, x-axis title, y-axis title, gridlines, legends and color etc.

x=df['TName']
y1=df['Salary']
y2=df['Bonus']
plt.plot(x,y1,color='r',label='Salary',linestyle='dashdot',linewidth=2,marker='o',markersize=8,markeredg
ecolor='m')
plt.plot(x,y2,color='b',label='Bonus',linestyle='dashed',linewidth=2,marker='^',markersize=8,markeredge
color='m')
plt.xlabel('Teacher Name-->',fontsize=12,color='b')
plt.ylabel('Salary & Bonus(INR)->',fontsize=12,color='b')
plt.legend()
plt.title('ABC Public School\nTeacher Wise Salary Analysis', fontsize=14, color='r')
plt.grid()
plt.show()
 Python Pandas and Data Visualization
Q.1- Write a program in python to create the following dataframe named “df” storing the
following details:
Country Population BirthRate UpdateDate
0 China 1379750000 12.00 2016-08-11
1 India 1330780000 21.76 2016-08-11
2 United States 324882000 13.21 2016-08-11
3 Indonesia 260581000 18.84 2016-01-07
4 Brazil 206918000 18.43 2016-08-11
5 Pakistan 194754000 27.62 2016-08-11
Considering the above dataframe answer the following queries by writing appropriate
command in python pandas.
a) Display complete data for China and India.
b) Display Country, Population and BirthRate of Brazil and Pakistan.
c) Create a CSV File from the above data frame.
d) Retrieve data from above created csv file into a new dataframe df1 with columns
Country and Population only .
e) Now plot a bar chart depicting the Country on x-axis and their corresponding
Population on y-axis, with appropriate Graph title, x-axis title, y-axis title, gridlines and
color etc.

Handle the above options using a Menu driven program.

SQL Queries with Solution
Q.2- Consider the table "Pop" with above data of the given data frame and define first
column 'Country' as primary key, feed all records in to the table. Now answer the
following sql queries.
Solution SQL Commands:

Table Creation:
Create table pop(Country varchar(20) primary key,
Population bigint(13), BirthRate double(5,2), UpdateDate date);
 Record Insertion:
Insert into pop values('China',1379750000,12.00, '2016-08-11');
Insert into pop values('India',1330780000,21.76, '2016-08-11');
Insert into pop values('United States', 324882000,13.21, '2016-08-11');
Insert into pop values('Indonesia', 260581000,18.84, '2016-01-07');
Insert into pop values('Brazil', 206918000,18.43, '2016-08-11');
Insert into pop values('Pakistan', 194754000,27.62, '2016-08-11');

a) Display those records whose population lies in the range 1300000000 and
140000000.
Select * from pop where population between 1300000000 and 140000000;
OR
Select * from pop where population>=1300000000 and population<=140000000;

b) Display maximum and minimum BirthRate for each UpdateDate.

Select UpdateDate, max(BirthRate), min(BirthRate) from pop group by updatedate;

c) Display number of countries updated on each update date.

Select UpdateDate, count(*) as "No. of Countries" from pop group by updatedate;

d) increase the birthrate of India by 2.0

Update pop set birthrate=birthrate+2.0 where country="India";

e) Display detail of the given table in ascending order of their population.

Select * from pop order by population asc;

Solution: Q.1- Program Code:

Creation of Dictionary and Data Frame:

import pandas as pd
import matplotlib.pyplot as plt
dict1={'Country':['China','India','United States','Indonesia','Brazil','Pakistan'],
'Population':[1379750000,1330780000,324882000,260581000,206918000,194754000],
'BirthRate':[12.00,21.76,13.21,18.84,18.43,27.62],
'dojoin':['2016-08-11','2016-08-11','2016-08-11','2016-01-07','2016-08-11','2016-08-11']}
df=pd.DataFrame(dict1)
print(df)

a) Display complete data for China and India.

print(df[(df.Country=='India')|(df.Country=='China')])
OR
print(df.loc[(df.Country=='India')|(df.Country=='China')])
b) Display Country, Population and BirthRate of Brazil and Pakistan.

print(df.loc[(df.Country=='Brazil')|(df.Country=='Pakistan'),['Country','Population','BirthRate']])

c) Create a CSV File from the avove data frame.

df.to_csv("Population.csv",index=False)
print("Data Transferred to csv file successfully.")

d) Retrieve data from above created csv file into a new dataframe df1 with columns Country and
Population only .

df1=pd.read_csv("Population.csv",usecols=['Country','Population'])
print(df1)

e) Now plot a bar chart depicting the Country on x-axis and their corresponding Population on y-axis,
with appropriate Graph title, x-axis title, y-axis title, gridlines and color etc.

x=df['Country']
y=df['Population']
plt.bar(x,y,color='g')
plt.xlabel('Country-->',fontsize=12,color='r')
plt.ylabel('Population(In Billion)->',fontsize=12,color='r')
plt.title('Country Wise Population Analysis', fontsize=14, color='g')
plt.grid()
plt.show()

Python Pandas and Data Visualization

Q.1- Write a program in python to create the following dataframe named “emp” storing the details
of employees:
ename job salary dojoin department
1001 Scott Manager 90000 2010-10-01 Accounts
1002 Tom Clerk 34000 2010-01-04 Admin
1003 Joy Clerk 32000 2009-03-01 Admin
1004 Sam Salesman 40000 2009-04-01 Sales
1005 Martin Manager 85000 2008-08-05 Sales
1006 Michel Salesman 43000 2008-08-06 Sales
1007 Francis Clerk 30000 2009-10-10 Accounts
Considering the above dataframe answer the following queries by writing appropriate
command in python pandas.
a) Add a new column named bonus ( just after salary column) which is 15% of their salary.
b)Add a row with row index 1008 as Robin,Analyst,60000,9000,2011-04-01,Admin.
c) Now change the salary of Francis as 35000.
d) Display the details of Sales and Accounts department .
e) Display the employee name, job and salary for all those employees whose salary lies
between 30000 and 50000.
f) Delete a column dojoin permanently.
g) Now plot a bar chart depicting the employee name on x-axis and their corresponding salary
on y-axis, with appropriate Graph title, x-axis title, y-axis title, gridlines and color etc.
 SQL Queries with Solution
Q.2- Consider the above table as "emp" and first column is 'empno' as primary key. Answer the
following sql queries.
a) Add a row with empno 1008 as Robin,Analyst,60000,2011-04-01,Admin.
Insert into emp values(1008,'Robin','Analyst',60000,'2011-04-01','Admin');
b) Display the detail of all employees whose salary lies in the range 50000 and 100000.
Select * from emp where salary between 50000 and 100000;
c) Display average salary and no. of employees of each department.
Select department, avg(salary), count(*) from emp group by department;
d) Display maximum salary of each type of job for Clerk and Manager only.
Select job,max(salary) from emp group by job having job in ('Clerk','Manager');
e) increase the salary of sales department employees by 5 percent.
Update emp set salary=salary+salary*0.05 where department='Sales';
f) Delete the column dojoin.
Alter table emp drop column dojoin;
g) Display detail of employees in descending order of their salary.
Select * from emp order by salary desc;

Solution: Q.1- Program Code:

import pandas as pd
import matplotlib.pyplot as plt
dict1={'ename':['Scott','Tom','Joy','Sam','Martin','Michel','Francis'],
'job':['Manager','Clerk','Clerk','Salesman','Manager','Salesman','Clerk'],
'salary':[90000,34000,32000,40000,85000,43000,30000],
'dojoin':['2010-10-01','2010-01-04','2009-03-01','2009-04-01','2008-08-05','2008-08-06','2009-10-10'],
'department':['Accounts','Admin','Admin','Sales','Sales','Sales','Accounts']}
emp=pd.DataFrame(dict1,index=[1001,1002,1003,1004,1005,1006,1007])
print(emp)

x=emp['salary']*0.15
# Add a new column named Bonus ( just after salary column) which is 15% of their salary.
emp.insert(3,'bonus',value=x)
print(emp)

# Add a row with row index 1008 as Robin,Analyst,60000,2011-04-01,Admin.

emp.loc[1008]=['Robin','Analyst',60000,9000,'2011-04-01','Admin']
print(emp)
# Now change the salary of Francis as 35000.
emp.loc[(emp.ename=='Francis'),'salary']=35000
print(emp)

#Display the details of Sales and Accounts department

print(emp.loc[(emp.department=='Accounts') | (emp.department=='Sales')])

# Display the employee name, job and salary for all those employees whose
# salary lies between 30000 and 50000.
print(emp.loc[(emp.salary>=30000) & (emp.salary<=50000),['ename','job','salary']])

# Delete a column dojoin permanently.

emp.drop('dojoin',axis=1,inplace=True)
print(emp)

#Now plot a bar chart depicting the employee name on x-axis and their corresponding
#salary on y-axis, with appropriate Graph title, x-axis title, y-axis title,
#gridlines and color etc.
x=emp['ename']
y=emp['salary']
plt.bar(x,y,color='r')
plt.xlabel('Name-->',fontsize=12,color='g')
plt.ylabel('Salary (Rs.)-->',fontsize=12,color='g')
plt.title('ABC PVT Limited\n Employees Salary analysis', fontsize=14, color='r')
plt.grid()
plt.show()

OUTPUT:

Python Pandas and Data Visualization

Q.1- Write a program in python to create the following dataframe named “temp” storing the
following details:
Cid City MaxTemp MinTemp Rainfall
0 C01 Delhi 40 32 24.1
1 C02 Bengaluru 31 25 36.2
2 C03 Chennai 35 27 40.8
3 C04 Mumbai 29 21 35.2
4 C05 Kolkata 39 23 41.8
5 C06 Allahabad 41 30 32.4
Considering the above dataframe answer the following queries by writing appropriate
command in python pandas.
a) Add a new column named Humidity as [54, 65,70,75,71,59].
b) Display MaxTemp, MinTemp of Delhi and Mumbai.
c) Create a CSV File from the above data frame named weather.csv.
d) Change the column name MaxTemp to MxT and MinTemp to MnT.
e) Now plot a multi-bar chart depicting the City Name on x-axis and their corresponding
MaxTemp and MinTemp on y-axis, with appropriate Graph title, x-axis title, y-axis title,
gridlines, legends and color etc.
OR
Handle the above options using a Menu driven program.
SQL Queries with Solution
Q.2- Create the table "weather" as given below, take data type and size yourself and insert
all records. Don't define primary key while creating table. Now define primary key on
the column Cid and answer the following sql queries.
Cid City MaxTemp MinTemp Rainfall
C01 Delhi 40.0 32.0 24.1
C02 Bengaluru 31.0 25.0 36.2
C03 Chennai 35.0 27.0 40.8
C04 Mumbai 29.0 21.0 35.2
 C05 Kolkata 39.0 23.0 41.8
C06 Allahabad 41.0 30.0 32.4

Solution SQL Commands:

Database creation:
Create database boardexam2021;
Use boardexam2021;

Table Creation:
Create table weather(Cid char(3) , City varchar(15),
MaxTemp double(3,1), MinTemp double(3,1),
Rainfall double(3,1));

Adding primary key:

Alter table weather add primary key(Cid);

Record Insertion:
Insert into weather values('C01','Delhi',40.0,32.0,24.1);
Insert into weather values('C02','Bengaluru',31.0,32.0,36.2);
Insert into weather values('C03','Chennai',35.0,27.0,40.8);
Insert into weather values('C04','Mumbai',29.0,21.0,35.2);
Insert into weather values('C05','Kolkata',39.0,23.0,41.8);
Insert into weather values('C06','Allahabad',41.0,30.0,32.4);

a) Add a column Zone and update this with values - North, South, South, West, East,
North.
Alter table weather add column Zone varchar(5);
Update weather set Zone='North' where Cid='C01';
Update weather set Zone='South' where Cid='C02';
Update weather set Zone='South' where Cid='C03';
Update weather set Zone='West' where Cid='C04';
Update weather set Zone='East' where Cid='C05';
Update weather set Zone='North' where Cid='C06';

b) Display Average of MaxTemp, MinTemp and Rainfall of each Zone.

Select Zone, Avg(MaxTemp), Avg(MinTemp), Avg(Rainfall) from weather group by Zone;

c) Display detail for Delhi and Mumbai.

Select * from weather whether City='Delhi' or City='Mumbai';

d) Display City and Rainfall whose rainfall is in the range 30 and 40.
Select City, Rainfall from weather where rainfall between 30 and 40;
Select City, Rainfall from weather where rainfall >=30 and rainfall<=40;

Solution: Q.1- Program Code:

# Creation of Dictionary and Data Frame:

import pandas as pd
import matplotlib.pyplot as plt
dict1={'Cid':['C01','C02','C03','C04','C05','C06'],
'City':['Delhi','Bengaluru','Chennai','Mumbai','Kolkata','Allahabad'],
'MaxTemp':[40,31,35,29,39,41],
'MinTemp':[32,31,27,21,23,30],
'Rainfall':[24.1,36.2,40.6,35.2,41.8,32.4]}
temp=pd.DataFrame(dict1)
print(temp)

#a) Add a new column named Humidity as [54, 65,70,75,71,59].

temp['Humidity']= [54, 65,70,75,71,59]

#or
temp.loc[:,'Humidity']= [54, 65,70,75,71,59]
#or
temp.at[:,'Humidity']= [54, 65,70,75,71,59]
#or
temp=temp.assign(Humidity= [54, 65,70,75,71,59])
#or
temp.insert(loc=5, column='Humidity', value= [54, 65,70,75,71,59])
print(temp)

#b) Display City, MaxTemp, MinTemp of Delhi and Mumbai.

print(temp.loc[(temp.City=='Delhi') | (temp.City=='Mumbai'),['City','MaxTemp','MinTemp']])
#or
print(temp.loc[(temp['City']=='Delhi') | (temp['City']=='Mumbai'),['City','MaxTemp','MinTemp']])

#c) Create a CSV File from the above data frame named weather.csv.
temp.to_csv("weather.csv",index=False)
print("Data Transferred to csv file successfully.")

#d) Change the column name MaxTemp to MxT and MinTemp to MnT.
temp=temp.rename(columns={'MaxTemp':'MxT', 'MinTemp':'MnT'})
print(temp)
# e) Now plot a multi-bar chart depicting the City Name on x-axis and their corresponding MaxTemp
and MinTemp # on y-axis, with appropriate Graph title, x-axis title, y-axis title, gridlines, legends and
color etc
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
city=temp['City']
y1=temp['MxT']
y2=temp['MnT']
X=np.arange(len(city))
plt.bar(city,y1,color='r',label='Max. Temp',width=0.25)
plt.bar(X+0.25,y2,color='b',label='Min. Temp',width=0.25)
plt.xlabel('City-->',fontsize=12,color='b')
plt.ylabel('Max. vs Min Temperature-->',fontsize=12,color='b')
plt.legend()
plt.title('Temperature Analysis', fontsize=14, color='r')
plt.grid()
plt.show()
 Python Pandas and Data Visualization
Q.1- Write a program in python to create the following dataframe named “emp” storing the details
of employees:
ename job salary dojoin department
1001 Scott Manager 90000 2010-10-01 Accounts
1002 Tom Clerk 34000 2010-01-04 Admin
1003 Joy Clerk 32000 2009-03-01 Admin
1004 Sam Salesman 40000 2009-04-01 Sales
1005 Martin Manager 85000 2008-08-05 Sales
1006 Michel Salesman 43000 2008-08-06 Sales
1007 Francis Clerk 30000 2009-10-10 Accounts
Write a menu based program to perform the following operations on columns of the above
defined dataframe based on user specified inputs:

The Menu Options are:

(a) Add a row at specified index at the bottom
(b) Insert a new column at the end
(c) Insert a new column at a specified position
(d) Display specific columns on a criteria.
(e) Display the dataframe.
(f) Delete a column permanantly.
g) Plot a Line chart between ename and salary

SQL Queries with Solution

Q.2- Consider the above table as "emp" and first column is 'empno' as primary key. Answer the
following sql queries.
a) Add a row with empno 1008 as Robin,Analyst,60000,2011-04-01,Admin.
Insert into emp values(1008,'Robin','Analyst',60000,'2011-04-01','Admin');
b) Display the detail of all employees whose are either Clerk or Manager.
Select * from emp where job='Clerk' or job='Manager';
c) Display average salary and no. of employees of each job type.
Select job, avg(salary),count(*) from emp group by job;
d) Display maximum salary of each department for Admin and Sales only.
Select department, max(salary) from emp group by department having department='Admin' or
department='Sales';
e) Decrease the salary of sales and Accounts department employees by 15 percent due to
Covid-19.
Update emp set salary=salary-salary*0.15 where department in ('Sales','Accounts');
f) Add a column City for all employees with default value as 'New Delhi'.
Alter table emp add city varchar(15) default 'New delhi';
g) Display detail of employees in Ascending order of their Date of join.
Select * from emp order by dojoin asc;
 Problem Definition : Consider
the data given below in the
table, Create a bar chart
depicting the downloads of
the app.

Coding-
Output –

Que) Consider the table STUDENT given below, write MySQL Commands .
TABLE: STUDENT
Name Class DOB Sex City Marks
Nanda X 1995-06-06 M Agra 451
Saurabh XII 1993-07-05 M Mumbai 462
Sanal XI 1994-05-06 F Delhi 400
Trisla XII 1995-08-08 F Mumbai 450
Steve XII 1995-10-08 M Delhi 369
Marisla XI 1994-12-12 F Dubai 250
Neha X 1995-12-08 F Moscow 377
Nishant X 1995-12-06 M Moscow 489

(i) To display all the information of males whose city is NEITHER Delhi or Mumbai.
select * from student where sex='M' and city not in('Delhi','Mumbai');
select * from student where sex='M' and city!='Delhi' and city!='Mumbai';
select * from student where sex='M' and not(city='Delhi' or city='Mumbai');

(ii) To display the details of all the students whose date of birth is after Nishant’s Birth date
1995-12-06. (consider the Sql’s standard date format)
select * from student where dob>'1995-12-06';

(iii) To Display all information about class XII students rankwise (descending order).
select * from student where class='XII' order by marks desc;
(iv) List names of all students whose name has the character ‘a’.
select name from student where name like '%a%';

(v) Display Name and Marks of those students whose marks is in the range 400 and 500 (both
are inclusive)
select name, marks from student where marks between 400 and 500;
select name, marks from student where marks>=400 and marks<=500;

Problem Definition : Given the

following data of rainfall in
North & South zones of India
in mm for 12 months.
Create multiple Bar charts in a
Figure to compare rail fall of
North and South zone from
Jan to Dec.
Coding -

Output -

Que) Consider the table STUDENT given below, write MySQL Commands. 7
TABLE: STUDENT

Name Class DOB Sex City Marks

Nanda X 1995-06-06 M Agra 451
Saurabh XII 1993-07-05 M Mumbai 462
Sanal XI 1994-05-06 F Delhi 400
Trisla XII 1995-08-08 F Mumbai 450
Steve XII 1995-10-08 M Delhi 369
Marisla XI 1994-12-12 F Dubai 250
Neha X 1995-12-08 F Moscow 377
Nishant X 1995-12-06 M Moscow 489

(i) Display average marks, highest marks and total no. of students for each class.
select class, avg(marks), max(marks), count(*) from student group by class;
(ii) Display total no of males and females separately, along with their average marks.
select sex, count(*), avg(marks) from student group by sex;
(iii) Increase the marks of 10th class students by 5% marks.
update student set marks=marks +(marks*0.05) where class='X';

(iv) Add a new column named Address. ( Take the data type and size yourself)
alter table student add address varchar(30);

(v) Add Primary key to the column Name.

alter table student add primary key(name);

Problem Definition : Write a

python code to create a Series object
Temp1 that stores temperature of
seven days in it, Take any random 7
values of temperatures and print this
Series. Now define indexes as 'Sunday',
'Monday'.......... Up to 'Saturday' and
print the Series again. Now print all
attributes of the series as well as
average temperature of 7 days.
Coding -

Output -

PROBLEM DEFINITION:
 Write a Python program to create a
DataFrame as given below, and display all
its attributes ( index, columns, shape,
size, axes, rows and columns.

Coding-

Output-
Q.1)

Problem Definition : Write a

python program to generate
line graph with suitable title
and labels. Where x is the year
of performance with values
2014, 2015, 2016, 2017,2018
and 2019. And y axis shows
the profit of a particular
company in Rs.(Millions).
Coding -

Output -

Que) Consider the table STUDENT given below, write MySQL Commands .
 TABLE: STUDENT
Name Class DOB Sex City Marks
Nanda X 1995-06-06 M Agra 474
Saurabh XII 1993-07-05 M Mumbai 462
Sanal XI 1994-05-06 F Delhi 400
Trisla XII 1995-08-08 F Mumbai 450
Steve XII 1995-10-08 M Delhi 369
Marisla XI 1994-12-12 F Dubai 250
Neha X 1995-12-08 F Moscow 396
Nishant X 1995-12-06 M Moscow 513

(i) Display name, class and city of 'XII' class students who lives in 'Mumbai'.
select Name, Class City from student where class=’XII’ and City=’Mumbai’;
(ii) Display name, data of birth of all females, whose marks is greater than or equal to 400.
select Name,DOB from student where Sex=’F’ and Marks>=400;
(iii) Display City and highest marks of students of each city, but only for Delhi and Mumbai.
select City, Max(Marks) from student group by City having City in(‘Delhi’, ‘ Mumbai’);
(iv) Display round of average marks up to zero places, truncate of average marks up to 1 decimal
place for class 'X' students only.
select round(avg(Marks),0) , truncate(avg(Marks),1) from student where Class=’X’;
(v) To display name and the position of character 'a' in the names of students having 'a' anywhere
in their names.
select name instr(name, ’a’) from student name like ‘%a%’;

Que) Consider the table pharma given below, write MySQL Commands.
Table pharma

RxID DrugID DrugName Price PharmacyName PharmacyLocation

(i) R1000 5476 Amlodipine 100.00 Rx Pharmacy Pitampura, Delhi To

R1001 2345 Paracetamol 15.00 Raj Medicos Bahadurgarh, Haryana
R1002 1236 Nebistar 60.00 MyChemist Rajouri Garden, Delhi
R1003 6512 VitaPlus 150.00 MyChemist Gurgaon,Haryana
R1004 5631 Levocitrezine 110.00 RxPharmacy South Extension,Delhi

increase the price of “Amlodipine” by 50.

update pharma set price=price+50 where Drugname like ‘Amlodipine’;
(ii) To display all those medicines whose price is in the range 100 to 150(both values inclusive).
select DurgName from pharma where price between 100 and 150;

(iii) To display the Maximum price offered by pharmacy located in “Gurgaon” .

select max(price) from pharma group by PharmacyLocation having PharmacyLocation like
‘Gurgaon’;

(iv) To display the Drug ID, DrugName and Pharmacy Name of all the records in descending order of
their price.
 select DurgID, DurgName, PharmacyName from pharma order by price desc;

Que) Consider the table STUDENT given below, write MySQL Commands .
TABLE: STUDENT
Name Class DOB Sex City Marks
Nanda X 1995-06-06 M Agra 474
Saurabh XII 1993-07-05 M Mumbai 462
Sanal XI 1994-05-06 F Delhi 400
Trisla XII 1995-08-08 F Mumbai 450
Steve XII 1995-10-08 M Delhi 369
Marisla XI 1994-12-12 F Dubai 250
Neha X 1995-12-08 F Moscow 396
Nishant X 1995-12-06 M Moscow 513

(i) Display name, character number 3rd and 4th of each name and counting of number of
characters in each name.
select Name substr(Name,3,2) ,length(name) from student;
(ii) Display current date and current time together.
select curdate() as “Current date” , curtime() as “Current time”;
(iii) Display name, marks and round up the Square root of marks up to 2 decimal places.
select Name , Marks , round (sqrt(Marks),2) from student;
(iv) Increase the marks of 10th class students by 5% marks.
update student set marks=marks +(marks*0.05) where class='X';
(v) Display all information about class XII students rank wise.
select * from student where Class=’XII’ order by Marks desc;