Enhanced Guide to Oracle 10g - PowerPoint PPT Presentation

1 / 91
About This Presentation
Title:

Enhanced Guide to Oracle 10g

Description:

You must insert a value or a NULL placeholder for every field ... To insert a string with a single quote, type the single quote twice. Example: ... – PowerPoint PPT presentation

Number of Views:320
Avg rating:3.0/5.0
Slides: 92
Provided by: course241
Category:
Tags: 10g | enhanced | guide | insert | oracle

less

Transcript and Presenter's Notes

Title: Enhanced Guide to Oracle 10g


1
Enhanced Guide to Oracle 10g
Chapter 3 Using SQL Queries to Insert, Update,
Delete, and View Data
2
SQL Scripts
  • Script text file that contains a sequence of
    SQL commands
  • Running a script
  • SQLgt START path_to_script_file
  • OR
  • SQLgt _at_ 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 DESCRIBE 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
Format Masks
  • All data is stored in the database in a standard
    binary format
  • Format masks are alphanumeric text strings that
    specify the format of input and output data
  • Table 3-1 Number format masks
  • Table 3-2 Date format masks

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

9
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'

10
Inserting Interval Values
  • Year To Month Interval
  • TO_YMINTERVAL(years-months)
  • e.g. TO_YMINTERVAL(3-2)
  • Day To Second Interval
  • TO_DSINTERVAL(days HHMISS.99)
  • e.g. TO_DSINTERVAL(-0 011500)

11
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)

12
Transactions
  • A transaction starts when you type one or more
    DML commands in SQLPlus
  • A transaction ends when you issue either the
    COMMIT or ROLLBACK command
  • SQLgtCOMMIT
  • SQLgtROLLBACK

13
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

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

15
Updating Records
  • Syntax
  • UPDATE tablename
  • SET column1 new_value,
  • column2 new_value,
  • WHERE search_condition
  • Each update statement can update row(s) in one
    table only
  • Can update multiple records if they all match the
    search condition

16
Search Conditions
  • Format
  • WHERE fieldname operator expression
  • Operators
  • Equal ()
  • Greater than, Less than (gt, lt)
  • Greater than or Equal to (gt)
  • Less than or Equal to (lt)
  • Not equal (lt gt, !, )
  • LIKE
  • BETWEEN
  • IN
  • NOT IN

17
Search Condition Examples
  • WHERE s_name Sarah
  • WHERE s_age gt 18
  • WHERE s_class ltgt SR
  • Text in single quotes is case sensitive

18
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 cant delete a record if it contains a
    primary key value that is referenced as a foreign
    key

19
Truncating Tables
  • Removes ALL table data WITHOUT saving any
    rollback information
  • Advantage fast way to delete table data
  • Disadvantage cant be undone
  • Syntax
  • TRUNCATE TABLE tablename

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

21
Creating Sequences
  • Syntax
  • CREATE SEQUENCE sequence_name
  • optional parameters
  • Example
  • CREATE SEQUENCE f_id_sequence
  • START WITH 200

22
Viewing Sequence Information
  • Query the SEQUENCE Data Dictionary View

23
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

24
Pseudocolumn Examples
25
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

26
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)

27
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,

28
Examples of Object Privileges
29
Granting and Revoking Object Privileges
30
Retrieving Data From a Single Table
  • Syntax
  • SELECT column1, column2,
  • FROM tablename
  • WHERE search_condition

31
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

32
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 owners name

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

34
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

35
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

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

37
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)

38
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)

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

40
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

41
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

42
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

43
Using Calculations in Queries
  • Arithmetic operations on retrieved data
  • Addition ()
  • Subtraction (-)
  • Multiplication ()
  • Division (/)
  • Example
  • SELECT inv_id, qohprice
  • FROM inventory

44
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

45
Using Single-RowNumber Functions
  • Example
  • SELECT s_name,
  • TRUNC((SYSDATE - s_dob)/365)
  • FROM my_students

46
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

47
Using Single-RowCharacter Functions
  • Example
  • SELECT UPPER(s_name)
  • FROM my_students

48
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

49
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

50
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

51
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

52
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

53
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)

54
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

55
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

56
Creating Alternate Column Headings in SQLPlus
  • Syntax
  • SELECT column1 heading1, column2 heading2,
  • Example
  • SELECT (SYSDATE s_dob) Student Age
  • FROM my_students

57
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

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

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

60
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

61
Using Runtime Variables
  • Example

62
Formatting Data Using theTO_CHAR Function
  • Used to display NUMBER and DATE values using a
    specific format mask
  • Syntax
  • TO_CHAR(fieldname, format_mask)

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

64
Inner Joins
FACULTY
  • One record is retrieved for each matching row

65
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
66
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

67
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

68
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

69
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

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

71
Outer Joins
  • No records retrieved for Michael

72
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

73
Outer Joins
Outer join marker
74
Self Joins
  • Used to join a table to itself when the table has
    a hierarchical relationship

75
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

76
Self Joins
PARENT_PROJECT
SUB_PROJECT
PROJECT
77
Self Join Example
78
Inequality Joins
  • Join created by placing making join condition
    satisfy an inequality condition
  • Only makes sense when primary/foreign key values
    are not surrogate keys

79
Inequality Joins
80
Nested Queries
  • Created when a subquery is nested within a main
    query
  • Main query first query listed in SELECT command
  • Subquery retrieves one or more values that
    specify the main querys search condition

81
Nested Query WhereSubquery 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)

Subquery that returns one value
82
Nested Query WhereSubquery 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)

Subquery that returns multiple values
83
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

84
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

85
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

86
Database Views
  • Creating a view
  • CREATE VIEW view_name AS
  • SQL_command
  • Views can be queried just like tables
  • SELECT
  • FROM view_name

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

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

89
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

90
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

91
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
Write a Comment
User Comments (0)
About PowerShow.com