OVERVIEW
This class provides the basic knowledge and skills needed to code SQL statements in Oracle 11g. We also offer this class in Oracle 9i and 10g. In addition to SQL topics, the course includes relational database management system (RDBMS) concepts such as referential integrity, primary keys, foreign keys, locking, and programming considerations. The SQL information is ANSI standards and can be applied to any RDBMS, including DB2, SQL/Server, Informix, etc. Workshops are executed using an appropriate interactive tool such as SQL Developer, TOAD or SQL*Plus or your company standard software.
DURATION: 3 Days
OBJECTIVES
- Describe the relational model and how it relates to SQL
- Explain the three categories of SQL; namely DDL, DCL and DML
- Use proper syntax and coding standards when coding SQL statements.
- Code SQL statements to retrieve relational database data using the SELECT verb.
- Use SQL parameters WHERE, DISTINCT, IN, LIKE, BETWEEN to qualify data retrieval
- Understand when to use the SQL parameters ORDER BY, UNION, GROUP BY and HAVING.
- Use SQL aggregated functions COUNT, MIN, MAX, AVG, SUM
- Use scalar functions to format the results, e.g. ROUND, SUBSTR, CASE, TO_CHAR, DECODE, etc.
- Use regular functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
- Use SQL to join tables and retrieve data, both inner and outer joins.
- Use subqueries to select information from tables and retrieve data.
- Use subqueries to retrieve differences from data in two or more tables.
- Code SQL statements to change data, including the UPDATE, INSERT and DELETE instructions.
- Understand the impact of ROLLBACK and COMMIT
- Code efficient SQL statements by applying numerous performance considerations.
INTENDED AUDIENCE
This course is designed for people who will be retrieving data from a relational database using SQL with an Oracle focus.
PREREQUISITES
Introduction to Relational Database, and working knowledge of the editor (Wordpad, Notepad, or VI Edit) is required to perform the SQL lab exercises.
FORMAT/MATERIALS
This course includes a combination of lecture, discussion and numerous computer workshops. Students will receive an extensive course manual covering SQL, which will be a valuable reference after class. An optional text book Oracle 11g New Features by Robert Freeman is also available.
Introduction to Oracle11g
- What is the Oracle Database?
- Oracle Architecture
- Common Schema Objects
- Storage Group
- Databases
- Tablespaces
- Tables
- Indexes
- Views
- Principal Features
- Enterprise Edition
- Standard & Personal Editions
- 11g Release Overview
- Related Products: PL/SQL, SQL Developer, SQL*Plus, Forms, Reports, OBIEE
- Data Dictionary
- System Logging
- Utility Programs
- Locking Services
- SQL Developer
- SQL*Plus and scripting
SQL Basics
- The Language syntax
- The Relational Implementation
- The Subdivisions of SQL
- SQL Syntax
Retrieving Information
- The SELECT Statement
- The WHERE clause
- Comparison Operators
- Available Comparison Operators
- Quoting Text Strings (11g)
- Logical Operator AND
- Logical Operator OR
- BETWEEN, IN, LIKE
- IS NULL, IS NOT NULL
CONTENT- DAY 2
Functions
- TO_CHAR
- TO-DATE
- SUBSTR
- ROUND
- TRUNC
- CASE and DECODE
- COALESCE
- NVL and NULLIF
- Date Functions
- REGEXP_LIKE
- REGEXP_INSTR
- REGEXP_REPLACE
- REGEXP_SUBSTR
- MIN
- MAX
- SUM
- AVG
- COUNT
Ordering and Grouping
- Orders and Groups
- ORDER BY
- Sorting by Calculated Columns
- Sorting by Column Alias
- Sorting by Multiple Columns
- Case (and Accent) Insensitive Sort
Advanced Retrievals
- Joining Tables
- Join condition syntax
- Alias Names
- Joining a table to itself
- The Outer Join (LEFT, RIGHT, FULL)
- NATURAL JOIN
CONTENT- DAY 3
Subqueries
- Single-valued subqueries
- Multi-valued subqueries
- ALL and ANY parameters
- Correlated subqueries
- EXISTS parameter
Set Operations
- Differences
- Coding differences
- Impact of NULLS on differences
- Unions
- UNION operator
- UNION ALL
Data Manipulation
- UPDATE
- Updating a Single Row
- Updating Multiple Rows
- INSERT
- Inserting Multiple Rows
- Multi-Table INSERT.
- DELETE
- TRUNCATE
- MERGE
- COMMITs and ROLLBACKs
PERFORMANCE GUIDELINES
- Role of Indexes
- SQL Tuning Basics
- Tuning - The Process
- SQL*Plus AUTOTRACE