Database Systems

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 SQLPlus

• 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 SQLPlus
  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 tables 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 dont 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 querys 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 doesnt 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
Indexes

• 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

SLName ROWID
Brown 13387289
Jones 13879872
Smith 58925789
Helgeson 29875018
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
  lt15 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
  owners username when you are querying a table
  that belongs to another user

51
Public Synonyms

• Can only be created by a DBA
• Syntax
• CREATE PUBLIC SYNONYM synonym_name
• FOR owner_name.tablename
• All users with privileges to use table can then
  use synonym instead of owner_name.tablename

52
Private Synonyms

• You can create private synonyms for any tables
  that you have privileges to use
• Only you can use the synonym
• Syntax
• CREATE SYNONYM synonym_name
• FOR table_name.table_name

53
End of Lecture