Database 10g: SQL Fundamentals I

Scheduled Dates (sort by: location | date)

Course Overview

This course offers students an introduction to Oracle Database 10g database technology. In this class, students learn the concepts of relational databases and the powerful SQL programming language. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, create database objects, and query meta data.

Demonstrations and hands-on practices reinforce the fundamental concepts.

Learn to:

• • Use SQL Statements to retrieve data from tables
  • Create and Manage Tables
  • Employ SQL functions to generate and retrieve customized data
  • Run data manipulation statements (DML) to update data in the Oracle Database 10g

  Audience

  • Application Developers
  • Business Intelligence Developer
  • Database Administrators
  • Database Designers
  • End Users
  • Forms Developer
  • PL/SQL Developer
  • Portal Developer

• Prerequisites

  Familiarity with Data Processing Concepts and Techniques

• Ability to use a graphical user interface (GUI)

• Skills Gained

  • Identify the major structural components of Oracle Database 10g
  • Retrieve row and column data from tables with the SELECT statement
  • Create reports of sorted and restricted data
  • Employ SQL functions to generate and retrieve customized data
  • Create reports of aggregated data
  • Write SELECT statements that include queries
  • Use the SET operators to create subsets of data
  • Run data manipulation statements (DML) to update data in the Oracle Database 10g
  • Create tables to store data
  • Utilize views to display and retrieve data
  • Create sequences to generate numbers
  • Obtain meta data by querying the dictionary views.

• Course Topics

Introduction

• • List the Oracle Database 10g Main Features
  • An Overview of: components, internet platform, app server and developer suite
  • Describe Relational and Object Relational Database Designs
  • Review the System Development Life Cycle
  • Define the term Data Models
  • Describe different means of Sorting Data
  • Show how Multiple Tables can be related
  • Describe how SQL Communicates to the Database

• Writing SQL SELECT statements

• • Define projection, selection, and join terminology
  • Review the basic SQL SELECT statement syntax
  • Select all columns using a wildcard notation from a table
  • State simple rules and guidelines for writing SQL statements
  • Write a query containing the arithmetic operators
  • Use aliases to customize column headings
  • Create a character expression with the concatenation operator
  • Use the quote operator q to control text strings containing the quote character

• Using the iSQL*Plus Environment

• • SQL statements versus iSQL*Plus commands
  • Explaining the iSQL*Plus interface
  • Interacting with script files
  • Setting iSQL*Plus Preferences

• Restricting Data

• • Limit Rows Using a Selection
  • Use the WHERE Clause
  • List the main Comparison Conditions
  • Use the LIKE Condition to Compare Literal Values
  • List the Logical Conditions AND, OR, NOT
  • Use Multiple Conditions in the WHERE clause
  • Describe the Rules of Precedence

• Sorting Data

• • Sort Rows with the ORDER BY Clause
  • Use the & Substitution Variable
  • Use the iSQL*Plus DEFINE Command
  • Use the VERIFY Command

• SQL Functions

• • Differentiate between Single Row and Multiple Row SQL Functions
  • Categorize the Character Functions into Case Manipulation and Character Manipulation types
  • Explain the Numeric Functions ROUND, TRUNC, and MOD
  • List the Rules for Applying the Arithmetic Operators on Dates
  • Use the Arithmetic Operators with Dates in the SELECT Clause
  • Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
  • Explain Implicit and Explicit conversion
  • Nest Functions to Perform Multiple Tasks in One Statement

• Conditional Expressions

• Use the CASE Expression
• Explain the DECODE Expression

• Aggregating Data Using Group Functions

• • Categorize the Types of Group Functions
  • Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
  • Utilize the DISTINCT Keyword with the Group Functions
  • Describe how Nulls are handled with the Group Functions
  • Create Groups of Data with the GROUP BY Clause
  • Group Data by more than one column
  • Avoid Illegal Queries with the Group Functions
  • Exclude Groups of Data with the HAVING Clause

• Displaying Data from Multiple Tables

• • Identify Types of Joins
  • Retrieve Records with Natural Joins
  • Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
  • Create a Join with the USING clause to identify specific columns between tables
  • Use the ON clause to specify arbitrary conditions or specify columns to Join
  • Create a Three-way join with the ON clause to retrieve information from 3 tables
  • List the Types of Outer Joins LEFT, RIGHT, and FULL
  • Generating a Cartesian Product

• Using Subqueries

• • Use a sub query to solve a problem
  • Identify where sub queries can be placed in a SELECT statement
  • Describe the types of sub queries (single row, multiple row)
  • Show the single row sub query operators
  • Use the group functions in a sub query
  • Identify illegal statements with sub queries
  • Show the multiple row sub query operators
  • Explain how null values are handled in sub queries

• Using the Set Operators

• • Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
  • Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
  • Describe the INTERSECT operator
  • Use the INTERSECT operator
  • Explain the MINUS operator
  • Use the MINUS operator
  • List the SET operator guidelines
  • Order results when using the UNION operator

• Inserting and Updating Data

• • Write INSERT statements to add rows to a table
  • Insert Special Values
  • Copy Rows from Another Table
  • Update Rows in a Table

• Deleting Data

• Use DELETE statements to remove rows from a table
• Delete Rows Based on Another Table
• Describe the TRUNCATE Statement

• Database Transactions

• Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
• Show how Read Consistency works

• Using DDL Statements

• • List the main database objects
  • Identify the Naming Rules
  • Display the basic Syntax for Creating a Table
  • Show the DEFAULT option
  • List the Data Types that are available for Columns

• Managing Tables

• • Explain the different types of constraints
  • Show resulting exceptions when constraints are violated with DML statements
  • Create a table with a sub query
  • Describe the ALTER TABLE functionality
  • Remove a table with the DROP statement

• Creating Other Schema Objects

• • Categorize simple and complex views and compare them
  • Create a view
  • Retrieve data from a view
  • Explain a read-only view
  • List the rules for performing DML on complex views
  • Create a sequence
  • List the basic rules for when to create and not create an index
  • Create a synonym

• Managing Objects with Data Dictionary Views

• • Describe the structure of each of the dictionary views
  • List the purpose of each of the dictionary views
  • Write queries that retrieve information from the dictionary views on the schema objects
  • Use the COMMENT command to document objects

• Appendices

• Oracle Join Syntax
• Using SQL*Plus
• Using SQL Developer
• Additional Practices

Database 10g: SQL Fundamentals I / D17108GC20