Let’s say that we have tables like the ones above and that we often want to answer questions like the following:

1. What is the capital city of Azerbaijan?
2. Which country is Tashkent the capital of?
3. What are the names of capital cities in Asia?
4. What is the population of Oman’s capital city?

As a SQL query they would be like:

	1.	SELECT name FROM City WHERE country="Azerbaijan" AND is_capital=true
	2.	SELECT country FROM City WHERE is_capital=true AND name="Tashkent" 
	3.	SELECT name, country FROM City WHERE is_capital=true AND continent="Asia"
	4.	SELECT population FROM City WHERE is_capital=true AND country="Oman"

It’s quite easy to notice that these queries deal only with the capital cities, so let’s make them a little bit simpler by adding a view.

Adding a view

To add a new view just press 5 on your keyboard or click the Add new view icon on your toolbox, then just click wherever you want to create a view. Alternatively, you can click Add view in Model Structure.

Adding columns

Using a view, we’re going to create a virtual table containing only capital cities. That’s going to be done by the SQL query below, which takes information about the city only if it’s a capital.

Now, let’s click Update columns to parse the query.

Vertabelo shows us a comparison between the old and new version of the view, allowing us to check whether SQL did exactly what we intended. Fortunately, in our example everything seems to be all right, so we can update the columns without fear.

And that’s our view. Now, to make a query about capital cities, we no longer have to type "WHERE is_capital=true", since Capital, according to its name, contain only capital cities. Keep in mind that queries for views look exactly the like they do for tables.

Additional properties

You can provide your own SQL scripts for views too.

We can also set some additional properties more specific to the chosen database engine; in this example, we’re using PostgreSQL.

If you don’t like the colors, you can change them in Format.

Dependencies

Let’s think about the situation when we don’t need any details about the capitals and all we want to know it’s the capital’s name and the country which it is a capital of. Luckily, we already know what should be done in such a situation – another view must be created. Now, we can use either

SELECT name, country FROM City WHERE is_capital=true

or

SELECT name, country FROM Capital

to add the appropriate columns.

We can make this new view dependent on the currently existing Capital view.

If you click Add, the dependency will be created.

SQL preview

SQL preview is available for views; the sample below shows our Capital view.

And here is our second view – Capital2.