Class XII CS - SQL – Practice Sample Paper 5

(2025-26)
Max Marks: 45
1) Amisha Dutta a student of class XII commerce has created the following
Cricketer table showing the batting records of Indian players in ‘One
Day International’ cricket matches. Write SQL commands for (i) to
(iv), and give output for (v) and (vi) on the basis of the table Cricketer given
below: (5)
Table : CRICKETER
PName Matche Inning Notout Run Highes Averag Hundred Fiftie
s s s s t e s s
MS Dhoni 280 243 66 893 183 50.48 8 61
6
Virat Kohli 176 16887 25 757 183 52.93 26 38
0
R Jadeja 126 147 NULL 184 87 32.43 0 10
9
Rohit Sharma 153 73 23 513 264 41.37 10 29
1
Sikhar 74 58 3 307 137 43.97 9 17
Dhawan 8
R Ashwin 102 40 NULL 658 65 16.45 0 1

(i) To show all the details of the players having scored hundreds and not
played matches less than 150.

(ii) To display player’s name, matches played, runs scored of those players
who have remained not out and scored more than 5000 runs.

(iii) To list player name, runs and average in descending order of their
highest score.
(iv) To count the number of players who have scored both hundreds and
fifties.
(v) SELECT PNAME, INNINGS, NOTOUTS, INNINGS – NOTOUTS “TOTAL OUTS”
FROM CRICKETER WHERE RUNS BETWEEN 3078 AND 7570;
(vi) SELECT PNAME, RUNS, AVERAGE FROM CRICKETER WHERE FIFTIES >
(SELECT MAX(HUNDREDS) FROM PLAYER);

2) Mr. Ramdin runs his medical store and he has created table STORE with
Ayurvedic medicines. Write command of SQL for (i) to (xii) and output for
(xiii) to (xv). (15)
 (i) To display the details of all the items which are priced more than 100.
(ii) To display the names of all the medicines with the type Syrup.
(iii) To display the name of all the medicines by the manufacturer
Dindayal.
(iv) To show the price with medicine name in descending order of their
price.
(v) To add a new row for product with the details : E4992, Kaya Kalp,
Patanjali, Churna, 1st Dec 2002, 31st March 2006, 60.
(vi) To display the details of products of type Oil and Ark.
(vii) To display the name of the medicine along with its price for those
medicines with manufacturing data before 30th June 2014.
(viii) To display the latest manufactured date and earliest expiry date
from the table
(ix) To display the different company names available in the table without
duplication.
(x) To display the Medicine type and Total price of medicines in each
medicine type.
(xi) To display the Medicine Name and Date of Expiry in which price is not
available.
(xii) To display the details of the items in which Medicine Name start or
end with K.
(xiii) Select * from store where company like “%an%”
(xiv) Select Count(distinct company) from store;
(xv) Select Type, max(price) from store group by type;

3 a) Consider the following two tables Player and Coach and write MySQL
Queries for (i) to (iv) and output(s) for (vi) and (vi).
(6)
Table: Player
Code Name Game Cost
P02 Ankit Chess 12000
P04 Rahul Carom 18000
P07 Dhirendr Cricket 22000
a
P03 Himansh Chess 15000
u
Table: Coach
Ch_No Ch_Name Ch_Addres JoinDate Cod
s e
1 Mr Dutta Rajkot 2002-06- P07
22
2 Mrs Jamuna Ahmedaba 2004-10- P02
 d 03
3 Mr Kumar Kolkata 1992-08- P04
16
4 Mrs Sonya Ahmedaba 2000-05- Null
d 31
5 Mr Sen Rajkot 2007-01- P07
18
(i) Display the Name of the Player and Coach Name of the corresponding
matched Code from the above tables.
(ii) Display the Player Name, Game and Coach Address of all the Female
Coaches.
(iii) Display the Player ID, Player Name, Coach Name and their Join Date for
those players cost is more than Rs. 18,000.
(iv) Increase the cost of the Player who is coached by Mr. Kumar and Mr. Sen
by 10%.
(v) SELECT PLAYER.CODE, PLAYER.NAME, COACH.CH_ADDRESS,
COACH.JOINDATE FROM PLAYER, COACH WHERE
PLAYER.CODE=COACH.CODE AND PLAYER.NAME LIKE “%AN%”;
(vi) SELECT PLAYER.NAME, PLAYER.COST, COACH.CH_NAME FROM PLAYER,
COACH WHERE PLAYER.CODE=COACH.CODE
AND COACH.CH_ADDRESS NOT IN (“RAJKOT”, “AHMEDABAD”);

3 b) Based on the above two tables answer the following:

(4)
(i) What will be the Degree of Cartesian Join of above tables?
(ii) What will be the Cardinality of Equi-Join of above tables?
(iii) What will be the Degree and Cardinality of Non-Equi Join of above
tables?
(iv) What are the Primary Keys and Foreign Key of above tables?

4) In a database there are two tables “Books” and “Issued”. Write the
Queries for (i) to (vi) and output(s) for (vii) to (viii). Also answer (ix) and
(x). (10)
 (i) Display the Book Name and Quantity Issued from the above tables
with corresponding matched records.
(ii) Display the Author Name, Publisher Name and Price of all the books
for those books with quantity issued is less than 5.
(iii) Display the Book ID, Book Type and Quantity Issued from the above
tables of EPB Publishers.
(iv) Display the Book Name and Quantity Issued of all the books which
are priced in the range 500 and 700. (Both values inclusive)
(v) Display the details of all the books along with the issued details in
which Quantity is more than 25.
(vi) Increase the Quantity Issued by 3 for those books written by the
authors whose name consists of RO.
(vii) Select Books. Book_Name, Books.Author_Name,
Issued.Quantity_Issued from Books, Issued where Books.Bk_Id =
Issued.Bk_Id;
(viii) Select Books.Author_Name, Books.Qty from Books, Issued where
Books.Bk_ID=Issued.Bk_ID and Issued.Quantity_Issued > 3;
(ix) Identify the Primary and Foreign Key from the above tables.
(x) What will the degree and cardinality of Cartesian Join and Equi Join
of above two tables.
5) Consider the tables Trainee & Trainer given below:
(5)
Trainee
Trainee_ID Trainee Gender Trainer_ID
TE001 Pawan Male TR005
TE002 Radhika Female TR004
TE009 Ankit Male TR004
TE007 Abhishek Female TR002
TE005 Vishnu Male TR001
Trainer
Trainer_ID Trainer Sports
TR005 Mr Gupta Carom
TR004 Mr Chaudhary Football
TR003 Mr Singh Carom
TR002 Mr Das Cricket
TR001 Mr Sen Football
Based upon above given tables, write commands in SQL for (i) and (ii) and
output for (iii) given below:
(i) Display the Trainee Name, Gender and corresponding Trainer Name
for all Trainee.
(ii) Display the Trainee Name and Trainer Name of all Trainees.

(iii) Select Trainne, Gender from Trainee, Trainer

where Trainee.Trainer_id = Trainer.Trainer_id and sports =
“carom”;

All the Best