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
- 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
- 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
- Nest multiple functions
- Apply the NVL, NULLIF, and COALESCE functions to data
- Use conditional IF THEN ELSE logic in a SELECT statement
Reporting Aggregated Data Using the Group Functions
- Group Functions
- Creating Groups of Data
- Restricting Group Results
Displaying Data From Multiple Tables Using Joins
- Introduction to JOINS
- Types of Joins
- Natural join
- Self-join
- Non equijoins
- OUTER join
Using Subqueries to Solve Queries
- Introduction to Subqueries
- Single Row Subqueries
- Multiple Row Subqueries
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
Introduction to Data Definition Language
- Data Definition Language