After completing this lesson, you should be able to do the following:

• List the features of Oracle10g
• Discuss the theoretical and physical aspects of a relational database
• Describe the Oracle implementation of the RDBMS and ORDBMS
• Identify the major structural components of Oracle Database 10g
• Retrieve row and column data from tables with the SELECT statement
• Create reports of sorted and restricted data
• Employ SQL functions to generate and retrieve customized data
• Run data manipulation language (DML) statements to update data in Oracle Database10g
• Obtain metadata by querying the dictionary views
• Use advanced SQL data retrieval techniques to retrieve data from database tables
• Apply advanced techniques in a practice that simulates real life
• In this course, you will use advanced SQL data retrieval techniques such as:
• Datetime functions
• ROLLUP, CUBE operators, and GROUPING SETS
• Hierarchical queries
• Correlated subqueries
• Multitable inserts
• Merge operation
• External tables
• Regular expression usage

В программе курса:

• Basic SELECT Statement.

Selecting All Columns, Selecting Specific Columns, Arithmetic Expressions, Defining a Null Value, Null Values in Arithmetic Expressions, Defining a Column Alias, Concatenation Operator, Using Literal Character Strings, Alternative Quote (q) Operator, SQL and iSQL*Plus Interaction, Logging In to iSQL*Plus, iSQL*Plus Environment, Displaying Table Structure, Interacting with Script Files, iSQL*Plus History Page, Setting iSQL*Plus Preferences.

• Restricting and Sorting Data.

Limiting Rows Using a Selection, Using the WHERE Clause, Comparison Conditions, Using the BETWEEN Condition, Using the IN Condition, Using the LIKE Condition, Using the NULL Conditions, Logical Conditions, Приоритеты операций, Sorting, Substitution Variables.

•  Specifying Column Names, Expressions, and Text.

Specifying Column Names, Expressions, and Text. Using the iSQL*Plus DEFINE Command. Using the VERIFY Command.

• Using Single-Row Functions to Customize Output.

SQL Functions. Single-Row Functions. Character Functions. Case-Manipulation Functions. Character-Manipulation Functions. Number Functions. Working with Dates. Arithmetic with Dates. Date Functions. Conversion Functions. Implicit Data Type Conversion. Explicit Data Type Conversion. Elements of the Date Format Model. Using the TO_CHAR Function with Dates. Using the TO_CHAR Function with Numbers. Using the TO_NUMBER and TO_DATE Functions. RR Date Format. Nesting Functions. General Functions. Using the NVL Function. Using the NVL2 Function.

• Reporting Aggregated DataUsing the Group Functions.

Types of Group Functions. Group Functions: Syntax. Using the AVG and SUM Functions. Using the MIN and MAX Functions. Using the COUNT Function. Using the DISTINCT Keyword. Creating Groups of Data. Using the GROUP BY Clause. Using the GROUP BY Clause on Multiple Columns. Restricting Group Results. Nesting Group Functions.

• Displaying Data from Multiple Tables.

Obtaining Data from Multiple Tables. Types of Joins. Joining Tables Using SQL:1999 Syntax. Creating Natural Joins. Creating Joins with the USING Clause. Creating Joins with the ON Clause. Self-Joins Using the ON Clause. Applying Additional Conditionsto a Join. Creating Three-Way Joins with the ON Clause. Non-Equijoins. Outer Joins. LEFT OUTER JOIN. RIGHT OUTER JOIN. FULL OUTER JOIN. Cartesian Products. Creating Cross Joins.

• Using Subqueries to Solve Queries.

Using a Subquery. Types of Subqueries. Single-Row Subqueries. Using Group Functions in a Subquery. The HAVING Clause with Subqueries. Multiple-Row Subqueries. Using the ANY Operator in Multiple-Row  Subqueries. Using the ALL Operatorin Multiple-Row  Subqueries. Null Values in a Subquery

• Using the Set Operators.

Set Operators. UNION Operator. UNION ALL Operator. INTERSECT Operator. MINUS Operator. Controlling the Order of Rows.

• Data Manipulation Language.

Inserting a New Row to a Table. Inserting Rows with Null Values. Inserting Special Values. Inserting Specific Date Values. Updating Rows in a Table. Updating Two Columns with a Subquery. Updating Rows Based on Another Table. Deleting Rows from a Table. Deleting Rows Based on Another Table. TRUNCATE Statement. Using a Subquery in an INSERT Statement.

• Database Transactions.

Database Transactions. Advantages of COMMIT and ROLLBACK Statements. Controlling Transactions. Rolling Back Changes to a Marker. Implicit Transaction Processing. State of the Data Before COMMIT or ROLLBACK. State of the Data After COMMIT. State of the Data After ROLLBACK. Implementation of Read Consistency.

• Using DDL Statements to Create and Manage Tables.

Database Objects. Naming Rules. CREATE TABLE Statement. Referencing Another User’s Tables. DEFAULT Option. Creating Tables. Data Types. Including Constraints. Defining Constraints. NOT NULL Constraint. UNIQUE Constraint. PRIMARY KEY Constraint. FOREIGN KEY Constraint. CHECK Constraint. Creating a Tableby Using a Subquery. ALTER TABLE Statement. Dropping a Table

• Creating Other Schema Objects.

Database Objects. Creating a View. Simple Views and Complex Views. Retrieving Data from a View. Modifying a View. Creating a Complex View. Rules for Performing DML Operations on a View. Using the WITH CHECK OPTION Clause. Removing a View. Creating a Sequence. NEXTVAL and CURRVAL Pseudocolumns. Using a Sequence. Caching Sequence Values. Modifying a Sequence. Creating an Index. Removing an Index. Creating and Removing Synonyms.

• Managing Objects with Data Dictionary Views.

Data Dictionary Structure. USER_OBJECTS and ALL_OBJECTS Views. Table Information. Column Information. Constraint Information. View Information. Sequence Information. Synonym Information. Adding Comments to a Table.

• Controlling User Access.

Privileges. System Privileges. Creating Users. User System Privileges. Granting System Privileges. What Is a Role? Creating and Granting Privileges to a Role. Object Privileges. Granting Object Privileges. Confirming Privileges Granted. Revoking Object Privileges.

• Manage Schema Objects.

The ALTER TABLE Statement. Adding a Column. Modifying a Column. Dropping a Column. The SET UNUSED Option. Adding a Constraint Syntax. Deferring Constraints. Dropping a Constraint. Disabling Constraints. Enabling Constraints. Cascading Constraints. CREATE INDEX with CREATE TABLE Statement. Function-Based Indexes. Removing an Index. DROP TABLE … PURGE. The FLASHBACK TABLE Statement. External Tables. Creating a Directory for the External Table. Creating an External Table. Creating an External Table Using ORACLE_LOADER. Querying External Tables.

• Manipulating Large Data Sets.

Using Subqueries to Manipulate Data. Copying Rows from Another Table. Inserting Using a Subquery as a Target. Retrieving Data with a Subquery as Source. Updating Two Columns with a Subquery. Updating Rows Based on Another Table. Deleting Rows Based on Another Table. Using the WITH CHECK OPTION Keyword on DML Statements. Using Explicit Default Values. Overview of Multitable INSERT Statements. Types of Multitable INSERT Statements. Multitable INSERT Statements. Unconditional INSERT ALL. Conditional INSERT ALL. Conditional INSERT FIRST. Pivoting INSERT. The MERGE Statement Syntax. Tracking Changes in Data. The VERSIONS BETWEEN Clause.

• Generating Reports by Grouping Related Data

Review of Group Functions. GROUP BY with ROLLUP and CUBE Operators. ROLLUP Operator. CUBE Operator. GROUPING Function. GROUPING SETS. Composite Columns. Concatenated Groupings.

• Managing Data in Different Time Zones

Time Zones. TIME_ZONE Session Parameter. CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP. CURRENT_DATE. CURRENT_TIMESTAMP. LOCALTIMESTAMP. DBTIMEZONE and SESSIONTIMEZONE. TIMESTAMP Data Type. TIMESTAMP WITH TIME ZONE Data Type. TIMESTAMP WITH LOCAL TIMEZONE. INTERVAL Data Types. INTERVAL YEAR TO MONTH Data Type. NTERVAL DAY TO SECOND Data Type. EXTRACT. TZ_OFFSET. TIMESTAMP Conversion Using FROM_TZ. Converting to TIMESTAMP Using TO_TIMESTAMP and TO_TIMESTAMP_TZ. Time Interval Conversion with TO_YMINTERVAL.

• Retrieving Data Using Subqueries.

Multiple-Column Subqueries. Pairwise Comparison Subquery. Nonpairwise Comparison Subquery. Scalar Subquery Expressions. Correlated Subqueries. Using the EXISTS Operator. Correlated UPDATE. Correlated DELETE. The WITH Clause. Hierarchical Retrieval. Natural Tree Structure. Hierarchical Queries. Walking the Tree: From the Bottom Up. Walking the Tree: From the Top Down. Ranking Rows with the LEVEL Pseudocolumn. Formatting Hierarchical Reports Using LEVEL and LPAD. Pruning Branches.

• Regular Expression Support.

Meta Characters. Using Meta Characters. Regular Expression Functions. The REGEXP Function Syntax. Checking the Presence of a Pattern. Replacing Patterns.