Online College Courses for Credit

+
VIEW & Complex Queries

VIEW & Complex Queries

Rating:
(0)
Author: Sophia Tutorial
Description:

Given an existing view, select a query to get more complex data from that view than would otherwise be possible without the view.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using views to provide a useful report on a complex set of data.

Views can also be used to help simplify very complex queries such as queries that require subqueries or ones that use aggregate data. This can get very complex without the use of views especially if you have to write out the statements each time. The use of the views will help simplify a lot of that underlying content.

For example, we may have a view created to list the calculated total of the amount per country:

CREATE VIEW invoice_country
AS
SELECT billing_country, SUM(quantity*unit_price) AS calculated_total, MAX(quantity*unit_price) AS max_total, MIN(quantity*unit_price) AS min_total
FROM invoice
INNER JOIN invoice_line ON invoice.invoice_id = invoice_line.invoice_id
GROUP BY invoice.billing_country;

This can get complex to look at specific criteria on the aggregate calculations but querying the view would result in the following results:

File:11476-2430-1.png

From there, we may want to filter that data out further by doing:

File:11477-2430-2.png

Compare this to the full statement on the base tables to get that same information:

SELECT billing_country, SUM(quantity*unit_price) AS calculated_total, MAX(quantity*unit_price) AS max_total, MIN(quantity*unit_price) AS min_total
FROM invoice
INNER JOIN invoice_line ON invoice.invoice_id = invoice_line.invoice_id
GROUP BY invoice.billing_country
HAVING MAX(quantity*unit_price) = 1.99;

File:11478-2430-3.png

We can take the data from the view and use the information to query other tables. Using the same example above, if we find that the max_total = 1.99, we may want to list all of the customers in those countries while listing all of the countries calculated criteria:

SELECT *
FROM invoice_country
INNER JOIN customer ON invoice_country.billing_country = customer.country
WHERE max_total = 1.99;

File:11479-2430-4.png

Consider what that the query would need to look like if you queried this using just the base tables to consider the complexity of that type of statement. These aggregate data scenarios like this would be very difficult to create without the use of views.

Imagine if you wanted to link all of the tables in our database together through a consistent data set to get a list of the track names that a customer has purchased. Rather than writing that query each time, having a view that joins all of the tables together may make it a lot simpler to query.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
Complex queries can be created off of views to help simplify a lot of the processing especially with aggregate functions being used.