Download App

Database Query Using–SQL — Computer Science STD 12 Science — Question

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Query Using–SQL5 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 3-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

Get the step-by-step solution for this question inside the Vidyadip app.

Get the answer in the app

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 "5 Marks Each" from Database Query Using–SQL→Database Query Using–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

Mr. Sharma is working on pandas series and he is facing some problem while working on Series. Help him with few concepts by answering the given question:
He has created the series:

(a) He wanted the given output, which command will be appropriate to do the sam(e)

(b) Which command will give him the output as 5.
(i) s.index
(ii) s.size
(iii) s.values
(iv) None of the mentioned
(c) Assuming the changed structure, answer the question.

Which command will display the given output?
(i) s.head (2) (ii) s.top(2)
(iii) s.head() (iv) s.first(2)
(d) Choose the write output for the given command:
>>>print(stud1.count())
(i) 4 (ii) 3
(iii) 5 (iv) Error
(e) What is the name of the above Series?
(i) Student (ii) s
(iii) int64 (iv) float64
→
You are a student in CBSE School. Teacher has given a task to write a python code to perform the following binary file operations with the help of two user defined functions/modules:
(a)
→
On the basis of the given data set, answer the questions below:

ExpenditureData={'Country': ['Brazil', 'India', 'United Kingdom', 'Nigeria', 'China','Pakistan'],

'Expenditure': [785300000, 530780000, 694582000, 130581000, 407191800, 4754000],

'GrowthRate': [15, 18.6, 13.79,14.87,20.86, 12.54]

'UpdateDate': ['2016-03', '2016-08', '2016-02', '2016-01','2016-09','2016-05']}

Country Expenditure GrowthRate UpdateDate
0 Brazil 785300000 15.00 2016-03
1 India 530780000 18.60 2016-08
2 United Kingdom 694582000 13.79 2016-02
3 Nigeria 130581000 14.87 2016-01
4 China 407191800 20.86 2016-09
5 Pakistan 4754000 12.54 2016-05

(i) Write the command to create the data frame from the given lists:

(ii) To display the complete data frame.

(iii) To display the column country.

(iv) To display the following columns 'Country', 'Expenditure', 'UpdateDate'.

(v) To display the first and second record.

(vi) To change the index to the column 'country'.

(vii) To display the records of 'India','United Kingdom'

(viii) To display the records of the countries whose growth rate is greater than 18.

(ix) To display the Expenditure and update data whose growth rate is greater than 18.

(x) To display the Expenditure and update data whose expenditure is 694582000.

(xi) To add the new column GDP_Growth_rate.

(xii) Write the output:

(a) data1.iloc[2:5]

(b) data1.iloc[2:5,1:2]

→
Krishna of class 12 is writing a program to read the details of Sports performance and store in the csv file “games.csv” delimited with a tab character. As a programmer, help him to achieve the task.

(a)Line 1 : Name the module he should import.

(b)Line 2 : To create an object to enable to write in the csv file.

(c)Line 3 : To store the data in list

(d)Line 4 : To write a record.

(e)Line 5 : To close the file.

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


[Delhi 2011]

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:

→
Consider the following table Student & Stream.

Table: Student

Admno Sname Class Sec Fee Mobile Area S_ID
1001 RAMESH XII A 2500 987654321 Madipur 10
1078 KRISHNA XII B 2400 999911111 Jawala Heri 30
1006 FARDEEN XII C 2600 987654321 Paschim Puri 40
1004 SUBHAM XII A 2500 963025874 Madipur 20
1029 KRITIKA XI C 2700 987456210 Madipur 30
1008 SAMEEKSHA XII A 2450 987123456 Mangol Puri 20
1025 SALMA XII B 2580 998877445 Madipur 30
1036 AMANDEEP XII B 2600 999333555 Khyala 40
1037 TEJAS XI C 2650 987951357 Paschim Puri 40
1029 HIMANSHU XII A 2750 951369874 Jawala Heri 10

Table : Stream

S_ID Stream_name
10 MEDILCAL
20 NON MEDICAL
30 COMMERCE WITH MATH
40 COMMERCE WITH IP
50 HUMANITIES

Write SQL commands for the statements (a) to (h) on the table: Student and Stream

(a) Display class and total fee collected from each class.

(b) Display admission no, students name and stream name.

(c) Display all the student details who have taken Commerce Stream.

(d) Count number of students who have opted for HUMANITIES stream.

(e) Display information of commerce with ip students whose name start with ‘S’. Arrange the record by admission number.

(f) Display details of all students who are in the MEDICAL stream.

(g) Display total fee of ‘Non-Medical’ Student.

(h) Change the name of the Column Sname to Student_Name.

→
Software Development Company has set up its new center at Raipur for its office and web-based activities. It has 4 blocks of buildings named Block A, Block B, Block C, Block (d).

Block Number of Computers
A 25
B 50
C 125
D 10

Shortest distances between various Blocks in meters:

Block A to Block B 60m
Block B to Block C 40m
Block C to Block A 30m
Block D to Block C 50m

(a) Suggest most suitable place to store server of this company.

(i) Block A (ii) Block B

(iii) Block C (iv) Block D

(b) Suggest the topology within the blocks.

(i) Bus (ii) Ring

(iii) Tree (iv) Star

(c) Suggest the placement of the Router in the network.

(i) Block A (ii) Block B

(iii) Block C (iv) Block D

(d) The company wants to design a website for its customers that can be changing the contents as per the responses of customers. What type of website can they design for this purpose?

(i) Static Website (ii) HTML website

(iii) Dynamic Website (iv) Blog

(e) What type of network would be formed if the Raipur office is connected to their New Delhi office?

(i) LAN (ii) PAN

(iii) MAN (iv) WAN

→
Perfect Edu Services Ltd. is an educational organization. It is planning to setup its India campus at Chennai with its head office at Delhi. The Chennai campus has 4 main buildings – ADMIN,ENGINEERING, BUSINESS and MEDIA

Shortest distances between various buildings:

ADMIN to ENGINEERING55 m
ADMIN to BUSINESS90 m
ADMIN to MEDIA50 m
ENGINEERING to BUSINESS55 m
ENGINEERING to MEDIA50 m
BUSINESS to MEDIA45 m
DELHI Head Office to CHENNAICampus2175 km

Number of Computers installed at various buildings are as follows:

ADMIN110
ENGINEERING75
BUSINESS40
MEDIA12
DELHI Head Office20

(a) Suggest the most appropriate location of the server inside the CHENNAI campus (out of the 4 buildings), to get the best connectivity for maximum no. of computers. Justify your answer.

(b) Suggest and draw the cable layout to efficiently connect various buildings within the CHENNAI campus for connecting the computers.

(c) Which hardware device will you suggest to be procured by the company to be installed to protect and control the internet uses within the campus?

(d) Which of the following will you suggest to establish the online face-to-face communication between the people in the Admin Office of CHENNAI campus and DELHI Head Office?

(i) Cable TV

(ii) Email

(iii) Video Conferencing

(iv) Text Chat

→
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables: [Delhi 2018(C)]

TABLE: SALESPERSON

CODE NAME SALARY ITCODE
1001 TANDEEP JHA 60000 I2
1002 YOGRAJ SINHA 70000 I5
1003 TENZIN JACK 45000 I2
1005 ANOKHI RAJ 50000 I7
1004 TARANA SEN 55000 I7

TABLE: ITEM

ITCODE ITEMTYPE TURNOVER
I5 STATIONARY 3400000
I7 HOISTERY 6500000
I2 BAKERY 10090000

(a) To display the CODE and NAME of all salesperson having “I7” item Type Code from the table SALESPERSON.

(b) To display all details from table SALESPERSON in descending order of SALARY.

(c) To display the number of SALESPERSON dealing in each TYPE of ITEM.(Use ITCODE for the same)

(d) To display NAME of all the salesperson table along with their corresponding ITEMTYPE from the ITEM table.

(e) Select max(salary) from salesperson;

(f) Select distinct itcode from salesperson;

(g) select code,name,i.itcode from sales person s, item i where s.itcode=i.itcode and turnover>=7000000;

(h) Select sum (salary)from salesperson where itcode=”i2”;

→
Mr. Mathew is a programmer, who has recently joined the company and given a task to write a python code to perform update operation in binary file (stud.dat) which contain admno (Admission No) and fee(Fee paid by student).

He has succeeded in writing partial code and has missed out certain statements, You as an expert of Python have to provide the missing statements.

(a)

→