1 / 15

Insert & Delete

Insert & Delete. Objectives of the Lecture :. To consider the insertion of tuples into a relation; To consider the deletion of tuples from a relation; To consider how to do insertions and deletions in SQL. For conceptual simplicity, these all involve sets of tuples. Updating a Relation.

magee
Download Presentation

Insert & Delete

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Insert & Delete Objectives of the Lecture : • To consider the insertion of tuples into a relation; • To consider the deletion of tuples from a relation; • To consider how to do insertions and deletions in SQL.

  2. For conceptual simplicity,these all involvesets of tuples. Updating a Relation Having created a relation, we need to be able to : • Insert tuples into it, • Delete tuples from it, • Amend tuples in it. It is also essential that, having changed one or more tuples in a relation, the changed value of the relation still satisfies all the integrity constraints. The insertion and deletion of tuples is considered in this lecture, the amendment of tuples and handling of integrity constraints in the next lecture.

  3. Relational Assignment • In principle, all that is needed for all updating isrelational assignmentwhich is the relational equivalent of value assignment in a 3GL programming language. 3GL :y 3 + x Relation : RelVar  “expression evaluating to set of tuples” From the user’s perspective, it is more convenient to have special assignments that execute insertions, deletions and amendments. RelVar Insert ‘set of tuples’ RelVar Delete ‘set of tuples’ The RHS set of tuples is inserted / deleted from the LHS relational variable.

  4. EMPLOYEE EMPLOYEE ENo ENo EName EName M-S M-S Sal Sal 1 1 1 1 E3 E3 1 1 5 Smith 5 5 5 Smith 5 5 2 2 S 2 2 2 S 2 2 12,500 12,500 2 2 2 2 2 2 E5 2 E5 2 2 6 6 Mitchell 6 Mitchell 6 4 4 4 M 4 M 4 4 21,000 21,000 4 4 E1 3 3 3 E1 3 7 Robson Robson 7 7 7 6 6 6 D D 6 32,500 6 6 32,500 6 6 4 E6 4 4 4 4 E6 4 Blake 8 8 8 8 8 8 Blake 8 8 8 8 8 M M 8 8 8 54,000 8 8 8 54,000 8 E8 E8 Jones Jones W W 68,000 68,000 4 E2 E2 4 4 4 4 4 8 8 8 8 8 Nash Nash 8 M 8 8 8 M 8 8 8 8 8 8 50,000 8 8 50,000 8 E4 E4 Archer Archer S S 40,000 40,000 Example of Insertion Insert Value Variable Resulting value of variable

  5. Insertion in Principle RelVar Insert ‘set of tuples’ This is a set of tuple values. This is the name of anyrelation in the DB. The tuples could be written out literally,be the value of a named relation variable, be the result of evaluating a complex relationalexpression, or any combination of these. In principle, therecan be any numberof tuples in the set,including zero ! The tuples can be in anyorder, because they are a set. There should be no tuples in commonwith “RelVar”, or an error will result.

  6. SQL : Insert There are 2 forms of SQL syntax :- • Insert Into RELATION_NAME Values ( a value for each attribute in one tuple ) ; • Insert Into RELATION_NAME Select ….. From … ….. A tuple literal is writteninside the brackets. The retrieval of either a namedrelation or of any SQL expressionis written here.

  7. Examples of SQL Insert 1. Insert 2 literal tuples, i.e. the earlier graphical example : Insert Into EMPLOYEEValues ( ‘E2’, ‘Nash’, ‘M’, 50,000 ) ; Insert Into EMPLOYEEValues ( ‘E4’, ‘Archer’, ‘S’, 40,000 ) ; 2. Insert the contents of relation EMP into EMPLOYEE : Insert Into EMPLOYEE Select * From EMP ; SQL permits only one tuple literalto be entered at a time.So two insertions have to be made.

  8. Insertion : Attribute Order • Although a tuple is a set of attributes, the attribute values have to be written out in some physical order. • Attribute values are matched with the relvar’s corresponding attributes by attribute name, so that the physical order doesn’t matter.But we haven’t used any attribute names so far ! • This is because the default sequence has been used, which is the sequence in which the attributes appeared in the Create Table statement. If the attribute values are written out in the same physical sequence, then they will be matched by position. • To avoid relying on the default sequence, the attribute names can be written out in any desired sequence as an additional parameter, and then the attribute values written in this same sequence, in order to match value with attribute.

  9. SQL : Insert with Attribute Order The 2 revised forms of SQL syntax are :- • Insert Into RELATION_NAME ( names of all the attributes in sequence ) Values ( a value for each attribute in one tuple ) ; • Insert Into RELATION_NAME ( names of all the attributes in sequence ) Select ….. From … …..

  10. Further Examples of SQL Insert 1. Insert 2 literal tuples, i.e. the earlier graphical example : Insert Into EMPLOYEE ( EName, Sal, ENo, M-S ) Values ( ‘Nash’, 50,000, ‘E2’, ‘M’ ) ; Insert Into EMPLOYEE ( EName, Sal, ENo, M-S ) Values ( ‘Archer’, 40,000, ‘E4’, ‘S’ ) ; 2. Insert the contents of relation EMP into EMPLOYEE : Insert Into EMPLOYEE ( EName, M-S, Sal, ENo ) Select EName, M-S, Sal, ENo From EMP ; The second attribute sequence couldhave been different to the first one. Any attribute sequencecould have been used.

  11. Same SQL : Insertions Involving NULLs Sometimes tuples containing nulls must be inserted. • Use the Insert statement without an attribute name sequence parameter; use the keyword NULL for the relevant attribute(s). Example :-InsertIntoEMPLOYEEValues ( ‘E9’, ‘Collins’, NULL, null ) ; • Use the Insert statement with attribute names; omitnames and values of attributes that are to receive nulls. Example :-InsertIntoEMPLOYEE(EmpNo, EName)Values ( ‘E9’, ‘Collins’ ) ; No speech marks.Upper or lower case.

  12. EMPLOYEE ENo EName M-S Sal 1 1 1 E3 Smith 5 5 5 2 2 2 S 2 2 2 12,500 2 E5 2 Mitchell 6 6 M 4 4 4 21,000 4 E1 3 3 7 7 Robson D 6 6 6 32,500 6 E3 1 1 1 Smith 5 5 5 2 2 S 2 2 2 2 12,500 4 E6 4 4 Blake 8 8 8 M 8 8 8 8 54,000 8 8 2 E5 2 Mitchell 6 6 M 4 4 4 21,000 4 E8 Jones W 68,000 E1 3 3 Robson 7 7 6 6 D 32,500 6 6 4 4 E6 4 Blake 8 8 8 M 8 8 8 54,000 8 8 8 EMPLOYEE E8 Jones W 68,000 ENo EName M-S Sal Example of Deletion Delete Value Variable Resulting value of variable

  13. Deletion in Principle RelVar Delete ‘set of tuples’ This is a set of tuple values. This is the name of anyrelation in the DB. The tuples could be written out literally,be the value of a named relation variable, be the result of evaluating a complex relationalexpression, or any combination of these. In principle, therecan be any numberof tuples in the set,including zero ! If all the RelVar’s tuplesare deleted, the RelVarremains but is empty. Typically an expressionis used that picks outsome particular tuples. All tuples should be in “RelVar”, or an error will result.

  14. SQL : Delete • The SQL syntax is :- Delete From RELATION_NAMEWherecondition ; In the Wherephrase, a condition is written that picks out the required tuples from RELATION_NAME. These tuples are then deleted from RELATION_NAME. • The Where phrase is optional. One can just write Delete From RELATION_NAME ; In this case, all the tuples in RELATION_NAME are deleted from it (but RELATION_NAME still exists, albeit empty).

  15. Examples of SQL Delete 1. Delete 2 tuples, i.e. the earlier graphical example. This will be done by specifying tuples where the salary is less than £30,000 : Delete From EMPLOYEEWhere Sal < 30000 ; 2. Delete all the tuples in EMPLOYEE : Delete From EMPLOYEE ; Relation EMPLOYEE is now empty. 3. Delete a specific tuple, i.e. the one referring to employee ‘E1’ : Delete From EMPLOYEEWhere ENo = ‘E1’ ; Because ‘ENo’ is a candidate key, we can be sure that we have deleted one specific tuple, which will correspond to the right person. The number of tuplesdeleted depends onhow many tuplessatisfy the condition.

More Related