Sql updates dml and views ddl l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

SQL: Updates (DML) and Views (DDL) PowerPoint PPT Presentation


  • 85 Views
  • Uploaded on
  • Presentation posted in: General

SQL: Updates (DML) and Views (DDL). SQL DML (Updating the Data) Insert Delete Update. 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

Download Presentation

SQL: Updates (DML) and Views (DDL)

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql updates dml and views ddl l.jpg

SQL: Updates (DML) and Views (DDL)

Murali Mani


Slide2 l.jpg

  • SQL DML (Updating the Data)

    • Insert

    • Delete

    • Update

Murali Mani


Inserting tuples l.jpg

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;

Murali Mani


Delete and update l.jpg

Delete and Update

  • Deleting tuples

    DELETE FROM Student

    WHERE sNumber=‘6’;

  • Updating tuples

    UPDATE Student SET professor=‘ER’

    WHERE sNumer=‘6’

Murali Mani


Slide5 l.jpg

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.jpg

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.

Murali Mani


Views7 l.jpg

Views

  • CREATE VIEW <viewName> as <query>

  • DROP VIEW <viewName>

CREATE VIEW studentProfessor (student, professor) AS

SELECT sName, pName

FROM Student, Professor

WHERE Student.professor = Professor.pNumber;

DROP VIEW studentProfessor

Murali Mani


Views example l.jpg

Views - Example

Student

Professor

CREATE VIEW studentProfessor (student, professor) AS

SELECT sName, pName

FROM Student, Professor

WHERE Student.professor = Professor.pNumber;

SELECT * from studentProfessor

Murali Mani


Updating views l.jpg

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.

Murali Mani


Updating single relation views l.jpg

Updating Single relation Views

For instance, the following updates are valid:

DELETE FROM MyStudent WHERE name=`Dave';

-- 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’);

Murali Mani


Inserting into single relation views l.jpg

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

Murali Mani


Updating single relation views12 l.jpg

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;

Murali Mani


Updating single relation views13 l.jpg

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.

Murali Mani


Multiple relation views delete l.jpg

Multiple relation views: Delete

Consider a multi-relation view such as

CREATE VIEW studentProf(student, professor)

AS SELECT sName, pName

FROM Student, Professor

WHERE professor=pNumber;

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

Murali Mani


Deleting from multi relation views l.jpg

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.

Murali Mani


Deleting from multirelation views l.jpg

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.

Murali Mani


Inserting into multi relation views l.jpg

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 professor=pNumber;

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 views18 l.jpg

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.

Murali Mani


  • Login