Become a member

Online Access to 1000+ Courses!

Querying Microsoft SQL Server 2012 (Exam 70-461) Course

Author : Mark Long

8.5hrs (110 Lessons) 2012-10-09

This Course can only be played using a subscription. You can play only first 3 chapters for free. Click Here to avail a subscription
The 2012 Microsoft MCSA Certification track includes Exam 70-461 Querying Microsoft SQL Server 2012. The ability to work with Transact-SQL and understand the specifics of querying Microsoft SQL server is a fundamental skill. This VTC course is intended for SQL Server database administrators, implementers, system engineers, and developers who are preparing to take the 70-461 exam as well as those who are just beginning to learn Transact-SQL. Mark Long is the instructor and will take you through the various tools and concepts needed to help you get ready for the 70-461 exam. To begin learning today, simply click on the movie links.

Hello there. I'd like to welcome you to the Virtual Training Company presentation of Querying Microsoft SQL Server 2012. My name's Mark Long and I'll be your instructor for this course. And I first of all want to welcome you here and thank you for being here. Now what we're going to do in this course is go through the process of covering Transact-SQL and using that to query Microsoft SQL Server 2012. And all of this is done with the express purpose of helping you prepare for Microsoft's exam, certification exam 70-461. Now in the SQL Server 2012 Certification track, Microsoft's shaking things up a little bit and for the first time we're actually seeing an exam focused on querying Microsoft's database products using Transact-SQL. This is pretty exciting, it's pretty cool. We're going to cover a lot of information as we go through this course, probably in excess of at least 90 videos that you're going to see as we move there. So let's just jump in there and get started. I'm glad you're here I hope you learn a lot.

Course: Querying Microsoft SQL Server 2012 (Exam 70-461)
Author: Mark Long
SKU: 34376
ISBN: 978-1-61866-070-1
Work Files: Yes
Captions: No
Subject: Certification
The first 3 chapters of courses are available to play for FREE (first chapter only for QuickStart! and MasterClass! courses). Just click on the movie link to play a lesson.

Download WorkFiles

MCSE: SQL Server 2012 Bundle
Author : Mark Long

41.5 / 499 2013-12-19
MCSA: SQL Server 2012 Bundle
Author : Mark Long

25.5 / 305 2013-12-19
Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012
Author : Mark Long

8 / 96 2013-05-02
Implementing a Data Warehouse with Microsoft SQL Server 2012 (Exam 70-463)
Author : Mark Long

8 / 96 2013-04-19
Microsoft SQL Server 2012 Development (Exam 70-464)
Author : Mark Long

8 / 98 2012-09-05
Microsoft SQL Server 2012 Admin (Exam 70-462)
Author : Mark Long

9 / 99 2012-06-18

MCSE: SQL Server 2012 Bundle
Author : Mark Long

41.5 / 499 2013-12-19
MCSA: SQL Server 2012 Bundle
Author : Mark Long

25.5 / 305 2013-12-19

Pricing & availability

Lifetime online access to this course

US$ 39.00

Download for offline access

US$ 79.95

Subscribe to 1000+ courses

Starting at $30/month

Course outline

Introduction

Welcome (01:11)

Course Overview (03:35)

Understanding SQL (04:22)

Understanding TSQL (03:21)

SQL Server History (04:49)

Microsoft SQL Server Certifications (02:51)

Getting Set Up

Getting SQL Server 2012 (03:36)

Installing SQL Server 2012 (05:42)

Attaching the Sample Database (04:04)

Getting SQL Books Online Locally (03:34)

Code Examples (01:41)

SQL Server 2012 Management Studio

SSMS Tour (06:09)

Graphical Queries (05:10)

SSMS Options (04:38)

Query Results Options (03:12)

Database Diagrams (05:18)

Creating Database Objects

Table Design Basics (03:54)

Creating a Table (05:41)

Altering Tables (06:09)

Dropping Tables (04:21)

Understanding Views (04:24)

Creating Views pt. 1 (04:44)

Creating Views pt. 2 (06:12)

Dropping Views (02:40)

Understanding Constraints (05:17)

Creating Constraints (05:07)

Understanding Triggers (05:00)

Trigger Options (04:04)

Inserted and Deleted Tables (02:55)

Creating Triggers (05:40)

Basic Select Queries

The Basic Select Statement (06:43)

Select Statement Structure (03:56)

The FROM Clause (03:27)

The WHERE Clause (04:23)

WHERE Clause Example (04:32)

The ORDER BY Clause (03:55)

ORDER BY Example (05:22)

The GROUP BY Clause pt. 1 (05:56)

The GROUP BY Clause pt. 2 (05:46)

The HAVING Clause (05:42)

The Power of LIKE (04:03)

More Advanced Select Statements

Using Distinct (04:44)

Column Aliasing (05:34)

Using the Top Option (04:02)

Top Option Example (05:01)

Using the OFFSET-FETCH Option (05:02)

Understanding NULLs (05:24)

Working with NULLs (03:42)

Using the Case Statement (05:29)

SELECT INTO (03:27)

The UNION Statement (05:56)

Using INTERSECT (05:23)

Using EXCEPT (06:18)

Understanding Synonyms (03:21)

Creating and Using Synonyms (02:38)

Using Concatenation (04:00)

Dynamic Queries pt. 1 (04:49)

Dynamic Queries pt. 2 (05:55)

INSERT Into (04:09)

SQL Server 2012 Data Types

Understanding GUIDs (05:50)

NEWID() vs. NEWSEQUENTTIALID() (05:09)

Using GUIDs (05:13)

Data Type Basics (05:44)

Numerical Data Types (04:50)

Character Data Types (04:49)

Date and Time Data Types (04:06)

Binary Data Types (03:02)

Using Joins

Understanding Joins (03:58)

Table Aliasing (02:42)

Inner Joins (06:46)

Outer Joins (04:41)

Cross Joins (04:28)

The Merge Statement pt. 1 (05:36)

The Merge Statement pt. 2 (05:07)

Querying Metadata

Catalog Views (05:55)

Information Schema Views (04:12)

System Stored Procedures (05:06)

Inserts / Updates / Deletes

The Basic INSERT Statement (03:17)

INSERT Examples (05:17)

The Basic UPDATE Statement (02:39)

UPDATE Examples (04:42)

The Basic DELETE Statement (02:53)

DELETE Examples (03:39)

Using @@ROWCOUNT (05:03)

Subqueries

Scalar Subqueries (03:42)

Multi-Valued Subqueries (04:23)

Correlated Subqueries (05:16)

Table Expressions

Understanding Temporary Results Sets (03:21)

Understanding Derived Tables (04:22)

Understanding Common Table Expressions (02:43)

Using CTEs (04:15)

Understanding Temporary Tables (04:32)

Table Variables (05:29)

Temporary Table or Table Variable? (02:48)

Stored Procedures

Understanding Stored Procedures (04:14)

Creating a Stored Procedure (04:10)

Using Input Parameters (05:33)

Stored Procedure Return Codes (04:06)

Stored Procedure Permissions (02:53)

Functions

Understanding Functions (03:40)

Built-in Functions (04:13)

Scalar Functions Example (05:30)

Table Valued Functions Example pt. 1 (05:30)

Table Valued Functions Example pt. 2 (04:51)

Transactions

Understanding Concurrency (03:52)

Understanding Transactions (04:36)

Coding Explicit Transactions (04:44)

Transactions and Locking (04:30)

Conclusion

Course Wrap Up (03:13)

Credits

About the Author (02:39)