1.
Python Pandas Questions
(a) Create a DataFrame named ITEMS and display it.
import pandas as pd
# Creating DataFrame from the given data
data = {'Itemcat': ['Car', 'AC', 'Coller', 'Washing Machine', 'AC'],
'Itemname': ['Maruti', 'LG', 'Symphony', 'Samsung', 'Blue Star'],
'Cost': [700000, 43000, 8500, 29000, 22000]}
ITEMS = pd.DataFrame(data)
print(ITEMS)
i. Add a column named Stock.
ITEMS['Stock'] = [10, 15, 20, 8, 12] # Example stock data
print(ITEMS)
ii. Display the first three rows of the DataFrame.
print(ITEMS.head(3))
iii. Details of items with a cost less than ₹50,000.
print(ITEMS[ITEMS['Cost'] < 50000])
---
2. Chart (Matplotlib)
Subject-wise bar chart for given data.
import matplotlib.pyplot as plt
Subject = ['English', 'Hindi', 'Maths', 'Science', 'Computer']
Percentage = [91, 65, 77, 94, 87]
plt.bar(Subject, Percentage, color='skyblue')
plt.title('Subject-wise Performance')
plt.xlabel('Subjects')
plt.ylabel('Percentage')
plt.show()
---
3. SQL Queries for Student_Data Table
(a) Create the Student_Data table.
CREATE TABLE Student_Data (
StudentNo INT PRIMARY KEY,
Class INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Game VARCHAR(50),
G_Grade CHAR(1)
);
(b) Insert records into the table.
INSERT INTO Student_Data VALUES
(10, 7, 'Sameer', 'Cricket', 'B'),
(11, 8, 'Sujit', 'Tennis', 'A'),
(12, 7, 'Kamal', 'Swimming', 'B'),
(13, 7, 'Veena', 'Tannis', 'C'),
(14, 9, 'Archana', 'Basket Ball', 'A'),
(15, 10, 'Arpit', 'Cricket', 'A');
(c) Query to display the number of students in each class.
SELECT Class, COUNT(*) AS StudentCount FROM Student_Data GROUP BY Class;
(d) Query to display different games offered.
SELECT DISTINCT Game FROM Student_Data;
(e) Query to display names of students starting with 'S'.
SELECT Name FROM Student_Data WHERE Name LIKE 'S%';
(f) Add a new column Marks.
ALTER TABLE Student_Data ADD Marks INT;
---
4. Python Pandas for Student DataFrame
(a) Create and display the Student DataFrame.
data = {'StudentID': [101, 102, 103, 104],
'Name': ['Rahul', 'Priya', 'Anjali', 'Akash'],
'Grade': [10, 12, 11, 10],
'Marks': [85, 78, 91, 68],
'Attendance': [92, 88, 95, 80]}
Student = pd.DataFrame(data)
print(Student)
i. Display all students who scored more than 80 marks.
print(Student[Student['Marks'] > 80])
ii. Display the first three records of the DataFrame.
print(Student.head(3))
iii. Delete the last row of the DataFrame.
Student = Student[:-1]
print(Student)
---
5. Line Chart for Temperature Variations
Days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
Temperature = [30, 32, 31, 33, 35, 34, 36]
plt.plot(Days, Temperature, marker='o', color='red', linestyle='--')
plt.title('Temperature Variations During the Week')
plt.xlabel('Days')
plt.ylabel('Temperature (°C)')
plt.show()
---
6. SQL Queries for Employee Table
(a) Create the Employee table.
CREATE TABLE Employee (
Empno INT PRIMARY KEY,
Ename VARCHAR(50),
Job VARCHAR(50),
Salary INT,
Dojoin DATE,
Department VARCHAR(50)
);
(b) Insert records into the table.
INSERT INTO Employee VALUES
(1001, 'Scott', 'Manager', 90000, '2010-10-01', 'Accounts'),
(1002, 'Tom', 'Clerk', 34000, '2010-01-04', 'Admin'),
(1003, 'Joy', 'Clerk', 32000, '2009-03-01', 'Admin'),
(1004, 'Sam', 'Salesman', 40000, '2009-04-01', 'Sales'),
(1005, 'Martin', 'Manager', 85000, '2008-08-05', 'Sales');
(c) Display the average salary of each department.
SELECT Department, AVG(Salary) AS AvgSalary FROM Employee GROUP BY
Department;
(d) Display the maximum salary for Clerk and Manager.
SELECT Job, MAX(Salary) AS MaxSalary FROM Employee
WHERE Job IN ('Clerk', 'Manager') GROUP BY Job;
(e) Display employees in descending order of salary.
SELECT * FROM Employee ORDER BY Salary DESC;
(f) Change the job of all employees in the "Admin" department to "Assistant".
UPDATE Employee SET Job = 'Assistant' WHERE Department = 'Admin';
