Online College Courses for Credit

+
VIEW to Simplify Queries

VIEW to Simplify Queries

Rating:
(0)
Author: Sophia Tutorial
Description:

Create a VIEW that shows data from multiple tables.

(more)
See More
Tutorial

what's covered
This tutorial explores using views to join multiple table data to simplify queries in two parts:
  1. Combining Data
  2. Complex Query Example

1. Combining Data

Beyond just querying from a single table, you can also combine data from multiple tables as it is based on a select statement. For example, seeing the support_rep_id may not be extremely useful in an organization unless you know who that value belongs to. Instead, you could include the employee’s name that is the support rep similar to the following:


CREATE VIEW customer_contact
AS
SELECT customer.*, employee.first_name as support_first_name, employee.last_name as support_last_name
FROM customer, employee
WHERE customer.support_rep_id = employee.employee_id;

If we queried the customer_contact view, it would look like the following:


SELECT * 
FROM customer_contact;

table

We could further add to the query to include the necessary columns:


SELECT first_name, last_name, support_first_name, support_last_name 
FROM customer_contact;

table

If we wanted to write this out using the tables, we would have to do the following each time:


SELECT customer.first_name, customer.last_name, employee.first_name as support_first_name, employee.last_name as support_last_name
FROM customer, employee
WHERE customer.support_rep_id = employee.employee_id;

2. Complex Query Example

Let us take a look at a bit more complex query with the use of more than 2 tables. So far, when we query the tables for the tracks, we are looking just at the id values. This is not telling as you may want to get the artist’s name, album title, and track name at the same time. Creating a view for this purpose can simplify this process:


CREATE VIEW artist_album_track
AS
SELECT artist.name as artist_name, album.title as album_title, track.name as track_name
FROM artist 
INNER JOIN album ON artist.artist_id = album.artist_id
INNER JOIN track ON album.album_id = track.album_id;

Rather than querying the tables to get that list:  


SELECT artist.name as artist_name, album.title as album_title, track.name as track_name
FROM artist 
INNER JOIN album ON artist.artist_id = album.artist_id
INNER JOIN track ON album.album_id = track.album_id;

We can simply query the view directly like:


SELECT * 
FROM artist_album_track;

table

If we wanted to add some filters into our SELECT statement such as only listing the rows that belong to AC/DC, instead of doing:


SELECT artist.name as artist_name, album.title as album_title, track.name as track_name
FROM artist 
INNER JOIN album ON artist.artist_id = album.artist_id
INNER JOIN track ON album.album_id = track.album_id
WHERE artist.name = 'AC/DC';

table

We would query the view like:


SELECT * 
FROM artist_album_track
WHERE artist_name = 'AC/DC';

table

The second option just greatly simplifies the query without having to join each of the tables together.


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
Views allow us to join multiple table data to simplify queries.

Source: Authored by Vincent Tran