Using SQL Queries to Insert, Update, Delete, and View Data

1. Using SQL Queries to Insert,Update, Delete, and View Data Chapter 3 A Guide to Oracle9i

2. Lesson A Objectives • Learn how to run a script to create database tables automatically • Learn how to insert data into database tables • Learn how to create database transactions and commit data to the database • Create search conditions in SQL queries • Understand how to update and delete database records, and how to truncate tables • Learn how to create and use sequences to generate surrogate key values automatically • Learn how to grant and revoke database object privileges A Guide to Oracle9i

3. Using Scripts to Create Database Tables • One or more SQL commands saved in a text file • Usually have .sql extension • To run from SQL*Plus: • Start full file path (c:\temp\myfile.sql) • @full file path (@c:\temp\myfile.sql) • Extension can be omitted if it is .sql A Guide to Oracle9i

4. Inserting Data into Tables • INSERT command adds new records • Field values should match column order, or be specified in command • INSERT INTO faculty (F_ID, F_LAST, F_FIRST, F_MI, LOC_ID) VALUES (1, 'Cox', 'Kim', 'J', 9); A Guide to Oracle9i

5. Format Models • Used to format data retrieved from database • Can be used to format a date to display time or a number to display as a currency A Guide to Oracle9i

6. Numerical Format Models A Guide to Oracle9i

7. Date Format Models A Guide to Oracle9i

8. Date Format Models A Guide to Oracle9i

9. Inserting Date and Interval Values • Use to_date function to convert a character string to a date • Specify date string and matching format model • TO_DATE('08/24/2004', 'MM/DD/YYYY') • TO_DATE('10:00 AM', 'HH:MI AM') • Use functions to convert character strings to intervals • TO_YMINTERVAL('4-9') inserts a positive interval of 4 years, 9 months • TO_DSINTERVAL('0 01:15:00') inserts a positive interval of 4 days, 1 hour, 15 minutes, 0 seconds A Guide to Oracle9i

10. Inserting LOBs • Before inserting LOB must insert a LOB locator • LOB locator: a structure that contains information that identifies the LOB data type and points to the alternate memory location • Write a program or use a utility to add LOB data to database • Use EMPTY_BLOB() function to insert a LOB locator A Guide to Oracle9i

11. Creating Transactions and Committing New Data • Transaction: series of action queries that represent a logical unit of work • User can commit (save) changes • User can roll back (discard) changes • Pending transaction: a transaction waiting to be committed or rolled back • Oracle DBMS locks records associated with pending transactions • Other users cannot view or modify locked records A Guide to Oracle9i

12. Commit and Roll Back in SQL*Plus • Transactions begin automatically with first command • Type COMMIT to commit changes • Type ROLLBACK to roll back changes A Guide to Oracle9i

13. Savepoints • A bookmark that designates the beginning of an individual section of a transaction • Changes are rolled back to savepoint A Guide to Oracle9i

14. Creating Search Conditions in SQL Queries • An expression that seeks to match specific table records • Used in SELECT, UPDATE and DELETE statements • WHERE fieldnamecomparison_operatorsearch_expression • WHERE S_ID = 1 A Guide to Oracle9i

15. Comparison Operators A Guide to Oracle9i

16. Defining Search Expressions • Character strings • Must be enclosed in single quotes • Case sensitive • Dates • Use to_date function with date string and format model • Intervals • Use to_yminterval and to_dsinterval with interval string format model A Guide to Oracle9i

17. Creating Complex Search Conditions • Combines multiple search conditions using the AND,OR, and NOT logical operators. • AND – both conditions must be true • OR – one or both condition must be true • NOT – opposite of actual value • Use () to group logical operators A Guide to Oracle9i

18. Updating and Deleting Existing Table Records • UPDATE: • Updates field values in one or more records in a table • Only one table may be updated at a time • UPDATE tablename SET field1= new_value1, field2 = new_value2, ... WHERE search condition; • DELETE: • Removes specific records from a database table • If search condition is omitted, entire table data is removed • DELETE FROM tablename WHERE search condition; A Guide to Oracle9i

19. Updating and Deleting Existing Table Records • TRUNCATE • Removes all of the table data without saving any rollback information • Must disable foreign key constraints before truncating table • TRUNCATE TABLE tablename; A Guide to Oracle9i

20. Sequences • Sequential lists of numbers to create unique surrogate key values • To use a sequence: • SELECT sequence_name.NEXTVAL FROM DUAL; • INSERT INTO location LOC_ID) VALUES(loc_id_sequence.NEXTVAL); A Guide to Oracle9i

21. Database Object Privileges • Privileges must be granted so that other users can access objects in user schema • GRANT privilege1, privilege2,…ON object_name TO user1,user2,...; • REVOKE privilege1, privilege2,... ON object_name FROM user1, user2, ...; • To grant or revoke privileges for everyone use PUBLIC as user A Guide to Oracle9i

22. Lesson B Objectives • Learn how to write SQL queries to retrieve data from a single database table • Create SQL queries that perform calculations on retrieved data • Use SQL group functions to summarize retrieved data A Guide to Oracle9i

23. Retrieving Data from a Single Database Table • SELECT fieldname1, fieldname2, ... FROM ownername.tablename WHERE search_condition; • To select: • All rows omit where clause • All fields, use *: SELECT * FROM … • Only unique field values: SELECT DISTINCT fieldname • Search condition: • Use comparison and logical operators • IS NULL/IS NOT NULL to match/exclude NULL values • IN/NOT IN to match set values • LIKE with wildcards % and _ to match character strings A Guide to Oracle9i

24. Sorting Query Output • Use ORDER BY sort_key_field(s) • Default order is ascending, use DESC to sort descending A Guide to Oracle9i

25. Using Calculations in SQL Queries • Calculations are performed by DBMS, result only sent to client • Can use arithmetic operators (+, -, *, /) • Place calculation in select clause: SELECT price * quantity FROM … • Calculations can be performed on NUMBER, DATE and INTERVAL fields only • Single-row functions: built in Oracle functions to perform calculations and manipulate retrieved data values A Guide to Oracle9i

26. Oracle9i SQL Group Functions • Group functions:perform an operation on a group of queried rows and returns a single result A Guide to Oracle9i

27. Group By • Use to group output by the field with duplicate values and apply group functions to the grouped data A Guide to Oracle9i

28. Having • Use to place a search condition on results of group function calculations • Like “WHERE” for group functions • HAVING group_function comparison_operator value • HAVING SUM(capacity) >= 100 A Guide to Oracle9i

29. Formatting Output in SQL*Plus • To change default column headings: • Specify alternate column headings: SELECT fieldname1 "heading1_text", fieldname2 "heading2_text", ... • Use an alias for column headings: SELECT fieldname1 AS alias_name1... • To change SQL*Plus line and page size settings • Select Options/Environment on menu bar • Modify linesize and pagesize to desired values A Guide to Oracle9i

30. Formatting Numbers and Dates • Use to_char function with format models • TO_CHAR(field_name, 'format_model') • SELECT inv_id, TO_CHAR(inv_price, '$99,999.99') FROM inventory WHERE item_id = 1; A Guide to Oracle9i

31. Lesson C Objectives • Learn how to create SQL queries that join multiple tables • Learn how to create nested SQL queries • Understand how to combine query results using set operators • Create and use database views A Guide to Oracle9i

32. Joining Multiple Tables • Join: combine data from multiple database tables using foreign key references • SELECT field1, field2, ... FROM table1, table2 WHERE table1.joinfield = table2.joinfield AND search_condition(s); • If tables share field names, must prefix field in select with table name (table1.field1, table2.field1) • Join condition: part of where clause indicating how tables are related (table1.foreign_key = table2.primary key) • Search conditions can be added to join condition using AND operator A Guide to Oracle9i

33. Inner Join • Join two tables based on values in one table being equal to values in another table • Also known as equality join, equijoin or natural join • Returns results only if records exist in both tables A Guide to Oracle9i

34. Joining Via Linking Table A Guide to Oracle9i

35. Using a Query Design Diagram • Helpful for creating complicated queries • Can use a formula to derive actual query from diagram A Guide to Oracle9i

36. Outer Join • Returns all rows in one table and matching rows in joined table • Inner table: all rows are returned • Outer table: matching rows are returned • Outer table marked with a + in join condition • inner_table.join_field = outer_table.join_field(+) • Null values are inserted for fields in outer table that are not found A Guide to Oracle9i

37. Self-Join • A query that joins a table to itself • Used when a table has a foreign key relationship to itself (usually parent-child relationship) • Must create a table alias and structure the query as if you are joining the table to a copy of itself • FROM table1 alias1, ... • Use alias, not table name for select and where clauses A Guide to Oracle9i

38. Self-Join Example A Guide to Oracle9i

39. Creating Nested Queries • Used to select results based on the result of a query • Consists of a main query and one or more subqueries. • Main query: first query that appears in the SELECT command • Subqueryretrieves values that the main query’s search condition must match A Guide to Oracle9i

40. Creating Nested Queries • Nested queries can return single or multiple values • To match single values use = operator • To match multiple values use IN operator • Subqueries can be nested to more than one level (nested subqueries) • Nested subqueries are slower than joins and should be used sparingly A Guide to Oracle9i

41. Nested Subquery Example A Guide to Oracle9i

42. Using Set Operators To Combine Query Results • Use to select data from multiple tables not connected with foreign key relationships A Guide to Oracle9i

43. Set Operators • query1 OPERATOR query2; (where operator is UNION, UNION ALL, INTERSECT, or MINUS) • Both queries must have same number of select fields and same data types in same order • UNION suppresses duplicate values • UNION ALL includes duplicate values • INTERSECT takes only matching fields • MINUS takes only fields in query1 not query2 A Guide to Oracle9i

44. Creating and Using Database Views • Similar to storing the result of a query in the database • Based on a source query that: • can specify a subset of a single table’s fields or records • can join multiple tables • Can be used to enforce security (user has access to view but not underlying table) A Guide to Oracle9i

45. Creating and Using Database Views • Views can be updateable if: • SELECT clause contains only fieldnames, no functions or calculations • cannot contain the ORDER BY, DISTINCT, or GROUP BY clauses, group functions, or set operators • search condition cannot contain a nested query • Views are used like tables for selecting, inserting, updating and deleting data (only updatable views can be modified) A Guide to Oracle9i

46. Creating and Deleting Views • CREATE OR REPLACE VIEW view_name AS source_query; • DROP VIEW viewname; A Guide to Oracle9i

47. Summary • Use INSERT commands to add data • NUMBER, DATE and INTERVAL data types can be converted to and from character strings using format models • Database changes are made within a transaction that can be committed or rolled back • Use search conditions to specify records to update, delete or select • Arithmetic, logical, grouping, and built-in Oracle functions can be used to specify search conditions and manipulate data • Query output can be formatted by modifying SELECT clause A Guide to Oracle9i

48. Summary • Results from more than one table related through foreign key relationships can be combined in a join • Results from more than one unrelated table can be combined using set operators • Queries can be “saved” by creating a view • Views can be used like tables to select, insert, update and delete data A Guide to Oracle9i