Querying

Name: Querying
Author: Daniel Hanks

Sorting, filtering, grouping and joining data using queries

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

In this lesson, we're gonna talk about queries. Before we do that, I just wanted to show you this AdventureWorks database that's out on GitHub and the links are included here. We have a AdventureWorks Read Me. If we scroll down, I would like to do an install from a backup here. So if you click on this, it'll bring sample.databases. And we're gonna use the 2019.bak. Just click on that and save it to your downloads and either copy it somewhere you know that you can access it easy enough or just you can access it from the Downloads folder. But what we're gonna do is grab this database 'cause it's gonna have data filled out for us already. So we don't have to go and fill out our tables. So this will just save us a lot of time and gives you another database to play with and learn from, basically. Let's go take a look at this database over in our software. And once you have that downloaded, we can just do a right click on the database and do a Restore Database. And we're gonna have a database dropdown box. We want the device and you're gonna click on the ellipses. I'm just gonna add. Now this is where I, why it's important you remember where you put your file. I put mine right on my local C drive so I know exactly where it is in the route. So that's my AdventureWorks 2019.bak file. So I'm gonna click Okay. Click Okay there. And there's the full database backup that I can restore. Click Okay again and it restored successfully. So now under Databases, you should have AdventureWorks 2019, can expand that out. And we got a whole bunch of tables. Let's give you something to explore and play with. Just grab the top thousand rows of person.address, just as an example. And we got all sorts of data. Coincidentally, we are getting into the select statements first and we just did one, which we went over that in the earlier lessons. So that just grabbed the top 1000. So we can just stick with that person.address. Like we did before, you can just modify it right through here or you can come up here and click on New Query. You wanna expand this out. So this is the most basic select statement you can do. Select * from person.address. So it's just says, give me everything from this table, which you have to be very careful with that. 'Cause if it's a very large table, it could take a while. Let's see what we got here. So we execute, that went pretty smooth. About 19,000 records here. And we talked about the where clause just a little bit before. So if we wanted to add that to this query, we could just do a where and let's say we wanted to get all the cities. We want all the records where the cities were in Dallas, where cities equal Dallas. So in your right now queries, you wanna do the where. Just take that person.address. When you have a lot of tables like this, you have to specify what table you're grabbing it from and it does auto complete for you, which is nice. Person.address.city. So the reason for this again, is another table could have city in it. So you can't just put where city equals 'cause it doesn't know which table to look for. So you're gonna do person.address.city. And then important thing to remember for a SQL server is you need single quotes. Don't use regular quotation marks. You want the singles and we execute that and now we have all the records that were just for the city of Dallas. So this is really just scratching the surface on select statements. So if we go over, let me drag this over here, our trusty Documentation. It talks about select statements for SQL server and just specifically, for AdventureWorks. Oh, these are 2012. They should work in 2019 as well. Or you could go out and get the 2012 one and do the same thing we just did. And you can literally just copy and paste these and run the queries and get a feel for 'em. But these are gonna go over all the different select statement commands with examples. So this is all very helpful. So I'm gonna stop there for this lesson now that we've done some basic select statements and you know where to look for more intricate select statement commands and next, we're gonna get into grouping and joining.

Beginner Provider : Daniel Hanks 5 Lessons · 16m in Arabic, German, English, Spanish, French, Portuguese, Urdu, Chinese

Course Description

This course will introduce you to queries. You’ll learn about sorting and filtering data using queries. You’ll also learn about grouping and joining data. You’ll also select, insert, update, and delete data by building queries.

The lessons will show you how to import and use a sample database commonly used in database courses. With the data found in these tables, you’ll start building queries that will allow you select, sort, and filter data. You’ll then move on to building queries that will allow you to group and join data. Finally, you’ll build queries that allow you to insert, update and delete data.

What You'll Learn

Build queries to select, sort, and filter data
Group and join data using queries
Insert, update, and delete data by building queries
Import and use a sample database commonly used in database courses
Apply query languages to retrieve and manage data in tables

Key Takeaways

Queries can be used to sort and filter data from database tables.
Grouping and joining queries let you combine and organize data across tables.
Queries can select, insert, update, and delete data in a database.
The course uses a sample database commonly used in database courses to practice building queries.

Frequently Asked Questions

What will I learn in this course?

You will learn about queries, including how to sort and filter data, how grouping and joins work, and how to build queries that select, insert, update, and delete data.

Does this course include hands-on practice with a database?

Yes. The lessons show you how to import and use a sample database commonly used in database courses, and you use the data in its tables to build queries.

What lessons does this course cover?

The lessons cover Introduction to Queries, Sorting and Filtering, Grouping and Joins, Inserting Data, Updating Data, and Deleting Data.

What skills does this course help build?

It helps build skills in Data Mining Query Language (DMQL), database programming, database queries, query languages, query planning, and query understanding.