Download App

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

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Query Using–SQL5 Marks
Question

Write SQL commands for the following queries on the basis of Club relation given below:

Relation: Club

Coach-ID CoachName Age Sports date_of_app Pay Sex
1 Kukreja 35 Karate 27/03/1996 1000 M
2 Ravina 34 Karate 20/01/1998 1200 F
3 Karan 34 Squash 19/02/1998 2000 M
4 Tarun 33 Basketball 01/01/1998 1500 M
5 Zubin 36 Swimming 12/01/1998 750 M
6 Ketaki 36 Swimming 24/02/1998 800 F
7 Ankita 39 Squash 20/02/1998 2200 F
8 Zareen 37 Karate 22/02/1998 1100 F
9 Kush 41 Swimming 13/01/1998 900 M
10 Shailya 37 Basketball 19/02/1998 1700 F

(a) To show all information about the swimming coaches in the club.

(b) To list the names of all coaches with their date of appointment (date_of_app) in descending order.

(c) To display a report showing coach name, pay, age, and bonus (15% of pay) for all coaches.

(d) To insert a new row in the Club table with ANY relevant data:

(e) Give the output of the following SQL statements:

(i) Select COUNT(Distinct Sports) from Club;

(ii) Select Min(Age) from Club where SEX = “F”;

✓

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

Answer the following questions:

id Feature1 Feature2
0 1 A B
1 2 C D
2 3 E F
3 4 G H
4 5 I J


id Feature1 Feature2
0 1 K L
1 2 M N
2 6 O P
3 7 Q R
4 8 S T

(i) To create the data frame for the above dataset.

(ii) To join the data frames.

(iii) To count the the rows in new data frame.

(iv) To reset the index.

→
Indian Law Organization is planning to computerized their application form. For registration, organization has setup the rules imposed by Indian Law. Citizen can check their eligibility criteria by providing Nationality, Gender and Age.

On the basis of above code, choose the best appropriate option.

(a) Which statement will be executed if Nationality is Indian, Gender is Male and Age is 19.

(i) Statement 1 (ii) Statement 2

(iii) Statement 3 (iv) Statement 4

(b) Which Statement will be executed, if Nationality is American, Gender is Female and Age is 27.

(i) Statement 1 (ii) Statement 2

(iii) Statement 4 (iv) Statement 5

(c) Which Statement is executed, if Nationality is Indian, Age is 25 and Gender if Female.

(i) Statement 1 (ii) Statement 2

(iii) Statement 3 (iv) Statement 4

(d) Which Statement is executed, if Nationality is Indian, Age is 17 and Gender is Female.

(i) Statement 1 (ii) Statement 2

(iii) Statement 3 (iv) Statement 4

(e) Which statement will be executed if Nationality is Indian, Gender is Male and Age is 29.

(i) Statement 1 (ii) Statement 2

(iii) Statement 3 (iv) Statement 4

→
Answer the (a) and (b) on the basis of the following tables STORE and ITEM: [Delhi 2014]

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):

→
Uplifting Skills Hub India is a knowledge and skill community which has an aim to uplift the standard of knowledge and skills in the society. It is planning to setup its training centers in multiple towns and villages pan India with its head offices in the nearest cities. They have created a model of their network with a city, a town and 3 villages as follows.

As a network consultant, you have to suggest the best network related solutions for their issues/problems raised in (a) to (d) keeping in mind the distances between various locations and other given parameters.

Shortest distances between various locations:

VILLAGE 1 to B_TOWN 2 KM
VILLAGE 2 to B_TOWN 1.0 KM
VILLAGE 3 to B_TOWN 1.5 KM
VILLAGE 1 to VILLAGE 2 3.5 KM
VILLAGE 1 to VILLAGE 3 4.5 KM
VILLAGE 2 to VILLAGE 3 2.5 KM
A_CITY Head Office to B_HUB 25 KM

Number of Computers installed at various locations are as follows:

B_TOWN 120
VILLAGE 1 15
VILLAGE 2 10
VILLAGE 3 15
A_CITY OFFICE 6

Note:

• In Villages, there are community centers, in which one room has been given as training center to this organization to install computers.
• The organization has got financial support from the government and top IT companies.

(a) Suggest the most appropriate location of the SERVER in the B_HUB (out of the 4 locations), to get the best and effective connectivity. Justify your answer.

(b) Suggest the best wired medium and draw the cable layout (location to location) to efficiently connect various locations within the B_HUB.

(c) Which hardware device will you suggest to connect all the computers within each location of B_HUB?

(d) Which service/protocol will be most helpful to conduct live interactions of Experts from Head Office and people at all locations of B_HUB?

→
Pawan is confused in understanding Dictionary concept. Help him to understand the concept by choosing the appropriate answer. (a) Which statement will display Not Available?
(i)Statement 9 (ii)Statement 10 (iii)Statement 11(iv)Statement 13
(b) Choose the key from Dictionary.
(i)ADMNO (ii)‘admno’ (iii)101 (iv)“Not Available”
(c) Which statement will display None?
(i)Statement 9 (ii)Statement 10 (iii)Statement 11(iv)Statement 13
(d) Which Statement will display Tejas?
(i) Statement 8 (ii) Statement 9 (iii) Statement 10 (iv) Statement 11
(e) Choose the identifier.
(i) ADMNO (ii) ‘admno’ (iii) setdefault (iv)Not Available
→

Consider the table LOANS given below:

Table: LOANS

AccNo Name Loan Amt EMI IntRate Start Date Interest
1001 R.K. Gupta 300000 36 12.00 19-07-2009 1200
1002 S. P. Shanna 500000 48 10.00 22-03-2008 1800
1003 K.P. Jain 300000 36 NULL 08-03-2007 1600
1004 M.P. Yadav 800000 60 10.00 06-12-2008 2250
1005 S.P. Sinlia 200000 36 12.50 03-01-2010 4500
1006 P. Shanna 700000 60 12.50 05-06-2008 3500
1007 K.S. Dhall 500000 48 NULL 05-03-2008 3800

(a) State the command that will give the output as:

Name
S. P. Shanna
K.S. Dhall
M.P. Yadav
P. Shanna
K.S. Dhall

(i) Select name from LOANS where Name >‘D ’;

(ii) Select name from LOANS where AccNo >1001;

(iii) Select name from LOANS where Loan_Amt >= 500000;

(iv) Select name from LOANS where Loan_Amt >500000 order Loan_AmtDESC;

(b) What will be the output of the following command:

Select name, EMI from LOANS where Loan_Amt >500000 and Int_Rate is NULL;

(c) John has given the following command to display the count of all loan holders whose name ends with “Sharma ”:

Select count() from LOANS where Name like ‘Sharma%’;

But he is not getting the desired result. Help him by finding out the correct command fromthe followings:

(i) Select count(*) from LOANS where name Like ‘Sharma%’;

(ii) Select count(*) from LOANS where name Like ‘%Sharma ’;

(iii) Select count( ) from LOANS where name like ‘%sharma ’;

(iv) Select count(Name) from LOANS where name ‘Sharma%’;

(d) State the command to display the maximum Loan_Amt and Cust_Name :-

(i) Select Max(Cust_Name, Loan_Amt) from LOANS;

(ii) Select Cust_Name, max(LoanAmt) from LOANS;

(iii) Select Cust_Name, max(Loan_Amount) from LOANS;

(iv) Select Cust_name, max(Loan_Amt) from LOANS;

(e) Help Ramesh to display the count of all loan holders whose interest is NULL.

(i) Select Count(*) from LOANS where Int_Rate is NULL.

(ii) Select Count() from LOANS where Interest is NULL.

(iii) Select Count(*) from LOANS where Int_rate = NULL;

(iv) Select Count( ) from LOANS where Interest = NULL;

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

→
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 the few points for the healthy use of technology.
→
On the basis of given dataframe answer the following questions:

Account Name Rep Manager Product Quantity Price Status
0 714466 Tata Saryu Abhishek CPU 1 30000 presented
1 714466 Tata Saryu Abhishek Software 1 10000 presented
2 714466 Tata Saryu Abhishek Maintenance 2 5000 pending
3 737550 Infosys Saryu Abhishek CPU 1 35000 declined
4 146832 Sapient Taneja Abhishek CPU 2 65000 won
5 218895 IBM Taneja Abhishek CPU 2 40000 pending
6 218895 IBM Taneja Abhishek Software 1 10000 presented
7 412290 Oracle Joe Abhishek Maintenance 2 5000 pending
8 740150 Flipkart Joe Abhishek CPU 1 35000 declined
9 141962 Byju Charu Arush CPU 2 65000 won
10 163416 Gradup Charu Arush CPU 1 30000 presented
11 239344 Funtoot Charu Arush Maintenance 1 5000 pending
12 239344 Funtoot Charu Arush Software 1 10000 presented
13 307599 SQL Naveen Arush Maintenance 3 7000 won
14 688981 PiE Naveen Arush CPU 5 100000 won
15 729833 Amazon Naveen Arush CPU 2 65000 declined
16 729833 Amazon Naveen Arush Monitor 2 5000 presented

(i) To print the complete dataframe Name wise.

(ii) To print the dataframe Name wise, Rep waise and Manage wise.

(iii) To print the data frame Manager and Rep wise.

(iv) To print the data frame Manager and Rep price wise

(v) To print the sum of the price, manager and rep wise.

(vi) To print the mean and count of the price which belong to each manager and rep.

(vii) To print the sum of the price which belong to each manager and rep.

(viii) To print the sum of the price which belong to each manager and rep along with Product belongs to them. Fill the NaN with 0.

→