Oracle SQL

Oracle PL/SQL

Oracle Forms

Oracle Reports

Residential Courses

Prices

Location

Oracle SQL: Basic SELECT Statements
Computer based training course

Course ID: 61110

Course Overview

This course is the first in a three-part Oracle SQL series that is based 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.

Learn To:

• 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 funcitons, in order to define single-row 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.

Content Emphasis

Skills-Based

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: Certified Database Administrator and Oracle Certified Application Developer, Release 2.

Total Learning Time

6 - 8 Hour(s)

Course Contents

Unit 1: Relational Database Concepts

Duration: 1 - 2 Hour(s)

• 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

Duration: 1 - 2 Hour(s)

• 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

Duration: 2 Hour(s)

• 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

Duration: 2 Hour(s)

• 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.

Anglesey has over 125 miles of coastline designated "areas of Natural Outstanding Beauty".

Tyn Y Felin is a mere five minutes away from the A55 North Wales Coast Expressway and is a perfectly situated coastal retreat on the serene and beautiful Isle of Anglesey.