Microsoft Querying Data with Transact-SQL - Only 3 Days

Seven reasons why you should sit your Querying Data with Transact-SQL course with Firebrand Training

Find Out How We Help You To Learn New Skills Quickly

  1. You'll be Querying Data with Transact-SQL certified in just 3 days. With us, you’ll be Querying Data with Transact-SQL trained in record time
  2. Our Querying Data with Transact-SQL course is all-inclusive. A one-off fee covers all course materials, exams, accommodation and meals. No hidden extras
  3. Pass Querying Data with Transact-SQL first time or train again for free. This is our guarantee. We’re confident you’ll pass your course first time. But if not, come back within a year and only pay for accommodation, exams and incidental costs
  4. You’ll learn more Querying Data with Transact-SQL. A day with a traditional training provider generally runs from 9am – 5pm, with a nice long break for lunch. With Firebrand Training you’ll get at least 12 hours/day quality learning time, with your instructor
  5. You’ll learn Querying Data with Transact-SQL faster. Chances are, you’ll have a different learning style to those around you. We combine visual, auditory and tactile styles to deliver the material in a way that ensures you will learn faster and more easily
  6. You’ll be studying Querying Data with Transact-SQL with the best. We’ve been named in Training Industry’s “Top 20 IT Training Companies of the Year” every year since 2010. As well as winning many more awards, we’ve trained and certified 65,143 professionals, and we’re partners with all of the big names in the business
  7. You'll do more than study Querying Data with Transact-SQL courseware. We use practical exercises to make sure you can apply your new knowledge to the work environment. Our instructors use demonstrations and real-world experience to keep the day interesting and engaging

Think you are ready for the course? Take a FREE practice test to assess your knowledge!

When do you want to sit your accelerated course?

Start

Finish

Status

Book now

Limited availability

Book now

Open

Book now

Open

Book now

Here's the Firebrand Training review section. Since 2001 we've trained exactly 65,143 students and asked them all to review our Accelerated Learning. Currently, 96.55% have said Firebrand exceeded their expectations:



"Firebrand taught me 2 months ago for a networking course and was just as good this time, highly recommend Firebrand. Until next time!"
Jordan Duncan, LSI Architects. - Microsoft MTA Security Fundamentals (2 days) (2017.10.6 to 2017.10.7)

"This is now the 3rd time I have attended a Firebrand course and still cannot fault them. Each instructor I've had has been extremely knowledge and helpful. I have recommended FB to many colleagues."
Laura OConnor, Utilita Energy Limited. - Microsoft MTA Security Fundamentals (2 days) (2017.10.6 to 2017.10.7)

"The course was intensive with a lot to take in during a short period but you have every resource you need. Great Staff, great atmosphere and you get well looked after too. I'll be back!"
D.W.. - Microsoft MCSE: Messaging (Exchange 2016) (6 days) (2017.10.2 to 2017.10.7)

"Excellent tutor, all explained clearly and in detail."
Arkadiusz Rycyk, Environ Audio Ltd. - Microsoft Specialist: Programming in C# (4 days) (2017.10.2 to 2017.10.5)

"The teaching was dynamic, engaging, immersive and knowledgeable. The instructor made complex concepts simple. I enjoyed the course very much. "
Richard Barnes, Whale Tankers. - Microsoft Specialist: Programming in C# (4 days) (2017.10.2 to 2017.10.5)

"Intense but very rewarding course, learned a lot in a few days."
S.S.. - Microsoft Specialist: Programming in C# (4 days) (2017.10.2 to 2017.10.5)

"The course was very intense and very informative, real examples were given to concepts hard to understand."
Anonymous - Microsoft Specialist: Programming in C# (4 days) (2017.10.2 to 2017.10.5)

"Training with Firebrand has been a great way to kick-start my IT certifications and to help me aim to progress in my careers. Instructors are knowledgeable and insightful and the learning experience was top notch!"
L.M.. - Microsoft MTA: Windows Server Administration Fundamentals (2 days) (2017.10.2 to 2017.10.3)

"Great facilities, good teacher and an enjoyable course."
Adam Batten. - Microsoft MTA Software Development Fundamentals (2 days) (2017.9.28 to 2017.9.29)

"This course was condensed but covered most of the topics. Will recommend to my colleagues and friends. One has to understand OOP, SDLC, VS, .net +Core. The Dev course was tough for me but the trainer made it understandable for me. "
P.A., kpdsolutions Ltd and contract client - TfL. - Microsoft MCSE: Cloud Platform and Infrastructure (Azure) (9 days) (2017.9.9 to 2017.9.17)

"As expected, I had an excellent experience here and the instructor knew the subject matter very well. I will definitely recommend Firebrand anytime and any day."
Ekhorutomwen Asemota, Capgemini. - Microsoft Specialist: Architecting Microsoft Azure Solutions (2 days) (2017.9.16 to 2017.9.17)

"Excellent and to the point, cannot not praise enough the instructor whose delivery was impeccable. "
P.V.. - Microsoft MCSD: Azure Solutions Architect (9 days) (2017.9.9 to 2017.9.17)

"The training was well structured and comprehensive. I filled in my Azure knowledge gaps and the instructor prepared me for the exams excellently, and was extremely knowledgeable, patient, and friendly throughout. All hail Lord Chinzilla!"
Chris Smallman. - Microsoft MCSE: Cloud Platform and Infrastructure (Azure) (9 days) (2017.9.9 to 2017.9.17)

"Lots of long days paid off - the course was tough but greatly rewarding. The instructor was a brilliant coach and we wouldn't have made it through the 9 days without him. The food wasn't bad either!"
Joseph Ellis, PA Consulting. - Microsoft MCSE: Cloud Platform and Infrastructure (Azure) (9 days) (2017.9.9 to 2017.9.17)

"The instructor has been great. Extremely enthusiastic and engaging. Would definitely recommend him to my colleagues. The structure and coverage of the material will definitely help me succeed with Azure, thanks Victor!"
Tarun Julka, Diligent Corporation. - Microsoft MCSE: Cloud Platform and Infrastructure (Azure) (9 days) (2017.9.9 to 2017.9.17)

"I always enjoy my courses at Firebrand and would recommend people take their courses here."
Ridhan Romjon, Retail 247. - Microsoft MCSE: Cloud Platform and Infrastructure (Azure) (9 days) (2017.9.9 to 2017.9.17)

"Intensive means just that. Long and exhausting training days but the quality of instruction makes the time fly by. Looking forward to part 2!"
C.P.. - Microsoft Specialist: Implementing Microsoft Azure Infrastructure Solutions (3 days) (2017.9.9 to 2017.9.11)

"The instructor was very engaging and knew his stuff! "
Anonymous - Microsoft Specialist: Implementing Microsoft Azure Infrastructure Solutions (3 days) (2017.9.9 to 2017.9.11)

"The quality of the training at Firebrand is perfect for preparing your for the exams!"
Rachel Hillier, Computer-eye2. - Microsoft MCSA: Windows 10 (6 days) (2017.8.21 to 2017.8.26)

"If you need massive amounts of knowledge in a short time, these are the guys to train with. Excellent facility and instructors. "
Darrall Jarman, Dazzle Computer Solutions (Self Employed). - Microsoft MCSA: Windows 10 (6 days) (2017.8.21 to 2017.8.26)

"Great place to come if like me you cant sit at home and read through a book, as it just does not go in. I came here having read books and have learnt a lot in the 6 days I was here. The classrooms are quite a nice environment and if you get a good trainer then it makes the whole thing better. The days are long and a bit of a slog at times getting in at 8/8.30 and then leaving at 9. Obviously there are breaks in between but still long days and don't sleep much. Overall a great experience and will definitely be using Firebrand for training again. "
Ben Farnath-Jones, OC&C Strategy Consultants. - Microsoft MCSA: Windows 10 (6 days) (2017.8.21 to 2017.8.26)

"Although the course consisted of very long days, the instructor was brilliant, it was a good laugh in the classroom and covered the subject areas very well! "
Anonymous - Microsoft MCSA: Windows 10 (6 days) (2017.8.21 to 2017.8.26)

"The trainer was great and very engaging. I thoroughly enjoyed the course and felt that all of the modules were covered clearly and concisely."
Ethan ODonnell. - Microsoft Windows Server 2016 - Identity (4 days) (2017.8.17 to 2017.8.20)

"After finishing the training for the MCSA course, I now feel confident in taking the examination!"
Callum Nocton-Gribben, Onefile ltd. - Microsoft MCSA: SQL Server 2016 - Database Development (6 days) (2017.8.14 to 2017.8.19)

"Had a great time at Firebrand. Thank you for all your support."
Jacob Johnson, St Johns School. - Microsoft Windows Server 2016 - Networking (3 days) (2017.8.14 to 2017.8.16)

"MCSD - App Builder course experience was great, got to know lot of new stuff like Azure & MVC and information about how to develop/plan enterprise level web application. The instructor was great through out the course explaining lot of modern stuff which goes on and also balancing the course with exam perspective. "
Amit Rathod. - Microsoft MCSD: App Builder (9 days) (2017.8.5 to 2017.8.13)

"The instructor was great and answered all questions in detail as well as making sure everyone understood. I found the course challenging but learning at Firebrand helps beginners get better knowledge of all the courseware covered."
Hannah McEwan. - Microsoft MCSD: App Builder (9 days) (2017.8.5 to 2017.8.13)

"The course was of very high quality and has provided me with the relevant information to allow me to put it into practice. I attended the course with little knowledge of administering SharePoint and I am leaving knowing that I have the relevant knowledge to build and administer a SharePoint farm. This is all thanks to the instructor - he has been fantastic!"
Benjamin Riley, Halton Borough Council. - Microsoft MCSE: SharePoint 2016 (6 days) (2017.8.7 to 2017.8.12)

"Very experienced tutor also allowed flexible learning environment to discuss wider scope of applied knowledge. All In one location (accommodation and self contained classroom building that is 24 hours accessible)."
A.A.. - Microsoft MCSA: SQL Server 2016 - Database Administration (6 days) (2017.8.7 to 2017.8.12)

"The quality of the training experience was excellent! Good facilities, helpful staff and brilliant instructors. This was my first experience of official Microsoft training and Firebrand have definitely made me want more... and with them! I will be Back!"
D.W., Declaro Support Limited. - Microsoft MCSA: Office 365 (5 days) (2017.8.7 to 2017.8.11)

"Excellent facility with excellent trainers."
Owen Heaume . - Microsoft MCSA: Office 365 (5 days) (2017.8.7 to 2017.8.11)

"It has been a great course, the instructor takes a fast-paced & information dense course, yet somehow makes it feel relaxed. Its been a great 3 days."
Anonymous - Microsoft Developing ASP.NET MVC Web 4 Applications (3 days) (2017.8.8 to 2017.8.10)

"Very good course, enjoyable under many aspects. The instructor was very friendly, knowledgeable and patient (I ask many questions). Thank you"
Christian Dornetto, CRM Dynamics Ltd. - Microsoft Specialist: Programming in HTML5 with JavaScript and CSS3 (3 days) (2017.8.5 to 2017.8.7)

"Firebrands intensive courses are great for achieving qualifications fast!"
Muhammed Haque. - Microsoft Specialist: Programming in HTML5 with JavaScript and CSS3 (3 days) (2017.8.5 to 2017.8.7)

"The training was very helpful and puts a lot of things into perspective and real life. It is explained in a way that makes sense, which isn't always captured from a book."
M.J., Knauf Insulation. - Microsoft MTA Networking, Security & Windows Server Administration (6 days) (2017.7.31 to 2017.8.5)

"Really enjoyed my short time here. I've made lots of new friends and learned a lot of new stuff within the 6 days that I've been here. Gareth has been great and has helped us through every section of the course."
James Laird, Jmfinn & Co. - Microsoft MTA Networking, Security & Windows Server Administration (6 days) (2017.7.31 to 2017.8.5)

"It was a great course, relevant information and a knowledgeable instructor. It helped to make a good bond with other attendees, helping with the evening revision and practice exams together in the hotel bar, or purely just moral support! Be prepared for a lot of intense information, take notes and use whatever vice you need; coffee, sugar, fizzy drinks to keep you alert! I had a great time and would definitely recommend."
KAYLEA REED, UTILITA ENERGY. - Microsoft MTA Networking, Security & Windows Server Administration (6 days) (2017.7.31 to 2017.8.5)

"My journey at Firebrand has been great. First course with them, it was extremely informative and I learnt more than I ever would in my own time. The course duration was just right, 6 days to cover 3 modules was perfect. The practice exams are priceless, you can never prepare yourself for the real thing however they got you ready for the daunting aspect of exam conditions. The level of help, support and enthusiasm for us to succeed drives you to do your best (and the failure is not an option T-shirt helps!). Thank you - I will be back!"
Leah Hunt, Scientifica. - Microsoft MTA Networking, Security & Windows Server Administration (6 days) (2017.7.31 to 2017.8.5)

"I have learnt a lot this week with Firebrand, their instructors were all friendly and experienced, and have the experience they have in the real world has significantly contributed to my passing this course."
James Gunn, Softwerx. - Microsoft MTA Networking, Security & Windows Server Administration (6 days) (2017.7.31 to 2017.8.5)

"The course was great; fast paced enough to keep you interested and the instructors will give you excellent insight the books wont."
R.W., 3T RPD Ltd. - Microsoft MTA Networking, Security & Windows Server Administration (6 days) (2017.7.31 to 2017.8.5)

"Excellent Environment and training provided. Thank you"
Anonymous - Microsoft MTA Networking Fundamentals (2 days) (2017.8.2 to 2017.8.3)

"The instructor was very knowledgeable and very accommodating in changing and adding additional content for the course."
Colin Lobo, NCC Group. - Microsoft Azure Academy: Infrastructure and Networking (4 days) (2017.7.31 to 2017.8.3)

"Instructor was very knowledgeable in his subject. If he did not know the answer to a question he would go off and investigate to provide answers."
C.H.. - Microsoft Azure Academy: Infrastructure and Networking (4 days) (2017.7.31 to 2017.8.3)

"It was well spent 4 days understanding the Azure Platform and good pointers for security best practices for deployment, monitoring and assessment."
A.N., NCC Group. - Microsoft Azure Academy: Infrastructure and Networking (4 days) (2017.7.31 to 2017.8.3)

"Its been really hard work, but really worth the time. "
Jeffrey Vermunt, MK2. - Microsoft MCSE: Communication (Skype for Business) (6 days) (2017.7.17 to 2017.7.22)

"Training has been of high standards and quality and why I have returned for 3rd time to train with them. I would highly recommended Firebrand Training to anyone wanting to certify themselves."
S.B., Sutaj Ltd. - Microsoft MCSE: Communication (Skype for Business) (6 days) (2017.7.17 to 2017.7.22)

"Great course with a brilliant course teacher. The instructor managed to dumb down complex concepts and made an interactive educative environment."
Mohammad B, CRMCS Consultancy. - Microsoft Specialist: Programming in C# (4 days) (2017.7.3 to 2017.7.6)

"The training is very good. It is not passive, so you have to work hard, but you get all the key information you need to build upon - with your own reading and revision adding the extra insight needed for the exam. My expectation before the course was that I might pass one of the three exams, but I soon realised that if I was prepared to work in the evenings as the instructor encouraged us all to, then I could get through them all (so far Ive passed 2 with one more this afternoon!). It was up to me now. Having passed an MCP in server 2003 through self-study - which took nearly 12 months - I was very impressed with how much you can learn when able to get the answers you need from a subject matter expert and how quickly you can get through a single exam - a matter of a few days. The labs were also very useful for getting hands-on experience and testing out what you had learned during the day. In terms of the accommodation and food, it was excellent - a small issue with my shower was fixed within hours and the staff are all friendly and very professional. You get a very good choice of food and if you prefer a low carb diet like myself then thats no problem. If you prefer to relax in the evening then there is a decent bar where you can take some time out with your new friends. "
Ben Kane, Euro Recycling. - Microsoft MCSA: Windows Server 2016 (11 days) (2017.6.24 to 2017.7.4)

"Excellent learning environment, knowledgeable staff. Well paced course syllabus."
Matthew Ottley, Manuli Hydraulics UK Ltd. - Microsoft MCSA: Windows Server 2016 (11 days) (2017.6.24 to 2017.7.4)

"5th Course at firebrand, and as always a great experience. Trainers are friendly and willing to answer most any questions you may have, even if they have to get back to you later 10/10"
Ethan ODonnell. - Microsoft Windows Server 2016 - Networking (3 days) (2017.6.28 to 2017.6.30)

On this 3-day accelerated Querying Data with Transact-SQL course you will develop your understanding of managing, querying and programming data through Transact-SQL components. You will also learn the skills to use Transact-SQL language, relevant to all of the SQL Server disciplines. These skills will help you run advanced queries against your data to extract insight to drive your business' performance.

This course is 50% faster than other traditional training courses, meaning you’ll be back in the office sooner, putting your new skills into practice.

You will develop your knowledge and skills of Transact-SQL. Specific skills you will learn include:

  • Create Transact-SQL SELECT queries
  • Implement functions and aggregate data
  • Query data by using table expressions
  • Group and pivot data by using queries
  • Implement error handling transactions
  • Implement data types and NULLs

Firebrand are Microsoft Gold Learning Partners which means you'll benefit from Microsoft's Official Curriculum (MOC), delivered by our experienced Microsoft Certified Trainers (MCT). Our unique Lecture | Lab | Review approach means you’ll get exposure to both the theoretical and practical tasks. This will help you retain information and provide you with the hands-on skills necessary to apply your new skills when you return to the office.

You’ll be prepared for and sit your exam on-site. This is covered by your Certification Guarantee.

This exam is intended for SQL Server database administrators, system engineers and developers with two or more years of experience. This is for professionals looking to validate their skills and knowledge in writing queries.

Read more ...

Interested? See prices or call +44 207 907 1120

Use your free Microsoft training vouchers

You may be entitled to heavily-discounted training via Microsoft's Software Assurance Training Voucher (SATV) scheme. If your business has bought Microsoft software, check to see if it came bundled with free training vouchers! Vouchers can be exchanged against training for all Microsoft technologies. If you’re unsure, get in touch with us

You'll get certified 50% faster: This means you'll return to the office sooner, putting your new skills to practice.

Microsoft Official Curriculum (MOC): We offer Microsoft Official Curriculum meaning you'll have the latest and most up-to-date curriculum available.

Microsoft Certified Trainers (MCT): You'll learn from our Microsoft Certified Instructors who boast years of industry knowledge. Their experience and unique insights will be used to enrich your learning experience.

Other accelerated training providers rely heavily on lecture and independent self-testing and study.

Effective technical instruction must be highly varied and interactive to keep attention levels high, promote camaraderie and teamwork between the students and instructor, and solidify knowledge through hands-on learning.

Firebrand Training provides instruction to meet every learning need:

  • Intensive group instruction
  • One-on-one instruction attention
  • Hands-on labs
  • Lab partner and group exercises
  • Question and answer drills
  • Independent study

This information has been provided as a helpful tool for candidates considering training. Courses that include certification come with a Certification Guarantee. Pass first time or train again for free (just pay for accommodation, exams and incidental costs). We do not make any guarantees about personal successes or benefits of obtaining certification. Benefits of certification determined through studies do not guarantee any particular personal successes.

Read more ...

Interested? See prices or call +44 207 907 1120

Course 20761B: Querying Data with Transact-SQL

Module 1: Introduction to Microsoft SQL Server 2016

This module introduces SQL Server, the versions of SQL Server, including cloud versions, and how to connect to SQL Server using SQL Server Management Studio.

Lessons

  • The Basic Architecture of SQL Server
  • SQL Server Editions and Versions
  • Getting Started with SQL Server Management Studio

Lab : Working with SQL Server 2016 Tools

After completing this module, you will be able to:

  • Describe the architecture and editions of SQL Server 2012.
  • Work with SQL Server Management Studio.

Module 2: Introduction to T-SQL Querying

This module introduces the elements of T-SQL and their role in writing queries, describes the use of sets in SQL Server, describes the use of predicate logic in SQL Server, and describes 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 statements

Lab : Introduction to Transact-SQL Querying

After completing this module, you will be able to:

  • Describe 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

Module 3: 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

After completing this module, you will be able to:

  • Write simple SELECT statements.
  • Eliminate duplicates using the DISTINCT clause.
  • Use column and table aliases.
  • Write simple CASE expressions.

Module 4: Querying Multiple Tables

This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.

Lessons

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

Lab : Querying Multiple Tables

After completing this module, you will be able to:

  • Describe how multiple tables may be queried in a SELECT statement using joins.
  • Write queries that use inner joins.
  • Write queries that use outer joins.
  • Write queries that use self-joins and cross joins.

Module 5: Sorting and Filtering Data

This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.

Lessons

  • Sorting Data
  • Filtering Data with Predicates
  • Filtering with the TOP and OFFSET-FETCH Options
  • Working with Unknown Values

Lab : Sorting and Filtering Data

After completing this module, you will be able to:

  • Filter data with predicates in the WHERE clause.
  • Sort data using ORDER BY.
  • Filter data in the SELECT clause with TOP.
  • Filter data with OFFSET and FETCH.

Module 6: Working with SQL Server 2016 Data Types

This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.

Lessons

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

Lab : Working with SQL Server 2016 Data Types

After completing this module, you will be able to:

  • Describe numeric data types, type precedence and type conversions.
  • Write queries using character data types.
  • Write queries using date and time data types.

Module 7: Using DML to Modify DataThis module describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates, and deletes to your data.

Lessons

  • Inserting Data
  • Modifying and Deleting Data

Lab : Using DML to Modify Data

After completing this module, you will be able to:

  • Insert new data into your tables.
  • Update and delete existing records in your tables.

Module 8: Using Built-In Functions

This module introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.

Lessons

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

Lab : Using Built-In Functions

After completing this module, you will be able to:

  • Write queries with built-in scalar functions.
  • Use conversion functions.
  • Use logical functions.
  • Use functions that work with NULL.

Module 9: Grouping and Aggregating Data

This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.

Lessons

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

Lab : Grouping and Aggregating Data

After completing this module, you will be able to:

  • Write queries which summarise data using built-in aggregate functions.
  • Use the GROUP BY clause to arrange rows into groups.
  • Use the HAVING clause to filter out groups based on a search condition.

Module 10: Using Subqueries

This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

Lessons

  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries

Lab : Using Subqueries

After completing this module, you will be able to:

  • Describe the uses of queries which are nested within other queries.
  • Write self-contained subqueries which return scalar or multi-valued results.
  • Write correlated subqueries which return scalar or multi-valued results.
  • Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.

Module 11: Using Table Expressions

Previously in this course, you learned about using subqueries as an expression that returned results to an outer calling query. Like subqueries, table expressions are query expressions, but table expressions extend this idea by allowing you to name them and to work with their results as you would work with data in any valid relational table. Microsoft SQL Server 2016 supports four types of table expressions: derived tables, common table expression (CTEs), views, and inline table-valued functions (TVFs). In this module, you will learn to work with these forms of table expressions and learn how to use them to help create a modular approach to writing queries.

Lessons:

  • Using Views
  • Using Inline Table-Valued Functions
  • Using Derived Tables
  • Using Common Table Expressions

Lab: Using Table Expressions

  • Writing Queries That Use Views
  • Writing Queries That Use Derived Tables
  • Writing Queries That Use Common Table Expressions (CTEs)
  • Writing Queries That Sue Inline Table-Valued Expressions

After completing this module, you will be able to:

  • Write queries that return results from views.
  • Use the CREATE FUNCTION statement to create simple inline TVFs.
  • Write queries that create and retrieve results from derived tables.
  • Write queries that create CTEs and return results from the table expression

Module 12: Using Set Operators

This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets.

Lessons:

  • Writing Queries with the UNION Operator
  • Using EXCEPT and INTERSECT
  • Using APPLY

Lab: Using SET Operators

After completing this module, you will be able to:

  • Write queries using UNION, EXCEPT, and INTERSECT operators.
  • Use the APPLY operator.

Module 13: Using Windows Ranking, Offset, and Aggregate Functions

This module describes the benefits to using window functions. Restrict window functions to rows defined in an OVER clause, including partitions and frames. Write queries that use window functions to operate on a window of rows and return ranking, aggregation, and offset comparison results.

Lessons:

  • Creating Windows with OVER
  • Exploring Window Functions

Lab: Using Windows Ranking, Offset, and Aggregate Functions

  • Writing Queries that use Ranking Functions
  • Writing Queries that use Offset Functions
  • Writing Queries that use Window Aggregate Functions

After completing this module, students will be able to:

  • Describe the T-SQL components used to define windows, and the relationships between them.
  • Write queries that use the OVER clause, with partitioning, ordering, and framing to define windows
  • Write queries that use window aggregate functions.
  • Write queries that use window ranking functions.
  • Write queries that use window offset functions

Module 14: Pivoting and Grouping Sets

This module describes write queries that pivot and unpivot result sets. Write queries that specify multiple groupings with grouping sets.

Lessons:

  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets

Lab: Pivoting and Grouping Sets

  • Writing Queries that use the PIVOT Operator
  • Writing Queries that use the UNPIVOT Operator
  • Writing Queries that use the GROUPING SETS CUBE and ROLLUP Subclauses

After completing this module, students will be able to:

  • Describe how pivoting data can be used in T-SQL queries.
  • Write queries that pivot data from rows to columns using the PIVOT operator.
  • Write queries that unpivot data from columns back to rows using the UNPIVOT operator.
  • Write queries using the GROUPING SETS subclause.
  • Write queries that use ROLLUP AND CUBE.
  • Write queries that use the GROUPING_ID function.

Module 15: Executing Stored Procedures

This module describes how to return results by executing stored procedures. Pass parameters to procedures. Create simple stored procedures that encapsulate a SELECT statement. Construct and execute dynamic SQL with EXEC and sp_executesql.

Lessons:

  • Querying Data with Stored Procedures
  • Passing Parameters to Stored procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL

Lab : Executing Stored Procedures

  • Using the EXECUTE statement to Invoke Stored Procedures
  • Passing Parameters to Stored procedures
  • Executing System Stored Procedures

After completing this module, students will be able to:

  • Describe stored procedures and their use.
  • Write T-SQL statements that execute stored procedures to return data.
  • Write EXECUTE statements that pass input parameters to stored procedures.
  • Write T-SQL batches that prepare output parameters and execute stored procedures.
  • Use the CREATE PROCEDURE statement to write a stored procedure.
  • Create a stored procedure that accepts input parameters.
  • Describe how T-SQL can be dynamically constructed.
  • Write queries that use dynamic SQL.

Module 16: Programming with T-SQL

This module describes how to enhance your T-SQL code with programming elements.

Lessons:

  • T-SQL Programming Elements
  • Controlling Program Flow

Lab: Programming with T-SQL

  • Declaring Variables and Delimiting Batches
  • Using Control-Of-Flow Elements
  • Using Variables in a Dynamic SQL Statement
  • Using Synonyms

After completing this module, students will be able to:

  • Describe how Microsoft SQL Server treats collections of statements as batches.
  • Create and submit batches of T-SQL code for execution by SQL Server.
  • Describe how SQL Server stores temporary objects as variables.
  • Write code that declares and assigns variables.
  • Create and invoke synonyms
  • Describe the control-of-flow elements in T-SQL.
  • Write T-SQL code using IF...ELSE blocks.
  • Write T-SQL code that uses WHILE.

Module 17: Implementing Error Handling

This module introduces error handling for T-SQL.

Lessons:

  • Implementing T-SQL error handling
  • Implementing structured exception handling

Lab: Implementing Error Handling

  • Redirecting errors with TRY/CATCH
  • Using THROW to pass an error message back to a client

After completing this module, students will be able to:

  • Implement T-SQL error handling.
  • Implement structured exception handling.

Module 18: 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
  • Adding error handling to a CATCH block

After completing this module, students will be able to:

  • Describe transactions and the differences between batches and transactions.
  • Describe batches and how they are handled by SQL Server.
  • Create and manage transactions with transaction control language (TCL) statements.
  • Use SET XACT_ABORT to define SQL Servers handling of transactions outside TRY/CATCH blocks.

Read more ...

Interested? See prices or call +44 207 907 1120

You'll sit the following exams, covered by your Certification Guarantee:

  • Exam 70-761: Querying Data with Transact-SQL

This exam will test your knowledge and skills on the following:

  • Manage data with Transact-SQL (40-45%)
  • Query data with advanced Transact-SQL components (30-35%)
  • Program databases by using Transact-SQL (25-30%)

Read more ...

Interested? See prices or call +44 207 907 1120

Included in your accelerated training course will be:

  • Microsoft Official Curriculum
  • Exam 70-761: Querying Data with Transact-SQL

Firebrand Training offers top-quality technical education and certification training in an all-inclusive course package specifically designed for the needs and ease of our students. We attend to every detail so our students can focus solely on their studies and certification goals.

Our Accelerated Learning Programmes include:

  • Intensive Hands-on Training Utilising our (Lecture | Lab | Review)TM Delivery
  • Comprehensive Study Materials, Program Courseware and Self-Testing Software including MeasureUp *
  • Fully instructor-led program with 24 hour lab access
  • Examination vouchers **
  • On site testing ***
  • Accommodation, all meals, unlimited beverages, snacks and tea / coffee****
  • Transportation to/from designated local railway stations
  • Examination Passing Policy

Our instructors teach to accommodate every student's learning needs through individualised instruction, hands-on labs, lab partner and group exercises, independent study, self-testing, and question/answer drills.

Firebrand Training has dedicated, well-equipped educational facilities where you will attend instruction and labs and have access to comfortable study and lounging rooms. Our students consistently say our facilities are second-to-none.

Firebrand goes digital

We’re currently migrating from printed to digital courseware. Some courseware is already available in digital, while other books remain in printed form. To find out if this course is digital, call us on +44 207 907 1120.

There are several benefits of easy-to-use digital courseware: downloads are immediate, and you’ll always have your courseware available wherever you are. You won’t need to wait for a printed book to be delivered before you start learning – and it’s better for the environment. You can choose to download the courseware to your own device, or borrow one of ours.

Considering a Microsoft course? Always have the most up-to-date Microsoft digital courseware with ‘Fresh Editions’. This gives you access to all versions of digital courseware – you’ll receive updates and revisions of your textbook, at no charge, for the life of that course.

Examination Passing Policy

Should a student complete a Firebrand Training Program without having successfully passed all vendor examinations, the student may re-attend that program for a period of one year.  Students will only be responsible for accommodations and vendor exam fees.

Please note

  • * Not on all courses
  • ** Examination vouchers are not included for the following courses: PMI, GIAC, CISA, CISM, CGEIT, CRISC, CISSP CBK Review and CSX Practitioner Level 1,2 and 3
  • *** On site testing is not included for our PMI, GIAC, CSX Practitioner or ITIL Managers and Revision Certification Courses.
  • **** Accommodation not included on the CISSP CBK Review Seminar

Read more ...

Interested? See prices or call +44 207 907 1120

You must have a working knowledge of relational databases and basic knowledge of the Microsoft Windows operating system and its core functionality.

Unsure whether you meet the prerequisites?

Don’t worry - we’ll discuss your technical background, experience and qualifications to determine whether this accelerated course suits you.

Just call us on +44 207 907 1120 and speak to one of our enrolment consultants.

Firebrand is an immersive environment and requires commitment. Some prerequisites are simply guidelines; you may find your unique experience, attitude and determination enables you to succeed on your accelerated course.

Interested? See prices or call +44 207 907 1120