Oracle Database: SQL Fundamentals I Training Course

Oracle Database: SQL Fundamentals I Course Contents

Introduction

• Overview of Oracle Database 11g and related products
• Overview of relational database management concepts and terminologies
• Introduction to SQL and its development environments
• The HR schema and the tables used in this course
• Oracle Database documentation and additional resources

Retrieve Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Usage of arithmetic expressions and NULL values
• Implement Column aliases
• Describe the concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Display the table structure using the DESCRIBE command

Restrict and Sort Data

• Write queries with a WHERE clause to limit the output retrieved
• Use the comparison operators and logical operators
• Identify the rules of precedence for comparison and logical operators
• Usage of character string literals in the WHERE clause
• Write queries with an ORDER BY clause
• Sort output in descending and ascending order
• Substitution Variables

Use Single-Row Functions to Customize Output

• Differentiate between single row and multiple row functions
• Manipulate strings using character functions
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the DATE functions

Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Describe TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nesting multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic

Aggregated Data Using Group Functions

• How aggregation functions help to produce meaningful reports?
• Use the AVG, SUM, MIN, and MAX function
• How to handle Null Values in a group function?
• Divide the data in groups by using the GROUP BY clause
• Exclude groups of date by using the HAVING clause

Display Data From Multiple Tables Using Joins

• Write SELECT statements to access data from more than one table
• Join Tables Using SQL:1999 Syntax
• View data that does not meet a join condition by using outer joins
• Join a table to itself by using a self join
• Create Cross Joins

Use Sub-queries to Solve Queries

• Use a Subquery to Solve a Problem
• Execute Single-Row Sub-queries
• Deploy Group Functions in a Sub-query
• Multiple-Row Subqueries
• Use ANY and ALL Operator in Multiple-Row Sub-queries
• Use EXISTS Operator

SET Operators

• What are SET operators?
• Use a SET operator to combine multiple queries into a single query
• Use UNION, UNION ALL, INTERSECT, and MINUS Operator
• Use ORDER BY Clause in Set Operations

Data Manipulation

• Add New Rows to a Table
• Change the Data in a Table
• Use DELETE and TRUNCATE Statements
• Save and discard changes with the COMMIT and ROLLBACK statements
• Implement Read Consistency
• Describe the FOR UPDATE Clause

Use DDL Statements to Create and Manage Tables

• Categorize Database Objects
• Create Tables using the CREATE TABLE Statement
• Identify the data types
• Describe Constraints
• Create a table using a subquery
• How to alter a table?
• Drop a table

Other Schema Objects

• Create, modify, and retrieve data from a view
• Perform Data manipulation language (DML) operations on a view
• Drop a view
• Create, use, and modify a sequence
• Create and maintain indexes
• Create and drop synonyms