SQL Database Modifications: Insertion, Deletion, and Updates, Slides of Database Management Systems (DBMS)

Download SQL Database Modifications: Insertion, Deletion, and Updates and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

More SQL

Database Modification

Defining a Database Schema

Views

Database Modifications

• A modification command does not return a result (as a query does), but changes the database in some way.
• Three kinds of modifications: 1. Insert a tuple or tuples. 2. Delete a tuple or tuples. 3. Update the value(s) of an existing tuple or tuples.

Specifying Attributes in INSERT

• We may add to the relation name a list of attributes.
• Two reasons to do so:
  1. We forget the standard order of attributes for the relation.
  2. We don’t have values for all attributes, and we want the system to fill in missing components with NULL or a default value.

Example: Specifying Attributes

• Another way to add the fact that Sally likes Twizzlers to Likes(consumer, candy):

INSERT INTO Likes(candy, consumer)

VALUES(’Twizzler’, ’Sally’);

Example: Insert a Subquery

• Using Frequents(consumer, store), enter into the new relation CoShoppers(name) all of Sally’s “co-shoppers,” i.e., those consumers who frequent at least one store that Sally also frequents.

Solution

INSERT INTO CoShoppers

(SELECT c2.consumer

FROM Frequents c1, Frequents c WHERE c1.consumer = ’Sally’ AND c2.consumer <> ’Sally’ AND c1.store = c2.store

);

8

Pairs of Consumer tuples where the first is for Sally, the second is for someone else, and the stores are the same.

The other consumer (shopper)

Example: Deletion

• Delete from Likes(consumer, candy) the fact that Sally likes Twizzlers: DELETE FROM Likes WHERE consumer = ’Sally’ AND candy = ’Twizzler’;

Example: Delete all Tuples

• Make the relation Likes empty:

DELETE FROM Likes;

• Note no WHERE clause needed.

Semantics of Deletion --- (1)

• Suppose Hershey makes only Twizzlers and Kitkats.
• Suppose we come to the tuple c for Twizzler first.
• The subquery is nonempty, because of the Kitkat tuple, so we delete Twizzler.
• Now, when c is the tuple for Kitkat, do we delete that tuple too?

Semantics of Deletion --- (2)

• Answer: we do delete Kitkat as well.
• The reason is that deletion proceeds in two stages:
• Mark all tuples for which the WHERE condition is satisfied.
• Delete the marked tuples.

Example: Update

• Change consumer Fred’s phone number to 555-1212: UPDATE Consumers SET phone = ’555-1212’ WHERE name = ’Fred’;

Example: Update Several Tuples

• Make $4 the maximum price for candy:

UPDATE Sells SET price = 4. WHERE price > 4.00;

Creating (Declaring) a Relation

• Simplest form is:

CREATE TABLE ( );

• To delete a relation:

DROP TABLE ;

Elements of Table Declarations

• Most basic element: an attribute and its type.
• The most common types are:
  • INT or INTEGER (synonyms).
  • REAL or FLOAT (synonyms).
  • CHAR( n ) = fixed-length string of n characters.
  • VARCHAR( n ) = variable-length string of up to n characters.