CODEWITHSUNDEEP
mysqlsql
Frank Serkland
Frank Serkland

Reputation: 273

SQL -- Finding students taking 2 or more classes

I had this question in an interview, and I couldn't quite figure it out. I have three tables: a student table, a classes table and a table linking the other two. Here is a basic schema for them.

STUDENT
student_id | student_name
-----------+-------------
int        | varchar(30)

CLASS
class_id | class_name
---------+-----------
int      | varchar(30)

ROSTERS
student_id | class_id
-----------+---------
int        | int

The rosters table shows which students are taking which classes. How do I write a query showing which students are taking 2 or more classes?

Upvotes: 2

Views: 4447

Answers (5)

Christi G
Christi G

Reputation: 46

SELECT COUNT(class_id), student_id 
FROM Rosters 
GROUP BY student_id 
HAVING COUNT(class_id) >=2

This is the simplest way to do it IMO.

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

this will work:

select  s.*,r.*,c.*,count(*) 
from 
student s,
class c,
rosters r where
s.student_id=r.student_id and
c.class_id=r.class_id
group by s.student_id 
having count(*)>=2

Upvotes: 1

Nick
Nick

Reputation: 147146

This query will do what you want:

SELECT s.student_name, COUNT(DISTINCT r.class_id) AS num_classes
FROM student s
LEFT JOIN rosters r ON r.student_id = s.student_id
GROUP BY s.student_id
HAVING num_classes >= 2

It counts all the distinct class_id values in roster for each student (num_classes) and returns only students with 2 or more (HAVING num_classes >= 2).

Note I've used a LEFT JOIN to catch all students, however since you want only those with more than 1 class this is not necessary and you could use a straight JOIN.

Also note that it's not necessary to JOIN the class table for this question, however if you wanted the names of the classes the student was taking you would need to.

Upvotes: 3

nbirla
nbirla

Reputation: 610

You need to join the two tables, STUDENT and ROSTERS (I have used inner join, if required, this can be changed as per requirements), counting the number of classes each student is taking.

SELECT s.student_name, 
    COUNT(r.class_id) AS count
FROM 
    STUDENT s
INNER JOIN 
    ROSTERS r 
ON 
    r.student_id = s.student_id
GROUP BY 
    s.student_id
HAVING 
    count >= 2

Upvotes: 1

codeLover
codeLover

Reputation: 2592

You can use join, count, having and group by to get the required output:

select r.student_id,s.student_name,count(*) 
from STUDENT s inner join ROSTERS r
on s.student_id = r.student_id
group by r.student_id,s.student_name
having count(*)>=2;

Upvotes: 1

Related Questions