Oracle SQL Specifics: Retrieving and Formatting Data

This course is the first in a two-part series on Oracle SQL Specifics that will teach participants how to use commands functions and operators supported by Oracle as extensions to standard SQL. Students will be introduced to the Oracle implementation of the relational database management system (RDBMS) and the object-relational database management system (ORDBMS). Users will learn how SQL functions can be used in SQL SELECT statements to extract data from the database and how SET operators can be used to combine multiple queries into a single query. Users will also learn how subqueries and correlated subqueries can help to solve data retrieval problems. Finally participants will learn how to retrieve data based on a hierarchical relationship between rows.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

Audience

Application Developers System Analysts Database Administrators Technical Support Personnel. Prior knowledge of SQL is helpful.

Objective

• Define relational database concepts and object relational database concepts.
• Use the Oracle SELECT statement.
• Implement functions including single row conversion and group functions.
• Use SET operators.
• Use subqueries.
• Retrieve hierarchical data.

Topics Include

Unit 1: Oracle Environment Overview

• Identify three Relational Model concepts.
• Match the five database objects to their definitions.
• Identify features of Oracle's object relational database management system (ORDBMS) Oracle8.
• Identify how Oracle8 extends the capabilities of the Oracle7 server.
• Identify characteristics of object systems.
• Identify tasks performed by the Oracle DBMS.

Unit 2: The Oracle SELECT Statement

• Match each SQL statement to its category.
• Identify the components of a basic SELECT statement.
• Select rules that govern the writing of SQL statements.
• Use an arithmetic operator to perform a calculation in a query.
• Use the NVL function to convert null values to zero.
• Display a column with a column alias.
• Use the concatenation operator to combine query results from multiple table columns into a single column.
• Perform a query using a pseudocolumn.
• Identify the components of a basic WHERE clause.
• Use the WHERE clause to query for a character string or a date.
• Match each type of operator to the correct label.
• Identify which operators can be used to find rows that do not meet a condition.
• Perform a query using a SQL operator.
• Use the ORDER BY clause to sort query results.
• Use a join to query data from multiple tables.
• Use an outer join to return records without a direct match.
• Identify the features of the SQL*Plus environment.
• Log in to SQL*Plus.
• Use the DESCRIBE command to display the structure of a table.
• Match SQL*Plus editing commands to their descriptions.
• Match SQL*Plus file commands to their descriptions.

Unit 3: Implementing Functions

• Identify the different SQL functions.
• Identify the purpose of single row functions.
• Use a character function to display character data in a specific format.
• Use a number function to display numeric values in a specific format.
• Perform a query combining an arithmetic operator with a date.
• Use a date function to perform a calculation with a date value.
• Match each conversion function to its purpose.
• Use the TO_CHAR function to convert a date value from the default format to a specific format.
• Use the TO_CHAR function to convert a number to a character value.
• Use the TO_DATE function to convert a character string to a date format.
• Use the DECODE function to decode an expression.
• Perform a query with a nested single row function.
• Match each group function to its description.
• Use a group function to query a set of rows and display one result per group.
• Use the GROUP BY clause to divide query rows into smaller groups.

Unit 4: Using SET Operators

• Match each SET operator to the information it returns following a query.
• Use the UNION operator to return all rows from multiple queries eliminating any duplicate rows.
• Use the INTERSECT operator to return all common rows in two queries.
• Use the MINUS operator to return rows returned by the first query but not the second query.
• Identify SET operator rules.
• Use a dummy column and a datatype conversion function to match the number and datatypes in a compound query.
• Use the ORDER BY clause to sort a compound query.

Unit 5: Utilizing Subqueries

• Perform a query with an embedded subquery.
• Perform a query with a subquery embedded in a FROM clause.
• Perform a query with a multiple column subquery.
• Perform a query with a correlated subquery.
• Use a correlated subquery with the EXISTS operator to test for a value.

Unit 6: Retrieving Hierarchical Data

• Select the clauses that identify an hierarchical query.
• Use the CONNECT BY PRIOR and START WITH clauses to walk through the tree structure.
• Identify the pseudocolumn used to explicitly show the rank of a row in the hierarchy.
• Use the LPAD function to format a hierarchical report.
• Use the WHERE and the CONNECT BY clauses to prune a tree.
• Use the ORDER BY clause to sort the rows returned in a hierarchical query.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media

• Oracle SQL Specifics: Creating and Managing Database Objects