Database Systems

1. Database Systems Creating and Maintaining Database Objects

2. SQL Scripts • Script: text file that contains a sequence of SQL commands • Running a script: SQL> START path_to_script_file; • Path cannot contain any blank spaces

3. Inserting a Value Into EveryField in a Record • Syntax: INSERT INTO tablename VALUES (column1_value, column2_value, …); • You must insert a value or a NULL placeholder for every field • Fields must be entered in the order they appear in the table when you issue the DESC command

4. Inserting a Value Into EveryField in a Record • Example:

5. Inserting Selected Table Fields • Command to insert values for selected record fields: INSERT INTO tablename (column1_name, column2_name, …) VALUES (column1_value, column2_value, …);

6. Inserting Selected Table Fields • Example:

7. Inserting Date Values • Date values must be converted from characters to dates using the TO_DATE function and a format mask • Example:

8. Inserting Text Data • Must be enclosed in single quotes • Is case-sensitive • To insert a string with a single quote, type the single quote twice • Example: 'Mike''s Motorcycle Shop'

9. Transactions • Transaction • Logical unit of work consisting of one or more SQL DML commands • INSERT, UPDATE, DELETE • All transaction commands must succeed or none can succeed • Transaction results are not visible to other users until they are “committed” to the database • Until a transaction is committed, it can easily be “rolled back” (undone)

10. Transactions • A transaction starts when you type one or more commands in SQL*Plus • A transaction ends when you issue either the COMMIT or ROLLBACK command SQL>COMMIT; SQL>ROLLBACK;

11. Committing and Rolling Back Data • COMMIT • Makes transaction command changes permanent in the database and visible to other users • ROLLBACK • Rolls back transaction command changes and restores database to its state before the transaction

12. Savepoints • Used to mark individual sections of a transaction • You can roll back a transaction to a savepoint

13. Updating Records • Syntax: UPDATE tablename SET column1 = new_value, column2 = new_value, … WHERE search_condition; • Records can be updated in only one table at a time • Can update multiple records if they all match the search condition

14. Search Conditions • Format: WHERE fieldname operator expression • Operators • Equal (=) • Greater than, Less than (>, <) • Greater than or Equal to (>=) • Less than or Equal to (<=) • Not equal (< >, !=, ^= • LIKE • BETWEEN • IN • NOT IN

15. Search Condition Examples WHERE s_name = ‘Sarah’ WHERE s_age > 18 WHERE s_class <> ‘SR’ • Text in single quotes is case sensitive

16. Deleting Records • Syntax: DELETE FROM tablename WHERE search_condition; • Deletes multiple records if search condition specifies multiple records • If search condition is omitted, all table records are deleted • You can’t delete a record if it contains a primary key value that is referenced as a foreign key

17. Truncating Tables • Removes all table data without saving any rollback information • Advantage: fast way to delete table data • Disadvantage: can’t be undone • Syntax: TRUNCATE TABLE tablename;

18. Sequences • Sequential list of numbers that is automatically generated by the database • Used to generate values for surrogate keys

19. Creating Sequences • Syntax: CREATE SEQUENCE sequence_name [optional parameters]; • Example: CREATE SEQUENCE f_id_sequence START WITH 200;

20. Viewing Sequence Information • Query the SEQUENCE Data Dictionary View:

21. Pseudocolumns • Acts like a column in a database query • Actually a command that returns a specific values • Used to retrieve: • Current system date • Name of the current database user • Next value in a sequence

22. Pseudocolumn Examples

23. Using Pseudocolumns • Retrieving the current system date: SELECT SYSDATE FROM DUAL; • Retrieving the name of the current user: SELECT USER FROM DUAL; • DUAL is a system table that is used with pseudocolumns

24. Using PseudocolumnsWith Sequences • Accessing the next value in a sequence: sequence_name.NEXTVAL • Inserting a new record using a sequence: INSERT INTO my_faculty VALUES (f_id_sequence.nextval, ‘Professor Jones’);

25. Object Privileges • Permissions that you can grant to other users to allow them to access or modify your database objects • Granting object privileges: GRANT privilege1, privilege2, … ON object_name TO user1, user 2, …; • Revoking object privileges: REVOKE privilege1, privilege2, … ON object_name FROM user1, user 2, …;

26. Examples of Object Privileges

27. Granting and Revoking Object Privileges

28. Retrieving Data From a Single Table • Syntax: SELECT column1, column2, … FROM tablename WHERE search_condition;

29. Retrieving Data From a Single Table • To retrieve every column in a table: SELECT * FROM … • To retrieve every record in a table, omit the search condition SELECT column1, column2, … FROM tablename;

30. Qualifying Table Names • If you retrieve data from a table that is owned by another user, you must qualify the table name by prefacing it with the owner’s name

31. Suppressing Duplicate Records • Sometimes queries retrieve duplicate records • To suppress duplicate outputs, use the DISTINCT qualifier: SELECT DISTINCT column1, column2, … FROM ...

32. Using Multiple Search Conditions • Combining search conditions • AND: both conditions must be true • OR: either condition can be true • Combining AND and OR in a single operation • AND comparisons are evaluated first • Always use parentheses to force conditions to be evaluated in the correct order

33. Searching for NULL Records • NULL: not defined • Use IS NULL search condition SELECT s_name, s_class FROM my_students WHERE s_class IS NULL;

34. Searching for NOT NULL Records • Use IS NOT NULL operator SELECT s_name, s_age FROM my_students WHERE s_class IS NOT NULL;

35. Using the IN and NOT IN Operators • IN retrieves all values where the search column value matches a set of values SELECT * FROM enrollment WHERE grade IN (‘A’, ‘B’);

36. Using the IN and NOT IN Operators • NOT IN retrieves all values where the search column value matches a set of values SELECT * FROM enrollment WHERE grade NOT IN (‘A’, ‘B’);

37. Using the LIKE Operator • Performs inexact searches by matching part of a character string WHERE fieldname LIKE character_string;

38. Using the LIKE Operator • Character string must be in single quotes and use wildcard characters • % represents multiple wildcard characters • _ represents a single wildcard character • Wildcard characters can be placed at beginning or end of string • Examples: WHERE s_class LIKE ‘_R’; WHERE s_name LIKE ‘J%’;

39. Sorting Query Output • Use the ORDER BY clause • Specify sort key, which is column by which output is sorted SELECT s_name, s_age FROM my_students ORDER BY s_age;

40. Sorting Query Data • Default sort order • Numerical: ascending • Character: A - Z • Date: oldest - newest • To force the sort order: use ASC or DESC • Example SELECT s_name, s_age FROM my_students ORDER BY s_age DESC;

41. Using Calculations in Queries • Arithmetic operations on retrieved data • Addition (+) • Subtraction (-) • Multiplication (*) • Division (/) • Example: SELECT inv_id, qoh*price FROM inventory;

42. Single-RowNumber Functions • ABS - absolute value • CEIL – rounds a number up to the next integer • FLOOR – rounds a number down to the previous integer • MOD – returns the remainder of a number and a divisor • POWER - raises a number to an exponent • ROUND - rounds a number • SQRT – returns the square root of a value • TRUNC - truncates a number to the nearest whole number

43. Using Single-RowNumber Functions • Example: SELECT s_name, TRUNC((SYSDATE - s_dob)/365) FROM my_students;

44. Single-RowCharacter Functions • CONCAT – joins 2 character strings • INITCAP – returns a string with the initial letter only uppercase • LENGTH – returns the length of a string • LPAD, RPAD – returns a string with a specific number of characters added on the left or right side • LTRIM, RTRIM – returns a string with all instances of a specific character trimmed from the left or right side • REPLACE – replaces all instances of a character with another character • UPPER/LOWER – returns a string in all upper/lower case letters

45. Using Single-RowCharacter Functions • Example: SELECT UPPER(s_name) FROM my_students;

46. Date Arithmetic • To find a date that is a specific number of days before or after a known date, add or subtract the number from the known date • Example: SELECT order_date + 30 FROM cust_order;

47. Date Arithmetic • To find the number of days between two known dates, subtract the later date from the earlier date • Example: SELECT SYSDATE – s_dob FROM my_students;

48. Date Functions • ADD_MONTHS • returns a date that is a specific number of months after a given date • Example: SELECT ADD_MONTHS(SYSDATE, 6) FROM dual;

49. Date Functions • LAST_DATE • Returns the date that is the last day of the month specified in the current date • Example: SELECT LAST_DATE(order_date) FROM cust_order WHERE order_id = 1057;

50. Date Functions • MONTHS_BETWEEN • Returns the number of months between two input dates • Example: SELECT MONTHS_BETWEEN(order_date, SYSDATE) FROM cust_order WHERE order_id = 1057;