SQL Views and Updates cs 3431 SQL DML



























- Slides: 27

SQL Views and Updates cs 3431

SQL DML (Updating the Data) • Insert • Delete • Update cs 3431

Inserting tuples INSERT INTO Student VALUES (6, ‘Emily’, ‘ 324 FL’, NULL); INSERT INTO Student (s. Number, s. Name) VALUES (6, ‘Emily’); INSERT INTO Professor (p. Number) SELECT professor FROM Student; cs 3431

Delete and Update l Deleting tuples DELETE FROM Student WHERE s. Number=‘ 6’; l Updating tuples UPDATE Student SET professor=‘ER’ WHERE s. Number=‘ 6’ cs 3431

Views NOTE: You can present logical subsets or combinations of the data by creating views of tables. A view is a virtual table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary. cs 3431

Views l View is a virtual relation defined by: l l Named stored SQL query Views can be queried like any “base” relation. cs 3431

Views l CREATE VIEW <view. Name> as <query> CREATE VIEW student. Professor (student, professor) AS SELECT s. Name, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Number; DROP VIEW student. Professor cs 3431

Views ? Why ? l View is a virtual relation ? l l Convenience: Queries on base relations might be “complex” Logical Data Independence: “Base tables” may change, but still queries using views need not change. Customization: Provide different views of the same data. Security: Expose only necessary data to users cs 3431

Views - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 CREATE VIEW student. Professor (student, professor) AS SELECT s. Name, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Number; SELECT * from student. Professor cs 3431 p. Number p. Name address 1 MM 235 FL 2 ER 241 FL student professor Dave MM Greg MM Matt ER

Views - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 CREATE VIEW student. Professor (student, professor) AS SELECT s. Name, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Number; SELECT professor, count(*) FROM student. Professor GROUPBY professor; cs 3431 p. Number p. Name address 1 MM 235 FL 2 ER 241 FL student professor Dave MM Greg MM Matt ER

Querying Views CREATE VIEW student. Professor (student, professor) AS SELECT s. Name, p. Name student FROM Student, Professor WHERE Student. professor = Dave Professor. p. Number; SELECT pnumber as professor, count(*) FROM Student, Professor WHERE Student. professor = Professor. p. Number GROUPBY professor; cs 3431 professor MM Greg MM Matt ER SELECT professor, count(*) FROM student. Professor GROUPBY professor;

Updating Views l Consider views defined with only one relation in the FROM clause such as: CREATE VIEW My. Student (num, name) AS SELECT s. Number, s. Name FROM Student; Question: Are these views updatable? Answer: Updating these views are done by updating the underlying Student tables. cs 3431

Updating Single Relation Views DELETE FROM My. Student WHERE name=`Dave'; -- This will delete the corresponding row from the Student table DELETE FROM Student WHERE name=`Dave'; The update is valid ! cs 3431

Updating Single Relation Views INSERT INTO My. Student VALUES (4, `Mary’); -- This will be translated to: INSERT INTO Student (s. Number, s. Name) VALUES (4, `Mary’); cs 3431

Inserting into single relation views CREATE VIEW My. Student 1(name) AS SELECT s. Name FROM Student; INSERT INTO My. Student 1 VALUES (‘Mary’) will be translated to: INSERT INTO Student(s. Name) VALUES (‘Mary’). This will return an error as s. Number must not be null. cs 3431

Updating Single Relation views l If the SELECT clause specifies DISTINCT, then the view is not updatable. For instance, the following view is not updatable. CREATE VIEW My. Student 2(num) AS SELECT DISTINCT s. Number FROM Student; cs 3431

Updating Single Relation Views l WHERE clause may specify subqueries. CREATE VIEW My. Student 3 (num, name) AS SELECT s. Number, s. Name FROM Student WHERE s. Number NOT IN (SELECT s. Number FROM Student); -- this view will always have 0 tuples. -- Insert into this view will still insert into student table, though that tuple does not appear in the view. cs 3431

Multiple relation views: Delete Consider a multi-relation view such as CREATE VIEW student. Prof (studentname, profname) AS SELECT s. Name, p. Name FROM Student, Professor WHERE SName = PName; -- Ambigious what base table to update ! -- Side effects as other tuples may disappear out of the view ! cs 3431

Multiple relation views Consider a multi-relation view such as CREATE VIEW student. Prof (studentname, profname) AS SELECT s. Name, p. Name FROM Student, Professor WHERE SName = PName; Student Professor s. Number s. Name address professor 1 MM 320 FL 1 2 MM 320 FL 1 3 Matt 320 FL 2 cs 3431 p. Number p. Name address 1 MM 235 FL 2 ER 241 FL

Multi-Relation View l Deletes can be done against multi-relation views if there is a table such that the view and the table have the same key. cs 3431

Views - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 CREATE VIEW student. Professor (student, professor) AS SELECT s. Number, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Number; p. Number p. Name address 1 MM 235 FL 2 ER 241 FL student professor 1 MM 2 MM 3 ER 1. p. Number is key in Professor 2. s. Number is key of Student 3. s. Number is key of view cs 3431

Deleting from multi-relation views Try the following update statements: DELETE FROM student. Professor WHERE professor='MM'; -- What will be deleted ? cs 3431

Views - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 CREATE VIEW student. Professor (student, professor) AS SELECT s. Number, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Number; p. Number p. Name address 1 MM 235 FL 2 ER 241 FL student professor 1 MM 2 MM 3 ER DELETE FROM student. Professor WHERE professor='MM'; -- This will actually delete the two rows in the student table. cs 3431

Views - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 CREATE VIEW student. Professor (student, professor) AS SELECT s. Number, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Number; l. Suppose we drop key constraint lon the professor table for this view. cs 3431 p. Number p. Name address 1 MM 235 FL 2 ER 241 FL l. Now delete will fail because there is no table whose key is the key of the view.

Inserting into multi-relation views Consider view definition: CREATE VIEW student. Prof(student, professor) AS SELECT s. Number, p. Name FROM Student, Professor WHERE professor=p. Number; INSERT INTO Studentprof VALUES (4, 'ER'); -- THIS ABOVE INSERT WILL FAIL AS IT TRIES TO INSERT INTO Professor TABLE AS WELL. INSERT INTO Studentprof(student) VALUES (4); -- THIS ABOVE INSERT WILL SUCCEED. cs 3431

Inserting into multi-relation views l Insert will succeed only if l l The insert translates to insert into only one table. The key for the table to be inserted will also be a key for the view. cs 3431

Views Conclusion l. Views are useful – Virtual relations l Querying through views is always possible l Updating through views has limitations l DBA can set up INSTEAD OF triggers cs 3431