iSelfSchooling - SQL Table of contents of Online Oracle Training

iSelfSchooling.com Since 1999 References | Search more | Oracle Syntax | Free Online Oracle Training

Home .Services Login Start Learning Certification . .Share your BELIEF(s)...

. Online Accounting     .Copyright & User Agreement   |

    .Vision      .Biography     .Acknowledgement
.Contact Us      .Comments/Suggestions       Email2aFriend    |

Online Oracle Training

Table of contents

SQL -- Table of contents

Introduction 1: Basic Introduction

Elements/Fields/Items; Columns; Records/Rows; Tables; Datatypes; Primary Key; Foreign Key; Logical Data Model; Oracle Tools; DBA vs. Developer

Introduction 2: SQL Introduction

Note on the important definitions to remember

Hands-On 01: Selecting Data

Writing SELECT statements; Performing Arithmetic Equations; Handling NULL Values; Changing Column Headings with Aliases; Putting Columns with Concatenation; The ORDER BY Clause; The WHERE Clause; The EXISTS Clause; The IN Clause; DECODE function

Hands-On 02: Advanced Data Selection

Select Statements That Join Data from More than One Table; Creating Outer Joins; Joining a Table to Itself; Identifying Available Group Functions; Using Group Functions; Using the GROUP BY Clause; Excluding GROUP BY Data with HAVING; Nested Sub queries; Entering Variables at Run Time; Using Runtime Variables

Hands-On 03: Creating the Database objects

Creating a Table with Integrity Constraints; Using Table Naming Conventions; Datatypes and Column Definitions; Indexes Created by Constraints; Creating One table with Data From Another; Available Dictionary Views; Querying the Data Dictionary; Inserting New Rows into a Table; Making Changes to Existing Row Data; Deleting Data from Oracle Database

Hands-On 04: Creating more Database Objects

Adding and Modifying Columns; Dropping Tables; Truncating Tables; Renaming Objects; Viewing Dictionary; Data Dictionary Views; Creating Simple Views; Dropping Views; Manual and Automatic Indexes; Uses for Indexes; Creating Indexes; Removing Indexes; Creating user; Granting System Privileges; Granting and Revoking Object Privileges; Changing Passwords; Alter user; Using Synonyms

Hands-On 05: PL/SQL Command line editing

L command - Lists SQL or PL/SQL from the SQL buffer; I command - Inserts SQL or PL/SQL lines; Del command - Deletes SQL or PL/SQL lines; C command - Changes SQL or PL/SQL lines; SAVE command; GET command

Hands-On 06: PL/SQL Notepad editing

Open Notepad editor; Save the Oracle Buffer; Edit the file; Work with the file

Introduction 07 (Oracle9i: SQL New Features)

The Oracle9i ANSI standard JOIN syntax vs. the original join syntax; The ANSI standard NATURAL JOIN syntax; The ANSI standard CROSS JOIN syntax; The USING clause; The ON clause; The OUTER JOIN clause; RIGHT OUTER JOIN vs. original Oracle syntax; LEFT OUTER JOIN vs. original Oracle syntax; FULL OUTER JOIN

Introduction 08 (Oracle9i: SQL New Features)

The (WITH SUMMARY AS) statement; The use of multiple columns in the WHERE clause; The (inline view); The MERGE statement

Hands-On 09 (Materialized Views – The view that stores data)

Creating a materialized view log; Listing the snapshot logs' view; MLOG$_EMP; Creating a materialized view; Granting materialized view privilege; Listing the user's materialized views; Executing the REFRESH procedure; Using DBMS_SNAPSHOT package; Executing the REMOVE procedure; Listing the materialized view in the job’s queue; Deleting the materialized view’s job; Dropping the created Materialized view; Revoking the materialized view privilege

Hands-On 10 (The BREAK ON clause)

REPHEADER; REPFOOTER; COLUMN <col> HEADING <hdr> FORMAT a20; BREAK ON <col> SKIP n; COMPUTE SUM OF <col> ON <col>; SPOOL; HOST; CLEAR BUFFER; CLEAR COLUMNS; CLEAR COMPUTE

Hands-On 11 (Troubleshooting – UNION; INTERSET; and MINUS statements)

UNION; INTERSECT; MINUS

Hands-On 12 (Troubleshooting Deleting duplicated records)

Creating a table with lots of duplicated records; Writing a simple delete (DML) statement to remove duplicated records; Verifying the removal of the duplicated records; Dropping the duplicated table

Hands-On 13 (Data Partitioning)

Creating the OS level folders; Creating a tablespace; Granting the CREATE TABLESPACE; Granting the DROP TABLESPACE; Creating a range-based partitioning table; Inserting records into the partition table; Analyzing the partition table; Listing the statistics on the partition data table; Compare a regular table with a partition table; Listing the partition table; Creating a local partition index; Analyzing the partition table index; Listing the statistics on the index partition table; Dropping the partition table and all of the tablespaces; Revoking the CREATE TABLESPACE privilege; Removing the folders

Hands-On 14 (Data Warehousing – ROLLUP and CUBE function)

ROLLUP; CUBE

Hands-On 15 (Using Native Dynamic SQL)

Native Dynamic SQL

Hands-On 16 (JAVA Stream)

Checking the JAVA tool installation; Checking the JAVA pool memory usage; Creating a java class; Publishing the JAVA class; Running and testing the JAVA program; Dropping the JAVA SOURCE; Dropping the FUNCTION

Hands-On 17 (Defining Objects and Collection Types)

Creating an object type; Creating a table type using object type; Listing the object types; Creating a nested table using a table type; INSERT INTO tablename VALUES (col; table_object_type(record_object_type())); Listing the nested table; INSERT INTO THE (SELECT col… FROM tablename); SELECT nested_table_id; col1; col2 FROM THE (SELECT col1 FROM tablename); UPDATE THE(SELECT col1 FROM tablename); Adding a unique index to the nested table; Dropping the nested table and types

Good Luck!