SQL LAB SESSION 2
TID TNAME CITY HIREDATE SALARY
101 SUNAINA MUMBAI 1998-10-15 90000
102 ANAMIKA DELHI 1994-12-24 80000
103 DEEPTI CHANDIGARH 2001-12-21 82000
104 MEENAKSHI DELHI 2002-12-25 78000 COURSE
105 RICHA MUMBAI 1996-01-12 95000
106 MANIPRABHA CHENNAI 2001-12-12 69000
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
TRAINER
C204 DDTP 9000 2018-09-15 104
C205 DHN 20000 2018-08-01 101
C206 O LEVEL 18000 2018-07-25 105
1. Display the Trainer Name,City and Salary in descending order of their
Hiredate
2. To display the Tname and City of the TRAINER who joined the institute
in the month of December 2001.
3. To display TNAME,HIREDATE,CNAME,STARTDATE from tables TRAINER
and COURSE of all those courses whose fees is less than or equal to
10000.
4. To display the number of trainees from each city.
5. To display the name,bonus for each trainer where bonus is 10% of
salary.
1. Display the Trainer Name,City and Salary in descending order of their Hiredate
SELECT TNAME, CITY, SALARY
FROM TRAINER
ORDER BY HIREDATE DESC;
2. To display the Tname and City of the TRAINER who joined the institute in the month of December 2001.
SELECT TNAME, CITY
FROM TRAINER
WHERE HIREDATE BETWEEN '2001-12-01' AND '2001-12-31’;
3. To display TNAME,HIREDATE,CNAME,STARTDATE from tables TRAINER and COURSE of all those courses whose
fees is less than or equal to 10000.
SELECT [Link], [Link], [Link], [Link]
FROM TRAINER T NATURAL JOIN COURSE C
WHERE [Link] <= 10000;
4. To display the number of trainees from each city.
SELECT CITY, COUNT(*) AS NUM_TRAINEES
FROM TRAINER
GROUP BY CITY;
5. To display the name,bonus for each trainer where bonus is 10% of salary
SELECT TNAME, SALARY * 0.10 AS BONUS
FROM TRAINER;
.
6. Select trainers whose names start with 'S’
7. Select the total salary paid to trainers in each city.
8. Select the average salary of trainers in each city, but only for cities
with more than one trainer (using GROUP BY and HAVING):
9. elect the names of trainers who were hired in the years 1994, 1996,
or 2001
10. Select the highest salary of trainers in each city
11. Select trainers and their courses, ordered by the course start date
12. Select the number of trainers in each city, ordered by the number
of trainers in descending order
13. Select the trainers who have a salary greater than the average
salary of all trainers
6. Select trainers whose names start with ‘S’
SELECT TNAME, CITY, SALARY
FROM TRAINER
WHERE TNAME LIKE 'S%';
7. Select the total salary paid to trainers in each city.
SELECT CITY, SUM(SALARY) AS TOTAL_SALARY
FROM TRAINER
GROUP BY CITY;
8. Select the average salary of trainers in each city, but only for cities with more than one trainer (using GROUP BY
and HAVING):
SELECT CITY, AVG(SALARY) AS AVERAGE_SALARY
FROM TRAINER
GROUP BY CITY
HAVING COUNT(*) > 1;
9. Select the names of trainers who were hired in the years 1994, 1996, or 2001
SELECT TNAME, CITY, HIREDATE
FROM TRAINER
WHERE YEAR(HIREDATE) IN (1994, 1996, 2001);
10. Select the highest salary of trainers in each city
SELECT CITY, MAX(SALARY) AS HIGHEST_SALARY
FROM TRAINER
GROUP BY CITY;
11. Select trainers and their courses, ordered by the course start date
SELECT [Link], [Link], [Link]
FROM TRAINER T NATURAL JOIN COURSE C
ORDER BY [Link];
[Link] the number of trainers in each city, ordered by the number of trainers in descending order
SELECT CITY, COUNT(*) AS NUM_TRAINERS
FROM TRAINER
GROUP BY CITY
ORDER BY NUM_TRAINERS DESC;
13. Select the trainers who have a salary greater than the average salary of all trainers
SELECT TNAME, CITY, SALARY
FROM TRAINER
WHERE SALARY > (SELECT AVG(SALARY) FROM TRAINER);