By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE students (
StudentName VARCHAR(255),
StudentID VARCHAR(255)
);
INSERT INTO students VALUES ('Biplab', '04CS9501'),
('Amit', '05CS3001'),
('Rahul', '05CS3003'),
('Divya', '05CS3004'),
('Praveen', '05CS3005');
CREATE TABLE courses (
CourseName VARCHAR(255),
CourseID VARCHAR(255)
);
INSERT INTO courses VALUES
('Business and Science', 'B3D'),
('Economics', 'B3B'),
('Business and Laws', '63O1'),
('Economics and Laws', '63K1'),
('Economics and Science', 'B3E');
CREATE TABLE participation (
CourseID VARCHAR(255),
StudentID VARCHAR(255)
);
INSERT INTO participation VALUES
('B3D', '04CS9501'),
('B3D', '05CS300'),
('B3D', '05CS3003'),
('B3B', '05CS3003'),
('B3B', '05CS3004'),
('63O1', '04CS9501'),
('63O1', '05CS3004'),
('63K1', '05CS3001'),
('63K1', '05CS3003'),
('63K1', '05CS3004');
-- to get all empty courses
SELECT * FROM courses WHERE NOT CourseID IN (SELECT CourseID FROM participation)
| CourseName | CourseID |
|---|---|
| Economics and Science | B3E |
-- to get all students
SELECT * FROM students WHERE NOT StudentID IN (SELECT StudentID FROM participation)
| StudentName | StudentID |
|---|---|
| Praveen | 05CS3005 |
SELECT 'Student' AS type, s.StudentID AS 'ID', s.StudentName AS 'Name'
FROM students s LEFT JOIN participation p ON s.StudentID = p.StudentID
WHERE p.CourseID IS NULL
UNION ALL
SELECT 'Course' AS type, c.CourseID, c.CourseName
FROM courses c LEFT JOIN participation p ON c.CourseID = p.CourseID
WHERE p.StudentID IS NULL
ORDER BY type, Name
| type | ID | Name |
|---|---|---|
| Course | B3E | Economics and Science |
| Student | 05CS3005 | Praveen |