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)



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



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)



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!


Web web site