Querying SQL Server

Map Unavailable

Date/Time
Date(s) - 25/06/2020 - 26/06/2020
9:00 am - 4:00 pm

Categories


This 2-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for  SQL Server. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.
Note: This course is designed for customers who are interested in learning Transact SQL (Microsoft’s implementation of SQL) however the skills attained in this course can be applied to nearly all other Database systems.

Audience Profile

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused; namely, report writers, business analysts and client application developers.

At Course Completion

After completing this course, students will be able to:

Describe the basic architecture and concepts of Microsoft SQL Server 2014.
Understand the similarities and differences between Transact-SQL and other computer languages.
Describe key capabilities and components of SQL Server.
Write a single table SELECT statement.
Write a multi-table SELECT statement.
Write SELECT statements with filtering and sorting.
Describe how SQL Server uses data types.
Write DML statements.
Write queries that use built-in functions.
Write queries that aggregate data.
Write subqueries.
Create and implement views and table-valued functions

Course Outline

Module 1: Introduction to T-SQL Querying

This module describes the elements of T-SQL and their role in writing queries. Describe the use of sets in SQL Server. Describe the use of predicate logic in SQL Server. Describe the logical order of operations in SELECT statements.

Lessons

Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statement

LAB: Introduction to SQL Querying

Executing Basic SELECT Statements
Executing Queries that Filter Data using Predicates
Executing Queries That Sort Data Using ORDER BY

Module 2: Writing SELECT Queries

This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table

Lessons

Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions

Lab : Writing Basic SELECT  Statements

Writing Simple SELECT Statements
Eliminating Duplicates Using DISTINCT
Using Column and Table Aliases
Using a Simple CASE Expression

Module 3:  Querying Multiple Tables

This module describes how to write queries that combine data from multiple sources in Microsoft SQL Server

Lessons

Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins

Lab : Querying Multiple Tables

Writing Queries that use Inner Joins
Writing Queries that use Multiple Table Inner Joins
Writing Queries that use Self-Joins
Writing Queries that use Outer Joins
Writing Queries that use Cross Join

Module 4: Sorting and Filtering Data

This module describes how to implement sorting and filtering

Lessons

Sorting Data
Filtering Data with Predicates
Working with Unknown Values

Lab : Sorting and Filtering Data

Writing Queries that Filter Data using a WHERE Clause
Writing Queries that Sort Data Using an ORDER BY Clause
Writing Queries that Filter Data Using the TOP Option

Module 5:  Working with SQL Server Data Types

This module introduces the data types SQL Server uses to store data

Lessons

Introducing SQL Server Data Types
Working with Character Data
Working with Date and Time Data

Lab : Working with SQL Server Data Types

Writing Queries that Return Date and Time Data
Writing Queries that use Date and  Time Functions
Writing Queries That Return Character Data
Writing Queries That Return  Character Functions

Module 6: Using DML to Modify Data

This module describes how to create DML queries, and why you would want to.

Lessons

Adding Data to Tables
Modifying and Removing Data
Generating automatic column values

Lab : Using DML to Modify Data

Inserting Records with DML
Updating and Deleting Records  Using DML

Module 7:  Using Built-In Functions

This module introduces some of the many built in functions in SQL Server.

Lessons

Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NUL

Lab : Using Built-In Functions

Writing Queries That Use  Conversion Functions
Writing Queries that use Logical Functions
Writing Queries that Test for  Nullability

Module 8: Grouping and Aggregating Data

This module describes how to use aggregate functions.

Lessons

Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING

Lab :  Grouping and Aggregating Data

Writing Queries That Use the  GROUP BY Clause
Writing Queries that Use Aggregate Functions
Writing Queries that Use Distinct  Aggregate Functions
Writing Queries that Filter Groups with the HAVING Clause

Module 9: Using Subqueries

This module describes several types of subquery and how and when to use them.

Lessons

Writing Self-Contained Subqueries

Lab : Grouping and Aggregating Data

Writing Queries That Use Self Contained Subqueries

Module 10: Implementing Transactions

This module describes how to implement transactions.

Lessons

Transactions and the database engines
Controlling transactions

Lab : Implementing Transactions

Controlling transactions with  BEGIN, COMMIT, and ROLLBACK

 

 

NOTE: This course will be given online however if you wish to do the session at Quill please contact us directly as spaces are limited due to the COVID-19 precautions that need to be taken.

Bookings

This event is fully booked.