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:

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

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 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: "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)

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

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 LearnSQL's SQL Views course if you've already got a smattering of SQL, or the SQL Basics course if you're a complete beginner.