CIT 111 | Project

SQL Views

A SQL view is a SELECT statement that is stored as a database object.
A SQL view acts as a virtual table, but contains no data.
You can use a view anywhere you would use a table including in a SELECT, INSERT, UPDATE, or DELETE statement.

Design Flexibility: By using a view instead of a query in an application, it is easier to make changes to the underlying table structure.
Improved Security: By using a view to return data from tables instead of a SELECT, you can hide the WHERE clause or other columns to which you do not want the user to have access.
Query Simplification: You can write simple select statements against views, which handle complex queries and joins.

The following code creates a view city_country

CREATE VIEW city_country AS
SELECT ci.name AS city_name, co.name AS country_name
FROM city ci
    JOIN country co
    ON ci.CountryCode = co.Code;

```
CREATE VIEW city_country AS
```
- Create a new VIEW object and give it the name city_country.
- The AS statement precedes the query that will be assigned to the VIEW
```
SELECT ci.name AS city_name, co.name AS country_name
```
- Only the columns defined in the SELECT statement will be available to the VIEW
- It is a good idea to provide column alias in the select because the VIEW will not have access to the underlying table structure.

FROM city ci
    JOIN country co
    ON ci.CountryCode = co.Code;

Once you have created a VIEW, you can run SQL statements using the VIEW as if it were a table. Screen capture of view used in select statement.

By creating a VIEW, we can run selects that retrieve data from multiple tables without having to re-code a join.
Notice how the SELECT * retrieves only the rows defined in the SELECT statement used in the VIEW creation.
If you want to drop a VIEW, we can run the DROP VIEW statement
If you want to modify an existing view you can use the statement CREATE OR REPLACE VIEW. That way you do not have to run a DROP VIEW statement and then a CREATE VIEW statement.

If you use any of the following statements in your view, you will NOT be able to use the view in an UPDATE statement. The view will be read-only

SELECT list cannot include a DISTINCT clause.
SELECT list cannot contain aggregate functions (SUM, COUNT, MIN, MAX, AVG, COUNT(*))
SELECT statement cannot use GROUP BY or HAVING.
The VIEW cannot include a UNION operator.