Microsoft SQL Server 2014 - 70-461

Querying Microsoft SQL Server 201419 H 15 M

This course helps certification candidates by describing, discussing, and demonstrating SQL query writing skills in multiple areas to help you prep for the exam.

Just you? Training a whole team? There's an ITProTV plan that fits.

Start Training Today
This course has a virtual lab
  • Create database objects
    • Overview
    • SQL Server Basics
    • SQL Server Basics Part 2
    • SQL Querying Basics
    • Create and Alter Tables
    • Create and Alter Views
    • Create and Alter Views Part 2
    • Create and Alter Views Part 3
    • Create and Modify Constraints
    • Create and Modify Constraints Part 2
    • Create and Alter DML Triggers
  • Work with data
    • Basic SELECT Queries
    • Basic SELECT Queries Part 2
    • Query Using JOINS
    • Query Using JOINS Part 2
    • Implement Subqueries
    • Implement Subqueries Part 2
    • Implement Subqueries Part 3
    • Implement Aggregate Queries
    • Implement Aggregate Queries Part 2
    • Implement Aggregate Queries Part 3
    • Query Using Synonyms, Except and Intersect
    • Understand Data Access Technologies
    • Work with Conditions and Nulls
    • Work with Table Constraints
    • Query and Manage XML Data
    • Query and Manage XML Data Part 2
    • Implement Data Types
    • Implement Data Types Part 2
  • Modify data
    • Create and Alter Stored Procedures
    • INSERT, UPDATE and DELETE Part 2
    • Combine Datasets
    • Work with Functions
  • Troubleshoot and optimize
    • Optimize Queries
    • Optimize Queries Part 2
    • Optimize Queries Part 3
    • Manage Transactions
    • Evaluate Different Operations
    • Implement Error Handling


7 M

itprotv course thumbnailitprotv course thumbnailitprotv course thumbnail
  • Episode Description
  • Transcript

In this episode, Daniel and Ronnie give a general overview of what to expect in the upcoming Querying SQL series. They cover topics like who is the intended audience, what the scope of the series will cover, and what are some specific topics that will be addressed.

[SOUND] In this segment, we're gonna give you a general overview on our upcoming series on querying the Microsoft SQL server 2014. And Ronnie I'm glad you're here with me today because I was hoping you could give us a very good overview, at least a general expectation of what we should, well expect, on the upcoming series. >> Sure that'd be a great way for us to get started when we start in on Microsoft SQL server 2014. Now let's go ahead and take a look at the official exam objectives here. And that way that will help us to get situated in the mindset that we need. So on my screen I went ahead and pulled up the official exam objective page. Now you're going to notice something. Even though we centered in on SQL server 2014, we use that as the environment for everything that we did in our show. You'll notice it's actually for querying, well both SQL Server 2012 and 2014, the entire exam actually centers around both of them. But the great thing is everything that we've actually done in the show should work for both products. So that's why we're not so much worried about going between 2012 and 2014 SQL server right at the beginning here. Now when it comes down to what we end up covering in the exam objectives itself, we take the official exam objectives and what we're going to do of course is we're going to essentially cover all the elements in those exam objectives. So where we're gonna begin is taking a look of course at the database objects as well. So we are going to make sure that we understand how of course to create and alter tables and that's of course using SQL querying to help us to understand those elements that we need and the syntax that we need to do that. As well as create the idea of views which is a very powerful way that we can get the information that we need and actually limit that particular exposure to the entire database as well. We'll talk about of course the idea of designing the views. And then table constraints as well as some of the triggers that we can use to help us to ensure that if we do one thing, we can also trigger another process, as well. Now, we also want to actually took a look there because that's about a quarter of the exam is working with data. And this is the main process that we actually spend a little bit more time on. This takes up almost a full third of the exam itself. Where we begin with simple select statements. And we'll actually see that quite often as we actually go in there that more than likely select statements are where we'll focus in so we do a lot of examples from this idea and show you some of the different ways we can do it. Including of course building sub queries and working with data types as well as aggregates. And then we do a little bit of a brief example here in terms of xml querying as well. Okay and then we go into modifying our data and this is where we get into a little bit more advanced things that we can do inside of querying our SQL server. When we start to create and of course even alter the idea of stored procedures. Which allows us, once again, to be able to limit the access that other people can have to our database if we choose to. And to make sure that the complex logic behind some of the things that we have to do, is not gonna ever have to be exposed to a course our customers as well. Being able to build other objects inside using Insert, Delete, and Update, as well as combining data sets together, and of course, basic working with functions. Our last little section is going to be on optimizing our queries, talking about how transactions are managed here. As well as of course the idea of error handling. And then noticing the differences between row based operations and of course set based operations too. Now all those elements, every single episode that we actually are going to do You're gonna see that it's gonna be jam packed with examples and of course actually work inside of the query analyzer. Now, the reason why I want to point that out is because it does require you then as you are preparing for it not just enough that you can watch the episodes here. But you do need to get your hands in working inside the SQL and actually doing the querying that you need to do. And that's actually very important. The reason why is that you're gonna see that a lot of times, especially when you're getting ready for the exam that's all about comparing what queries actually do work and what they don't. So understanding the syntax, getting the muscle memory that you need to involved is actually gonna be very important for just about everything that we do for the exam as well. Now I do want us to make sure that we understand there is a couple of things we would consider prerequisites, when getting ready for the exam, and so lets go and take a look at my page again and this should help us out to. Okay, so notice that it even says who should actually take this exam here? It says this exam is intended for SQL Server database administrators, system engineers, and developers. And the key is right here at the end, two or more years of experience who are seeking to validate their knowledge in of course in writing queries. Now this is actually very important. It doesn't mean that you cannot take the exam any time you want to. Regardless of whether or not you have the SQL experience or not but what it does mean is if you don't you're going to be very confused by some of the things that they're going to show you. They're gonna implement of course different scenarios that you might not be familiar with if you're not working with SQL pretty much on a daily basis. So do not expect as a beginner here if you're just learning SQL that this is the exam for you right away. You may wanna make sure that you ahead and have the foundational concepts down and that you feel very comfortable in writing at least basic SQL statements before you start actually on this as a track that you may wish to follow. So it's very important that we do that because a lot of times maybe you have this expectation that this is a very much a fundamental exam. That you can jump right into it but you gonna see challenges here that you may not expect to see and you do not work in SQL just about on a daily basis. Now overall though for the exam itself the price in US dollars is about $150 here. So it does require you to pay that exam fee. So you go to your Pearson Vue testing center. And don't forget, you do actually have to sign up for this. You can go online to make sure you have your account, and that you pay the fee, and register to the nearest Pearson Vue center, where you'll schedule a date and then you will go in and take that exam as well. So, this particular exam even though it may cover both of them everything that we'll cover essentially works on both. And the other thing, if you are really concerned about the idea that hey, 2012 is different from 2014, you'll see that Microsoft itself, in December 2015 here, okay? Evaluated that the exam didn't need to be updated between both. So in other words, whatever you actually learned on SQL Server 2012, if you've already been studying for that you can jump right into this and this should also to continue to help you out as well. So there's some details in it, but you actually see that we'll cover a lot of them. And we actually make sure that we use a bunch of examples as well to ensure that you actually have the most visuals that you can and you'll actually be typing in SQL, just about, I want to say at least 50% of the content that we end up actually covering will end up being straight examples that we end up using. So just make sure that you take the time, go through that. And make sure you study on your own as well. And that's really going to help you. If you're working towards your SQL server MCSA. >> All right, Ronnie well thank you so much for that overview, it was great. And hopefully if that's something that sounds like that you might be interested in or that you need to prepare for to get that actual exam certification underneath your belt, we look forward to seeing you in the upcoming episodes. [SOUND]

Start training today

Just you? Check out our personal plans


$529 per seat/per year


Total seats


$349 per seat/per year


Total seats

Credit card required

This is for your account.
This is for your account.
We will contact you with this phone number about your trial.
We will contact you with this email about your trial.
What is the name of your company?
In which country is your company located?

Step 1 of 2

Get a demo or a start a team trial