Objectives
– This course will enable delegates to query a SQL Server 2016 database. This includes querying joined tables in Transact-SQL. The course will cover the structure of a SQL Server 2016 database. Delegates will be able to basic and advanced querying technique. They will also insert, update and delete data in tables. They will cover the new features in SQL Server 2016 that are not available in SQL Server 2012.
Duration:
2 Days
Audience
This course is designed for those who are responsible for querying database objects and programming SQL Server databases by using Transact-SQL.
Prerequisites
An understanding of basic relational database concepts, including:
- Logical and physical database design
- Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many)
- How data is stored in tables (rows and columns)
5 Top Takeaways from the Course
- Improve database design
- Improve query performance
- Create complex queries across multiple tables
- Transform raw data to meaningful information
- Program databases for resilience and growth
Module 1: SQL Server 2016 Overview
- SQL Server 2016 Components and Architecture
- SQL Server 2016 Developer Tools
- SQL Server 2016 Security Implementation
Module 2: Language Features
- SQL Server Programming Tools
- Elements of Transact-SQL
- SQL Server Object Names
- Additional Language Elements
- Local Variables
- Operators
- Functions
- Ways to Execute Transact-SQL Statements
- New Transact-SQL (T-SQL) Features in 2016
Module 3: Basic Query Techniques
- Using SELECT to retrieve rows and columns
- Grouping rows
- Retrieving Specific Rows of Data Using the WHERE Clause
- Filter Data by Using Logical Operators
Module 4: Advanced Query Techniques
- Using Aggregate Functions Native to SQL Server
- Sub Queries
- Creating Pivot Queries
- Writing and Reading XML
Module 5: Querying Multiple Tables
- Inner Joins
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
Module 6: Inserting Data into Tables
- Inserting a Single Row into a Table
- Inserting Multiple Rows into a Table
- Differentiating Various INSERT Statements
- Importing Data to Tables
Module 7: Updating Data in Tables
- Updating a Single Row into a Table
- Updating Multiple Rows into a Table
- Updating Rows Based on Data in Other Tables
Module 8: Deleting Data from Tables
- Deleting Rows from a Table
- Truncating a Table
- Deleting Rows Based on Data in Other Tables