Oracle DBA Database Administrator course Expert level by Sardius Technologies

Introduction to SQL Database Object:

Oracle Pre Defined Data types

DDL Commands

Create, Alter (add, modify, rename, drop)Columns, Rename, truncate, drop

DML-Insert, update, delete, merge

DCL-Grant, Revoke

TCL-Commit, Rollback, Savepoint

Writing Basic SQL SELECT Statements

Objectives

Capabilities of SQL SELECT Statements

Basic SELECT Statement

Selecting All Columns

Selecting Specific Columns

Writing SQL Statements

Column Heading Defaults

Arithmetic Expressions

Using Arithmetic Operators

Operator Precedence

Using Parentheses

Defining a Null Value

Null Values in Arithmetic Expressions

Defining a Column Alias

Using Column Aliases

Concatenation Operator

Using the Concatenation Operator

Literal Character Strings

Using Literal Character Strings

Duplicate Rows

Eliminating Duplicate Rows

Displaying Table Structure

Limiting Rows Using a Selection

Limiting the Rows Selected

Using the WHERE Clause

Character Strings and Dates

Comparison Conditions

Using Comparison Conditions

Other Comparison Conditions

Using the BETWEEN Condition

Using the IN Condition

Using the LIKE Condition

Using the NULL Conditions

Logical Conditions

Using the AND Operator

Using the OR Operator

Using the NOT Operator

Rules of Precedence

ORDER BY Clause

Sorting in Descending Order

Sorting by Column Alias

Sorting by Multiple Columns

Set Operators (UNION, UNION ALL, INTERSECT, MINUS)

Constraints

What are Constraints?

Constraint Guidelines

Defining Constraints

The NOT NULL Constraint

The UNIQUE Constraint

The PRIMARY KEY Constraint

The FOREIGN KEY Constraint

FOREIGN KEY Constraint Keywords

The CHECK Constraint

Adding a Constraint Syntax

Adding a Constraint

Dropping a Constraint

Disabling Constraints

Enabling Constraints

Cascading Constraints

Viewing Constraints

Viewing the Columns Associated with Constraints

Functions

SQL Functions

Two Types of SQL Functions

Single-Row Functions

Single-Row Functions

Character Functions

Character Functions

Case Manipulation Functions

Using Case Manipulation Functions

Character-Manipulation Functions

Using the Character-Manipulation Functions

Number Functions

Using the ROUND Function

Using the TRUNC Function

Using the MOD Function

Working with Dates

Arithmetic with Dates

Using Arithmetic Operators with Dates

Date Functions

Using Date Functions

Conversion Functions

Implicit Data Type Conversion

Explicit Data Type Conversion

Using the TO_CHAR Function with Dates

Elements of the Date Format Model

Using the TO_CHAR Function with Dates

Using the TO_CHAR Function with Numbers

Using the TO_NUMBER and TO_DATE Functions

General Functions

NVL Function

Using the NVL Function

Using the NVL2 Function

Using the NULLIF Function

Using the COALESCE Function

Conditional Expressions

The CASE Expression

Using the CASE Expression

The DECODE Function

Using the DECODE Function

Aggregate functions

Joins

Obtaining Data from Multiple Tables

Cartesian Products

Generating a Cartesian Product

Types of Joins

Joining Tables Using Oracle Syntax

What is an Equijoin?

Additional Search Conditions Using the AND Operator

Qualifying Ambiguous Column Names

Using Table Aliases

Joining More than Two Tables

Non-Equijoins

Retrieving Records with Non-Equijoins

Outer Joins

Outer Joins Syntax

Using Outer Joins

Self Joins

Joining a Table to Itself

Practice 4, Part One: Overview

Joining Tables Using SQL: 1999 Syntax

Creating Cross Joins

Creating Natural Joins

Retrieving Records with Natural Joins

Creating Joins with the USING Clause

Retrieving Records with the USING Clause

Creating Joins with the ON Clause

Retrieving Records with the ON Clause

Creating Three-Way Joins with the ON Clause

INNER Versus OUTER Joins

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Subqueries

Using a Subquery to Solve a Problem

Subquery Syntax

Using a Subquery

Guidelines for Using Subqueries

Types of Subqueries

Single-Row Subqueries

Executing Single-Row Subqueries

Using Group Functions in a Subquery

The HAVING Clause with Subqueries

What is Wrong with this Statement?

Will this Statement Return Rows?

Multiple-Row Subqueries

Using the ANY Operator in Multiple-Row Subqueries

Using the ALL Operator in Multiple-Row Subqueries

Null Values in a Subquery

Database Objects

Views

Why use Views?

Simple Views and Complex Views

Creating a View

Retrieving Data from a View 1

Querying a View

Modifying a View

Creating a Complex View

Rules for Performing DML Operations on a View

Using the WITH CHECK OPTION Clause

Denying DML Operations

Removing a View

Inline Views

Sequence

Sequence

The CREATE SEQUENCE Statement Syntax

Creating a Sequence

Confirming Sequences

NEXTVAL and CURRVAL Pseudo columns

Using a Sequence

Modifying a Sequence

Guidelines for Modifying a Sequence

Removing a Sequence

Index

How Are Indexes Created?

Creating an Index

When to Create an Index

When Not to Create an Index

Confirming Indexes

Function-Based Indexes

Removing an Index

Synonyms

Creating and Removing Synonyms