Scribd
Upload
19 views4 pages

SQL Queries for Student and Product Tables

The document provides examples of SQL queries using ORDER BY, GROUP BY, and HAVING clauses on multiple tables. The queries demonstrate how to order and group results, count occurrences, and filter groups based on conditions.

Uploaded by

Yug Jain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
19 views4 pages

SQL Queries for Student and Product Tables

The document provides examples of SQL queries using ORDER BY, GROUP BY, and HAVING clauses on multiple tables. The queries demonstrate how to order and group results, count occurrences, and filter groups based on conditions.

Uploaded by

Yug Jain
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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;

Common questions

Powered by AI

SQL sorting (ORDER BY) and grouping (GROUP BY) enable detailed data structuring that underpins strategic business decisions. For instance, ranking products by sales volume or cost helps prioritize inventory, while grouping by attributes like color or size facilitates tailored marketing and logistics strategies, maximizing profit and efficiency .

The DISTINCT keyword is not typically used with GROUP BY because GROUP BY already aggregates unique groupings. However, you can use SELECT DISTINCT with GROUP BY for complex queries involving subqueries within the selection. In this dataset context, it's unnecessary as GROUP BY inherently isolates these distinct groupings .

The query: SELECT COLOUR, SUM(PRICE) FROM GARMENT GROUP BY COLOUR; calculates the total price value of garments per color. This aggregation helps in inventory management by providing insight into the financial value tied to each color, aiding decisions like restocking or sale planning .

The data will be displayed first sorted by SIZE in ascending order. For garments with the same SIZE, they will be further sorted by GNAME in descending order. The SQL query is: SELECT * FROM GARMENT ORDER BY SIZE ASC, GNAME DESC; .

To display student names in ascending order, use the query: SELECT * FROM STUDENT ORDER BY NAME; For descending order, use: SELECT * FROM STUDENT ORDER BY NAME DESC;

Use the query: SELECT PRICE, SUM(CALORIES) FROM SOFTDRINK GROUP BY PRICE HAVING SUM(CALORIES) > 30; This combines SUM and HAVING to filter out price groups with a calorie total exceeding 30, useful for identifying potentially less profitable price segments .

Using GROUP BY SIZE with HAVING COUNT(*) > 1 (SELECT SIZE, COUNT(*) FROM GARMENT GROUP BY SIZE HAVING COUNT(*) > 1;) identifies sizes that might require additional inventory review. This can indicate popular or overstocked sizes, guiding restocking or selling strategies .

Use the query: SELECT CITY, COUNT(*) FROM STUDENT GROUP BY CITY HAVING COUNT(*) > 1; The HAVING clause filters groups based on a condition. Here, it ensures that only cities with more than one student are included .

Using multiple columns like SELECT * FROM PRODUCT ORDER BY QUANTITY ASC, PRICE DESC; first sorts results by QUANTITY ascending and then by PRICE descending for equal quantities. This provides hierarchical data view, enhancing analysis by showing tiered prioritization .

Use the query: SELECT DRINCODE, DNAME, CALORIES FROM SOFTDRINK ORDER BY CALORIES DESC; This ordering is useful for prioritizing high-calorie drinks, allowing nutritionists to quickly identify products that may need to be re-evaluated for health concerns .

You might also like