Mastering Data Manipulation in SQL: Insert, Update, Delete, and Views

1. Lecture 4 Intro to SQL DML

2. Outline • DML Data Manipulating Language and Transactions • Views (Virtual Tables) in SQL • Triggers in SQL Lecture Resources • Jeffrey A. Hoffer, Modern Database Management- Ch6 + Ch7 • Ramez El masri, Fundamentals Of Database Systems- Ch4+Ch5

3. Data manipulation language (DML) • Is a family of SQL statements used for inserting, deleting and updating data in a database. • Performing read-only queries of data is sometimes also considered a component of DML. • Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are: • INSERT INTO ... VALUES ... • UPDATE ... SET ... WHERE ... • DELETE … FROM ... WHERE …

4. Insert Statement INSERT INTO TableVALUES (value-list) • Adds one or more rows to a table • Inserting into a table • VALUE – “s” Sequence!!! • Inserting a record that has some null attributes requires identifying the fields that actually get data (field-list) VALUES (value list)

5. Insert Statement (cont) • Inserting from another table • A SUBSET from another table 5

6. Insert Statement (cont) • Specify the relation name and a list of values for the tuple

7. Creating Tables with Identity Columns Introduced with SQL:200n • Inserting into a table does not require explicit customer ID entry or field list (No “001” as compared w slide #24) -- • INSERT INTO CUSTOMER_T VALUES ( ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);

8. Creating Tables with Identity Columns Example

9. Update Statement • Modifies data in existing rows • Note: the WHERE clause may be a subquery • UPDATE Table-name • SET Attribute = Value • WHERE Criteria-to-apply-the-update 9

10. Update Statement (cont) • Modify attribute values of one or more selected tuples • Additional SET clause in the UPDATE command • Specifies attributes to be modified and new values 10

11. Update Statement (cont) 11

12. Addendum: Comparison of ALTER, INSERT, and UPDATE • ALTER: changing the columns of the table • ALTER TABLE CUSTOMER_T ADD COLUMN… • INSERT: adding records based on the existing table • INSERT INTO CUTTOME_T VALUES (… ) • UPDATE: changing the values of some fields in existing records • UPDATE PRODUCT_T SET …WHERE…

13. Delete Statement • Removes rows from a table • Delete certain rows • DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE = ‘HI’; • Delete all rows • DELETE FROM CUSTOMER_T; • Careful!!! 13

14. Delete Statement(cont) • Includes a WHERE clause to select the tuples to be deleted

15. Merge Statement • Makes updating a table easier • allows combination of Insert and Update in one statement • Useful for updating master tables with new data • Many database applications need to update master tables with new data. • Example: • A Purchases_T table, might include rows with data about new products and rows that change the standard price of existing products. • Updating Product_T can be accomplished by using INSERT to add the new products and UPDATE to modify Standard Price. • DBMSs can accomplish the update and the insert in one step by using MERGE

16. Merge Statement - Example

17. Merge Statement - Example

18. Merge Statement - Example

19. Transactions • Transaction = A discrete unit of work that must be completely processed or not processed at all • May involve multiple updates • If any update fails, then all other updates must be cancelled • SQL commands for transactions • BEGIN TRANSACTION / END TRANSACTION • Marks boundaries of a transaction • COMMIT • Makes all updates permanent • ROLLBACK • Cancels updates since the last COMMIT

20. An SQL Transaction sequence (in pseudocode)

21. Views (Virtual Tables) in SQL • Concept of a view in SQL • Single table derived from other tables • Considered to be a virtual table • Syntax of CREATE VIEW: • CREATE VIEW view-name AS SELECT (provides the rows &columns of view)

22. Advantages of Views • Simplify query commands • Assist with data security (but don't rely on views for security, there are more important security measures) • Enhance programming productivity • Contain most current base table data • Use little storage space • Provide customized view for user • Establish physical data independence Disadvantages of Views • Use processing time each time view is referenced • May or may not be directly updateable

23. Specification of Views in SQL • CREATE VIEWcommand • Give table name, list of attribute names, and a query to specify the contents of the view

24. Specification of Views in SQL (cont’d.) • Specify SQL queries on a view For example, to retrieve the last name and first name of all employees who work on the ‘ProductX’ project • View always up-to-date • Responsibility of the DBMS and not the user • DROP VIEW command • Dispose of a view DROP VIEW WORKS_ON1;

25. View Implementation • View materialization approach • Physically create a temporary view table when the view is first queried • Keep that table on the assumption that other queries on the view will follow • Requires efficient strategy for automatically updating the view table when the base tables are updated • Incremental update strategies • DBMS determines what new tuples must be inserted, deleted, or modified in a materialized view table when a database update is applied to one of the defining base tables.

26. View Update and Inline Views • Update on a view defined on a single table without any aggregate functions • Can be mapped to an update on underlying base table • View involving joins • Often not possible for DBMS to determine which of the updates is intended • Clause WITH CHECK OPTION • Must be added at the end of the view definition if a view is to be updated to allows the system to check for view updatability and plan an execution strategy for updates. • In-line view • Defined in the FROM clause of an SQL query

27. Routines and Triggers • Routines • Program modules that execute on demand • Functions–routines:return values and take input parameters • Procedures–routines :do not return values and can take parameters • Triggers –routines that execute in response to a database event (INSERT, UPDATE, or DELETE) 27

28. Figure: Triggers contrasted with stored procedures Procedures are called explicitly Triggers are event-driven Source: adapted from Mullins, 1995. 28

29. Simplified trigger syntax, SQL:200n Syntax for creating a routine, SQL:200n 29

30. 30

31. CREATE TRIGGER • CREATE TRIGGER statement • Used to monitor the database • Typical trigger has three components: • Event(s) • Condition • Action • CREATE TRIGGER • Specify automatic actions that database system will perform when certain events and conditions occur

32. Create Trigger Examples

33. Create Trigger Examples

34. Create procedures Example

35. Query Efficiency Considerations • Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set • Limit the number of subqueries; try to make everything done in a single query if possible • If data is to be used many times, make a separate query and store it as a view

36. Guidelines for Better Query Design • Understand how indexes are used in query processing • Keep optimizer statistics up-to-date • Use compatible data types for fields and literals • Write simple queries • Break complex queries into multiple simple parts • Don’t nest one query inside another query • Don’t combine a query with itself (if possible avoid self-joins)

37. Guidelines for Better Query Design (cont.) • Create temporary tables for groups of queries • Combine update operations • Retrieve only the data you need • Don’t have the DBMS sort without an index • Learn! • Consider the total query processing time for ad hoc queries