CODEWITHSUNDEEP
sql
dgbarrett
dgbarrett

Reputation: 13

Selecting SQL data based on multiple separate tables

I'm doing some SQL practice and have been stumped by the following question.

I'm given the database schema:

Course (Course#, title, dept)
Student (Student#, name, program)
Enrolled (Student#, Course#, grade)

I'm trying the translate the following statement to SQL:

List the names of all students who takes Computer courses or Science courses.

Initially I thought the answer might be something like this:

SELECT Sname 
FROM Course,Student,Enrolled
WHERE Course.dept = "Computer" OR Course.dept = "Science"

However, I feel like the rows in the table are not joined quite how I imagined, and that there is something off with this. How far off am I?

Upvotes: 0

Views: 40

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

This is not that simple: first, you need to join the tables, and then you need to group by name to eliminate duplicates:

SELECT s.name 
FROM Student s
JOIN Enrolled e ON s.Student#=e.Student#
JOIN Course c ON e.Course#=c.Course#
WHERE c.dept = 'Computer' OR c.dept = 'Science'
GROUP BY s.name

GROUP BY is necessary because the same student may be taking both "Computer" and "Science" courses, in which case JOIN would produce multiple records for the same student. In this case you have an option of replacing it with DISTINCT.

Upvotes: 2

codeguy
codeguy

Reputation: 700

If you have 2 courses Computing (1) and Science (2) with the IDs 1 and 2, you need to do a query like this:

SELECT s.first_name, s.last_name FROM students s JOIN enrolled e ON e.student_id = s.id WHERE e.course_id IN(1, 2)

Sorry may have misread, if you need to do it by course type and the courses are tagged as dept = Computer, Science, Literacy etc... Do the following query:

SELECT s.first_name, s.last_name FROM students s JOIN enrolled e ON e.student_id = s.id JOIN courses c ON c.id = e.course_id WHERE c.dept IN('Computing', 'Science')

Or if you want to do an OR:

SELECT s.first_name, s.last_name FROM students s JOIN enrolled e ON e.student_id = s.id JOIN courses c ON c.id = e.course_id WHERE c.dept  = 'Computing' OR c.dept = 'Science'

Upvotes: 0

Related Questions