Advertisements
Advertisements
Question
What is the purpose of the Order By clause in SQL? Explain with the help of a suitable example.
Solution
Order By clause:
The ORDER BY command is used to sort the result set in ascending or descending order.
The following SQL statement displays all the customer’s names in alphabetical order:
SELECT Cname FROM Customers ORDER BY Cname;
APPEARS IN
RELATED QUESTIONS
What is the meaning of "HAVING" clause in Mysql?
Which of the following is not a built in aggregate function in SQL?
We apply the aggregate function to a group of sets of tuples using the clause.
Which clause is similar to "HAVING" clause in Mysql?
What is the purpose of the following clause in a select statement?
ORDER BY
What is the purpose of the following clause in a select statement?
GROUP BY
Consider the following table named “Product”, showing details of products being sold in a grocery shop.
| PCode | PName | UPrice | Manufacturer |
| P01 | Washing Powder | 120 | Surf |
| P02 | Toothpaste | 54 | Colgate |
| P03 | Soap | 25 | Lux |
| P04 | Toothpaste | 65 | Pepsodent |
| P05 | Soap | 38 | Dove |
| P06 | Shampoo | 245 | Dove |
Write SQL queries for the following:
- Create the table Product with appropriate data types and constraints.
- Identify the primary key in the Product.
- List the Product Code, Product Name, and price in descending order of their product name. If PName is the same, then display the data in ascending order of price.
- Add a new column Discount to the table Product.
- Calculate the value of the discount in the table Product as 10 percent of the UPrice for all those products where the UPrice is more than 100, otherwise, the discount will be 0.
- Increase the price by 12 percent for all the products manufactured by Dove.
- Display the total number of products manufactured by each manufacturer. Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
- SELECT PName, Average (UPrice) FROM Product GROUP BY Pname;
i) SELECT DISTINCT Manufacturer FROM Product;
j) SELECT COUNT (DISTINCT PName) FROM Product;
k) SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
Write the output of the queries based on the table, TECH_COURSE given below:
| Table: TECH_COURSE | ||||
| CID | CNAME | FEES | STARTDATE | TID |
| C201 | Animation and VFX | 12000 | 2022-07-02 | 101 |
| C202 | CADD | 15000 | 2021-11-15 | NULL |
| C203 | DCA | 10000 | 2020-10-01 | 102 |
| C204 | DDTP | 9000 | 2021-09-15 | 104 |
| C205 | Mobile Application Development |
18000 | 2022-11-01 | 101 |
| C206 | Digital Marketing | 16000 | 2022-07-25 | 103 |
- SELECT DISTINCT TID FROM TECH_COURSE;
- SELECT TID, COUNT(*), MIN(FEES) FROM TECH_COURSE GROUP BY TID HAVING COUNT(TID)>1;
- SELECT CNAME FROM TECH_COURSE WHERE FEES>15000 ORDER BY CNAME;
- SELECT AVG(FEES) FROM TECH_COURSE WHERE FEES BETWEEN 15000 AND 17000;
Which SQL statement do we use to find out the total number of records present in the table ORDERS?
Rashmi, a database administrator needs to display house wise total number of records of the ‘Red’ and ‘Yellow’ houses. She is encountering an error while executing the following query:
SELECT HOUSE, COUNT (*) FROM STUDENT GROUP BY HOUSE WHERE HOUSE=’RED’ OR HOUSE= ‘YELLOW’;
Help her in identifying the reason for the error and write the correct query by suggesting the possible correction (s).
Discuss the significance of Group by clause in detail with the help of a suitable example.
