Oracle SQL for End Users Part 1

Audience

End users are the targeted audience for this course.

Objective

• Identify database principles and concepts concerning the Oracle server SQL and SQL*Plus.
• Write basic SQL SELECT statements.
• Add clauses to basic SQL SELECT statements to restrict and sort data retrieved by a query.
• Write SQL statements that contain a variety of singlerow functions.

Topics Include

Unit 1: Database Principles and Oracle Concepts

• Identify aspects of the relational database model.
• Match the relational database table element with its description.
• Sequence the phases of the system development life cycle.
• Match components of the EMPLOYEE and DEPARTMENT entity relationship model with their description.
• Define structured query language (SQL).
• Match SQL statement types with their appropriate SQL commands.
• Identify the capabilities of the Oracle implementation of ORDBMS and RDBMS.
• Match database column values with their data types.
• Match the names of the three tables used in this course with the information they contain.

Unit 2: Writing Basic SQL Statements

• Match the capabilities of SQL SELECT statements with their definitions.
• Match SQL statement elements with their descriptions.
• Write a SELECT statement that displays columns and rows 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.
• Embed a literal character string in a SELECT clause to customize the output displayed.
• Eliminate duplicate rows from the output of a query by inserting the DISTINCT keyword.
• Identify the differences between the characteristics of SQL*Plus and SQL.
• Log in to SQL*Plus in a Windows environment.
• Display the structure of a table by using the DESCRIBE command.
• Match the SQL*Plus editing command with its function.
• Match the SQL*Plus file command with its function.

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 data types.
• Compare one expression to another within a WHERE clause.
• Display rows within 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 precedence rules when evaluating conditions that use comparison and logical operators.
• Sort rows retrieved in ascending order using the ORDER BY clause.
• Sort rows retrieved in descending order 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

• Identify the capabilities of the two types of SQL functions.
• Match single-row function categories with their features.
• Change the case of data displayed by using case conversion 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.
• Convert a null value to an actual value for display using the NVL function.
• Identify the purpose of the SYSDATE function.
• Perform arithmetic operations on dates.
• Display date information using common SQL date functions.
• Match the data type conversion function with the appropriate description.
• 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.
• Convert a character string to a number format by using the TO_NUMBER data type conversion function.
• Convert a character string to a date format by using the TO_DATE data type conversion function.
• Identify the order in which nested functions are evaluated.
• Write a query that includes nested single-row functions.

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