Oracle SQL

Faculty profile:

Real time professionals with 10+ years of experience.

Duration:

15 Hours

Audience:

Freshers, Analysts,Support Engineers

Software needed on student PC:

Oracle Database 11g R2

Course Topics:

Introduction

SQL Overview

Outlining SQL as the cornerstone of database activity

Applying the ANSI/ISO standard

Describing the fundamental building blocks: Tables, Columns, Primary Keys, and Foreign Keys


Building the Database Schema


Creating Tables and Columns

Building tables with CREATE TABLE

Modifying table structure with ALTER TABLE

Adding columns to an existing table

Removing tables with DROP TABLE


Protecting data integrity with constraints

Guaranteeing uniqueness with primary key constraints

Enforcing integrity with foreign key constraints

Imposing business rules with check constraints

Enabling and disabling constraints

Removing constraints with ALTER TABLE


Improving Performance with Indexes

Expediting data retrieval with indexes

Recommending guidelines for index creation


Manipulating Data

Adding table rows with INSERT

Changing row content with UPDATE

Removing rows with DELETE


Applying transactions

Atomic Consistent Isolated Durable (ACID) rules

Controlling transactions with COMMIT and ROLLBACK


Writing Single Table Queries

Retrieving data with SELECT

Restricting rows with the WHERE filter

Sorting the result with ORDER BY

Handling NULL values in expressions

Avoiding NULL value pitfalls in filter conditions


Querying Multiple Tables

Matching related rows with INNER JOIN

Including nonmatched rows with OUTER JOIN

Creating a Cartesian product with CROSS JOIN


Combining results with set operators

Stacking results with UNION

Identifying matching rows with INTERSECT

Utilizing EXCEPT to find nonmatching rows


Employing Functions in Data Retrieval


Processing data with row functions

Conditional formatting with the CASE expression

Utilizing the CASE expression to simulate IF tests

Dealing with NULL values


Performing analysis with aggregate functions

Summarizing data using SUM, AVG and COUNT

Finding the highest/lowest values with MAX and MIN

Defining the summary level with GROUP BY

Applying filter conditions with HAVING


Constructing Nested Queries


Applying subqueries in filter conditions

Correlated vs. noncorrelated subquerie

Testing the existence of rows


Including subqueries in expressions

Placing subqueries in the column list

Creating complex expressions containing subqueries

Handling subqueries that return no rows


Developing In-Line and Stored Views


Breaking down complex problems

Selecting data from a query result set

Subqueries in the FROM clause


Creating views in a database

Building reusable code

Updateable vs. non-updateable views