SQL query for querying counts from a table

Question

The prompt is to form a SQL query.

That finds the students name and ID who attend all lectures having ects more than 4.

The tables are

CREATE TABLE CLASS (
    STUDENT_ID INT NOT NULL,
    LECTURE_ID INT NOT NULL
   );


CREATE TABLE STUDENT (
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(255), 
   PRIMARY KEY (STUDENT_ID)
    )

CREATE TABLE LECTURE (
 LECTURE_ID INT NOT NULL,
 LECTURE_NAME VARCHAR(255),
 ECTS INT,
 PRIMARY KEY (LECTURE_ID)
 )

I came up with this query but this didn't seem to work on SQLFIDDLE. I'm new to SQL and this query has been a little troublesome for me. How would you query this?

SELECT STUD.STUDENT_NAME FROM STUDENT STUD
INNER JOIN CLASS CLS AND LECTURE LEC ON
CLS.STUDENT_ID = STUD.STUDENT_ID
WHERE LEC.CTS > 4

How do I fix this query?

UPDATE

insert into STUDENT values(1, 'wick', 20);
insert into STUDENT values(2, 'Drake', 25);
insert into STUDENT values(3, 'Bake', 42);
insert into STUDENT values(4, 'Man', 5);

insert into LECTURE values(1, 'Math', 6);
insert into LECTURE values(2, 'Prog', 6);
insert into LECTURE values(3, 'Physics', 1);
insert into LECTURE values(4, '4ects', 4);
insert into LECTURE values(5, 'subj', 4);


insert into SCLASS values(1, 3);
insert into SCLASS values(1, 2);
insert into SCLASS values(2, 3);
insert into SCLASS values(3, 1);
insert into SCLASS values(3, 2);
insert into SCLASS values(3, 3);
insert into SCLASS values(4, 4);
insert into SCLASS values(4, 5);

Thorsten Kettner · Accepted Answer · 2019-01-31 11:34:15Z

1

The following approach might get the job done.

It works by generating two subqueries :

one that counts how many lectures whose ects is greater than 4 were taken by each user
another that just counts the total number of lectures whose ects is greater than 4

Then, the outer query filters in users whose count reaches the total :

SELECT x.student_id, x.student_name
FROM 
    (
        SELECT s.student_id, s.student_name, COUNT(DISTINCT l.lecture_id) cnt
        FROM 
            student s
            INNER JOIN class c    ON c.student_id = s.student_id
            INNER JOIN lecture l ON l.lecture_id = c.lecture_id
        WHERE l.ects > 4
        GROUP BY s.student_id, s.student_name
    ) x 
    CROSS JOIN (SELECT COUNT(*) cnt FROM lecture WHERE ects > 4 ) y
WHERE x.cnt = y.cnt ;

edited Jan 31, 2019 at 11:34

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

answered Jan 31, 2019 at 2:18

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

prat Over a year ago

For some reason, the query gives only one result from the insertion statements i have provided above. Did you miss out on anything?

Thorsten Kettner Over a year ago

@prat: The query should only return one row (namely student #3 Bake, because that's the only student who took both Math and Prog; GMB''s query had mistakenly ects=4 instead of ects>4, though, and returned student #4 Man hence. I've corrected this.)

Thorsten Kettner Over a year ago

You can shorten this query. This is probably the straight-forward way to write it. See here: dbfiddle.uk/…

GMB Over a year ago

Thanks @ThorstenKettner for the fixes !

Thorsten Kettner · Accepted Answer · 2019-01-31 11:32:34Z

As GMB already said in their answer: count required lections and compare with those taken per student. Here is another way to write such query. We outer join classes to all lectures with ECTS > 4. Analytic window functions allow us to aggregate by two different groups at the same time (here: all rows and student's rows).

select *
from student
where (student_id, 0) in -- 0 means no gap between required and taken lectures
(
  select
    student_id,
    count(distinct lecture_id) over () -
    count(distinct lecture_id) over (partition by c.student_id) as gap
  from lecture l
  left join class c using (lecture_id)
  where l.ects > 4
);

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=74371314913565243863c225847eb044

Mano · Accepted Answer · 2019-02-01 04:58:15Z

0

You can try the following query.

SELECT distinct
STUD.STUDENT_NAME,
STUD.STUDENT_ID 
FROM STUDENT STUD
INNER JOIN CLASS CLS ON CLS.STUDENT_ID = STUD.STUDENT_ID 
INNER JOIN LECTURE LEC ON LEC.LECTURE_ID=CLS.LECTURE_ID 
where LEC.ECTS > 4 group by STUD.STUDENT_ID,STUD.STUDENT_NAME 
having COUNT(STUD.STUDENT_ID) =(SELECT COUNT(*) FROM LECTURE WHERE ECTS > 4)

edited Feb 1, 2019 at 4:58

answered Jan 31, 2019 at 1:59

Mano

7883 silver badges15 bronze badges

4 Comments

prat Over a year ago

your solution for some reason provides only a few results on searching. I have updated the question with my insertion set. Please have a look.

Mano Over a year ago

I checked the your insertion set and it looks like the 2 students are attended the lecture more than 4(ECTS). The above mentioned query returns the 2 records those who are attended lecture more than 4 ECTS.

Thorsten Kettner Over a year ago

This query returns all students who took at least one lecture with ECTS > 4. The desired result, however, is all students who took all lectures with ECTS > 4.

Mano Over a year ago

Thank you @ThorstenKettner for the best understanding. I have missed the Group by in the above Query.

Collectives™ on Stack Overflow

SQL query for querying counts from a table

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related