Write SQL commands for the following queries (i) to (iv) based on the relations TRAINER and COURSE given below:

TRAINER Table:
| 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 Table:
| 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 all details of Trainers who are living in city CHENNAI.
II. Count and Display the number of Trainers in each city.
III. Display the Course details which have Fees more than 12000 and name ends with 'A'.
IV. (A) Display the Trainer Name & Course Name from both tables where Course Fees is less than 10000.
OR
(B) Display the Cartesian Product of above two tables.

To answer the queries based on the TRAINER and COURSE tables, we'll use SQL commands. Here is a breakdown of each query and its corresponding SQL statement:

Display all details of Trainers who are living in city CHENNAI.

We want to retrieve all columns from the TRAINER table where the CITY is 'CHENNAI'.

SELECT * FROM TRAINER WHERE CITY = 'CHENNAI';

Count and Display the number of Trainers in each city.

We need to group the trainers by CITY and count how many trainers are in each city.

SELECT CITY, COUNT(TID) AS NumberOfTrainers FROM TRAINER GROUP BY CITY;

Display the Course details which have Fees more than 12000 and name ends with 'A'.

We are looking at the COURSE table and need to filter rows where the FEES are greater than 12000 and the CNAME ends with 'A'.

SELECT * FROM COURSE WHERE FEES > 12000 AND CNAME LIKE '%A';

(A) Display the Trainer Name & Course Name where Course Fees is less than 10000.

We will perform a Join between TRAINER and COURSE tables where the COURSE FEES are less than 10000.

SELECT TRAINER.TNAME, COURSE.CNAME FROM TRAINER JOIN COURSE ON TRAINER.TID = COURSE.TID WHERE COURSE.FEES < 10000;
(B) Display the Cartesian Product of above two tables.

To show the Cartesian product, every row of TRAINER is combined with every row of COURSE.

SELECT * FROM TRAINER, COURSE;


These SQL queries will help you retrieve the necessary information from the TRAINER and COURSE tables according to the specified conditions.

Answered by danjohnbrain | 2025-07-06

The provided SQL commands accurately retrieve data from the TRAINER and COURSE tables based on the specified conditions, including filtering trainers by city, counting trainers by city, and selecting courses based on fee criteria. Additionally, options for retrieving trainer-course relationships either as a filtered join or as a Cartesian product are offered. These commands facilitate effective data management and analysis within a relational database context.
;

Answered by danjohnbrain | 2025-07-15