SET A
ANS. 1 A) import pandas as pd [5]
# Creating the DataFrame
data = {
"S_ID": [1, 2, 3, 4],
"Name": ["Alok Kumar", "Ritika Sharma", "Nitin Verma", "Priya Saxena"],
"Subject1": [85, 92, 75, 89],
"Subject2": [78, 88, 82, 91]
}
students = pd.DataFrame(data)
# i) Add marks of Subject1 and Subject2 and assign them to the column Total_Marks
students["Total_Marks"] = students["Subject1"] + students["Subject2"]
# ii) Display the average marks of each student across Subject1 and Subject2
students["Average_Marks"] = students["Total_Marks"] / 2
# iii) Display the details of the student with the highest Total_Marks
highest_marks_student = students[students["Total_Marks"] == students["Total_Marks"].max()]
# iv) Print details of Priya Saxena
priya_saxena_details = students[students["Name"] == "Priya Saxena"]
# Display the outputs
print("Updated DataFrame:\n", students)
print("\nStudent with the Highest Total Marks:\n", highest_marks_student)
print("\nDetails of Priya Saxena:\n", priya_saxena_details)
ANS. B) [3]
import matplotlib.pyplot as plt
# Data for the bar chart
categories = ["Science", "Commerce", "Arts"]
students = [120, 80, 60]
# Creating the bar chart
plt.bar(categories, students, color=["blue", "orange", "green"])
1
# Adding labels and title
plt.xlabel("Streams")
plt.ylabel("Number of Students")
plt.title("Students Enrolled per Stream")
# Adding grid lines
plt.grid(True)
# Display the chart
plt.show()
ANS 2 A. SQL Commands [5]
1. Display titles of all books priced below 400:
SELECT Title FROM LIBRARY WHERE Price < 400;
2. Display Title and Category of books sorted alphabetically by the title:
SELECT Title, Category FROM LIBRARY ORDER BY Title ASC;
3. Find the average price of all books in the "Self-Help" category:
SELECT AVG(Price) AS Average_Price FROM LIBRARY WHERE Category = 'Self-
Help';
4. Update the price of all "Fiction" books by increasing it by 10%:
UPDATE LIBRARY SET Price = Price * 1.10 WHERE Category = 'Fiction';
5. Display Title and Author for books where the title contains "Python" or price > 400:
SELECT Title, Author FROM LIBRARY WHERE Title LIKE '%Python%' OR Price > 400;
ANS6. SQL Outputs [2]
1. SELECT LENGTH(Title) FROM LIBRARY;
13 (The Alchemist)
18 (Think Like a Monk)
20 (Python Programming)
13 (Atomic Habits)
2
2. SELECT SUBSTRING(Title, 1, 5) FROM LIBRARY;
The A
Think
Pytho
Atomi
3. SELECT ROUND(AVG(Price), 2) FROM LIBRARY WHERE Category = 'Self-Help';
400.00
4. SELECT MAX(Price), Category FROM LIBRARY GROUP BY Category;
330 Fiction
450 Self-Help
650 Programming
—-----------------------------------------------------------------------------------------
SET -B
ANS. 1 A) [5]
import pandas as pd
# Creating the DataFrame
data = {
"Emp_ID": [1, 2, 3, 4],
"Name": ["Rohan Sharma", "Sneha Verma", "Karan Gupta", "Priya Malhotra"],
"Dept": ["HR", "IT", "Finance", "Marketing"],
"Salary1": [50000, 75000, 64000, 58000],
"Salary2": [52000, 77000, 66000, 60000]
}
employees = pd.DataFrame(data)
# Calculate the average salary
employees["Average_Salary"] = (employees["Salary1"] + employees["Salary2"]) / 2
print("DataFrame with Average Salary:\n", employees)
# Calculate the percentage hike
employees["Salary_Hike"] = ((employees["Salary2"] - employees["Salary1"]) /
employees["Salary1"]) * 100
print("DataFrame with Salary Hike:\n", employees)
# Filter employees in the Finance department
3
finance_employees = employees[employees["Dept"] == "Finance"]
print("Employees in Finance Department:\n", finance_employees)
# Filter employees with Average_Salary > 60,000
high_avg_salary = employees[employees["Average_Salary"] > 60000]
print("Employees with Average Salary greater than 60,000:\n", high_avg_salary)
—-----------------------------------------------------------------------------------------
ANS.1 B) [3]
import matplotlib.pyplot as plt
# Data for the line chart
months = ["January", "February", "March", "April"]
profits = [200000, 250000, 300000, 350000]
# Creating the line chart
plt.plot(months, profits, marker='o', color='blue', linestyle='-', linewidth=2)
# Adding labels and title
plt.xlabel("Months")
plt.ylabel("Profits (in INR)")
plt.title("Monthly Profits")
# Adding grid lines
plt.grid(True)
# Display the chart
plt.show()
—------------------------------------------------------------------------------------------------
ANS.2 [5]
I) SELECT Name FROM EMPLOYEES WHERE Experience > 7;
—------------------------------------------------------------------------------------------------
2) SELECT Name, Salary FROM EMPLOYEES ORDER BY Experience DESC;
—------------------------------------------------------------------------------------------------
3) SELECT AVG(Salary) AS Average_Salary FROM EMPLOYEES WHERE
Department = 'IT';
4
—------------------------------------------------------------------------------------------------
4) UPDATE EMPLOYEES
SET Salary = Salary * 1.08
WHERE Department = 'Finance';
—------------------------------------------------------------------------------------------------
5) SELECT Name, Experience FROM EMPLOYEES
WHERE Department = 'HR' OR Salary > 80000;
—------------------------------------------------------------------------------------------------
ANS6. SQL Outputs [2]
I) UPPER(Name)
-----------------
ADITI SHARMA
RAHUL VERMA
SNEHA MALHOTRA
KARAN MEHTA
—------------------------------------------------------------------------------------------------
2) Full_Details
-------------------------
Aditi Sharma (HR)
Rahul Verma (IT)
Sneha Malhotra (Finance)
Karan Mehta (IT)
—------------------------------------------------------------------------------------------------
3) Total_Employees
-----------------
3
—------------------------------------------------------------------------------------------------
4) Lowest_Salary Department
---------------------------
60000 HR
75000 Finance
85000 IT
—------------------------------------------------------------------------------------------------
5
6
