1 / 53

Database Systems

Database Systems. Creating and Maintaining Database Objects Part 2. 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;. Date Arithmetic.

melchior-magda
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 Part 2

  2. 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;

  3. 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;

  4. 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;

  5. 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;

  6. 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;

  7. Group Functions • Used to perform an operation on a field from a group of retrieved records • AVG (average of all retrieved values) • COUNT (number of records retrieved) • MAX (maximum value retrieved) • MIN (minimum value retrieved) • SUM (sum of all retrieved values)

  8. Group Function Examples SELECT AVG (s_age) FROM my_students; SELECT MAX (s_age) FROM my_students; SELECT MIN (s_age) FROM my_students; SELECT SUM (s_age) FROM my_students;

  9. Using the GROUP BY Clause • GROUP BY must be used if some columns in the SELECT clause are used in a group function and some are not • Group all fields that are not included in the group function • Example: SELECT s_class, AVG(s_age) FROM my_students GROUP BY s_class;

  10. Creating Alternate Column Headings in SQL*Plus • Syntax: SELECT column1 “heading1”, column2 “heading2”, … • Example: SELECT (SYSDATE – s_dob) “Student Age” FROM my_students;

  11. Creating a Column Alias • Column alias: alternate column name that can be referenced in the ORDER BY and GROUP BY clauses • Syntax: SELECT column1 AS alias1 … • Example: SELECT (SYSDATE – s_dob) AS age_alias ORDER BY age_alias

  12. Dynamic SQL Queries • Queries that allow users to specify search conditions at runtime • Approaches • Substitution Values • Runtime Variables

  13. Using Substitution Values • Created when search expression is prefaced with an ampersand (&) • System then prompts user for value

  14. Using Runtime Variables • Runtime variable: variable defined in SQL*Plus environment • Syntax: DEFINE variable_name = variable_value; • You can then substitute the variable name for a query search condition value

  15. Using Runtime Variables • Example:

  16. Formatting Data Using theTO_CHAR Function • Used to display NUMBER and DATE values using a specific format mask • Syntax: TO_CHAR(fieldname, ‘format_mask’);

  17. Join Queries • Retrieve data from multiple tables by joining tables using foreign key references • Join query types: • Inner (equality) • Outer • Self • Inequality

  18. Inner Joins • One record is retrieved for each matching row

  19. Inner Joins • Syntax: SELECT column1, column2, … FROM table1, table2 WHERE table1.join_column = table2.join_column • You must include a join condition for every link between 2 tables Join condition

  20. Inner Joins • Example: SELECT s_name, f_name FROM student, faculty WHERE student.f_id = faculty.f_id; • If you have N tables in the FROM clause, you must have (N - 1) join conditions

  21. Qualifying Field Names • If a field in the SELECT clause exists in multiple tables in the FROM clause, you must qualify the field name by prefacing it with either table’s name

  22. Process for DesigningComplex Inner Join Queries • Identify all of the tables involved in the query, and label: • Display fields • Join fields • Search fields • Write the query • List all display fields in the SELECT clause • List all table names in the FROM clause • List all join condition links in the WHERE clause • List all search fields in the WHERE clause

  23. Outer Joins • Limitation of inner joins: some records may be omitted if corresponding records don’t exist in one of the tables • Example: retrieve records for all students, along with their corresponding ENROLLMENT information

  24. Outer Joins • Student 105 (Michael Connoly) does not have any ENROLLMENT records

  25. Outer Joins • No records retrieved for Michael:

  26. Outer Joins • To include records in first (inner) table, even when they do not have matching records in second (outer) table, place outer join marker (+) beside outer table name in join clause

  27. Outer Joins Outer join marker

  28. Self Joins • Used to join a table to itself when the table has a hierarchical relationship

  29. Self Joins • To create a self-join, you need to create a table alias, which gives an alternate name to the table so you can create a join condition • Syntax to create table alias in FROM clause: FROM table1 alias1, table2 alias2

  30. Self Joins PARENT_PROJECT SUB_PROJECT PROJECT

  31. Self Join Example

  32. Inequality Joins • Join created by placing making join condition satisfy an inequality condition

  33. Inequality Joins

  34. Nested Queries • Created when a sub-query is nested within a main query • Main query: first query listed in SELECT command • Sub-query: retrieves one or more values that specify the main query’s search condition

  35. Nested Query WhereSub-query Returns a Single Value • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE join conditions AND search_column1 = (SELECT column1 FROM table1, table2, … WHERE search and join conditions) Sub-query that returns one value

  36. Nested Query WhereSub-query Returns Multiple Values • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE join conditions AND search_column1 IN (SELECT column1 FROM table1, table2, … WHERE search and join conditions) Sub-query that returns multiple values

  37. Using Set Operators in Queries • Performs set operations on outputs of two unrelated queries • Both queries must have: • same number of display fields • corresponding display fields must have same data type

  38. Query Set Operators • UNION: combines results, suppresses duplicate rows • UNION ALL: combines results, displays duplicates • INTERSECT: finds matching rows • MINUS: returns the difference between returned record sets

  39. Selecting Records For Update • In a normal SELECT command, the retrieved records are not locked, and are available for other users to view, updated, and delete • Sometimes, you need to select records, and then immediately update them based on the retrieved values • Airline seat reservations • Inventory items for sale

  40. Selecting Records For Update • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE search and join conditions FOR UPDATE OF column1, column2, … NOWAIT;

  41. Selecting Records For Update • All retrieved records are locked until you issue a COMMIT command • Fields listed in FOR UPDATE clause are for documentation purposes only • NOWAIT clause is optional • Makes it so when another user tries to retrieved locked record, their system doesn’t just “hang”

  42. Database Views • Logical table based on a query • Does not physically exist in the database • Presents data in a different format from underlying tables • Uses: • Security • Simplifying complex queries

  43. Database Views • Creating a view: CREATE VIEW view_name AS SQL_command; • Views can be queried just like tables: SELECT * FROM view_name;

  44. Simple Views • Based on SQL query that retrieves data from only one table • View can support all table operations: • INSERT • UPDATE • DELETE

  45. Complex Views • Based on query that retrieves data from multiple tables • Can only be used to support SELECT operations • No table operations supported

  46. Index: Separate table is maintained that shows index keys and physical locations of corresponding records In Oracle, ROWID is translated to physical location of row on disk Improves response time of searches and joins Indexes

  47. Using Indexes • Create table index AFTER table is populated with data • Indexes make INSERT, UPDATE, and DELETE operations slower because index must also be maintained

  48. Indexing Strategies • A table can have indexes on multiple fields • Create indexes based on fields used for search or join operations • Typically, indexes only speed retrievals when <15% of the table records are involved • Each additional index adds processing overhead for INSERT, UPDATE, and DELETE operations • In Oracle, primary keys are automatically indexed

  49. Creating Indexes • Syntax: CREATE INDEX index_name ON tablename(index_field);

  50. Synonyms • Alternate name for a table • Allows you to not have to preface table with owner’s username when you are querying a table that belongs to another user

More Related