SQL ORDER BY,GROUP BY ,HAVING CLAUSE
QUERIES
Q.1 Table : STUDENT
Write SQL queries for following w/r to STUDENT table
i. Display data in ascending order of name
Ans. SELECT * FROM STUDENT ORDER BY NAME;
ii. Display data in descending order of name
Ans. SELECT * FROM STUDENT ORDER BY NAME DESC;
iii. Display data in asc order of city and desc order of name
Ans. SELECT * FROM STUDENT ORDER BY CITY ASC,NAME
DESC;
iv. Count and display the number of student from each city
Ans. SELECT CITY,COUNT(*) FROM STUDENT GROUP BY CITY;
v. Count and display the number of student from each city
where number of students are more than 1
vi. Ans. SELECT CITY,COUNT(*) FROM STUDENT GROUP BY CITY
HAVING COUNT(*)>1;
b.
Write SQL queries for following w/r to GARMENT table
i. Display data in ascending order of GNAME
Ans. SELECT * FROM GARMENT ORDER BY GNAME;
ii. Display data in descending order of GANAME
Ans. SELECT * FROM GARMENT ORDER BY GNAME DESC;
iii. Display data in asc order of SIZE and desc order of GNAME
Ans. SELECT * FROM GARMENT ORDER BY SIZE ASC,GNAME
DESC;
iv. Count and display the number of GARMENT in each SIZE
Ans. SELECT SIZE,COUNT(*) FROM GARMENT GROUP BY SIZE;
v. Count and display the number of GARMENT from each SIZE
where number of GARMENTS are more than 1
Ans. SELECT SIZE,COUNT(*) FROM GARMENT GROUP BY SIZE
HAVING COUNT(*)>1;
vi. Display the sum of price of each color garment
Ans. SELECT COLOUR,SUM(PRICE) FROM GARMENT GROUP
BY COLOUR;
C. Table PRODUCT
+-----------+-------------+-----------+----------+------------+
| prodID | prodCod | name | quantity | price |
+-----------+-------------+-----------+----------+------------+
| 1001 | PEN | Pen Red | 4000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 6000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
| 1006 | PEC | Pencil HB | 4000 | 3.99 |
Write SQL queries for following w/r to PRODUCT table
i. Display data in ascending order of QUANTITY
Ans. SELECT * FROM PRODUCT ORDER BY QUANTITY;
ii. Display data in descending order of PRICE
Ans. SELECT * FROM PRODUCT ORDER BY PRICE DESC;
iii. Display data in asc order of QUANTITY and desc order of
PRICE
Ans. SELECT * FROM PRODUCT ORDER BY QUANTITY
ASC,PRICE DESC;
iv. Count and display the number of PEN in each PRICE GROUP
Ans. SELECT PRICE,SUM(QUANTITY) FROM PRODUCT GROUP
BY PRICE;
v. Count and display the number of PEN in each PRICE GROUP
where number of PENS are more than 4000
Ans. SELECT PRICE,SUM(QUANTITY) FROM PRODUCT GROUP
BY PRICE HAVING SUM(PRICE)>4000;
d. TABLE : SOFTDRINK
Write SQL queries for following w/r to SOFTDRINK table
i. Display drink codes, names and calories of all drinks, in
descending order of calories.
Ans. SELECT DRINCODE,DNAME,CALORIES FROM SOFTDRINK
ORDER BY CALORIES DESC;
ii. Display data in ascending order of PRICE
Ans. SELECT * FROM SOFTDRINK ORDER BY PRICE;
iii. Display data in asc order of PRICE and desc order of CALORIES
Ans. SELECT * FROM SOFTDRINK ORDER BY PRICE ASC,CALORIES
DESC;
iv. Count and display the number of DRINK in each PRICE GROUP
Ans. SELECT PRICE,COUNT(*) FROM SOFTDRINK GROUP BY
PRICE;
v. Count and display the number of DRINK in each PRICE GROUP
where sum of price is >30
Ans. SELECT PRICE,COUNT(*) FROM SOFTDRINK GROUP BY PRICE
HAVING SUM(PRICE)>30;
