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';