/* 028- SubQuery With EXISTS & NOT EXISTS */
-- create table "tOne" and insert records --
CREATE TABLE tOne(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO tOne(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","13","M",1,1),
(2,"Sarita Kumari","56","21","F",2,2),
(3,"Salman Khan","62","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,1),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","52","20","M",1,3);
-- create table "courses" and insert records --
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- use SubQuery --
SELECT name FROM tOne
WHERE courses = (SELECT course_id FROM courses WHERE course_name = "MBA");
SELECT course_id FROM courses WHERE course_name = "MBA";
SELECT name FROM tOne
WHERE courses IN (SELECT course_id FROM courses WHERE course_name IN ("MBA","Btech"));
-- Exists & Not Exists --
SELECT name FROM tOne
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));
SELECT name FROM tOne
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));
SELECT name FROM tOne
WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));
SELECT name FROM tOne
WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));