QUESTION 1: PYTHON PROGRAMMING (PANDAS AND MATPLOTLIB) (5+3=8)
Context: A school maintains a record of student performance across three terms. The data includes Student
ID, Student Name, Subject, and Marks obtained in each subject.
Dataset: Consider a CSV file named `student_marks.csv` containing the following data:
StudentID StudentName Subject Term1 Term2 Term3
101 Rajesh Kumar Mathematics 85 88 90
101 Rajesh Kumar English 78 82 85
101 Rajesh Kumar Science 92 94 96
102 Priya Singh Mathematics 90 92 94
102 Priya Singh English 88 90 92
102 Priya Singh Science 85 87 89
103 Arjun Patel Mathematics 75 78 82
103 Arjun Patel English 80 83 85
103 Arjun Patel Science 88 90 92
104 Neha Gupta Mathematics 95 96 98
104 Neha Gupta English 92 94 96
104 Neha Gupta Science 90 92 94
Questions:
Part A: Data Manipulation using Pandas (5 marks)
(a) Write a Python program to read the CSV file `student_marks.csv` and create a DataFrame. Display the
first 5 rows of the DataFrame with proper formatting.
(b) Calculate the average marks for each student across all three terms and all subjects. Display the results
sorted in descending order of average marks.
(c) Write a program to find:
(i) The student who secured the highest average marks
(ii) The student who secured the lowest average marks
(iii) The subject in which the overall average marks is highest
(d) Filter and display all records where the average marks of a student across all subjects (considering
average of Term1, Term2, and Term3) is greater than 85. *(OR)* Create a new column "Performance
Level" and assign values as:
- "Excellent" if average > 90
- "Good" if average between 80-90
- "Average" if average between 70-80
- "Need Improvement" if average < 70
Part B: Data Visualization using Matplotlib (3 marks)
(e) Create an appropriate visualization for the following:
Option 1: A bar chart showing the average marks of each student across all subjects and terms.
Option 2: A line graph showing the performance trend (Term1, Term2, Term3) for each student.
Option 3: A pie chart showing the subject-wise distribution of total marks obtained by all students.
QUESTION 2: SQL QUERIES [7 marks]
Context: A retail company maintains customer and sales data in a MySQL database.
Table 1: Customer
Table 2: Product
Table 3: Sales
Questions:
(a) Write a query to display the names and email addresses of all customers from Delhi. [1 mark]
(b) Write a query to find the total sales amount for each product. Display Product Name and Total Sales
Amount in descending order. [1 mark]
(c) Write a query to display the customer name, product name, and amount for all sales. Use appropriate
joins to combine data from all three tables. [1.5 marks]
(d) Write a query to find customers who have purchased products from the "Electronics" category. Display
customer name and product name. [1.5 marks]
(e) Write a query to find the product(s) with the maximum stock quantity. Display Product Name and Stock
Quantity.
(OR)
Write a query to calculate the average sales amount and display only those products whose total sales
amount is greater than the average. [1 mark]
(f) Write a query to add a new column "Discount" (FLOAT data type) to the Product table. [1 mark]