Advanced SQL

Advanced SQL

1. Views

   • Virtual creation of tables. Base tables used in statements. CREATE VIEW redpart AS         SELECT * FROM p         WHERE colour = 'red' SELECT * FROM redpart
   • View Mat'
     • CREATE REFRESH MATERIALIZED VIEW Old contents? Discarded! Completely replaces the contents of the mat' view.
   • View Res'
     • Combined with SELECT definition at runtime + execution. Original values are used to replace aliases.
   • Update V'
     • DBMS must trace row/col back to original. No DISTINCT, JOIN etc allowed.
   • Delete V
     • DROP VIEW summary;
   • Adv's
     • Data Independence. Currency. Improved Security. Reduced Complexity. Customization.
   • Disadv's
     • Update Restrictions. Structure Restrictions. Performance overheads.
   • Uses
     • External Schema. Selective Security. Alternative Presentations. Simplify User Queries.

2. Access Control

   • Features
     • Grant users access to DB GRANT SELECT ON p TO PUBLIC. Privledges --> S/I/U/D Users --> PUBLIC/GROUPS/ID's Used on --> Tables/Views. REVOKE --> remove privledges. (Replace GRANT with REVOKE + TO with FROM)

3. System Catalog

   • Info
     • Details about tables/columns/indexes. All DBMS do this! No standard design/implementation. Contains --> Check constraints, columns, RI etc. Information_Schema --> name of system catalog. Referential_constraints --> RI constraints.

4. SQL functions

   • Info
     • Artbitary list of SQL statements - returns the last query in the list, if < 0 --> NULL.
   • Example
     • CREATE OR REPLACE FUNCTION clean_emp() RETURNS void AS 'Delete FROM emp WHERE hourly rate < 0;' Language SQL;

5. Triggers

   • Info
     • Not called explicits; DBMS executes without awareness. Called automatically; after an action (S/I/U/D)
   • Uses
     • Validity input data. Maintain complex integrity constraints. Support alerts. Maintain Audit - record changes. Support replication indistributed environments.
   • Example
     • CREATE TRIGGER update_sup AFTER UPDATE ON s FOR EACH row EXECUTE PROCEDURE maintain_changes();
   • Ordering
     • Before row level trigger. Statement itself Referential Constraints. Execution of any other row-level triggers.