SQL

Introduction

• Describing the Life Cycle Development Phases.
• Defining a Database.
• Discussing the Theoretical Conceptual and Physical Aspects of a Relational Database.
• Describing how a Relational Database Management System 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 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.
• Filtering the groups using the HAVING clause.

Writing Subqueries

• Describing the types of problems that subqueries can solve.
• Describing subqueries.
• Listing the types of subqueries.
• Writing single-row and multiple-row.
• Describing and explaining the behavior of subqueries when NULL values are retrieved.

Producing the readable output with iSQL*Plus

• Producing queries that require an input variable.
• Customizing the iSQL*Plus environment.
• Producing more readable output.
• Creating and executing the script files.

Manipulating the 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 from two or more tables.
• Controlling transactions.
• Describing the transaction processing.
• Describing the read consistency, 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.

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/10g Single Row functions

• Using DATETIME functions.
• Using the NVL2 function to handle NULL values.

Enhancements to the GROUP BY clause

• Using the ROLLUP as an extension to the GROUP BY clause to produce subtotal values.
• Using the CUBE as an extension to the GROUP BY clause to cross-product 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

• Updating and deleting rows by using Correlated Subqueries.
• Writing a multicolumn subquery.
• Describing and explaining the behavior of Subqueries when NULL values are retrieved.
• Writing a subquery in a FROM clause.
• Describing the types of problems that can be solved with a correlated subquery.
• Describing a correlated subquery.
• Writing correlated Subqueries.
• Using the EXISTS and NOT EXISTS operators.

Hierarchical Retrieval

• Discussing the benefits of the Hierarchical Query.
• Ordering the rows retrieved by a query in hierarchical manner.
• Formatting hierarchical data so that it is easy to read.
• Excluding branches from the tree structure.

Extensions to DML and DDL statements

• Discussing multi-table inserts.
• Creating and using external tables.
• Naming the index and using the CREATE INDEX command at the time of creating Primary Key constraint.

Writing scripts to generate scripts

• Describing the types of problems that are solved by writing SQL scripts that generate other SQL scripts.
• Writing and executing scripts that generate scripts with commands to create and drop tables.
• Writing and executing a script that generates a script of INSERT INTO commands.

SQL Workshop

• Applying techniques learned in this course.
• Preparing for future Oracle courses.