You are here
This question asked in CBSE 2018-19
Write SQL queries for (i) to (iv) and find output for SQL queries (v) to (viii), which are based on the tables.
Table: TRAINER
| TID | TNAME | CITY | HIREDATE | SALARY |
| 101 | SUNAINA | MUMBAI | 1998-1015 | 90000 |
| 102 | ANAMIKA | DELHI | 1994-12-24 | 80000 |
| 103 | DEEPTI | CHANDIGARH | 2001-12-21 | 82000 |
| 104 | MEENAKSHI | DELHI | 2002-12-25 | 78000 |
| 105 | RICHA | MUMBAI | 1996-01-12 | 95000 |
| 106 | MANIPRABHA | CHENNAI | 2001-12-12 | 69000 |
Table: COURSE
| CID | CNAME | FEES | STARTDATE | TID |
| C201 | AGDCA | 12000 | 2018-07-02 | 101 |
| C202 | ADCA | 15000 | 2018-07-15 | 102 |
| C203 | DCA | 10000 | 2018-10-01 | 103 |
| C204 | DDTP | 9000 | 2018-09-15 | 104 |
| C205 | DHN | 20000 | 2018-08-01 | 105 |
| C206 | O LEVEL | 18000 | 2018-07-25 | 106 |
Question 2 (i)
Display the Trainer Name, City & Salary in descending order of their Hiredate.
Answer:
To display the Trainer Name, City & Salary in descending order of their Hiredate, the ORDER BY clause followed by the column name ie., HIREDATE followed by the keyword DESC should be used in the query.
QUERY: SELECT TNAME,CITY,SALARY FROM TRAINER ORDER BY HIREDATE DESC;
-long-answer-5---image-3.jpg)
Question 2 (ii)
To display the TNAME and CITY of Trainer who joined the Institute in the month of December 2001.
Answer:
To display the TNAME and CITY of Trainer who joined the Institute in the month of December 2001, the LIKE operator followed by the specific pattern to check for match ie., ’2001-12%’ should be used in the query.
QUERY: SELECT TNAME,CITY FROM TRAINER WHERE HIREDATE LIKE ‘2001-12%’;
-long-answer-5---image-2.jpg)
OR
To display the TNAME and CITY of Trainer who joined the Institute in the month of December 2001, the BETWEEN operator followed by the range ie., ‘2001-12-01’ AND ‘2001-12-31’ should be used in the query.
QUERY: SELECT TNAME,CITY FROM TRAINER WHERE HIREDATE BETWEEN '2001-12-01' AND '2001-12-31';
-long-answer-5---image-2.jpg)
Question 2 (iii)
To displayTNAME, HIREDATE, CNAME, STARTDATE from tables TRAINER and COURSE of all those courses whose FEES is less than or equal to 10000.
Answer:
To display TNAME, HIREDATE, CNAME, STARTDATE from tables TRAINER and COURSE of all those courses whose FEES is less than or equal to 10000:
- The RELATIONAL operator ‘<=’ should be used to check if FEES is less than or equal to 1000;
- The tables TRAINER and COURSE should be joined using an EQUI JOIN ie., TRAINER.TID=COURSE.TID
QUERY: SELECT TNAME,HIREDATE,CNAME,STARTDATE FROM TRAINER,COURSE WHERE FEES<=10000 AND TRAINER.TID=COURSE.TID;
-long-answer-5---image-1.jpg)
Question 2 (iv)
To display number of Trainers from each city.
Answer:
To display the number of Trainers from each city:
- The COUNT( ) function is used to fetch the number of trainers.
- The GROUP BY clause is used to group the records city wise.
QUERY: SELECT CITY,COUNT(*) FROM TRAINER GROUP BY CITY;
-long-answer-5---image1.jpg)
Question 2 (v)
SELECT COUNT(*), SUM(FEES) FROM COURSE WHERE STARTDATE< ‘2018-09-15’;
Answer:
The query will display the number of courses and total fees of courses that started before ‘2018-09-15’.
-long-answer-5---image.jpg)
