CBT Nuggets 70-461 - Writing Queries in SQL Server 2012
English | MP4 | Author : Garth Schulte | Duration : 11 hours | 1.22 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 objects using DDL (Data Definition Language) to becoming a data ninja by writing queries that Dice and slice the information stored in Database Objects These 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 objectives 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 instance and into Our Show you a FEW Tips on How to manage your queries and scripts using SQL Server Projects.
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 scripts from tables, and learn some tips and tricks in SQL Server 2012 Management Studio Along the way.
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.
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 Permission to Simplify Management and Keep Our Secure base tables.
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.
Creating and Modifying DML Triggers
DML Triggers allow us to respond to Our Data Modifications on 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. Also You'll learn How to build nested triggers, Understand Potential Performance Implications and See How to Send an Email from Inside a trigger using T-SQL.
Querying Data using SELECT
Nugget This 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. Also You'll learn How to Rank Data, build and execute Dynamic SQL, and use system metadata to query the Database About Itself.
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. Also You'll learn How to create Common Table Expressions (CTES) to help us manage Better T-SQL code and finish with How to build matrix style results using pivot tables.
Implementing Data Types
Choosing the right type for Data 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 Data in each store type.
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 ranking and Analytic Functions in Action while Learning How to generate, and work with Aggregate Spatial Data.
Querying and Managing XML Data
XML is a format for Widely Adopted 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. Also You'll 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).
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.
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.
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.
Working with Functions
Functions Give us a Great Alternative to reusing code stored over 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
Performance is a top priority for many Databases and Performance issues Come from Poorly designed queries. This Nugget Will Show you How to Find query Performance issues using query Execution Plans, Find Performing slow queries in a Database using DMVs and Give you Design guidelines needed to create Blazing fast queries.
Transactions the ensure Stays in Our Data a consistent state and is protected from concurrent processes. This Nugget Will Show you How to create, manage and work with transactions - Also you'll learn How locking works and See How isolation levels help us When IT Comes to Concurrent Operations.
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.
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. Also You'll learn How to generate, store and View Custom error messages in SQL Server.
Click or Copy and Paste To Download:
For other news, visit my profile every day!
To Unzip the files use 7zip or WinRar.
I recommend to download as fast as you can or you will lose file you need ( Links dead because of Copyright Infringement )