Database 10g: SQL Fundamentals I

Course Overview

This class is applicable to Oracle8i, Oracle9i and Oracle Database 10g users. 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 practice reinforce the fundamental concepts.

Audience

• Technical Consultant
• PL/SQL Developer
• Database Designers

Prerequisites

Required Descriptive Prerequisites:

Familiarity with data processing concepts and techniques

Suggested Descriptive Prerequisites:

Ability to use a graphical user interface (GUI)

Skills Gained

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

Course Outline

Retrieving Data Using the SQL SELECT Statement

• List the Oracle Database 10g main features
• Provide an overview of : components, internet platform, apps server and developer suite

Describe relational and object relational database designs

• Review the system development life cycle
• Describe different means of storing data
• Review the relational database concept
• Define the term data models
• Show how multiple tables can be related

Restricting and Sorting Data

• Define projection, selection, and join terminology
• Review the basic SQL SELECT statement syntax
• Select all columns using a wildcard notation from a table
• Identify specific columns to be retrieved
• State simple rules and guidelines for writing SQL statements
• Show the column heading defaults in a SQL statement
• List the arithmetic operators and their precedence
• Write a query containing the arithmetic operators

Using Single Row Functions to Customise Reports

• Limit rows using a selection
• Describe the syntax of the WHERE clause
• Use the WHERE clause to retrieve specific rows
• Specify character string and date notation in the WHERE clause
• List the main comparison conditions
• Use the comparison conditions in the WHERE clause
• Show the other comparison conditions - BETWEEN, IN, LIKE, IS NULL
• Create queries that contains the BETWEEN, IN, LIKE, and IS NULL comparisons

Reporting Aggregated Data Using the Group Functions

• Show the differences between single row and multiple row SQL functions
• Categorise the character functions into case manipulation and character manipulation types
• Explain the case manipulation functions - LOWER, UPPER, and INITCAP
• Use the case manipulation functions in the SELECT and WHERE clauses
• Explain the character manipulation functions - CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM
• Use the character manipulation functions in the SELECT and WHERE clauses
• Explain the numeric functions - ROUND, TRUNC, and MOD
• Introduce the DUAL table as a means to view function results

Displaying Data From Multiple Tables

• Describe the group functions
• Categorise the types of group functions
• List the syntax for the group functions
• Use the AVG, SUM, MAX, MIN, and COUNT functions in a query
• Utilise 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

Using Subqueries to Solve Queries

• Show the join tables syntax using SQL 99 syntax
• Use table aliases to write shorter code and explicitly identify columns from multiple tables
• Issue a SQL CROSS JOIN statement to produce a Cartesian product
• Use the NATURAL JOIN clause to retrieve data from tables with the same named columns
• 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

Using the SET Operators

• Use a subquery to solve a problem
• List the syntax for subqueries in a SELECT statement's WHERE clause
• Identify where subqueries can be placed in a SELECT statement
• List the guidelines for using subqueries
• Describe the types of subqueries (single row, multiple row)
• Show the single row subquery operators
• Execute single row subqueries
• Use the group functions in a subquery

Manipulating Data

• Describe the tables used in this lesson
• 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

Using DDL Statements to Create and Manage Tables

• Write INSERT statements to add rows to a table
• Insert special values
• Copy rows from another table
• Create UPDATE statements to change data in a table
• Generate DELETE statements to remove rows from a table
• Use a script to manipulate data
• Save and discard changes to a table through transaction processing (COMMIT, ROLLBACK, and SAVEPOINT)
• Show how read consistency works

Database 10g: SQL Fundamentals I / D17108GC20