Oracle9i SQL: Basic SELECT Statements

This course covers relational database principles and Oracle concepts; writing basic SQL statements; restricting and sorting data; and using single-row functions. Additionally this course provides an introduction to relational database concepts and the use of SQL for storing retrieving and manipulating data in a relational database.

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

The target audience for this course includes database administrators and application developers. This course will help students prepare for the Oracle9i DBA certification and is mapped to Exam 1Z0-007.

Objective

• Identify the components of the relational model.
• Match the structural elements of a relational database table with their descriptions.
• Sequence the phases of the system development life cycle.
• Match the types of SQL statements with their descriptions.
• Match the capabilities of SQL SELECT statements with their functions.
• Identify the guidelines for constructing basic SQL SELECT statement.
• Write a query that restricts rows returned by using the WHERE clause.
• Write a WHERE clause to accommodate different datatypes.
• Use comparison operators with the WHERE clause.
• Identify the different categories of datatypes.
• Match the character datatypes with their descriptions.
• Identify the features of the NUMBER datatype.

Topics Include

Unit 1: Relational Database Concepts

• Identify the components of the relational model.
• Match the structural elements of a relational database table with their descriptions.
• Sequence the phases of the system development life cycle.
• Identify the three components of an entity relationship model.
• Match the entity relationship model components with their conventions.
• Match the components of Oracle9i with their functions.
• Identify the tools and languages used to communicate with the Oracle server.
• Match the services provided by Oracle9iAS with their functions.
• Match the tools in Oracle9iDS with their functions.
• Identify the features of Oracle9i.
• Identify the features of Oracle9i Database.
• Match the components of Oracle Internet Platform with their features.
• Identify the benefits of the Entity Relationship (ER) model.

Unit 2: Writing Basic SQL Statements

• Match the capabilities of SQL SELECT statements with their functions.
• Identify the guidelines for constructing basic SQL SELECT statement.
• Use the SELECT statement to display rows and columns from a table.
• Write a SQL statement that includes an arithmetic expression.
• Assign an alias to a column heading when retrieving data from a table.
• Concatenate multiple columns that are selected from a table.
• Identify the uses of iSQL*Plus.
• Embed a literal character string in a SELECT clause to customize the output.
• Eliminate duplicate rows from the output of a query by using the DISTINCT keyword.
• Log on to SQL*Plus through a Windows environment.
• Display the structure of a table using the DESCRIBE command.
• Match the SQL*Plus edit commands with their functions.
• Match the SQL*Plus file commands with their functions.
• Match the types of SQL statements with their descriptions.
• Log on to the iSQL*Plus environment by using a browser.
• Match the options in the iSQL*Plus window with their functions.
• Display the structure of a table by using the DESCRIBE command.
• Save iSQL*Plus commands to Script files by using the Save Script button of the iSQL*Plus window.
• Load a previously saved iSQL*Plus command from a script file by using the Load Script button of iSQL *Plus window.
• Save the output of iSQL*Plus commands to a script file by using the iSQL *Plus window.
• Identify the differences between iSQL*Plus commands and SQL statements.

Unit 3: Restricting and Sorting Data

• Write a query that restricts rows returned by using the WHERE clause.
• Write a WHERE clause to accommodate different datatypes.
• Use comparison operators with the WHERE clause.
• Display rows based on a range of values by using the BETWEEN operator.
• Display rows that match a list of values by using the IN operator.
• Perform wildcard searches by using the LIKE operator.
• Display rows that contain NULL values by using the IS NULL operator.
• Write a combined expression by using the AND operator in a WHERE clause.
• Write a combined expression by using the OR operator in a WHERE clause.
• Restrict rows by using the NOT operator in a WHERE clause.
• Identify the precedence rules when evaluating conditions that use comparison and logical operators.
• Sort rows retrieved in ascending order by using the ORDER BY clause.
• Sort rows retrieved in descending order by using the DESC keyword in the ORDER BY clause.
• Sort rows retrieved by using a column alias in the ORDER BY clause.
• Sort rows retrieved by more than one column.

Unit 4: Single-Row Functions

• Match the types of character functions with their uses.
• Display data in lowercase by using the LOWER function.
• Display the number of characters in a value by using the LENGTH function.
• Match the character datatypes with their descriptions.
• Display information about a character value by using SUBSTR and INSTR functions.
• Display values padded with characters using LPAD and RPAD functions.
• Display rounded values to a specified decimal place by using the ROUND function.
• Display truncated values to a specified decimal place by using the TRUNC function.
• Return the remainder of one value divided by another by using the MOD function.
• Identify the uses of the SYSDATE function.
• Perform arithmetic operations on dates.
• Display date information by using common SQL date functions.
• Match the explicit datatype conversion functions with their uses.
• Convert a date value to a character string for display by using the TO_CHAR function.
• Convert a number to a character string for display by using the TO_CHAR function.
• Identify functions used to convert characters to other datatypes.
• Convert a NULL value to an actual value for display by using the NVL function.
• Identify the features of the NUMBER datatype.
• Decode an expression by using the DECODE function.
• Evaluate single-row functions that are nested.
• Match the number functions with their uses.
• Identify the concepts related to time zones.
• Concatenate character values by using the CONCAT function.
• Remove characters or extra spaces from character strings by using the TRIM function.
• Search for and replace a text expression by using the REPLACE function.
• Return the current date and time in a session's time zone by using the CURRENT_DATE function.
• Return the current date time and time zone of a session by using the CURRENT_TIMESTAMP function.
• Return the current date and time in a session time zone by using the LOCALTIMESTAMP function.
• Return the database time zone by using the DBTIMEZONE function.
• Return the current session's time zone by using the SESSIONTIMEZONE function.
• Return a datetime field value from a datetime or an interval value expression by using the EXTRACT function.
• Convert a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value by using the FROM_TZ function.
• Convert a character value to a TIMESTAMP datatype by using the TO_TIMESTAMP function.
• Convert a character value to a TIMESTAMP WITH TIME ZONE value by using the TO_TIMESTAMP_TZ function.
• Convert a character value to an INTERVAL YEAR TO MONTH datatype by using the TO_YMINTERVAL function.
• Identify the datatypes used in implicit datatype conversion.
• Return an expression based on the evaluation of a NULL value by using the NVL2 function.
• Compare two expressions by using the NULLIF function.
• Compare multiple expressions by using the COALESCE function.
• Implement conditional processing by using the CASE expression.
• Identify the different categories of datatypes.
• Match the LOB datatypes with their functions.
• Match the datetime datatypes with their functions.
• Define the RR date format.
• Return the time zone offset by using the TZ_OFFSET function.
• Identify the types of SQL functions.
• Match the single-row function types with their features.

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

CDROM

• Oracle9i SQL: Data Retrieval Techniques
• Oracle9i SQL: DML and DDL