Oracle Database

SQL Fundamentals

Chapter 1: Introduction of DBMS

Introduction to Oracle Database

Oracle internet Platform

SDLC approach theory

Relational Database concept and its definition

ER Model and its conventions

How to relate multiple tables.

Relational Database terminology

What is SQL and its types

Chapter 2: Retrieving data using SQL Select Statement

Basic Select statement

Selecting customized rows, cols

Column heading defaults

Arithmetic Expressions and its operators

Operator precedence

Defining Null value

Null values in Arithmetic expressions

Defining column alias

Concatenation operators

Literal character strings

quote character (q)

Duplicate rows

Describe command

Chapter 3: Restricting and sorting data

Limiting rows using a selection

using the where clause

Character Strings and Date

Comparison conditions

Using BETWEEN, IN, LIKE, ISNULL etc conditions

Using AND, OR, NOT operators

Rules of precedence

Using ORDER BY clause

Sorting

Substitution variable

Using DEFINE Command

Using VERIFY Command

Chapter 4: Using Single-Row Functions to Customize Output

SQL Functions

Two types of SQL Functions

Single-Row Functions

Case-Manipulation Functions

Character-Manipulation Functions

Number Functions like ROUND, TRUNC, MOD etc

Working with Dates

Arithmetic with Dates

Date operators and functions

Conversion functions

Implicit and Explicit datatype conversion (TO_DATE, TO_CHAR, TO_NUMBER functions)

RR date format

Nesting of functions

NVL, NVL2, NULLIF, COALESCE functions

CASE Expressions

DECODE function

Chapter 5: Aggregated Data using the Group Functions

Introduction to group functions

Using SUM, AVG, MAX, MIN, COUNT, DISTINCT functions

Group functions and Null values

Creating groups of data using GROUP BY clause

Grouping by more than one column

Group functions ambiguities

Restricting group results

HAVING clause

Nesting Group functions

Chapter 6: Displaying data from Multiple Tables

Obtaining data from multiple tables

Types of joins

Creating Natural Join

Creating joins with the USING clause

Querying from ambiguous column names

Use of table aliases

Creating joins with the ON clause

Applying additional conditions to a join

3 way joins

Nonequi Joins

Inner, Outer Joins

Left Outer, Right Outer, Full Outer joins

Cartesian products

Cross Joins

Chapter 7: Use of Subqueries

Using a Subquery

Guidelines for using Subqueries

Types of Subqueries

Single row Subquery

Multiple row Subquery

Using Group functions in Subqueries

Using Having clause in Subqueries

Using ANY / ALL operators in Subqueries

Null values in Subqueries

Chapter 8: Use of Set Operators

UNION, UNION ALL, INTERSECT, MINUS operators

Controlling the order of rows

Chapter 8: Manipulating Data

What are DML statements?

Adding new row in a table using INSERT statement

Inserting rows with Null, Date, Special Values

Copying rows from another table

Modify data in a table using UPDATE statement

Updating two columns using Subquery

Updating rows based o another table

Removing a row from a table

Deleting rows based on another table

TRUNCATE Statement

Using a subquery in an INSERT Command

Database Transaction concept

Commit and Rollback transactions

Savepoints

Implicit transaction processing

State of the data before commit or rollback

State of data after commit

Statement-level rollback

Read consistency concept

Chapter 9: Creating and Managing Tables

What are Database Objects?

Naming rules

CREATE TABLE statement

DEFAULT values

Using different Datatypes

Use of Constraints

NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY etc

Violating Constraints

Creating a Table using Subqueries

ALTER TABLE statement

Dropping tables

Chapter 10: Schema Objects

What are Views? Its advantages

Simple and Complex Views

Create, Modify, Delete views

DML operations on Views

Using WITH CHECK OPTION

Denying DML operations on a Table

What are Sequences? Its advantages

Creating Sequence

NEXTVAL and CURRVAL

Caching Sequence values

Modifying a sequence

What are Indexes?

Creating / Removing Indexes

Synonyms

Chapter 11: Data Dictionary

Data Dictionary Structure

Dictionary views

USER_OBJECTS, ALL_OBJECTS and other views

Extract Table / Column / Constraint / Sequence / Synonym information