Students and Stationaries

 

Write an SQL query to fetch the student ID from the Students table who bought all the stationary items in the Stationary_Items table.

Input :-

Students table :-
There is no Primary Key column for the Students table.

ID	Stationary
1	Pen
3	Pencil
3	Scale
2	Pencil
1	Scale
3	Pen
1	Pencil

Stationary_Items table :-

Stationary is the Primary Key column for the Stationary table.

Stationary

Pen

Pencil

Scale

Output :-

ID

1

3

Query :-

select ID
from Students
group by ID
having count(distinct Stationary) = (select count(*) from Stationary_Items);

Explanation : - 

This query selects the ID from the "Students" table and groups the records by ID. It then filters the grouped records by the number of distinct Stationary items for each ID. The count of distinct Stationary items for each ID from the Students table must be equal to the total count of items in the "Stationary_Items" table.

In other words, this query retrieves the ID's of students who have bought all the available stationary items in the Stationary table.