Description

SQL Server 70-768: Developing SQL Data Models By Scot J. Reagin

SQL Server 70-768: Developing SQL Data Models LiveLessons (Video Training)
MP4 | Video: AVC 1280×720 | Audio: AAC 44KHz 2ch | Duration: 6 Hours 44M | 14.1 GB
Genre: eLearning | Language: English
SQL Server Analysis Services (SSAS) provides a robust semantic environment for modeling data in support of analytic and ad hoc reporting needs. With each release of SQL Server, SSAS functionality is enhanced, with tabular models in particular becoming more capable and fully featured.This course is the foundation for the Microsoft Certificate 70-461: “Querying Microsoft SQL Server 2012” and 70-761 “Querying Data with Transact-SQL”.

Session 1

The basics presented are: how to install SQL Server, and how to create and drop tables.

We then try to create a more advanced table, but find that we need to know more about data types – so we go into some detail about data types and data functions, the foundation of T-SQL.

Session 2

We’ll create tables which use these, and then INSERT some data into them. Then we’ll write queries which will retrieve and summary this data, using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.

We’ll then JOIN these tables together to find where we are missing data and where we have inconsistent data. We’ll then UPDATE and DELETE data from the tables. This will allow up to fully complete objective number 1 from the 70-461 exam.

Session 3

We’ll now use that data to create views, which enable us to store these SELECT queries for future use, and triggers, which allow for code to be automatically run when INSERTing, DELETEing or UPDATEing data.

We’ll look at the database that we developed in session 2,

and see what is wrong with it. We’ll add some constraints, such as UNIQUE, CHECK

, PRIMARY KEY and FOREIGN KEY constraints, to stop erroneous data from

being added some data. By doing this, we will complete objectives 2, 3, 4 and 5 from the 70-461 exam

Session 4

We will further encapsulate our routines by creating procedures, allowing us to EXECUTE parameterised commands with just one statement, and we’ll add some error handling with TRY, CATCH and THROW.

We’ll also combine datasets together, by looking at UNION and UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL and Coalesce, and the mighty MERGE statement. By doing this, we will complete objectives 11, 12, 13 and parts of 6 and 18 from the 70-461 exam.