Scribd
Upload
3 views4 pages

SQL Queries for Trainer and Course Data

The document contains SQL queries for various tasks related to a trainer and course database. It includes queries to display trainer details, filter by hire date, join tables, count trainers by city, and aggregate course fees. Additionally, it provides expected outputs for specific SQL queries regarding trainers and courses.

Uploaded by

shaumikahlawat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views4 pages

SQL Queries for Trainer and Course Data

The document contains SQL queries for various tasks related to a trainer and course database. It includes queries to display trainer details, filter by hire date, join tables, count trainers by city, and aggregate course fees. Additionally, it provides expected outputs for specific SQL queries regarding trainers and courses.

Uploaded by

shaumikahlawat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

My SQL Assignment 2

Q. Write SQL queries for (a) to (d) and find outputs for SQL queries (e) to (h), which are
based on the tables

a) Display the Trainer Name , City and Salary in descending order of their hire
date.

Ans(a): select TNAME, CITY, SALARY from trainer order by HIREDATE desc;

b) To display the TNAME and CITY of Trainer of joined the institute in the
month of December 2001.

Ans(b): select TNAME, CITY from trainer where HIREDATE between '2001-12-
01' and '2001-12-31';

c) To display TNAME, HIREDATE,CNAME,STARTDATE from tables TRAINER and


COURSE whose FEES is less than or equal to 10000.
Ans(c): select [Link], [Link], [Link], [Link] from trainer t join
course c on [Link] = [Link] where [Link] <= 10000;

d) To display number of trainer from each city.

Ans(d): select CITY, count(*) as NUM_OF_TRAINERS from trainer group by CITY;

e) SELECT TID, TNAME,FROM TRAINER WHERE CITY NOT


IN(‘DELHI’,’MUMBAI’);
Ans(e):

+------+-----------------+
| TID | TNAME |

+------+-----------------+

| 103 | Deepti |

| 106 | Maniprabha|
+------+-----------------+

f) SELECT DISTINCT TID FROM COURSE;


Ans(f):
+------+

| TID |
+------+
| 101 |
| 103 |
| 102 |

| 104 |

| 105 |
+------+
g) SELECT TID , COUNT (*), MIN(FEES) FROM COURSE GROUP BY TID HAVING
COUNT(*)>1;
Ans(g):

+------+------------------+------------------+

| TID | NumCourses | MinFee |

+------+------------------+------------------+
| 101 | 2 | 12000 |

+------+------------------+------------------+

h) SELECT COUNT(*),SUM(FEES) FROM COURSE WHERE STARTDATE <’2018-


09-15’;

Ans(h):
+------------------+--------------+

| NumCourses | TotalFees |
+------------------+--------------+
| 4 | 65000 |

+------------------+--------------+

You might also like