Write SQL queries for (i) to (iv) and find outputs for SQL queries (v)

1.	Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.TRAINERTIDTNAMECITYHIREDATESALARY101SUNAINAMUMBAI1998-10-1590000102ANAMIKADELHI1994-12-2480000103DEEPTICHANDIGARG2001-12-2182000104MEENAKSHIDELHI2002-12-2578000105RICHAMUMBAI1996-01-1295000106MANIPRABHACHENNAI2001-12-1269000COURSECIDCNAMEFEESSTARTDATETIDC201AGDCA120002018-07-02101C202ADCA150002018-07-15103C203DCA100002018-10-01102C204DDTP90002018-09-15104C205DHN200002018-08-01101C206O LEVEL180002018-07-25105(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» (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

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.TRAINERTIDTNAMECITYHIREDATESALARY101SUNAINAMUMBAI1998-10-1590000102ANAMIKADELHI1994-12-2480000103DEEPTICHANDIGARG2001-12-2182000104MEENAKSHIDELHI2002-12-2578000105RICHAMUMBAI1996-01-1295000106MANIPRABHACHENNAI2001-12-1269000COURSECIDCNAMEFEESSTARTDATETIDC201AGDCA120002018-07-02101C202ADCA150002018-07-15103C203DCA100002018-10-01102C204DDTP90002018-09-15104C205DHN200002018-08-01101C206O LEVEL180002018-07-25105(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»

(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’;

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

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

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment