Course Content
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.
