Transcript The SQL Query Language DML The SQL Query Language DML

The SQL Query Language DML
The SQL Query Language
DML Odds and Ends
The SQL Query Language DML
1
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
The SQL Query Language DML
2
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)
The SQL Query Language DML
3
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’
The SQL Query Language DML
4
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’)
The SQL Query Language DML
5
DELETE, cont
 Delete the films with a rental price below the average
DELETE FROM Film WHERE RentalPrice <(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
The SQL Query Language DML
6
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’
The SQL Query Language DML
7
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 >= 3.00
UPDATE Film
SET
RentalPrice = RentalPrice * 1.20
WHERE RentalPrice < 3.00
 Careful, order is important!
The SQL Query Language DML
8
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
The SQL Query Language DML
9
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’
The SQL Query Language DML
10
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
The SQL Query Language DML
11
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
The SQL Query Language DML
12
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
The SQL Query Language DML
13
Transaction example
 An example of transaction is given in the following code
begin transaction
X := x – 10;
Y := y + 10;
Commit work;
end transaction
The SQL Query Language DML
14
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
The SQL Query Language DML
15
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
before
after
X
insertion
deletion
update
 On event, evaluate condition
 If condition is true, execute action
The SQL Query Language DML
16
How to create a trigger (SQL Server Enterprise Manager)
1.
Expand a server group, and then expand a server.
2.
Expand Databases, expand the database in which the table to
contain the trigger belongs, and then click Tables.
3.
In the details pane, right-click the table on which the trigger will
be created, point to All Tasks, and then click Manage Triggers.
4.
In Name, click <new>.
5.
In the Text box, enter the text of the trigger. Use CTRL-TAB to
indent the text of a trigger.
6.
To check the syntax, click Check Syntax.
The SQL Query Language DML
17
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 ]
}
}
The SQL Query Language DML
18
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 > 100) THEN
RAISE_APPLICATION_ERROR(-20000, ‘too many in Dublin’);
END IF;
END
The SQL Query Language DML
19
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
The SQL Query Language DML
20
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
The SQL Query Language DML
21
Summary
 Schema Definition




CREATE TABLE
CREATE VIEW
ALTER
DROP
 Queries
 SELECT
 Modifications
 INSERT
 DELETE
 UPDATE
 Transaction Management
 COMMIT
 ROLLBACK
 Active Database
 Trigger
The SQL Query Language DML
22