MS
Set A
Q1. Answer Key
Q1. Problem Solving using Python
Code to create the series serObj:
import pandas as pd
serObj = pd.Series([31, 28, 31, 30], index=['Jan', 'Feb',
'Mar', 'Apr'])
print(serObj)
i) Command to add one row: ‘May’ – 31:
serObj['May'] = 31
ii) Command to update Feb to 29:
serObj['Feb'] = 29
iii) Command to change index to 1, 2, 3, 4, 5:
serObj.index = [1, 2, 3, 4, 5]
iv) Command to print a month name having number of days less than 31:
print(serObj[serObj < 31])
v) Output:
a) print(serObj < 30):
1 False
2 True
3 False
4 True
5 False
dtype: bool
b) print(serObj + 3):
1 34
2 32
3 34
4 33
5 34
dtype: int64
Q2. Python Program to Display a Bar Chart
import matplotlib.pyplot as plt
# Data
groups = ['I', 'II', 'III', 'IV']
strength = [38, 30, 45, 49]
# Create bar chart
plt.bar(groups, strength, color=['red', 'blue', 'green', 'orange'])
# Titles and labels
plt.xlabel('Groups')
plt.ylabel('Number of Students')
plt.title('Group wise Students')
plt.grid(True)
# Display chart
plt.show()
Q3. SQL Queries
a) Create a table DRUGDB:
CREATE TABLE DRUGDB (
DrugID INT,
DrugName VARCHAR(50),
RxID INT,
PharmacyName VARCHAR(50),
Price DECIMAL(10, 2),
Loc VARCHAR(50)
);
b) SQL Queries:
(i) To increase the price of “Paracetamol” by 35:
UPDATE DRUGDB
SET Price = Price + 35
WHERE DrugName = 'Paracetamol';
(ii) To display the DrugID, RxID, and PharmacyName in descending order of their
price:
SELECT DrugID, RxID, PharmacyName
FROM DRUGDB
ORDER BY Price DESC;
(iii) Display all details of the drugs where name starts with ‘C’ and has ‘sh’
somewhere in the name:
SELECT *
FROM DRUGDB
WHERE DrugName LIKE 'C%sh%';
(iv) Display the drug name in lower case along with price rounded off to the nearest
integer:
SELECT LOWER(DrugName) AS DrugName, ROUND(Price) AS RoundedPrice
FROM DRUGDB;
(v) Delete the field Loc from the DRUGDB table:
ALTER TABLE DRUGDB
DROP COLUMN Loc;
Set B
Q1. import matplotlib.pyplot as plt
# Data
groups = ['I', 'II', 'III', 'IV']
strength = [38, 30, 45, 49]
plt.bar(groups, strength, color=['purple', 'green', 'blue', 'red'])
plt.xlabel('Groups')
plt.ylabel('Number of Students')
plt.title('Group wise Students')
plt.grid(True)
plt.show()
Q2. import pandas as pd
# Creating DataFrame
data = {
'Product Name': ['Moisturiser', 'Sanitizer', 'Bath Soap', 'Shampoo', 'Lens Solution',
'Bath Soap'],
'Manufacture': ['XYZ', 'LAC', 'COP', 'TAP', 'COP', 'TQR'],
'Price': [40, 35, 25, 95, 350, 500]
} df = pd.DataFrame(data)
# Adding a new column 'Quantity'
df.insert(1, 'Quantity', [10, 15, 20, 25, 5, 10])
# Minimum and Maximum price
print("Minimum Price:", df['Price'].min())
print("Maximum Price:", df['Price'].max())
# Replacing 'Lens Solution' with 'Perfume'
df['Product Name'] = df['Product Name'].replace('Lens Solution', 'Perfume')
# Save to CSV
df.to_csv('products.csv', index=False)
To list Vno, Vname, Age for all the voters sorted by Age:
SELECT Vno, Vname, Age
FROM VOTERS
ORDER BY Age;
To list all the voters where address is "Guwahati":
SELECT *
FROM VOTERS
WHERE Address = 'Guwahati';
Display Vname, Vno of those voters who have "N" at the start of their name:
SELECT Vname, Vno
FROM VOTERS
WHERE Vname LIKE 'N%';
To list voters where address is "Delhi" but age between 20 and 30:
SELECT *
FROM VOTERS
WHERE Address = 'Delhi' AND Age BETWEEN 20 AND 30;
To delete the records of all those voters who are either residing in "Delhi" or
whose Age > 35:
DELETE FROM VOTERS
WHERE Address = 'Delhi' OR Age > 35;
To change the age to 45 where Vname contains the word "Kumar":
UPDATE VOTERS
SET Age = 45
WHERE Vname LIKE '%Kumar%';
Display the sum of all the ages grouped by Address:
SELECT Address, SUM(Age) AS TotalAge
FROM VOTERS
GROUP BY Address;
Set C
Q1. import matplotlib.pyplot as plt
names = ['Raj', 'Ajay', 'Minu', 'Tina', 'Akash']
marks = [88, 99, 55, 66, 77]
plt.bar(names, marks, color=['blue', 'orange', 'green', 'red', 'purple'])
plt.xlabel('Names')
plt.ylabel('Marks')
plt.title('Student Marks')
plt.grid(True)
plt.show()
Q2. import pandas as pd
# Creating DataFrame
data = {
'Rno': [10, 20, 30, 40, 50],
'Name': ['Raj', 'Ajay', 'Minu', 'Tina', 'Akash'],
'Subject': ['Maths', 'Science', 'Maths', 'SST', 'SST'],
'Marks': [88, 99, 55, 66, 77]
}
df = pd.DataFrame(data)
# Adding a new column 'Grade'
df['Grade'] = ['A' if x > 90 else 'B' for x in df['Marks']]
# Changing index
df.index = ['I', 'II', 'III', 'IV', 'V']
# Updating last row's marks to 65
df.loc['V', 'Marks'] = 65
print(df)
Write a query to create the table:
CREATE TABLE PRODUCT (
Pid INT,
Pname VARCHAR(50),
Cname VARCHAR(50),
Price DECIMAL(10, 2),
Qty INT
);
Write a query to find the sum of all the product prices:
SELECT SUM(Price) AS TotalPrice
FROM PRODUCT;
Write a query to display the name of those customers who have
purchased Mobile and Laptop:
SELECT DISTINCT Cname
FROM PRODUCT
WHERE Pname IN ('Mobile', 'Laptop');
Write a query to change the price of Cooler from 5000 to 10000:
UPDATE PRODUCT
SET Price = 10000
WHERE Pname = 'Cooler' AND Price = 5000;
Display Cname, Pname of those customers who have "a" anywhere in
their name:
SELECT Cname, Pname
FROM PRODUCT
WHERE Cname LIKE '%a%';
Display Pid, Price from PRODUCT where Qty is more than 4:
SELECT Pid, Price
FROM PRODUCT
WHERE Qty > 4;
Write a query to display all the tuples:
SELECT *
FROM PRODUCT;
