Skip to main content

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.


Comments

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger et...

Basic SQL Commands in Oracle Database #Diksha Oracle Training Centre

  Basic SQL Commands in Oracle Database.   Hello Everyone, In my previous blog I discussed about SQL and Classification of SQL . Today I will discuss about SQL basic commands which widely used in RDBMS. The Topics of SQL command which I am going to cover in this blog are mainly divided into four Categories: ·         DDL:   DDL consists of commands which are used to define and design the database. ·         DML:   DML consists of commands which are mainly used to manipulate the data in the database. ·        DCL: DCL Consists of commands which deal with the user permissions/access and controls in the database. ·       TCL:   TCL Consist of commands which deal with the transaction in the database. If you want to explore theory part please follow my below blog as in today blog I will discuss about how to use the commands. h...

Pseudo Column in Oracle #Diksha Oracle Training Centre

  Pseudo Column in Oracle. Hello Everyone, Today I will discuss about Pseudo column in Oracle. This topic is very important so before going to read this blog, I suggest you to please see my previous blogs which is in archive folder for your reference so that it’s easy for you to understand this topic. Let’s discuss about Pseudo Column in Oracle. Pseudo column: A pseudo-column is dynamic in Oracle as it behaves like a table column but is not stored in the table. Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but you cannot insert, update, or delete their values. A pseudo column is also similar to a function without arguments. In   Simple word we can say Pseudo column is a column that yields a value when selected, but which is not an actual column of the table. An example is RowID or SysDate. It can be use in combination with the DUAL table. Below are the Pseudo Column commonly used in Oracle Dat...