In this Course, we go beyond the basics to empower you with the skills, how-to knowledge, and hands-on expertise to work through and solve complex business issues with SQL. We go deep and hands-on with transactions, subqueries, inline views, summarised queries, the exists predicate and set operations.
This Course is built from content created and delivered by Ian Wallington of QA.
Hello and welcome back. Now let's learn about inline views. Let's start by writing a query that is going to be used as an inline view. Let's start by writing a query that is going to be used as an inline view. Our mission is to find all sales and then with each sale, show the biggest sale for that company. The first part of this is to get a result set, company and maximum order value. Then we want number and Max . FROM sale we wanna GROUP BY a company number. We could create a table with that information in it. You create a temporary table or you could create a view that will retrieve that information. Once you have the information, you could write a SELECT statement to go through the sale table and retrieve data from the sale table and then JOIN it to this table ON with an ON statement, company underscore number and retrieve this value. Because this SELECT statement is going to be used in a FROM clause in lieu of a table or view, I'm going to select sa.order_no, sa.company_no and I'm also going to get the max_value. I also want the order_value. So let's put the order_value in here. So that is data from the sale table but I want to JOIN it so that I can I can put the max_value at the top of the results set. I want to JOIN this sale to the sale table using the SELECT statement. Essentially it's a form of subquery. I'm gonna close the parentheses. That is what is known as an inline view. An inline review requires a table alias. Then to JOIN statement, this company, then the ON clause and the ON clause. The condition is that the value must be joined to this value. That is an example of an inline view. Fundamentally if you have an inline view, you have to give it a table alias. In my SQL terminology, that would be a table subquery. You could take that SELECT statement and create a named view and then join that view to the sale table. But the argument here is that we don't want to do that. We don't want that overhead or maybe we're not allowed to create actual named views. So this gives us the capability to JOIN to a view where the view is defined somewhere else. Start with the subquery, add the main query, do the JOIN and the add an ORDER BY statement. It's an inline version of what you could do with a view. So in my SQL terminology, it's a table subquery. That ends our session on inline views.
Andrew is fanatical about helping business teams gain the maximum ROI possible from adopting, using, and optimizing Public Cloud Services. Having built 70+ Cloud Academy courses, Andrew has helped over 50,000 students master cloud computing by sharing the skills and experiences he gained during 20+ years leading digital teams in code and consulting. Before joining Cloud Academy, Andrew worked for AWS and for AWS technology partners Ooyala and Adobe.