Title: Oracle SQL
1Oracle SQL
- Penny Bowman
- February, 2003
- Using text Oracle SQL Interactive Workbook
2Oracle 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
3Oracle 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
4Chapter 1- SQL and Data
- Lab 1.2 - Student Schema Appendix C
- Lab 1.3 - Referential Integrity and Table
Relationships - Lab 1.4 - SQLPlus Environment
5Relational 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
6Introduction 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)
7Student 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
8While 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
9Data 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
10Chapter 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
11SELECT 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
12SELECT Statement Syntax
- SELECT ALLDISTINCT
- columns AS newname
- FROM table or view AS shortname
- WHERE conditions
- GROUP BY fields
- HAVING conditions
- ORDER BY columninteger ASCDESC
13BETWEEN 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
14IN 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
15LIKE 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
16Compound 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
17NULL 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
18Chapter 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
19Chapter 3 Oracle Number Functions
- Number Functions
- ABS
- SIGN
- MOD
- Round / Trunc
- Arithmetic Operators
Labs 34Ex32, 35Lab321
20Number 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)
21Chapter 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
22Date 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.
23Date 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)
24Date 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
25Conversion 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.
26Conversion 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 )
27Date 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)
28Date 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
29Number Formats for fmt of TO_CHAR
- L
- C
- ,
- .
- V
- EEEE
- RN or rn
- DATE
30SYSDATE 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
31Review 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
32Review 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.
33Other Single-Row Functions
- NVL (expr1, expr2)
- UID
- USER
- USERENV (option) (option can be ENTRYID,
SESSIONID, TERMINAL, LANGUAGE, LABEL)
34Using 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
35Chapter 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
36Decode
- 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.
37Decode 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
38Decode Example
- Example Resultset
- New York Count Texas Count New Jersey Count
- 123 0
74 - (Note Oracle returned 1 row with 3 calculations
included.)
39Decode 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.
-
40Decode 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
-
41Decode 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.
42Decode 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.
43Chapter 5 Aggregate Functions
- Aggregate Functions
- Group By
- Having
Labs 50Ex51, 51Lab511, 52Ex52, 53Lab52
44Grouping 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
45GROUP BY
- SELECT fields FROM tables
- WHERE conditions
- GROUP BY fields
- HAVING conditions
- ORDER BY fields
- GROUP BY is used to summarize the columns
selected.
46GROUP 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
47Group 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)
48Count 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.
49Count 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
50Count 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.
51Chapter 6 - Equijoins
- Two Table Join
- Nulls and Joins
- Cartesian Product
- Joining More than 2 Tables
Labs 54Ex61a, 55Lab611a, 56Lab612,
57Lab613, 58Ex62a, 59Lab621, 60Lab622a2,
61Lab622bc
52JOIN (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
53Chapter 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
54SubQueries 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.
55SubQueries 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.
56SubQuery 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.
57Using 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.
58Not 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.
59Not 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.
60SubQueries 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.
61Chapter 8 Set Operators
- Union
- Union All
- Minus
- Intersect
- Labs
- 71Ex81,
- 72Lab811, 73Ex82,
- 74Lab82
62UNION 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.
63UNION 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
64MINUS
- .
- 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.
65INTERSECT
- .
- 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.
66Chapter 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
67Joining Tables Using Oracle
- JOIN Types
- JOIN (equi or inner)
- OUTER JOINS
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- Joining a table to itself
68Outer 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.
69NVL 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.
70Chapter 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
71Chapter 12 Views, Indexes, and Sequences
- Creating and Modifying Views
- Indexes
- Sequences
72Sequence
- 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.
73Sequence
- 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
74Sequence
- Create SEQUENCE customer_number START WITH
2002100 - INCREMENT BY 10
- NOCACHE
- Select customer_number.nextval from dual
- Select customer_number.currval from dual
75Sequence
- 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
76Chapter 11 Create, Alter, Drop Tables
- Creating Tables
- Common Datatypes
- Creating Tables Based on Other Tables
- Integrity Constraints
- Dropping Tables
- Altering Tables and Manipulating Constraints
77Working 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
78Working 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
79Create 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.
80Create 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'
81Evaluate 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.
82Dropping 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
83Chapter 10 Insert, Update, and Delete
- Manipulating Data and Transaction Control
- Insert
- Update
- Delete
- Truncate
84Insert 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.
85Insert 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.
86Insert 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.
87UPDATE 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.
88UPDATE 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.
89DELETE 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.
90When 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
91When 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.
92Oracle 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
93Oracle 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.
94Oracle 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.
95Oracle 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)
96Oracle 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.
97Oracle 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)
98Oracle 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)
99Oracle 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
100RAW 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
101Large 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)
102Large 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
103Oracle 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.
104Oracle 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
105Oracle 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
106Chapter 14 - Security
- Users
- Privileges
- Roles
- Synonyms
107Maintaining 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.
108Chapter 16 SQL Optimization
- Optimizer
- The Explain Plan
- Use Analyze to Collect Statistics
109Oracle 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
110To 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.
111To 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 )
112To 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)
113Oracle 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
114Oracle 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