Querying Microsoft SQL Server 2012 – Version 1.0

Download the latest Querying Microsoft SQL Server 2012 course’s tutorials that are being authored and delivered by the MCT certified instructor Ahmad Adel Gad.

ZIP Download: Querying Microsoft SQL Server 2012 – Full Tutorials

ISO Download: Querying Microsoft SQL Server 2012 – Full Tutorials

Download The Course Outline Only – PowerPoint

Course Outline:

  1. Overview

    •About SQL Language (T-SQL)
    •Introduction to Microsoft SQL Server 2012
  2. Select Statement – Retrieve Data

    •Fixed Values
    •Variables
    •Calculated Values
    •All Fields From Specific Table
    •Specific Fields From Specific Table
    •Concatenated Fields – Alias Name
    •Calculated Value On Specific Fields
    •Specific Columns From Table Alias Name
    •Value Returned From Function
  3. Select Statement – Filtering Data

    •Comparison operators
    •Logical operators
  4. Working with NULL Values

    •IS   Null – Comparison
    •ANSI_NULLS Settings
    •ISNULL() Function
    •NullIF() Function
    •Coalesce() Function
  5. Formatting Result Sets

    •Sorting Data
    •Eliminating Duplicate Rows
  6. System Functions

    •Aggregate Functions
    •Scalar Functions
    •Converting Functions
  7. Combining and Limiting Result Sets

    •Join
    Inner/Where Clause
    Outer (Full/Left/Right)
          Cross
          Self Join
    •Data Samples (TableSample Operator)
    •Cube
    •Union/Except/Intersect
    •Apply (Cross/Outer)
  8. Grouping Data

    •Group By
    •RollUP
    •Cube
    •Pivot – UnPivot
  9. Using Table Expressions

    •Sub Queries
    •Derived Tables
    •Common Table Expressions (CTE)
    •Views
    •Table-Valued Functions
  10. Windowing and Paging Data (Over Clause)

    •Paging Functions
          Offset – Fetch
          Row_Number
    •Over Clause
          Ranking Functions
          Analytic Functions
          Aggregate Functions
          NEXT VALUE FOR
  11. Querying SQL Server Metadata

    •System Variable
    •System Stored Procedures
    •System Catalog Views
    •System Catalog Functions
    •Dynamic Management Views (DMV)
    •Dynamic Management Functions (DMF)
  12. Data Manipulation Language (DML)

    •Insert
    •Update
    •Delete
    •Output Clause
  13. Querying Full-Text Index Search

    •Contains
    •FreeText
  14. Distributed Queries

    •OpenDataSource Function
    •OpenRowSet Function
    •Configure ‘Ad Hoc Distributed Queries’ Settings
  15. Implementing Error Handling (Handling Exceptions)

    •@@Error Variable
    •Try … Catch
    •Throw Exceptions
    •RaiseError
    •Throw
    •System Error Information Functions
    •Error Severities
  16. Implementing Transactions (TCL)

    •Autocommit Transactions
    •Implicit Transactions
    •Explicit Transactions
    •XACT_ABORT Setting
    •Implementing Transactions with Exception Handling
    •XACT_STATE() Function
    •Isolation Levels

Important remarks:

1. The included demos are not completed yet and should be enhanced and completed with later versions.

2. The sample databases should be downloaded separately (the direct download links included inside the tutorials CD).

3. My references are mainly the Microsoft Official Materials and MSDN.

4. All the included demos are tested by me on SQL Server 2012.

If you have any comment or need a further assistant please don’t hesitate to put your comment and I’ll do my best.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s