Description

# FREE complete set of course materials
Who Should Attend?

Application Developers
Business Intelligence
Developer
Database Administrators
Database Designers
End Users
Forms Developer
PL/SQL Developer
Portal Developer

Course Pre-requisites

Familiarity with Data Processing Concepts and Techniques Ability to use a graphical user interface (GUI)

What will you learn?

# Use SQL Statements to retrieve data from tables
# Create and Manage Tables Employ SQL functions to generate and retrieve
# customized data Run data manipulation statements (DML) to update data in the Oracle Database 10g
# Control privileges at the object and system level Use grouping operations to create reports
# Manage Schema Objects Search data using Advanced Sub-queries, and retrieve hierarchical data
# Manipulate data in the Oracle database by using sub-queries
# Use Regular Expression support

Course Contents

Oracle Database 10g: SQL Fundamentals I

Introduction
List the Oracle Database 10g Main Features
An Overview of: components, internet platform, app server and developer suite
Describe Relational and Object Relational Database Designs
Review the System Development Life Cycle
Define the term Data Models
Describe different means of Sorting Data
Show how Multiple Tables can be related
Describe how SQL Communicates to the Database

Writing SQL SELECT statements
Define projection, selection, and join terminology
Review the basic SQL SELECT statement syntax
Select all columns using a wildcard notation from a table
State simple rules and guidelines for writing SQL statements
Write a query containing the arithmetic operators
Use aliases to customize column headings
Create a character expression with the concatenation operator
Use the quote operator q to control text strings containing the quote character

Using the iSQL*Plus Environment
SQL statements versus iSQL*Plus commands
Explaining the iSQL*Plus interface
Interacting with script files
Setting iSQL*Plus Preferences

Restricting Data
Limit Rows Using a Selection
Use the WHERE Clause
List the main Comparison Conditions
Use the LIKE Condition to Compare Literal Values
List the Logical Conditions AND, OR, NOT
Use Multiple Conditions in the WHERE clause
Describe the Rules of Precedence

Sorting Data
Sort Rows with the ORDER BY Clause
Use the && Substitution Variable
Use the iSQL*Plus DEFINE Command
Use the VERIFY Command

SQL Functions
Differentiate between Single Row and Multiple Row SQL Functions
Categorize the Character Functions into Case Manipulation and Character Manipulation types
Explain the Numeric Functions ROUND, TRUNC, and MOD
List the Rules for Applying the Arithmetic Operators on Dates
Use the Arithmetic Operators with Dates in the SELECT Clause
Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
Explain Implicit and Explicit conversion
Nest Functions to Perform Multiple Tasks in One Statement

Conditional Expressions
Use the CASE Expression
Explain the DECODE Expression

Aggregating Data Using Group Functions
Categorize the Types of Group Functions
Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
Utilize the DISTINCT Keyword with the Group Functions
Describe how Nulls are handled with the Group Functions
Create Groups of Data with the GROUP BY Clause
Group Data by more than one column
Avoid Illegal Queries with the Group Functions
Exclude Groups of Data with the HAVING Clause

Displaying Data from Multiple Tables
Identify Types of Joins
Retrieve Records with Natural Joins
Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
Create a Join with the USING clause to identify specific columns between tables
Use the ON clause to specify arbitrary conditions or specify columns to Join
Create a Three-way join with the ON clause to retrieve information from 3 tables
List the Types of Outer Joins LEFT, RIGHT, and FULL
Generating a Cartesian Product

Using Subqueries
Use a sub query to solve a problem
Identify where sub queries can be placed in a SELECT statement
Describe the types of sub queries (single row, multiple row)
Show the single row sub query operators
Use the group functions in a sub query
Identify illegal statements with sub queries
Show the multiple row sub query operators
Explain how null values are handled in sub queries

Using the Set Operators
Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
Describe the INTERSECT operator
Use the INTERSECT operator
Explain the MINUS operator
Use the MINUS operator
List the SET operator guidelines
Order results when using the UNION operator

Inserting and Updating Data
Write INSERT statements to add rows to a table
Insert Special Values
Copy Rows from Another Table
Update Rows in a Table

Deleting Data
Use DELETE statements to remove rows from a table
Delete Rows Based on Another Table
Describe the TRUNCATE Statement

Database Transactions
Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
Show how Read Consistency works

Using DDL Statements
List the main database objects
Identify the Naming Rules
Display the basic Syntax for Creating a Table
Show the DEFAULT option
List the Data Types that are available for Columns

Managing Tables
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query
Describe the ALTER TABLE functionality
Remove a table with the DROP statement

Creating Other Schema Objects
Categorize simple and complex views and compare them
Create a view
Retrieve data from a view
Explain a read-only view
List the rules for performing DML on complex views
Create a sequence
List the basic rules for when to create and not create an index
Create a synonym

Managing Objects with Data Dictionary Views
Describe the structure of each of the dictionary views
List the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views on the schema objects
Use the COMMENT command to document objects

Appendices
Oracle Join Syntax
Using SQL*Plus
Using SQL Developer
Additional Practices

Oracle Database 10g: SQL Fundamentals II

Controlling User Access
Controlling User Access
System versus Objects Privileges
Using Roles to define user groups
Changing Your Password
Granting Object Privileges
Confirming Privileges Granted
Revoking Object Privileges
Using Database Links

Manage Schema Objects
Using the ALTER TABLE statement
Adding a Column
Modifying a Column
Dropping a Column, Set Column UNUSED
Adding, Enabling and Disabling Constraints
Creating Function-Based Indexes
Performing FLASHBACK operations
External Tables

Manipulating Large Data Sets
Using the MERGE Statement
Performing DML with Sub queries
Performing DML with a RETURNING Clause
Overview of Multi-table INSERT Statements
Tracking Changes in DML

Generating Reports by Grouping Related Data
Overview of GROUP BY Clause
Overview of Having Clause
Aggregating data with ROLLUP and CUBE Operators
Determine subtotal groups using GROUPING Functions
Compute multiple groupings with GROUPING SETS
Define levels of aggregation with Composite Columns
Create combinations with Concatenated Groupings

Managing Data in Different Time Zones
Time Zones
Using date-time functions
Identifying TIMESTAMP Data Types
Differentiating between DATE and TIMESTAMP
Performing Conversion Operations

Searching Data Using Advanced Sub queries
Sub query Overview
Using a Sub query
Comparing several columns using Multiple-Column Sub queries
Defining a Data source Using a Sub query in the FROM Clause
Returning one Value using Scalar Sub query Expressions
Performing ROW by-row processing with Correlated Sub queries
Reusing query blocks using the WITH Clause

Hierarchical Retrieval
Sample Data from the EMPLOYEES Table
The Tree Structure of Employee data
Hierarchical Queries
Ranking Rows with LEVEL
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches with the WHERE and CONNECT BY clauses

Regular Expression Support
Regular Expression Support Overview
Describing simple and complex patterns for searching and manipulating data

Appendices
Writing Advanced Scripts
Oracle Architectural Components
Using SQL Developer
Additional Practices

download Here

password tactools.org

http://vip-file.com/download/e87f2d13393/SQL-fundamentals-complete.rar.html

http://rapidshare.com/files/227501533/SQL_20fundamentals_20complete.rar

[This hidden content is only available for our VIP member]. Become VIP Member NOW

Similar Posts:

• CBT Nuggets Exam Pack 70-451 Microsoft SQL Server 2008 Designing Database Solutions And Data Access
• Professional Oracle Programming (Programmer to Programmer)
• CBT Nuggets SQL Fundamentals / SQL 2000 Implementation/Design
• SQL In A Nutshell, 2nd Edition
• Lynda.com – SQL Essential Training