Return schools and classes with more than 2 students enrolled in Maths and Physics | SQL Joins scenario question

 DROP TABLE IF EXISTS Students;


DROP TABLE IF EXISTS Enrollments;


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');

Comments

Post a Comment

Popular posts from this blog

50 Essential SQL Questions to Land Your Dream Job

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');
Read more