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