Learn SQL Views in 30 Minutes

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 SELECT, WHERE and 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:

Table: olympic_games
og_id city year
1 Barcelona 1992
2 Rio de Janeiro 2016
3 London 2012
Table: medals
og_id discipline country medal_type athlete_name is_ww_record
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:

  • city
  • year
  • discipline
  • athlete

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 “medals”.

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
1 Justine Brandon Barcelona 1992
2 Edith Merc London 2012

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:

  • country
  • gold_medals
  • silver_medals
  • bronze_medals
  • year

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
			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, 
		       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: “gold_medals”, “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)
country text

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:

	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
Poland 6
Jamaica 5
France 5
England 5
Japan 4
Spain 3

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.

Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one.