We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
= DATABASE QUERY USING 50 wi
HO. (a) Write outputs for SQL queries (i) to (ii), which are based on the
wnte $C di ant | ,
A x A ANTS
(cHsE D 2020C)
Table: PR HCIPANT
- Le - |
“ANY | “001-12 |
penate AM | | 20at-12:25
| te SeRUTT | 10 | 2003-11-10
{ - MEHER: | 2 | 2001-11-10
| QUIZ SAKSHI u | 2002-10-12
| Quiz RITESH | 12 200. 10-12 |
juz RAHUL 10 | 2ecs-012 |
| | SROSSWORD, AMEER i 2002-05-09 |
{CROSSWORD | :
() To display details of all PARTICIPANTS of CLASS 10 and 12.
(i) Te display the SNAME and CLASS of all PARTICIPANTS in ascending order of their
(ii b dpay the number of PARTICIPANTS along with their respective CLASS, of every
(jo) SELECT DISTINCT EVENT FROM PARTICIPANTS;
(0) SELECT MAX(DOB), PNO FROM PARTICIPANTS GROUP BY
PNO HAVING COUNT(*) > 15
Ans.
(SELECT * FROM PARTICIPANTS WHERE CLASS IN(1@, 12);
OR
SELECT * FROM PARTICIPANTS WHERE CLASS = 10 OR CLASS = 12;
(ii) SELECT SNAME, CLASS FROM PARTICIPANTS ORDER BY SNAME;
(iil) SELECT COUNT(*), CLASS FROM PARTICIPANTS GROUP BY CLASS;
(iv) DISTINCT EVENT (vo) MAX(D0B)
‘CROSSWORD 2003-11-16
DEBATE
quiz
following. tables
CUSTOMERS and PURCHASES :
Table : CUSTOMERS
NO CNAME cries
a SANYAM DELHI
a SHRUTI DELHI
G MEHER ‘MUMBAI
a SAKSHI CHENNAI
GS RITESH INDORE
C6 RAHUL DELHI
a AMEER CHENNAI
3 MINAKSHI | BANGALORE
o ANSHUL MUMBAI
|
;
i ®
2018-12-25
2018-11-10
2018-11-10
2019-01-12
2019-02-12
2018-10-12
2019-05-09
2019-05-09
2018-05-09
2018-11-12
2018-08-04
Segeaees
(i) SELECT COUNT (DISTINCT CITIES) FROM CUSTOMERS;
(ii) SELECT MAX(PUR_DATE) FROM PURCHASES ;
(iii) SELECT CNAME, QTY, PUR_DATE FROM CUSTOMERS, PURCHASES
WHERE CUSTOMERS . CNO = PURCHASES .CNO AND QTY IN (10,20);
Write SQL queries for (i) to (i), which are based on the tables : CUSTOMERS an
PURCHASES given in part (a) :
() To display details of all Cl
Mumbai
(i) To display the CNAME and CITIES of all CUSTOMERS in ascending
order of their CNAME.
(ii) To display the number of CUSTOMERS along with their respective CITES
in each of the CITIES.
(jv) To display details of all PURCHASES whose Quantity is more than 15.
ICBSE D 20¢]
‘USTOMERS whose CITIES are neither Delhi nor
‘Ans. (a) (i) COUNT(DISTINCT CITIES)
(ii)
(iii)
@)
@
)
(iii)
Gv
5
MAX(PUR_DATE)
2019-05-89
CNAME = QTY PUR_DATE
SANYAM 10 2018-11-10
RAHUL = 10 2018-10-12
MEHER = 20 2019-05-09
SELECT * FROM CUSTOMERS WHERE CITIES NOT IN( "DELHI" , 'MUMBAZ");
oR
‘SELECT * FROM CUSTOMERS WHERE CITIES<>'DELHI' AND CITIES<>' MUM MBAL j
‘SELECT CNAME, CITIES FROM CUSTOMERS ORDER BY CNAME ;
‘SELECT COUNT(*), CITIES FROM CUSTOMERS GROUP BY CITIES;
‘SELECT * FROM PURCHASES WHERE QTY > 15; UNIT bat
agi. Consider the following tables ACTIVITY and COACH and
; and answer
fon
Table : ACTIVITY
E QUERY USING SaL
123
the following parts of this
{CBSE Sample Paper 1, 12]
‘ActivityName | stodium err Rye es
+ Portic
[joo | Relay 100 x4 | Star Annex enti | pony I scheduledote_
| 1002 | High jump Star Annex 7 10000 2eJan-O4
1003 | Shot Put Super Power e aa 12Dec03 |
4005 | Long Jump | Star Annex 2 000 nen |
|_ 1008 _| Discuss Throw _| Super Power 10 15000 isin
2 J
Table : COACH
Poode Name ‘code
1 Ahmad Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003
Give the output of the following SQL queries
ACTIVITY ;
() SELECT COUNT (DISTINCT ParticipantsNum) FROM
(ii) SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM ACTIVITY ;
(ii) SELECT Name, ActivityName FROM ACTIVITY A, COACH C
WHERE [Link] = [Link] AND A ParticipantsNum
(iv) SELECT DISTINCT ParticipantsNum FROM ACTIVITY.
=10;
Ans.
@ 3
(ii) max(ScheduleDate) MIN(ScheduleDate)
19-Mar-04 12-Dec-03
(iii) Name ACTIVITYNAME
Ravinder Discuss Throw
(iv) 16
10
12 a
i YER and answer the following parts of this
442. Consider the following tables GAMES and PLA’ Fhe lon Se
question
Table : GAMES
y | ScheduleDate _|
‘Carom Board Indoor aoa
Badminton Outdoor sane 2001
n
Table Tennis a eae
Chess Se
Lawn Tennis_|_ Outdoor _T 19-Mar-2004 MOVE FAST WITH INFORMATICS PRACTICES ~ Xi
14
Nabi Ahmad
Ravi Sahai
Jatin
Nazneen _|
Give the output of the following SQL queries
() SELECT COUNT (DISTINCT Number) FROM GAMES ;
(ii) SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM GAMES ;
(ii) SELECT Name, GameName FROM GAMES G, PLAYER P
WHERE [Link] = [Link] AND [Link] > 10000 ;
(jv) SELECT DISTINCT GCode FROM PLAYER.
‘Ans. (i) 2
(ii) MAx(Schedulepate) MIN(ScheduleDate)
19-Mar-2004 12-Dec-2003
(ii) Ravi Sahai Lawn Tennis
(iv) 10
108
103
443. Consider the following tables CABHUB and CUSTOMER and answer the following parts of
this question : (CBSE D 12)
Table : CABHUB
Veode | VehicleName Hoke Color Copacity Charges
100 Innova Toyota WHITE 7 15
| 102 Sx Suzuki BLUE 4 u
| 104 C Class Mercedes RED 4 3
| 105 AStar ‘Suzuki WHITE 3 4
| 108 | indigo Tata SILVER 3 nl
Table : CUSTOMER
Hemant Sahu 101
2 Raj Lal 108
3 Feroza Shah 105
4 Ketan Dhal 104
Give the output of the following SQL queries
(i) SELECT COUNT (DISTINCT Make) FROM CABHUB ;
(ii) SELECT MAX(Charges), MIN(Charges) FROM CABHUB ;
(iii) SELECT COUNT(*), Make FROM CABHUB ;
(iv) SELECT VehideName FROM CABHUB WHERE Capacity = 4. UNIT DATABASE QUERY USING SOL 125
Ans.
a 4
jy 3 2
(ii) Invalid query
(jo) 8x4
cClass
: jer the following tables C. |
wa ah ing tables CARDEN and CUSTOMER and answer the following parts of
{CBSE OD 12)
_ Table : CARDEN
{ceode | Cartome | Make Be]
| Color | Capac Charges
a) A-Star Suzuki | RED 3 ~ “|
| | nig | Tata SILVER 3 |
| 502 Innova Toyota WHITE 7 15 |
509 Sx4 ‘Suzuki SILVER 4 cy
| s10__| Class Mercedes _| RED 4 35.
Table : CUSTOMER
Code Crome Goode
1001 Hemant Sahu 501
1002 Raj Lal 0?
1003 Feroza Shah 503
| 1004 Ketan Dhal 502
Give the output of the following SQL queries :
() SELECT COUNT (DISTINCT Make) FROM CARDEN
(ii) SELECT MAX(Charges), MIN(Charges) FROM CARDEN ;
(ii) SELECT COUNT(’), Make FROM CARDEN ;
(io) SELECT CarName FROM CARDEN WHERE Capacity = 4
Ans.
@ 4
(i) 35 2
(iii) Invalid query
(iv) Sx4
CClass
yLEVI maser the following parts of
Constr he fig vance WORKER and PAYLEVEL and nse the leg JO
his question
Table : WORKER
_ eee |
| ecooe | _NAME [peste _|_ PEL st |
[an] Rade Shyam — | Supervisor J poor | 13Sep2008 | 25-Aug 981
Leena | Openee_| ram | meee [BM 170. MONE FAST MFORMANES FRACICES = 1250 ;
(iii) SELECT MAX(MCODE) FROM MATERIAL ;
(iv) SELECT COUNT(DISTINCT PRICE) FROM DRESS,
Ans.
() sumcprice)
2700
(ii) Description Type
FORMAL SHIRT TERELENE
INFORMAL SHIRT COTTON
PENCIL SKIRT SILK
FORMAL PANT TERELENE
INFORMAL PANT COTTON
(iii) max(mcooe)
Mees
(iv) COUNT(DISTINCT PRICE)
6