Product HomePage: http://www.cbtnuggets.com/it-training-videos/series/mcrsft_s...
CBTNuggets Microsoft SQL Server 2012 70-461 Writing Queries in SQL Server
2012-PLATO | 1.06 Gb
Take your SQL Server knowledge to the next level by learning how to write queries and work with database entities behind the scenes using T-SQL in Microsoft SQL Server 2012.
This series will take you from front-end user to back-end wizard. You'll learn the ins and outs of working with SQL Server 2012 programmatically, covering in great detail both sides of the T-SQL language -- from creating database ob
jects using DDL (Data Definition Language) to becoming a data ninja by writing queries that slice and dice the information stored in these database ob
jects using DML (Data Manipulation Language).
This is the beginning of the road for SQL Server 2012 certifications and a prerequisite to both of the professional level certifications: Data Platform and Business Intelligence. This series will prepare you for the 70-461 exam and beyond with real world examples!
Introduction to 70-461: Querying Microsoft SQL Server 2012
In this introductory Nugget we take a good look at what's in store for the series. We start with the 70-461 exam ob
jectives from Microsoft and follow that up with a look at how this series is laid out. Next, we get familiar with the SQL Server 2012 express editions and install it on our Nuggetlab virtual machine. Finally we look at how to obtain and attach the AdventureWorks2012 sample database into our instance and show you a few tips on how to manage your queries and sc
ripts using SQL Server projects.
00:35:20
Creating and Altering Tables with T-SQL
You'll learn how to create, modify and delete tables using T-SQL in this Nugget. You'll also learn how to utilize context sensitive help, translate T-SQL syntax, generate and analyze sc
ripts from tables, and learn some tips and tricks in SQL Server 2012 Management Studio along the way.
00:34:26
Creating and Altering Views with T-SQL
This Nugget will show you how to create, modify and delete views using T-SQL. You'll learn how to modify data using views, create and use indexed views, analyze query execution plans and generate sample data for testing purposes.
00:43:22
Designing Views
Understanding when and how to design views will help you create solutions to common problems and simplify data access. This Nugget will give you the know-how to do just that -- you'll start with view design 101 and learn when to use views; next we'll learn how to design views in SQL Server 2012 Management Studio as well as encrypt view definitions. Finally we'll learn how to apply security at the view level to simplify permission management and keep our base tables secure.
00:32:22
Creating and Modifying Constraints
In this Nugget, you'll learn how to create constraints, which gives us a way to shape the data going into our database and ensure data integrity. You'll learn how to create column and table constraints such as defaults, checks, unique indexes, primary keys and foreign keys using T-SQL. You'll also see how to create a database diagram to get a better visual of the overall database design.
00:42:30
Creating and Modifying DML Triggers
DML Triggers allow us to respond to data modifications on our tables in real-time and are a great tool for any DBA or developer to have in their skill set. This Nugget will show you when and how to create every type of DML trigger for every type of data modification. You'll also learn how to build nested triggers, understand potential performance implications and see how to send an email from inside a trigger using T-SQL.
00:46:11
Querying Data using SELECT
This Nugget will take you on a tour through the SELECT statement and how we use it to return data from the database. You'll start with the basics -- learning the major components of the SELECT statement and follow it up with how to use some extremely useful T-SQL language constructs and built-in functions. You'll also learn how to rank data, build and execute dynamic SQL, and use system me
tadata to query the database about itself.
00:58:55
Implementing Subqueries
Learning how to write subqueries gives us more ways to design a query. This Nugget will start with the subquery basics and show you where and when we can write nested queries. You'll also learn how to create Common Table Expressions (CTEs) to help us better manage T-SQL code and finish with how to build matrix st
yle results using pivot tables.
00:33:54
Implementing Data Types
Choosing the right data type for storing data, whether it be in table columns or variables, is essential for the integrity, storage and performance of our database. This Nugget gives you an understanding of every data type available in SQL Server 2012 -- you'll learn how to choose the right data type for your database structures, get some common guidelines for working with data types and see first hand the data we can store in each data type.
00:40:16
Implementing Aggregates
This Nugget will show you how to group and aggregate data in a variety of ways. You'll start with the GROUP BY clause to get a handle on the basics of grouping data and incorporate grouping sets for subtotaling our query results. You'll see the new analytic and ranking functions in action while learning how to generate, work with and aggregate spatial data.
00:44:03
Querying and Managing XML Data
XML is a widely adopted format for storing data and this Nugget will show you everything you need to know about XML in SQL Server. The basics cover XML itself -- from schemas to namespaces to how we transform relational data to an XML format. You'll also learn how to load and associate XSD schemas with XML columns and import/export XML using BCP, OPENXML and even SQL Server Integration Services (SSIS).
00:43:24
Creating and Altering Stored Procedures
Stored Procedures allow us to create reusable components on the database server to increase managability and performance of our T-SQL code -- this Nugget will show you the basics and beyond of creating and executing Stored Procedures as well as techniques for building and designing a set of Stored Procedures for use in the data layer of an application.
00:33:21
Modifying Data with T-SQL
This Nugget will show you the basics of data modification with the INSERT, UPDATE and DELETE statement. We'll analyze the syntax of each statement, learn the basic usages and see how to utilize the OUTPUT clause to discover what changes were caused by each statement.
00:33:08
Combining Datasets
Understanding how to work with data from multiple queries will give you another skill to prepare you for anything and everything. This Nugget will show you how to combine, compare and merge results using operators such as UNION, EXCEPT, INTERSECT and MERGE.
00:22:49
Working with Functions
Functions give us a great alternative to reusing code over stored procedures and provide more flexibility in where we can use them. This Nugget will get you familiar with everything functions in T-SQL -- you'll learn how to create scalar and table-value functions, use built-in and system functions and see examples of how to use them in the real world.
00:42:21
Optimizing Queries
Performance is a top priority for databases and many performance issues come from poorly designed queries. This Nugget will show you how to find query performance issues using query execution plans, find slow performing queries in a database using DMVs and give you design guidelines needed to create blazing fast queries.
00:37:24
Managing Transactions
Transactions ensure our data stays in a consistent state and is protected from concurrent processes. This Nugget will show you how to create, manage and work with transactions -- you'll also learn how locking works and see how isolation levels help us when it comes to concurrent operations.
00:27:41
Row-based vs. Set-based Operations
Understanding how T-SQL statements process rows can help you make better design decisions when creating queries. This Nugget will get you familiar with row-based operations such as cursors and loops, when to use them and how to convert them to set-based operations for performance benefits.
00:22:57
Implementing Error Handling
Knowing when and how to handle errors will improve end user and developer experience with the database. This Nugget will show you where, when and how to handle errors using the TRY..CATCH statement. You'll also learn how to generate, store and view custom error messages in SQL Server.
00:15:26
x2WEog