Data Handling Using Pandas – I — Computer Science STD 12 Science — Question

Write SQL commands for the statements (a) to (h) on the table HOSPITAL

(a) To insert a new row in the HOSPITAL table with the following data: 11,’ Kasif’, 37,’ENT’,’2018-02-25’, 300, ’M’.

(b) To set charges to NULL for all the patients in the Surgery department.

(c) To display patient details who are giving charges in the range 300 and 400 (both inclusive).

(d) To display the details of that patient whose name second character contains ‘a’.

(e) To display total charges of ENT Department.

(f) To display details of the patients who admitted in the year 2019.

(g) To display the structure of the table hospital.

(h) Write the command to create the above table.

Describe Data Operations in Pandas.

How can we convert a Series to DataFrame?

Assuming the given dataset answer the questions given below:

	animal	age	visits	priority
a	cat	2.5	1	yes
b	cat	3.0	3	yes
c	snake	0.5	2	No
d	dog	NaN	3	yes
e	dog	5.0	2	No
f	cat	2.0	3	No
g	snake	4.5	1	No
h	cat	NaN	1	yes
i	dog	7.0	2	No
j	dog	3.0	1	No

(a) To create the dataframe from the above dictionary and index is stored in label list.

(b) To display the Data frame.

(c) To calculate the sum of all visits (the total number of visits).

(d) Calculate the mean age for each different animal in df

(e) To Append a new row 'k' to df with your choice of values for each column.

(f) To delete the new entered row.

(g) To count the number of each type of animal in df.

(h) To sort df first by the values in the 'age' in descending order, then by the value in the 'visit' column in ascending order.

(i) To print the maximum values of each column.

(j) To display all the statistics.

(k) To sort the data according the index.

Answer the questions with reference to below data frame:

	class	order	max_speed
falcon	bird	Falconiformes	389.0
parrot	bird	Psittaciformes	24.0
lion	mammal	Carnivora	80.2
monkey	mammal	Primates	NaN
leopard	mammal	Carnivora	58.0

(i) To print the class wise sum.

(ii) To print the order wise mean

(iii) To sort the index.

(iv) To print the sum of the class wise along with orders columns indexing.

(v) To count the records.

Write the function Selection() to sort the list using Selection sort technique. The function will take the list as parameter.

WAP using function to insert new element in sorted list.

Consider the following tables CARHUB and CUSTOMER and answer (a) and (b) parts of this question:

Table: CARHUB

Vcode	VehicleName	Make	Color	Capacity	Charges
100	Innova	Toyota	WHITE	7	15
102	SX4	Suzuki	BLUE	4	14
104	C Class	Mercedes	RED	4	35
105	A-Star	Suzuki	WHITE	3	14
108	Indigo	Tata	SILVER	3	12

Table: CUSTOMER

CCode	CName	VCode
1	Hemant Sahu	101
2	Raj Lal	108
3	Feroza Shah	105
4	Ketan Dhal	104

(a) Write SQL commands for the following statements:

(i) To display the names of all white colored vehicles

(ii) To display name of vehicle, make and capacity of vehicles in ascending order of their sitting capacity

(iii) To display the highest charges at which a vehicle can be hired from CARHUB.

(iv) To display the customer name and the corresponding name of the vehicle hired by them.

(b) Give the output of the following SQL queries:

(i) Select count(distinct make) from cabhub;

(ii) Select max(charges), min(charges) from carhub;

(iii) Select count(*), make from carhub;

(Iv) Select vehiclename from carhub where capacity = 4;