Download D64260GC10_sg1 PDF

TitleD64260GC10_sg1
TagsOracle Database Sql Table (Database) Databases 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
                        

Similer Documents