WITH Clause

Khaled Alkhudari
A free video tutorial from Khaled Alkhudari
Sr Oracle Developer/ Sr system analyst
4.5 instructor rating • 7 courses • 24,515 students

Learn more from the full course

Oracle Database 12c SQL Certified Associate 1Z0-071

Become Oracle SQL Certified ( OCA ) / Covers ORACLE university track 100%. All the presentations & scripts are attached

19:53:19 of on-demand video • Updated January 2020

  • The Full track of Oracle Database 12c: Introduction to SQL Ed 2.
  • You will be prepared 100% to pass exams Oracle Database SQL 1Z0-071 And also exam Oracle Database 12c: SQL Fundamentals 1Z0-061
  • Very strong introduction to make you understand the oracle database and RDBMS.
  • Oracle Database 12c Architecture
  • Downloading and installing oracle database 12c in details
  • Connecting to the Database
  • Retrieving data using the select statement / Restricting and Sorting Data
  • Substitution Variables
  • Using Single-Row Functions to Customize Output
  • Reporting Aggregated Data Using the Group Functions
  • Displaying Data from multiple tables using joins
  • Using Subqueries to Solve Queries
  • Using the Set Operators
  • Managing tables using DML Statments ( Insert, Update, Delete )
  • Data Definition Language / Creating tables
  • Data Dictionary Views
  • Creating sequences, synonyms, and indexes
  • Creating Views in details
  • Managing Schema Objects
  • Retrieving Data By Using Subqueries
  • Manipulating Data By Using Subqueries
  • Controlling User Access in details
  • Manipulating Data
  • Managing Data in Different Time Zones
  • Exam information / Exams Details / Exam Practice
  • many other information, you can see the full curriculum
English [Auto] I know everyone will come back and this listen we will talk about every last topic which is using that with the clause actually when we want to retrieve the data from the database we use the select statement. But Oracle and we'll see added a new feature called with the cloth and with the Lord's help you do creates complicated queries and it's very very nice option because that is a lot of these tricks and this statement will be stored in memory and it will be faster and the feeling that data. So let's start by a simple scenario and simple examples then we will go to more advanced examples. So are you. Let's start there with the close is very simple. You will write with then you will give it Elyas you can give any Elyas. So it's like a table name with ENP. So I give any just call MP as then you would open the brackets and close the brackets. And here you will mention you will select the statement. So the select statement here as select employee id come up first name from employees. So this is called the with the clause. So here I defined a variable you can name it. And I stored the result of this statement and this variable or in this Elyas. After that you will put your many query select first name from ENP. So this means that the query will read from the previous query which we defined in this Elyas. So for example I will execute the first statement this one and this is the result. I will put this result in Excel sheet and I will name the alias ENP and this is the result of that select statement. OK. Very very nice. So now I can understand that the result of this will be stored in the memory. Now I will go to the query select first name from ENP. So this is the menu query. Select first name from ENP Where is the ANP. This is the MP. Where is the first name. This is the first name. So now when I try to execute the select statement it will retrieve the first name from the query that we defined in the Elyas. Actually this is a very simple scenario and it will not give you a clear information. But I just want to give you this example just to understand what is the meaning of the with the clause. Let's take advance example for example with ENP as I defined here are first thoughts which is select Imry Id first name department ID of salary from employees. OK I will take the result of this next statement and I will put it in any scholarly MP. So I would go to the execute. Here is the Excel sheet. This is the earliest called MP or three of them three ID the first name debarment ID and the celery. So there is all of this Ikari stored in memory and in Elyas gold. MP OK very nice. So here I defined the first Elyas. Now I will put another and a score. Get some sun. So I will put to your depth some sod. OK. And the second Elyas select departement ID some salaries and give it some sod from MP group by department ID so I can see now that the second source which is the area school did some sun reading from the first query. So I will put the second degree like this select the permit ID some salaries I give it some sod from MP group by department ID. What is the ANP. This is MP so I will bring from the first query the department ID and the sum of salary and department ID. So this is the result for example the department one hundred and this is the some salaries of department one hundred. OK very nice. So I have this query ready from the query. Finally I have them in a query which is select a star from that depth some Sal and I will put them in a query select a star from some. Where is that the Phumzile. This is the result of dipt some sun. So finally my query will read from here and this query will read from here. So this is the result. OK. Very nice. So now I understand more what is the meaning of that with the close. I can execute now this little statement and it will give you the same result. I show you here. OK I can understand now more what does that mean with the close. Now lets go to another example. For example display each department name and account count for the Emrys simply. I can make select Department Id count one from Embry's group by department ID. I will execute this next. And you will find now each department on the count of employees for example the department 100 containing six employees the department one hundred ten containing two employees. So now I can take this select statement and I will put it here. I give it any small depth count and I will make join with the departments and I will make the join the primary key with the foreign key department. Id equal Department ID and it will give you this result. So I can find that department name and that can for department ID. Now how can I use this to look at a statement using that with the clause. It's very simple. You were right with dep't underscore can't. So here you find Elyas in memory called the underscore count as. And you will put here the select statement which is select the apartment ID comma count 1 employees grow by department. And here you will put them in the query and the query you will join the Elyas so select the parameter Id see a.. Where is the s.a.a. This is the C.A. right from deps coma dipped gownd. So here I use the Elyas or a query to be as a source and the join conditions and also I make a join between the key and the primary key. I would execute this and it will give you the same result but this query it will be more faster because the result of this query will be stored in memory. OK. Now let's take a very complicated example. This example I write a query to display the department name and the total salaries for departments but only for those departments whose total salaries is greater than the average salaries across departments. So here I need some information. First thing I will are three of the department name and the sum the salaries from Embry's joint departments. I will make the condition between differently and the primary key. So now I will execute this little statement. So I have. Each department on the sum of salaries for this department. So now I want to know what is the average salaries across departments. The average salary is a across department. It will be the submission of all these values divided by 11. The number of departments. So I can make like this I will put all this as a query. Then I will make up a query. Select sum total salaries divided by come to a stop. I will execute this and this is the result. So this is the average salaries across all departments. So now let's go to the question. The question now to the State Department name and total salaries for departments but only for those departments whose total salaries is greater than the average salaries across Department. So I want the submission of salaries but it will be more than this amount which is sixty four thousand two hundred ten. So simply I can do this query. Select department name come on some salaries some I the address from employees join departments and I make the conditions group body part ID. Having some of salary is greater than this value which is this selector statement OK. So now I would execute this. So this is the result. I have two departments that there are some measure of salaries greater than the average salaries across all departments. So how can I execute this statement using with the clause. It's very simple and easy with that cost as I defined areas called gipped ghost. And in this I would retrieve the department name and the sum of salaries. So this is the first Elyas. So let's go to the Excel sheet. So this is the cost that the ghost I retrieve the department name and the sum of salaries. So this is the result you will find. Each department and that's some mention of salaries. OK very nice. After that I will define another area called Average cost which is select some cell divided by country star. I give it a list called dipt average from dep't cost. So this is the second query. Select some sal divided by counter-top. I give it a high school get average from dipt cost. Where is that because this is the cost. How can I calculate this value. It is the sum of these divided by the number. So it will be this value divided by 11 and it will give you this value. OK so now I understand what this is the first query and this is the second degree the second degree reading from the first query. And now we will go to the many query and this is the main query. Select star from the coast where some some greater than select dipt average from average cost. Very nice. So this is the main query. So lets start from the deep coast where is that of course this is the direct cost. So I need to retrieve all this data but were some sun greater than this value. So the result will be selected start from the left coast where some SARL greater than this value and it will give you this result. So I would execute this on it will give you the same result. So I have any query I have another query I have another query. This is the first query the second query read from here the third the query or the query will read from here and here together to give you this result. So actually maybe it's a little complicated but I describe it for you very well and I hope that you understand what is the meaning of the clause. Thank you for listening and see you in the next village.