Course Details: Oracle 11g SQL Fundamentals and Exam Prep

Synopsis:
This course gives students the knowledge needed as a first step in obtaining an Oracle Certification in Oracle 11g. The exam it targets is: 1Z0-051 Oracle Database 11g SQL Fundamentals

Students will learn the concepts of the Oracle database and the SQL language. Essential SQL skills such as how to write simple and complex queries, apply Oracle built-in functions, applications of analytical functions, creating database objects and managing Oracle schemas are covered in detail.

Primary: Perfect course for those wishing to take the 1Z0-051 Oracle Database 11g SQL Fundamentals exam in pursuit of an Oracle Certification to further their careers. All relevant topics on that exam will be covered.

Secondary: This course is also extremely useful for any person requiring in-depth knowledge of using SQL with an Oracle backend for developing applications, data reporting, quality assurance, business intelligence, and testing. Developers and programmers using any of the following will benefit greatly from this course:

• Oracle Reports
  
• Crystal Reports
  
• Java, JSP
  
• .NET
  
• Perl
  
• Cold Fusion
  
• C#, C++, C
  
• PHP
  
• ASP
  
• SAS
  
• Excel
  
• Informatica
  
• AJAX

• Basic database concepts (tables, records)
  
• Use of a standard text editor such as Notepad in a GUI environment (Windows)
  
• Familiarity with Windows XP is helpful

Course JE-001 Module Breakdown (8 Weeks):
1. An Overview of Oracle Database technology
2. Using SQL*Plus and SQL*Developer
3. Understanding the integration of SQL and traditional programming
4. Describe Relational and Object Relational Database Designs
5. Reading Data Models (ERDs)
6. Retrieving Data Using the SQL SELECT Statement
7. Restricting and Sorting Data
8. Using Conversion Functions and Conditional Expressions
9. Reporting Aggregated Data Using the Group Functions
10. SQL Joins
11. Using Sub-queries
12. Using the Set Operators
13. Manipulating Data (Insert, Update, Delete, Merge)
14. Using DDL Statements to Create and Manage Tables
15. Data Dictionary Views
16. Managing Schema Objects

• A copy of the book, OCA Oracle Database 11g SQL Fundamentals I Exam Guide: Exam 1Z0-051
• A Free PDF Copy of the book, Practical Oracle SQL Functions
• Copies of all course notes, hand-outs, and exercises with binders
• A 2 GB Flash Drive for saving student work
• Access to a laptop for use in the classroom for their use (not shared unless a shared system is requested) – these remain at the training center
• Complimentary pens, pencils, and paper
• Complimentary beverages and snacks each evening
• Access to the Stetson e-Education Blackboard System for downloading course materials and access to online content

Basic Relational Theory
[ ] Entities, relationships
[ ] Reading Entity Relationship Diagrams
[ ] Tables, columns, keys
[ ] Identifying design issues
[ ] Resolving many to many relationships
Oracle Product Family
[ ] Database and its versions
[ ] Role of SQL
[ ] Importance of PL/SQL
[ ] SQL*Developer GUI
[ ] Using SQL*Developer to access the database
[ ] Scripting SQL with SQL*Plus
Retrieving Data Using the SQL SELECT Statement
[ ] Data projection with SELECT statements
[ ] How SELECT statements work on the database server
[ ] Execute a basic SELECT statement
[ ] Using proper aliasing for columns and tables
Restricting and Sorting Data
[ ] Using WHERE clauses
[ ] Understanding relational operators
[ ] Using special operators LIKE, BETWEEN, IN
[ ] Exploring the impact of NULL of WHERE conditions
[ ] Sort the rows with ORDER BY
Using Single-Row Functions to Customize Output
[ ] Describe scalar functions
[ ] Use character, number, and date functions in SELECT statements
[ ] Using ANSI CASE to enhance a query
[ ] Handling problematic NULLS with NVL and COALESCE
Using Conversion Functions and Conditional Expressions
[ ] Converting datatypes with ANSI CAST
[ ] Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
[ ] Apply conditional expressions in a SELECT statement
Reporting Aggregated Data Using the Group Functions
[ ] Using aggregate functions AVG, SUM, MIN, MAX, and COUNT
[ ] Group and Set theory
[ ] Group data by using the GROUP BY clause
[ ] Filtering Groups with the HAVING clause
Displaying Data from Multiple Tables
[ ] Access data from more than one table using JOIN
[ ] ANSI and non-ANSI JOIN syntax
[ ] Self-joins
[ ] Outer joins LEFT, RIGHT, and FULL
[ ] THETA join conditions
Using Subqueries to Solve Queries
[ ] Define subqueries
[ ] Describe the types of problems that the subqueries can solve
[ ] List the types of subqueries
[ ] CORRELAYED and NON-CORRELATED subqueries
[ ] Understanding the EXISTS operator
Using the Set Operators
[ ] Describe set operators UNION, MINUS, INTERSECT
[ ] Ensuring proper set compatibility
[ ] Control the order of rows returned
Managing Database Data
[ ] INSERT, UPDATE, DELETE, MERGE
[ ] Insert rows into a table
[ ] Update rows in a table
[ ] Delete rows from a table
[ ] COMMIT, ROLLBACK and SAVEPOINT
[ ] Using subqueries in DML statements
Creating Objects
[ ] Understanding the Oracle Schema model
[ ] Creating tables and columns
[ ] Working with column datatypes and choosing the best fit
[ ] Enforcing integrity with constraints
[ ] Creating PRIMARY, UNIQUE, and FOREIGN keys
[ ] Using the Data Dictionary to find schema objects
Useful Schema objects
[ ] Breaking up complex queries with Views
[ ] Oracle sequences
[ ] Create and maintain indexes
[ ] Create private and public synonyms