Functions — Computer Science STD 12 Science — Question

Answer the following questions:

Students

Adm No	Name	Class	Sec	R No.	Address	Phone
1271	Utkarsh Madam	12	C	1	C-32, Punjabi Bagh	4356154
1324	Naresh Sharma	10	A	1	31, Mohan Nagar	435654
1325	Md. Yusuf	10	A	2	12/12, Chand Nagar	145654
1328	Sumedha	10	B	23	59, Moti Nagar	4135654
1364	Subya Akhtar	11	B	13	12, Janak Puri	Null
1434	Varuna	12	B	21	69, Rohini	Null
1461	David DSouza	11	B	1	D-34, Model Town	243554. 98787665
2324	Satinder Singh	12	C	1	1/2, Gulmohar Park	143654
2328	Peter Jones	10	A	18	21/328, Vishal Enclave	24356154
2371	Mohini Mehta	11	C	12	37, Raja Garden	435654, 6765787

Sports

Adm No	Game	CoachName	Grade
1324	Cricket	Narendra	A
1326	Volleball	M.P. Singh	A
1271	Volleball	M.P. Singh	B
1434	Basket Ball	I. Malhotra	B
1461	Cricket	Narendra	B
2328	Basket Ball	I. Malhotra	A
2371	Basket Ball	I. Malhotra	A
1271	Basket Ball	I. Malhotra	A
1434	Cricket	Narendra	A
2328	Cricket	Narendra	B
1364	Basket Ball	I. Malhotra	B

(a) Based on these tables write SQL statements for the following queries:

(i) Display the lowest and the highest classes from the table STUDENTS.

(ii) Display the number of students in each class from the table STUDENTS.

(iii) Display the number of students in class 10.

(iv) Display details of the students of Cricket team.

(v) Display the Admission number, name, class, section, and roll number of the students whose grade in Sports table is 'A'.

(vi) Display the name and phone number of the students of class 12 who are play some game.

(vii) Display the Number of students with each coach.

(viii) Display the names and phone numbers of the students whose grade is 'A' and whose coach is Narendra.

(b) Identify the Foreign Keys (if any) of these tables. Justify your choices.

(c) Predict the output of each of the following SQL statements, and then verify the output by actually entering these statements:

(i) SELECT class, sec, count(*) FROM students GROUP BY class, sec;

(ii) SELECT Game, COUNT(*) FROM Sports GROUP BY Game;]

(iii) SELECT Game FROM students, Sports WHERE students.admno = sports.admno AND Students.AdmNo = 1434;

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

Answer the (a) and (b) on the basis of the following tables STORE and ITEM:

Table: STORE

SNo	SName	AREA
S01	ABC Computronics	GK II
S02	All Infotech Media	CP
S03	Tech Shoppe	Nehru Place
S05	Hitech Tech Store	CP

Table: ITEM

INo	IName	Price	SNo
T01	Mother Board	12000	S01
T02	Hard Disk	5000	S01
T03	Keyboard	500	S02
T04	Mouse	300	S01
T05	Mother Board	13000	S02
T06	Key Board	400	S03
T07	LCD	6000	S04
T08	LCD	5500	S05
T09	Mouse	350	S05
T10	Hard disk	4500	S03

(a) Write the SQL queries (1 to 4):

(i) To display IName and Price of all the items in the ascending order of their Price.

(ii) To display the SNo and SName or all stores located in CP.

(iii) To display the minimum and maximum price of each IName from the table Item.

(iv) To display the IName, price of all items and their respective SName where they are available.

(b) Write the output of the following SQL commands (i) to (iv):

(i) Select distinct iname from item where price >= 5000;

(ii) Select area, count(*) from store group by area;

(iii) Select count(distinct area) from store;

(iv) Select iname, price*0.05 discount from item where sno in (‘s02 ’, ‘s03 ’);

Write a function addrecord() a add new record to the binary file “student” using list. The list should consist of student number, student name and marks of the student.

Write a python program to create a list of numeric values and sort the list using Insertion Sort Technique.

Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e1) to (e4)

Relation: GAMES

GCode	Game Name	Number	Prize Money	Schedule Date
101	Carom Board	2	5000	23-Jan-2004
102	Badminton	2	12000	12-Dec-2003
103	Table Tennis	4	8000	14-Feb-2004
105	Chess	2	9000	01-Jan-2004
108	Lawn Tennis	4	25000	19-Mar-2004

Relation: PLAYER

PCode	Name	Gcode
1	Nabi Ahmad	101
2	Ravi Sahai	108
3	Jatin	101
4	Nazneen	103

(a) To display the name of all Games with their Gcodes

(b) To display details of those games which are having PrizeMoney more than 7000.

(c) To display the content of the GAMES table in ascending order of ScheduleDate.

(d) To display sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number)

(e1) Select COUNT(DISTINCT Number) FROM GAMES;

(e2) Select MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;

(e3) Select SUM(PrizeMoney) FROM GAMES;

(e4) Select DISTINCT Gcode FROM PLAYER;

Write a python program using function to accept a list as parameter and print all the even number in the list.

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)

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.

Describe Data Operations in Pandas.