Question

Consider the following tables:

Table 1: TEACHER, which stores Teacher ID (TID), Teacher Name (TName), Experience(Experience) and City (City) that they live in.

TID	TName	Experience	City
1	Kartik	5	Bhopal
2	Shahnaz	6	Nagpur
3	Rajendra	7	Delhi
4	Tanvi	4	Bhopal
5	Alam	9	Delhi

Table 2: SUBJECT, which stores Subject ID (SID), Subject Name (SubName) and ID of the teacher (TID) teaching that Subject.

SID	SubName	TID
101	Physics	1
102	Chemistry	2
103	Mathematics	3
104	Informatics Practices	4
105	Computer Science	5

Write appropriate SQL query for the following:

(I) Delete the record of the subject whose TID is equal to 4.

(II) Display the names of teachers who have more than 5 years of experience and who stay in Nagpur.

(III) Display subject names along with their teacher names.

Hint:

Use DELETE with WHERE for part I, AND for the two conditions in part II, and match TID in both tables for part III.

Answer 1

Step 1: Read the two tables:
TEACHER holds TID, TName, Experience and City. SUBJECT holds SID, SubName and TID.
The column TID is common to both tables, so it links a subject to its teacher.
We need one DELETE query, one SELECT with two conditions and one join query.

Step 2: Part (I) - delete the subject with TID 4:
The record of a subject sits in the SUBJECT table, so the delete runs on SUBJECT.
The DELETE FROM clause names the table and WHERE picks the row.

DELETE FROM SUBJECT WHERE TID = 4;

Only one row matches TID = 4: SID 104, Informatics Practices. That row is removed.

Step 3: Part (II) - teachers with more than 5 years in Nagpur:
Both conditions must hold at the same time, so we join them with AND.

SELECT TName FROM TEACHER WHERE Experience > 5 AND City = 'Nagpur';

Check each row. Kartik has 5 years, not more than 5. Shahnaz has 6 years and lives in Nagpur, so she qualifies.
Rajendra and Alam have enough experience but live in Delhi. Tanvi has 4 years.
The output is one name: Shahnaz.

Step 4: Part (III) - subject names with teacher names:
The names come from two tables, so we need a join on the common column TID.

SELECT SubName, TName FROM TEACHER, SUBJECT WHERE TEACHER.TID = SUBJECT.TID;

The same query can be written with JOIN: SELECT SubName, TName FROM TEACHER JOIN SUBJECT ON TEACHER.TID = SUBJECT.TID;
The WHERE condition keeps only the pairs of rows that have the same TID. Without it we would get 25 mixed rows.

Step 5: Result of the join:
Physics - Kartik
Chemistry - Shahnaz
Mathematics - Rajendra
Informatics Practices - Tanvi
Computer Science - Alam

Final Answer:
(I) DELETE FROM SUBJECT WHERE TID = 4;
(II) SELECT TName FROM TEACHER WHERE Experience > 5 AND City = 'Nagpur';
(III) SELECT SubName, TName FROM TEACHER, SUBJECT WHERE TEACHER.TID = SUBJECT.TID;