SQL Server

For clarifications, contact ANIL AILANI SIR-9819704277 OFFICE-022-66730730/9892841512

Click here to download the .pdf file of the contents

SQL

Duration : 25 hrs.

Description:

This course offers students an extensive introduction to the powerful SQL programming language. Students are taught to create and maintain database objects and to store, retrieve, and manipulate data. Demonstrations and hands-on practice reinforce the fundamental concepts.

Introduction

  • Introduction to Database Management Systems

Writing a Basic SQL Statement

  • Describing the SQL Select Capabilities
  • Executing a Basic Select Statement with the Mandatory Clauses

Restricting and Sorting Data

  • Limiting the Rows Retrieved by a Query
  • Sorting the Rows Retrieved by a Query

Single Row Functions

  • Describing Various Types of Functions Available in SQL
  • Using a Variety of Character, Number, and Date Functions in SELECT Statements
  • Explaining What the Conversion Functions Are and How They Are Used
  • Using Control Statements

Displaying Data from Multiple Tables

  • Writing SELECT Statements to Access Data from More Than One Table
  • Describing the Cartesian Product
  • Describing and Using the Different Types of Joins
  • Writing Joins Using the Tips Provided
  • Aggregating Data by Using Group Functions
  • Identifying the Different Group Functions Available
  • Explaining the Use of Group Functions
  • Grouping Data by Using the GROUP BY Clause.

Writing Joins

  • Joining more than two tables
  • Non-equijoins
  • Self joins
  • Comparing SQL:1999 to Oracle joins

Using Group Functions

  • Group BY clause
  • HAVING Function
  • Nesting group functions

Writing Sub queries

  • Describing the Types of Problems That Sub queries Can Solve
  • Describing Subqueries
  • Listing the Types of Subqueries
  • Writing Single-Row and Multi-Row Subqueries
  • Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved

Manipulating Data

  • Describing Each Data Manipulation Language (DML) Command
  • Inserting Rows into a Table
  • Updating Rows in a Table
  • Deleting Rows from a Table
  • Merging Rows into a Table
  • Controlling Transactions
  • Describing Transaction Processing
  • Describing Read Consistency and Implicit and Explicit Locking

Creating and Managing Tables

  • Describing the Main Database Objects
  • Creating Tables
  • Describing the Data Types
  • Altering Table Definitions
  • Dropping, Renaming, and Truncating Tables

Including Constraints

  • Describing Constraints
  • Creating and Maintaining Constraints

Creating Views

  • Describing Views and Their Uses
  • Creating a View
  • Retrieving Data by Means of a View
  • Inserting, Updating, and Deleting Data Through Views
  • Dropping Views
  • Altering the Definition of a View

Other Database Objects

  • Creating and Maintaining Indexes
  • Timestamp Conversion

Writing Explicit Cursors

  • Explicit Cursor Functions
  • Controlling Explicit Cursor
  • Declaring, Opening and Closing the Cursor
  • Fetching Data from the cursor
  • Explicit Cursor Attributes
  • % ISOPEN, % NOT FOUND and % ROW COUNT Attributes
  • Cursor and Records
  • Cursor FOR Loops
  • Cursor FOR Loops using Sub-Queries

Advanced Explicit Cursor Concepts

  • Cursors with Parameters
  • The FOR UPDATE Clause
  • The WHERE CURRENT OF Clause
  • Cursors with Sub-Queries

Creating Functions

  • Stored Functions
  • Creating a Function
  • Calling User Defined Functions
  • Restrictions on Calling Functions
  • Removing Functions

Manipulating LOBs

  • Managing LOBs.
  • Securing Bfiles.
  • Directory Objects
  • BFILENAME Function
  • Migrating Long to LOB.
  • DBMS_LOB Package.
  • Adding, populating & Removing LOB Column to Tables
  • Using LOBs in PL/SQL
  • Temporary LOB.

Database Triggers

  • Types of Triggers
  • Creating Triggers on DDL Statements
  • Creating Triggers on System Events