Querying Microsoft SQL Server 2014 (70-461)

Overview

This section speaks about the overview of upcoming querying SQL . It includes scope of series ,also it covers who all would be the audience.

SQL Server Basics

This section talks about querying Microsoft SQL Server 2014, basics of MS SQL Server 2014 editions, primer on SQL databases in general, different default databases you’ll encounter after installation, which includes the System Databases (Master, MSDB, Model, TempDB) and User created databases .

SQL Querying Basics

This section speaks about basics of SQL querying It starts by defining what SQL is and how it differs from Transact SQL and explain the 3 categories of T-SQL statements which are Data Manipulation Language (DML), Data Definition Language(DDL), and Data Control Language(DCL).

Create and Alter Tables

This section covers table creation , alteration, compress tables and querying information from existing tables to new tables.

Create and Alter Views

This section explains how to restrict the information presented to a user or application by creating views and demonstrate how to view that has already been created. It explains inline table-valued function as well as creating indexed views to optimize performance. Further it demonstrates creating and altering views with querying Microsoft SQL Server 2014 and explains non-regression and data validation.

Create and Modify Constraints

This section covers how to maintain consistency within a database by using constrainsts, primary key, foreign key, unique and default constraints, their demonstration and implementation.

Create and Alter DML Triggers

This section demonstrate how to create and alter DML Triggers in SQL 2014. It covers the particulars of DML Triggers, the when/where/why/how they are used, AFTER trigger followed by the INSTEAD OF trigger type,nested triggers and how to use the UPDATE function.

Basic SELECT Queries

This section covers SELECT SQL query, demonstrate how to perform a simple SELECT query to find set data form a database and pair the results by using WHERE and GROUP BY filter statements. It expalins filtering of data and removing duplicates from the results and demonstrate the decision making features of SQL using CASE command.

Query Using JOINS

This section cover the usage of JOINs function to return data from multiple databases, INNER JOIN command to filter the data, LEFT JOIN and RIGHT JOIN commands to return data sets that have matching values as well as all values from either the right or left tables respectively. It explains the concept and use of Temporary Tables, Views, and Derived Tables showing how to use the Temporary Tables and Views their benefits and problems.

Implement Subqueries

This section covers 3 different types of subqueries in SQL. It explains

1. Scalar subquery, which is used to return a single value result.
2. Multi-Valued subquery, which is used to return multiple results.
3. Logical aptitude with Correlated Subqueries which works together with the outer query.
4. Use APPLY operator, which is replacement for correlated subquieries and derived tables.
5. Use of Common Table Expression(CTE).

Implement Aggregate Queries

This section covers the concept and different implementations of aggregate queries. It demonstrates

1. GROUPING SETS which will define the groups
2. ROLLUP statement which will create a group with a hierarchy
3. RANK() function, which assigns a value to data for sorting purposes
4. NTILE() function for grouping data
5. Use of Spatial aggregates to visualize data by using a map(Geographical) or a shape(Geometrical)
6. 4 Spatial Aggregate types including: Union, Envelope, Collection, and Convex.

Query Using Synonyms

This section explains how to query SQL Server 2014 using Synonyms, Intersects and Except. It demonstrates Synonyms, which are similar to Aliases, use of the Intersect which returns distinct rows that are common between sets, Except which returns distinct rows that are in the first query but not the second.

Understand Data Access Technologies

This section covers different Data Access (DA) technologies. It demonstrates what Data Access technologies are and how they’re used. Further it explains DA’s like SQL Server Native Client(SNAC), ADO.NET, JDBC drivers, and Microsoft drivers for PHP.

Work with Conditions and Nulls

This tutorial covers how to work with conditional statements and NULL values. It demonstrates the CASE function to perform decisions based on matching conditions,IS NULL function to test for NULL values and COALESCE function to replace a NULL value with another value.

Work with Table Constraints

This section covers how to work with table constraints and demonstrates using the INSERT INTO function to select data from one table and insert it into an existing table.

Query and Manage XML Data

This section demonstrate how to query and manage XML data in a SQL 2014 database, how to create XML data from relational data and when and why to work with XML. It also explains “shredding” XML into tables,the process of XML indexing, creating and using both primary and secondary indexes.

Implement Data Types

This section covers different data types and database structure planning. It explains numeric, date and time data types and their characteristics. It demonstrates the skills and knowledge in writting Transact SQL queries. It also describes, discusses and demonstrates SQL query writing skills in multiple areas and covers the advanced SELECT statements, grouping sets, aggregates, SET operators, basic T-SQL programming and performance Improvement. It mainly focuses heavily on demonstrating the syntax and logic of writing the queries.

Create and Alter Stored Procedures

This section covers using Stored Procedure in SQL 2014. It starts with introduction to store procedures, where and why they are used, how to create simple and complex store procedures with and without parameters. At the end it explains Branching Logic controls used as conditional elements.

INSERT

This section covers the INSERT, UPDATE, and DELETE statements in SQL Server 2014. It explains to use INSERT command to insert one or more rows into a target table with single and multiple values. At the end it covers using the INSERT SELECT and EXEC statements.

Combine Datasets

This section covers Datasets in SQL Server 2014. It explains difference between UNION and UNION ALL and the rules that govern their use and modifying data with MERGE statements.

Work with Functions

This section demonstrates what are functions in SQL, how to create and work with functions, types of User Defined Functions(UDFs),Scalar and Table-Valued functions.

Optimize Queries

This section introduces optimization of queries. It explains query execution phases and two types of statistics (TIME and IO). It continues with exploring query optimization, Execution Plans (Actual and Estimated) and the ways in which they are presented (Textual, Graphical, and XML). It also covers exploring query optimization by looking at Dynamic Management Views, 3 Hint types (JOIN, QUERY, and Table), JOIN types and Nested Loops.

Manage Transactions

This section covers how SQL Server 2014 manages Transactions to ensure the database integrity. It explains isolation levels, how SQL 2014 uses Commits and Rollbacks and the difference between Implicit and Explicit transactions.

Evaluate Different Operations

This section evaluate the use of row-based operations vs set-based operations. It explains when to use the Cursors as well as the impact of Scalar User Defined Functions(UDFs). At the end it demonstrates combining multiple DML operations and using set-based logic rather than row-based logic.

Implement Error Handling

This section covers implementing errors and error handling in SQL Server 2014. It describes types of locks and their scope of use and implementing the TRY/CATCH/THROW functions.