REVISION SQL COMMANDS

Class 12 - Computer Science

Section A
Question No. 1 to 4 are based on the given text. Read the text carefully and answer the questions:
[4]
Consider the following tables WORKER and PAYLEVEL:
Table: WORKER

ECODE NAME DESIGN PLEVEL DOJ DOB

11 Radhe 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-Jun-2009 14-Oct-1983

15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984

18 Sanya Clerk P002 19-Dec-2005 09-Jun-1983

Table: PAYLEVEL

PLEVEL PAY ALLOWANCE

P001 26000 12000

P002 22000 10000

P003 12000 6000

1. Write SQL commands for the following statements:

i. To display the name of all Workers in descending order of DOB.
ii. To display NAME and DESIGN of those Workers, whose PLEVEL is either P001 or P002.
2. Write SQL commands for the following statements:
i. To display the content of all the workers table, whose DOB is in between '19- JAN-1984' and '18-JAN-1987'.
ii. To add a new row with the following: 19, 'DayaKishore', 'Operator', 'P003', '19- Sep-2008', 17-Jul-1984'
3. 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;
4. Give the output of the following SQL queries:
i. SELECT Name, PAY FROM WORKER W, PAYLEVEL P WHERE [Link] = [Link] AND [Link] <
13;
ii. SELECT PLEVEL, PAY+ ALLOWANCE FROM PAYLEVEL WHERE PLEVEL= "P003 ";

Question No. 5 to 8 are based on the given text. Read the text carefully and answer the questions: [4]

Consider the following tables GAMES and PLAYER:

Table: GAMES

GCode Game Name Type Number Prize Money Schedule Date

101 Carom Board Indoor 2 5000 23-Jan-2004

102 Badminton Outdoor 2 12000 12-Dec-2003

103 Table Tennis Indoor 4 8000 14-Feb-2004

105 Chess Indoor 2 9000 01-Jan-2004

108 Lawn Tennis Outdoor 4 25000 19-Mar-2004

Table: PLAYER

PCode Name GCode

1 Nabi Ahmad 101

2 Ravi Sahai 108

3 Jatin 101

4 Nazneen 103

5. What do you understand by primary key and candidate keys?

6. Write SQL commands for the following statements:
i. To display the name of all GAMES with their GCodes.
ii. To display details of those GAMES which are having PrizeMoney more than 7000.

1/
 7. Write SQL commands for the following statements:
i. To display the content of the GAMES table in ascending order of Schedule Date.
ii. To display sum of PrizeMoney for each type of GAMES.
8. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT Number) FROM GAMES;
ii. SELECT MAX(ScheduleDate), MIN (ScheduleDate) FROM GAMES;
iii. SELECT Name, GameName FROM GAMES G, PLAYER P WHERE ([Link]= [Link] AND [Link]>10000);
iv. SELECT DISTINCT Gcode FROM PLAYER;

Question No. 9 to 13 are based on the given text. Read the text carefully and answer the questions:
[5]
In a Database, there are two tables with the instances given below:

Table : STUDENTS

ADMNO NAME CLASS SEC RNO ADDRESS PHONE

1211 MEENA 12A D 4 A-26 3245678

1212 VANI 10A D 1 B-25 5456789

1213 MEENA 12B A 1 NULL NULL

1214 KARISH 10B B 3 AB-234 4567890

Table : SPORTS
ADMNO GAME COACHNAME GRADE

1215 CRICKET MR. RAV A

1213 VOLLEYBALL [Link] B

1211 VOLLEYBALL [Link] A

1212 BASKET BALL MR. TEWARI B

9. Choose the command to display name and game of those students whose address is available in STUDENTS table.

a) b)
SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE
[Link]=[Link], ADDRESS IS NULL; [Link]=[Link] NOT ADDRESS IS NULL;

c) d)
SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE
[Link]=[Link] AND ADDRESS IS NOT NULL; [Link]=[Link] AND ADDRESS IS NULL;

10. Identify the statement to delete a column phone from the table STUDENTS.

a) b)
DROP PHONE; UPDATE DROP PHONE;

c)
ALTER TABLE STUDENTS DROP PHONE; d)
DELETE FROM STUDENTS WHERE DROP PHONE;

11. Choose the command to display name of the students who are studying in class 12 and their corresponding coach names.

a) b)
SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLSAESLSECLTIKNEA=M"E1,2%C"OAACNHDNASMTEUDFERNOTM=SSPTOURDTESN;TS, SPORTS WHERE CLASS
LIKE "12%"

c) d)
SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLSAESLSECLTIKNEAM"E1,2%C"[Link]=SS,[Link];RE
CLASS LIKE "12%"

12. Choose the command to count the number of students who play volleyball.

a) b)
SELECT COUNT(GAME) FROM STUDENTS, SPORTS WHERE GAME="SVEOLLELCETYBCAOLULN"T(A*N)D
[Link],O=[Link];SPORTS="VOLLEYBALL" AND
=[Link]

c) d)
SELECT C0UNT(*) FROM STUDENTS, SPORTS WHERE GAME="VOLSLEELYEBCATLLC"OUANNTD(*S)[Link]=SS,[Link];RE
GAME="VOLLEYBALL";

13. Which two select queries will give the same output?
A. SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NULL AND [Link] =[Link];
B. SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NOT NULL AND [Link] =[Link];
C. SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NULL OR [Link]=[Link];
D. SELECT [Link], [Link] FROM STUDENTS ST, SPORTS SP WHERE ADDRESS IS NULL AND ST. ADMNO=[Link];

a) B and C b) A and D

c) C and D d) A and B
Section B

2/
14. Which of the following attributes cannot be considered as a choice for the primary key? [1]

a) Id b) Street

c) Dept_id d) License number

15. Aggregate functions can be used in the select list or the clause of a select statement. They cannot be used in a clause. [1]

a) Group by, where b) Group by, having

c) Where, having d) Having, where

16. With SQL, how do you select all the records from a table named "Students" where the value of the column "FirstName" ends with an "a"? [1]

a) SELECT * FROM Students WHERE FirstName LIKE '%a' b) SELECT * FROM Students WHERE FirstName = '%a%'

c) SELECT * FROM Students WHERE FirstName LIKE 'a%' d) SELECT * FROM Students WHERE FirstName = 'a'
17. Which of the following is not an aggregate function? [1]

a) Min b) With

c) Avg d) Sum
18. Consider the table with structure as: [1]
Student(ID, name, dept name, tot_cred)
Which attribute will form the primary key?
a) Dept b) ID

c) Total credits d) Name

19. Consider the following query :
[1]
SELECT name, instructor name, course_id FROM instructor;
To display the field heading course with a different heading as id, which keyword must be used here to rename the field name?

a) From b) Join

c) As d) Rename
20. By default, ORDER BY clause lists the results in order [1]

a) Any b) Descending

c) Same d) Ascending
Section C
21. What is an Alternate Key? [1]
22. Differentiate between Candidate Key and Alternate Key in context of RDBMS. [1]
23. Write the query to select all the persons from the table Persons and sort the persons by their last name in ascending order. [1]
24. What do you understand by the terms Primary Key and Degree of a relation in relational database? [1]
25. Write a query that counts the number of sales people registering orders for each day. (If a salesperson has more than one order on a given day, he or she should be counted
[1]
only once.).
26. Give a suitable example of a table with sample data and illustrate Primary and Alternate Keys in it. [3]
27. i. Write two examples of DBMS software.
[3]
ii. What is meant by NULL value in MySQL?
iii. Table 'Club' has 4 rows and 3 columns. Table 'Member' has 2 rows and 5 columns. What will be the cardinality of the Cartesian product of them?
28. Give output for following SQL queries as per given table(s):
[3]
Table: INTERIORS

NO. ITEM NAME TYPE DATEOFSTOCK PRICE DISCOUNT

1. Red rose Double Bed 23/02/02 32000 15

2. Soft-touch Baby cot 20/01/02 9000 10

3. Jerry's home Baby cot 19/02/02 8500 10

4. Rough wood Office Table 01/01/02 20000 20

5. Comfort zone Double Bed 12/01/02 15000 20

6. Jerry look Baby cot 24/02/02 700 19

7. Lion king Office Table 20/02/02 16000 20

8. Royal tiger Sofa 22/02/02 30000 25

9. Park sitting Sofa 13/12/01 9000 15

10. Dine Paradise Dining Table 19/02/02 11000 15

i. SELECT COUNT (DISTINCT TYPE) FROM INTERIORS;

ii. SELECT AVG(DISCOUNT) FROM INTERIORS WHERE TYPE = "Baby cot" ;
iii. SELECT SUM(PRICE) FROM INTERIORS WHERE DATEOFSTOCK< {12/02/02}.
29. What do you understand by Union and Cartesian Product operations performed upon two relations? [3]
30. Consider the following tables EMPLOYEE and DEPARTMENT and answer (a) and (b) parts of this question.
[4]
Table: EMPLOYEE

3/
 TCode TName DepCde Salary Age JoinDate

15 Sameer Sharma 123 75000 39 01-Apr-2007

21 Raguvinder K 101 86000 29 11-Nov-2005

34 Rama Gupta 119 52500 43 03-Mar-2010

46 C R Menon 103 67000 38 12-Jul-2004

77 Mohan Kumar 103 63000 55 25-Nov-2000

81 Rajesh Kumar 119 74500 48 11-Dec-2008

89 Sanjeev P 101 92600 54 12-Jan-2009

93 Pragya Jain 123 32000 29 05-Aug-2006

Table: DEPARTMENT

DepCde DepName DepHead

101 ACCOUNTS Rajiv Kumar

103 HR P K Singh

119 IT Yogesh Kumar

123 RESEARCH Ajay Dutta

a. Write SQL commands for the following statements:

i. To display all DepName along with the DepCde in descending order of DepCde.
ii. To display the average age of Employees in DepCde as 103.
iii. To display the name of DepHead of the Employee named "Sanjeev P"
iv. To display the details of all employees who have joined before 2007 from the EMPLOYEE table.
b. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT DepCde) FROM EMPLOYEE;
ii. SELECT MAX(JoinDate), MIN (JointDate) FROM EMPLOYEE;
iii. SELECT TName, DepHead FROM EMPLOYEE E, DEPARTMENT D WHERE [Link] = [Link];
iv. SELECT COUNT (*) FROM EMPLOYEE WHERE Salary > 60000 AND Age > 30;
31. Give a suitable example of a table with sample data and illustrate Primary and Candidate Keys in it. [4]

32. Consider the following table named "SOFTDRINK". Write commands of SQL for (i) to (iv).
[4]
Table : SOFTDRINK

DRINKCODE DNAME PRICE CALORIES

101 Lime and Lemon 20.00 120

102 Apple Drink 18.00 120

103 Nature Nectar 15.00 115

104 Green Mango 15.00 140

105 Aam Panna 20.00 135

106 Mango Juice Bahaar 12.00 150

i. To display names and drink codes of those drinks that have more than 120 calories.
ii. To display drink codes, names and calories of all drinks, in descending order of calories.
iii. To display names and price of drinks that have price in the range 12 to 18 (both 12 and 18 included).
iv. Increase the price of all drinks in the given table by 10%.
33. Consider the following tables STUDENT and STREAM. Write SQL commands for the statements (i) to (v).
TABLE: STUDENT [4]

SCODE NAME AGE STRODE POINTS GRADE

101 Amit 16 1 6 NULL

102 Arjun 13 3 4 NULL

103 Zaheer 14 2 1 NULL

105 Gagan 15 5 2 NULL

108 Kumar 13 6 8 NULL

109 Rajesh 17 5 8 NULL

110 Naveen 13 3 9 NULL

113 Ajay 16 2 3 NULL

115 Kapil 14 3 2 NULL

120 Gurdeep 15 2 6 NULL

4/
 TABLE: STREAM

STRCDE STRNAME

1 SCIENCE+COMP

2 SCIENCE+BIO

3 SCIENCE+ECO

4 COMMERCE+MATHS

5 COMMERCE+SOCIO

6 ARTS+MATHS

7 ARTS+SOCIO

i. To display the name of streams in alphabetical order from table STREAM.

ii. To display the number of students whose POINTS are more than 5.
iii. To update GRADE to ‘A’ for all those students, who are getting more than 8 as POINTS.
iv. ARTS+MATHS stream is no more available. Make necessary change in table STREAM.
v. To display student’s name whose stream name is science and computer.
34. Consider the following tables ACTIVITY and COACH. Write SQL commands for the following statements [4]
Table: ACTIVITY

Acode ActivityName Stadium ParticipantsNum PrizeMoney ScheduleDate

1001 Relay 100 × 4 Star Annex 16 10000 23-Jan-04

1002 High jump Star Annex 10 12000 12-Dec-03

1003 Shot Put Super Power 12 8000 14-Feb-04

1005 Long Jump Star Annex 12 9000 01-Jan-04

1008 Discuss Throw Super Power 10 15000 19-Mar-04

Table: COACH

Pcode Name Acode

1 Ahmad Hussain 1001

2 Ravinder 1008

3 Janila 1001

4 Naaz 1003

i. To display the names of all activities with their Acodes in descending order.
ii. To display sum of PrizeMoney for the Activities played in each of the Stadium separately.
iii. To display the coach's names and Acodes in ascending order of Acode from the table COACH.
iv. To display the content of all activities for which ScheduleDate is earlier than 01-01-2004 in ascending order of ParticipantsNum.
35. Consider the following tables CABHUB and CUSTOMER and answer (b) and (c) parts of this question:
Table: CABHUB [6]
Vcode VehicleName Make Colour Capacity Charges

100 Innova Toyota WHITE 7 15

102 SX4 Suzuki BLUE 4 14

104 C Class Mercedes RED 4 35

105 A-Star Suzuki WHITE 3 14

108 Indigo Tata SILVER 3 12

Table: CUSTOMER

Ccode Cname Vcode

1 Hemant Sahu 101

2 Raj Lai 108

3 Feroza Shah 105

4 Ketan Dhal 104

a. Give a suitable example of a table with sample data and illustrate Primary and alternate Keys in it.
b. Write SQL commands for the following statements:
i. To display the names of all the white-colored vehicles.
ii. To display the name of vehicle name and the capacity of vehicles in ascending order of their sitting capacity.
iii. To display the highest charges at which a vehicle can be hired from CABHUB.
iv. To display the customer name and the corresponding name of the vehicle hired by them.
c. Give the output of the following SQL queries:

5/
 i. SELECT COUNT (DISTINCT Make) FROM CABHUB;
ii. SELECT MAX(Charges), MIN(Charges) FROM CABHUB;
iii. SELECT COUNT (*) Make FROM CABHUB;
iv. SELECT Vehicle FROM CABHUB WHERE Capacity=4;
36. Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the tables 'Watches' and Sale given below.
[6]
Watches

Watchid WatchName Price Type Qty_Store

W001 High Time 10000 Unisex 100

W002 Life Time 15000 Ladies 150

W003 Wave 20000 Gents 200

W004 High Fashion 7000 Unisex 250

W005 Golden Time 25000 Gents 100

Sale

Watchid Qty_Sold Quarter

W001 10 1

W003 5 1

W002 20 2

W003 10 2

W001 15 3

W002 20 3

W005 10 3

W003 15 4

i. TO DISPLAY ALL THE DETAILS OF THOSE WATCHES WHOSE NAME ENDS WITH TIME.
ii. TO DISPLAY WATCH'S NAME AND PRICE OF THOSE WATCHES WHICH HAVE PRICE RANGE IN BETWEEN 5000-15000.
iii. TO DISPLAY TOTAL QUANTITY IN-STORE OF UNISEX TYPE WATCHES.
iv. TO DISPLAY WATCH NAME AND THEIR QUANTITY SOLD IN the FIRST QUARTER.
v. SELECT MAX (PRICE), MIN(QTY_STORE) FROM WATCHES;
vi. SELECT QUARTER, SUM(QTY_SOLD) FROM SALE GROUP BY QUARTER;
vii. SELECT WATCHNAME, PRICE, TYPE FROM WATCHES W, SALE S WHERE W. WATCHID!= [Link];
viii. SELECT WATCHNAME, QTYSTORE, SUM (QTYSOLD), QTY_STORE - SUM (QTY_SOLD) "STOCK" FROM WATCHES W, SALE S WHERE W.
WATCHID = [Link] GROUP BY [Link];
37. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables:
DVD
[6]
DCODE DTITLE DTYPE

F101 Henry Martin Folk

Cl 02 Dhrupad Classical

C101 The Planets Classical

F102 Universal Soldier Folk

R102 A day in the life Rock

MEMBER

MID NAME DCODE ISSUEDATE

101 AGAM SINGH R102 2017-11-30

103 ARTH JOSEPH F102 2016-12-13

102 NISHA HANS C101 2017-07-24

i. To display all details from the table MEMBER in descending order of ISSUEDATE.
ii. To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
iii. To display the Dtype and number of DVDs in each DTYPE from the table DVD.
iv. To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued (i.e., ISSUEDATE) in the year 2017.
v. SELECT MIN (ISSUEDATE) FROM MEMBER;
vi. SELECT DISTINCT DTYPE FROM DVD;
vii. SELECT [Link]. NAME, DTITLE: FROM DVD D, MEMBER M WHERE [Link]=[Link];
viii. SELECT DTITLE FROM DVD WHERE DTYPE NOT IN ("Folk", "Classical");

6/