SQL Queries @ Learning Tree

Learning Tree ,London, United Kingdom

Mar 2005 – London, United Kingdom

I attended this Developing SQL Queries for Microsoft SQL Server to optimise the performance of UKTV’s websites.

Course content:

  • Introduction and Overview
  • SQL fundamentals
    • Retrieving data with SELECT
    • Expressions
    • Literals
    • Handling NULLs properly
  • Executing queries
    • Analysing query plans
    • Enhancing query performance
    • Selecting the best alternatives
    • Avoiding errors and pitfalls
  • Querying Multiple Tables
  • Implementing various types of joins
    • Inner joins
    • Cross joins
    • Left, right and full outer joins
    • Equijoins vs. theta joins
    • Adding filter conditions to outer joins
  • Writing self-joins
    • Joining a table to itself
    • Chaining self-joins
    • Solving time-interval problems
  • Combining queries with set operators
    • UNION
    • UNION ALL
    • INTERSECT
    • EXCEPT
  • Scalar and Aggregate Functions
  • Taking advantage of scalar functions
    • Converting datatypes
    • Performing calculations on dates and times
    • Extracting date and time components
    • Manipulating strings
    • Choosing the right function for the job
  • Summarising data with aggregate functions
    • COUNT
    • SUM
    • AVG
    • MIN
    • MAX
    • Managing NULLs
    • Suppressing duplicates
  • Grouping data
    • GROUP BY and GROUP BY ALL
    • Applying conditions with HAVING
  • Extending group queries
    • Nesting grouped aggregates
    • Joins and grouping
  • Building crosstab reports
    • Using CASE to turn rows into columns
    • Applying PIVOT
  • Performing Analysis with Analytic Functions
  • The OVER clause
    • Specifying the ordering before applying the function
    • Splitting the result set into logical partitions
  • Calculating ranks
    • RANK and DENSE_RANK
    • ROW_NUMBER with ordered sets
  • Extending the use of aggregates
    • Partitioning in multiple levels
    • Comparing row and aggregate values
  • Building Subqueries
  • Simple subqueries
    • Subqueries in conditions and column expressions
    • Creating multilevel subqueries
    • Avoiding problems when subqueries return NULLs
  • Correlated subqueries
    • Accessing values from the outer query
    • Avoiding accidental correlation
  • Common table expressions
    • Reusable and recursive subqueries
    • Traversing hierarchies
  • Breaking Down Complex Queries
    • Overcoming SQL limitations
    • Reducing complexity and improving performance
    • Exploring alternatives for decomposing: temporary tables, views, common table expressions