***

## description: DDL - Views

# Views

A view is a virtual table based on the result set of a SQL statement. It derives its fields from a `SELECT` statement. You can do anything with a HEAVY.AI view query that you can do in a non-view HEAVY.AI query.

## Nomenclature Constraints

View object names must use the NAME format, described in [regex](https://en.wikipedia.org/wiki/Regular_expression) notation as:

```
[A-Za-z_][A-Za-z0-9\$_]*
```

## CREATE VIEW

Creates a view based on a SQL statement.

### Example

```sql
CREATE VIEW view_movies
AS SELECT movies.movieId, movies.title, movies.genres, avg(ratings.rating)
FROM ratings
JOIN movies on ratings.movieId=movies.movieId
GROUP BY movies.title, movies.movieId, movies.genres;
```

You can describe the view as you would a table.

```sql
\d view_movies
VIEW defined AS: SELECT  movies.movieId, movies.title, movies.genres,
avg(ratings.rating) FROM ratings JOIN movies ON ratings.movieId=movies.movieId
GROUP BY movies.title, movies.movieId, movies.genres
Column types:
    movieId INTEGER,
    title TEXT ENCODING DICT(32),
    genres TEXT ENCODING DICT(32),
    EXPR$3 DOUBLE
```

You can query the view as you would a table.

```sql
SELECT title, EXPR$3 from view_movies where movieId=260;
Star Wars: Episode IV - A New Hope (1977)|4.048937
```

## DROP VIEW

Removes a view created by the CREATE VIEW statement. The view definition is removed from the database schema, but no actual data in the underlying base tables is modified.

### Example

```sql
DROP VIEW IF EXISTS v_reviews;
```