Data Structure — Computer Science STD 12 Science — Question

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 function readall() the record from the binary file “student” and display all the records

Aarti is new in python data-handling. Please help her to count the number of lines which begins with ‘W’ or ‘w’ in poem.txt.

(a) # Line 1 : To open file POEM.txt in read mode

(b) # Line 2 : To check first character of every line is ‘W’ or ‘w’.

(c) # Line 3 : To increase the value of count by 1.

(d) # Line 4 : To call the function count_poem.

Answer the questions related to pandas Series which will perform:

(i) To import the pandas

(ii) To create the series from the list

(iii) To print List.

(iv) To print the index of the series.

(v) To print the values of the series.

Write python program using function to find the sum of first n terms of the following series:

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 Push(Book) and Pop(Book) methods in Python to Add a new Book and Remove a Book from a List of Books, considering them to act as PUSH and POP
operations of the data structure Stack.

Write a python program using function to find greater number between two numbers. The function should take 2 numbers as parameters.

WAP to generate n lines of the following pattern on the computer screen: 1
12
123
1234

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 a program that reads a line and a substring. It should then display the number of the occurrence of the given substring in the line.