1z0-071 Oracle Database SQL – USING SUBQUERIES TO SOLVE QUERIES

1. SUBQUERIES – DEFINITION AND SUBQUERY IN THE SELECT CLAUSE

Sub queries. Let us assume that I want to see today’s Date, the number of employees I have and how much salary I pay. I can do all this by running three queries separately select Sys Date from Dual gives me today’s Date select Count Star from Employees gives me the total number of employees I have and Select Some Salary from Employees gives me the total salary that I am paying. But these are three separate queries with separate results. What if I want to see them as a part of single result set? This is where sub queries come into picture a query can have another query inside like this here I am using two queries in the select class these queries are called as sub queries or inner queries the main query is called as an outer query.

Let’s look at the query. Select Sys date gives me today’s date select Count Star from Employees gives me total number of employees and select some salary from Employees gives me the total salary that I am paying and I have combined these into a single query where these two queries form as sub queries or inner queries. In this main outer query. This brings all the results in a single result result set. This is also an example of using subqueeries in the select class. Sub queries can be used also in from class where class group by class and having class.

2. SUBQUERY – IN THE WHERE CLAUSE

Look at this query and its result. Select first name. Last name employee ID manager ID from Employees Here, Nina’s Employee ID is 101. Her manager’s. ID is is 100. And that would be Stephen because his employee ID is 100. So you can deduce that if an employee ID such as Hundred is part of this Manager ID, then that employee is a Manager. Now I want to find out the details of those who are managers. For that I can use this SQL which is select first name, last name, phone number from employees. Where employee ID in. Select Manager ID from employees.

Let me run this. Basically, I need to tell bring me the information of those whose Employee ID is present in the Manager ID column again. Whose employee ID is present in the manager ID column. I can do it effectively by using this sub query select Manager ID from Employees which will bring me a set of Manager IDs like this. Now I need to bring the results of every rows whose Employee ID is present in the set brought by this inner query. By using the condition where employee ID in select manager ID from employees. Let me me run it again so it brings me the result of just the managers. This is an example of using sub query in the where class.

3. SUBQUERY – IN THE FROM CLAUSE

I want to see the maximum salary paid in each department. Let us accomplish that in a single query using a subquery select salary department name from employees join departments on employee department ID equals to department department ID. This query brings us the salary and department name of all the employees. Here I am joining the employees table with departments table on Department ID columns. Now, I am going to write a sequel where this sequel would be a subquery of that. So basically I will tell that SQL to select from this sequel’s result. So instead of specifying a table name in the from class, I am specifying a sub query which is this whose result will act as a data source in the from class. Let me run it. Okay, so this is an example of using a subquery in the from class.

4. SUBQUERY – IN DMLs – IN INSERT UPDATE DELETE

Let us use the subqueeries in the Dml statements such as insert, update, and Delete. Select star from tab one. Select Star from tab two. I’m going to create a copy of those tables. Create table sub tab one as select star from tab one. Create table sub tab two as select star from tab two. Let us verify the data. Select Star from sub tab one. Select Star from sub tab two. Now I am going to insert rows into subtap one by reading from subtap two. Insert into subtap one. Call one. Call two. Select call three. Call four from sub tab two. It inserted three rows. Let me commit it. Verify. Select star from tab one. I see the rows from tab two inserted into it. Now select max call three from subtap two.

The answer is four. I’m going to update every value of call one in subtap one with the maximum value from subtap two. So I’m going to use this subquery in update statement. The previous one was an example of using a subquery in the insert statement update subtab one. Set call one equals to select max call three from subtap two. Firewalls updated commit. Verify everything is four. Now select call four from subtap two. Def. Now I am going to delete from subtab one where column two is in select column four from sub tab two. So this is an example of using a sub query in the delete statement, three rows deleted, commit. Select Star from sub tab one. Okay.