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.

Question 2 Long Answer 5 - image.jpg

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

 

Question 2 Long Answer 5 - image 2.jpg

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;

Question 2 (i) 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%’;

Question 2 (i) 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';

Question 2 (ii) 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;

Question 2(iii) 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;  

Question 2(v) 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’.

Question 2(v) Long Answer 5 - image.jpg

Learn in your speed, with individual attention - Teachoo Maths 1-on-1 Class


Transcript

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; 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%’; 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'; 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; 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; 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’.

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 13 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.