Advertisements
Advertisements
Question
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;
Solution
DISTINCT TID 101 NULL 102 104 103
TID COUNT(*) MIN(FEES) 101 2 12000
CNAME Digital Marketing Mobile Application Development - 15500.00
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;
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).
What is the purpose of the Order By clause in SQL? Explain with the help of a suitable example.
Discuss the significance of Group by clause in detail with the help of a suitable example.
