DDL - 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 OmniSci view query that you can do in a non-view OmniSci query.
View object names must use the NAME format, described in regex notation as:
Creates a view based on a SQL statement.
CREATE VIEW <view_name> AS <sql_statement>;
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.
\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.
SELECT title, EXPR$3 from view_movies where movieId=260; Star Wars: Episode IV - A New Hope (1977)|4.048937
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
DROP VIEW [IF EXISTS] <view>;
DROP VIEW IF EXISTS v_reviews;