SQL Interview Question | SQL Intermediate Question 22

Question - Return schools and classes with atleast one student enrolled in Maths and Physics.
DDL :-
drop table if exists students
CREATE TABLE Students (
StudentID INT NOT NULL PRIMARY KEY,
School VARCHAR(100) NOT NULL,
Class VARCHAR(50) NOT NULL
);
CREATE TABLE Enrollments (
StudentID INT NOT NULL,
Subject VARCHAR(100) NOT NULL
);
INSERT INTO Students (StudentID, School, Class) VALUES
(1, 'Greenfield High', '10A'),
(2, 'Greenfield High', '10A'),
(3, 'Blue River School', '10A'),
(4, 'Blue River School', '10A'),
(5, 'Sunshine Academy', '8C'),
(6, 'Sunshine Academy', '8C'),
(7, 'Greenfield High', '10A'),
(8, 'Blue River School', '9B'),
(9, 'Sunshine Academy', '8C'),
(10, 'Greenfield High', '11A');
INSERT INTO Enrollments (StudentID, Subject) VALUES
(1, 'Maths'),
(1, 'Physics'),
(2, 'Maths'),
(2, 'Physics'),
(3, 'Maths'),
(3, 'Chemistry'),
(4, 'Physics'),
(4, 'Maths'),
(5, 'Maths'),
(6, 'Physics'),
(6, 'Maths'),
(7, 'Physics'),
(8, 'Maths'),
(8, 'Physics'),
(9, 'Chemistry'),
(10, 'Maths');
select * from Students;
select * from Enrollments;
#ai #facebook #meta #amazon #tcs #hiring #placement #infosys #dataengineer #sql #dataanalytics #college

[@tanmoykarmakar3917]

WITH cte AS (select student_id, school, class, subject from students where subject IN ('maths','physics'))
select school, class, COUNT(DISTINCT student_id) AS student_count from cte
where student_id IN (select student_id from cte group by student_id having COUNT(subject) >=2)
group by school, class
having student_count >=1; This will be the answer