Microsoft SQL Server 2016: Querying Data with Transact-SQL - 70-761

This course introduces viewers to Microsoft Transact-SQL, the language used to input and retrieve data from Microsoft SQL servers. The series is designed for viewers of all experience levels as it walks through running your very first query all the way through crafting advanced server-side stored procedures.

[MUSIC] [SOUND] Hello, thank you for choosing ITProTV. We're talking about Querying Data with Transact-SQL, with Don Pezet. Don, tell us about Transact-SQL. So Transact-SQL, it's actually an abbreviation for Transact Structured Query Language. It is a language that we use to talk to databases. So if you have a relational database, there's a ton of great information in there. You have to be able to get it out. And one of the most common ways to do that is through SQL, or the Structured Query Language. Well, Transact-SQL is Microsoft's implementation of SQL. And it's what they use on their server products. So if you're using Microsoft SQL Server, well, really, all the way back, or a long time, since their earliest versions of SQL Server, but all the way to the newer ones like 2016, which is what we're gonna be using here in this show. Transact-SQL is how you're going to talk to that server, so in this series, that's one of the things that we're going to learn is, if I've got a Microsoft SQL Server 2016, How do I talk to it? Now the cool part is that most of this is standards based, so what you learn in this series can apply to SQL 2014, SQL 2012, SQL 2000, SQL 6. I mean, it just goes way, way back. These languages, they haven't changed all that much in all these years. Now, the way the databases optimized their performance, the way they work. The volumes of data that they can handle, have gotten much larger and evolved quite a bit. But by having a standard language, it gives us the ability to do something awesome. Which is to learn how to talk to the database service in a consistent manner. And that's what this course is all about. >> You already got me excited about the whole thing, but who is this show targeted towards? >> All right, well, it's really targeted to anybody who needs to get data out of the database. Now, I could kinda trim that down a little bit, like if you don't really care about database. You just want the data, there's plenty of third party graphic utilities you can use. There's things like Microsoft Access that gives you a little Wizard that you can walk through, but if you're trying to go to scale. If you're trying to be able to handle any kind of high volume transactions. Those hand holding type programs, they don't work. We actually have to get in and talk to the database server. Be able to get that information and put information in, and that's where SQL comes in. So if your job responsibility involves that, this is what you want to learn. Now what types of jobs would do that, developers. Developers often times have to store data for their applications in a database. And be able to get the data back out for their application uses, so they need to know SQL. We also have DevOps, people that are standing up their own database server, right? They still need to be able to talk to it. We also have system administrators. If you're a system administrator and you're responsible for standing up a SQL Server. Well, some people just have to install the server and then they turn it over to the dev team and that's it. But more often than not, we're gonna need to be able to monitor and maintain that database. And the way we do that is by querying against it. Sending it commands, seeing how it responds, that's how we know it's up and active. Just because the server's powered on doesn't mean it's working. We need to know, can I actually get data out of it? So if you fall into any of those roles, developer, DevOps, system administrator. Or just somebody who wants to learn more about the way the databases work. Learning SQL is a great way to find out how to talk to talk to it. And learning Transact-SQL, is a great way to learn how to talk to Microsoft's SQL Servers. >> Wow, that sounds fantastic, is there a certification available for the show? >> Absolutely this is actually the first step in becoming like a Microsoft Certified SQL Engineer. So the first certification that we start with is called the MCSA, and I've got Microsoft's page pulled up here. The MCSA for SQL Server 2016, if you look at it. The very first course in that journey is right here, course number one. It's exam 70-761, and that's what our show is being based on. So, in this show we're going to cover each of the topics that are a part of that exam. So that we can learn what we need to know to go and pass it. Now, by itself, that's not enough, but if you complete the next course or next exam, the 70-762 exam. When you pas both of those, you get your MCSA in SQL Server 2016. And from there you can even move on and there's an MCSE that you can get. And there's several specializations for big data and cloud databases, and all of that. But they all start right here with this one, with this show, 70-761, which is for SQL Server 2016. Be aware that there are other ones for the previous versions, like SQL Server 2012 and 2014. They shared a different exam, 70-741, if that's the one you're looking for. Well, we have a whole separate show film for that because there's a different user interface. Things look different, so we filmed it specifically for that. Here though, things look different enough, that we actually got the dedicated course specifically for it. And that's exactly what we're gonna be learning. >> A good IT pro is always learning, and what topics are covered in this show? Alright, well, it really all starts kinda, from a beginner's perspective. So what is Transact-SQL, what does it look like, how do I type in the commands? And then, once I know how to input commands, what are the commands to even use? And if we drill into that exam, let me pull it up here. So I'm going to pull up that 70-761 exam and I'll pull up the details here from Microsoft. They basically divide things into three areas, now in the show we divided it up into a lot more areas. Because this is, it's a lot to take in just in three chunks, right? So I think we actually divide it into, I can't remember, like 10 or 11 different areas. But when you look inside, first off we start with Managing data with Transact-SQL. Notice that that's almost half the exam. A large part of the exam is based, just on managing that data. Actually interacting with the database server, what Transact SQL is all about, right? And if you break that apart you'll see it's things like using SELECT queries. Which are the most common types of queries, that's how we request information from the database. How we combine data between tables using joins, how we aggregate data using functions. That's all interacting with the database. Then we get into doing advanced queries, these are queries that not everybody uses. The ones up here in the first section, everybody uses these. You use these everyday, just going to a website, coming to our website and clicking on the course library. You're running these queries in the background and you don't even know it, right? These happen everyday, the advance queries aren't in every day things. These are more like an edge case or a corner case, where you can actually get some great benefits by using these. Doing things like grouping sets, and pivots, and unpivots. So you can change the data that you're getting back from the database servers. So we'll get a chance to see that, that makes up a smaller portion of the exam. And then the smallest portion is right down here at the end, programming databases by using Transact SQL. If you're given access to a SQL Server, it has nothing on it. It doesn't have a database, has no tables, how do you get that set up? So we focus on the infrastructure side, how do I create the database? How do I create the tables, how do I deal with data types. And actually work with that information, inside of those tables? Until we run through that process of being able to maintain the integrity of our data on the server. It's a great way to see some of the neat tools that are a part of Microsoft SQL Server. That set it apart from other competitors that are out there. So that's kind of a rundown of it, now do be aware that this last section here where programing databases. It's not as super-crazy in depth, that if you're not a developer, don't worry, right? It's something that everybody can tackle. There's actually another course, if we look down the little list here. You'll see this, Provisioning SQL Databases. And that goes far more in-depth, into all the different case scenarios for deploying a database. Like doing it in the cloud or locally or with replication, and all these other crazy solutions. And there is another course that's out there, this one. Developing SQL Data Models, and Implementing a SQL Data Warehouse. Those two as well go far more in-depth, in how to get things set up. So here in 70-761 we're really focusing on the bare minimum. What's the minimum it takes to get a database up and running? And to get a table in place and to be able to share some data with it? But those are the topics that we're gonna be covering as we move the course. So by the time we're done, you'll be able to complete each of these tasks. >> Fantastic, well, if this sounds like something you'd be interested in, and it should be. Then Querying Data with Transact-SQL is for you. So make sure you catch every single episode, and thanks for watching ITProTV. [SOUND]