DBMS: SQL for Relational Database of a College. Tables - Student, Teachers and College

a. Find the name of Students who live in Lalitpur.

 

Attribute to find: StudentName from the Table Student.
Given condition: who lives in Lalitpur. ie; Address = 'Lalitpur'.

SELECT StudentName
FROM   Student
WHERE  Address = 'Lalitpur'; 

 

b. Find the name of the teacher who teaches the Database Management System subject.

 

Attribute to find: TeacherName from the Table Teachers

Given condition: who teaches 'Database Management System'. ie; TeachingSubject = 'Database Management System'.

SELECT TeacherName
FROM   Teachers
WHERE  TeachingSubject = 'Database Management System';  

 

c. Find the name of the teacher who teaches Computer Organization subject to student John Smith.

 

Attribute to find: TeacherName from the Table Teachers.
Condition 1: who teaches 'Computer Organization'. ie; TeachingSubject = 'Computer Organization'.
Condition 2: who teaches the student 'John Smith'. ie; StudentName = 'John Smith'.

Here, the column for the first condition(TeachingSubject) is available on the table Teachers and the column for the second condition(StudentName) is available on another table Student. Thus we will have to JOIN those two tables. To JOIN them, there needs to be a common Column but the tables Student and Teachers do not have any common column. Therefore we need to JOIN the third table College through which the common columns(RollNumber & TeacherlD) are made available for the JOIN.

Method #1: Using INNER JOIN.

SELECT TeacherName
FROM   Student
       INNER JOIN College
               ON Student.RollNumber = College.RollNumber
       INNER JOIN Teachers
               ON Teachers.TeacherID = College.TeacherID
WHERE  TeachingSubject = 'Computer Organization'
       AND StudentName = 'John Smith';  

 

Method #2: Using NATURAL JOIN.

 SELECT TeacherName
FROM   Student
       NATURAL JOIN College
       NATURAL JOIN Teachers
WHERE  TeachingSubject = 'Computer Organization'
       AND StudentName = 'John Smith';  

 

d. Insert a new tuple into relation teachers.

 

Since the values are not given we will assume some values and insert them into the Teachers table.

INSERT INTO Teachers
VALUES     (5,
            'Mr Brown',
            'JAVA');  

 

e. Delete records of students whose address is "Pokhara''.

DELETE FROM Student
WHERE  Address = 'Pokhara';