1 / 97

Database Systems

Learn how to create and maintain database objects using SQL scripts, including inserting values into every field, updating records, deleting data, and using sequences.

Download Presentation

Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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;

More Related