Oracle SQL: Basic SELECT Statements

This course is the first in a three-part Oracle SQL series that is based on the ILT course Introduction to Oracle: SQL and PL/SQL. This course introduces the learner to Oracle relational database concepts and the use of SQL for storing retrieving and manipulating data in a relational database. This course covers relational database principles and Oracle concepts; writing basic SQL statements; restricting and sorting data; and using single-row functions.

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 database administrators designer/developers and technical support professionals. Prior to taking this course participants should be able to use a graphical user interface (GUI) and should be familiar with data processing concepts and techniques. Some prior knowledge of programming languages would be an advantage. This course will help candidates prepare for test 1Z0-001 which applies towards the following Oracle Certified Professional (OCP) Program certification tracks: Oracle7.3 Certified Database Administrator Oracle8 Certified Database Administrator and Oracle Certified Application Developer Release 2.

Objective

• Identify relational database concepts and properties.
• Describe database design processes by explaining the system development life cycle and entity relationship modeling.
• Describe the concept of object relational database management systems.
• Write SQL SELECT queries.
• Customize SQL queries when selecting data from a table.
• Identify SQL*Plus environment features and commands.
• Restrict rows retrieved by a query.
• Identify comparison operators that can be used in defining row retrieval restrictions.
• Identify logical operators that can be used to define row retrieval restrictions.
• Sort rows retrieved by a query.
• Describe the types of SQL functions in order to define singlerow functions.
• Demonstrate the use of four character functions; LOWER UPPER INITCAP and LENGH.
• Demonstrate the use of three number functions; ROUND TRUNC and MOD.
• Demonstrate the use of data functions.
• Demonstrate the use of data type conversion functions.

Topics Include

Unit 1: Relational Database Concepts

• Define components of the relational model.
• Identify the structural elements of a relational database.
• List the properties of a relational database.
• Identify the phases of the system development life cycle.
• Identify the three components of an entity relationship model.
• Indicate the diagramming conventions used in entity relationship modeling.
• List characteristics of object relational database management systems.
• Identify the benefits of Oracle's implementation of object relational technology.
• Define the tools used to interact with the Oracle server.
• Identify the types of SQL statements.

Unit 2: Writing Basic SQL

• List capabilities of SQL SELECT statements.
• Identify the mandatory elements of a SELECT statement.
• Identify methods for executing SELECT statements.
• Write a SELECT statement that displays all columns and rows of a table.
• Write a SELECT statement that displays specific columns from a table.
• Identify precedence in evaluating an arithmetic expression within a SQL statement.
• Specify an alias for a column heading when extracting 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.
• Use the DISTINCT keyword to eliminate duplicate rows from the output of a query.
• Manage null values using the NVL function in a SELECT statement.
• Identify the differences between SQL*Plus commands and SQL statements.
• Log in to SQL*Plus through a Window environment.
• Display the structure of a table using the DESCRIBE command.
• Identify SQL*Plus commands used to edit SQL statements.
• Identify SQL*Plus commands used to manage the contents of the SQL buffer.

Unit 3: Restricting and Sorting Data: Write a query that restricts rows returned by using the WHERE clause

• Identify guidelines for specifying literal strings in a WHERE clause.
• 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 containing null values by using 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 features of a SQL function.
• Identify the two types of SQL functions.
• Identify the features of a single-row function.
• Convert alpha characters to lowercase by using the LOWER function.
• Convert alpha characters to uppercase by using the UPPER function.
• Capitalize the first letter of each work in a character value by using the INITCAP function.
• Display the number of characters in a value by using the LENGTH function.
• Round a value to a specified decimal place by using the Round function.
• Truncate a value to a specified decimal place by using the TRUNIC function.
• Return the remainder of one value divided by another by using the MOD function.
• Identify the common Oracle date formats.
• Select the current date by using the SYSDATE function.
• Perform arithmetic operations on dates.
• Identify common SQL date functions.
• Identify common data type conversion functions.
• Convert a date value to a character string by using the TO_CHAR function.
• Convert a number to a character string by using the TO_CHAR function.
• Evaluate single row functions that are nested.

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

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