categories of sql statements
DESCRIPTION
Categories of SQL Statements. Data definition statements Data manipulation statements Cursor manipulation statements Cursor optimization statements Dynamic management statements Data access statements Data integrity statements Optimization statements Routine Definition statements - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/1.jpg)
1
Categories of SQL Statements • Data definition statements• Data manipulation statements• Cursor manipulation statements• Cursor optimization statements• Dynamic management statements• Data access statements• Data integrity statements• Optimization statements• Routine Definition statements• Auxiliary statements• Client/server connection statements• Optical subsystem statements
![Page 2: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/2.jpg)
2
Data Definition StatementsALTER FRAGMENT ALTER FUNCTIONALTER INDEX ALTER PROCEDUREALTER ROUTINE ALTER TABLECLOSE DATABASE CREATE AGGREGATECREATE CAST CREATE DATABASECREATE DISTINCT TYPE CREATE EXTERNAL TABLECREATE INDEX CREATE OPAQUE TYPECREATE PROCEDURE CREATE PROCEDURE FROMCREATE ROLE CREATE ROW TYPECREATE SCHEMA CREATE SYNONYMCREATE TABLE CREATE TEMPORARY TABLECREATE TRIGGER CREATE VIEWDATABASE DROP AGGREGATEDROP CAST DROP DATABASEDROP INDEX DROP PROCEDUREDROP ROLE DROP ROW TYPEDROP SYNONYM DROP TABLEDROP TRIGGER DROP VIEWRENAME COLUMN RENAME DATABASERENAME TABLE TRUNCATE
![Page 3: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/3.jpg)
3
Data Manipulation Statements
DELETE INSERT
LOAD SELECT
UNLOAD UPDATE
Cursor Manipulation Statements
CLOSE DECLARE
FETCH FLUSH
FREE OPEN
PUT SET AUTOFREE
Optimization Statements
SET AUTOFREE
SET DEFERRED_PREPARE
![Page 4: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/4.jpg)
4
Dynamic Management StatementsALLOCATE COLLECTION ALLOCATE DESCRIPTORALLOCATE ROW DEALLOCATE COLLECTIONDEALLOCATE DESCRIPTOR DEALLOCATE ROWDESCRIBE EXECUTEEXECUTE IMMEDIATE FREEGET DESCRIPTOR PREPARESET DEFERRED_PREPARE SET DESCRIPTOR Data Access StatementsGRANT GRANT FRAGMENTLOCK TABLE REVOKEREVOKE FRAGMENT SET ISOLATIONSET LOCK MODE SET ROLESET SESSION AUTHORIZATION SET TRANSACTIONSET TRANSACTION MODE UNLOCK TABLE
![Page 5: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/5.jpg)
5
Data Integrity Statements
BEGIN WORK COMMIT WORK
ROLLBACK WORK SET DATABASE OBJECT MODE
SET LOG SET PLOAD FILE
SET TRANSACTION MODE START VIOLATIONS TABLE
STOP VIOLATIONS TABLE
Optimization Statements
SET EXPLAIN SET OPTIMIZATION
SET PDQPRIORITY SET RESIDENCY
SET SCHEDULE LEVEL SET STATEMENT CACHE
UPDATE STATISTICS
![Page 6: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/6.jpg)
6
Routine Definition Statements
ALTER FUNCTION ALTER PROCEDURE
ALTER ROUTINE CREATE FUNCTION
CREATE FUNCTION FROM CREATE PROCEDURE
CREATE PROCEDURE FROM CREATE ROUTINE FROM
DROP FUNCTION DROP PROCEDURE
DROP ROUTINE EXECUTE FUNCTION
EXECUTE PROCEDURE SET DEBUG FILE TO
![Page 7: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/7.jpg)
7
Auxiliary Statements
INFO OUTPUT
GET DIAGNOSTICS SET DATASKIP
WHENEVER
Client/Server Connection Statements
CONNECT DISCONNECT
SET CONNECTION
Optical Subsystem Statements
ALTER OPTICAL CLUSTER CREATE OPTICAL CLUSTER
DROP OPTICAL CLUSTER RELEASE
RESERVE SET MOUNTING TIMEOUT
![Page 8: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/8.jpg)
8
Data Type
![Page 9: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/9.jpg)
9
Data Type
![Page 10: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/10.jpg)
10
CREATE DATABASE
![Page 11: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/11.jpg)
11
CREATE SCHEMA
![Page 12: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/12.jpg)
12
CREATE TABLE
![Page 13: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/13.jpg)
13
CREATE TABLE 1
![Page 14: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/14.jpg)
14
CREATE TABLE 2
![Page 15: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/15.jpg)
15
CREATE TABLE 3
![Page 16: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/16.jpg)
16
CREATE TABLE 4
![Page 17: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/17.jpg)
17
CREATE TABLE 5
![Page 18: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/18.jpg)
18
CREATE INDEX
![Page 19: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/19.jpg)
19
TRIGGER
• A mechanism that resides in the database.• It is available to any user who has permission to use it.• Specifies that when a particular action, an insert, a select, a delete, or an update, occurs on a particular table, the database server should automatically perform one or more additional actions.• The additional actions can be INSERT, DELETE, UPDATE, EXECUTE PROCEDURE or EXECUTE FUNCTION statements.
![Page 20: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/20.jpg)
20
CREATE TRIGGER 1
![Page 21: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/21.jpg)
21
CREATE TRIGGER 2
![Page 22: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/22.jpg)
22
CREATE TRIGGER 3
![Page 23: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/23.jpg)
23
CREATE TRIGGER 4
![Page 24: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/24.jpg)
24
CREATE TRIGGER 5
![Page 25: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/25.jpg)
25
CREATE PROCEDURE 1
![Page 26: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/26.jpg)
26
CREATE PROCEDURE 2
![Page 27: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/27.jpg)
27
CREATE PROCEDURE 3
![Page 28: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/28.jpg)
28
Example 1:
CREATE TRIGGER upqtyUPDATE OF quantity ON itemsBEFORE (EXECUTE PROCEDURE upd_items_p1)
Example 2:
CREATE PROCEDURE upd_items_p1()DEFINE GLOBAL old_qty INT DEFAULT 0;LET old_qty = (SELECT SUM(quantity) FROM items);
END PROCEDURE;
CREATE PROCEDURE upd_items_p2()DEFINE GLOBAL old_qty INT DEFAULT 0;DEFINE new_qty INT;LET new_qty = (SELECT SUM(quantity) FROM items;IF new_qty > old_qty * 1.50 THEN
RAISE EXCEPTION –746, 0, ‘Not allowed / rule violation;’END IF
END PROCEDURE;
![Page 29: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/29.jpg)
29
Example 3: CREATE TRIGGER up_itemsUPDATE OF quantity ON itemsBEFORE(EXECUTE PROCEDURE upd_items_p1())AFTER(EXECUTE PROCEDURE upd_items_p2());
![Page 30: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/30.jpg)
30
Example 4: FOR EACH ROW clause, REFERENCING clause
CREATE TABLE log_record(item_num SMALLINT,ord_num INTEGER,username CHAR (8),update_time DATETIME YEAR TO MINUTE,old_qty SMALLINT,new_qty SMALLINT);
CREATE TRIGGER upqtyUPDATE OF quantity ON itemsREFERENCING OLD AS pre_upd NEW AS post_updFOR EACH ROW
(INSERT INTO log_record VALUES (pre_upd.item_num, pre_upd.order_num, USER, CURRENT year to fraction(3), pre_upd.quantity, post_upd.quantity));
![Page 31: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/31.jpg)
31
Example 5: WHEN condition
CREATE TRIGGER up_priceUPDATE OF unit_price ON stockREFERENCING OLD AS pre NEW AS postFOR EACH ROW WHEN (post.unit_price > pre.unit_price * 2)
(INSERT INTO warn_tab VALUES (pre.stock_num, pre.order_num, pre.unit_price, post.unit_price, CURRENT))
![Page 32: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/32.jpg)
32
Example 6: passing data to SPL procedure
CREATE TRIGGER upd_totprUPDATE OF quantity ON itemsREFERENCING OLD AS pre_upd NEW AS post_updFOR EACH ROW(EXECUTE PROCEDURE calc_totpr(pre_upd.quantity,
post_upd.quantity, pre_upd.total_price) INTO total_price)
CREATE PROCEDURE calc_totpr(old_qty SMALLINT, new_qty SMALLINT, total MONEY(8)) RETURNING MONEY(8);DEFINE u_price LIKE items.total_price;DEFINE n_total LIKE items.total_price;LET u_price = total / old_qty;LET n_total = new_qty * u_price;RETURN n_total;
END PROCEDURE
![Page 33: Categories of SQL Statements](https://reader036.vdocuments.us/reader036/viewer/2022062314/56814238550346895dae564e/html5/thumbnails/33.jpg)
33
Zdroje:Informix Dynamic Server 2000, Product Documentation