the sql query language dml1 the sql query language dml odds and ends
Post on 20-Dec-2015
246 views
TRANSCRIPT
The SQL Query Language DML 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
The SQL Query Language DML 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)
The SQL Query Language DML 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’
The SQL Query Language DML 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 ReservedWHERE CustomerID = ANY(SELECT CustomerID
FROM Customer WHERE Name = ’Eric’)
The SQL Query Language DML 6
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 balanceOuter “loop” deletes tuples without recomputing AVG
The SQL Query Language DML 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 FilmSET RentalPrice = RentalPrice * 1.10WHERE Kind = ’F’
The SQL Query Language DML 8
UPDATE, cont
Increase the rental price of films with a current price of under $3 by 20%, and all others by 10%
UPDATE FilmSET RentalPrice = RentalPrice * 1.10WHERE RentalPrice >= 3.00
UPDATE FilmSET RentalPrice = RentalPrice * 1.20WHERE RentalPrice < 3.00
Careful, order is important!
The SQL Query Language DML 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
The SQL Query Language DML 10
View
Views provide a mechanism to create a virtual tableCREATE 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 ASSELECT *
FROM CustomerWHERE City = ’Dublin’
The SQL Query Language DML 11
View, cont.
Define a view of all customers holding reservations, and the films they have reserved
CREATE VIEW Reservations ASSELECT Name, TitleFROM Customer, Reserved, FilmWHERE Customer.CustomerID = Reserved.CustomerID
AND Reserved.FilmID = Film.FilmID
The SQL Query Language DML 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
The SQL Query Language DML 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
The SQL Query Language DML 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
The SQL Query Language DML 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
The SQL Query Language DML 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
Xbefore after
insertion deletion update
The SQL Query Language DML 17
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 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 ] } }
The SQL Query Language DML 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 > 100) THEN
RAISE_APPLICATION_ERROR(-20000, ‘too many in Dublin’);
END IF;
END
The SQL Query Language DML 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
The SQL Query Language DML 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