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

Quick Learn University is setting up its academic blocks at Prayag nagar and is planning to set up a network. The University has 3 academic blocks and one Human Resource Center as shown in the diagram below:

Center to Center distances between various blocks/center is as follows:

Law Block to business Block	40m
Law block to Technology Block	80m
Law Block to HR center	105m
Business Block to technology Block	30m
Business Block to HR Center	35m
Technology block to HR center	15m

Number of computers in each of the blocks/Center is as follows:

Law Block	15
Technology Block	40
HR center	115
Business Block	25

(a) Suggest the most suitable place (i.e., Block/Center) to install the server of this University with a suitable reason.

(b) Suggest an ideal layout for connecting these blocks/centers for a wired connectivity.

(c) Which device will you suggest to be placed/installed in each of these blocks/centers to efficiently connect all the computers within these blocks/centers.

(d) The university is planning to connect its admission office in the closest big city, which is more than 250km from university. Which type of network out of LAN, MAN, or WAN will be formed? Justify your answer.

How to Rename the Index or Columns of a Pandas DataFrame?

Write python program using function to find the sum of first n terms of the following series: x - x2 + x3 - . . . where n and x have to be input from the user. The function should have two parameter n and x and return the sum of the series.

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

Consider the following table Bank.

Table: Bank

AccNo	Cust_name	FD_Amount	Months	Int_Rate	FD_Date
1001	Arti Gupta	30000	36	6.00	2018-07-01
1002	Dilip Lal	50000	48	6.75	2018-03-22
1003	Navin Gupta	30000	36	NULL	2018-03-01
1004	D.P. Yadav	80000	60	8.25	2017-06-12
1005	Jyoti Sharma	20000	36	6.50	2017-01-31
1006	Rakesh Kumar	70000	60	8.25	2018-06-15
1007	K.D. Singh	50000	48	NULL	2018-07-05
1008	Anjali Sharma	60000	48	6.75	2017-04-02
1009	Swati Garg	40000	42	6.50	2018-06-15
1010	Rupinder Kaur	25000	36	6.50	2018-09-27

Write SQL commands for the statements (a) to (g) on the table BANK

(a) To create the table Bank (Primary Key: AccNo)

(b) Display the structure of the table Bank.

(c) Display the details of all the bank.

(d) Display the AccNo, Cust_Name, and FD_Amount.

(e) Display the details of all the FD’s having maturity time is less than 40 months.

(f) Display the AccNo and FD amount which started before 01-04-2018.

(g) Display details of all FD whose rate of interest are NULL.

Write a script to create a list of 10 random floating point numbers and then
(a) Find the mean of the elements present in the list (without using sum() function)
(b) Find the mean of all those elements of the list which are more than the mean calculated in 5c (above part of the question)

Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of tables FURNITURE and ARRIVALS

Table: FURNITURE

NO	ITEMNAME	TYPE	DATEOFSTOCK	PRICE	DISCOUNT
1	White lotus	Double Bed	23/02/2002	30000	25
2	Pink feather	Baby cot	20/01/2002	7000	20
3	Dolphin	Baby cot	19/02/2002	9500	20
4	Decent	Office Table	01/01/2002	25000	30
5	Comfort zone	Double Bed	12/01/2002	25000	25
6	Donald	Baby cot	24/02/2002	6500	15
7	Royal Finish	Office Table	20/02/2002	18000	30
8	Royal tiger	Sofa	22/02/2002	31000	30
9	Econo sitting	Sofa	13/12/2001	9500	25
10	Eating Paradise	Dining Table	19/02/2002	11500	25

Table: ARRIVALS

NO	ITEMNAME	TYPE	DATEOFSTOCK	PRICE	DISCOUNT
1	Wood Comfort	Double Bed	23/03/2003	25000	25
2	Old Fox	Sofa	20/02/2003	17000	20
3	Micky	Baby cot	21/02/2003	7500	15

(a) To show all information about the Baby cots from the FURNITURE table.

(b) To list the ITEMNAME which are priced at more than 15000 from the FURNITURE table.

(c) To list ITEMNAME and TYPE of those items, in which date of stock is before 22/01/2002 from the FURNITURE table in descending order of ITEMNAME.

(d) To display ITEMNAME and DATEOFSTOCK of those items, in which the discount t percentage is more than 25 from FURNITURE table.

(e) To count the number of items, whose TYPE is “Sofa ”from FURNITURE table.

(f) To insert a new row in the ARRIVALS table with the following data:

14,“Valvet touch ”, “Double bed ”, {25/03/03}, 25000,30

(g) Give the output of following SQL statement

Note: Outputs of the above mentioned queries should be based on original data given in both the tables i.e., without considering the insertion done in (f) part of this question.

(i) Select COUNT(distinct TYPE) from FURNITURE;

(ii) Select MAX(DISCOUNT) from FURNITURE, ARRIVALS;

(iii) Select AVG(DISCOUNT) from FURNITURE where TYPE=”Baby cot ”;

(iv) Select SUM(Price) from FURNITURE where DATEOFSTOCK <12/02/02;

Write the Pseudocode to sort the list using Selection sort – to sort a list A in ascending order

Write a program to create the list and ask user whether you want to add new element if say yes then add the element and print the new list else print the entered list.

Create the following table named "Charity "and write SQL queries for the tasks that follow:

Table: Charity

P_Id	LastName	FirstName	Address	City	Contribution
1	Bindra	Jaspreet	5B, Gomti Nagar	Lucknow	3500.50
2	Rana	Monica	21 A, Bandra	Mumbai	2768.00
3	Singh	Jatinder	8, Punjabi Bagh	Delhi	2000.50
4	Arora	Satinder	K/1, Shere Punjab Colony	Mumbai	1900.00
5	Krishnan	Vineeta	A-75, Adarsh Nagar

(i) Display all first names in lowercase.

(ii) Display all last names of people of Mumbai city in uppercase.

(iii) Display Person Id along with First 3 characters of his/her name.

(iv) Display first name concatenated with last name for all the employees.

(v) Display length of address along with Person Id.

(vi) Display last 2 characters of City and Person ID.

(vii) Display Last Names and First names of people who have "at "in the second or third position in their first names.

(viii) Display the position of 'a' in Last name in every row.

(ix) Display Last Name and First name of people who have "a "as the last character in their First names.

(x) Display the first name and last name concatenated after removing the leading and trailing blanks.

(xi) Display Person Id, last names and contribution rounded to the nearest rupee of all the persons.

(xii) Display Person Id, last name and contribution with decimal digits truncated of all the persons.

(xiii) Display Last name, contribution and a third column which has contribution divided by 10. Round it to two decimal points.