DBMS CONCEPTS

  • What is Database
  • What is Database Management System
  • Different Types of Database Models
  • Normalization
  • RDBMS
  • ORDBMS
  • Difference Between DBMS, RDBMS & ORDBMS

INTRODUCING ORACLE

  • Different versions of Oracle
  • Features of Oracle

SQL

  • What is SQL
  • Role of SQL in RDBMS
  • Data Types of Oracle
  • Naming rules in Oracle
  • What is SQL*PLUS
  • What is Metadata
  • What is Schema
  • Data Dictionary Tables

SQL COMMANDS

  • Data Definition Language (DDL)
  • Data Retrieval Language (DRL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL)
  • 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
  • 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