Try our training for free.

Gain instant access to our entire IT training library for 1 week. Train anytime on your desktop, tablet, or mobile devices.

This SQL Server video training with Garth Schulte covers the latest version of Microsoft’s much in-demand server, and includes topics such as creating and altering tables with T-SQL, implementing subqueries, managing transactions, and more....
This SQL Server video training with Garth Schulte covers the latest version of Microsoft’s much in-demand server, and includes topics such as creating and altering tables with T-SQL, implementing subqueries, managing transactions, and more.

Related Area of Expertise:
  • Database Development

Recommended skills:
  • At least two years experience with writing queries

Recommended equipment:
  • Microsoft SQL Server 2012

Related certifications:
  • MCSA: SQL Server 2012
  • MCSE: Data Platform
  • MCSE: Business Intelligence
  • MCSM: Data Platform

Related job functions:
  • Database administrators
  • System engineers
  • Database developers
  • Database analysts

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 course 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 slice and dice the information stored in these database objects 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 course will prepare you for the 70-461 exam and beyond with real world examples!

1. Introduction to 70-461: Querying Microsoft SQL Server 2012 (35 min)
2. Creating and Altering Tables with T-SQL (34 min)
3. Creating and Altering Views with T-SQL (43 min)
4. Designing Views (32 min)
5. Creating and Modifying Constraints (42 min)
6. Creating and Modifying DML Triggers (46 min)
7. Querying Data using SELECT (58 min)
8. Implementing Subqueries (33 min)
9. Implementing Data Types (40 min)
10. Implementing Aggregates (44 min)
11. Querying and Managing XML Data (43 min)
12. Creating and Altering Stored Procedures (33 min)
13. Modifying Data with T-SQL (33 min)
14. Combining Datasets (22 min)
15. Working with Functions (42 min)
16. Optimizing Queries (37 min)
17. Managing Transactions (27 min)
18. Row-based vs. Set-based Operations (22 min)
19. Implementing Error Handling (15 min)

Introduction to 70-461: Querying Microsoft SQL Server 2012

00:00:01

An introduction to 70-461, query Microsoft SQL Server 2012. Hello, and welcome everyone. My name's Garth Schulte. I'll be your guide through this series. And a little background on myself and SQL Server. I've been working with it personally as a DBA and a developer since SQL Server 6.5, which kind of blows my mind.

00:00:18

Because that was 15 years ago. And SQL Server's come a long way since then. So this exam specifically from Microsoft is really focused on getting you familiar with how to work with database objects and the data stored inside these objects using Transact SQL, TSQL.

00:00:35

So we're going to get very familiar with Transact SQL both from a DDL perspective, data definition language statements, things like create, alter, and drop. And we'll get very familiar with DML statements, data manipulation statements, those statements such as select, insert, update, and delete, and all the fun stuff we can do with those.

00:00:51

And so we'll cover all the exam objectives that Microsoft's going to be expecting from us, and go way above and beyond that. I'll show you a lot of the tricks that I've learned over the years working on SQL Server, some time saving features, and just learning how to work with SQL Server and work with Transact SQL in a way that'll make you a very efficient programmer.

00:01:10

Or as I like to call us, DBDs, database developers. Hey, DBAs get their own acronym, so why not the developers, too? So we'll take a good look at it from both perspectives, from the DBA and the DBD perspective on how to query and work with SQL Server 2012. So with all that said, let's head over to our white boarding and take a look at the outline for this Nugget.

00:01:30

We're going to start with an exam overview. Are here, I just want to take you over to Microsoft's website and get you familiar with what is going to be required, or what Microsoft is going to expect from us as far as the objectives go on the exam. So we'll look at the 70-461 exam, check out the objectives, and see what this Nugget is going to cover.

00:01:47

Then, we'll take a look at the Nugget series overview. Just want to go over all the different Nuggets in this series, talk about the videos. And specifically here, find out how you can relate these two together. So if there's something that you're weak on or something that you just want to learn specifically inside of the exam objectives, you'll easily be able to find it and know exactly what video to go to inside of the series.

00:02:07

And finally, we're going to go take a look at how to install SQL Server 2012 Express Edition. And there are actually five editions out there of SQL Server 2012 Express. And I'll briefly go over each one of them. And then, we're going to head over to Nugget Lab, which is a new initiative here from Nuggets that allows you to work on the same exact server that we're going to be doing these Nuggets on.

00:02:28

So just to show you, you've probably seen this little icon in the upper right hand corner. This is a virtual instance that, again, you can also gain access to. Just head over to cbtnuggets.com or nuggetlab.com to find out more about how you can access Nugget Lab.

00:02:40

But it's a fresh install of Windows Server 2008 right now. So there's nothing on it. It's brand new. So we're going to head over. We're going to install SQL Server 2012 Express on it, get it set up. I'll show you how to set it up, the documentation, because that's changed a little bit in this version.

00:02:54

And then, we will do all of our demos on this Nugget. And we'll build it up. And I'll show you how we can access all the demos from using Nugget Lab. And we'll generally have a good time over there in Nugget Lab. And learn a lot, of course. All right, let's head over to Microsoft's website and take a look at the 70-461 example. So if we head down to the Start button and fire up Internet Explorer here, we can just Google 70-461. And we'll find out that it equals negative 391. But actually, what we really want here is the very first link that comes up, exam 70-461 from Microsoft. If we click on that and head on over here, I'm just going to zoom out a little bit here.

00:03:34

There we go. And here's the overview. So it just gives you a brief overview. It's intended for SQL Server DBAs, implementers, system engineers, and developers with a few years experience looking to prove their skills and knowledge in writing queries. So here's a list of some of the primary responsibilities and some of the things that we're going to need to know in order to pass the exam.

00:03:52

What I like to do is come over to the skills measured tab and take a look at really the details here of what they're going to be expecting. So you can see 24% of the exam is going to be focused on creating database objects. So this is where we're going to start.

00:04:05

And again, I modeled this series-- pretty much each one of these main bullet points here is a Nugget. So the very first Nugget that's going to follow after this introduction is going to be how to create and work with tables using Transact SQL. So some simple statements here, just get familiar with the syntax.

00:04:22

We're going to learn how to create tables without using the built in tools using things like the alter drop, the alter column, and obviously here the create table statement. So we'll take a good look how to create tables programmatically using Transact SQL.

00:04:34

Then, we'll do the same thing with views, how to create and alter views using create, alter, and drop. And then, we'll move into learning how to design views. So this is going to-- we're going to ensure code non-regression by keeping a consistent signature for procedures, views, and functions.

00:04:49

Otherwise known as interfaces, and what the security implications are when designing views. And if that is confusing, we'll make sure to clear it up here over there in the design views. It's actually not that bad. And it's pretty cool, too. It's a great way to learn how to not only design good views for use, but it's also cool.

00:05:08

I like this because it's a way that you can ensure backwards compatibility for many applications in your database. So we'll take a good look at how to properly design views there. We'll take a look how to create and modify constraints. We'll look at how to create and alter data manipulation statement triggers, DML triggers.

00:05:25

And that's really going to cover how to create database objects. So the big objects here are going to be tables, views, constraints, and triggers. Then, we'll head on down to working with data. We'll start this segment off with a big look at the select statement.

00:05:39

So select is our main statement here that we use to work with data, slice and dice data, pull data out of our tables to pass into client applications or that our views are going to use to do the same thing, pull the data out of the tables, pass it back to the applications.

00:05:53

Or ourselves, if we just want to write some statements against tables to analyze data and such. So we'll take a big look at the select statement here, everything from how to work with ranking functions, which I personally like and I use quite a bit inside of the .NET

00:06:06

world when I build applications. I'm always using ranking functions, really cool stuff. We'll learn how to write and perform queries efficiently. We'll use some of the new code items here, synonyms and joins using the except and intersect keywords here, implement logic that uses dynamic SQL and system metadata.

00:06:23

Learn how to write efficient, technically complex SQL queries including all types of joins versus the use of drive tables. So you can see, this is going to be a big Nugget. This will be a long one, I guarantee you. And here at the end here, we'll take a look at the case versus isNull versus the coalesce statement.

00:06:39

I love the coalesce statement. I'll do some good real world examples of how this is useful so you can get the hang of this. Believe me, you'll be using it everywhere. It's really neat to format data output. We'll also take a look at how to implement sub-queries.

00:06:50

So we'll look at, how do I identify problematic elements in query plans? That's cool stuff. So we'll get familiar with how to work with query plans here, pivot and un-pivot, the Apply operator, CTE statements, which are common table expressions, and the with statement.

00:07:04

So we'll take a good look here at sub-queries and some of the different things inside of there. We'll look at how to implement data types. We'll look at a lot of different data types here, how to understand and use the right data type for the column. We'll look at the GUID, the global unique identifier using new ID and the new sequential ID and how that affects database performance.

00:07:23

And again here, when to use which data type for specific columns. We'll then also look at aggregate queries and how to work with some of the new functions inside of SQL Server 2012. We've got some new analytic functions, grouping sets, spatial aggregates a lot of people are excited about.

00:07:39

So we'll look at what those are all about. And then, how to apply a ranking function. So we'll take a good look here at how to work with aggregates. And then, the last part of working with data here, we'll look at how to query and manage XML data. We'll get familiar with some of the XML data types, their limitations, restrictions, implement XML schemas, handle XML data, and just generally work with XML through importing, exporting, and also how to index XML columns.

00:08:03

All right, scrolling down here, the next section is dealing with data modifications. So we'll take a look at how to modify data. 24% of the exam is on modifying data. And we'll start with stored procedures. Everybody loves stored procedures. They're awesome.

00:08:16

They're a great way to speed up application performance. They're just queries that sit on the server side, optimized for the server side. And so we'll take a good look at how to build stored procedures. I've built thousands in my day. Still build them every day, and I love them.

00:08:28

They're great. They're great, especially for slow performing applications. Great way to speed things up and keep the logic on the database side. A lot easier to change logic that's stored in the database than it is compiled inside of an application. So we'll take a good look there.

00:08:42

We'll also go ahead on how to modify data using the three big data modification statements here. Insert, used to add new data. Update, change existing data. Delete, remove data out of tables. So we'll take a good look at these. We'll look how to combine data sets.

00:08:56

We'll take a look at the union versus union all. Microsoft has this in two places, right, case versus null coalesce. That's already up in the select statement. So we'll probably look at that up there. And here, we'll look how to modify data using the new merge statement that also came out in SQL Server 2008. Then, we'll look at how to work with functions.

00:09:16

Here, we'll take a look at deterministic versus non-deterministic functions, scalar and table valued functions, how to apply built in scalar functions, and then just generally create UDFs, user defined functions. And finally, the last 25% of the exam is going to deal with troubleshooting and optimizing queries.

00:09:34

So we'll start with a Nugget on optimizing queries here. We'll learn how to understand statistics, database statistics, and the object that contains all the statistics for the columns inside of the tables. We'll learn how to read query plans, look at plan guides, look at DMVs-- and no, that's not the Department of Motor Vehicles, although that's funny that that's in the optimize area because nobody wants to stand in line all day, right? That could be highly optimized.

00:09:59

But this is more for the dynamic management views that we can use to check out the status of a server. We'll look at some hints here, table hints, statistics, input, output, dynamic versus parameterized queries. We'll look at the different join types inside of the query plans and how SQL Server internally fetches this data.

00:10:17

So we'll look at the hash merge and join types and describe the scenarios in which they would be used. So we'll take a good look here at optimized queries. We'll also look at how to manage transactions, everything from marking a transactions to getting familiar with the begin, commit, and roll back trans, implicit versus explicit transaction, look at isolation levels, and scope and the type of locks, trancount.

00:10:38

So just pretty much everything there is to do with transactions. And we'll look at how to evaluate the use of row based operations versus set based operation. So when to use cursors, basically when to go row by row. It's kind of like programming where row based operations is looping through the table one row at a time, performing logic on every row.

00:10:59

Set based is working with an entire result set, so the entire table at once, or a chunk of data at once. So evaluate the use of both of those here. See when to use cursors here, which is pretty much never these days. The impact of scalar user defined functions.

00:11:16

We'll combine multiple DML operations, all that good stuff. And finally here, we'll take a look at how to implement error handling. Look at the try catch throw statements inside of SQL Server. We'll use set based rather than row based logic and deal with transaction management.

00:11:31

So a small Nugget there on how to work with error handling inside of Transact SQL. So that's what this series is going to cover. That's pretty much querying Microsoft SQL Server 2012. And again, we're going to go far above and beyond that. I'll show you a lot of cool things along the way here and get as much extra stuff as I can squeeze into this series.

00:11:48

But we'll definitely cover all of this stuff and more. All right, let's close out of this. And now, what I want to show you here going back to the white board is our Nugget series overview. So if we head over there, this is going to look really familiar. See this? I broke it out by each of those areas.

00:12:03

So pretty much, we got close to 25% in each segment here, give or take a little bit more in certain areas. But you can see we're creating database objects starting with Nugget number one here, creating and altering tables with TSQL, then views with TSQL, designing views, modifying constraints, modifying DML triggers.

00:12:20

So you can see the relation here between the series and the objectives. I tried to marry them as closely as possible. That way, it'll be very easy for you to find out what objectives are taught inside of which Nugget. All right, and I won't go through all these because we just did over on Microsoft's website.

00:12:38

I just wanted to show you how to relate the two together there. Now, let's minimize the white board. I want to head over to Nugget Lab and get installation of SQL Server 2012 up and running here, Express Edition. Now again, Nugget Lab, it's a virtual instance out there on the internet that we're going to be doing all of our demos on.

00:12:53

By the time we're done with this series, there's going to be a nice collection of all the demos broken out by Nugget. And again, you can have access to this exact image here, the exact instance of SQL Server Express Server we're going to be working on. It'll all be preloaded for you, and all the demos will be there for you to work with.

00:13:09

So you can follow along. So again, visit cbtnuggets.com or nuggetlab.com to find out more information about how you can access this Nugget Lab instance. So let's head over to Nugget Lab. I'll double click on the icon here. This will get us a remote desktop connection over to our Nugget Lab instance.

00:13:25

And again, this is a fresh install of Windows Server 2008 R2. I haven't done anything at all to this server. So the first thing we do need to do is get SQL Server 2012 Express Edition installed. Now, I already have it downloaded on this machine. But I still want to show you where you can find it.

00:13:41

So let's head down to Start. Hit up Internet Explorer here. And let's just go over to Google. And we're just going to type in SQL Server 2012 Express. We'll search for this. Why wait when we can click on that now? And oh, something happened there. Must have clicked too fast.

00:14:04

Here we go. So the first link that comes up, it takes us right to the Microsoft Express page for SQL, the latest version here. So if we click on that, it'll get us right there. You can click on the link right here. That will take you to the download page.

00:14:16

There's also a link over here to the right that says, Get SQL Server 2012 Express. So let's click on that guy. And this will take you right to the download page. So you can download these products in 32-bit or 64-bit. We're in a 64-bit machine here, so let's drop this down. Here are the five editions that we can download.

00:14:35

Local DB is brand new to the Express Suite. This is really targeted for developers because it's a very lightweight version of the database engine that they can quickly get installed and up and running. It's actually a zero configuration installation. You don't have to go through all these wizards and configure everything.

00:14:52

It's just launch it, and it's done. It installs. And what's neat about this is a developer can install it on their laptop. They can actually access it through their development environment, things like Visual Studio, .NET. They can hook right into using some tools and designers inside of Visual Studio that'll allow them to work with the instances.

00:15:11

So that's brand new, and it's very lightweight, easy for developers to use. And that's what developer should use to design against a SQL Server Express database. Then, we have Express with the database only. And very similar to Local DB, only of course you have to go through the installation, choose your instance, set everything up through the wizard.

00:15:31

So it's your typical Express Install, only it doesn't come with tools. So the only way you'll be able to access this is if you already have the tools installed on the server that you're installing Express, or remotely using SQL Server Management Studio that's installed on a different machine.

00:15:44

The third version we have here is Express with tools. So this is both Express, the engine itself, with SQL Server Management Studio and the tools that come with it. And this fourth one is SQL Server Management Studio Express. This is just simply the tools by themselves, no database engine.

00:15:59

So if you need just to install the tools on a remote machine that needs access to a SQL Server Express Edition, you can just download and install the tools. And finally, what we're going to use and what I've downloaded is Express with Advanced Services. In the training world and when you're dealing with wanting to learn this stuff, I always like to download the biggest and the best because it has everything.

00:16:20

And that's what this has. It has everything-- reporting services, integration services, all what they call the Advanced Service Suite inside of SQL Server 2012. So this is what I downloaded. I'd like to show you how to set it up here in case you want to get adventurous and go outside of this Nugget and get familiar with things like reporting services, integration services, and that kind of stuff here.

00:16:39

So you would click on this, hit the Download button, and that'll start your download there. So again, I already downloaded this. So let's close out of the browser here. Yeah, we want to close all tabs. And I'm going to go to Start. I'm just going to hit Run here.

00:16:54

And let's just go right to the C drive. And I made a folder in the C drive, the root called 70-461 Support Files. So if we go in here, I downloaded SQL Server Express Advanced Tools 64-bit in English. So this is what we're going to install. I've also here-- what I want to show you after we get this up and running is I downloaded the AdventureWorks 2012 database. This is the sample database that comes separate with SQL Server that you can download and attach to it.

00:17:23

And it's a great database to get familiar with things because it's got everything from simple structures in it to complex structures to views. It's really a great sample database. So if you want to play around with it, it'll be on the server. We'll definitely use it in some demos here to get familiar with things.

00:17:37

We're also going to do our own thing, as well. But it's nice that we have a sample database filled with data for us to work with, all sorts of different data. So that's why I like to install it. And any time I want to test out something new, rather than create a new database, create a new table structure and all that, I just like to pop over to AdventureWorks, run some tests out, just maybe get familiar with the new features or new statements in TSQL, that kind of stuff.

00:18:00

So I definitely love using AdventureWorks. I use it all the time to do that kind of stuff. Now, I'll also show you where we can download this when we get there. But first, let's install SQL Server 2012 Express Edition. I'm just going to double click on this.

00:18:13

It's going to unpack the files for us here, and then hopefully launch the installation manager here. So we'll just give this a second. I'm actually just going to pause this until it's done unpacking. So I'll be right back. All right, we're all done here unpacking the SQL Server installation.

00:18:29

So I'm just going to maximize our screen. And on our installation screen here, we can install a standalone edition. We can upgrade from an existing version of SQL Server here. We're just going to install the brand new installation. It's going to unpack everything once again, just prepare SQL Server 2012 for install. And shortly after this here, it'll fire up our installation wizard.

00:18:50

So here it is. And the first thing it needs to do is just go through the set up, install the set up files here. I'm just going to minimize this screen for now so it's not in the way. And it's also going to check for updates. So actually, the first thing it did there is just get the support files going.

00:19:04

Now, it's going to check for product updates, which it did. Nothing out there, brand new. We'd hope not. So let's hit Next. Now, it's just going to install the set up files. So I'll just give this a second while it installs the setup files and be right back.

00:19:18

All right, so it's all set here with the support files. It went through all that and skipped to this screen, which just goes through and checks out all the rules against it. So we pass all the rules here except one, Windows Firewall is enabled. So it's just going to let you know to make sure the appropriate ports are open to enable remote access.

00:19:35

So once this is done here, we can hit Next. We'll go through the license term. We can accept the terms. You can optionally here send usage data to Microsoft. Hit Next. And now comes the feature selection. And here it is. Here, we're going to install the instance features here, the database engine, replication, full text, reporting services, native mode, the data tools, documentation components, management tools, the SDK here for client connectivity, and here's the Local DB option as well.

00:20:06

So we're going to leave all these checked. This is what the default is, which is pretty much everything. And here's our default installation paths. And we'll hit Next. Now, the next area here is going to be our instance configuration. And let's just call this here-- we're going to do a named instance.

00:20:23

We'll call this SQL 2012 XP. So that'll be the instance we work with here. When we tab off that field, that's what it'll give our instance ID. And then, it'll also scan here if there's any previous installations, which there aren't. If you do have previous installations, it'll tell you what the name is, what the features are, the edition, the version, all that good stuff.

00:20:42

We're good here. So we'll hit Next once again. And now, the disk space requirements. We should have plenty of that on this machine, as we do. So it just gives us a little overview here of the disk usage summary and where things are going. So if we hit Next, we'll go to Server Configuration.

00:20:57

We can set up our service accounts for the database engine, reporting services for the full text launcher here, and then for SQL Server browser. So we can find this on the network. So we'll leave all the defaults on those. You can also set up your collation here.

00:21:10

We're just going to hit Next. You can pretty much leave all those at the default here for our test environment. And then comes time to configure the database engine itself. So we can choose a server configuration. What type of authentication do we want to do? We can do Windows authentication or mixed mode, where we do both SQL Server and Windows authentication.

00:21:29

We'll just keep this simple for now and stick with Windows authentication. And here is the SQL Server administrator, which is me, my account here. And the name of this box, by the way, on our Nugget Lab server, is actually SQL Nugget. I changed the name to SQL Nugget.

00:21:43

So that's going to be the name of the server here. And then, you can do some other things here. You can specify your data directory. So here are all the data directories here for the root, data root directory. We've got the database directory, the log directory, the temp database directory, temp log, and then our backup directory.

00:21:58

We'll leave all the defaults there. You can configure user instances and also files streams. So we'll leave all that the way it is here. Everything looks good. Let's hit Next. And now, for reporting services, since really we don't have anything to do with reporting services in this Nugget, I'd just like to get it installed again in case you want to venture outside of this Nugget and play with it.

00:22:19

We will just choose Install Only. We will not configure it here in the set up. We'll choose Next. Then, you have some error reporting here. If you want to send errors to Microsoft, feel free. And finally here, it's just going to go through the installation configuration rules followed by the progress.

00:22:36

So this is going to take a little bit to install SQL Server 2012. I'm going to let it go. I'll be back when it's done. And we're back once again here. The last screen of the set up just gives us an overview of everything that was installed. So we've got the management tools installed.

00:22:50

This is SQL Server Management tools and some extra database tools like the profiler and stuff. SQL Server data tools is actually Business Intelligence Development Studio, otherwise known as BIDS. It's really just Visual Studio for SQL Server development.

00:23:04

So you can build things like reports and reporting services, integration services packages, things of that nature. We've got the database engine services. This is just the core SQL Server engine service. Full text was installed. Replication, reporting services in native mode.

00:23:20

Scroll down a little bit, here we have SQL browser documentation components. So one thing I do want to show you about documentation is it's not installed by default. It actually is the help software that points to the online version. So I'm going to show you how you can install help because it'll speed things up here, especially because we're going to be using it.

00:23:41

Show you some cool things you can do so you can learn any statement on your own, or learn the syntax of the statement. So it's a good idea to get books online. There's documentation for SQL Server installed locally. We've got SQL Writer connectivity components and the SDK also installed.

00:23:55

So we're good to go on this. Let's go ahead and close out of this. Let's take a look at what the installation did. So let's head down to the Start menu, up to All Programs. Here we have a SQL Server 2012 program area here. So here's all the components that were installed.

00:24:10

And we have a Visual Studio 2010 area, where you can see Visual Studio 2010 was installed. So going through some of the SQL Server stuff, you've got the Import and Export Wizard for 32 and 64-bit. We've got SQL Server data tools. This is Business Intelligence Development Studio renamed, and thankfully they did it because that's a mouthful to say.

00:24:30

And so I'm happy. It makes a lot more sense. SQL Server Data Tools, it sounds a lot better, looks a lot better. And you'll notice that the icons are the same here. That's because really what it is is it's Visual Studio geared towards SQL Server and the DBAs and DBDs of the world, and has all the projects that we can work with to do things like create reporting services, report projects, or create integration services projects.

00:24:53

So that's that. We also have SQL Server Management Studio, which is what we'll be spending most of our time in. We'll come in there shortly to configure the sample AdventureWorks database. We have configuration tools here. If you want to configure reporting services, this is where you would do it.

00:25:07

If you want to configure SQL Servers as far as service configuration, network configuration, client connectivity, all that kind of stuff is inside of the Configuration Manager. Error and usage reporting, which we touched on inside of the installation, and then the installation center, which we were just in, to install SQL Server.

00:25:23

So those are the configuration tools. We have the documentation and community here, which we're going to come back to in a second to get books online, SQL Server's help documentation, installed locally. And finally, you have a bunch of tools for integration services in here like the data profile viewer deployment wizard, execute package utility, and the project conversion wizard.

00:25:42

So let's install the documentation here, books online. And what we can do is hit this Manage Help Settings menu item here. And this is going to take us to the help library manager. This is Microsoft's Help Viewer 1.0. This is really cool stuff. I actually really like this because it's kind of a simplified, centralized way to get help installed not just for SQL Server, but anything that's installed on your system.

00:26:04

So I'm a big fan of this. And here's how it works. Let's install content from online. And it's just going to scan, see what we have installed on our system, compare it with what's available online for the Help Viewer 1.0 and give us everything. So not only will SQL Server documentation be in here, but a lot of things like .NET development.

00:26:23

If you want to do any .NET development, read the documentation on that, you can hit Add here, update pending, and it shows just the size. So we're not going to do that. But you can if you want to. You've got BizTalk Server 2010 for Office, patterns and practices, all very good stuff.

00:26:38

Highly recommended if you're ever going to get into development. SharePoint information, here's SQL Server 2012. So we want books online. And again here, I just like to install everything so it's all on here in case you want to play and get out of your comfort zone or out of this Nugget series.

00:26:54

So I'm going to add the developer reference because of that. And also, the installation. So we've got all the documentation for SQL Server 2012 locally. Here are the sizes over on the right. And then, you've got some other things in here. So all the Visual Studio documentation you can install, Web Developer, Windows is here, the development driver kit phone development, and the XNA Game Studio.

00:27:15

So we're good, though. We've got everything in SQL Server 2012 checked and ready. Update pending. So let's hit Update. I'm going to put you on hold one more time while this downloads from the internet and installs it locally. Be right back. All right, installation is complete.

00:27:29

Once you hit Finish, it'll take you back to Help Library Manager screen here. And you can just double check through choosing online or local help that we're using local help. And we are. So we'll cancel out of this. Let's exit out of the library manager and just take a quick look at the documentation.

00:27:42

So go to Start, All Programs, SQL Server 2012, Documentation and Community. And the bottom one inside of here, SQL Server Documentation, will launch the help viewer. And check it out. All three that we chose to install are here. We have the books online, which is the main instruction manual for all things SQL Server 2012. We have the developer reference for all the different tools and components, including the database engine.

00:28:05

And then, we have the installation guide as well here. So all three areas are here. We're not going to be specifically coming into books online like I am here. We're going to use it in a context sensitive manner. And what that basically means is when we're in Transact SQL and there's a statement, pretty much anything that's highlighted in blue or pink, or anything that's a SQL Server keyword, if you highlight it are double click on it and hit Shift F1 on your keyboard, it'll take you right to that area inside of the documentation and give us help for that specific statement or keyword.

00:28:36

Things like syntax, what all the different parameters are, describe them, and give us an example of how to use it. So it's awesome. I use it all the time. I love it. And it'll save you a ton of time. And it's a great way to learn a lot of the different language constructs inside of Transact SQL.

00:28:49

So we'll get there in future Nuggets. We'll use it extensively and hopefully learn a lot. So let's close out of help. The last thing I want to show you here is how to install the AdventureWorks sample database. But first, I need to show you where to find it.

00:29:05

So let's hit the Start button one more time here. And boy, this Start button's getting a work out tonight, huh? And let's hit the Internet Explorer here. Go to Google. And once we're at the Google, let's type in AdventureWorks 2012. All right, and the first link that comes up is good.

00:29:23

This will take us right to the database product samples page here. And there's going to be a lot to choose from down here. There's things for data warehousing, multidimensional models. There's online transaction processing scripts down below. But really what we're concerned with here is the recommended download, the big one here, AdventureWorks 2012 data file. This will give us the .mdf, the primary data file, which

00:29:45

is all we need. All right, so I already have this downloaded. So I'm going to close out of the browser here, go back to the file system. And again, it's in the C drive here. It's in our 70-461 support files. And there it is, AdventureWorks 2012_data.mdf. So that's what we're going to attach to.

00:30:02

Let's minimize this. And let's launch SQL Server 2012 Management Studio. So let's go to Start, All Programs, SQL Server 2012. Let's launch the Management Studio. And you'll notice that it's going to look a little different than it used to. They used better colors in my opinion here.

00:30:20

And the whole point to this was to make it less distracting. So just muted colors. And I like it. I've been working with Visual Studio 2010 for a while now, for a few years. And it's got the same kind of interface with the same colors. And it's awesome. It's really cool, professional, slick looking and all that.

00:30:38

So kind of neat. So let's connect to the instance we installed here, SQL Nugget, backslash SQL 2012 XP. There it is. And if we expand the databases node, we have nothing in there but system databases. So let's right click on that databases node, hit the attach button, hit the Add button here to specify an MDF file location.

00:30:59

So if we hit Add, we're going to point right to our MDF file in our support files directory. And let's just close out of this. You'll of course get prompted to make the experience better. And we'll just hit OK here. And this will just tell us where it is, what the database name is, how we're going to attach it as the database name.

00:31:17

And again, you have full control over this stuff. So if you don't like AdventureWorks 2012, you can change it. Who's the owner, and just some extra stats on it there. And then, it's telling us there's no log file found. And it defaults to what our installation log directory was.

00:31:32

So what you want to do here, you'll get an error if you leave it as it is because it won't be able to find the log file. So here's what I do-- remove it. Get it out of there. Now, it'll create a log file on the fly in the same location as our primary data file.

00:31:45

So that's the only trick to doing this. So once we hit OK, it's going to bring it in. And there it is. Here is the AdventureWorks 2012 database. All the tables that we'll be working with, here is stored procedures, in here as well, views, you name it, we'll work with it inside of here.

00:32:02

And of course, we'll make our own databases and work a lot on our own stuff, as well. But for some of the advanced things, there's already some good data in here, some good examples that we're going to use here. So we've got it up and running. SQL Server is also installed and up and running.

00:32:16

So everything looks good. There's just one more thing I want to show you, always one more thing. And this might be the developer coming out in me, but this'll definitely help you stay organized within Management Studio. This is really cool stuff. I love doing this.

00:32:28

If you go to the File menu, New, Project. And you may already do this. But if not, this'll definitely save you some time in the long run. And you can also kind of build up your library of queries and projects. And everything that you work, eventually you can just keep evolving over time.

00:32:44

You have a nice spot for all your scripts, everything. Right, so let's name this solution 70-461. We'll just call this Querying SQL 2012. And then, the name here is going to be the project. So basically what you have here, you have a solution which is just a container for all the projects.

00:33:06

And for now, we'll just go ahead and call this 01-Creating_Tables, which is going to be the first Nugget in the series. And we may come back and change this. But that's what I'll end up doing just to show you the structure and the naming convention I'm using.

00:33:23

If we hit OK here, that's going to create a solution for us. And over here on the right hand side, if it doesn't show up, what you can do is go to View, Solution Explorer. And this is what we want to see. So in here, we have the name of our solution. Again, just a container here for all of the project files.

00:33:40

And then, what we'll do is every Nugget when we create a demo, we will create a new project. And we'll put all our queries in here. We can have connections inside of here, which is kind of neat. Because if you have lots of databases you're connect to and running samples scripts against, whether it's a dev database or a production database, you can point it to a connection.

00:33:57

So not only can you manage your connections, you can manage your queries. And then, you can put scripts inside of miscellaneous. So just a cool little project to help you stay organized. And this is really nice in this Nugget series because again, we'll have a project for every one.

00:34:10

So eventually when we're done, then, the solution will be filled with all of our projects, all the demos. So you'll have a nice, centralized place for everything, Querying SQL Server 2012. So we are officially ready to rock. All right, so in this CBT Nugget, we took a look at an introduction to 70-461, Querying Microsoft SQL Server 2012. We started off with a look at the exam objectives, went over to Microsoft's website, took a look at what's to be expected on the exam, all the objectives we should be familiar with.

00:34:40

And then, we came back here and looked at the series outline for this Nugget series and saw that it's very close to what the objectives are. And we did that by design here so you could easily find what you're looking for if you need to freshen up on a specific topic.

00:34:53

And then, we jumped in and learned how to install SQL Server 2012 Express. We looked at all the editions, we got an installation going, we installed the help documentation locally. And we even got the sample AdventureWorks database downloaded, attached, and installed inside of our SQL Server instance.

Creating and Altering Tables with T-SQL

Creating and Altering Views with T-SQL

Designing Views

Creating and Modifying Constraints

Creating and Modifying DML Triggers

Querying Data using SELECT

Implementing Subqueries

Implementing Data Types

Implementing Aggregates

Querying and Managing XML Data

Creating and Altering Stored Procedures

Modifying Data with T-SQL

Combining Datasets

Working with Functions

Optimizing Queries

Managing Transactions

Row-based vs. Set-based Operations

Implementing Error Handling

Please help us improve by sharing your feedback on training courses and videos. For customer service questions, please contact our support team. The views expressed in comments reflect those of the author and not of CBT Nuggets. We reserve the right to remove comments that do not adhere to our community standards.

comments powered by Disqus
Entry 12 hrs 19 videos

COURSE RATING

Basic Plan Features


Speed Control
Included in this course
Play videos at a faster or slower pace.

Bookmarks
Included in this course
Pick up where you left off watching a video.

Notes
Included in this course
Jot down information to refer back to at a later time.

Closed Captions
Included in this course
Follow what the trainers are saying with ease.

NuggetLab
Included in this course
Files/materials that supplement the video training

Premium Plan Features


Practice Exams
Included in this course
These practice tests help you review your knowledge and prepare you for exams.

Virtual Lab
Included in this course
Use a virtual environment to reinforce what you are learning and get hands-on experience.

Offline Training
Included in this course
Our mobile apps offer the ability to download videos and train anytime, anywhere offline.

Accountability Coaching
Included in this course
Develop and maintain a study plan with assistance from coaches.
Garth Schulte
Nugget trainer since 2002