SQL Training

SQL Course Reviews

The course is practical oriented. Good place for learning.

SQL Course Content

Introduction-1

• Objectives
• Oracle11g/12c versions
• Ms Sql Server
• MySql Hadoop MongoDB
• Relational and Object Relational Database Management System
• System Development Life Cycle
• Data Storage on Different Media
• Relational Database Concept
• Definition of a Relational Database
• Data Models Entity Relationship Model
• Entity Relationship Modeling Conventions
• Relating Multiple Tables
• Relational Database Terminology
• Relational Database Properties
• Communicating with a RDBMS Using SQL
• Relational Database Management System
• SQL Statements
• Tables Used in the Course

Introduction-2

• Database Models
• Normalization Rules
• Denormalization
• ACID Property
• Dr E F Codd Rules

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
• Eliminating Duplicate Rows
• Displaying Table Structure
• Interacting with Script Files

Restricting and Sorting Data

• Objectives
• 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

Single-Row Functions

• Objectives SQL Functions
• Two Types of SQL Functions
• Single-Row Functions
• Single-Row Functions
• Character Functions
• Character Functions
• Case Manipulation Functions
• Using Case 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
• 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
• RR Date Format
• Example of RR Date Format
• Nesting 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

Displaying Data from Multiple Tables

• Objectives
• Obtaining Data from Multiple Tables
• Cartesian Products
• Generating a Cartesian Product
• Types of Joins
• Joining Tables Using Oracle Syntax
• What is an Equijoin?
• Retrieving Records with Equijoins
• 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
• Joining Tables Using SQL: 1999 Syntax
• Creating Cross Joins
• Creating Natural Joins
• Retrieving Records with Natural Joins
• Creating Joins with the USING Clause
• INNER Versus OUTER Joins
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• Additional Conditions

Aggregating Data Using Group Functions

• Objectives
• What Are Group Functions?
• Types of Group Functions
• Group Functions Syntax
• Using the AVG and SUM Functions
• Using the MIN and MAX Functions
• Using the COUNT Function
• Using the DISTINCT Keyword
• Group Functions and Null Values
• Using the NVL Function with Group Functions
• Creating Groups of Data
• Creating Groups of Data: The GROUP BY Clause Syntax
• Using the GROUP BY Clause
• Grouping by More Than One Column
• Using the GROUP BY Clause on Multiple Columns
• Illegal Queries Using Group Functions
• Excluding Group Results
• Excluding Group Results: The HAVING Clause
• Using the HAVING Clause
• Nesting Group Functions

Subqueries

• Objectives
• 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

Manipulating Data

• Objectives
• Data Manipulation Language
• Adding a New Row to a Table
• The INSERT Statement Syntax
• Inserting New Rows
• Inserting Rows with Null Values
• Inserting Special Values
• Inserting Specific Date Values
• Creating a Script Copying Rows from Another Table
• Changing Data in a Table The UPDATE Statement Syntax
• Updating Rows in a Table
• Updating Two Columns with a Subquery
• Updating Rows Based on Another Table
• Updating Rows: Integrity Constraint Error
• Removing a Row from a Table The DELETE Statement
• Deleting Rows from a Table
• Deleting Rows Based on Another Table
• Deleting Rows: Integrity Constraint Error
• Using a Subquery in an INSERT Statement
• Using the WITH CHECK OPTION Keyword on DML Statements
• Overview of the Explicit Default Feature
• Using Explicit Default Values
• The MERGE Statement
• The MERGE Statement Syntax
• Merging Rows
• Database Transactions
• Advantages of COMMIT and ROLLBACK Statements
• Controlling Transactions
• Rolling Back Changes to a Marker
• Implicit Transaction Processing
• State of the Data Before COMMIT or ROLLBACK
• State of the Data after COMMIT
• Committing Data State of the Data After ROLLBACK
• Statement-Level Rollback
• Read Consistency
• Implementation of Read Consistency
• Locking
• Implicit Locking

Creating and Managing Tables

• Objectives
• Database Objects
• Naming Rules
• The CREATE TABLE Statement
• Referencing Another User’s Tables
• The DEFAULT Option
• Creating Tables
• Tables in the Oracle Database
• Querying the Data Dictionary
• Data Types
• DateTime Data Types
• TIMESTAMP WITH TIME ZONE Data Type
• TIMESTAMP WITH LOCAL TIME Data Type
• INTERVAL YEAR TO MONTH Data Type
• INTERVAL DAY TO SECOND Data Type
• Creating a Table by Using a Subquery Syntax
• Creating a Table by Using a Subquery
• The ALTER TABLE Statement
• Adding a Column
• Modifying a Column
• Dropping a Column
• The SET UNUSED Option
• Dropping a Table
• Changing the Name of an Object
• Truncating a Table
• Adding Comments to a Table

Including Constraints

• Objectives
• 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

Creating Views

• Objectives
• Database Objects
• What is a View?
• Why use Views?
• Simple Views and Complex Views
• Creating a View
• Retrieving Data from a View
• 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
• Top-N Analysis
• scriptlet tag
• expression tag
• declaration tag
• Performing Top-N Analysis
• Example of Top-N Analysis

Other Database Objects Objectives

• Database Objects
• What is a Sequence?
• The CREATE SEQUENCE Statement Syntax
• Creating a Sequence
• Confirming Sequences
• NEXTVAL and CURRVAL Pseudocolumns
• Using a Sequence
• Modifying a Sequence
• Guidelines for Modifying a Sequence
• Removing a Sequence What is an 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
• page directive
• Include directive
• taglib directive

Controlling User Access

• Objectives
• Controlling User Access
• Privileges
• System Privileges
• Creating Users
• User System Privileges
• Granting System Privileges
• Creating Users
• User System Privileges
• Granting System Privileges
• What is a Role?
• Creating and Granting Privileges to a Role
• Changing Your Password
• Object Privileges
• Granting Object Privileges
• Using the WITH GRANT OPTION and PUBLIC Keywords
• Confirming Privileges Granted
• How to Revoke Object Privileges
• Revoking Object Privileges
• Database Links

Using SET Operators

• Objectives
• The SET Operators
• Tables Used in This Lesson
• The UNION Operator
• Using the UNION Operator
• The UNION ALL Operator
• Using the UNION ALL Operator
• The INTERSECT Operator
• Using the INTERSECT Operator
• The MINUS Operator
• SET Operator Guidelines
• The Oracle Server and SET Operators
• Matching the SELECT Statements
• Controlling the Order of Rows

Oracle Datetime Functions

• Objectives
• TIME ZONES
• Oracle 11g Datetime Support
• TZ_OFFSET CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP 1
• DBTIMEZONE and SESSIONTIMEZONE
• EXTRACT 1
• TIMESTAMP Conversion Using FROM_TZ STRING To TIMESTAMP
• TO_TIMESTAMP_TZ
• Time Interval Conversion with TO_YMINTERVAL

Enhancements to the GROUP BY Clause

• Objectives
• Review of Group Functions
• Review of the GROUP BY Clause
• Review of the HAVING Clause
• GROUP BY with ROLLUP and CUBE Operators
• ROLLUP Operator
• ROLLUP Operator Example
• CUBE Operator
• CUBE Operator: Example
• GROUPING Function
• GROUPING Function: Example
• GROUPING SETS
• GROUPING SETS: Example
• Composite Columns
• Composite Columns: Example
• Concatenated Groupings
• Concatenated Groupings Example

Advanced Subqueries

• Objectives
• What Is a Subquery?
• Subqueries
• Using a Subquery
• Multiple-Column Subqueries
• Column Comparisons
• Pairwise Comparison Subquery
• Nonpairwise Comparison Subquery
• Using a Subquery in the FROM Clause
• Scalar Subquery Expressions
• Scalar Subqueries: Examples
• Correlated Subqueries
• Using Correlated Subqueries
• Correlated DELETE
• The WITH Clause
• WITH Clause: Example

Hierarchical Retrieval

• Objectives
• Sample Data from the EMPLOYEES Table
• Natural Tree Structure
• Hierarchical Queries
• Walking the Tree
• Walking the Tree: From the Bottom Up
• Walking the Tree: From the Top Down
• Ranking Rows with the LEVEL Pseudocolumn
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches

Extensions to DML and DDL Statements

• Objectives
• Review of the INSERT Statement
• Review of the UPDATE Statement
• Overview of Multitable INSERT Statements
• Overview of Multitable INSERT Statements
• Types of Multitable INSERT Statements
• Multitable INSERT Statements
• Unconditional INSERT ALL
• Conditional INSERT ALL
• Conditional FIRST INSERT
• Pivoting INSERT
• External Tables
• Creating an External Table
• Example of Creating an External Table
• Querying External Tables
• CREATE INDEX with CREATE TABLE Statement