Views

The views are used in situations where for safety or performance, we want to hide certain data to users

SELECT number_acount, name_branch, balance
FROM acount
WHERE number_acount LIKE 'C-9732'

In the example, a client needs to know the number of a loan operation to its name, the 'C-9732' and the name of the branch that has processed, but for that operation does not need to see the balance or more related data

With the above query we solved what the client needed, but we gave more data than necessary and may put the bank in a situation that could be unsafe

Relationships that are not part of the logical model but are visible to users through virtual relationships are called views

Basic definition of views

CREATE VIEW

Relationships are defined by the command CREATE VIEW:

\text{CREATE VIEW v} AS r

Where r is a query expression and v is the name of the view

CREATE VIEW all_accounts AS
SELECT number_acount, name_branch
FROM acount

The example has created a view that tries to solve the problem of the above example

However, even though we have improved safety to the user not show balances, does not solve the problem, we should still do the following subquery to solve:

SELECT number_acount, name_branch
FROM all_accounts
WHERE number_acount LIKE 'C-9732'

The example has treated the view as if it were a more

However, for the management system database it is a virtual connection being a view

Being a virtual relationship we can do query operations SELECT

But we can not do update operations as INSERT, UPDATE or DELETE

CREATE VIEW loan(name_branch, total) AS
SELECT name_branch, COUNT(balance) total
FROM acount
GROUP BY name_branch, balance

In the example we have created a view called loan(name_branch, total) that allows you to get the names of all branches and the total balance they have

As shown, it is possible to return any attribute and usually work with queries

In this case the attribute has been returned name_branch and it used the aggregation function COUNT after using clause GROUP BY with attributes name_branch and balance

CREATE VIEW accounts_navacerrada AS
SELECT number_acount, name_branch
FROM all_accounts
WHERE name_branch LIKE 'Navacerrada'

In the example it defined a view from another to solve the query that retrieves all account numbers branch 'Navacerrada‘

So we've used the view named all_accounts

Since it is taken as a virtual relationship management system for the database to be a view

Keep in mind that nest views on themselves can produce a recursive effect and if ill defined block database falling into an infinite loop

Updating views

Generally a view can not be updated, however a management database system can allow make in individual cases

You have to consult the manual system manager databases to know what specific cases permits and how to solve it

In the event that updating is permitted, the view must meet:

• The clause FROM only has a relationship
• The clause SELECT only contains names of attributes of the relationship without alias, added value without specifying DISTINCT
• Any attribute that does not appear in clause SELECT should be defined as null
• The query must not contain aggregate functions (GROUP BY clause, HAVING clause)

DROP VIEW

You can use the command DROP VIEW to remove a view

And only from that view, as he is not able to work on several

The most common form of view deletion query will be:

\text{DROP VIEW v}

Where v is the name of the view

DROP VIEW all_accounts

In the example it has deleted the view named all_accounts

WITH clause

The clause WITH was introduced in SQL: 1999 standard and allows you to define a temporary view

This temporary view can only be used in the query which has been defined

The most common way is:

\text{WITH} v \text{AS} r_0\newline\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_0, r_1, r_2, \cdots, r_m, r\\ \text{WHERE} P

Where every A_i represents an attribute, each r_i a relationship (r₀ is a query), P is a predicate and v is the name of the temporary view

WITH all_accounts AS
SELECT number_acount, name_branch
FROM acount

SELECT number_acount, name_branch
FROM all_accounts
WHERE number_acount LIKE 'C-9732'

This example is equivalent to the view that we saw in a previous example

Only this time we have not used a defined view in the database

We have used a defined time view with clause WITH