Nested Queries on sample exercise
Aim:- To Execute Subqueries
Theory:-A subquery in MySQL is a query, which is nested into another SQL query and embedded with SELECT, 
INSERT, UPDATE or DELETE statement along with the various operators. We can also nest the subquery with another
subquery. Asubquery is known as the inner query, and the query that contains subquery is known as the outer query. 
The inner query executed first gives the result to the outer query, and then the main/outer query will be performed. 
MySQL allows us to use subquery anywhere, but it must be closed within parenthesis. All subquery forms and operations
supported by the SQL standard will be supported in MySQL also.
QUERY:-
CREATE TABLE department(dept_id 
INT PRIMARY KEY, dept_name
VARCHAR(50)
);
INSERT INTO department
VALUES
(1,'H-R'),
(2,'Finance'),
(3,'Accounts'),
(4,'Administration'),
(5,'Counselling');
CREATE TABLE employee( emp_id 
INT PRIMARY KEY,name
VARCHAR(500),
gender VARCHAR(50),age 
INT,
salary INT,
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES
department(dept_id)
);
INSERT INTO employee
VALUES
(1,'Ali','M',23,24000,3),
(2,'Anup','M',24,25000,4),
(3,'Akshay','M',22,22000,1),
(4,'Akshat','M',21,65000,2),
(5,'Rahul','M',23, 22000,4);
-- THIS IS NESTED QUERY--
SELECT * from employee
WHERE dept_id =(SELECT dept_id FROM departmentWHERE dept_name='H-R');
29
 
Subqueries:-
CREATING TABLE FOR SUBQUERY
CREATE TABLE department(id
INT primary key,
name varchar(100) NOT NULL,
gender varchar(50) NOT NULL,
city varchar(20) NOT NULL,
salary int NOT NULL
);
30
INSERT INTO department(id,name,gender,city,salary)
VALUES
(1,'Ram Kumar','M','Rajasthan',12000),
(2,'Neeraj Singh','M','MP',15000),
(3,'Devansh Sharma','M','Delhi',30000),
(4,'Rahul Kalia','M','UP',40000),
(5,'Akshat Jain','M','UP',50000);
QUERY 1:-
SELECT * from department where id
IN(SELECT id from department where salary>12000);