Back to articles list October 18, 2017 - 6 minutes read Learn SQL Views in 30 Minutes Ignacio L. Bisso 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. Tags: creating tables DELETE INSERT standard sql functions tables in sql UPDATE 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_idcityyear 1Barcelona 1992 2Rio de Janeiro2016 3London 2012 Table: medals og_iddisciplinecountrymedal_typeathlete_nameis_ww_record 1Marathon 42K WomanJamaicaGold Justine Brando True 1Marathon 42K WomanSpain SilverJuana Lopez False 2Marathon 42K WomanFrance Gold Edith Merc True 2Triathlon Men EnglandGold John Smith True 2Triathlon Men Japan SilverEiko Shamamoto False 2Triathlon Men Poland BronzeDimitry TomasevskyFalse 3Judo Men Poland Gold Matthew Solansky False 3Judo Men Japan BronzeEiko 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 textcity textyear integer 1Justine BrandonBarcelona1992 2Edith MercLondon2012 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) country text Jamaica Spain France England 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 textpoints numeric Poland 6 Jamaica5 France 5 England5 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 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. Tags: creating tables DELETE INSERT standard sql functions tables in sql UPDATE You may also like You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more The 5 Highest Paying Jobs That Use SQL Every year, many people look and aim for new & better jobs. SQL-related jobs are some of the most promising. Discover the top 5 highest SQL job salaries! Read more How to Track Down Duplicate Values in a Table Duplicates are one of the most common challenges to data quality. Let's see how it is possible to find & distinguish duplicate names with the help of SQL. Read more Common SQL Window Functions: Using Partitions With Ranking Functions Once you’ve learned such window functions as RANK or NTILE, it’s time to master using SQL partitions with ranking functions. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.