Beginner SQL database query

Question

I am learning about databases and queries. With the following tables - that I cannot restructure - I am trying to find two things:

1. Names of CS major students taking a Database class
2. Names of students taking less than 9 credits and have a GPA lower than 3.5

StudentTable
SID  NAME  YEAR    MAJOR GPA
1111 Smith Senior   CS   3.51
2222 Jones Freshman IT   4.00
3333 Jacks Junior   CS   2.75
4444 Johns Senior   CS   3.12

CourseTable
COURSEID CNAME    CREDITS
1111     Reading    1
2222     Counting   1
3333     Drawing    3
4444     Swimming   3
5555     Physics    3
6666     Database   3
7777     Hacking    3
8888     Emailing   3
9999     Internship 1

EnrollmentTable
SID  COURSE1 COURSE2 COURSE3 COURSE4
1111  9999    null    null    null
2222  1111    2222    3333    4444
3333  6666    7777    8888    null
4444  8888    9999    null    null

I got started with my first query in this way, but I have a syntax error:

DECLARE @courseID varchar(10) = (
    select COURSEID from Assignment5Schema.CourseTable
        where CNAME = 'Database'
)

SELECT StudentTable.NAME from EnrollmentTable
where (
    COURSE1=@courseID or
    COURSE2=@courseID or
    COURSE2=@courseID or
    COURSE3=@courseID or
    COURSE4=@courseID
) and StudentTable.MAJOR = 'CS'

UPDATE: I'm now trying it this way based on another user's answer. I'm trying to modify it, as I find that it's not correctly filtering only CS majors. I believe it's working now.

SELECT s.name
FROM student s
JOIN enrollment e ON e.sid = s.sid
JOIN course c ON s.major = 'CS' AND c.cname = 'Database' AND (c.courseid = e.course1 OR c.courseid = e.course2 OR c.courseid = e.course3 OR c.courseid = e.course4);

Answer 1

To get the name of CS majors taking the database class, you will need to inner join the tables together. You can join the student and enrollment table where the studentid matches, and then you can join that with the course table. You can also include in your join condition that the class name be database. That would look like this:

SELECT s.name
FROM student s
JOIN enrollment e ON e.sid = s.sid
JOIN course c ON c.cname = 'Database' AND (c.courseid = e.course1 OR c.courseid = e.course2 OR c.courseid = e.course3 OR c.courseid = e.course4);

The tables will only be joined on rows where one of the columns in the student's row is the database class.

For the second one, start by breaking it into parts. Finding students with a GPA lower than 3.5 is easy, as all you need is a where clause:

SELECT s.name
FROM student s
WHERE s.gpa < 3.5;

I don't want to do your entire assignment for you, so I hope that the ideas presented will help you finish up the second one. What you'll need to do is search the enrollment table for students who are taking less than 9 credits, and join it with the query above to get students who have both less than 9 credits and a gpa less than 3.5

I've tested some of it in an SQL Fiddle you can use to test your work.

EDIT

The first thing I did to solve the second one was join on any conditions where the courseid matched. This gives a table that contains a row for each student and course. In other words, one row for student one, four rows for student two, and so on. See the next Fiddle link for an example.

SELECT *
FROM enrollment e
JOIN course c 
   ON c.courseid = e.course1 
   OR c.courseid = e.course2 
   OR c.courseid = e.course3 
   OR c.courseid = e.course4;

I then realized that I could group the rows by student id and sum them on the condition that the sum was less than 9:

SELECT e.sid, SUM(c.credits) AS totalCredits
FROM enrollment e
JOIN course c ON c.courseid = e.course1 OR c.courseid = e.course2 OR c.courseid = e.course3 OR c.courseid = e.course4
GROUP BY e.sid
HAVING totalCredits < 9;

Then, as I said, I could join the previous two subqueries together and get their names, like this:

SELECT t.name
FROM(
  SELECT s.sid, s.name
  FROM student s
  WHERE s.gpa < 3.5) t
JOIN(
  SELECT e.sid, SUM(c.credits) AS totalCredits
  FROM enrollment e
  JOIN course c ON c.courseid = e.course1 OR c.courseid = e.course2 OR c.courseid = e.course3 OR c.courseid = e.course4
  GROUP BY e.sid
  HAVING totalCredits < 9) w ON w.sid = t.sid;

Here is the updated Fiddle.

Answer 2

Select s.Name 
from StudentTable s 
left outer join Assignment5Schema a on s.SID = a.SID 
where (COURSE1=@courseID or
        COURSE2=@courseID or
        COURSE2=@courseID or
        COURSE3=@courseID or
        COURSE4=@courseID) 
        and s.Major = '%CS%'

If you have any control over the structure of the tables, I would change that assignment table. Break it up.

|SID| COURSE #| SLOT|

Where slot is 1 - 4.

That way you can do queries against the Course Numbers and not against the attributes of the table itself... if that makes sense.