SQL Updates DML and Views DDL Murali Mani


















- Slides: 18

SQL: Updates (DML) and Views (DDL) Murali Mani

SQL DML (Updating the Data) • Insert • Delete • Update Murali Mani

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; Murali Mani

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

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. Murali Mani

Views l View is a virtual relation l 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. Provide different views of the same data. Security: Expose only necessary data to users Views can be queried like any “base” relation. Murali Mani

Views l l CREATE VIEW <view. Name> as <query> DROP VIEW <view. Name> 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 Murali Mani

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 Murali Mani p. Number p. Name address 1 MM 235 FL 2 ER 241 FL student professor Dave MM Greg MM Matt ER

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; These views are updatable. Updating these views are done by updating the underlying Student tables. Murali Mani

Updating Single relation Views For instance, the following updates are valid: DELETE FROM My. Student WHERE name=`Dave'; -- This will delete the corresponding row from the Student table INSERT INTO My. Student VALUES (4, `Mary’); -- This will be translated to INSERT INTO Student(s. Number, s. Name) VALUES (4, `Mary’); Murali Mani

Inserting into single relation views Consider the view 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 Murali Mani

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; Murali Mani

Updating Single Relation Views l Note that the WHERE clause may specify subqueries. Let us consider an extreme example. 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. Murali Mani

Multiple relation views: Delete Consider a multi-relation view such as CREATE VIEW student. Prof(student, professor) AS SELECT s. Name, p. Name FROM Student, Professor WHERE professor=p. Number; -- Note that the p. Number is key for professor. We see that s. Number is a key for Student and also for the view (though s. Number does not appear in the result). So deleting from the views are possible by deleting appropriate s. Numbers from the Student table. Murali Mani

Deleting from multi-relation views Try the following update statements: DELETE FROM Studentprof WHERE professor='MM'; -- This will actually delete the two rows in the student table. -- Therefore deletes can be done against multirelation views if there is a table such that the view and the table have the same key. Murali Mani

Deleting from multirelation views l Suppose we drop the key constraint on the professor table for the same view. l Now delete will fail because there is no table whose key is the key of the view. Murali Mani

Inserting into multi-relation views Consider the following slightly modified 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. Murali Mani

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. Murali Mani