Training Schedule Training Schedule

(6552e) Querying Microsoft SQL Server 2014 (eLearning)
Code: 6552e v12
Type: null
Tuition (USD): $895
Length: 90 Days
Have a question?

Contact Us

character(s) remaining

Location Date Guaranteed to Run
eLearning, US 12/16/2019
eLearning, US 12/17/2019
eLearning, US 12/18/2019
eLearning, US 12/19/2019
eLearning, US 12/20/2019
eLearning, US 12/21/2019
eLearning, US 12/22/2019
eLearning, US 12/23/2019
eLearning, US 12/24/2019
eLearning, US 12/25/2019
eLearning, US 12/26/2019
eLearning, US 12/27/2019
eLearning, US 12/28/2019
eLearning, US 01/01/2020
eLearning, US 01/02/2020
eLearning, US 01/03/2020
eLearning, US 01/04/2020

Not finding any suitable dates? Contact us for additional availability: training@dunnsolutions.com

Skills Gained:
1. SELECT query writing 2. Query multiple tables 3. Sort and filter data 4. Data types in SQL Server 5. Data modification using T-SQL 6. Built-in functions 7. Group and aggregate data 8. Set operators 9. Window functions: ranking, offset, and aggregate 10. Pivot and group sets 11. T-SQL programming 12. Error handling and transaction implementation
Who Should Attend?
1) Database administrators 2) Database developers 3) Business intelligence professionals 4) SQL power-users such as: a) Report writers b) Business analysts c) Client application developers
Prerequisites:
  • Working knowledge of relational databases
  • Basic knowledge of Microsoft Windows operation system and its core functionality
What You Will Learn:
  • COURSE OUTLINE
  • 1. Microsoft SQL Server 2014
  • SQL Server Architecture
  • SQL Server Editions and Versions
  • SQL Server Management Studio
  • 2. Transact-SQL Querying
  • Transact-SQL
  • Sets
  • Predicate Logic
  • Logical Order of Operations in SELECT Statements
  • 3. Write SELECT Queries
  • Write Simple SELECT Statements
  • Eliminate Duplicates with DISTINCT
  • Column and Table Aliases
  • Write Simple CASE Expressions
  • 4. Querying Multiple Tables
  • Joins
  • Query with Inner Joins and Outer Joins
  • Query with Cross Joins and Self Joins
  • 5. Sorting and Filtering Data
  • Sort Data
  • Filter Data with a WHERE Clause
  • Filter with the TOP and OFFSET-FETCH Options
  • Work with Unknown and Missing Values
  • 6. SQL Server 2014 Data Types
  • SQL Server 2014 Data Types
  • Work with Character Data
  • Work with Date and Time Data
  • 7. DML to Modify Data
  • Insert Data
  • Modify and Delete Data
  • 8. Built-In Functions
  • Write Queries with Built-In Functions
  • Conversion Functions
  • Logical Functions
  • Use Functions to Work with NULL
  • 9. Grouping and Aggregating Data
  • Use Aggregate Functions
  • Use the GROUP BY Clause
  • Filter Groups with HAVING
  • 10. Sub-queries
  • Write Self-Contained Sub-queries
  • Write Correlated Sub-queries
  • Use the EXISTS Predicate with Sub-queries
  • 11. Table Expressions
  • Use Derived Tables
  • Use Common Table Expressions
  • Use Views
  • Use Inline Table-Valued Functions
  • 12. Set Operators
  • Write Queries with the UNION Operator
  • Use EXCEPT and INTERSECT
  • Use APPLY
  • 13. Window Ranking, Offset, and Aggregate Functions
  • Create Windows with OVER
  • Explore Window Functions including Ranking, Aggregate and Offset Functions
  • 14. Pivoting and Grouping Sets
  • Write Queries with PIVOT and UNPIVOT
  • Work with Grouping Sets
  • 15. Query Data with Stored Procedures
  • Query Data with Stored Procedures
  • Pass Parameters to Store Procedures
  • Create Simple Stored Procedures
  • Work with Dynamic SQL
  • 16. Programming with T-SQL
  • 17. Implement Error Handling
  • Use TRY/CATCH Blocks
  • Work with Error Information
  • 18. Implement Transactions
  • Transactions and the Database Engine
  • Control Transactions
  • Isolation Levels
  • 19. Appendix 1: Improve Query Performance
  • Factors in Query Performance
  • Display Query Performance Data
  • 20. Appendix 2: Query SQL Server Metadata
  • Query System Catalog Views and Functions
  • Execute System Stored Procedures
  • Query Dynamic Management Objects
  • LABS
  • Lab 1: Work with SQL Server 2014 Tools
  • SQL Server Management Studio
  • Create and Organize T-SQL Scripts
  • Books Online
  • Lab 2: Transact-SQL Querying
  • Execute Basic SELECT Statements
  • Execute Queries which filter data using predicates and sort data using ORDER BY
  • Lab 3: Write Basic SELECT Statements
  • Write Simple SELECT Statements
  • Eliminate Duplicates using Distinct
  • Table and Column Aliases
  • Simple CASE Expression
  • Lab 4: Query Multiple Tables
  • Write Queries that use Inner Joins, Multiple-Table Inner Join, Self Joins, Outer Joins, and Cross Joins
  • Lab 5: Sort and Filter Data
  • Write Queries that filter data using a WHERE Clause, ORDER BY Clause, TOP Option and OFFSET-FETCH Clause
  • Lab 6: SQL Server 2014 Data Types
  • Write Queries that return date and time data and character data
  • Write Queries that use date, time, and character functions
  • Lab 7: Modify Data using DML
  • Insert, Update and Delete Data
  • Lab 8: Built-In Functions
  • Write Queries which use conversion and logical functions
  • Write Queries which test for nullability
  • Lab 9: Group and Aggregate Data
  • Write Queries which use the GROUP BY Clause
  • Write Queries which use aggregate and distinct aggregate functions
  • Write Queries which filter group with the HAVING Clause
  • Lab 10: Sub-Queries
  • Write Queries which use self-contained, scalar and multi-result sub-queries
  • Write Queries which use correlated sub-queries and EXISTS predicate
  • Lab 11: Table Expressions
  • Write Queries which use Views, Derived Tables and Common Table Expressions
  • Write Queries which use Inline Table-Valued Functions
  • Lab 12: Set Operators
  • Write Queries which use UNION set operators and UNION ALL multi-set operators
  • Write Queries which use CROSS APPLY and OUTER APPLY operators
  • Write Queries which use EXCEPT and INTERSECT operators
  • Lab 13: Windows Ranking, Offset and Aggregate Functions
  • Lab 14: Pivoting and Grouping Sets
  • Lab 15: Execute Stored Procedures
  • Invoke stored procedures using the EXECUTE statement
  • Pass parameters to stored procedures
  • Execute system stored procedures
  • Lab 16: Programming with T-SQL
  • Declare Variables and Delimiting Batches
  • Control-of-Flow Elements
  • Generate Dynamic SQL
  • Synonyms
  • Lab 17: Implement Error Handling
  • Redirect Errors with TRY/CATCH
  • Pass an Error Message Back to a Client using THROW
  • Lab 18: Implement Transactions
  • Control transactions with BEGIN, COMMIT and ROLLBACK
  • Add error handling to a CATCH block
  • Lab 19: Improve Query Performance
  • View Query Execution Plans
  • View Index Usage and Using SET STASTICS Statements
  • Lab 20: Query SQL Server Metadata
  • Query System Catalog Views
  • Query System Functions
  • Query System Dynamic Management Views
Course Info:
Duration: 47 hours

Course notes and announcements:

1) In this course, you will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 2014. This is the foundational course for all SQL server related disciplines: database administration, database development and business intelligence. Tools and skills you 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.

This course is designed for customers interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features of SQL Server 2014 as well as the important capabilities across the SQL Server data platform.

This course incorporates material from the Official Microsoft Learning Product 20461: Querying Microsoft SQL Server 2014. It covers the skills and knowledge measured by Exam 70-461 and along with on-the-job experience, helps you prepare for the exam.

This course is also available in the On-Demand delivery format with digital Microsoft Official Courseware (dMOC).

RELATED CERTIFICATIONS:
MCSA: SQL Server 2012/2014

Follow-On Courses:
Administering Microsoft SQL Server 2014 Databases (M20462)
Implementing a Data Warehouse with Microsoft SQL Server 2014 (M20463)
Developing Microsoft SQL Server 2014 Databases (M20464)
Designing a Data Solution with Microsoft SQL Server 2014 (M20465)

* Terms and conditions may apply. Learn more about our Microsoft training exclusives by contacting training@dunnsolutions.com

eLearning:
This course provides the same superior content as the instructor-led training delivered via the Internet so you can learn at your own pace.

These Self-Paced delivery format includes hundreds of titles designed to support your learning initiatives by focusing on both interactivity and expert instruction. These courses combine the knowledge of our subject matter experts with sophisticated development tools and techniques to create interactive self-paced content that provides flexibility and stretches your training dollars.

These award-winning Self-Paced training effectively incorporates the latest instructional design methodologies and mirrors the content we have used to train millions of IT and business professionals. WThese elf-paced training use the same instructional design and content from the instructor-led courses in developing these Self-Paced courses.

Benefits:
Study topical, results-oriented course content.
Learn at your own pace.
Have convenient access to hands-on labs.
Enjoy flexibility and savings.
Support corporate green initiatives.
Learn basic Transact- SQL queries for SQL Server 2012 and 2014.