Do the following Using Pandas and Matplotlib libraries in Python

1. Create the dataframe - expense

Jan Feb March
Drinks 450 560 400
Clothes 490 600 425
2. Find the total expenses on clothes and drinks by adding new column “tExpense”
3. Display the feb month expenses of clothes
4. Plot bar chart with title,axis labels and legend
2-
1. Create the following Data frame player from dictionary
Pid pname runs balls
0 1 virat kohli 78 24
1 2 kunal 34 12
1. Add new row for “ 5,Shikhar Dhawan “,78,48
2. Delete column balls
3. Create line chart having pname on x-axis and runs and balls on y axis.Give proper axis
label and chart title

3.

Subject Book title class Publisher Price

B01 Computer NCERT CS 12 NCERT 270
Science
B02 Computer Move fast 10 Dhanpat Rai 340
Applications with CS
B03 Informatics Sample 12 BPB 120
practices papers
B04 Artificial Question 9 KIPS 250
Intelligence Bank
B05 Artificial Sample 9 Oswal Books 300
Intelligence papers
1. Create Dataframe –“ books “ using dictionary
2. Display books for class 12
3. Delete row where bookid is B04
4. Plot book and price data on a line chart

4. Create the following dataframe-“ClassStrength” using List of Dictionaries.

A B C
1 11 22 33
2 51 26 81
1. Display details for sec A and C
3. Add column “total strength”( total_strength =A+B+C)
4. Plot bar graph for above dataframe and customize properly
5. Name Degree Score
0 Aparna MBA 90.0
1 Pankaj BCA NaN
3 Navesh NaN 78.0
4 Ram BCA 89.0
1. Create DataFrame “Selected”
2. display BCA Students details
3. Delete column Score
4. Display bar chart for following data:
Country=[“india”,”China”,”Pakistan”]
Medals=[60,90,25]
6.

Exercise Caloriesburnt Duration

0 Wall pilate 420 50

1 Cardio 380 40
2 Yoga and meditation 220 45
1. Create above dataframe using Series
2. Extract those detail where calorie burnt is more than 300
3. Delete column exercise
4. Add one more row of desired data

Do the following using SQL

Table:Training

No Name Age Department DOA Fee Gender

1 Piyush 24 Computer 22/02/01 120 M
2 Shivangi 21 History 24/02/03 200 F
3 Suman 34 History 23/01/20 300 F
4 Rohit 30 History 22/04/12 250 M
5 Yasha 22 computer 22/05/06 210 F
Table :Department
Department HOD qualification
Computer 1 Mtech
History 3 Phd
Physics null null

1. Show all information of HOD of History Department

2. List the first three letters of name and monthname of admission in the ascending order of
fee
3. Count the number of students with the age greater than 23
4. Display name of students in capital letters
5. Display total fee collected department wise
Table: studetail
Table:clas
Class Classteacher Subject taught
IX Ms Suman English
X Mr Ritesh Physics
XI Mr Afzal Biology
XII Ms Reshma Computer Science
1. Find the maximum marks of the student for each class.
2. To display total number of students with their class teacher name.
3. Display total number of male and female students
Table- Translator

id title type Date _of_translation translator

2 Your Trip translated 2022/12/13 Weng
5 Oranges translated 2021/2/27 Davies
6 Your Happy Life translated 2023/8/6 Green
7 Applied AI Not translated NULL Edwards
Table- Authors

id title first_name last_name

1 Time to Grow Up! Ellen Writer
2 Your Trip Yao Dou
5 Oranges Olga Savelieva
7 Applied AI Jack Smart
1. show book titles along with their authors(first name) and translation status of each
book
2. Display total number of books which are translated.
3. Display position of letter “o” in the firstname from table authors
Table –“Order”

Orderid Customerid Order Date

10308 2 1996/09/18
10309 37 1997/09/19
10310 77 1996/09/20
Table –“Customer”

Customerid CustomerName ContactName Country

37 Abbas Mirza Pakistan
77 Ana Maria Germany
77 Ana Antonio Mexico
2 Krishna Krish India

1. Display Orderid ,CustomerName and their month of order

2. Display all ContactName starting with “m” and belong to Pakistan
3. Display CustomerName,ContactName who ordered in year 1996

Table Garment

garmentcode Garment Size Export Date Payment Stock supid

name detail in hand
103 Shirt 42 2022-07-10 2300 45 11
104 Trouser 32 2020-09-15 null 50 12
105 Shorts 30 2021-04-10 2400 20 11
101 Skirt 30 2020-07-15 0 15 13
104 Trouser 28 2023-08-12 2456 10 14
Table supplier

Supid Supplier address

11 Steals Export Rohini delhi
12 Krishna Export Jogeswari,mumbai
13 Steals Export Chandpole,jaipur
14 North Sales Khela,pune
•
To count total number of garments supplied each supplier
•
To display garment name ,size ,stock and supplier name in descending order of their
stock
• Display monthname on which export has been done.
Table Customer
ID Name Age TransacDate Area Commission Gender
103 Kumkum 20 2020-07-10 North 2300 F
104 Vijay 25 2020-09-15 East null M
105 Garima 30 2020-04-10 North 2400 F
101 Hemant 24 2020-07-15 North 0 M
102 Manas 23 2020-06-14 East 1500 M
Table Account
ID Account installments loan Date Loan amt Payment
number detail
103 12456784 42 2020-07-10 450000 2300
104 12456456 32 2020-09-15 600000 null
105 12456567 30 2020-04-10 470000 2400
101 12456342 30 2020-07-15 250000 0

1. Display a report for all customers along with ID, Name, Month and loan date and account
number
2. Display total number of customer area wise if more than 2 customers is in that area
3. To display the detail female customers in descending order of their age