Display the Trainer Name, City & Salary in descending order of their Hiredate.

Question

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.

TRAINER

TID	TNAME	CITY	HIREDATE	SALARY
101	SUNAINA	MUMBAI	1998-10-15	90000
102	ANAMIKA	DELHI	1994-12-24	80000
103	DEEPTI	CHANDIGARG	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

COURSE

CID	CNAME	FEES	STARTDATE	TID
C201	AGDCA	12000	2018-07-02	101
C202	ADCA	15000	2018-07-15	103
C203	DCA	10000	2018-10-01	102
C204	DDTP	9000	2018-09-15	104
C205	DHN	20000	2018-08-01	101
C206	O LEVEL	18000	2018-07-25	105

(i) Display the Trainer Name, City & Salary in descending order of their Hiredate.

(ii) To display the TNAME and CITY of Trainer who joined the Institute in the month of December 2001.

(iii) To display TNAME, HIREDATE, CNAME, STARTDATE from tables TRAINER and COURSE of all those courses whose FEES is less than or equal to 10000.

(iv) To display number of Trainers from each city.

(v) SELECT TID, TNAME, FROM TRAINER WHERE CITY NOT IN(‘DELHI’, ‘MUMBAI’);

(vi) SELECT DISTINCT TID FROM COURSE;

(vii) SELECT TID, COUNT(*), MIN(FEES) FROM COURSE GROUP BY TID HAVING COUNT(*)>1;

(viii) SELECT COUNT(*), SUM(FEES) FROM COURSE WHERE STARTDATE< ‘2018-09-15’;

Answer 1

(i) SELECT TNAME, CITY, SALARY FROM TRAINER ORDER BY HIREDATE;

(iii) SELECT TNAME, CITY FROM TRAINER WHERE HIREDATE BETWEEN ‘2001-12-01’ AND ‘2001-12-31’;

OR

SELECT TNAME, CITY FROM TRAINER WHERE HIREDATE >= ‘2001-12-01’ AND HIREDATE<=‘2001-12-31’;

OR

SELECT TNAME, CITY FROM TRAINER WHERE HIREDATE LIKE ‘2001-12%’;

(iii) SELECT TNAME, HIREDATE, CNAME, STARTDATE FROM TRAINER, COURSE WHERE TRAINER.TID=COURSE.TID AND FEES<=10000;

(iv) SELECT CITY, COUNT(*) FROM TRAINER GROUP BY CITY;

(v) TIDTNAME

103   DEEPTI

106   MANIPRABHA

(vi) DISTINCT TID

101

103

102

104

105

(vii) TIDCOUNT(*)MIN(FEES)

101   2   12000

(viii) COUNT(*)SUM(FEES)

4    65000

Comments

SELECT TNAME, CITY, SALARY FROM TRAINER ORDER BY HIREDATE desc;