CS157a
Chris Pollett
Nov 20, 2019
CREATE VIEW < view-name > AS < view-definition >;
CREATE VIEW MovieProd AS SELECT title, name FROM Movies, MovieExec WHERE producerC# = cert#;
DROP VIEW < view-name > ;
SELECT title FROM MovieProd WHERE name = 'Spielberg';
SELECT title FROM ( SELECT title, name FROM Movies, MovieExec WHERE producerC# = cert#; ) MovieProd WHERE name = 'Spielberg';
CREATE VIEW MovieProd(movieTitle, prodName) AS SELECT title, name FROM Movies, MovieExec WHERE producerC# = cert#;
CREATE VIEW ParamountMovies AS SELECT studioName, title, year FROM Movies WHERE studioName = 'Paramount';
INSERT INTO ParamountMovies VALUES ('Paramount', 'Star Trek', 1979);it would be mapped to the insert on Movies:
INSERT INTO Movies(studioName, title, year) VALUES ('Paramount', 'Star Trek', 1979);
DELETE FROM ParamountMovies WHERE title LIKE '%Trek%';would map to:
DELETE FROM Movies WHERE title LIKE '%Trek%' AND studioName = 'Paramount';
UPDATE ParamountMovies SET year = 1979 WHERE title = 'Star Trek the Movie';would also involve such an ANDing on the underlying table update:
UPDATE Movies SET year = 1979 WHERE title = 'Star Trek the Movie' AND studioName = 'Paramount';
CREATE VIEW ParamountMovies AS SELECT title, year FROM Movies WHERE studioName = 'Paramount';
CREATE TRIGGER ParamountInsert INSTEAD OF INSERT ON ParamountMovies REFERENCING NEW ROW AS NewRow FOR EACH ROW INSERT INTO Movies(title, year, studioName) VALUES(NewRow.title, NewRow.year, 'Paramount');
SELECT * FROM Movies WHERE studioName = 'Disney' and year=1990;might require a full tables scan to return its results (O(n) where `n` is the table size)
CREATE INDEX < name-of-index> ON < table_name>(<indexed-attributes>);
CREATE UNIQUE INDEX < name-of-index> ON < table_name>(<indexed-attributes>);
CREATE INDEX KeyIndex ON Movies(title, year);
CREATE INDEX KeyIndex1 USING HASH ON Movies(title, year); CREATE INDEX KeyIndex2 USING BTREE ON Movies(title, year);
DROP INDEX KeyIndex1 ON Movies;
CREATE MATERIALIZED VIEW MovieProd AS SELECT title, year, name FROM Movies, MovieExec WHERE producerC# = cert#to indicate a view ought to be materialized