Views aren’t complicated – if you’ve got half an hour, we’ll get you started writing SQL queries using views!
Let’s start by answering the question “What is a view in SQL?’. A view is a database object (as is a table, an index, or a stored procedure). Like a table, you can query a view and extract the information in it. It can be used in the
FROM clause of a
SELECT, and you can reference view columns in clauses like
GROUP BY among other clauses as well.
However, views and tables differ in one important way: Views do not store any records anywhere. Tables store records physically, on a disk. Views use records from existing tables, called “base tables”, and they can also calculate new records as needed. But there is never anything stored in a view.
You can also think of a view as being similar to a saved query; once you create it and name it, you can use and reuse it as needed. Other ways to describe views are “virtual tables” and “a named result set”.
There are many situations where using a view is helpful. For example, you can use a view to summarize information in a table (or multiple tables). Or you can use it to control what information is available to certain employees or departments. Views can create a sort of data redundancy in a database.
In this post, we’ll show you how to create a simple view, how to calculate columns in a view, and how to use one view over top of another.
So if you’ve got 30 minutes, let’s get started with SQL views!
A Simple View
Before we code, look at the following tables,
olympic_games” and “
medals”. We’ll be using them to explain the use of views:
|2||Rio de Janeiro||2016|
|1||Marathon 42K Woman||Jamaica||Gold||Justine Brando||True|
|1||Marathon 42K Woman||Spain||Silver||Juana Lopez||False|
|2||Marathon 42K Woman||France||Gold||Edith Merc||True|
|2||Triathlon Men||England||Gold||John Smith||True|
|2||Triathlon Men||Japan||Silver||Eiko Shamamoto||False|
|2||Triathlon Men||Poland||Bronze||Dimitry Tomasevsky||False|
|3||Judo Men||Poland||Gold||Matthew Solansky||False|
|3||Judo Men||Japan||Bronze||Eiko Shamamoto||False|
Now, let’s make a simple view. This will show you the idea behind views.
We’ll create a view called “
world_wide_records_breaks”. It will contain the following columns:
We can also say that the view syntax is “
world_wide_records_breaks(city, year, discipline, athlete)”. Notice that the columns we will use are enclosed in parentheses.
We can create a view quite easily using the CREATE VIEW statement:
CREATE VIEW world_wide_records_breaks AS SELECT city, year, discipline, athlete FROM olympic_games JOIN medals ON olimpic_games.og_id = medals.og_id WHERE is_ww_record = true;
A view is a simple association between a view name and a SELECT statement. Now, whenever the view name is referenced, the associated SELECT returns the same records. However, it is important to note that the view records are not stored anywhere, they are obtained “on the fly” by executing the SELECT.
Did you notice the JOIN clause? This means we have created the view from two base tables, “
olympic_games” and “
Once we’ve created a view, we can use it as a regular table in any SELECT statement. In the previous example, we used it to see which athletes broke world records. Now, let’s use the view to find out which female 42k marathoners broke records. Let’s also organize this information by year:
SELECT athlete_name, city, year FROM world_wide_records_breaks WHERE discipline = 'Marathon 42K Woman' ORDER BY year
|athlete_name text||city text||year integer|
Views with Calculated Fields
Let’s imagine that we need to create reports about the number of medals each country won. The tables have all the data we need, but their schemas are not ideal for the type of query we are going to execute. So we will create a view of the data we need.
The view name will be “
country_medals” and it will contain the following columns:
This is the schema of the view will be
country_medals(country, gold_medals, silver_medals, bronze_medals, year).
Here is the code:
CREATE VIEW country_medals AS SELECT country, sum(case when medal_type = 'gold' then 1 else 0 end) as gold_medals, sum(case when medal_type = 'silver' then 1 else 0 end) as silver_medals, sum(case when medal_type = 'bronze' then 1 else 0 end) as bronze_medals, year FROM olimpic_games, medals WHERE olimpic_games.og_id = medals.og_id GROUP BY country, year
Notice that we are using a calculation to come up with data for three columns: “
silver_medals” and “
bronze_medals”. When we create views with calculated columns, we must define the names of these columns in the AS sub-clause. This is very important.
Now let’s use our new view in another query. Say we want to get countries that have only one type of medal – i.e. only gold medals, or only silver medals, or only bronze medals. Here is what this query looks like:
SELECT country FROM country_medals GROUP BY country HAVING (sum(gold_medals) > 0 and sum(silver_medals)=0 and sum(bronze_medals)=0) OR (sum(gold_medals) = 0 and sum(silver_medals)>0 and sum(bronze_medals)=0) OR (sum(gold_medals) = 0 and sum(silver_medals)=0 and sum(bronze_medals)>0)
Using Views Over Other Views
Can we create a view over another view? Yes, we can. Let’s see how.
Suppose we need to rank countries based on the number and type of medals they’ve won. We would set up a simple point system: gold medals get 5 points each, silver gets 3 points, and bronze gets 1 point.
If we plan to execute many queries over this ranking, the best idea is to create a view and then query that view as needed.
First, let’s create the view, using the “
country_medals” view as the base table. Yup, we’re using a view based on another view! Here’s how it’s done:
CREATE VIEW ranking AS SELECT country, SUM (gold_medals * 5 + silver_medals * 3 + bronze_medals ) as points FROM country_medals GROUP BY country ORDER BY points DESC
We’ve named the new view “
ranking”. Notice that we’re treating views exactly like tables. Now we can use the “
ranking” view to see our ordered results:
SELECT * FROM ranking
|country text||points numeric|
Want to Learn More about Views?
As you can see, views are a powerful and convenient database object. Often, they are used to keep information as READ ONLY, which is why we’ve focused on using views with SELECT statements. But it certainly is possible to use them in INSERT, UPDATE, and DELETE statements. Our 30 minutes are up, though, so you will have to investigate that on your own. I recommend using Vertabelo Academy’s Creating Tables in SQL course if you’ve already got a smattering of SQL, or the SQL Basics course if you’re a complete beginner.