Categories of SQL Statements

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

2. Data Definition Statements ALTER FRAGMENTALTER FUNCTION ALTER INDEXALTER PROCEDURE ALTER ROUTINEALTER TABLE CLOSE DATABASECREATE AGGREGATE CREATE CASTCREATE DATABASE CREATE DISTINCT TYPECREATE EXTERNAL TABLE CREATE INDEXCREATE OPAQUE TYPE CREATE PROCEDURECREATE PROCEDURE FROM CREATE ROLECREATE ROW TYPE CREATE SCHEMACREATE SYNONYM CREATE TABLECREATE TEMPORARY TABLE CREATE TRIGGERCREATE VIEW DATABASEDROP AGGREGATE DROP CASTDROP DATABASE DROP INDEXDROP PROCEDURE DROP ROLEDROP ROW TYPE DROP SYNONYMDROP TABLE DROP TRIGGERDROP VIEW RENAME COLUMNRENAME DATABASE RENAME TABLETRUNCATE

3. Data Manipulation Statements DELETEINSERT LOADSELECT UNLOADUPDATE Cursor Manipulation Statements CLOSEDECLARE FETCHFLUSH FREEOPEN PUTSET AUTOFREE Optimization Statements SET AUTOFREE SET DEFERRED_PREPARE

4. Dynamic Management Statements ALLOCATE COLLECTIONALLOCATE DESCRIPTOR ALLOCATE ROWDEALLOCATE COLLECTION DEALLOCATE DESCRIPTORDEALLOCATE ROW DESCRIBEEXECUTE EXECUTE IMMEDIATEFREE GET DESCRIPTORPREPARE SET DEFERRED_PREPARESET DESCRIPTOR Data Access Statements GRANTGRANT FRAGMENT LOCK TABLEREVOKE REVOKE FRAGMENTSET ISOLATION SET LOCK MODESET ROLE SET SESSION AUTHORIZATIONSET TRANSACTION SET TRANSACTION MODEUNLOCK TABLE

5. Data Integrity Statements BEGIN WORKCOMMIT WORK ROLLBACK WORKSET DATABASE OBJECT MODE SET LOGSET PLOAD FILE SET TRANSACTION MODESTART VIOLATIONS TABLE STOP VIOLATIONS TABLE Optimization Statements SET EXPLAINSET OPTIMIZATION SET PDQPRIORITYSET RESIDENCY SET SCHEDULE LEVELSET STATEMENT CACHE UPDATE STATISTICS

6. Routine Definition Statements ALTER FUNCTIONALTER PROCEDURE ALTER ROUTINECREATE FUNCTION CREATE FUNCTION FROMCREATE PROCEDURE CREATE PROCEDURE FROMCREATE ROUTINE FROM DROP FUNCTIONDROP PROCEDURE DROP ROUTINEEXECUTE FUNCTION EXECUTE PROCEDURESET DEBUG FILE TO

7. Auxiliary Statements INFOOUTPUT GET DIAGNOSTICSSET DATASKIP WHENEVER Client/Server Connection Statements CONNECTDISCONNECT SET CONNECTION Optical Subsystem Statements ALTER OPTICAL CLUSTERCREATE OPTICAL CLUSTER DROP OPTICAL CLUSTERRELEASE RESERVESET MOUNTING TIMEOUT

8. Data Type

9. Data Type

10. CREATE DATABASE

11. CREATE SCHEMA

12. CREATE TABLE

13. CREATE TABLE 1

14. CREATE TABLE 2

15. CREATE TABLE 3

16. CREATE TABLE 4

17. CREATE TABLE 5

18. CREATE INDEX

19. TRIGGER • A mechanism that resides in the database. • It is available to any user who has permission to use it. • Specifies that when aparticular action, an insert, a select, a delete, or an update, occurs on aparticular table, the database server should automatically perform one ormore additional actions. • The additional actions can be INSERT, DELETE,UPDATE, EXECUTE PROCEDURE or EXECUTE FUNCTION statements.

20. CREATE TRIGGER 1

21. CREATE TRIGGER 2

22. CREATE TRIGGER 3

23. CREATE TRIGGER 4

24. CREATE TRIGGER 5

25. CREATE PROCEDURE 1

26. CREATE PROCEDURE 2

27. CREATE PROCEDURE 3

28. Example 1: CREATE TRIGGER upqty UPDATE OF quantity ON items BEFORE (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;

29. Example 3: CREATE TRIGGER up_items UPDATE OF quantity ON items BEFORE(EXECUTE PROCEDURE upd_items_p1()) AFTER(EXECUTE PROCEDURE upd_items_p2());

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 upqty UPDATE OF quantity ON items REFERENCING OLD AS pre_upd NEW AS post_upd FOR 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));

31. Example 5: WHEN condition CREATE TRIGGER up_price UPDATE OF unit_price ON stock REFERENCING OLD AS pre NEW AS post FOR 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))

32. Example 6: passing data to SPL procedure CREATE TRIGGER upd_totpr UPDATE OF quantity ON items REFERENCING OLD AS pre_upd NEW AS post_upd FOR 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

33. Zdroje: Informix Dynamic Server 2000, Product Documentation