Data Manipulation Language (DML), Slides of Database Management Systems (DBMS)

Download Data Manipulation Language (DML) and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Data Manipulation Language

(DML)

Lecture 13

Sub-sets of SQL

 Data retrieval: SELECT

• Data Manipulation Language (DML): INSERT,

UPDATE, DELETE

 Data Definition Language (DDL): CREATE,

ALTER, DROP, RENAME

• Transaction control: COMMIT, ROLLBACK

 Data Control Language (DCL): GRANT,

REVOKE

Insertion

• To insert a single tuple: INSERT INTO VALUES ( ); Example
• Consider MovieExec (name, address, cert#, netWorth)

INSERT INTO MovieExec

VALUES('Melanie Griffith', '34 Boston Blvd', 700, 300000);

Specifying Attributes in INSERT

• We may add to the relation name a list of attributes.

INSERT INTO MovieExec(name, address, cert, netWorth)

VALUES('Melanie Griffith', NULL, 700, 3000000);

• There are 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.

Deletion

• To delete tuples satisfying a condition from some relation: DELETE FROM WHERE ; Example
• Delete from the Movie table the Disney’s movies: DELETE FROM Movie WHERE studioName ='Disney';

Example: Delete all Tuples

• Make the relation Movie empty:

DELETE FROM Movie;

• No WHERE clause needed here.

Another Example

• Suppose that Brown’s movies have approximately 20 min of info before starting.
• So, let’s take that 20 min off.

UPDATE Movie

SET length = length - 20

WHERE (title, year) IN

( SELECT title, year

FROM Movie, Movieexec

WHERE Movie.producerc = Movieexec.cert

AND name = 'Brown');