Download D64260GC10_sg1 PDF

TitleD64260GC10_sg1
TagsOracle Database Databases Sql Table (Database) Information Technology Management
File Size1.9 MB
Total Pages290
Table of Contents
                            Cover Page
Table of Contents
Lesson I: Introduction
	Lesson Objectives
	Lesson Agenda
	Course Objectives
	Course Prerequisites
	Course Agenda
	Lesson Agenda
	Tables Used in This Course
	Appendixes Used in This Course
	Development Environments
	Lesson Agenda
	Review of Restricting Data
	Review of Sorting Data
	Review of SQL Functions
	Review of Single-Row Functions
	Review of Types of Group Functions
	Review of Using Subqueries
	Review of Manipulating Data
	Lesson Agenda
	Oracle Database SQL Documentation
	Additional Resources
	Summary
	Practice I: Overview
Lesson 1: Controlling User Access
	Objectives
	Lesson Agenda
	Controlling User Access
	Privileges
	System Privileges
	Creating Users
	User System Privileges
	Granting System Privileges
	Lesson Agenda
	What Is a Role?
	Creating and Granting Privileges to a Role
	Changing Your Password
	Lesson Agenda
	Object Privileges
	Granting Object Privileges
	Passing On Your Privileges
	Confirming Granted Privileges
	Lesson Agenda
	Revoking Object Privileges
	Quiz
	Summary
	Practice 1: Overview
Lesson 2: Managing Schema Objects
	Objectives
	Lesson Agenda
	ALTER TABLE Statement
	ALTER TABLE Statement
	Adding a Column
	Modifying a Column
	Dropping a Column
	SET UNUSED Option
	Lesson Agenda
	Adding a Constraint Syntax
	Adding a Constraint
	ON DELETE Clause
	Deferring Constraints
	Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE
	Dropping a Constraint
	Disabling Constraints
	Enabling Constraints
	Cascading Constraints
	Renaming Table Columns and Constraints
	Lesson Agenda
	Overview of Indexes
	CREATE INDEX with the CREATE TABLE Statement
	Function-Based Indexes
	Removing an Index
	DROP TABLE … PURGE
	Lesson Agenda
	FLASHBACK TABLE Statement
	Using the FLASHBACK TABLE Statement
	Lesson Agenda
	Temporary Tables
	Creating a Temporary Table
	Lesson Agenda
	External Tables
	Creating a Directory for the External Table
	Creating an External Table
	Creating an External Table by Using ORACLE_LOADER
	Querying External Tables
	Creating an External Table by Using ORACLE_DATAPUMP: Example
	Quiz
	Summary
	Practice 2: Overview
Lesson 3: Managing Objects with Data Dictionary Views
	Objectives
	Lesson Agenda
	Data Dictionary
	Data Dictionary Structure
	How to Use the Dictionary Views
	USER_OBJECTS and ALL_OBJECTS Views
	USER_OBJECTS View
	Lesson Agenda
	Table Information
	Column Information
	Constraint Information
	USER_CONSTRAINTS: Example
	Querying USER_CONS_COLUMNS
	Lesson Agenda
	View Information
	Sequence Information
	Confirming Sequences
	Index Information
	USER_INDEXES: Examples
	Querying USER_IND_COLUMNS
	Synonym Information
	Lesson Agenda
	Adding Comments to a Table
	Quiz
	Summary
	Practice 3: Overview
Lesson 4: Manipulating Large Data Sets
	Objectives
	Lesson Agenda
	Using Subqueries to Manipulate Data
	Retrieving Data by Using a Subquery as Source
	Inserting by Using a Subquery as a Target
	Using the WITH CHECK OPTION Keyword on DML Statements
	Lesson Agenda
	Overview of the Explicit Default Feature
	Using Explicit Default Values
	Copying Rows from Another Table
	Lesson Agenda
	Overview of Multitable INSERT Statements
	Types of Multitable INSERT Statements
	Multitable INSERT Statements
	Unconditional INSERT ALL
	Conditional INSERT ALL: Example
	Conditional INSERT ALL
	Conditional INSERT FIRST: Example
	Conditional INSERT FIRST
	Pivoting INSERT
	Lesson Agenda
	MERGE Statement
	MERGE Statement Syntax
	Merging Rows: Example
	Lesson Agenda
	Tracking Changes in Data
	Example of the Flashback Version Query
	VERSIONS BETWEEN Clause
	Quiz
	Summary
	Practice 4: Overview
Lesson 5: Managing Data in Different Time Zones
	Objectives
	Lesson Agenda
	Time Zones
	TIME_ZONE Session Parameter
	CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
	Comparing Date and Time in a Session’s Time Zone
	DBTIMEZONE and SESSIONTIMEZONE
	TIMESTAMP Data Types
	TIMESTAMP Fields
	Difference Between DATE and TIMESTAMP
	Comparing TIMESTAMP Data Types
	Lesson Agenda
	INTERVAL Data Types
	INTERVAL Fields
	INTERVAL YEAR TO MONTH: Example
	INTERVAL DAY TO SECOND Data Type: Example
	Lesson Agenda
	EXTRACT
	TZ_OFFSET
	FROM_TZ
	TO_TIMESTAMP
	TO_YMINTERVAL
	TO_DSINTERVAL
	Daylight Saving Time
	Quiz
	Summary
	Practice 5: Overview
Lesson 6: Retrieving Data by Using Subqueries
	Objectives
	Lesson Agenda
	Multiple-Column Subqueries
	Column Comparisons
	Pairwise Comparison Subquery
	Nonpairwise Comparison Subquery
	Lesson Agenda
	Scalar Subquery Expressions
	Scalar Subqueries: Examples
	Lesson Agenda
	Correlated Subqueries
	Using Correlated Subqueries
	Lesson Agenda
	Using the EXISTS Operator
	Find All Departments That Do Not Have Any Employees
	Correlated UPDATE
	Using Correlated UPDATE
	Correlated DELETE
	Using Correlated DELETE
	Lesson Agenda
	WITH Clause
	WITH Clause: Example
	Recursive WITH Clause
	Recursive WITH Clause: Example
	Quiz
	Summary
	Practice 6: Overview
Lesson 7: Regular Expression Support
	Objectives
	Lesson Agenda
	What Are Regular Expressions?
	Benefits of Using Regular Expressions
	Using the Regular Expressions Functionsand Conditions in SQL and PL/SQL
	Lesson Agenda
	What Are Metacharacters?
	Using Metacharacters with Regular Expressions
	Using Metacharacters with Regular Expressions
	Lesson Agenda
	Regular Expressions Functions and Conditions: Syntax
	Performing a Basic Search by Using the REGEXP_LIKE Condition
	Replacing Patterns by Using the REGEXP_REPLACE Function
	Finding Patterns by Using the REGEXP_INSTR Function
	Extracting Substrings by Using the REGEXP_SUBSTR Function
	Lesson Agenda
	Subexpressions
	Using Subexpressions with Regular Expression Support
	Why Access the nth Subexpression?
	REGEXP_SUBSTR: Example
	Lesson Agenda
	Using the REGEXP_COUNT Function
	Regular Expressions and Check Constraints: Examples
	Quiz
	Summary
	Practice 7: Overview
                        
Document Text Contents
Page 1

Oracle Database: SQL
Fundamentals II

Volume I • Student Guide

D64260GC10

Edition 1.0

January 2010

D64999

O
ra

cl
e

U
ni

ve
rs

ity
a

nd
B

re
za

S
of

tw
ar

e
E

ng
in

ee
rin

g
d.

o.
o

us
e

on
ly

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS
COMPUTER IS STRICTLY PROHIBITED

Page 2

Copyright © 2010, Oracle. All rights reserved.

Disclaimer

This course provides an overview of features and enhancements planned in release
11g. It is intended solely to help you assess the business benefits of upgrading to 11g
and to plan your IT projects.

This course in any form, including its course labs and printed matter, contains
proprietary information that is the exclusive property of Oracle. This course and the
information contained herein may not be disclosed, copied, reproduced, or distributed
to anyone outside Oracle without prior written consent of Oracle. This course and its
contents are not part of your license agreement nor can they be incorporated into any
contractual agreement with Oracle or its subsidiaries or affiliates.

This course is for informational purposes only and is intended solely to assist you in
planning for the implementation and upgrade of the product features described. It is
not a commitment to deliver any material, code, or functionality, and should not be
relied upon in making purchasing decisions. The development, release, and timing of
any features or functionality described in this document remain at the sole discretion
of Oracle.

This document contains proprietary information and is protected by copyright and
other intellectual property laws. You may copy and print this document solely for your
own use in an Oracle training course. The document may not be modified or altered in
any way. Except where your use constitutes "fair use" under copyright law, you may
not use, share, download, upload, copy, print, display, perform, reproduce, publish,
license, post, transmit, or distribute this document in whole or in part without the
express authorization of Oracle.

The information contained in this document is subject to change without notice. If you
find any problems in the document, please report them in writing to: Oracle University,
500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not
warranted to be error-free.

Restricted Rights Notice

If this documentation is delivered to the United States Government or anyone using
the documentation on behalf of the United States Government, the following notice is
applicable:

U.S. GOVERNMENT RIGHTS
The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or
disclose these training materials are restricted by the terms of the applicable Oracle
license agreement and/or the applicable U.S. Government contract.

Trademark Notice

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other
names may be trademarks of their respective owners.

Authors

Chaitanya Koratamaddi

Brian Pottle

Tulika Srivastava

Technical Contributors
and Reviewers

Claire Bennett
Ken Cooper
Yanti Chang
Laszlo Czinkoczki
Burt Demchick
Gerlinde Frenzen
Joel Goodman
Laura Garza
Richard Green
Nancy Greenberg
Akira Kinutani
Wendy Lo
Isabelle Marchand
Timothy Mcglue
Alan Paulson
Manish Pawar
Srinivas Putrevu
Bryan Roberts
Clinton Shaffer
Hilda Simson
Abhishek Singh
Jenny Tsai Smith
James Spiller
Lori Tritz
Lex van der Werff
Marcie Young

Editors
Amitha Narayan
Daniel Milne
Raj Kumar

Graphic Designer

Satish Bettegowda

Publishers

Veena Narasimhan

Pavithran Adka

O
ra

cl
e

U
ni

ve
rs

ity
a

nd
B

re
za

S
of

tw
ar

e
E

ng
in

ee
rin

g
d.

o.
o

us
e

on
ly

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS
COMPUTER IS STRICTLY PROHIBITED

Page 145

Copyright © 2010, Oracle. All rights reserved.

Manipulating Large Data Sets

O
ra

cl
e

U
ni

ve
rs

ity
a

nd
B

re
za

S
of

tw
ar

e
E

ng
in

ee
rin

g
d.

o.
o

us
e

on
ly

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS
COMPUTER IS STRICTLY PROHIBITED

Page 146

Oracle Database: SQL Fundamentals II 4 - 2

Copyright © 2010, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to do the
following:
• Manipulate data by using subqueries
• Specify explicit default values in the INSERT and UPDATE

statements
• Describe the features of multitable INSERTs
• Use the following types of multitable INSERTs:

– Unconditional INSERT
– Pivoting INSERT
– Conditional INSERT ALL
– Conditional INSERT FIRST

• Merge rows in a table
• Track the changes to data over a period of time

Objectives

In this lesson, you learn how to manipulate data in the Oracle database by using subqueries. You
learn how to use the DEFAULT keyword in INSERT and UPDATE statements to identify a
default column value. You also learn about multitable INSERT statements, the MERGE
statement, and tracking changes in the database.

O
ra

cl
e

U
ni

ve
rs

ity
a

nd
B

re
za

S
of

tw
ar

e
E

ng
in

ee
rin

g
d.

o.
o

us
e

on
ly

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS
COMPUTER IS STRICTLY PROHIBITED

Page 289

Oracle Database: SQL Fundamentals II 7 - 27

Copyright © 2010, Oracle. All rights reserved.

Practice 7: Overview

This practice covers using regular expressions functions to do
the following:

• Searching for, replacing, and manipulating data
• Creating a new CONTACTS table and adding a CHECK

constraint to the p_number column to ensure that phone
numbers are entered into the database in a specific
standard format

• Testing the adding of some phone numbers into the
p_number column by using various formats

Practice 7: Overview

In this practice, you use regular expressions functions to search for, replace, and manipulate
data. You also create a new CONTACTS table and add a CHECK constraint to the p_number
column to ensure that phone numbers are entered into the database in a specific standard format.

O
ra

cl
e

U
ni

ve
rs

ity
a

nd
B

re
za

S
of

tw
ar

e
E

ng
in

ee
rin

g
d.

o.
o

us
e

on
ly

THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS
COMPUTER IS STRICTLY PROHIBITED

Similer Documents