Important Queries in SQL Part 5 #DOTC_Mdb

Important Queries in SQL Part 5.

In my previous blogs I discussed about several SQL queries which commonly used in SQL and today again  I will discuss about some important queries in SQL . Before that I suggest you to go through my SQL and PL/SQL blogs in archive May and June folder for your understanding.  Also you can see my below previous blogs about Queries in SQL for your reference.

https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part1-dotcmdb.html

https://dheeraj60.blogspot.com/2020/07/important-sql-queries-part-2-dotcmdb.html

https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part3-dotcmdb.html

https://dheeraj60.blogspot.com/2020/07/important-queries-in-sql-part-4-dotcmdb.html

1.  Co- Related Sub Query Example : Let’s take an example for a typical correlated sub query. In this example, we need to find all employees whose salary is above average for their department.

Below is the Query :

SELECT emp_id, emp_name

 FROM department dept

 WHERE salary > (

 SELECT AVG(salary)

 FROM department

 WHERE dept_no = dept.dept_no);

 

Here the outer query is SELECT emp_id, emp_name FROM department dept WHERE salary >  and the inner query that is correlated sub query is SELECT AVG(salary) FROM department    WHERE dept_no = dept.dept_no;

Note : In the above nested query the inner query has to be re-executed for each department . Please refer my previous blog that is sub query and correlated sub query for better understanding.

Below is the output of above Query :

2.  Example of Co-related Sub Queries using joins to obtain the required data. We use the same department table which we used in Previous Query.

Below is the Query will explain it later

SELECT department.emp_id, department.emp_name

   FROM department INNER JOIN

     (SELECT dept_no, AVG(salary) AS department_average

       FROM department

       GROUP BY dept_no) a ON department.dept_no = a.dept_no

   WHERE department.salary > a.department_average;

 

Note: This sub query is not correlated with the outer query, and is therefore executed only once. Also please refer my previous blog related to sub query and co-related sub query for better understanding.

Output of above query is as below :

3 . Another example of co-related Sub query with the 2 table employee and department.

 

SELECT a.emp_name,a.salary,a.dept_no,a.location,a.emp_id

FROM department a

WHERE a.emp_id IN (

SELECT b.emp_id

FROM employee b WHERE b.emp_name in('Dheeraj','Minu'));

 

Here we use two table employee and department and the inner of the above query returns the emp_id 1000 and 2000.

 

Output of above query is as below :

4 . Taking one example from outer join whether I want matching records from department table and all records from department table  whose salary is greater than 60000.

In this scenario we use outer Join :

Below is the Query :

select a.emp_name, a.location ,

b.manager_id

from department a LEFT JOIN

employee b on a.emp_id=b.emp_id

where a.salary >60000;

In Above query we can see all the result from department and only the matching records from employee and for filter we put salary is greater than 60000.

Output of above query is as below :

 

Here we can see manager id from employee table is coming null for some records as we need only matching records from employee table and all the records from department table with filter condition salary is greater than 60000.

 

5 . In same Manner we can use Right Outer join to see all the records from employee table and only the matching records from department table . Below is the query for that:

 

select a.emp_name, a.location ,

b.manager_id

from department a RIGHT JOIN

employee b on b.emp_id=a.emp_id

where a.salary >60000;

 

Here we can see the manager_id as it gives all the records from department table as per our requirement.

Output of above query is as below :

Please go through this blog carefully and try to write as many queries as you can according to your requirement and let me know if you have any doubts. I will suggest you to please go through my previous blogs related to topics sub query and correlated sub query and Joins .

Thanks.