Subquery
The SQL Query statement that enclosed within another query statement is called Subquery.
Types of Subqueries:
- Single Row Subquery - The subquery return zero or one value. The outer query will process that value and return only row.
- Multiple Row and Column Subquery- The subquery return zero or one value. The outer query will process that value and return one or multiple row.
- Correlated Subquery- The subquery return multiple value to outer query and outer query will process each value return from subquery.
Single Row Subquery
Employee Table
id | Emp_name | Age | Salary | Dept_id |
1 | Jebas | 35 | 80,000 | 2 |
2 | Thomas | 30 | 40,000 | 1 |
3 | Mark | 25 | 40,000 | 2 |
4 | Harry | 42 | 50,000 | 2 |
5 | Willam | 45 | 100,000 | 1 |
6 | James | 32 | 35000 | 3 |
7 | Mock | 22 | 15000 | 3 |
SubQuery to display name of employee who get maximum salary.
SELECT
Emp_name
FROM Employee where salary=(SELECT
Max(salary)
FROM Employee);
Employee
Emp_name |
Willam |
SubQuery to display name of employee who get Minimum salary.
SELECT
Emp_name
FROM Employee where salary=(SELECT
MIN(salary)
FROM Employee);
Employee
Emp_name |
Mock |
Multiple Row SubQuery.
Now we add one extra table called Dept_table.
Dept_table
Dept_id | Dept_name |
1 | Director |
2 | Manager |
3 | Employee |
4 | HR |
5 | TeamLeader |
6 | Developer |
7 | Tester |
To display the name of employee whose designation was manager. The subquery return the manager id as 2 and the outer query return name of employee whose dept_id=2.
SELECT
Emp_name
FROM Employee where dept_id=(SELECT
dept_id
FROM dept_tablewhere dept_name='Manager');
Employee
Emp_name |
Jebas |
Mark |
Harry |
Correlated Subquery.
Employee Table
id | Emp_name | Age | Salary | Dept_id |
1 | Jebas | 35 | 80,000 | 2 |
2 | Thomas | 30 | 40,000 | 1 |
3 | Mark | 25 | 40,000 | 2 |
4 | Harry | 42 | 50,000 | 2 |
5 | Willam | 45 | 100,000 | 1 |
6 | James | 32 | 35000 | 3 |
7 | Mock | 22 | 15000 | 3 |
SELECT
Emp_name,salary
FROM Employee outer where salary>(SELECT
Avg(salary)
FROM Employee where dept_id=outer.dept_id );
This query return the employee name who get salary more than average salary in their respective department.
Emp_name | Salary |
Thomas | 80,000 |
Willam | 100,000 |
James | 35000 |
INSERT statement with Subquery
If we want maintain separate table for Manager.
manager_table
id | Emp_name |
We can insert multiple value to manager_table directly from Employee table.
INSERT into manager_table
SELECT ID,Emp_name from Employee where dept_id IN (SELECT dept_id from dept_table where dept_name ='manager');
The query mark as red color return dept_id of manager and The query mark as green color return Emp_name and id of manager and insert value into manager table
manager_table
id | Emp_name |
2 | Thomas |
3 | Mark |
4 | Harry |
Update Statement with Subquery
UPDATE Employee SET salary = salary+0.25*salary
where Age in (SELECT Age FROM Employee where Age>40).
This statement increment the salary of employee whose age above 40. The Output:
Employee
id | Emp_name | Age | Salary | Dept_id |
1 | Jebas | 35 | 80000 | 2 |
2 | Thomas | 30 | 40000 | 1 |
3 | Mark | 25 | 40000 | 2 |
4 | Harry | 42 | 62500 | 2 |
5 | Willam | 45 | 125000 | 1 |
6 | James | 32 | 35000 | 3 |
7 | Mock | 22 | 15000 | 3 |
Delete Statement with Subquery
DELETE FROM Employee
where Age in (SELECT Age FROM Employee where Age>40).
The statement will remove the employee whose age greater than 40
Thank You. Like Share and Subscribe 👍 folowlect.com
Comments
Post a Comment