Jam, K Teyari
Class 12 IP CS
Group BY Agg Funcs
Lovejeet Arora
Q. No.1. Consider the table FANS:
Table: FANS
FAN_ID FAN_NAME FAN_CITY FAN_DOB FAN_MODE
F001 SUSHANT MUMBAI 1998-10-02 MAIL
F001 RIYA MUMBAI 1997-12-12 LETTER
F003 ANIKA DELHI 2001-06-30 BLOG
F004 RUDRA AJMER 2005-08-22 MAIL
F006 MIARA KOLKATTA 1998-11-01 BLOG
Write MySQL queries for the following questions.
(i) To display the details of fans in descending orderof their DOB.
(ii) To display the details of FANS who does not BELONGS TO Ajmer.
(iii) To count the total number of fans of each fan mode.
(iv) To display the DOB of the youngest fan
Q No-2 Consider the following table GARMENT, write SQL commands for the statements (i) to (v).
Table : GARMENT
GCODE DESCRIPTION PRICE FCODE READYDATE
10023 PENCIL SKIRT 1150 F03 19-DEC-08
10001 FORMAL SHIRT 1250 F01 12-JAN-08
10012 INFORMAL SHIRT 1550 F02 06-JUN-08
10024 BABY TOP 750 F03 07-APR-07
10090 TULIP SKIRT 850 F02 31-MAR-07
10019 EVENING GOWN 850 F03 06-JUN-08
10009 INFORMAL PANT 1500 F02 20-OCT-08
10007 FORMAL PANT 1350 F01 09-MAR-08
10020 FROCK 850 F04 09-SEP-07
10089 SLACKS 750 F03 20-OCT-08
(i) To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE.
(ii)To display the details of all the GARMENT, which have READYDATE in between 08-
DEC-07 and 16-JUN-08 (inclusive if both the dates).
(iii) To display the average PRICE of all the GARMENT.
(iv) To display total records from table.
Q 3- Consider the table Sports given below. Write outputs of following in SQL statements
Table : Sports
StudentN Cla Name Game1 Grade1 Game2 Grade2
o ss
10 7 Sammer Cricket B Swimming A
11 8 Sujit Tennis A Skating C
12 7 Kamal Swimming B Football B
13 7 Venna Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A Athletics C
(i) SELECTCOUNT(*) FROM Sports;
(ii) SELECTDISTINCT Class FROM Sports;
(iii) SELECTMAX(Class) FROM Student;
(iv) SELECTCOUNT(*) FROM Sports GROUP BY Game1;
Q4.Consider following table –Pharmacy
Write RxID DrugID DrugName Price PharmacyName PharmacyLocation
R1000 5476 Amlodipine 100.00 Rx Pharmacy Pitampura, Delhi
Bahadurgarh,
R1001 2345 Paracetamol 15.00 Raj Medicos Haryana
R1002 1236 Nebistar 60.00 MyChemist Rajouri Garden, Delhi
R1004 5631 Levocitrezine 110.00 RxPharmacy South Extension,Delhi
R1003 6512 VitaPlus 150.00 MyChemist Gurgaon,Haryana
commands in SQL for (i) to (iv):
(i) To increase the price of “Amlodipine” by 50.
(ii) To display all those medicines whose price is in the range 100 to 150.
(iii) To display the Maximum price offered by pharmacy located in “Gurgaon”
(iv) To display sum of price for each PharmacyName having more than 1 drug.
(v) To display Average price
Write the output(s) produced by executing the following queries:
(vi) SELECT RxID, Price from PharmaDB where PharmacyNa IN (“Rx Parmacy”, “Raj
Medicos”);
(vii) SELECT PharmacyName, COUNT(*) FROM PharmaDB GROUP BY PHARMACY NAME;
Q5-. Write SQL Command for (a) to (c) and output of (d)
TABLE : GRADUATE
S.NO NAME STIPEND SUBJECT AVERAGE DIV
1 KARAN 400 PHYSICS 68 I
2 DIWAKAR 450 COMP Sc 68 I
3 DIVYA 300 CHEMISTRY 62 I
4 REKHA 350 PHYSICS 63 I
5 ARJUN 500 MATHS 70 I
6 SABINA 400 CHEMISTRY 55 II
7 JOHN 250 PHYSICS 64 I
8 ROBERT 450 MATHS 68 I
9 RUBINA 500 COMP Sc 62 I
10 VIKAS 400 MATHS 57 II
a. List the names of those students who have obtained DIV I sorted by NAME.
b. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a
year assuming that the STIPEND is paid every month.
c. To count the number of students who are either PHYSICS or COMPUTER SC graduates.
d. Give the output of following sql statement based on table GRADUATE:
(i) Select MIN(AVERAGE) from GRADUATE where SUBJECT=”PHYSICS”;
(ii) Select SUM(STIPEND) from GRADUATE WHERE div=2;
(iii) Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;
(iv) Select COUNT(distinct SUBJECT) from GRADUATE;
Q 6- Carefully observe the following table named ‘Student
Table: Student
S.NO STUDENTNAME SUBJECT Marks DIV
1 ABHISHEK PHYSICS 355 I
2 RAJAT IP I
3 SUDHA Maths 225 II
4 MOHAK PHYSICS 385 I
5 MEERA IP 460 I
6 SANJAY BIOLOGY 210 II
Write SQL queries for the following:
(a) To display the records in decreasing order of marks.
(b) To display subject and subject wise total STUDENTNAME.
(c) To display total students in table.
(d) To display highest Marks .
Q 7-Write the output for SQL queries (i) to (iii), which are based on the table given below:
Table: SPORTS
Class Name Game1 Grade1 Game2 Grade2
Sammer Cricket Swimming
Tennis Skating
Kamal Swimming Football
Venna Tennis Tennis
Archana Basketball Cricket
Arpit Cricket Athletics
(i) SELECT COUNT(*) FROM SPORTS WHERE NAME LIKE ‘%a%’;
(ii) SELECT MAX(Class) FROM SPORTS WHERE Grade1=Grade2;
(iii) SELECT COUNT(*) FROM SPORTS GROUP BY Game1;
Q-8 Write a output for SQL queries (i) to (iii), which are based on the table: ACTIVITY given below:
Table: ACTIVITY
ACode ActivityNameParticipantsNum PrizeMoney ScheduleDate
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 Discuss Throw 10 15000 19-Mar-2004
(i) select count(distinct participantsnum) from activity;
(ii) select max(scheduledate),min(scheduledate) from activity;
(iii) select sum(prizemoney) from activity;
Q9- Write SQL queries for (i) to (iv), which are based on the table : ACTIVITY :
(i) To display the name of all activities with their Acodes in descending order.
(ii) To display sum of PrizeMoney for each of the Number of participants groupings (as
shown in column ParticipantsNum 10,12,16).
(iii) To display the Schedule Date and Participants Number for the activity Relay 100x4.
(iv) To increase PrizeMoney by 500 for High jump
activity Q-10 Consider the following table Vehicles
VNo Type Company Price Qty
AW125 Wagon Maruti 250000 25
J0083 Jeep Mahindra 4000000 15
S9090 SUV Mitsubishi 2500000 18
M0892 Mini Van Datsun 1500000 26
W9760 SUV Maruti 2500000 18
R2409 Mini Van Mahindra 350000 15
Basis on above table intormation, write SQL commands for the following questions.
(i) Display the average price of each type of vehicle having quantity more than 20.
(ii) Count the type of vehicles manufactured by each company.
(iii) Display the total price of all the types of vehicles
Q11- Consider the following DEPT and WORKER tables. Write SQL queries for (i)
to (iv) and find output for SQL query (v).
Table: DEPT
DCODE DEPARTMENT CITY
MEDIA DELHI
MARKETING DELHI
INFRASTRUCTURE MUMBAI
FINANCE KOLKATA
HUMAN RESOURCE MUMBAI
Table: WORKER
WNO NAME DOB GENDER DCODE
1001 George K 2013-09-02 1991-09-01 MALE D01
1002 Ryma Sen 2012-12-11 1990-12-15 FEMALE D03
1003 Mohitesh 2013-02-03 1987-09-04 MALE D05
1007 Jha 2014-01-17 1984-10-19 MALE D04
1004 Manila Sahai 2012-12-09 1986-11-14 FEMALE D01
1005 SAHAY 2013-11-18 1987-03-31 MALE D02
1006 Priya 2014-06-09 1985-06-23 FEMALE D05
Note DOJ refers to Date of Joining and DOB refers to Date of Birth of workers.
(i) To display WNO, NAME, GENDER from the table WORKER in descending
order of WNO.
(ii) To display the NAME of all the FEMALE workers from the table WORKER.
(iii) To display the WNO and NAME of those workers from the table WORKER, who
are born between‘1987-01-01’ and ‘1991-12-01’
(iv) To count and display MALE workers who have joined after ‘1986-01-01’.
(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT(*)>1;
Q-12- Gopi Krishna is using a table Employee. It has the following columns: Code,
Name, Salary, Dept_code
He wants to display maximum salary department wise. He wrote the following command:
SELECT Deptcode, Max(Salary) FROM Employee; But he
did not get the desired result.
Rewrite the above query with necessary changes to help him get the desired output.
Q-13 Carefully observe the following table named ‘student’:
Table: student
Name Category Class Marks
Radha
Mahesh
Manvi
Pooja
Write SQL queries for the following:
(a) To display the records in decreasing order of Name.
(b) To display category and category wise total Marks of Student.
(c) To display the category and its average Marks.
(d) To display category and category wise highest Marks of the Student.
Q-14 . Write the SQL commands for the (i) to (iv) and write the output of the (v) to (viii) on the basis of
table CLUB. Table: CLUB
COACH_ID COACHNA SPORTS DATOFAPP
KUKREJA KARATE 27/03/1996 10000
RAVINA KARATE 20/01/1997 12000
KARAN SQUASH 19/02/1998 20000
TARUN BASKETBALL 01/01/1998 15000
ZUBIN SWIMMING 12/01/1998 7500
KETAKI SWIMMING 24/02/1998 8000
ANKITA SQUASH 20/02/1998 22000
ZAREEN KARATE 22/02/1998 11000
KUSH SWIMMING 13/01/1998 9000
SHAILYA BASKETBALL 19/02/1998 17000
a. To show all information about the swimming coaches in the club.
b. To list names of all coaches with their date of appointment (DATOFAPP) in
descending order.
c. To display a report, showing coachname, pay, age and bonus (15% of pay) for all
the coaches.
d. To count the number of coaches in each sports.
e. Give the output of following SQL statements:
I. SELECT COUNT( DISTINCT SPORTS) FROM CLUB;
II. SELECT SUM(PAY) FROM CLUB WHERE DATOFAPP> ‘31/01/1998’;
III. SELECT LCASE(SPORTS) FROM CLUB;
SELECT MOD(AGE,5) FROM CLUB WHERE SEX=
‘F’;
Q 15- Write SQL Commands and the output for following queries.
Table : Shop
Shop_name Area Cust_percent Rating
West_Side 250000 West Delhi
Pantaloons 500000 South Chennai
& Her’s 300000 North Amritsar
Sports King 380000 North Baroda
Biswas Stores 456000 East Delhi
Bazar 290000 South Kolkota
Levis 230000 East Jameshdpur
Peter 428000 South Chennai
England
a) To display the name of all shop which are in area South and sale more than average sale.
(b) To display the city along with sum of sale for each city
(c) Give the output of the following sql statements.
(i) Select min(sale) from shop where sale>300000 group by sales;
(ii) Select count(distinct city) from shop;
(iii) select avg(sale) from shop where Area=’South’;
(iv) select avg(Cust_percentage),sum(sale) from shop where rating =’A’;
Solutions:-
Ans 1
(i) SELECT * FROM FANS ORDER BY FAN_DOB DESC;
(ii) SELECT * FROM FANS WHERE FAN_CITY <>”Ajmer”.
(iii) SELECT FAN_MODE, COUNT(*) FROM FANS GROUP BYFAN_MODE;
(iv) SELECT MAX(FAN_DOB) FROM FANS;
Ans 2:-
(i) Select GCODE , DESCRIPTION from GARMENT order by GCODE desc;
(ii) Select * from GARMENT where READYDATE between 08-DEC-07 and 16-JUN-08;
(iii) Select avg(PRICE) from GARMENT;
(iv) Select count(*) from GARMENT;;
Ans 3
–
(ii) 6
(iii) 7
8
9
1
0
(iv) 10
(v) 2
2
1
1
Ans 4.
(i) Update pharmadb set price =price+50 where DrugMane = “Amlodipine”;
(ii) Select DrugName from Pharmadb where price>=100 and price<=150;
(iii) Select Max(price) form pharmadb where pharmacylocation like “Gurgaon%”;
(iv) Select sum(price) from pharmadb group by pharmacyname having count(*)>1 ;
(v) Select avg(Price) from pahramadb
(vi) R1000 100
R001 15
R1004 110
(vii) Rx Pharmacy 2
Raj Medicos 1
MyChemist 2
Ans 5-
a. SELECT NAME from GRADUATE where DIV = ‘I’ order by NAME;
b. SELECT NAME,STIPEND,SUBJECT, STIPEND*12 from GRADUATE;
c. SELECT SUBJECT,COUNT(*) from GRADUATE group by SUBJECT having
SUBJECT=’PHYISCS’ or SUBJECT=’COMPUTER SC’;
d. (i) 63
(ii) 800
(iii) 475
(ii) 4
6. (iii) 2
(a) Select * from student order by marks 2
desc; 1
(b) Select subject ,sum(Marks) group 1
by Subject;
(c) Select count(*) from student; Ans 8.
(d) Select max (marks) from student; (i)
7. (ii) 19-Mar-2004 , 12-Dec-2003
(iii) 54000
(ii)9 Ans 9:
(i) Select ActivityName, Acodes from ACTIVITY order by Acodes desc;
(ii) SELECT SUM(PrizeMoney),ParticipantsNum FROM ACTIVITY GROUP BY
ParticipantsNum;
(iii) Select ScheduleDate, ParticipantsNum FROM ACTIVITY where ActivityName=’ Relay
100x4’
(iv) Update ACTIVITY set PrizeMoney = PrizeMoney + 500 where ActivityName=’ High jump’;
Ans 10-
(i) SELECT Type, AVG(Price) FROM Vehicle GROUP BY Type HAVING Qty > 20;
(ii) SELECTCompany, COUNT(DISTINCTType) FROM Vehicle GROUPBY Company;
(iii) SELECTType, SUM(Price* Qty) FROM Vehicle
GROUPBYType; Ans 11-
(i) SELECT WNO, NAME, GENDER FROM WORKER ORDER BY WNO DESC;
(ii) SELECT NAME FROM WORKER WHERE GENDER "FEMALE";
(iii) SELECT WNO, NAME FROM WORKER WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-
01’;
(iv) SELECT COUNT(*) FROM WORKER WHERE GENDER "MALE" AND DOJ > ‘1986-01-01’;
(v)
COUNT (*) DCODE
2 D01
2 D05
Ans 12.
SELECT Deptcode, Max(Salary) FROM Employee GROUP BY Deptcode;
Ans 13
(a) select * from student order by Name desc;
(b) select category, sum(Marks) from student group by Name;
(c) select category,avg(price) from stock group by category;
(d) select category, max(Marks) from studentock group by category;
Ans 14
(a) SELECT * FROM CLUB WHERE SPORTS=’SWIMMING’;
(b) SELECT COACHNAME,DATOFAPP FROM CLUB ORDER BY DATOFAPP DESC;
(c) SELECT COACHNAME, PAY, AGE, PAY *0.15 AS BONUS FROM CLUB ;
(d) SELECT COUNT(COACHNAME) FROM CLUB GROUP BY
SPORTS (e).(i) 4
(ii). 78000
(iii)
Karate
Karate
Squash
Basketball
Swimming
Swimming
Squash
Karate
Swimming
Basketball
(iv) 4 6 9 7
Ans 15
- a) select shop_name from shop where area like ‘South’ and sale>avg(sale);
b) select city, sum(sale) from shop group by
city; c)
i) Min(sale)
--------------- iii) Avg(sale)
380000 ---------------
428000 4060000
456000 iv) Area
500000 --------------
ii) Count(Distinct(City) East North
-------------------- South
6
