Scribd
Upload
15 views7 pages

Marking scheme practical paper (2)

The document contains programming exercises and SQL queries related to data manipulation using Python and SQL. It includes examples of creating and modifying data structures with pandas, generating bar charts with matplotlib, and performing various SQL operations like creating tables, updating records, and querying data. The content is organized into sets with distinct questions and code snippets for each task.

Uploaded by

kushagrajatthappvt
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
15 views7 pages

Marking scheme practical paper (2)

The document contains programming exercises and SQL queries related to data manipulation using Python and SQL. It includes examples of creating and modifying data structures with pandas, generating bar charts with matplotlib, and performing various SQL operations like creating tables, updating records, and querying data. The content is organized into sets with distinct questions and code snippets for each task.

Uploaded by

kushagrajatthappvt
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

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;

You might also like