(b) Write the output of the queries (i) to (iv) 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-0 | 102 |
| C204 | DCA | 9000 | 2021-09-15 | 104 |
| C205 |
Mobile Application Development |
18000 | 2022-11-01 | 101 |
| C206 | Digital marketing | 16000 | 2022-07-25 | 103 |
(i)
SELECT DISTINCT TID FROM TECH_COURSE;
(ii)
SELECT TID, COUNT(*), MIN(FEES) FROM
TECH_COURSE GROUP BY TID HAVING COUNT(TID)>1;
(iii)
SELECT CNAME FROM TECH_COURSE WHERE
FEES>15000 ORDER BY CNAME;
(iv)
SELECT AVG(FEES) FROM TECH_COURSE WHERE
FEES BETWEEN 15000 AND 17000;
Question 26(b)(i)
Answer:
Output:
|
DISTINCT TID |
|
101 |
|
NULL |
|
102 |
|
104 |
|
103 |
Explanation:
The SELECT statement when combined with DISTINCT clause, returns records without repetition.
So the query will return all the values in the column TID without repetition.
Question 26(b)(ii)
Answer:
Output:
|
TID |
COUNT(*) |
MIN(FEES) |
|
101 |
2 |
12000 |
Explanation:
The above query groups the records based on the value in the column TID and then checks if the count is greater than 1 . If the count is greater than 1 for any TID value, then that TID, the count and the minimum value in the column FEES for all records with that particular TID are displayed.
Question 26(b)(iii)
Answer:
Output:
|
CNAME |
|
Digital marketing |
|
Mobile Application Development |
Explanation:
The above query displays CNAME of records in the table with FEES value greater than 15000 , arranged in ascending order of CNAME .
Question 26(b)(iv)
Answer:
Output:
|
AVG(FEES) |
|
15500.00 |
Explanation:
The above query will display the average of values in the column FEES for records with FEES values between 15000 and 17000 , both 15000 and 17000 included.
