Question

Consider the student-section-takes DB for an education institution:

student(id, name, dept_name, tot_cred)

section(course_id, sec_id, semester, year, building. room_number, time_slot_id)

takes(ID, course_id, sec_id, semester, year, grade)

Give a SQL query for each of the following operations:

a. Find the buildings of sections taken by Shankar. (hint: join tables, Shankar is a student name)

b. Find all id's of students who have total credit of at least 100 or taken a course with course_id: CS-101 (hint: union)

c. Find the names of students who have not taken a course with course_id: CS-101. (hint: set difference)

d. Find the student id's of students with total credits over 80 who have not taken a course with course_id: CS-101. (hint: set difference)

Answer 1

Below are the SQL queries to perform the given operations.

a) Join between table section, takes and student is done. Filter is used in where clause.

SELECT s.building
FROM section AS s
INNER JOIN takes AS t
ON s.sec_id = t.sec_id
INNER JOIN student AS st
ON st.id = t.ID
WHERE st.name = "Shankar";

b) Union between two queries are done. UNION is used to combine the result of two queries.

(SELECT id
FROM student
WHERE tot_cred >= 100)
UNION
(SELECT s.id
FROM student AS s
INNER JOIN takes AS t
ON s.id = t.ID
WHERE t.course_id = 'CS-101');

c) Set difference (Minus) is done between two separate queries. This results in the difference result set of the two queries.

SELECT id
FROM student
MINUS
SELECT ID
FROM takes
WHERE course_id = 'CS-101';

d) Here also set difference is used between the two queries. WHERE clause is used in both the queries to filter the result.

SELECT id
FROM student
WHERE tot_cred > 80
MINUS
SELECT ID
FROM takes
WHERE course_id = 'CS-101';