Stuck! How do I join three SQL tables to answer these two questions? Or do I not need to?

Question

I'm fairly certain I'll need to join three SQL tables to answer these two questions, but I can't figure it out! Question 1 has me completely stumped, but I think I'm close with question 2. It's important to keep in mind that this database doesn't actually exist, only in theory.

Both Question 1 and 2 use the same tables:

Classes
ID
Name

Students
ID
Name

ClassesStudents
ClassID
StudentID

Question 1: Write a SQL query that will return the name of each class and how many students are taking it.

Question 2: Write a SQL query that will return the names of classes that the student named "John" is taking. Assume there is only one student with that name in the database.

My guess on Question 1 (WIP):

SELECT ClassesStudents.StudentID, ClassesStudents.ClassID, 
Classes.Name
FROM ClassesStudents, Classes;

My guess on Question 2:

SELECT Classes.Name
FROM Students
JOIN ClassesStudents ON ClassesStudents.StudentID=Students.ID AND 
Students.Name = "John"
JOIN Classes ON Classes.ID=ClassesStudents.ClassID

Can anyone please help me out? I've Googled everything I could think of :/

Question 1 Hint: `JOIN` ClasssesStudents and Classes, `COUNT()` and `GROUP BY` — kc2018, Apr 04 '18 at 01:01
Still not following :/ Would you be able to provide the answers so that I may dissect them? That's how I learn best. — LiaAbrams, Apr 04 '18 at 01:05
How about you work on a query to give you studentID, classID, className first (without `COUNT` and `GROUP BY`)? Also put the table structure (the table name and field list) into the posting instead of a screenshot. — kc2018, Apr 04 '18 at 01:08
Post your query and I will add `COUNT` and `GROUP BY` if you still need help. — kc2018, Apr 04 '18 at 01:11
Okay, I added the table names and field lists, as well as my query to give me studentID, classID, and className. Am I on the right path? — LiaAbrams, Apr 04 '18 at 02:07
Good effort. Note that the tables need to be joined. After that add the COUNT function and Group By. See answer below. — kc2018, Apr 04 '18 at 02:18

Wince · Answer 1 · 2018-04-04T01:21:06.490

0

For question 1, just join all the tables together Classes.ID = ClassesStudents.ClassID, ClassesStudents.StudentsID = Students.ID, then count the Student table and group by classes.name For Question 2, you can use back the joining from question 1 and put a "WHERE" condition after the joining...

edited Apr 04 '18 at 01:21

answered Apr 04 '18 at 01:08

Wince

21
3

Thanks for your help. I updated my answer for Q1. Am I going in the right direction? – LiaAbrams Apr 04 '18 at 02:07

kc2018 · Answer 2 · 2018-04-04T02:32:53.870

0

Question 1:

SELECT  
Classes.Name, COUNT(*)
FROM ClassesStudents 
JOIN Classes
ON ClassesStudents.ClassID = Classes.ID
GROUP BY Classes.Name

Assume no two classes have the same name. Otherwise include the ClassID in the query:

SELECT ClassesStudents.ClassID,
Classes.Name, COUNT(*)
FROM ClassesStudents 
JOIN Classes
ON ClassesStudents.ClassID = Classes.ID
GROUP BY ClassesStudents.ClassID, Classes.Name

edited Apr 04 '18 at 02:32

answered Apr 04 '18 at 02:18

kc2018

1,440
1
8
8

Thank you! I think it's starting to make sense. Was I close with Q2? – LiaAbrams Apr 04 '18 at 02:23
Q2 should work but I would use single quote instead of double quote `'John'`. Refer to https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql – kc2018 Apr 04 '18 at 02:41
For Q2 you could put `StudentsName = 'John'` as a condition of `WHERE` clause: `SELECT Classes.Name FROM Students JOIN ClassesStudents ON ClassesStudents.StudentID=Students.ID JOIN Classes ON Classes.ID=ClassesStudents.ClassID WHERE StudentName = 'John'` – kc2018 Apr 04 '18 at 02:44
You could test your query in sqlfiddle.com Here is the fiddle I created to test the queries http://sqlfiddle.com/#!9/277fa5 – kc2018 Apr 04 '18 at 02:46
[SQL join: where clause vs. on clause]( https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) – kc2018 Apr 04 '18 at 02:55

score 0 · Answer 3 · answered Apr 04 '18 at 02:31

Here you go: Key points are in bold, for Q1 you only need 2 tables. Attached is the relationships graph.

Question 1:
SELECT Classes.ClassName, Count(ClassesStudents.StudentID) AS CountOfStudentID
FROM Classes INNER JOIN ClassesStudents ON Classes.ClassID = ClassesStudents.ClassID
GROUP BY Classes.ClassName;

Question 2:
SELECT Classes.ClassName
FROM Students INNER JOIN (Classes INNER JOIN ClassesStudents ON Classes.ClassID = ClassesStudents.ClassID) ON Students.StudentID = ClassesStudents.StudentID
WHERE (((Students.StudentName)="John"));

Stuck! How do I join three SQL tables to answer these two questions? Or do I not need to?

3 Answers3