Querying MS SQL Server

By attending Querying MS SQL Server workshop, Participants will learn:

  • SELECT query writing
  • Query multiple tables
  • Sort and filter data
  • Data types in SQL Server
  • Data modification using T-SQL
  • Built-in functions
  • Group and aggregate data
  • Set operators
  • Window functions: ranking, offset, and aggregate
  • Pivot and group sets
  • T-SQL programming
  • Error handling and transaction implementation

In Querying MS SQL Server training course, Participants will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 2012 and 2014. Tools and skills that delegates will learn include: SQL Server Management Studio, T-SQL, SELECT statements in querying multiple tables, data types, data sorting and filtering, data manipulation language (DML), grouping and aggregating data, table expressions, set operators, window functions, T-SQL programming, error handlers, and transaction management in SQL Server.

  • Working knowledge of relational databases
  • Basic knowledge of Microsoft Windows operation system and its core functionality

  • Database administrators
  • Database developers
  • Business intelligence professionals
  • SQL power-users such as:
    • Report writers
    • Business analysts
    • Client application developers

COURSE AGENDA

  • Sort Data
  • Filter Data with a WHERE Clause
  • Filter with the TOP and OFFSET-FETCH Options
  • Work with Unknown and Missing Values
  • Joins
  • Query with Inner Joins and Outer Joins
  • Query with Cross Joins and Self Joins
  • Write Simple SELECT Statements
  • Eliminate Duplicates with DISTINCT
  • Column and Table Aliases
  • Write Simple CASE Expressions
  • Transact-SQL
  • Sets
  • Predicate Logic
  • Logical Order of Operations in SELECT Statements
  • SQL Server Architecture
  • SQL Server Editions and Versions
  • SQL Server Management Studio
  • SQL Server 2014 Data Types
  • Work with Character Data
  • Work with Date and Time Data
  • Insert Data
  • Modify and Delete Data
  • Write Queries with Built-In Functions
  • Conversion Functions
  • Logical Functions
  • Use Functions to Work with NULL
  • Use Aggregate Functions
  • Use the GROUP BY Clause
  • Filter Groups with HAVING
  • Write Self-Contained Sub-queries
  • Write Correlated Sub-queries
  • Use the EXISTS Predicate with Sub-queries
  • Use Derived Tables
  • Use Common Table Expressions
  • Use Views
  • Use Inline Table-Valued Functions
  • Write Queries with the UNION Operator
  • Use EXCEPT and INTERSECT
  • Use APPLY
  • Create Windows with OVER
  • Explore Window Functions including Ranking, Aggregate and Offset Functions
  • Write Queries with PIVOT and UNPIVOT
  • Work with Grouping Sets
  • Query Data with Stored Procedures
  • Pass Parameters to Store Procedures
  • Create Simple Stored Procedures
  • Work with Dynamic SQL
  • Use TRY/CATCH Blocks
  • Work with Error Information
  • Transactions and the Database Engine
  • Control Transactions
  • Isolation Levels
  • Factors in Query Performance
  • Display Query Performance Data
  • Query System Catalog Views and Functions
  • Execute System Stored Procedures
  • Query Dynamic Management Objects