On-line training course catalogue
Introduction to Oracle9i: SQL
Type of course: Classroom
| Fee | £2200 |
|---|---|
| Days | 5 |
| Course code | OR9ISQL |
We currently do not have public dates scheduled for this course.
If you have a requirement for this course, please contact us for the latest course information.
Print this page | Download as Word document |
Overview
Please note that Oracle courses running at London training centres are subject to a 5% surcharge.
This course offers students an extensive introduction to data server technology. The class covers the concepts of both relational and object relational databases and the powerful SQL programming language. Students are taught to create and maintain database objects and to store, retrieve, and manipulate data. Students learn to retrieve data by using advanced techniques such as ROLLUP, CUBE, set operators, and hierarchical retrieval. They also learn to write SQL and SQL*Plus script files using the iSQL*Plus tool to generate report-like output. Demonstrations and hands-on practice reinforce the fundamental concepts.
Leads to certification
Prerequisites
Familiarity with data processing concepts and techniques
Suggested Prerequisites:
- Ability to use a graphical user interface (GUI)
Outline
- Describing the Life Cycle Development Phases
- Defining a Database
- Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database
- Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database
- Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)
- Describing How SQL Is Used in the Oracle Product Set
- Writing a Basic SQL Statement
- Describing the SQL Select Capabilities
- Executing a Basic Select Statement with the Mandatory Clauses
- Differentiating Between SQL and iSQL*Plus Commands
- Restricting and Sorting Data
- Limiting the Rows Retrieved by a Query
- Sorting the Rows Retrieved by a Query
- Single Row Functions
- Describing Various Types of Functions Available in SQL
- Using a Variety of Character, Number, and Date Functions in SELECT Statements
- Explaining What the Conversion Functions Are and How They Are Used
- Using Control Statements
- Displaying Data from Multiple Tables
- Writing SELECT Statements to Access Data from More Than One Table
- Describing the Cartesian Product
- Describing and Using the Different Types of Joins
- Writing Joins Using the Tips Provided
- Aggregating Data by Using Group Functions
- Identifying the Different Group Functions Available
- Explaining the Use of Group Functions
- Grouping Data by Using the GROUP BY Clause
- Writing Subqueries
- Describing the Types of Problems That Subqueries Can Solve
- Describing Subqueries
- Listing the Types of Subqueries
- Writing Single-Row and Multi-Row Subqueries
- Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved
- Producing Readable Output with iSQL*Plus
- Producing Queries That Require an Input Variable
- Customizing the iSQL*Plus Environment
- Producing More Readable Output
- Creating and Executing Script Files
- Manipulating Data
- Describing Each Data Manipulation Language (DML) Command
- Inserting Rows into a Table
- Updating Rows in a Table
- Deleting Rows from a Table
- Merging Rows into a Table
- Controlling Transactions
- Describing Transaction Processing
- Describing Read Consistency and Implicit and Explicit Locking
- Creating and Managing Tables
- Describing the Main Database Objects
- Creating Tables
- Describing the Oracle Data Types
- Altering Table Definitions
- Dropping, Renaming, and Truncating Tables
- Including Constraints
- Describing Constraints
- Creating and Maintaining Constraints
- Creating Views
- Describing Views and Their Uses
- Creating a View
- Retrieving Data by Means of a View
- Inserting, Updating, and Deleting Data Through Views
- Dropping Views
- Altering the Definition of a View
- Inline Views
- Top ‘N’ Analysis
- Other Database Objects
- Creating, Maintaining, and Using Sequences
- Creating and Maintaining Indexes
- Creating Private and Public Synonyms
- Controlling User Access
- Understanding the Concepts of Users, Roles, and Privileges
- Granting and Revoking Object Privileges
- Creating Roles and Granting Privileges to Roles
- Creating Synonyms for Ease of Table Access
- SQL Workshop
- Applying Techniques Learned in This Course
- Preparing for Future Oracle Courses
- Using Set Operators
- Describing the Set Operators
- Obeying the Set Operators Rules and Guidelines
- Using a Set Operator to Combine Multiple Queries into a Single Subquery
- Controlling the Order of Rows Returned
- Oracle9i Single Row Functions
- Using DATETIME Functions
- Using the NVL2 Function to Handle NULL Values
- Enhancements to the GROUP BY Clause
- Using ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
- Using CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
- Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
- Using GROUPING SETS to Produce a Single Result Set That Is Equivalent to a UNION ALL Approach
- Using the WITH Clause
- Advanced Subqueries
- Updating and Deleting Rows by Using Correlated Subqueries
- Writing a Multicolumn Subquery
- Describing and Explaining the Behavior of Subqueries When Null Values Are Retrieved
- Writing a Subquery in a FROM Clause
- Describing the Types of Problems That Can Be Solved with a Correlated Subquery
- Describing a Correlated Subquery
- Writing Correlated Subqueries
- Using the EXISTS and NOT EXISTS Operators
- Hierarchical Retrieval
- Discussing the Benefits of the Hierarchical Query
- Ordering the Rows Retrieved by a Query in a Hierarchical Manner
- Formatting Hierarchical Data so That It Is Easy to Read
- Excluding Branches from the Tree Structure
- Extensions to DDL and DML Statements
- Discussing Multitable Inserts
- Creating and Using External Tables
- Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint
- Writing Scripts to Generate Scripts
- Describing the Types of Problems That are Solved by Writing SQL Scripts That Generate Other SQL Scripts
- Writing and Executing Scripts that Generate Scripts with Commands to Create and Drop Tables
- Writing and Executing a Script that Generates a Script of INSERT INTO Commands
Related Certifications
This course forms part of the following certification track(s):
- Oracle9i Database Administrator Certified Associate
- Oracle9i PL/SQL Developer Certified Associate
- Oracle 10g PL/SQL Developer Certified Associate
Link to this page: http://www.xpertise.co.uk/OR9ISQL
Xpertise – Introduction to Oracle9i: SQL training courses
Xpertise provides Introduction to Oracle9i: SQL training courses from 7 UK locations: in Altrincham Manchester, Hinckley East Midlands, Leeds Yorkshire, London King William Street, Maidenhead Berkshire, Malmesbury Wiltshire and Washington Tyne & Wear – and at 44 partner locations nationwide.
Xpertise Training reserves the right to improve the specification and format of its courses for the benefit of its customers without notice to the customer.