OBJECTIVE TYPE QUESTIONS

1. Fill in the blanks.

(a) The function works with data of multiple rows at a time and returns aggregated value.
(b) The clause lets you arrange the result set in the order of single column, multiple
columns and custom sort order too.
(c) To specify filtering condition for groups, the clause is used in MySQL.
(d) By default, the ORDER BY clause sorts the result-set in the order.
(e) To sort the result set in descending order, keyword is used with ORDER BY.
(f) Two types of MySQL functions are single row functions and functions.
(g) The function returns the total number of rows, including duplicates and NULL in a table.
(h) Group functions are also known as functions.
(i) In MySQL, option causes a group function to consider only unique values of the
argument expression.
(j) The function returns the lowest value from the given column or expression.

2. State whether the following statements are True or False.

(a) The ORDER BY clause combines all those records that have identical values in a particular field or a
group of fields.
(b) The WHERE clause is used to specify filtering conditions for groups.
(c) DISTINCT option causes a group function to consider only the unique values of the argument expression.
(d) By default, ORDER BY clause sorts the result set in descending order.
(e) COUNT() function ignores duplicate and null values while counting the records.
(f) The return value of MAX() function is a numeric value.
(g) Multiple row function is also known as scalar function.
(h) SUM() function is used to count the total number of records in a table.
(i) Argument type of AVG() function can be numeric or string data type.
(j) Group functions can be applied to any numeric values, some text types and DATE values.

3. Multiple Choice Questions (MCQs)

(a) What will be the order of the data being sorted after the execution of given SQL query?
SELECT *FROM STUDENT ORDER BY ROLL_NO;
(i) Custom Sort (ii) Descending (iii) Ascending (iv) None of these
(b) Which values will not be considered by SQL while executing the following statement?
SELECT COUNT(column name) FROM DEPARTMENT;
(i) Numeric value (ii) Text value (iii) Null value (iv) Date value
(c) Which of the following clauses is used to sort the result set?
(i) SORT BY (ii) GROUP BY (iii) ARRANGE BY (iv) ORDER BY
(d) Which clause is used in query to place the condition on groups in MySQL?
(i) WHERE (ii) HAVING (iii) GROUP BY (iv) Both (i) & (ii)
(e) If column "Marks" contains the data set {25, 35, 25, 35, 38}, what will be the output after the
execution of the given query?
SELECT MARKS (DISTINCT) FROM STUDENTS;
(i) 25,35,25,35,38 (ii) 25,25,35,35,38 (iii) 25,35,38 (iv) 25,25,35,35
(f) If column "Salary" contains the data set {10000,15000,25000,10000,15000}, what will be the output
after the execution of the given query?
SELECT SUM (DISTINCT SALARY) FROM EMPLOYEE;
(i) 75000 (ii) 25000 (iii) 10000 (iv) 50000
(g) What SQL statement do we use to find the total number of records present in the table Product?
(i) SELECT * FROM PRODUCT; (ii) SELECT COUNT(*) FROM PRODUCT;
(iii) SELECT FIND (*) FROM PRODUCT; (iv) SELECT SUM ( ) FROM PRODUCT;
 (h) What SQL statement do we use to display the record of all students whose last name contains 5
letters ending with "A"?
(i)
SELECT * FROM STUDENTS WHERE LNAME LIKE ' A';
(ii)
SELECT * FROM STUDENTS WHERE LNAME LIKE '
(iii)
SELECT * FROM STUDENTS WHERE LNAME LIKE '????A';
(iv)
SELECT * FROM STUDENTS WHERE LNAME LIKE '*A';
(i) Which of the following functions is not an aggregate function?
(i) ROUND() (ii) SUM() (iii) COUNT() (iv) AVG()
(j) Which of the following functions is used to find the largest value from the given data in MySQL?
(i) MAX() (ii) MAXIMUM() (iii) LARGEST() (iv) BIG()

SOLVED QUESTIONS
1. What is SQL?
Ans. SQL stands for Structured Query Language. This language is used to create, manage and manipulate
stored records in a table.
2. What is null value in MySQL?
Ans. If a column in a row has no value, then the column is said to be null. Null is used to represent missing values.
3. Which keyword eliminates redundant data from a query result?
Ans. DISTINCT
4. Differentiate between WHERE and HAVING clause.
Ans. WHERE clause is used to select particular rows that satisfy a condition whereas HAVING clause is used
in connection with the aggregate function, GROUP BY clause. For example, SELECT * FROM STUDENT
WHERE MARKS>75; this statement shall display the records for all the students who have scored more
than 75 marks.
On the contrary, the statement — SELECT * FROM STUDENT GROUP BY STREAM HAVING MARKS>75;
shall display the records of all the students grouped together on the basis of stream but only for those
students who have scored marks more than 75.
5. Why is it not allowed to give String and Date type arguments for SUM() and AVG() functions?
Ans. String and dates are not real numbers that we calculate, so SUM() or AVG() functions are not valid
for them.
6. How are NULL values treated by aggregate functions? [HOTS]
Ans. None of the aggregate functions takes NULL into consideration except COUNT(*) function. NULL is simply
ignored by all the aggregate functions.
7. There is a column C1 in a table T1. The following two statements:
SELECT COUNT(*) FROM T1; and SELECT COUNT(C1) FROM T1; are giving different outputs. What may be
the possible reason? [HOTS]
Ans. There may be a null value in a C1 column.
8. What is the purpose of GROUP BY clause?
Ans. GROUP BY clause is used in a SELECT statement in combination with aggregate functions to group the
result based on distinct values in a column.

9. What is HAVING clause?

Ans. HAVING clause is used in combination with GROUP BY clause in a SELECT statement to put a condition on
groups.
10. How would you display system date as a result of a query?
Ans. mysql> SELECT CURDATE ();
11. How would you calculate 13*15 in SQL?
Ans. mysql> SELECT 13*15;
 12. Define a function.
Ans. A function is a special type of predefined command set that performs some operations and returns a
single value.
13. What will be the output of the following code?
mysql> SELECT CONCAT (CONCAT ( 'Inform', 'atics'), 'Practices');
Ans. InformaticsPractices
14. What will be the output of the following code?
mysql> SELECT LCASE ( 'INFORMATICS PRACTICES CLASS 11TH');
Ans. informatics practices class 11th
15. What will be the output of the following code?
mysql> SELECT CONCAT (LOWER ('CLASS'), UPPER(Ixii 1 ));
Ans. classXII
16. If Str="INFORMATICS PRACTICES" and Str1=" FOR CLASS XI", then give the SQL command to display the
output string as "INFORMATICS PRACTICES FOR CLASS Xl".
Ans. SELECT CONCAT(STR,STR1);
17. (i) Create and open Database named MYORG.
Ans. CREATE DATABASE MYORG;
USE MYORG;
(ii) Write a command to display the name of the current month.
Ans. SELECT MONTHNAME (CURDATE () );
(iii) Write commands to display the system date.
Ans. SELECT SYSDATE();
(iv) Write a query to find out the result of 63.
Ans. SELECT POW (6,3);
(v) Write a command to show the tables in the MYORG Database.
Ans. USE MYORG;
SHOW TABLES;
(vi) Add one column State of data type VARCHAR and size 30 to table DEPT.
Table: DEPT
DeptiD DeptName MgrID Location
10 SALES 8566 Mumbai
20 PERSONNEL 8698 Delhi
30 ACCOUNTS 8882 Delhi
40 RESEARCH 8839 Bengaluru

Ans. ALTER TABLE DEPT ADD(State VARCHAR(30));

18. Write SQL commands for the following on the basis of the given table CLUB.
Table: CLUB
COACH _ID COACH NAME AGE SPORTS DATE_OF_APP PAY SEX
1 KUKREJA 35 KARATE 1996-03-27 1000 M
2 RAVINA 34 KARATE 1998-01-20 1200 F
3 KARAN 34 SQUASH 1998-02-19 2000 M
4 TARUN 33 BASKETBALL 1998-01-01 1500 M
5 ZUBIN 36 SWIMMING 1998-01-12 750 M
6 KETAKI 36 SWIMMING 1998-02-24 800 F
7 ANKITA 36 SQUASH 1998-02-20 2200 F
8 ZAREEN 37 KARATE 1998-02-22 1100 F
9 KUSH 41 SWIMMING 1998-01-13 900 M
10 SHAILYA 37 BASKETBALL 1998-02-19 1700 M
 (a) To show all information about the swimming coaches in the club.
(b) To list 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 the coaches.
(d) Give the output of the following SQL statements:
(i) SELECT LCASE (SPORTS) FROM CLUB;
(ii) SELECT MOD(AGE,5) FROM CLUB WHERE SEX='F';
(iii) SELECT POW(3,2) FROM CLUB WHERE SPORTS='KARATE';
Ans. (a) SELECT * FROM CLUB WHERE SPORTS= 'SWIMMING';
(b) SELECT COACHNAME DATE OF APP FROM CLUB ORDER BY DATE OF APP DESC;
(c) SELECT COACHNAME, PAY, AGE, PAY*0.15 AS "BONUS" FROM CLUB;
(d)
(i) LCASE(SPORTS)
karate
karate
squash
basketball
swimming
swimming
squash
karate
swimming
basketball

MOD(AGE,5)
4
1
1
2

POW(3,2)
9
9
9

19. Consider a database LOANS with the following table:

Table: LOANS
AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date Interest
1 R.K. Gupta 300000 36 12.00 19-07-2009 1200
2 S.P. Sharma 500000 48 10.00 22-03-2008 1800
3 K.P. Jain 300000 36 NULL 08-03-2007 1600
4 M.P. Yadav 800000 60 10.00 06-12-2008 2250
5 S.P. Sinha 200000 36 12.50 03-01-2010 4500
6 P. Sharma 700000 60 12.50 05-06-2008 3500
7 K.S. Dhall 500000 48 NULL 05-03-2008 3800

Answer the following questions.

(a) Display the sum of all loan amounts whose interest rate is greater than 10.
Ans. mysql> SELECT SUM(Loan Amount) FROM LOANS WHERE Int Rate>10;
(b) Display the maximum interest from Loans table.
Ans. mysql> SELECT MAX (INTEREST) FROM LOANS;
(c) Display the count of all loan holders whose name ends with 'Sharma'.
Ans. mysql> SELECT COUNT (*) FROM LOANS WHERE Cust Name LIKE '%Sharma';
 (d) Display the count of all loan holders whose interest is Null.
Ans. mysql> SELECT COUNT(*) FROM LOANS WHERE Interest IS NULL;
(e) Display the interest-wise details of loan account holders.
Ans. mysql> SELECT * FROM LOANS GROUP BY Interest;
(f) Display the interest-wise details of loan account holders with at least 10 instalments remaining.
Ans. mysql> SELECT * FROM LOANS GROUP BY Interest HAVING Instalments>=10;
(g) Display the interest-wise count of all loan holders whose instalments due are more than 5 in each group.
Ans. mysql> SELECT COUNT(*) FROM LOANS GROUP BY Interest HAVING Instalments>5;
20. Name two Aggregate (Group) functions of SQL. [CBSE D 2016]
Ans. COUNT()
MAX()
21. Consider the table: [CBSE D 2016]
Table: COMPANY
SID SALES
5101 20000
S103 NULL
S104 10000
S105 15000

What output will be displayed by the following SQL statement?

SELECT AVG(SALES) FROM COMPANY;
Ans. 15000
22. Consider the table 'Hotel' given below: [CBSE D 2016]
Table: HOTEL
EMPID CATEGORY SALARY
E101 MANAGER 60000
E102 EXECUTIVE 65000
E103 CLERK 40000
E104 MANAGER 62000
E105 EXECUTIVE 50000
E106 CLERK 35000
Mr. Vinay wanted to display average salary of each category. He entered the following SQL statement.
Identify error(s) and rewrite the correct SQL statement.
SELECT CATEGORY, SALARY
FROM HOTEL
GROUP BY CATEGORY;
Ans. SELECT CATEGORY, AVG(SALARY) FROM HOTEL GROUP BY CATEGORY;
23. (a) Mr. Manav, a database administrator in "Global Educational and Training Institute", has created the
following table named "Training" for the upcoming training schedule: [CBSE Sample Paper 2019]
Table: TRAINING
Training_Id Name Email _ Id Topic City Fee
NDO1 Mr. Rajan raj@[Link] Cyber Security New Delhi 10000
GUO1 Ms. Urvashi urv@[Link] ICT in Education Gurugram 15000
FD01 Ms. Neena [Link] Cyber Security Faridabad 12000
NDO2 Mr. Vinay NULL ICT in Education New Delhi 13000
GUO2 Mr. Naveen nav@[Link] Cyber Security Gurugram NULL
 Help him in writing SQL query for the following purpose:
i. To count how many female candidates will be attending the training.
Ans. SELECT COUNT (Name) FROM TRAINING WHERE Name Like 'Ms.°';
ii. To display list of free trainings.
Ans. SELECT * FROM TRAINING WHERE Fee IS NULL;
iii. To display all the cities where Cyber Security training is scheduled along with its fee.
Ans. SELECT City, Fee FROM TRAINING WHERE Topic = 'Cyber Security';
iv. To add a column 'feedback' with suitable data type.
Ans. ALTER TABLE TRAINING ADD Feedback Varchar (20);
(b) Observe the table named "Training" given above carefully and predict the output of the following
queries:
i. SELECT City FROM TRAINING WHERE Topic = "Cyber Security";
Ans.
City
New Delhi
Faridabad
Guru gram

ii. SELECT COUNT(Training Id) FROM TRAINING WHERE Email Id LIKE '%gmail%';
Ans. 2
iii. SELECT AVG (Fee) FROM TRAINING WHERE Topic = "Cyber Security";
Ans. 11000
iv. SELECT Name FROM TRAINING WHERE INSTR (Email Id, '@')=0;
Ans. Ms. Neena
24. Consider the following tables GAMES and PLAYER. Write SQL commands and output for the following
statements.
Table: GAMES
GCode GameName Number PrizeMoney ScheduledDate
101 Kabaddi 2 5000 23-Jan-2007
102 Badminton 2 12000 12-Dec-2013
103 Table Tennis 4 8000 14-Feb-2014
105 Chess 2 9000 1-Jan-2015
108 Table Tennis 4 25000 19-Mar-2014

Table: PLAYER
PCode Name Gcode
1 Ravi Shankar 101
2 Amir 108
3 Jatin 101
4 Shahrukh 103
(a) To display details of those games which have PrizeMoney more than 7000.
Ans. SELECT * FROM GAMES WHERE PrizeMoney>7000;
(b) To display the content of the GAMES table in ascending order of ScheduledDate.
Ans. SELECT * FROM GAMES ORDER BY ScheduledDate;
(c) To display game names along with name of the players.
Ans. SELECT [Link], [Link] FROM GAMES G, Player P WHERE [Link]=[Link];
(d) SELECT COUNT (DISTINCT GameName) FROM GAMES;
Ans.
COUNT(DISTINCT GameName)
4
 (e) SELECT MAX(ScheduledDate), MIN(ScheduledDate) FROM GAMES;
Ans.

MAX(ScheduledDate) MIN(ScheduledDate)
01-Jan-2015 23-Jan-2007

25. Write SQL commands for statements (a) to (c).

Table: FACULTY

F_ID FNAME LNAME HIREDATE SALARY

102 MANOJ SABHARWAL 12-10-2012 12000
103 PARVEEN ARORA 15-12-2014 9000
104 SANJEEV SHARMA 01-01-2016 14000
105 RASHMI MALHOTRA 02-04-2000 20000
106 NITIN SRIVASTAVA 12-12-2013 10000

Table: COURSES

C_ID F_ID CNAME FEES

C21 102 BOOLEAN ALGEBRA 14000
C22 106 COMPUTER NETWORK 20000
C21 104 C++ 18000
C24 106 HUMAN BIOLOGY 25000
C25 102 BIOTECH 30000
C26 103 COMPUTER TECH 40000

(a) To display details of those faculty members whose salary is greater than 12000.
Ans. SELECT * FROM FACULTY WHERE SALARY> 12000;
(b) To display the details of courses whose fees is in the range of 15000 to 50000 (both values included).
Ans. SELECT * FROM COURSES WHERE FEES BETWEEN 15000 AND 50000;
(c) To display faculty first name and last name from the Faculty table and course name from the Courses
table whose fees is greater than 20000.
Ans. SELECT [Link],[Link],[Link],[Link] FROM FACULTY F,COURSES C WHERE
F. F ID=C.F ID AND [Link]>20000;
(d) SELECT COUNT (DISTINCT F ID) FROM COURSES;
Ans.

COUNT (DISTINCT F ID)

102
103
104
106
26. Given the table 'PLAYER' with the following columns: [CBSE 2018]

Table: PLAYER

PCODE POINTS
1 50
2 NULL
3 40

Write the output of the following statements:

(a) SELECT AVG(POINTS) FROM PLAYER;
Ans.

AVG(POINTS)
45
 (b) SELECT COUNT(POINTS) FROM PLAYER;
Ans.

COUNT(POINTS)
2
27. Kunal has entered the following SQL command on Table 'STUDENT' that has TotalMarks as one of the
columns: [CBSE 2017]
SELECT COUNT(*) FROM STUDENT;
The output displayed is 20.
Then, Kunal enters the following command:
SELECT COUNT(*) FROM STUDENT WHERE TotalMarks < 100;
The output displayed is 15.
Then, Kunal enters the following command:
SELECT COUNT(*) FROM STUDENT WHERE TotalMarks >= 100;
He predicts the output of the above query to be 5. Do you agree with Kunal? Give reasons for your answer.
Ans. Yes, Total rows=20, rows with TotalMarks <100 are 15, so remaining rows left are 20 — 15 = 5
Or
No, the output of the query may not always be 5 as there may be rows with Total Marks as NULL which
would not have been included in either of the two SELECT statements mentioned.
28. What is an ORDER BY clause and GROUP BY clause?
Ans. ORDER BY clause is used to display the result of a query in a specific order (sorted order).
The sorting can be done in ascending or descending order. However, the actual data in the database is
not sorted; only the results of the query are displayed in sorted order. If order is not specified then, by
default, the sorting will be performed in ascending order.

For example,
SELECT Name, City FROM STUDENT
ORDER BY Name;
The above query returns name and city columns of table student sorted by name in ascending order.

For example,
SELECT * FROM STUDENT ORDER BY City DESC;
It displays all the records of table student ordered by city in descending order.
GROUP BY clause
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group
the results by one or more columns.
For example,
SELECT Name, COUNT(*) AS "NUMBER OF STUDENTS" FROM STUDENT
WHERE Marks>350 GROUP BY City;
29. Consider the following tables Product and Client. Write SQL commands for the statements (a) to (c) and
give outputs for SQL queries (d) to (f).
Table: PRODUCT
P_ID Product Name Manufacturer Price

TP01 Talcum Powder LAK 40

FW05 Face Wash ABC 45

BS01 Bath Soap ABC 55

SHO6 Shampoo XYZ 120

FW12 Face Wash XYZ 95
 Table: CLIENT
C_ID Client Name City P _ID
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi SHO6
15 Pretty Woman Delhi FW12
16 Dreams Bengaluru TP01

(a) To display the details of those clients whose city is Delhi.

Ans. SELECT * FROM CLIENT WHERE City="DELHI";
(b) To display the details of products whose price is in the range of 50 to 100 (both values included).
Ans. SELECT * FROM PRODUCT WHERE Price BETWEEN 50 AND 100;
(c) To display the details of those products whose name ends with 'wash'.
Ans. SELECT * FROM PRODUCT WHERE Name LIKE '%wash';
(d) SELECT DISTINCT City FROM CLIENT;
Ans.

DISTINCT(City)
Delhi
Mumbai
Bengaluru
(e) SELECT Manufacturer, MAX(Price), MIN(Price), COUNT(*) FROM PRODUCT GROUP
BY Manufacturer;
Ans.

Manufacturer Max(Price) Min(Price) Count(*)

LAK 40 40 1
ABC 55 45 2
XYZ 120 95 2

(f) SELECT PRODUCTNAME, PRICE * 4 FROM PRODUCT;

Ans.

ProductName Price*4
TalcumPowder 160
FaceWash 180
BathSoap 220
Shampoo 480
FaceWash 380

30. What are aggregate functions?

Ans. Aggregate functions are those functions that return single values from groups of values. There are several
group functions such as SUM(), AVG(), MAX(), MIN(), COUNT(), etc.
31. What is a base table?
Ans. A base table is a table from which the values can be derived for other tables. For example, in case of
views, the values are extracted from the base table on which the view depends.
32. What is NULL value?
Ans. A NULL value in a table is a value in a field which is blank. In other words, a NULL value is an unidentified
or unavailable value and not equal to zero.
33. What is a NOT NULL constraint?
Ans. If you do not want a column to have a NULL value, then you need to define a constraint on this column
which specifies that NULL is now not allowed for that column.
 34. Consider the table STUDENT given below and give answers to any four questions from (i) to (v).

Table: SHOP
RoliNo Name Class DOB Gender City Marks
1 Anand XI 06-06-1997 M Agra 430
2 Chetan XII 07-05-1994 M Mumbai 460
3 Geet XI 06-05-1997 F Agra 470
4 Preeti XII 08-08-1995 F Mumbai 492
5 Saniyal XII 08-10-1995 M Delhi 360
6 Meena XI 12-12-1994 F Dubai 256
7 Neha X 08-12-1995 F Moscow 324
8 Nishant X 12-06-1995 M Moscow 429
(A) State the command that wil give the output as:

Name
Anand
Chetan
Geet
Preeti
(i)
SELECT Name FROM STUDENT WHERE Class='XI' AND Class='XII';
(ii)
SELECT Name FROM STUDENT WHERE NOT Class='XI' AND Class='XII';
(iii)
SELECT Name FROM STUDENT WHERE City="AGRA" OR City="MUMBAI";
(iv)
SELECT Name FROM STUDENT WHERE City IN("AGRA", "MUMBAI");
Choose the correct option:
(a) Both (i) and (ii).
(b) Both (iii) and (iv).
(c) Any of the options (i), (ii) and (iv)
(d) Only (iii)
Ans. (b) Both (iii) and (iv)
SELECT Name FROM STUDENT WHERE City="AGRA" OR City="MUMBAI";
and
SELECT Name FROM STUDENT WHERE City IN ("AGRA", "MUMBAI");
(B) Pinky has given the following command to obtain the highest marks class-wise:
SELECT MAX(Marks) FROM STUDENT WHERE GROUP BY Class;
but she is not getting the desired result. Help her by writing the correct command.
(a)SELECT MAX(Marks) FROM STUDENT WHERE GROUP BY Class;
(b)SELECT Class, MAX(Marks) FROM STUDENT GROUP BY Marks;
(c)SELECT Class, MAX(Marks) GROUP BY Class FROM STUDENT;
(d)SELECT Class, MAX(Marks) FROM STUDENT GROUP BY Class;
Ans.(d) SELECT Class, MAX(Marks) FROM STUDENT GROUP BY Class;
(C) State the command to display the average marks scored by students of each gender who are in Class Xl.
(a)
SELECT Gender, AVG (Marks) FROM STUDENT WHERE Class= "XI" GROUP BY Gender;
(b)
SELECT Gender, AVG (Marks) FROM STUDENT GROUP BY Gender WHERE Class="XI";
(c)
SELECT Gender, AVG (Marks) GROUP BY Gender FROM STUDENT HAVING Class="XI";
(d)
SELECT Gender, AVG (Marks) FROM STUDENT GROUP BY Gender HAVING Class = "XI";
Choose the correct option:
(a) Both (ii) and (iii) (b) Both (ii) and (iv)
(c) Both (i) and (iii) (d) Only (iii)
Ans. (b) Both (ii) and (iv)
SELECT Gender, AVG(Marks) FROM STUDENT GROUP BY Gender WHERE Class="XI";
Or
SELECT Gender, AVG(Marks) FROM STUDENT GROUP BY Gender HAVING Class = "XI";
 (D) Help Ritesh write the command to display the name of the youngest student.
(a) SELECT Name,MIN(DOB) FROM STUDENT;
(b) SELECT Name,MAX (DOB) FROM STUDENT;
(c) SELECT Name,MIN(DOB) FROM STUDENT GROUP BY NAME;
(d) SELECT Name,MAXIMUM (DOB) FROM STUDENT;
Ans. (b) SELECT NAME, MAX (DOB) FROM STUDENT;
(E) What will be the output of the following command?
SELECT * FROM STUDENT WHERE GENDER ="F" ORDER BY MARKS;
(a)
RoliNo Name Class DOB Gender City Marks
4 Preeti XII 08-08-1995 F Mumbai 492
3 Geet XI 06-05-1997 F Agra 470
7 Neha X 08-12-1995 F Moscow 324
6 Meena XI 12-12-1994 F Dubai 256

(b)
RoliNo Name Class DOB Gender City Marks
6 Meena XI 12-12-1994 F Dubai 256
7 Neha X 08-12-1995 F Moscow 324
3 Geet XI 06-05-1997 F Agra 470
4 Preeti XII 08-08-1995 F Mumbai 492

(c)
Gender Marks
F 256
F 324
F 470
F 492

(d)
Gender Marks
F 492
F 470
F 324
F 256
Ans. (b)

RoliNo Name Class DOB Gender City Marks

6 Meena XI 12-12-1994 F Dubai 256
7 Neha X 08-12-1995 F Moscow 324
3 Geet XI 06-05-1997 F Agra 470
4 Preeti XII 08-08-1995 F Mumbai 492
35. (a) Explain the concept of Cartesian product between two tables with the help of an example.
[CBSE D 2014]
Note: Answer questions (b) and (c) on the basis of the following tables SHOP and ACCESSORIES.
Table: SHOP
Id SName Area
501 ABC Computronics CP
S02 All Infotech Media GK II
S03 Tech Shoppe CP
SO4 Geek Tenco Soft Nehru Place
SO5 Hitech Tech Store Nehru Place
 Table: ACCESSORIES
No Name Price Id
A01 Mother Board 12000 SO1
A02 Hard Disk 5000 SO1
A03 Keyboard 500 SO2
A04 Mouse 300 SO1
A05 Mother Board 13000 SO2
A06 Keyboard 400 S03
A07 LCD 6000 SO4
108 LCD 5500 S05
109 Mouse 350 S05
T10 Hard Disk 4500 S03
Ans. (a) When you join two or more tables without any condition, it is called Cartesian product or Cross join.
Example — SELECT * FROM SHOP, ACCESSORIES;
(b) Write the SQL queries:
(i) To display name and price of all the accessories in ascending order of their price.
(ii) To display Id and SName of all shops located in Nehru Place.
(iii) To display minimum and maximum price of each name of accessories.
Ans. (i) SELECT NAME, PRICE FROM ACCESSORIES ORDER BY PRICE;
(ii) SELECT ID, SNAME FROM SHOP WHERE AREA=' NEHRU PLACE' ;
(iii) SELECT NAME, MAX (PRICE), MIN (PRICE) FROM ACCESSORIES;
(c) Write the output of the following SQL commands:
(I) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>=5000;
(ii) SELECT AREA, COUNT(*) FROM SHOP GROUP BY AREA;
(iii) SELECT COUNT(DISTINCT AREA) FROM SHOP;
(iv) SELECT NAME, PRICE*0.05 AS "DISCOUNT" FROM ACCESSORIES WHERE ID
IN('S02', 'S03');
Ans.
(i) Name
Mother Board
Hard Disk
LCD

AREA COUNT(*)
CP 2
GK II 1
Nehru Place 2

COUNT (DISTINCT area)

3

(iv) NAME DISCOUNT

Keyboard 25.00
Mother Board 650.00
Keyboard 20.00
Hard Disk 225.00
36. Consider the following tables CARDEN and CUSTOMER and answer (a) and (b) parts of the question:
Table: CARDEN
Ccode CarName Make Color Capacity Charges
501 A-Star Suzuki RED 3 14
503 Indigo Tata SILVER 3 12
502 lnnova Toyota WHITE 7 15
509 SX4 Suzuki SILVER 4 14
510 C Class Mercedes RED 4 35
 Table: CUSTOMER
CCode CName Ccode
1001 Hemant Sahu 501
1002 Raj Lal 509
1003 Feroza Shah 503
1004 Ketan Dhar 502

(a) Write SQL commands for the following statements:

(i) To display the names of all the silver-coloured cars.
(ii) To display name, make and capacity of cars in descending order of their sitting capacity.
(iii) To display the highest charges at which a vehicle can be hired from CARDEN.
Ans. (i) SELECT CarName FROM CARDEN WHERE Color LIKE ' SILVER' ;
(ii) SELECT CarName, Make, Capacity FROM CARDEN ORDER BY Capacity DESC;
(iii) SELECT MAX (Charges) FROM CARDEN;
(b) Give the output of the following SQL queries:
(i) SELECT COUNT (DISTINCT Make) FROM CARDEN;
(ii) SELECT MAX (Charges) , MIN (Charges) FROM CARDEN;
(iii) SELECT COUNT (*) , Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;
Ans.
(i) COUNT(DISTINCT Make)
4

MAX(Charges) MIN(Charges)
35 12

COUNT(*) Make
5 Suzuki

(iv) CarName
SX4
C Class

37. Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question:
[CBSE D 2010]
Table: STOCK
ItemNo Item Dcode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 31-Mar-10
5003 Ball Pen 0.25 102 150 20 01-Jan-10
5002 Gel Pen Premium 101 125 14 14-Feb-10
5006 Gel Pen Classic 101 200 22 01-Jan-09
5001 Eraser Small 102 210 5 19-Mar-09
5004 Eraser Big 102 60 10 12-Dec-09
5009 Sharpener Classic 103 160 8 23-Jan-09

Table: DEALERS
Dcode DName
101 Reliable Stationers
103 Classic Plastics
102 Clear Deals
(a) Write SQL commands for the following statements:
(i) To display details of all items in the Stock table in ascending order of StockDate.
(ii) To display ItemNo and Item name of those items from Stock table whose UnitPrice is more
than 10.
 (iii) To display the details of those items whose dealer code (Dcode) is 102 or quantity (Qty) is
more than 100 from the table Stock.
(iv) To display maximum. UnitPrice of items for each dealer individually as per Dcode from the
table Stock.
Ans. (i) SELECT * FROM STOCK ORDER BY StockDate;
(ii) SELECT ItemNo, Item FROM STOCK WHERE UnitPrice>10;
(iii) SELECT * FROM STOCK WHERE Dcode=102 OR QTY>100;
(iv) SELECT Dcode, MAX (UnitPrice) FROM STOCK GROUP BY Dcode;
(b) Give the output of the following SQL queries:
(i) SELECT COUNT (DISTINCT Dcode) FROM STOCK;
(ii) SELECT Qty*UnitPrice FROM STOCK WHERE ItemNo=5006;
(iii) SELECT MIN (StockDate) FROM STOCK;
Ans.
(i) COUNT (DISTINCT Dcode)
3

Qty*UnitPrice
4400

MIN (StockDate)
01-Jan-09
38. Consider the following tables ACTIVITY and COACH. Write SQL commands for the statements (a) to
(d) and give outputs for SQL queries (e) to (h)

Table: ACTIVITY
ACode ActivityName ParticipantsNum PrizeMoney ScheduledDate
1001 Relay 100x4 16 10000 23-Jan-2004
1002 High Jump 10 12000 12-Dec-2003
1003 Shot Put 12 8000 14-Feb-2004
1005 Long Jump 12 9000 01-Jan-2004
1008 Discus Throw 10 15000 19-Mar-2004

Table: COACH
PCode Name ACode
1 Ahmad Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003
(a) To display the name of all activities with their Acodes in descending order.
(b) To display sum of PrizeMoney for each of the number of participants groupings (as shown in column
ParticipantsNum 10,12,16).
(c) To display the coach's name and ACodes in ascending order of ACode from the table COACH.
(d) To display the content of the GAMES table whose ScheduledDate is earlier than 01/01/2004 in
ascending order of ParticipantsNum.
(e) SELECT COUNT(DISTINCT ParticipantsNum) FROM ACTIVITY;
(f) SELECT MAX(ScheduledDate),MIN(ScheduledDate) FROM ACTIVITY;
(g) SELECT SUM (PrizeMoney) FROM ACTIVITY;
(h) SELECT DISTINCT ParticipantsNum FROM COACH;
Ans. (a) SELECT ActivityName FROM ACTIVITY ORDER BY ACode DESC;
(b) SELECT SUM (PRIZEMONEY) FROM ACTIVITY GROUP BY ParticipantsNum HAVING
ParticipantsNum IN (10,12,16);
(c) SELECT Name, Acode FROM COACH ORDER BY ACode;
(d) SELECT * FROM ACTIVITY WHERE ScheduledDate>' 01/01/2004' ORDER BY
ParticipantsNum;
 (e)
COUNT(DISTINCT ParticipantsNum)
3

(f)
MAX( Scheduled Date) MIN(ScheduledDate)
19-Mar-2004 12-Dec-2003

(g)
SUM (PrizeMoney)
54000
(h)
DISTINCT ParticipantsNum
16
10
12
39. Consider the following relations MobileMaster & MobileStock:
Table: MOBILEMASTER
M_Id M_Company M_Name M_Price M _ Mf _Date
MB001 Samsung Galaxy 4500 2013-02-12
MB003 Nokia N1100 2250 2011-04-15
MB004 Micromax Unite3 4500 2016-10-17
MB005 Sony XperiaM 7500 2017-11-20
MB006 Oppo SelfieEx 8500 2010-08-21

Table: MOBILESTOCK
S_Id M_Id M_Qty M_Supplier
5001 MB004 450 New Vision
S002 MB003 250 Praveen Gallery
S003 MB001 300 Classic Mobile Store
5004 MB006 150 A-one Mobiles
S005 MB003 150 The Mobile
S006 MB006 50 Mobile Centre
Write the SQL query for questions (a) to (d) and write the output of SQL command for questions (e) to (g)
given below:
(a) Display the mobile company, mobile name and price in descending order of their manufacturing date.
(b) List the details of mobiles whose name starts with "S".
(c) Display the mobile supplier and quantity of all mobiles except "MB003".
(d) To display the names of mobile companies having price between 3000 and 5000.
(e) SELECT M Id, SUM (M Qty) FROM MOBILESTOCK GROUP BY M_Id;
(f) SELECT MAX (M Mf Date), MIN (M Mf Date) FROM MOBILEMASTER;
(g) SELECT AVG (M Price) FROM MOBILEMASTER;
Ans. (a) SELECT M Company, M_Name, M Price FROM MOBILEMASTER ORDER BY M Mf Date DESC;
(b) SELECT * FROM MOBILEMASTER WHERE M_Name LIKE "S%";
(c) SELECT M Supplier, M_Qty FROM MOBILESTOCK WHERE M_Id <> "MB003";
(d) SELECT M Company FROM MOBILEMASTER WHERE M Price BETWEEN 3000 AND 5000;
(e)
M_Id SUM(M_Qty)
M 8004 450
MB003 400
MB001 300
MB006 200
 (f)
MAX(M_Mf_Date) MIN(M_Mf_Date)
2017-11-20 2010-08-21

(g) 5450
40. (a) Name any one single row function and any one aggregate function. Also mention the difference in
their working.
Ans. Single row function: Length()
Aggregate function: MAX()
A single row function works on every row of the table and, hence, gives output for each row in the table.
An aggregate function, also known as multiple row function, works on a group of rows and returns only
one output.
(b) Explain why the following queries give different outputs on execution:
(i) SELECT COUNT (ENAME) FROM EMP;
Output: 5
(ii) SELECT COUNT (*) FROM EMP;
Output: 8
Ans. Aggregate functions ignore NULL values. The first query counts the Not Null values in the column ENAME,
hence the output is 5.
But the second query counts all the records present in the table EMP which gives the output as 8.
Hence, this implies that there are 8 rows in the table and the Ename column contains the names of only
5 employees. 8-5 = 3, i.e., 3 names have been left blank. This means that there are 3 null values in the
column ENAME.
41. A user intends to group the result set based on some column's value. Also, it is required that the grouped
result should appear in sorted order. In which order should the user write the two clauses (for sorting
and grouping)? Give an example to support your answer.
Ans. When we use GROUP BY clause (for grouping of data) and ORDER BY clause (for sorting data) together,
the ORDER BY clause always follows other clauses. That is, the GROUP BY clause will come before
ORDER BY clause.
For example,
SELECT EMP ID, SUM (SALARY) AS "ANNUAL SALARY" FROM EMPLOYEE
GROUP BY DEPTID
ORDER BY EMP ID DESC;
42. On the basis of the following table, answer the given questions: [CBSE Sample Paper 2020]

Table: CUSTOMER DETAILS

I Cust_ID I Cust_Name I Acct_Type I Accumlt_Amt I DOJ I Gender I

CNR_001 I Manoj Saving I 101250 I 1992-02-19 I M

CNR_002 I Rahul I Current I 132250 I 1998-01-11 I M
CNR_004 I Steve I Saving I 18200 1998-02-21 I M
CNR_005 I Manpreet I Current I NULL I 1994-02-19 I M

(i) Write the degree and cardinality of the above table.

Ans. The degree is 6 and cardinality is 4.
(ii) What will be the output of the following query:
SELECT MAX (DOJ) FROM CUSTOMER DETAILS;
Ans. +
MAX(DOJ)

I 1998-02-21 I
 (iii) Write the SQL query to delete the row from the table where customer has no accumulated
amount.
Ans. DELETE FROM Customer Details WHERE Accumlt Amt IS NULL;
43. Write commands in SQL for (a) to (d) and output for (e) and (f).
Table: Store

Storeld 1 Name 1 Location I City NoOfEmp DateOpen 1 SalesAmt

5101 I Planet Fashion I Bandra I Mumbai 1 7 1 2015-10-16 1 40000 I

S102 I Vogue 1 Karol Bagh 1 Delhi 18 I 2015-07-14 1 120000
S103 I Trends Powai Mumbai 1 10 I 2015-06-24 1 30000
S104 I Super Fashion I Thane I Mumbai 1 11 I 2015-02-06 1 45000
S105 I Annabelle I South Extn. 1 Delhi I 8 I 2015-04-09 I 60000
S106 1 Rage I Defence Colony 1 Delhi I5 1 2015-03-01 1 20000

(a) To display names of stores along with SalesAmount of those stores that have 'fashion' anywhere in
their store names.
(b) To display names of the cities and the total amount of sales on the basis of City.
(c) To display the date of opening of the oldest store.
(d) To display the City and the number of stores located in that City, only if number of stores is more
than 2.
(e) SELECT MIN (DateOpen) FROM STORE;
(f) SELECT COUNT (StoreId) , NoOfEmp FROM STORE GROUP BY NoOfEmp HAVING
MAX (SalesAmt)<60000;
Ans. (a) mysql> SELECT Name, SalesAmt FROM STORE WHERE Name LIKE '%fashion%';
(b) mysql> SELECT City, SUM(SalesAmt) FROM STORE GROUP BY City;
(c) mysql> SELECT MIN (DateOpen) FROM STORE;
(d) mysql> SELECT COUNT(*) , City FROM STORE GROUP BY City HAVING COUNT (*) >2;
(e) +
1 MIN(DateOpen) I

1 2015-02-06 1

(f) +
1 COUNT(Storeld) I NoOfEmp 1

I1 110
I1 1 11
I5
I7

UNSOLVED QUESTIONS
1. What are single row and multiple row functions?
2. What is the significance of GROUP BY clause in an SQL query?
3. What is the difference between WHERE and HAVING clause in SQL select command?
4. Write a query to find out the sum, average, lowest and highest marks in Student table.
5. Write a query to find out the sum, average, lowest and highest marks of the students in Student table
grouped by STREAM.
 6. Consider the table "Item" given below and give the outputs on the basis of it:
Table: ITEM
Itemno !name Price (Z) Quantity
101 Soap 50 100
102 Powder 100 50
103 Facecream 150 25
104 Pen 50 200
105 Soapbox 20 100

(a) SELECT SUM(Price) FROM ITEM;

(b) SELECT AVG(Price) FROM ITEM;
(c) SELECT MIN(Price) FROM ITEM;
(d) SELECT MAX(Price) FROM ITEM;
(e) SELECT COUNT (Price) FROM ITEM;
(f) SELECT DISTINCT Price FROM ITEM;
(g) SELECT COUNT (DISTINCT Price) FROM ITEM;
(h) SELECT Iname, Price*Quantity FROM ITEM;
7. Define a function. Why are they useful?
8. Write commands to display the system date.
9. Write a command to display the name of the current month.
10. Write a command to print the day of the week of your birthday in the year 1999.
11. What is the difference between SYSDATE() and NOW() function?
12. Consider two fields—B_date, which stores the birth date, and J_date, which stores the joining date of an
employee. Write commands to find out and display the approximate age of an employee as on joining
date.

13. Write a query to find out the number of students in each Stream in STUDENT table.
14. Consider the given table Faculty and answer the questions that follow:
Table: FACULTY
F_ID F_Name L_Name Hire_date Salary
102 Amit Mishra 12-10-1998 10000
103 Nitin Vyas 24-12-1994 8000
104 Rakshit Soni 18-05-2001 14000
105 Rashmi Malhotra 11-09-2004 11000
106 Sulekha Srivastava 05-06-2006 10000

(a) To display the details of those Faculty members whose salary is higher than 12000.
(b) To display the details of Faculty members whose salary is in the range of 8000 to 12000 (both values
included).
(c) Count the number of different id from faculty.
(d) Count the number of faculty members getting salary as 10000.
(e) Display details of those faculty members whose names start with S.
(f) Display all records in descending order of Hire date.
(g) Find the maximum and the minimum salary.

15. Consider the following EMP and DEPT tables:

Table: EMP

EmpNo EmpName City Designation DOJ Sal Comm DeptID

8369 SMITH Mumbai CLERK 1990-12-18 800.00 NULL 20

8499 ANYA Varanasi SALESMAN 1991-02-20 1600.00 300.00 30

8521 SETH Jaipur SALESMAN 1991-02-22 1250.00 500.00 30
8566 MAHADEVAN Delhi MANAGER 1991-04-02 2985.00 NULL 20
 Table: DEPT

DeptID DeptName MgrID Location

10 SALES 8566 Mumbai
20 PERSONNEL 9698 Delhi
30 ACCOUNTS 4578 Delhi
40 RESEARCH 8839 Bengaluru

Write the SQL command to get the following:

(a) Show the minimum, maximum and average salary of managers.
(b) Count the number of clerks in the organization.
(c) Display the designation-wise list of employees with name, salary and date of joining.
(d) Count the number of employees who are not getting commission.
(e) Show the average salary for all departments with more than 5 working people.
(f) List the count of employees grouped by DeptID.
(g) Display the maximum salary of employees in each department.
(h) Display the name of employees along with their designation and department name.
(i) Count the number of employees working in ACCOUNTS department.
16. Write a MySQL command for creating a table PAYMENT whose structure is given below:
Table: PAYMENT
Field Name Datatype Size Constraint
Loan_number Integer 4 Primary key
Payment_number Varchar 3
Payment_date Date
Payment_amount Integer 8 Not Null

17. Consider the table Product shown below:

Table: PRODUCT
P_ID ProductName Manufacturer Price
P001 Moisteurizer XYZ 40
P002 Sanitizer LAC 35
P003 Bath Soap COP 25
P004 Shampoo TAP 95
P005 Lens Solution COP 350

Write the commands in SQL queries for the following:

(a) To display the details of product whose price is in the range of 40 and 120 (both values included).
(b) To increase the price of all the products by 20.
18. Consider the table RESULT given below. Write commands in MySQL for (a) to (d) and output for
(e) to (g):
Table: RESULT
No Name Stipend Subject Average Division
1 Sharon 400 English 38 THIRD
2 Amal 680 Mathematics 72 FIRST
3 Vedant 500 Accounts 67 FIRST
4 Shakeer 200 Informatics 55 SECOND
5 Anandha 400 History 85 FIRST
6 Upasna 550 Geography 45 THIRD

(a) To list the names of those students who have obtained Division as FIRST in the ascending order of
NAME.
(b) To display a report listing NAME, SUBJECT and Annual stipend received assuming that the stipend
column has monthly stipend.
 (c) To count the number of students who have either Accounts or Informatics as subject.
(d) To insert a new row in the table EXAM: 6, "Mohan", 500, "English", 73, "Second"
(e) SELECT AVG (STIPEND) FROM EXAM WHERE DIVISION = "THIRD";
(f) SELECT COUNT (DISTINCT Subject) from EXAM;
(g) SELECT MIN (Average) FROM EXAM WHERE Subject = "English";
19. Write the SQL query commands based on the following table:
Table: SCHOOLBUS

Rtno Area_Covered Capacity NoOfStudents Distance Transporter Charges

1 Vasant Kunj 100 120 10 Shivam Travels 100000
2 Hauz Khas 80 80 10 Anand Travels 85000
3 Pitampura 60 55 30 Anand Travels 60000
4 Rohini 100 90 35 Anand Travels 100000
5 Yamuna Vihar 50 60 20 Bhalla Co. 55000
6 Krishna Nagar 70 80 30 Yadav Co. 80000
7 Vasundhra 100 110 20 Yadav Co. 100000
8 Paschim Vihar 40 40 20 Speed Travels 55000
9 Saket 120 120 10 Speed Travels 100000
10 Janak Puri 100 100 20 Kisan Tours 95000

(a) To show all information of students where capacity is more than the number of students in order of
rtno.
(b) To show area_covered for buses covering more than 20 km, but charge is less than 80000.
(c) To show transporter-wise total no. of students travelling.
(d) To show rtno, area_covered and average cost per student for all routes where average cost per
student is — charges/no. of students.
(e) Add a new record with the following data:
(11, "Moti Bagh", 35,32,10, "Kisan Tours", 35000)
(f) Give the output considering the original relation as given below:
(i) SELECT SUM (Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";
(ii) SELECT MIN (NoOfStudents) FROM SCHOOLBUS;
(iii) SELECT AVG (Charges) FROM SCHOOLBUS WHERE Transporter= "Anand Travels";
(iv) SELECT DISTINCT Transporter FROM SCHOOLBUS;
20. Answer the questions based on the table given below:
Table: STUDENT
Column Name Data Type Size Constraint
Roll_No NUMBER 4 PRIMARY KEY
Name VARCHAR 20 Not Null
Stipend NUMBER 7 Stipend is greater than 0
Stream VARCHAR 15 Not Null
Grade VARCHAR 1

(a) Write the SQL command to create the above table with constraints.
(b) Insert 2 records with relevant information in the table Student.
(c) Display all the records of the table Student.
(d) Delete the student whose Roll no is 100.
(e) Change the stream of student to 'Computer' whose Roll no. is 536.
(f) Add one column email of data type VARCHAR and size 30 to the table Student.
(g) View structure of the table created by you.
(h) Drop the table Student.
21. Consider the CUSTOMERS table having the following records:
Table: CUSTOMERS
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Bengaluru 4500.00
7 Muffy 24 Indore 10000.00

(a) Write an SQL query to display all records in ascending order of name.
(b) Write an SQL query to display all records in descending order of name.
(c) Write an SQL query to display all records in ascending order of name and descending order of age.
(d) Write an SQL query to display maximum salary.
(e) Write an SQL query to display minimum salary.
(f) Write an SQL query to display total number of records.
(g) Write an SQL query to display average salary.
(h) Write an SQL query to display total salary of all the persons.
(i) Write an SQL query to display names of those persons whose salary is greater than the average
salary.
(i) Write an SQL query to display details of those persons whose age is less than the average age.
22. Consider the following tables WORKER and PAYLEVEL and answer the questions:
Table: WORKER
ECODE NAME DESIG PLEVEL D0.1 DOB
11 Sachin Patel Supervisor P001 13-Sep-2004 23-Aug-1985
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-200613
1 -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

(a) To display details of all workers in descending order of DOB.

(b) To display the PLEVEL and number of workers in that PLEVEL.
(c) To display the PLEVEL and number of workers in that PLEVEL whose pay is greater than 15000.
(d) To display NAME and DESIG of those workers, whose PLEVEL is either P001 or P002.
(e) 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;
23. Consider the tables given below and answer the questions that follow:

Table: EVENT
Eventld Event NumPerformers CelebritylD
101 Birthday 10 C102
102 PromotionParty 20 C103
103 Engagement 12 C102
104 Wedding 15 C104
 Table: CELEBRITY
CelebritylD CelebrityName Phone FeeCharged
C101 Faiz Khan 99101XXXXX 200000
C102 Sanjay Kumar 89346XXXXX 250000
C103 Neera Khan Kapoor 98116XXXXX 300000
C104 Reena Bhatia 70877XXXXX 100000

(a) Name the Primary keys in both the tables and the Foreign key in Event' table. Can NumPerformers
(Number of performers) be set as the Primary key? Give reason.
(b) How many rows will be present in the Cartesian join of the abovementioned two tables?
(c) Write the commands in SQL:
(i) To display EventId, Event name, Celebrityld for only those events that have more than
10 performers.
(ii) To display Celebrityld and names of celebrities who have "Khan" anywhere in their names.
(iii) To display names of celebrities and fee charged for those celebrities who charge more
than 200000.
24. Consider the following structure of TEACHER and STUDENT table:
Table: TEACHER
TeacherlD TName City Subject Qualification Designation Pay

Table: STUDENT
StdID Name FName Stream TeacherlD

Write the SQL commands to get the following:

(a) Show the name of students enrolled in Science stream.
(b) Count the number of students in Commerce stream.
(c) Count the number of teachers in each designation.
(d) Display the maximum pay of teacher who is teaching English.
(e) Display the names of students who are taught by "Anand Mathur".
(f) Display the names and designations of teachers who are teaching a student named "Amit".
(g) Find out the name of the teacher who is getting the highest pay.
(h) Find out the cities of teachers who are teaching Maths.
(i) Find out the name of teacher who is getting the lowest salary among PGTs.
(j) Display the list of students who are taught by PGTs only.

25. Consider the following table and answer the questions that follow:

Table: TEACHER
ID Name Department Hiredate Category Gender Salary
1 Tanya Nanda SocialStudies 1994-03-17 TGT F 25000
2 Saurabh Sharma Art 1990-02-12 PRT M 20000
3 Nandita Arora English 1980-05-16 PGT F 30000
4 James Jacob English 1989-10-16 TGT M 25000
5 Jaspreet Kaur Hindi 1990-08-01 PRT F 22000
6 Disha Sehgal Math 1980-03-17 PRT F 21000
8 Sonali Mukherjee Math 1980-11-17 TGT F 24500

Write the command/output for the following:

(a) To display all information about the teacher of PGT category.
(b) To list the names of female teachers of Hindi department.
(c) To list names, departments and date of hiring of all the teachers in ascending order of date of joining.
(d) To count the number of teachers in English Department.
(e) Display the department and hire date of all the female teachers whose salary is more than 25000.
(f) Display the list of teachers whose name starts with J.
(g) SELECT COUNT (*) FROM TEACHER WHERE Category= ' PGT ' ;
(h) SELECT AVG (Salary) FROM TEACHER GROUP BY Gender;
26. Write SQL commands and the output for the following queries:
Table: SPORTS
StudentNo Class Name Gamel Gradel Game2 Grade2
10 7 Sameer Cricket B Swimming A
11 8 Sujit Tennis A Skating C
12 7 Kamal Swimming B Football B
13 7 Veena Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A Athletics C
(a) Display the names of the students who have grade 'A' in either Game1 or Game2 or both
(b) Display the number of students having game 'Cricket'.
(c) Display the names of students who have the same game for both Game1 and Game2.
(d) Display the games taken by the students whose name starts with 'A'.
(e) Give the output of the following sql statements:
(i) SELECT COUNT (*) FROM SPORTS;
(H) SELECT DISTINCT CLASS FROM SPORTS;
(Hi) SELECT MAX (Class) FROM SPORTS;
(iv) SELECT COUNT(*) FROM SPORTS GROUP BY Gamel;
27. Consider the following table:
Table: ITEM
Reim() Iname Price Quantity
101 Soap 50 100
102 Powder 100 50
103 Facecrea m 150 25
104 Pen 50 200
105 Soapbox 20 100
Write queries based on the table ITEM:
(a) Display the information of all the items.
(b) Display item name and price value.
(c) Display soap information.
(d) Display the item information whose name starts with letter 's'.
(e) Display a report with item number, item name and total price (total price = price * quantity).
(f) SELECT DISTINCT PRICE FROM ITEM;
(g) SELECT COUNT (DISTINCT Price) FROM ITEM;
28. Write the output produced by the following SQL commands:
(a) SELECT POW (2,3);
(b) SELECT ROUND (123.2345, 2) ,ROUND (342.9234, -1);
(c) SELECT LENGTH ("Informatics Practices");
(d) SELECT YEAR("1979/11/26"), MONTH ("1979/11/26"), MONTHNAME ("1979/11/26"),
DAY ("1979/11/26") ;
(e) SELECT LEFT ("INDIA", 3) , RIGHT ("Computer Science", 4) ;
(f) SELECT MID ("Informatics", 3,4) SUBSTR ("Practices", 3);
(g) SELECT CONCAT ("You Scored", LENGTH ("123") , "rank");
(h) SELECT ABS (-67.89);
(i) SELECT SQRT (625) + ROUND (1234.89,-3);
(j) SELECT MOD (56,8);
CASE-BASED/SOURCE-BASED INTEGRATED QUESTIONS
ABC Associates has over 2000 employees on its roll and deals with customer support services. Help the
company to perform associated operations for calculating the salaries of their employees and to perform
relevant analysis on the fetched data from the SQL database/table.
1. How to retrieve records where sal between 1000 to 2000?
Ans. SELECT * FROM EMP WHERE SAL>=1000 AND SAL<2000;
2. Select all records where dept no of both emp and dept table matches.
Ans. SELECT * FROM EMP WHERE EXISTS (SELECT * FROM DEPT WHERE [Link]=DEPT.
DEPTNO) ;
3. If there are two tables emp1 and emp2, and both have common records, how can we fetch all the
records but common records only once?
Ans. (SELECT * FROM EMP) UNION (SELECT * FROM EMP1)
4. How to fetch only common records from two tables emp and emp1?
Ans. (SELECT * FROM EMP) INTERSECT (SELECT * FROM EMP1)
5. How can we retrieve all records of emp1 that are not present in emp2?
Ans. (SELECT * FROM EMP) MINUS (SELECT * FROM EMP1)
6. Count the totalsalary deptno wise where more than 2 employees exist.
Ans. SELECT DEPTNO, SUM (SAL) AS ' TOTALSAL'
FROM EMP
GROUP BY DEPTNO
HAVING COUNT (EMPNO) > 2;
7. Suppose there is annual salary information provided by emp table. How can we fetch monthly salary of
each and every employee?
Ans. SELECT ENAME, SAL/12 AS MONTHLYSAL FROM EMP;
8. Select all records from emp table where deptno=10 or 40.
Ans. SELECT * FROM EMP WHERE DEPTN0=30 OR DEPTN0=10;
9. Select all records from emp table where deptno=30 and sal>1500.
Ans. SELECT * FROM EMP WHERE DEPTN0=30 AND SAL>1500;
10. Count MGR and their salary in emp table.
Ans. SELECT COUNT (MGR), COUNT (SAL) FROM EMP;
11. To display all the employees grouped together on the basis of deptno and sal in descending order.
Ans. SELECT ENAME, DEPTNO, SAL FROM EMP ORDER BY DEPTNO, SAL DESC;