Oracle SQL - PowerPoint PPT Presentation

1 / 114
About This Presentation
Title:

Oracle SQL

Description:

Chapter 4 Date and Conversion Functions. Converting From One Datatype to Another ... The SYSDATE function returns the current date and time from Oracle. ... – PowerPoint PPT presentation

Number of Views:1368
Avg rating:3.0/5.0
Slides: 115
Provided by: pennyb2
Category:
Tags: sql | date | oracle

less

Transcript and Presenter's Notes

Title: Oracle SQL


1
Oracle SQL
  • Penny Bowman
  • February, 2003
  • Using text Oracle SQL Interactive Workbook

2
Oracle SQL
  • Relational Concepts / Introduction to SQL
  • Oracle Training Database
  • Oracle Data Dictionary
  • Use SQLPlus Worksheet
  • Oracle Functions
  • Number Functions
  • Character Functions
  • Date Functions
  • Conversion Functions
  • Coding SQL Predicates
  • Grouping Data

3
Oracle SQL
  • Decode
  • Combining Data from Multiple Tables
  • Coding Subqueries
  • Data Types Sequence
  • Oracle Tables
  • Create a Table from another Table
  • Insert, Update and Delete Rows
  • Drop a Table
  • Oracle Performance Considerations

4
Chapter 1- SQL and Data
  • Lab 1.2 - Student Schema Appendix C
  • Lab 1.3 - Referential Integrity and Table
    Relationships
  • Lab 1.4 - SQLPlus Environment

5
Relational Concepts
  • A relational database is a collection of data
    where logically related data is grouped into
    tables (files) within the database
  • Rows (records) appear horizontally in a report,
    and contain one or more columns
  • Tables within a relational database hold columns
    (fields) of data that appear vertically in a
    report
  • Indexes are created for relating tables and for
    faster access to the data

6
Introduction to Structured Query Language
  • DCL - Data Control Language - for data security -
    uses Grant and Revoke verbs
  • DDL - Data Definition Language - for physical
    data structure maintenance - uses Create, Alter,
    Drop verbs
  • DML - Data Manipulation Language - for accessing
    and modifying data - uses Select, Insert, Delete,
    and Update verbs
  • SQL can be bound ahead of execution time
    (static) or used prepared during execution
    (dynamic)

7
Student Database Schema
  • Throughout the training course we will be using a
    Student database that comes with the text
  • These tables contain student related information
  • Course, Section, Student, Enrollment, Instructor,
    Zipcode, Grade_Type, Grade_Type_Weight, Grade,
    Grade_Conversion
  • Lets Review Appendix C the Student Database
    Schema

8
While in SQLPlus Worksheet
  • Enter your sql statement at the top
  • Click the lightning bolt to execute it
  • Notice the result set in the bottom half
  • Click the SQL button to look at previous commands
    for this session
  • Use Edit ? Clear All to empty the top or the
    bottom, depending on your cursor position
  • Save your sql
  • File ? Save Input As saves the top half
  • Save the results of the query execution
  • File ? Save Output As saves the bottom half
  • Open previously saved files
  • File ? Open

9
Data Dictionary ViewsLab 13.1
  • Use the DESCribe command to examine the table
    structure of each View before you SELECT from
    the View. This is a sampling of the available
    views
  • Prefix choices are USER_, ALL_, DBA_
  • USER_CATALOG
  • USER_COL_COMMENTS
  • USER_COL_PRIVS
  • USER_CONSTRAINTS
  • USER_CONS_COLUMNS
  • USER_DEPENDENCIES
  • USER_INDEXES
  • USER_IND_COLUMNS
  • USER_OBJECTS
  • USER_RESOURCE_LIMITS
  • USER_ROLE_PRIVS
  • USER_TABLES
  • USER_TABLESPACES
  • USER_TAB_COLUMNS
  • USER_TAB_COMMENTS
  • USER_USERS
  • USER_VIEWS

10
Chapter 2 SQL The Basics
  • SELECT Statement
  • SQLPlus
  • WHERE Clause
  • Comparison and Logical Operators
  • Precedence of Logical Operators
  • ORDER BY Clause

Labs 1Lab211a, 2Lab211b, 3Lab211c, 4Ex231,
5Ex232, 6Ex233, 7Ex234, 8Lab231a, 9Lab231b,
10Lab232ab, 11Ex24, 12Lab241a, 13Lab241b,
14Lab241c
11
SELECT Statement
  • The result of a SELECT query is a result set that
    includes the rows and columns that satisfy your
    query.
  • SELECT - specifies the columns or fields to be
    included in the result set
  • FROM - identifies the tables or views from which
    the data will be retrieved
  • WHERE - the predicate(s) that determine which
    rows will be retrieved
  • ORDER BY - determines the sequence of the rows

12
SELECT Statement Syntax
  • SELECT ALLDISTINCT
  • columns AS newname
  • FROM table or view AS shortname
  • WHERE conditions
  • GROUP BY fields
  • HAVING conditions
  • ORDER BY columninteger ASCDESC

13
BETWEEN Predicate
  • Tests if the value of a field falls (inclusively)
    between two other values
  • WHERE field NOT BETWEEN
  • field AND field
  • Logically Comparable to using gt and lt

14
IN Predicate
  • Tests if the value equals another value in a
    provided set of values
  • WHERE field NOT IN
  • (field1, field2, field3)
  • Equivalent to ANY
  • Field can be a value, set or subquery

15
LIKE Predicate
  • Powerful for character string comparisons
  • If a pattern does not contain the character,
    the condition can be TRUE only if both operands
    have the same length
  • WHERE field NOT LIKE value
  • Wildcard characters
  • - string of zero or more characters ABC
  • _ - underscore - any single character A_C
  • Examples
  • WHERE ename LIKE MA
  • WHERE ename LIKE SMITH_
  • WHERE ename LIKE S_I

16
Compound Predicate
  • All of the predicate forms can be combined using
    the compound logical operators
  • AND, OR, and NOT
  • WHERE NOT predicate ANDOR
  • NOT predicate
  • The result is either TRUE, FALSE, or Unknown
    about a given row or group of rows (grouping
    predicates)
  • If you dont use parentheses, these operators are
    processed in this order NOT , AND, OR

17
NULL Predicate
  • A NULL value is the relational database
    representation of no value exists
  • WHERE field IS NOT NULL
  • Due to the complexity of null handling, usage of
    Nulls has been discouraged in FSU/AIS database
    table columns

18
Chapter 3 Oracle Character Functions
  • Character Functions
  • Lower / Upper / InitCap
  • LPAD / RPAD
  • LTRIM / RTRIM
  • SUBSTR / INSTR
  • Length
  • DUAL Table
  • Concatenation
  • Translate / Replace
  • Functions in Where and Order By clause
  • Nested Functions
  • Labs
  • 15Ex31a, 16Ex31b, 17Ex31c, 18Ex31d, 19Ex31e,
    20Ex31f, 21Ex31g, 22Ex31h, 23Ex31i, 24Ex31j,
    25Ex31k, 26Lab311a, 27Lab311b, 28Lab311c,
    29Lab311d, 30Lab311e, 31Lab311f, 32Lab312a,
    33Lab312b

19
Chapter 3 Oracle Number Functions
  • Number Functions
  • ABS
  • SIGN
  • MOD
  • Round / Trunc
  • Arithmetic Operators

Labs 34Ex32, 35Lab321
20
Number Functions
Number functions accept numeric input and return
numeric values.
  • ABS(n)
  • ACOS(n)
  • ASIN(n)
  • ATAN(n)
  • ATAN2(n, m)
  • CEIL(n)
  • COS(n)
  • COSH(n)
  • EXP(n)
  • FLOOR(n)
  • LN(n)
  • LOG(m,n)
  • MOD(m,n)
  • POWER(m,n)
  • ROUND(n,m)
  • SIGN(n)
  • SIN(n)
  • SINH(n)
  • SQRT(n)
  • TAN(n)
  • TANH(n)
  • TRUNC(n,m)

21
Chapter 4 Date and Conversion Functions
  • Converting From One Datatype to Another
  • To_Number
  • To_Char
  • To_Date
  • Applying Oracles Date Format Models
  • Trunc Function
  • The Fill Mode
  • Performing Date and Time Math
  • Round Function
  • Date Functions (p112)
  • Labs
  • 36Ex41a, 37Lab411, 38Lab412, 39Ex42, 40Lab421,
    41Lab422a, 42Lab422b, 43Lab42c, 44Ex43a, 45Lab431

22
Date Functions
  • Date functions operate on values of the DATE
    datatype.
  • All date functions return a value of DATE
    datatype, except the MONTHS_BETWEEN function,
    which returns a number.

23
Date Functions
  • ADD_MONTHS(d,n)
  • LAST_DAY(d)
  • MONTHS_BETWEEN(d,e)
  • NEW_TIME(d,a,b)
  • NEXT_DAY(d,char)
  • ROUND (d ,fmt)
  • SYSDATE
  • TRUNC(d ,fmt)

24
Date Truncation and Rounding
  • ROUND(d,fmt)
  • TRUNC(d,fmt)
  • Format model for fmt in ROUND and TRUNC
  • CC or SCC
  • YYYY or SYYYY
  • YYY or YY or Y
  • Y,YYY or YEAR or SYEAR
  • Q
  • MONTH or MON or MM or RM
  • WW or IW
  • W
  • DDD or DD or J
  • DAY or DY or D
  • HH or HH12 or HH24
  • MI

25
Conversion Functions
  • Conversion functions convert a value from one
    datatype to another.
  • Generally, the form of the function names follows
    the convention datatype TO datatype
  • The first datatype is the input datatype the
    last datatype is the output datatype.

26
Conversion Functions
  • CHARTOROWID(char)
  • CONVERT( char, dest_char_set ,source_char_set )
  • HEXTORAW(char)
  • RAWTOHEX(raw)
  • ROWIDTOCHAR(rowid)
  • TO_CHAR, date conversion
  • TO_CHAR(d , fmt , nlsparams )
  • TO_CHAR, number conversion
  • TO_CHAR(n , fmt , nlsparams )
  • TO_DATE (char , fmt , nlsparams )
  • TO_MULTI_BYTE(char)
  • TO_NUMBER (char , fmt , nlsparams )
  • TO_SINGLE_BYTE(char)
  • TRANSLATE USING
  • TRANSLATE(text USING CHAR_CS NCHAR_CS )

27
Date Format Models for fmt of TO_CHAR and TO_DATE
  • SCC or CC
  • YYYY or SYYYY
  • YYY or YY or Y
  • IYYY
  • IYY or IY or I
  • Y,YYY
  • SYEAR or YEAR
  • RR
  • BC or AD
  • B.C. or A.D.
  • Q
  • MM
  • RM
  • MONTH
  • MON
  • WW or W
  • IW
  • DDD or DD or D
  • DAY
  • DY
  • J
  • AM or PM
  • A.M. or P.M.
  • HH or HH12
  • HH24
  • MI
  • SS or SSSSS
  • -/ , . (punctuation)
  • text.. (string)

28
Date Format Prefixes and Suffixes
  • Prefix
  • FM (fill mode)
  • FX (format exact)
  • Suffix
  • TH (ordinal number 4th)
  • SP (spelled out number - FOUR)
  • SPTH and THSP (spelled out ordinal number
    FOURTH)
  • Note When prefixes and suffixes are added to a
    date format, the case (upper, initial, or lower)
    is determined by the format element, not by the
    prefix or suffix. ddTH produces 04th not
    04TH
  • Date Format Case Control
  • Uppercase
  • DAY, DY, MONTH, MON, YEAR, AM, PM, A.M., A.m.,
    P.M., P.m.
  • Initial Caps
  • Day, Dy, Month, Mon, Year, Am, Pm
  • Lowercase
  • day, dy, month, mon, year, am, pm

29
Number Formats for fmt of TO_CHAR
  • 9
  • 0
  • B
  • MI
  • S
  • PR
  • D
  • G
  • L
  • C
  • ,
  • .
  • V
  • EEEE
  • RN or rn
  • DATE

30
SYSDATE Function
  • The SYSDATE function returns the current date and
    time from Oracle.
  • If you want to only manipulate the CURRENT DATE,
    and you are not selecting other columns from a
    table, you may issue a SELECT statement using the
    Oracle one row table
  • SELECT SYSDATE FROM DUAL
  • Note This is the DB2 way ?
  • SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1

31
Review Date Functions
  • Before we move to the next topic, lets practice
    writing SQL queries that use the Oracle DATE
    functions and formats using the DUAL table.
  • SELECT SYSDATE FROM DUAL (notice the default
    Oracle date format is mm/dd/yy)
  • SELECT TO_CHAR(SYSDATE,'MM/DD/YY')as MyDate from
    dual
  • SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')as MyDate
    from dual
  • SELECT TO_CHAR(sysdate 1) as NextDay from dual

32
Review Date Functions
  • SELECT TO_CHAR(SYSDATE,'Year') as MyYear ,
    TO_CHAR(SYSDATE,'Month') as MyMonth,
    TO_CHAR(SYSDATE,'Day') as MyDay from dual
  • SELECT TO_CHAR(SYSDATE,'YYYYTH-MMTH-DDTH')as
    MyDate from dual
  • SELECT TO_CHAR(SYSDATE, 'hhmmss') as Time1,
    TO_CHAR(SYSDATE,'HHSPMMSPSSSP') as Time2 from
    dual
  • Use SQLPlus Worksheet to practice these queries.
    Develop other date / time queries.

33
Other Single-Row Functions
  • NVL (expr1, expr2)
  • UID
  • USER
  • USERENV (option) (option can be ENTRYID,
    SESSIONID, TERMINAL, LANGUAGE, LABEL)

34
Using a Function in an Order By
  • Oracle allows a column function in an Order By
    clause . See Lab 87ex
  • Select student_id, count()
  • from student.enrollment group by student_id
  • order by count() desc
  • Select street_address from student.student
  • order by length(street_address) desc

35
Chapter 4 NVL and Decode Functions
  • Chapter 4 NVL and Decode Functions
  • NVL Function
  • Decode Function
  • Order by Decode
  • Chapter 15 (pages 414, 415, 424)

Labs 46Ex44a, 47Ex44b, 48Lab441,
49Lab442, 82Lab151, 83Lab151
36
Decode
  • Oracle has the DECODE function that allows you to
    substitute values using IF/THEN logic in your
    SELECT statement.
  • DECODE (if_expr,
  • equals_search, Then_result ,else_default )
  • The if_expr is a column or an expression.
  • The equals_search, Then_result part of the
    statement are pairs the search for value and
  • the replace with value.
  • The Else part is optional, but will assign a
    value if none of the searched values are found.

37
Decode Example
  • You can use functions with DECODE
  • Select
  • count(decode(state, NY, 1)) New York Count,
  • count(decode(state, TX, 1)) Texas Count,
  • count(decode(state, NJ, 1)) New Jersey Count
  • From student.zipcode

38
Decode Example
  • Example Resultset
  • New York Count Texas Count New Jersey Count
  • 123 0
    74
  • (Note Oracle returned 1 row with 3 calculations
    included.)

39
Decode Example
  • You can also group data and nest functions with
    DECODE. This example uses the the Decode with the
    Count and the Sign functions.
  • Count the number of Courses that totaled
  • more than or equal to 500
  • and the number that totaled less.

40
Decode Example
  • Select course_no,
  • count (decode (sign(nvl(cost,0) -500), -1 , 'A',
    null ))
  • count (decode (sign(nvl(cost,0) -500), 0 , 'A',
    null)) "Less Than or 500",
  • count
  • (decode (sign(nvl(cost,0) -500), 1 , 'A', null))
    "Greater Than 500"
  • From student.course
  • group by course_no

41
Decode Example
  • To evaluate the function, read it from the inside
    out ? first look at the NVL function. If the
    total equals null, a zero is substituted.
  • The result of the expression (nvl(total,0) 500)
    returns either a zero, a negative number, or a
    positive number. The sign function is applied to
    this expression.
  • The SIGN function returns a 1 if the result is
    positive, a 1 if the result is negative, and a 0
    if the result is zero.

42
Decode Example
  • The DECODE function then compares the result from
    the SIGN function to the search criteria.
  • On the first column in our example, if the SIGN
    function returns a 1 or a 0, then the total was
    less than or 500. The DECODE function returns
    the value A that can be counted. The two counts
    are added together.
  • On the second column of our example, if the SIGN
    function returns a 1, then the total was gt 500.
    The DECODE function returns a value A that can
    be counted.

43
Chapter 5 Aggregate Functions
  • Aggregate Functions
  • Group By
  • Having

Labs 50Ex51, 51Lab511, 52Ex52, 53Lab52
44
Grouping Data
  • Occurs any time detail information is used only
    for the purpose of deriving summary information
  • Used to cluster your result into sets, or
    groups of rows that have equal values in the
    same columns
  • Use GROUP BY and HAVING

45
GROUP BY
  • SELECT fields FROM tables
  • WHERE conditions
  • GROUP BY fields
  • HAVING conditions
  • ORDER BY fields
  • GROUP BY is used to summarize the columns
    selected.

46
GROUP BY
  • HAVING optionally follows the GROUP BY and can be
    used to place a condition on the GROUP
  • The HAVING condition can reference
  • One of the columns
  • A column function applied to the rows of the
    groups
  • A subquery which only returns one row

47
Group Functions
  • Default is to include all the candidate rows, but
    using DISTINCT causes the group function to use
    only the distinct values of the argument
    expression
  • AVG (DISTINCT ALL n)
  • COUNT( DISTINCT ALL expr )
  • MAX(DISTINCT ALL expr)
  • MIN ( DISTINCT ALL expr)
  • STDDEV(DISTINCT ALL n)
  • SUM(DISTINCT ALL n)
  • VARIANCE (DISTINCT ALL n)

48
Count Rows By State
  • SELECT State ,
  • Count() as Total
  • FROM student.zipcode
  • GROUP BY State

ST TOTAL CT 19 FL 1 GA
1 MA 5 MI 1 NJ
74 NY 123 OH 1 PR 1 WV
1 10 rows selected.
49
Count the Number of Rows in New York
  • SELECT State , Count() as Total
  • FROM student.zipcode
  • GROUP BY State
  • Having State NY
  • ST TOTAL
  • -- ----------
  • NY 123

50
Count States With Only 1 Row
  • SELECT State, Count() as Total
  • FROM student.zipcode
  • GROUP BY State
  • Having Count() 1

ST TOTAL FL 1 GA 1 MI
1 OH 1 PR 1 WV 1 6
rows selected.
51
Chapter 6 - Equijoins
  • Two Table Join
  • Nulls and Joins
  • Cartesian Product
  • Joining More than 2 Tables

Labs 54Ex61a, 55Lab611a, 56Lab612,
57Lab613, 58Ex62a, 59Lab621, 60Lab622a2,
61Lab622bc
52
JOIN (equi or inner)
  • There must be at least one column with shared
    values between two tables in order to join the
    two tables in a SELECT statement
  • Either the table alias or table name must prefix
    the columns referenced that exist in more than
    one of the joined tables
  • The WHERE clause must contain all the proper join
    predicates to avoid a Cartesian Product

53
Chapter 7 - Subqueries
  • Simple Subqueries
  • Subqueries Returning Multiple Rows
  • Subqueries and Joins
  • Subqueries Returning Multiple Columns
  • Correlated Subqueries
  • The Exists Operator / Not In
  • Any, Some, and All Operators in Subqueries
  • Labs
  • 62Ex71a, 63Ex71b,
  • 64Lab711,
  • 65Lab712,
  • 66Lab713,
  • 67Ex72,
  • 68Lab722,
  • 69Lab73,
  • 70Lab731

54
SubQueries In a Predicate
  • When a subquery is used in a predicate, the
    subquery supplies information needed to qualify a
    row (in a WHERE clause), or a group of rows (in a
    HAVING clause).
  • The subquery is used as the right operand of a
    search condition, rather than a value or a column
    name.
  • In Oracle, a subquery can SELECT 1 or more
    columns and can reference more than 1 table.
  • Order By cannot be used in the subquery.

55
SubQueries In a Predicate
  • When only 1 row is returned by the subquery, you
    can use these comparative operators between the
    left operand and the Subquery
  • lt lt gt gt ltgt
  • If the subquery returns more than one row, and
    you have used one of these operators, Oracle will
    generate an SQL error.

56
SubQuery Correlation
  • A Correlated Subquery executes repeatedly, in
    sync with the outer querys execution. The outer
    query can be a SELECT, UPDATE, or DELETE
    statement.
  • A Non-correlated Subquery executes first, and
    once. Bottom-up execution. The previous subquery
    examples have all been non-correlated.

57
Using EXISTS
  • The (NOT) EXISTS predicate can be used to
    evaluate a subquery as True or False without
    materializing the data in the subquery.
  • WHERE EXISTS (SUBSELECT)
  • The result is true if the SUBSELECT defines a
    logical result table of at least one row.
  • Since only existence is important in this case,
    the field(s) defined by the SUBSELECT is
    immaterial. Many developers use the constant 1.

58
Not EXISTS vs Not In With NULL Values
  • SELECT instructor_id, last_name, first_name, zip
  • FROM student.instructor ins
  • WHERE not EXISTS
  • (SELECT 1 FROM student.zipcode
  • WHERE ins.zip zip)
  • Returns 1 row with a null value in the Zip
    column.

59
Not EXISTS vs Not In With NULL Values
  • SELECT instructor_id, last_name, first_name, zip
  • FROM student.instructor ins
  • WHERE zip NOT IN
  • (SELECT zip FROM student.zipcode)
  • Return no rows.
  • Note The not exists operator tests for NULL
    values, the NOT IN operator does not.

60
SubQueries In a Predicate
  • If the SOME or ANY statement is used
  • ? the comparison is true if at least one value
    from the logical result table of the subselect
    meets the condition. Evaluates to FALSE if the
    query returns no rows.
  • If the ALL statement is used
  • ? the comparison is true if the logical result
    table is empty or, if the comparison is true for
    all values of the result table. Evaluates to
    FALSE if the comparison is true for at least 1
    but not all the values.

61
Chapter 8 Set Operators
  • Union
  • Union All
  • Minus
  • Intersect
  • Labs
  • 71Ex81,
  • 72Lab811, 73Ex82,
  • 74Lab82

62
UNION UNION ALL
  • Combines rows from multiple queries to create a
    new results table.
  • Each SELECT statement forms an interim results
    table which are then combined into a final
    results table.
  • Rules
  • Each select statement must reference the same
    number of columns
  • The columns must be compatible data types, left
    to right.

63
UNION UNION ALL
  • Union Combines two queries removes any
    duplicate rows from the final result set, and
    sorts the results.
  • Union All Combines two queries returns all rows
    returned by each query, including duplicates.
    Does not sort.
  • ORDER BY, if used, must follow the last query in
    the UNION sequence

64
MINUS
  • .
  • A set operator that combines two queries and
    returns all distinct rows returned by the 1st but
    not by the 2nd query.
  • The sequence of the queries matters.
  • The final result set includes rows that only
    appeared in the first query
  • and does not return the rows that appeared in
    both of the interim result sets
  • nor does it return any rows that appeared in only
    the second query.

65
INTERSECT
  • .
  • A set operator that combines two queries and
    returns all distinct values where the two sets
    overlap, or intersect.
  • The final result set includes rows that appeared
    in both the two result sets
  • The sequence of the queries do not matter.

66
Chapter 9 Complex Joins
  • Outer Joins
  • Outer Join Operator ()
  • Left / Right
  • Use NVL Function
  • Using Union to Perform Full Outer Join
  • Self Joins
  • Labs
  • 75Ex91,
  • 76Lab911, 77Lab912,
  • 78Lab92

67
Joining Tables Using Oracle
  • JOIN Types
  • JOIN (equi or inner)
  • OUTER JOINS
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • Joining a table to itself

68
Outer Join
  • The Outer Join returns all the rows that satisfy
    the join condition and those rows from one table
    for which no rows from the other satisfy the join
    condition.
  • To write a query that performs an outer join of
    Tables A and B and returns all rows from A, apply
    the outer join operator () to all columns of B
    in the join condition.
  • For all rows in A that have no matching rows in
    B, Oracle returns NULL for any select list
    expressions containing columns of B.
  • The () operator is placed next to the columns in
    the WHERE clause that may have the null values
    returned to the result set.

69
NVL in an Outer Join
  • Using the NVL function in an OUTER JOIN provides
    a way to replace derived null values with an
    alternative value.
  • Both parameters of the NVL function must be of
    compatible data types.

70
Chapter 15 Advanced SQL Queries
  • Aggregate Functions and Joins (page 416)
  • In Line Views (page 418)
  • Answers 15.1.2 a and b (Lab 86Lab1512a)
  • Top-N Query (page 420)
  • Lab 85Lab15Topn
  • Hierarchical Queries (page 429)
  • Lab 84Lab152

71
Chapter 12 Views, Indexes, and Sequences
  • Creating and Modifying Views
  • Indexes
  • Sequences

72
Sequence
  • These sequence objects are independent of other
    objects in the Oracle database.
  • The sequence can begin at a specified value and
    increment by a specified value.
  • Use the CREATE command to set up a sequence and
    the DROP command to remove it from the system.

73
Sequence
  • You must qualify CURRVAL and NEXTVAL with the
    name of the sequence
  • sequence.CURRVAL
  • sequence.NEXTVAL
  • The following statement increments the ZSEQ
    sequence and returns the new value
  • SELECT zseq.nextval FROM dual
  • The following statement selects the current value
    of ZSEQ, but does not change its value
  • SELECT zseq.currval from dual

74
Sequence
  • Create SEQUENCE customer_number START WITH
    2002100
  • INCREMENT BY 10
  • NOCACHE
  • Select customer_number.nextval from dual
  • Select customer_number.currval from dual

75
Sequence
  • INSERT INTO mycustomer
  • (customer_id, name, address, city, state,
    zip_code, area_code, phone_number)
  • Values
  • (customer_number.NEXTVAL, Jones, Tallahassee,
    FL, 32312, 850, 6683885)
  • Select from mycustomer

76
Chapter 11 Create, Alter, Drop Tables
  • Creating Tables
  • Common Datatypes
  • Creating Tables Based on Other Tables
  • Integrity Constraints
  • Dropping Tables
  • Altering Tables and Manipulating Constraints

77
Working With Oracle Tables
  • Now that you have practiced selecting information
    from existing tables, you can learn to create
    your own tables and change the data stored in the
    table.
  • In this unit of the course, you will
  • Create a Table from another Table
  • Insert, Update and Delete Rows
  • Drop a Table

78
Working With Oracle Tables
  • In order to create a table in your own schema,
    you must have CREATE TABLE system privilege
  • To create a table in another users schema, you
    must have CREATE ANY TABLE system privilege
  • See Chapter 4 of the Oracle8 SQL Reference manual
    for more detailed information

79
Create Table Statement
  • CREATE TABLE tablename
  • (column-name1 datatype1 NOT NULL WITH
    DEFAULT,
  • (column-name2 datatype2 NOT NULL WITH
    DEFAULT,
  • )
  • This statement establishes the Table Name, all
    the Columns in the table, and the data type and
    length of the columns contained in the table.
  • An Oracle table can have up to 1000 columns.
  • You can also create associated indexes and create
    referential constraints to reference other tables
    in the database.

80
Create Table And Populate with Rows From Another
Table
  • Use a subquery in the CREATE statement to
    identify the rows that you want inserted into the
    new table. In this example, create a new table
    with information only for customers that are in
    Texas
  • CREATE TABLE mycustomer
  • (customer_id, name, address, city, state,
    zip_code, area_code, phone_number)
  • AS SELECT customer_id, name, address, city,
    state, zip_code, area_code, phone_number
  • FROM pbowman.CUSTOMER
  • WHERE state 'TX'

81
Evaluate the New Table
  • DESCRIBE mycustomer
  • Select count() from mycustomer
  • (9 rows selected)
  • Select from mycustomer
  • Use this technique to build tables with test data
    while you are developing your sql statements for
    your application.

82
Dropping Tables
  • DROP - To completely remove a table from the
    database, use the SQL DROP statement. This is
    the format for the DROP statement.
  • DROP TABLE table-name
  • When you drop a table, you delete
  • -all rows in the table
  •  -the CREATE statement which defined the table
  •  -corresponding referential constraints in which
    the table is a parent or dependent

83
Chapter 10 Insert, Update, and Delete
  • Manipulating Data and Transaction Control
  • Insert
  • Update
  • Delete
  • Truncate

84
Insert Statement
  • INSERT INTO table (columns..)
  • VALUES(values..)SUBSELECT
  • The INSERT statement inserts one or more rows
    into a table. The columns which are the target of
    the insert can optionally be specified. If they
    are not specified, all columns of the table is
    the default.
  • If the VALUES clause is used, only a single row
    can be defined and inserted. In this case, the
    values must be in the same order as the columns
    are listed in the CREATE statement for the table.

85
Insert Statement
  • You must supply values for the primary key
    columns. Oracle will provide default values for
    the non-specified columns according to the
    default value rules established when the table
    was created.
  • To insert rows into a table, the table must be in
    your own schema or you must have INSERT privilege
    on the table.

86
Insert Statement
  • When using a SUBSELECT to provide the source data
    for the INSERT statement, it is likely that more
    than a single row will be inserted.
  • When you use a subselect in an UPDATE, DELETE, or
    INSERT statement, the subselect cannot use the
    same table as the UPDATE, DELETE, or INSERT is
    using.
  • It is also possible that no rows will be inserted
    depending on filtering with a WHERE predicate.
  • As for the VALUES clause, the number of columns
    in the logical result table defined by the
    SUBSELECT must match and the data types must be
    compatible.

87
UPDATE Statement
  • UPDATE table
  • SET column fieldNULL,
  • WHERE search condition
  • The UPDATE statement updates one or more rows of
    a table or view.
  • Optionally, the UPDATE can specify a search
    condition using the WHERE clause.
  • When you use a subselect in an UPDATE, DELETE, or
    INSERT statement, the subselect cannot use the
    same table as the UPDATE, DELETE, or INSERT is
    using.

88
UPDATE Statement
  • The SET clause can be repeated for each column
    which we'd like to UPDATE (each SET except the
    last followed by a comma).
  • If you don't code a condition, every single row
    of the target table will be updated with the same
    values specified in the SET clause.
  • The majority of UPDATE data manipulation you will
    be doing will be accomplished using host
    variables in applications.

89
DELETE Statement
  • DELETE FROM table WHERE search condition
  • The DELETE statement deletes one or more rows
    from a table or view.
  • If a DELETE statement is run without a search
    condition, every row of the target table is
    deleted!
  • Therefore, the DELETE statement should be used
    with caution. Specify predicates that isolate one
    and only one row.
  • When you use a subselect in an UPDATE, DELETE, or
    INSERT statement, the subselect cannot use the
    same table as the UPDATE, DELETE, or INSERT is
    using.

90
When Updating and Deleting Rows
  • When updating a parent table...      
  • the updated value for a primary key
  • must be unique and not null
  • All dependent rows must be updated or deleted
    before the parent is updated.
  • When updating a dependent table... the updated
    value for a foreign key must have a matching
    primary key in the parent table or be null

91
When Updating and Deleting Rows
  • When deleting from a parent table, the effect on
    the dependent table is determined by the DELETE
    rule chosen when the table was created.  
  • RESTRICT - The parent row cannot be deleted if
    any associated dependent rows exist (default
    rule)
  • SET NULL - The parent row can be deleted the
    foreign key of all associated dependent rows is
    set to null values
  • CASCADE - The parent row can be deleted the
    delete operation cascades through all associated
    dependent rows
  • For dependent tables, no deletion rules apply.

92
Oracle Datatypes - Page 291
  • CHAR(size)
  • NCHAR(size)
  • NVARCHAR2(size)
  • VARCHAR2(size)
  • VARCHAR
  • NUMBER(p,s)
  • LONG
  • DATE
  • Most often used types
  • RAW(size)
  • LONG RAW
  • BLOB
  • CLOB
  • NCLOB
  • BFILE
  • MLSLABEL

93
Oracle Character Datatypes
  • CHAR(size) - fixed length character string,
    blank-padded by Oracle to the column length,
    maximum 2000 characters, a zero-length string is
    blank-padded to 1 character when used in
    comparisons
  • NCHAR(size) fixed length national character set
    string, the maximum length is determined by the
    national character set definition, but the
    maximum for Oracle is 2000. You cannot insert a
    CHAR into an NCHAR or vice versa.

94
Oracle Character Datatypes
  • NVARCHAR2(size) variable length national
    character set character string. Declare the
    maximum length for the column. The maximum length
    is determined by the national character set, but
    the maximum is 4000 bytes for Oracle.
  • VARCHAR2(size) - variable length character
    string. Declare the maximum length for the
    column. The maximum length for Oracle is 4000
    bytes. The actual length of the string stored is
    permitted to be zero because the column is not
    blank padded by Oracle.
  • VARCHAR currently synonymous with VARCHAR2 but
    might be different in the future. Oracle
    recommends you use VARCHAR2 for variable length
    strings.

95
Oracle Number Datatypes
  • NUMBER(p,s) stores zero, positive and negative
    fixed and floating-point numbers.
  • P is precision the total number of digits 1 to
    38
  • S is the scale the number of digits to the
    right of the decimal point 84 to 127 -if a
    value exceeds the scale, Oracle rounds it
  • Specify an integer using these forms
  • NUMBER(p) or NUMBER(p,0)

96
Oracle Number Datatypes
  • Specify a floating-point number using this form
    for a decimal precision of 38 no scale
    specified
  • NUMBER
  • You can also specify a floating-point number
    using the ANSI standard datatype
  • FLOAT decimal precision 38 or binary precision
    126
  • FLOAT(b) binary precision b that can range
    from1 to 126
  • To convert from binary to decimal precision,
    multiply b by 0.30103. To convert from decimal to
    binary precision, multiply the decimal precision
    by 3.32193. The maximum of 126 digits of binary
    precision is roughly equivalent to 38 digits of
    decimal precision.

97
Oracle LONG Datatype
  • LONG columns store variable length character
    strings up to 2 gigabytes. This datatype has many
    of the characteristics of VARCHAR2 columns.
  • Only one LONG type can be in a table.
  • LONG columns cannot be indexed.
  • LONG columns cannot appear is certain parts of
    SQL statements (see the manual for more
    information)

98
Oracle DATE Datatype
  • The DATE datatype stores both date and time
    information century, year, month, day, hour,
    minute, second
  • Oracle default date format is DD-MON-YY (unlike
    DB2 default date format which is YYYY-MM-DD)
  • From the DATE datatype, Date and Time information
    can be represented in both CHAR and NUMBER
    datatypes
  • Use the TO_DATE function to convert a character
    or numeric value to a date value
  • The default time is 120000AM(midnight)

99
Oracle DATE Datatype
  • Use the Oracle date function SYSDATE to get the
    current date and time
  • You can add and subtract days from a date
  • Subtracting one date from another date returns
    the number of days between them
  • Use ADD_MONTHS, MONTHS_BETWEEN functions for
    month operations
  • Because each date contains a time component, most
    results of date operations include a fraction.
    This fraction means a portion of one day. 1.5
    days is 36 hours

100
RAW and LONG RAW Datatypes
  • These two datatypes store data that is not to be
    interpreted by Oracle. They are intended for
    binary data or byte strings. The Oracle utilities
    do not perform character conversion when
    transmitting these types.
  • RAW(size) This type is a variable-length
    datatype like the VARCHAR2 character datatype
  • LONG RAW Used to store graphics, sound,
    documents, or arrays of binary data.
  • When Oracle automatically converts these types to
    and from CHAR data, the binary data is
    represented in hexadecimal form with one
    hexadecimal character representing every four
    bits of RAW data.
  • You can index RAW data, but not LONG RAW data

101
Large Object Datatypes
  • Oracle internal LOB datatypes
  • BLOB (stores unstructured binary large objects
    up to 4 gigabytes full transactional support)
  • CLOB (stores single-byte character large object
    data up to 4 gigabytes of character data full
    transactional support)
  • NCLOB (stores fixed-width, multibyte national
    character set character data up to 4 gigabytes
    full transactional support)
  • External LOB datatypes
  • BFILE (points to a binary file on the servers
    file system that can be up to 4 gigabytes)

102
Large Object Datatypes
  • These large object types can story large and
    unstructured data such as text, image, video, and
    spatial data up to 4 gigabytes in size.
  • Selecting a LOB from a table actually returns the
    LOBs locator and not the entire LOB value
  • See the Oracle8 Application developers Guide for
    more information

103
Oracle ROWID Datatype
  • The pseudocolumn ROWID contains hexadecimal
    strings representing the rows address in the
    database this is the fastest way to access a
    single row
  • This value should not be used as a tables
    primary key because the value could change if the
    table was exported / imported or the row was
    deleted / re-inserted
  • The format of the restricted ROWID is
    block.row.file
  • Block a hexadecimal string identifying the data
    block of the datafile containing the row
  • Row a four digit hexadecimal string identifying
    the row in the data block. The first row of the
    block has a digit of 0.
  • File a hexadecimal string identifying the
    database file containing the row. The first
    datafile has the number 1.

104
Oracle ROWID Datatype
  • The Oracle8 Extended ROWID adds a data object
    number to the format, representing the database
    segment.
  • Oracle8 added more ROWID functions and views for
    the extended ROWID value
  • You can select ROWID, but you cannot insert,
    update, or delete a value of the ROWID
    pseudocolumn

105
Oracle ROWNUM Datatype
  • The pseudocolumn ROWNUM returns a number
    indicating the order in which Oracle selects the
    row from a table or a set of joined rows.
  • The first row selected has a ROWNUM of 1, the
    second 2, and so on.
  • You can use ROWNUM to limit the number of rows
    returned by a query
  • SELECT FROM table WHERE ROWNUM lt 10
  • But you cant use the gt because the conditions
    testing for ROWNUM values greater than a positive
    integer are always false this query returns no
    rows
  • SELECT FROM table WHERE ROWNUM gt 10

106
Chapter 14 - Security
  • Users
  • Privileges
  • Roles
  • Synonyms

107
Maintaining Access to the Tables
  • GRANT - To allow others to share your table, use
    the GRANT statement.
  • REVOKE - The REVOKE statement takes away
    someone's privileges.
  • ALTER - Use the SQL statement, ALTER, to add a
    column to a table.

108
Chapter 16 SQL Optimization
  • Optimizer
  • The Explain Plan
  • Use Analyze to Collect Statistics

109
Oracle Analyze
  • DBA runs the Analyze command with compute
    statistics frequently on production tables
  • Create a plan_table in your schema using
    _at_c\oracle\ora81\rdbms\admin\utlxplan.sql
  • Then turn on tracing in your SQLPlus session
  • set autot trace explain ? without result set
  • set autot on ? with result set
  • set autot off ? turn it off when you are done

110
To Improve SQL Performance
  • You should develop SQL statements for each
    specific situation and avoid using a generic
    sql statements
  • Avoid NOT logic
  • Use EXISTS instead of IN
  • Avoid functions on indexed columns in where
    clauses
  • Use composite indexes properly (be sure to
    reference the leading column of the index)
  • Change predicates to used indexed columns
  • Create new indexes where needed
  • Use DISTINCT as little as possible, because it
    forces Oracle to perform a Sort operation. Try a
    group by on indexed columns instead.

111
To Improve SQL Performance
  • When coding JOIN statements
  • Construct where clauses that use small tables as
    driving tables.
  • Filter operations on single tables belong before
    join operations.
  • Construct join statements with equality
    comparisons and AND operations (inner join where
    table.column table.column )

112
To Improve SQL Performance
  • When coding JOIN statements(cont.)
  • Avoid full table scans use explain plan to
    devise join statements that utilize indexes
  • Use indexes from driving tables that are small
    (left table)
  • Set up join order to discard most rows early (the
    most selective operations should occur first)

113
Oracle SQL
  • Relational Concepts / Introduction to SQL
  • Oracle Training Database
  • Oracle Data Dictionary
  • Use SQLPlus Worksheet
  • Oracle Functions
  • Number Functions
  • Character Functions
  • Date Functions
  • Conversion Functions
  • Coding SQL Predicates
  • Grouping Data

114
Oracle SQL
  • Decode
  • Combining Data from Multiple Tables
  • Coding Subqueries
  • Data Types Sequence
  • Oracle Tables
  • Create a Table from another Table
  • Insert, Update and Delete Rows
  • Drop a Table
  • Oracle Performance Considerations
Write a Comment
User Comments (0)
About PowerShow.com