DBMS CONCEPTS Writing Basic SQL SELECT Statements

What is Database Objectives

What is Database Management Capabilities of SQL SELECT
System Statements
Different Types of Database Models Basic SELECT Statement
Normalization Selecting All Columns
RDBMS Selecting Specific Columns
ORDBMS Writing SQL Statements
Difference Between DBMS, RDBMS Column Heading Defaults
& ORDBMS Arithmetic Expressions
Using Arithmetic Operators
Operator Precedence
INTRODUCING ORACLE
Using Parentheses
Different versions of Oracle Defining a Null Value
Features of Oracle Null Values in Arithmetic
Expressions
Defining a Column Alias
SQL Using Column Aliases
Concatenation Operator
What is SQL
Using the Concatenation Operator
Role of SQL in RDBMS
Literal Character Strings
Data Types of Oracle
Using Literal Character Strings
Naming rules in Oracle
Duplicate Rows
What is SQL*PLUS
Eliminating Duplicate Rows
What is Metadata
Displaying Table Structure
What is Schema
Limiting Rows Using a Selection
Data Dictionary Tables
Limiting the Rows Selected
Using the WHERE Clause
SQL COMMANDS Character Strings and Dates
Comparison Conditions
Data Definition Language (DDL) Using Comparison Conditions
Data Retrieval Language (DRL) Other Comparison Conditions
Data Manipulation Language (DML) Using the BETWEEN Condition
Transaction Control Language (TCL) Using the IN Condition
Database Security and Privileges Using the LIKE Condition
(DCL) Using the NULL Conditions
Introduction to SQL Database Logical Conditions
Object: Using the AND Operator
Oracle Pre Defined Data types Using the OR Operator
DDL Commands Using the NOT Operator
Create, Alter (add, modify, rename, Rules of Precedence
drop)Columns, Rename, truncate, ORDER BY Clause
drop Sorting in Descending Order
DML-Insert, update, delete, merge Sorting by Column Alias
DCL-Grant, Revoke Sorting by Multiple Columns
TCL-Commit, Rollback, Savepoint Set Operators (UNION, UNION ALL,
INTERSECT, MINUS)
Constraints Elements of the Date Format Model
Using the TO_CHAR Function with
What are Constraints? Dates
Constraint Guidelines Using the TO_CHAR Function with
Defining Constraints Numbers
The NOT NULL Constraint Using the TO_NUMBER and
The UNIQUE Constraint TO_DATE Functions
The PRIMARY KEY Constraint General Functions
The FOREIGN KEY Constraint NVL Function
FOREIGN KEY Constraint Keywords Using the NVL Function
The CHECK Constraint Using the NVL2 Function
Adding a Constraint Syntax Using the NULLIF Function
Adding a Constraint Using the COALESCE Function
Dropping a Constraint Conditional Expressions
Disabling Constraints The CASE Expression
Enabling Constraints Using the CASE Expression
Cascading Constraints The DECODE Function
Viewing Constraints Using the DECODE Function
Viewing the Columns Associated Aggregate functions
with Constraints

Joins
Functions
Obtaining Data from Multiple Tables
SQL Functions Cartesian Products
Two Types of SQL Functions Generating a Cartesian Product
Single-Row Functions Types of Joins
Single-Row Functions Joining Tables Using Oracle Syntax
Character Functions What is an Equijoin?
Character Functions Additional Search Conditions Using
Case Manipulation Functions the AND Operator
Using Case Manipulation Functions Qualifying Ambiguous Column
Character-Manipulation Functions Names
Using the Character-Manipulation Using Table Aliases
Functions Joining More than Two Tables
Number Functions Non-Equijoins
Using the ROUND Function Retrieving Records with Non-
Using the TRUNC Function Equijoins
Using the MOD Function Outer Joins
Working with Dates Outer Joins Syntax
Arithmetic with Dates Using Outer Joins
Using Arithmetic Operators with Self Joins
Dates Joining a Table to Itself
Date Functions Practice 4, Part One: Overview
Using Date Functions Joining Tables Using SQL: 1999
Conversion Functions Syntax
Implicit Data Type Conversion Creating Cross Joins
Explicit Data Type Conversion Creating Natural Joins
Using the TO_CHAR Function with Retrieving Records with Natural
Dates Joins
 Creating Joins with the USING Rules for Performing DML
Clause Operations on a View
Retrieving Records with the USING Using the WITH CHECK OPTION
Clause Clause
Creating Joins with the ON Clause Denying DML Operations
Retrieving Records with the ON Removing a View
Clause Inline Views
Creating Three-Way Joins with the Sequence
ON Clause Sequence
INNER Versus OUTER Joins The CREATE SEQUENCE
LEFT OUTER JOIN Statement Syntax
RIGHT OUTER JOIN Creating a Sequence
FULL OUTER JOIN Confirming Sequences
NEXTVAL and CURRVAL Pseudo
columns
Subqueries
Using a Sequence
Using a Subquery to Solve a Modifying a Sequence
Problem Guidelines for Modifying a
Subquery Syntax Sequence
Using a Subquery Removing a Sequence
Guidelines for Using Subqueries Index
Types of Subqueries How Are Indexes Created?
Single-Row Subqueries Creating an Index
Executing Single-Row Subqueries When to Create an Index
Using Group Functions in a When Not to Create an Index
Subquery Confirming Indexes
The HAVING Clause with Function-Based Indexes
Subqueries Removing an Index
What is Wrong with this Synonyms
Statement? Creating and Removing
Will this Statement Return Rows? Synonyms
Multiple-Row Subqueries
Using the ANY Operator in Multiple- PL/SQL
Row Subqueries
Using the ALL Operator in Multiple- Introduction to PL/SQL
Row Subqueries Advantages of PL/SQL
Null Values in a Subquery The PL/SQL Block
Basic PL/SQL Block Structure
The Declaration Section
Database Objects
The Execution Section
Views The Exception Section
Why use Views? Generation Output
Simple Views and Complex Views
Creating a View
Variables And Constants
Retrieving Data from a View 1
Querying a View Data types
Modifying a View Scalar Data types
Creating a Complex View Composite Data types
 Variables (%type, %rowtype) Procedures Parameters
Variable Assignment Parameter Modes
Scope of Variables IN
Constants Out
Writing Sample programs IN Out
demonstrating all above Testing Procedure from anonymous
Functionalities blocks
Calling procedure from another
procedure
Conditions and Loops

Conditional Control User Defined Functions

Iterative Control
Guidelines Using Functions within Oracle
Sample, While, and For Loops Difference ways of Callings a
While Loop function
For Loop Named Notation
Functions
Function Parameters
Cursors
Referencing a Function
Implicit and Explicit Cursors
Cursors Actions Packages
Declaring a Cursors
Opening a Cursors Defining and Using Packages
Fetching Data from ad Cursor Advantages of Using Packages
Closing a Cursor Overloading
Cursor Attributes Dependency
Cursors and Loops Abstraction – Information Hiding
Cursors For Loops Inheritance
Defining Global Variables

Exception Handling
Database Triggers
Guidelines
Types of Exceptions Trigger Types
Named System Exceptions Trigger Events
Named User-Defined Exceptions Trigger Restrictions
Unnamed System Exceptions Trigger Body
Unnamed User-Defined Exceptions Complication of Database Triggers
SQL ERRM and SQL Code Trigger Syntax
Usage of Instead of Triggers

Procedures
An Overview of Oracle Large Objects
Writing Procedures using Oracle
SQL,PLSQL BLOB
Types of Program Units CLOB
Named and Unnamed Block PL/SQL Table
Structures VARRAY
Diff between Anonymous Blocks BFILE
and named Blocks Nested Tables