Oracle Database: SQL Workshop I (D80190) – Outline

Detailed Course Outline

Introduction

• Course Objectives, Course Agenda and Appendixes Used in this Course
• Overview of Oracle Database 12c and Related Products
• Overview of relational database management concepts and terminologies
• Introduction to SQL and its development environments
• What is Oracle SQL Developer?
• Starting SQL*Plus from Oracle SQL Developer
• The Human Resource(HR) Schema
• Tables used in the Course

Retrieving Data using the SQL SELECT Statement

• Capabilities of the SELECT statement
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Use of the DESCRIBE command

Restricting and Sorting Data

• Limiting the Rows
• Rules of precedence for operators in an expression
• Sorting rows using ORDER BY clause
• SQL row limiting clause in a query
• Substitution Variables
• Using the DEFINE and VERIFY command

Using Single-Row Functions to Customize Output

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Nesting Functions
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

Using Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Conditional Expressions

Reporting Aggregated Data Using the Group Functions

• Group Functions
• Grouping Rows (GROUP BY and HAVING clause)
• Nesting Group Functions

Displaying Data From Multiple Tables Using Joins

• Introduction to JOINS
• Types of Joins
• Natural join
• Self-join
• Nonequijoins
• OUTER join
• Cartesian product

Using Subqueries to Solve Queries

• Introduction to Subqueries
• Single Row Subqueries
• Multiple Row Subqueries
• Null values in a subquery

Using the SET Operators

• Set Operators
• UNION and UNION ALL operator
• INTERSECT operator
• MINUS operator
• Matching the SELECT statements
• Using ORDER BY clause in set operations

Managing Tables using DML statements

• Data Manipulation Language
• Database Transactions
• Manual Data Locking

Introduction to Data Definition Language

• Database Objects
• CREATE TABLE statement
• Data types
• Overview of constraint
• Creating a table using subquery
• Use the ALTER TABLE statement to add, modify, and drop a column
• DROP TABLE statement