The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language. Even professionals experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course.
Course Objectives
This course will cover the following elements:
- Learn to use the SQL99 join syntax
- Perform advanced SQL queries using techniques such as grouping and CUBE operations
- Move beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views, and others
- Understanding basic database security and object privileges
- Using the power set of built in SQL functions
- Protecting the database and extending the basic data model using declarative constraints
- Developing complex SQL plus reports and using SQL plus scripts
Prerequisites
The following are required courses before taking this class:
- Oracle Database 10g Introduction to SQL Part II
Course Benefits
Upon successful completion students will be familiar with the following topics:
- SQL99 join techniques
- Cross joins
- Natural joins
- Inner joins
- Outer joins
- Anti joins
- Using named sub queries
- Enhancing groups with ROLLUP & CUBE
- Using the case expression
- Understand using the case expression
- Understanding SQL functions character
- Understand what are SQL functions
- Understand character functions
- Understand greatest() and least() functions
- Numeric function
- Date function
- Date format function
- Date arithmetic functions
- Database objects and relational views
- Relational views
- Update view data
- Maintain view definitions
- Alter view
- Drop view
- Database objects: data dictionary storage
- Utilize user_update_columns
- Understand the data dictionary structure
- Database objects: indexes
- Understand indexes
- Database objects: other objects
- Create an application schema
- Creating tables
- Define sequences
- Alter sequences
- Drop sequences
- Understand synonyms
- Drop synonyms
- Create schema authorization
- Database objects: security
- User id passwords
- Object security
- Data integrity using constraints
- Not null constraints
- Check constraint
- Unique constraint
- Primary key constraint
- References constraint
- Manage constraints definitions
- Manage constraints
- Renaming & dropping constraints
- Enabling & disabling constraints
- Deferred enforcement
- Set constraints
- Handling constraints exceptions
- Data dictionary storage
- Break
- Compute
- Column
- Ttitle & bittle
- Using SQL *Plus variables
- Define & un-define
- Accept
- Prompt
- Pause