Subject: Informatics Practices (065)
Q1. Problem Solving using PANDAS & MATPLOTLIB [5 + 3 = 8]
a) Write the code to create a DataFrame ‘df’ and answer the questions followed. (5)
Maths Science SST
Amit 100 100.0 60.0
Mohan 95 50.0 57.48
Sudha 85 90.0 53.58
i) Write a command to add one column Total = Maths + Science + SST
ii) Write a command to add one row T5 with values 75.6, 98.6, 56.0
iii) Write a command to print Score of Maths and Science only.
iv) Write a command to update marks of Science of Sudha to 85.0
v) Write a command to delete a row - Mohan
b) Write a Python program to display the given Result using a BAR CHART (3)
Maths Science SST
Amit 100 100.0 60.0
Mohan 95 100.0 57.48
Sudha 85 100.0 53.58
(i) Set the title of graph is “Result Analysis”
(ii) Display the legends.
(iii) Display the label of x axis to “Name” and y axis to “Score”
Q2 SQL Queries: [7]
Write the commands in SQL for (i) to (vi) and output for (vii) and (viii).
(i) To list the names of items and their unit price that have unit price less than 800 and discount more than 5%.
(ii) To display the number of items that have more than 10% as discount.
(iii) To display item code and unit price in decreasing order of unit price.
(iv) To increase the unit price of each item by 10% of their unit price.
(v) To display the highest unit price of items.
(vi) To display the names of items that have ‘Baby’ anywhere in their item names.
Find Output :
(vii) SELECT MID (Item,1,2) FROM Infant;
(viii) SELECT AVG(UnitPrice) FROM Infant WHERE DATEPURCHASE ‘2015–01–01’;
Q3. Problem Solving using PYTHON [5 + 3 = 8]
a) Write the code to create the series ‘serObj’ and answer the questions followed. (5)
Jan 31
Feb 28
Mar 31
Apr 30
i) Write the command to add one row: ‘May’ – 31
ii) Write the command to update Feb to 29
iii) Write the command to change index to 1,2,3,4,5 in place of Jan, Feb, Mar, Apr and May.
iv) Write a command to print a month name having number of days less than 31.
v) Write the output:
(a) print(serObj < 30)
(b) print(serObj + 3)
b) Write a Python program to display a BAR CHART of the number of students in a school. (3)
(i) Use different colors for each bar.
(ii) Title for x axis should be ‘Groups’ and title for y axis should be ‘Number of Students’
(iii) Ensure the title of chart is “Group wise Students” and grid line must be shown.
Sample data: Group: I, II, III, IV and Strength: 38, 30, 45, 49
Q4 SQL Queries: [2+5=7]
a) Create a table DRUGDB with the fields given in below table and assuming data type of your own.
b) Consider the table DRUGDB. Write the SQL commands for queries given below:
(i) To increase the price of “Paracetamol” by 35.
(ii) To display the drugid, Rxid and pharmacy name of all records in descending order of their price.
(iii) Display all the details of the drugs where name starts with ‘C’ and has ‘sh’ somewhere in the name.
(iv) Display the drug name in lower case along with price rounded off to nearest interger.
(v) Delete the field name loc from drugdb table.
Informatics Practices – Solution
Q1. import pandas as pd
data = {‘Maths’: {‘Amit’:100, ‘Mohan’:95, ‘Sudha’:85},
‘Science’: {‘Amit’:100, ‘Mohan’:50, ‘Sudha’:90},
‘SST’: {‘Amit’:60, ‘Mohan’:57.48, ‘Sudha’:53.58},
}
df.pd.DataFrame(data)
print(df)
i) df[‘Total’] = df[‘Maths’] + df[‘Science’] + df[‘SST’]
print(df)
ii) df.loc[‘T5’, :] = [75.6, 98.6, 56.6, 230.8]
print(df)
iii) df[[‘Maths’, ‘Science’]]
iv) df.at[‘Sudha’, ‘Science’] = 85.0
v) df.df.drop(‘Mohan’)
B. import matplotlib.pyplot as plt
import numpy as np
Subject = [‘Maths’, ‘Science’, ‘S.St’]
Amit = [100, 100.0, 60.0]
Mohan = [95, 100.0, 57.48]
Sudha = [85, 100.0, 53.58]
x_axis = np.arange(len(Subject))
plt.bar(x_axis – 0.25, Amit, 0.25, label = ‘Amit’)
plt.bar(x_axis, Mohan, 0.25, label = ‘Mohan’)
plt.bar(x_axis + 0.25, Sudha, 0.25, label = ‘Sudha’)
plt.xticks(x_axis, Subject)
plt.legend(loc = 1)
plt.xlabel(“Name”)
plt.ylabel(“Score”)
plt.xlabel(“Result Analysis”)
plt. show()
Q2 SQL Queries
SELECT ITEM, UNITPRICE FROM INFANT
WHERE UNITPRICE < 800 AND DICSCOUNT > 5;
SELECT COUNT(*) FROM INFANT
WHERE DISCOUNT > 10;
SELECT ITEMCODE, UNITPRICE FROM INFANT
ORDER BY UNITPRICE DESC;
UPDATE INFANT SET UNITPRICE = UNITPRICE + UNITPRICE * 10/100;
SELECT MAX(UNITPRICE) FROM INFANT;
SELECT ITEM FROM INFANT
WHERE ITEM LIKE ‘%BABY%’;
FIND OUTPUT:
i. SELECT MID (Item, 1, 2) FROM infant;
ii. SELECT AVG(UnitPrice) FROM Infant WHERE DATEPURCHASE= ‘2015-01-01’;
Q3. 1. import pandas as pd
serObj = pd.Series([31, 28, 31, 30], index = [‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’])
print(serObj)
i. serObj[‘May’] = 31
ii. serObj[‘Feb’] = 29
iii. serObj.index = [1, 2, 3, 4, 5]
iv. serObj.index = [‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’, ‘May’]
serObj[serObj < 31].index
index([‘Feb’, ‘Apr’], dtype=’object’)
a) print(serObj < 30)
b) print(serObj + 3)
b) import matplotlib.pyplot as plt
Group = [‘I’, ‘II’, ‘III’, ‘IV’]
Strength = [38, 30, 45, 49]
plt.bar(Group, Strength, color=[‘red’, ‘green’, ‘blue’, ‘black’])
plt.xlabel(‘Group’)
plt.ylabel(‘Number of Students’)
plt.title(‘Group wise Students’)
plt.grid(True)
plt.show()
Q4 SQL queries
a) CREATE TABLE DRUGdb(
RxID CHAR(10) PRIMARY KEY,
DrugID INTEGER,
Drugname VARCHAR(30),
Prince DECIMAL(10, 2),
PharmacyName VARCHAR(40),
Loc VARCHAR(20)
b) SQL Commands
i) UPDATE DRUGDB SET price = price + 35 WHERE Drugname = ‘PARACETAMOL’;
ii) SELECT DRUGID, RXID, PHARMACYNAME
FROM DRUGDB
ORDER BY PRICE DECS;
iii) SELECT * FROM DRUGDB WHERE DRUGNAME LIKE ‘C%SH%’;
iv) SELECT LOWER(DRUGNAME0, ROUND(PRICE, 0) FROM DRUGDB;
v) ALTER TABLE DRUGDB DROP LOC;
