The SQL Query Language DML Odds and Ends

1
The SQL Query Language DML

• The SQL Query LanguageDML Odds and Ends

2
Outline

• Data creation and destruction
• Inserting into a table
• Deleting from a table
• Modifying values in a table
• Other commonly used features
• Views
• Transactions and triggers
• Summary

3
Modifications

• There are three modification statements
• INSERT
• UPDATE
• DELETE
• For insertions, either values can be specified,
  or a select statement provides the values
• Enter a reservation for Eric for the film 332244
• INSERT INTO Reserved
• VALUES (123456, 332244, CURRENT_DATE)

4
INSERT, cont.

• Let Melanie reserve all the films that Eric has
  reserved.

INSERT INTO Reserved SELECT
C2.CustomerID, FilmID, CURRENT_DATE FROM
Reserved, Customer C, Customer C2 WHERE
C.Name Eric AND C.CustomerID
Reserved.CustomerID AND C2.Name
Melanie
5
DELETE

• A where clause identifies which rows to remove
  from the table
• Delete all the reservations of customer 123456
• DELETE FROM Reserved
• WHERE CustomerID 123456
• Other tables can be consulted to determine which
  rows should be removed
• Delete all of Eric's reservations

DELETE FROM Reserved WHERE CustomerID
ANY(SELECT CustomerID
FROM Customer WHERE
Name Eric)
6
DELETE, cont

• Delete the films with a rental price below the
  average

DELETE FROM Film WHERE RentalPrice lt(SELECT
AVG(RentalPrice) FROM Film)

• Implementation difficulty As we delete rows from
  Film, the average rental balance changes
• Not a problem
• Inner query, first computes the average balance
• Outer loop deletes tuples without recomputing
  AVG

7
UPDATE

• Increase the rental price of all films by 10
• UPDATE Film SET RentalPrice RentalPrice
  1.10
• The update statement has an optional where clause
• Increase the rental price of foreign films by 10

UPDATE Film SET RentalPrice RentalPrice
1.10 WHERE Kind F
8
UPDATE, cont

• Increase the rental price of films with a current
  price of under 3 by 20, and all others by 10

UPDATE Film SET RentalPrice RentalPrice
1.10 WHERE RentalPrice gt 3.00 UPDATE
Film SET RentalPrice RentalPrice
1.20 WHERE RentalPrice lt 3.00

• Careful, order is important!

9
Outline

• Data creation and destruction
• Inserting into a table
• Deleting from a table
• Modifying values in a table
• Other commonly used features
• Views
• Transactions and triggers
• Summary

10
View

• Views provide a mechanism to create a virtual
  table
• CREATE VIEW name AS query expression
• To create a view we use the command
• Define a view of all customers in Dublin

CREATE VIEW Dublin_Customers AS SELECT FROM
Customer WHERE City Dublin
11
View, cont.

• Define a view of all customers holding
  reservations, and the films they have reserved

CREATE VIEW Reservations AS SELECT Name,
Title FROM Customer, Reserved, Film WHERE
Customer.CustomerID Reserved.CustomerID AND
Reserved.FilmID Film.FilmID
12
Transactions

• A transaction identifies an elementary unit of
  work carried out by an application, to which we
  wish to allocate particular characteristics of
  reliability and isolation
• A system that makes available mechanisms for the
  definition and execution of transactions is
  called a transaction processing system
• Transactions are initiated with any SQL statement
  that modifies the database

13
Transactions

• A transaction can be defined syntactically each
  transaction, irrespective of the language in
  which it is written, is enclosed whthin two
  commands
• begin transaction
• end transaction
• Within the transaction code, two particular
  instructions can appear
• commit work
• rollback work

14
Transaction example

• An example of transaction is given in the
  following code
• begin transaction
• X x 10
• Y y 10
• Commit work
• end transaction

15
Active databases

• An active database system is a DBMS that supports
  an integrated subsystem for the definition and
  management of production rules
• The rules follow the event condition action
  paradigm each rule reacts to some events,
  evaluates a condition and, based on the truth
  value of the condition, might carry out an action
• The execution of the rules happens under the
  control of an autonomous subsystem, known as the
  rule engine, which keeps track of the events that
  have occurred and schedules the rules for
  execution
• One of the active rules called triggers

16
Triggers

• The creation of triggers is part of the DDL
• Maintain data integrity
• Associated with a table (view)
• Event-condition-action
• Wait for a table event
• On event, evaluate condition
• If condition is true, execute action

insertion deletion update
before after
X
17
How to create a trigger (SQL Server Enterprise
Manager)

• Expand a server group, and then expand a server.
• Expand Databases, expand the database in which
  the table to contain the trigger belongs, and
  then click Tables.
• In the details pane, right-click the table on
  which the trigger will be created, point to All
  Tasks, and then click Manage Triggers.
• In Name, click ltnewgt.
• In the Text box, enter the text of the trigger.
  Use CTRL-TAB to indent the text of a trigger.
• To check the syntax, click Check Syntax.

18
Syntax (SQL server)

• CREATE TRIGGER trigger_name ON table view
  WITH ENCRYPTION      FOR AFTER
  INSTEAD OF INSERT , UPDATE
           WITH APPEND          NOT FOR
  REPLICATION         AS          IF UPDATE
  ( column )              AND OR UPDATE (
  column )                  ...n         
  IF ( COLUMNS_UPDATED ( ) bitwise_operator
  updated_bitmask )                 
  comparison_operator column_bitmask ...n
                   sql_statement ...n
      

19
Oracle Table-level Trigger Body

• Example At most 100 people can live in Dublin
• After update/insert into Person
• DECLARE Declare Variables
• C INTEGER
• BEGIN Body of Trigger
• SELECT COUNT(Name) INTO C
• FROM Person
• WHERE City Dublin
• IF (C gt 100) THEN
• RAISE_APPLICATION_ERROR(-20000, too many in
  Dublin)
• END IF
• END

20
Potential Applications

• Notification
• an active database may be used to monitor
• Enforce integrity constraints
• Business roles
• Maintenance of derived data
• Maintain the derived attribute whenever
  individual tuples are changed

21
Trigger Gotchas

• Potentially infinite loop
• Trigger A On insertion into Person, insert into
  Population
• Trigger B On insertion into Population, insert
  into Person
• Mutating tables
• Trigger A On insertion into Person, insert into
  Person!
• Disallowed!
• Trigger cannot make changes to table that trigger
  is defined on

22
Summary

• Schema Definition
• CREATE TABLE
• CREATE VIEW
• ALTER
• DROP
• Queries
• SELECT
• Modifications
• INSERT
• DELETE
• UPDATE
• Transaction Management
• COMMIT
• ROLLBACK
• Active Database
• Trigger