1、CS542SQL Views1CS542SQL DML (Updating the Data) Insert Delete UpdateCS542Inserting tuplesINSERT INTO Student VALUES (6, Emily, 324 FL, NULL);INSERT INTO Student (sNumber, sName) VALUES (6, Emily);INSERT INTO Professor (pNumber)SELECT professorFROM Student;3CS542Delete and UpdateDeleting tuplesDELETE

2、 FROM StudentWHERE sNumber=6;Updating tuplesUPDATE Student SET professor=MickyMouseWHERE sNumber=64CS542ViewsNOTE: You can present logical subsets or combinations of the data by creatingviews of tables. A view is a virtual table based on a table or another view. A viewcontains no data of its own but

3、 is like a window through which data from tables canbe 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.5CS542ViewsView is a virtual relation defined by:Named stored SQL queryViews can be queried like any “b

4、ase” relation.6CS542ViewsCREATE VIEW as CREATE VIEW studentProfessor (student, professor) ASSELECT sName, pNameFROM Student, ProfessorWHERE Sfessor = Professor.pNumber; DROP VIEW studentProfessor7CS542Views - ExamplesNumbersNameaddressprofessor1Dave320FL12Greg320FL13Matt320FL2StudentpNumbe

5、rpNameaddress1MM235FL2ER241FLProfessorCREATE VIEW studentProfessor (student, professor) ASSELECT sName, pNameFROM Student, ProfessorWHERE Sfessor = Professor.pNumber;SELECT * from studentProfessorstudentprofessorDaveMMGregMMMattER8CS542Views - ExamplesNumbersNameaddressprofessor1Dave320FL1

6、2Greg320FL13Matt320FL2StudentpNumberpNameaddress1MM235FL2ER241FLProfessorCREATE VIEW studentProfessor (student, professor) ASSELECT sName, pNameFROM Student, ProfessorWHERE Sfessor = Professor.pNumber;SELECT professor, count(*) FROM studentProfessorGROUPBY professor;studentprofessorDaveMMG

7、regMMMattER9CS542Querying ViewsCREATE VIEW studentProfessor (student, professor) ASSELECT sName, pNameFROM Student, ProfessorWHERE Sfessor = Professor.pNumber;SELECT pnumber as professor, count(*) FROM Student, ProfessorWHERE Sfessor = Professor.pNumberGROUPBY professor;studentpr

8、ofessorDaveMMGregMMMattERSELECT professor, count(*) FROM studentProfessorGROUPBY professor;10CS542Views ? Why ?View is a virtual relation ?Convenience: Queries on base relations might be “complex”Logical Data Independence: “Base tables” may change, but still queries using views need not change.Custo

9、mization: Provide different views of the same data.Security: Expose only necessary data to users11CS542Updating ViewsConsider views defined with only one relation in the FROM clause such as: CREATE VIEW MyStudent (num, name) AS SELECT sNumber, sName FROM Student;Question: Are these views updatable?

10、How ?Answer: Updating these views are done by updating the underlying Student tables.12CS542Updating Single Relation ViewsDELETE FROM MyStudent WHERE name=Dave;- This will delete the corresponding row from the Student tableDELETE FROM Student WHERE name=Dave;This update is valid ! 13CS542Updating Si

11、ngle Relation ViewsINSERT INTO MyStudent VALUES (4, Mary);- This will be translated to: INSERT INTO Student (sNumber, sName) VALUES (4, Mary); - What happens to other values of Student tuple ?- What if there is a tuple with Snumber of 4 already ? 14CS542Inserting into single relation viewsCREATE VIE

12、W MyStudent1(name)AS SELECT sName FROM Student;INSERT INTO MyStudent1 VALUES (Mary) will be translated to: INSERT INTO Student(sName) VALUES (Mary). This will return an error as sNumber is primary key, i.e., it must not be null.15CS542Updating Single Relation viewsWhat about the views with DISTINCT

13、? Are they updatable?CREATE VIEW MyStudent2(name) ASSELECT DISTINCT sNameFROM Student;CREATE VIEW MyStudent3(num) ASSELECT DISTINCT sNumber FROM Student;If the SELECT clause specifies DISTINCT, then the view is not updatable.16CS542Updating Single Relation ViewsWHERE clause may specify subqueries. C

14、REATE VIEW MyStudent4 (num, name) ASSELECT sNumber, sName FROM StudentWHERE sName IN (SELECT pName FROM Professor);- Insert into this view will insert into Student table17CS542Updating Single Relation ViewsWHERE clause may specify subqueries. CREATE VIEW MyStudent4 (num, name) ASSELECT sNumber, sNam

15、e FROM StudentWHERE sNumber NOT IN (SELECT sNumber FROM Student);- this view will always have 0 tuples.- Insert into this view will still insert into Student table, even though that tuple does not appear in the view.18CS542Multiple relation views: DeleteConsider a multi-relation view such asCREATE V

16、IEW studentProf (studentname, profname)AS SELECT sName, pNameFROM Student, ProfessorWHERE SName = PName;sNumbersNameaddressprofessor1MM320FL12MM320FL13Matt320FL2StudentpNumberpNameaddress1MM235FL2ER241FLProfessor19CS542Multiple relation views: DeleteUpdating this multi-relation view?CREATE VIEW stud

17、entProf (studentname, profname)AS SELECT sName, pNameFROM Student, ProfessorWHERE SName = PName;- Ambigious what base table to update ! - Side effects as other tuples may disappear out of the view !20CS542Multi-Relation ViewDeletes can be done against multi-relation views if there is a table such th

18、at the view and the table have the same key.21CS542Views - ExamplesNumbersNameaddressprofessor1Dave320FL12Greg320FL13Matt320FL2StudentpNumberpNameaddress1MM235FL2ER241FLProfessorCREATE VIEW studentProfessor (student, profname) ASSELECT sNumber, pNameFROM Student, ProfessorWHERE Sfessor = P

19、rofessor.pNumber;studentprofname1MM2MM3ERpNumber is key in ProfessorsNumber is key of StudentsNumber is key of view22What is key in studentProfessor table?CS542Multi-Relation ViewReminder : Deletes can be done against multi-relation views if there is a table such that the view and the table have the

20、 same key.23CS542Deleting from multi-relation viewsTry the following update statements:DELETE FROM studentProfessor WHERE profname =MM;- What will be deleted ?24CS542Views - ExamplesNumbersNameaddressprofessor1Dave320FL12Greg320FL13Matt320FL2StudentpNumberpNameaddress1MM235FL2ER241FLProfessorCREATE

21、VIEW studentProfessor (student, professor) ASSELECT sNumber, pNameFROM Student, ProfessorWHERE Sfessor = Professor.pNumber;studentprofname1MM2MM3ERDELETE FROM studentProfessor WHERE profname=MM;- This will actually delete the two rows in the student table.25CS542Views - ExamplesNumbersName

22、addressprofessor1Dave320FL12Greg320FL13Matt320FL2StudentpNumberpNameaddress1MM235FL1ER241FLProfessorCREATE VIEW studentProfessor (student, professor) ASSELECT sNumber, pNameFROM Student, ProfessorWHERE Sfessor = Professor.pNumber;Suppose we drop key constraint on professor table for this v

23、iew.Now delete will fail because there is no table whose key is the key of the view.26CS542Inserting into multi-relation viewsConsider view definition:CREATE VIEW studentProf(student, professor) AS SELECT sNumber, pName FROM Student, ProfessorWHERE professor=pNumber;INSERT INTO Studentprof(student)

24、VALUES (4);- the above insert will succeed; put into Student tableINSERT INTO Studentprof VALUES (4, ER);- THIS ABOVE INSERT WILL FAIL AS IT TRIES TO INSERT INTO Professor TABLE AS WELL.27CS542Inserting into multi-relation viewsInsert will succeed only if 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.28CS542Controlling