Introduction to Oracle9i: SQL
Oracle University provides a downloadable eKit containing the training materials for this course.您将学到什么内容:
This class is applicable to Oracle9i and Oracle8i users. You learn concepts of relational databases, to create database structures and to store, retrieve, and manipulate data. Here you learn to query using Basic SQL Statements, restrict, sort, perform single row functions and group the queried data.
You will write advanced SELECT statements and use advanced techniques such as ROLLUP, CUBE, set operators, and hierarchical retrieval. You will query multiple tables, perform nested queries, implement constraints, use date and time functions, and create sequences and views.
You 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. The SQL Workshop will enhance your learning experience of SQL.
This course counts towards the Hands-on course requirement for the Oracle 9i Database Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses are excellent study and reference tools but DO NOT meet the Hands-on Requirement for certification.
| 时间表/定购 | 培训形式 | 价格 | 持续时间 | 培训课程教材 | 使用说明语言 |
|---|---|---|---|---|---|
| 查看课程时间表 | 教师指导的课堂培训 | CNY 10925 | 5 天 | 英文 | |
| 在线购买 | 自学光盘 | CNY 6140 | 不存在 | 英文 |
注意: 享受更多个性化体验, 我们邀请您到: 登录!
先决条件:
必要的先决条件:
- Familiarity with data processing concepts and techniques
建议具备的先决条件:
- Ability to use a graphical user interface (GUI)
课程目标:
- Describe relational and object relational database concepts
- Retrieve, insert, update, and delete data
- Use the Single row and Group functions
- Retrieve data using additional advanced techniques like using ROLLUP, CUBE, set operators, correlated subqueries and hierarchical queries
- Solve problems by using multicolumn subqueries, subqueries in the from clause of a SELECT statement, correlated subqueries, and scalar subqueries
- Use the iSQL*Plus environment
- Write scripts to generate script files
- Generate reports using iSQL*Plus
- Create and maintain database objects
- Control transactions
- Control data/user access
- Use the Oracle9i Single row functions
- Use GROUPING SETS and the WITH clause
- Use the Oracle9i extensions to DML and DDL statements
- Apply techniques in real life simulation
课程主题:
Introduction
- Describing the Life Cycle Development Phases
- Defining a Relational 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
Oracle 9i Datetime 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
- Multiple-Column Subqueries
- Writing a Subquery in the FROM Clause
- Writing and describing Correlated Subquery
- Using EXISTS and NOT EXISTS Operators
- Updating and Deleting Rows Using Correlated Subqueries
- Using Scalar Subqueries in SQL
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
Oracle9i Extensions to DML and DDL 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