Download App

Database Management – SQL — Computer Science STD 12 Science — Question

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Management – SQL4 Marks
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;

✓

Answer

(a) SELECT GAMENAME, GCODE FROM GAMES;

(b) SELECT * FROM GAMES WHERE PRIZE MONEY >7000;

(c) SELECT * FROM GAMES ORDER BY SCHEDULEDATE;

(d) SELECT NUMBER, SUM(PRIZEMONEY) FROM GAMES GROUP BY NUMBER;

(e1) COUNT (DISTINCT NUMBER)

2

4

(e2) MAX(ScheduleDate) MIN(ScheduleDate)
----------------- -----------------
19-Mar-2004 12-Dec-2003

(e3) SUM (Prize Money)

59000

(e4) DISTINCT (GCODE)

101

108

103

Need a full question paper?

Generate a complete, print-ready paper with questions like this in minutes — across 16+ boards, with answer keys.

Start Generating Free

Explore more

More "4 Marks Each" from Database Management – SQL→Database Management – SQL — all question types→Computer Science STD 12 Science question papers→CBSE Board STD 12 Science question papers→CBSE Board question paper generator→

Similar questions

Write a script to create a list of 10 random integers and display it. Then input an integer from the user and check whether it is present in the list (use in operator) and display an appropriate message.

→
Write a program that accepts sequence of lines as input and prints the lines after making all characters in the sentence capitalized.
Suppose the following input is supplied to the program:
We are on first line.
second line
third line in python.
Then, the output should be:
WE ARE ON FIRST LINE.
SECOND LINE
THIRD LINE IN PYTHON.
→

Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of this question:

Relation: WORKER

ECODE NAME DESIG PAYLEVEL DOJ DOB
11 Radhey Shyam Supervisor P001 13-Sep-2004 23-Aug-1981
12 Chander Nath Operator P003 22-Feb-2010 12-Jul-1987
13 Fizza Operator P003 14-June-2009 14-Oct-1983
15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984
18 Sanya Clerk P002 19-Dec-2005 09-June-1983

Relation: PAYLEVEL

PAYLEVEL PAY ALLOWANCE
P001 26000 12000
P002 22000 10000
P003 12000 6000

(a) Write SQL commands for the following statements:

(i) To display the details of all WORKERs in descending order of DOB.

(ii) To display NAME and DESIG of those WORKERs whose PLEVEL is either P001 or P002.

(iii) To display the content of all the WORKERs table, whose DOB is in between ’19-JAN- 1984 ’and ’18-JAN-1987 ’.

(iv) To add a new row with the following: 19, ‘Daya kishore ’, ‘Operator ’, ‘P003 ’, ’19-Jun-2008 ’, ’11-Jul-1984 ’

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

(i) Select count(plevel), plevel from worker group by plevel;

(ii) Select max(dob), min(doj) from worker;

(iii) Select name, pay from worker w, paylevel p where w.plevel=p.plevel and w.ecode <13;

(iv) Select plevel, pay+allowance from paylevel where plevel=’p003 ’;

→
Write algorithm for Delete operation in Queue.
→
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.

→
Write a python program to accept the list as parameter and print the prime numbers of the list.
→

Write a program which accepts a sequence of commaseparated numbers from console and generate a list and a tuple which contains every number.

Suppose the following input is supplied to the program:
23,45,67,78,89
Then, the output should be:
['23', '45', '67', '78', '89']
('23', '45', '67', '78', '89')

→
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.

→
WAP to generate 2n+1 lines of the following pattern on the computer screen:
→
Write algorithm for POP(Remove) operation in stack.
→