Lesson 1

Welcome to the first course in the Introduction to Oracle Certification Series, Extensions for SQL. As the name implies, this series focuses on preparing you for certification in Oracle.
During the dotcom era, Oracle 8 introduced several extensions to the SQL standard, enhancing its functionality beyond what was available in the base SQL implementations at the time. These extensions were key in making Oracle 8 more powerful, especially in handling advanced database requirements. Some of the main extensions included:

1. Object-Relational Features: Oracle 8 added object-oriented capabilities to SQL, which were not part of the SQL-92 standard at the time. This included support for user-defined types (UDTs), object references, and collections (arrays and nested tables). These allowed users to model real-world entities directly in the database.
2. PL/SQL Enhancements: Oracle extended SQL through its proprietary procedural language, PL/SQL. This enabled developers to write procedures, functions, and triggers, providing more control over database transactions and improving SQL’s basic procedural limitations.
3. Partitioning: Oracle 8 introduced table partitioning, which allowed very large tables to be broken down into smaller, more manageable pieces. This feature provided extensions to SQL for managing and querying these partitions.
4. Advanced SQL Joins: While the SQL-92 standard supported basic join operations, Oracle 8 introduced enhancements such as outer joins (using the `(+ )` operator) to simplify more complex queries. This extension helped users handle scenarios like retrieving unmatched rows in a more concise manner than the SQL standard required.
5. Hierarchical Queries: Oracle 8 provided SQL extensions like `CONNECT BY` and `START WITH` to support hierarchical queries, which allowed users to retrieve data in a tree structure. These were non-standard SQL extensions at the time but became key in managing data with parent-child relationships.
6. Index-Organized Tables (IOTs): This was a performance-related extension, where Oracle allowed tables to be stored as indexes, making query lookups faster. This feature extended the typical SQL table storage model.
7. Materialized Views: Oracle 8 introduced materialized views, allowing users to store the results of complex queries physically. This extended the SQL capability for improving performance by precomputing and storing query results, especially in data warehousing applications.
8. Sequences and Synonyms: Oracle extended SQL with the use of `SEQUENCE` objects to generate unique values, typically for primary keys. `SYNONYMS` were also introduced to create aliases for database objects, making it easier to access them without needing to reference the full schema path.

These extensions were key in differentiating Oracle’s database offering during the highly competitive dotcom era, addressing complex business requirements that standard SQL alone could not handle.

The information covered in this series helps you get started on the Oracle Certified Database Administrator and/or Certified Application Developer tracks. Another important goal of this series is to enable you

1. to create advanced SQL queries,
2. write intermediate PL/SQL blocks, and
3. familiarize you with Oracle's unique SQL extensions.

Oracle SQL Extensions are a set of features that go beyond the ANSI/ISO Standard SQL specification. These extensions provide additional functionality and performance optimizations that are specific to the Oracle Database. Oracle SQL Extensions can be divided into two broad categories:

• Data type extensions: Oracle supports a number of data types that are not defined in the Standard SQL specification, such as the INTERVAL, ROWID, and XMLType data types.
• Feature extensions: Oracle supports a number of features that are not defined in the Standard SQL specification, such as MERGE statements, regular expression matching, and advanced windowing functions.

Here are some specific examples of Oracle SQL Extensions:

1. Data type extensions:
   • INTERVAL data type: This data type is used to store and manipulate time intervals.
   • ROWID data type: This data type is used to store the unique identifier of a row in a table.
   • XMLType data type: This data type is used to store and manipulate XML data.
2. Feature extensions:
   • MERGE statement: This statement is used to update or insert data into a table in a single operation.
   • Regular expression matching: Oracle supports a number of regular expression functions that can be used to search and manipulate text data.
   • Advanced windowing functions: Oracle supports a number of advanced windowing functions that can be used to perform complex aggregation and analysis on data.

• Increased functionality: Oracle SQL Extensions provide additional functionality that is not available in Standard SQL. This can make it possible to write more efficient and concise code.
• Improved performance: Oracle SQL Extensions can be used to optimize the performance of queries and other database operations.
• Portability: Oracle SQL Extensions are supported by other Oracle products, such as Oracle Real Application Clusters (RAC) and Oracle Exadata.

However, it is important to note that Oracle SQL Extensions are not portable to other database vendors. Therefore, if you are developing applications that need to be portable to other database platforms, you should avoid using Oracle SQL Extensions. To identify the use of Oracle extensions to Entry SQL-92 in your embedded SQL programs, you can use Oracle's FIPS Flagger. The FIPS Flagger is part of the Oracle precompilers and the SQL*Module compiler. The FIPS Flagger can also be enabled in SQL*Plus by using ALTER SESSION SET FLAGGER = ENTRY .

This course covers extensions to SQL. This means that the course is especially designed to show you how to use Oracle's own SQL extensions within queries and other SQL commands. By the end of this course, you will know how to:

1. Use advanced SQL query structures such as correlated sub-queries
2. Use Oracle's extensions to SQL
3. Write SQL to create Oracle tables, views, and indexes and the parameters used to create those structures in Oracle
4. Use SQL to modify or remove existing table structures and constraints
5. Write SQL to add, change, or delete rows in tables
6. Use SQL*Plus (Oracle's SQL programming environment) to create SQL command files, including commands to set titles, page breaks, and customized column headings for your queries

In this course, you will learn and practice SQL skills with many kinds of applets, including Tooltips, SlideShow, and simulations. Extensions for SQL is the first of three courses in the Introduction to Oracle Certification Series. In the next lesson, you will learn about prerequisites to this course.