Oracle Database: SQL Fundamentals I

3 Day Course
Hands On
Code OCDSQLFUND

This course has been retired. Please view currently available Oracle PL/SQL and SQL*Plus Training Courses.

Modules

Hide all

Introduction (5 topics)

  • 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 (6 topics)

  • 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 (7 topics)

  • 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 (5 topics)

  • 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 (5 topics)

  • 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 (5 topics)

  • 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 (5 topics)

  • 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 (6 topics)

  • 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 (4 topics)

  • 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 (6 topics)

  • 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 (7 topics)

  • 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 (6 topics)

  • 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

Prerequisites

Familiarity with data processing concepts and techniques.

Course PDF

Print

Sections