Download App

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

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

(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

(a) SELECT * FROM CLUB WHERE SPORTS = “Swimming ”;

(b) SELECT Name FROM CLUB ORDER BY date_ of_app desc;

(c) SELECT COACHNAME, PAY, AGE, PAY*15/100 AS BONUS FROM CLUB;

(d) INSERT INTO CLUB VALUES (11, “Neelam ”, 35, “Basketyball ”, “2000/04/01 ”, 2200, “F ”);

(e) (i) 4

(ii) 34

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

WAP to generate n lines of the following pattern on the computer screen: 1
2 2
3 3 3
4 4 4 4
→
Write a program which will find all such numbers which are divisible by 7 but are not multiples of 5, between 2000 and 2500 (both included). The numbers obtained should be printed in a comma-separated sequence on a single line.
→
Write a function AverageColumn() which accepts csv file as parameter. It will print sum and average of each row. For example if marks.csv contain 50,60,70,80,90 70,60,50,60,80 50,60,70,80,90 Then OUTPUT will be Sum is : 350.0 Average is : 116.66666666666667 Sum is : 320.0 Average is : 106.66666666666667 Sum is : 350.0 Average is : 175.0
→
Write a program to enter a string and remove all punctuation characters from the following string.

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

→
Answer the following questions based on dataset given below:

TNO Tname Tadd Salary
0 T01 Amit 123 Paschim Vihar 23000
1 T02 Rajesh 6/11 Ramesh Nagar 34000
2 T03 Binny 5 West Punjabhi Bagh 12000
3 T04 Charu 23Malviya Nagar 45000
4 T05 Meenakshi 19 Meera Bagh 34000

(i) To write the statement to import the required library.

(ii) To create the data frame from the above dictionary.

(iii) To print the data frame.

(iv) To print the name columns.

(v) To print the complete information of the data frame.

(vi) To print the index of data frame.

(vii) To print the various attributes of the data frame i.e Shape, Dimensions, Columns and number of records.

(viii) To print the records whose salary is greater than 5000.

(ix) To create another data frame which will store all the records of the teacher whose salary is greater than 5000.

(x) To display the Salary.

(xi) To set the index to the column 'Tname'.

(xii) To display the record of 'Amit'.

(xiii) To display the record fo 'Amit' and 'Binny'

(xiv) To display the Salary and Address of 'Amit' and 'Binny'.

(xv) To display the records of the teachers whos are earning salary more than 10000.

(xvi) To display the records of the teachers whos are earning salary less than 40000.

(xvii) To add new column Dearness Allowance with any values.

(xviii) To add new column "HRA" using .loc().

(xix) To add new column tax using assign().

(xx) To display the HRA column.

(xxi) To display the records of the Teachers who have HRA of more than 500.

(xxii) To add a new column Total Salary which is to be calculated as Salary + HRA - Tax.

(xxiii) To fill all NaN values with 5000.

(xxiv) To replace all the NaN with space.

(xxv) To Update the new colum with values as Salary + HRA + Dearness Allowance - Tax

Write the output of the given commands:

→
Write a python program using function to demonstrate random() along with list, tuple and dictionary. The program should do 3 things: (i) Should create the list of 5 random numbers between 10 to 99. (ii) Should create the tuple of 5 random numbers between 10 to 99. (iii) Should create the dictionary of 5 keys:values random characters as key and ASCII as its value.
→
Write a script to create a list of 10 random floating point numbers and then
(a) Display this list of numbers
(b) Find the largest and smallest numbers present in the list (without using max() and min() functions)
→
Granuda Consultants are setting up a secured network for their office campus at Faridabad for their day to day office and web based activities. They are planning to have connectivity between 3 buildings and the head office situated in Kolkata. Answer the questions (a) to (d) after going through the building positions in the campus and other details, which are given below:

Distances between various buildings:

Building “RAVI” to Building “JAMUNA” 120m
Building “RAVI” to Building “GANGA” 50m
Building “GANGA” to Building “JAMUNA” 65m
Faridabad Campus to Head Office 1460km

Number of computers:

Building “RAVI” 25
Building “JAMUNA” 150
Building “GANGA” 51
Head Office 10

(a) Suggest the most suitable place (i.e. block) to house the server of this organization. Also give a reason to justify your suggested location.

(b) Suggest a cable layout of connections between the buildings inside the campus.

(c) Suggest the placement of the following devices with justification:

(i) Switch

(ii) Repeater

(d) The organization is planning to provide a high speed link with its head office situated in KOLKATA using a wired connection. Which of the following cable will be most suitable for this job?

(i) Optical Fiber

(ii) Co-axial cable

(iii) Ethernet cable

→
Write the Pseudocode to sort the list using Selection sort – to sort a list A in ascending order
→