Advanced SQL Querying with T-SQL

1.      Logical Query Processing

Logical Query Processing Phases

                                Brief description of Logical Query processing Phases

Sample Query based on customers/orders scenario

Logical Query processing phase details

                                Step 1: Performing a Cartesian Product (Cross Join)

                                Step 2: Applying the ON filter (Join Condition)

                                Step 3: Adding outer rows

                                Step 4: Applying the WHERE filter

                                Step 5: Grouping

                                Step 6: Applying the CUBE or ROLLUP option

                                Step 7: Applying the HAVING filter

                                Step 8: Processing the SELECT list

                                Step 9: Applying the DISTINCT clause

                                Step10; Applying the ORDER By clause

                                Step 11: Applying the TOP option

New Logical Processing Phases in SQL server 2005

                                Table Operators

                                OVER clause

                                Set Operations

2.      Physical Query Processing

Flow of Data during query processing

        Compilation

                        Algebrizer

                        Optimization

                        Working with the query plan

                        Update plans

3.      Query Tuning

Tuning methodology

                        Analyze waits at the Instance level

                        Correlate waits with queues

                        Determine course of action

                        Drill down to the database/file level

                        Tune Indexes/Queries

Tools for Query Tuning

                        Syscacheobjects

                        Clearing the cache             

                        Dynamic management objects

                        STATISTICS IO

                        Measuring the run time of queries

                        Analyzing execution plans

                        Hints

                        Traces/Profiler

                        Database Engine Tuning Advisor

Index Tuning

                        Table and index structures

                        Index access methods

                        Index optimization scale

                        Fragmentation

                        Partitioning

                        Preparing sample data

                        Data preparation

                        TABLESAMPLE

                        An examination of set-based vs Iterataive/procedural approaches and tuning exercise

4.      Subqueries, table expressions and ranking functions

Subqueries

                Self contained subqueries

                Correlated Subqueries

                Misbehaving subqueries

                Uncommon predicates

Table expressions

                Derived tables

                Common table expressions (CTE)

Analytical Ranking functions

                Row number

                Rank and Dense rank

                NTILE

Auxiliary table of numbers

Existing and missing ranges (also known as Islands and Gaps)

                Missing range (also known as gaps)

                Existing ranges (also known as Islands)

5.      Joins and set operations

Joins

                Old style vs new style

                Fundamental join types

                Further examples of joins

                Sliding total of previous year

                Join algorithms

                Separating elements

Set operations

                UNION

                EXCEPT

                INTERSECT

                Precedence of set operations

                Using INTO with set operations

                Circumventing unsupported logical phases

6.      Aggregating and pivoting data

OVER Clause

Tiebrakers

Running Aggregations

                Cumulative aggregations

                Sliding aggregations

                Year to date (YTD)

Pivoting

                Pivoting attributes

                Relational Division

                Aggregating data

Unpivoting

Custom aggregations

                Custom aggregations using pivoting

                User defined aggregates (UDA)

                Specialized solutions

Histograms

Grouping Factor

CUBE and ROLLUP

                CUBE

                ROLLUP

7.      TOP and APPLY

SELECT TOP

                TOP and determinism

                TOP and Input Expressions

                TOP and modifications

APPLY

Solutions to common problems using TOP and APPLY

                                TOP n for each group

                                Matching current and previous occurrences

                                Paging

                                Random rows

                                Median

8.      Data modification

Inserting data

                               SELECT INTO

                               INSERT EXEC

                               Inserting new rows

                               INSERT with OUTPUT

                               Sequence mechanisms

Deleting Data

                                TRUNCATE vs DELETE

                                Removing rows with duplicate data

                                DELETE using joins

                                DELETE with OUTPUT

Updating Data

                                UPDATE using joins

                                UPDATE with OUTPUT

                                SELECT and UPDATE Statement assignments

Other performance considerations

9.      Graphs , Trees and Hierarchies and Recursive Queries

Terminology

                Graphs

                Trees

                Hierarchies

Scenarios

                Employee Organizational Chart

                Bill of Materials (BOM)

                Road System

Iteration/Recursion

                Subordinates

                Ancestors

                Subgraph/Subtree with path Enumeration

                Sorting

                Cycles

Materialized path

                Maintaining data

                Querying

Nested Sets

                Assigning left and right values

                Querying

Transitive Closure

                Directed Acyclic Graph

A. Logic Puzzles

                                Puzzle 1: Medication tablets

                                Puzzle 2: Chocolate bar

                                Puzzle 3: to a T

                                Puzzle 4: On the dot

                                Puzzle 5: Rectangles in a square

                                Puzzle 6; measuring time by burning ropes

                                Puzzle 7: Arithmetic maximum calculation

                                Puzzle 8: Covering a chessboard with Domino tiles

                                Puzzle 9: The missing buck

                                Puzzle 10: Flipping lamp switches

                                Puzzle 11: Cutting a stick to make a triangle

                                Puzzle 12: Rectangle within a circle

                                Puzzle 13: Monty Hall problem

                                Puzzle 14:Piece of cake

                                Puzzle 15: Cards facing up

                                Puzzle 16: Basic arithmetic

                                Puzzle 17: Self-Replicating code (Quine)

                                Puzzle 18: Hiking a mountain

                                Puzzle 19: Find the pattern in the sequence