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:
[A-Za-z_][A-Za-z0-9\$_]*
Creates a view based on a SQL statement.
CREATE VIEW view_moviesAS SELECT movies.movieId, movies.title, movies.genres, avg(ratings.rating)FROM ratingsJOIN movies on ratings.movieId=movies.movieIdGROUP BY movies.title, movies.movieId, movies.genres;
You can describe the view as you would a table.
\d view_moviesVIEW defined AS: SELECT movies.movieId, movies.title, movies.genres,avg(ratings.rating) FROM ratings JOIN movies ON ratings.movieId=movies.movieIdGROUP BY movies.title, movies.movieId, movies.genresColumn 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 v_reviews;