How to fix errors in SQL Joins?

Question

Courses

Instructors

Sections

Students

StudentSchedule

StudentSchedule Part 2

Using the database provided, Write and Execute SELECT statements to get the following information:

SIMPLE JOINS

Sections Table

1. How many total credit hours is instructor 6 teaching?

What I tried:

select sum(c.credithours) 
from courses c
join sections s on c.courseid = s.courseid
join instructors i on s.instructor = i.ID
                   and s.instructor = 6; 

ERROR:

Syntax error in FROM clause

2. Please give the CRN, Course Name, description and credithours, timedays and roomno for all the courses being taught in room 1147. Are there any conflicts?

What I tried:

select CRN, CourseName, description, credithours, timedays, roomno
from courses c
join sections s on c.courseid = s.courseid
join instructors i on s.instructor = i.ID
                   and roomno = "F1147";

ERROR:

Syntax error in FROM clause

StudentSchedule Table

1. Give a list of Student Names and IDs that are signed up for the section with CRN=30101. (List Student’s first name, last name, ID and CRN)

What I tried:

select firstname, lastname, ID, CRN 
from studentschedule ss
join students s on ss.student_id = s.id
                and CRN = 30101;

ERROR:

Syntax error in FROM clause

2. Please list all the student names(first and last) that are in the section with CRN=30115.

What I tried:

select firstname, lastname, ID, CRN 
from studentschedule ss
join students s on ss.student_id = s.id
                and CRN = 30115;

ERROR:

Syntax error in FROM clause

3. Please list all section information for all the sections that are being attended by Student with id=6.

What I tried:

select s.* 
from studentschedule ss
join sections s on ss.CRN = s.CRN
                and studentid = 6;

ERROR:

Syntax error in FROM clause

4. Advanced(Multiple tables): Please list the name of the course, the CRN and the Names of all the students that are in the section with CRN=30115.

What I tried:

select firstname, lastname, ID, CRN, coursename
from studentschedule ss
join students s on ss.student_id = s.id
join sections sec on ss.CRN = sec.CRN
join courses c on sec.courseid = c.courseid
               and CRN = 30115;

ERROR:

Syntax error in FROM clause

Answer 1

Access does not have JOIN on it's own.

You must use INNER JOIN, LEFT JOIN or RIGHT JOIN - or you will get that error.

for example: the solution to question 1 would be:

SELECT Sum([CreditHours]) AS totalHours
FROM Courses INNER JOIN Sections ON Courses.CourseID = Sections.CourseID
WHERE (((Sections.Instructor)=6));

note that you don't need the instructor table at all for this one, unless they ask for name or other details about the instructor.

also note: I wouldn't worry about optimizing using short table names on such a simple query.