SQL: Updates (DML) and Views (DDL)

1
SQL Updates (DML) and Views (DDL)
2

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

3
Inserting tuples

• INSERT INTO Student VALUES
• (6, Emily, 324 FL, NULL)
• INSERT INTO Student (sNumber, sName)
• VALUES (6, Emily)
• INSERT INTO Professor (pNumber)
• SELECT professor
• FROM Student

4
Delete and Update

• Deleting tuples
• DELETE FROM Student
• WHERE sNumber6
• Updating tuples
• UPDATE Student SET professorER
• WHERE sNumer6

5
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.
6
Views

• 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.
• Provide different views of the same data.
• Security Expose only necessary data to users
• Views can be queried like any base relation.

7
Views

• CREATE VIEW ltviewNamegt as ltquerygt
• DROP VIEW ltviewNamegt

• CREATE VIEW studentProfessor (student, professor)
  AS
• SELECT sName, pName
• FROM Student, Professor
• WHERE Student.professor Professor.pNumber

DROP VIEW studentProfessor
8
Views - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL 1
2 Greg 320FL 1
3 Matt 320FL 2
pNumber pName address
1 MM 235FL
2 ER 241FL
CREATE VIEW studentProfessor (student, professor)
AS SELECT sName, pName FROM Student,
Professor WHERE Student.professor
Professor.pNumber
student professor
Dave MM
Greg MM
Matt ER
SELECT from studentProfessor
9
Updating Views

• Consider views defined with only one relation in
  the FROM clause such as
• CREATE VIEW MyStudent (num, name) AS SELECT
  sNumber, sName FROM Student
• These views are updatable. Updating these views
  are done by updating the underlying Student
  tables.

10
Updating Single relation Views

• For instance, the following updates are valid
• DELETE FROM MyStudent WHERE nameDave'
• -- This will delete the corresponding row from
  the Student table
• INSERT INTO MyStudent VALUES (4, Mary)
• -- This will be translated to INSERT INTO
  Student(sNumber, sName) VALUES (4, Mary)

11
Inserting into single relation views

• Consider the view
• CREATE VIEW 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
  must not be null

12
Updating Single relation views

• If the SELECT clause specifies DISTINCT, then the
  view is not updatable.
• For instance, the following view is not
  updatable.
• CREATE VIEW MyStudent2(num) AS
• SELECT DISTINCT sNumber FROM Student

13
Updating Single Relation Views

• Note that the WHERE clause may specify
  subqueries. Let us consider an extreme example.
• CREATE VIEW MyStudent3 (num, name) AS
• SELECT sNumber, sName FROM Student
• WHERE sNumber NOT IN (SELECT sNumber 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.

14
Multiple relation views Delete

• Consider a multi-relation view such as
• CREATE VIEW studentProf(student, professor)
• AS SELECT sName, pName
• FROM Student, Professor
• WHERE professorpNumber
• -- Note that the pNumber is key for professor. We
  see that sNumber is a key for Student and also
  for the view (though sNumber does not appear in
  the result). So deleting from the views are
  possible by deleting appropriate sNumbers from
  the Student table.

15
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
  multi-relation views if there is a table such
  that the view and the table have the same key.

16
Deleting from multirelation views

• Suppose we drop the key constraint on the
  professor table for the same view.
• Now delete will fail because there is no table
  whose key is the key of the view.

17
Inserting into multi-relation views

• Consider the following slightly modified view
  definition
• CREATE VIEW studentProf(student, professor)
• AS SELECT sNumber, pName FROM Student, Professor
• WHERE professorpNumber
• 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.

18
Inserting into multi-relation views

• Insert 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.