Databases are wonderful, and amazing inventions that store our data, organize it effortlessly,
and make sure we don't lose any of it.
There are many types of databases, but common for all is that you need to be able to insert,
and retrieve the data in some way.
Most often, this is done with Structured Query Language, commonly known as SQL.
In this episode, you'll learn how to use SQL to getting data out of a database, filtering
and ordering the data, as well as being specific about what data to retrieve.
Before we start retrieving data we'll need some data, and a tool to retrieve that data.
We're gonna use the AdventureWorks sample database from Microsoft, a SQL server, and
the SQL Server Management Studio, SMSS, which are all linked in the resources.
They're all free.
So go get the now, and I'll wait if you installed them as well.
Go on.
The very first part of a SQL query, SQL query, SQL, same thing, to retrieve some of the data
is the word SELECT.
As you might've guessed, it selects some data from a data source.
Let me show you how that looks in SQL Server Management Studio.
I've opened SQL Server Management Studio, and on the left here, you can see you have
an Object Explorer, and I've opened up the AdventureWorks database, and under the tables
section, you can see all the different tables here.
Now in this demo, we're only going to use the Person.Person table, and we do this by
clicking a new query.
So that'll give us a query window here where we can type in our SQL syntax.
So I'm going to start by typing SELECT, and then a star.
That means everything, that's a general computing term.
FROM, and I'll do that on the next line.
It's a good habit to get into, to write each SQL keyword such as SELECT and FROM on a new
line, and I'll show you, or that will become obvious in a minute.
And this is from the Person.Person table.
And then I can go over here, and click execute, and that will then run that query on the database.
And you can see I get back a whole bunch of records.
So there are, you can see the bottom here 19,972 rows in this table Person.Person.
So for this example, let's go with title, FirstName, and LastName.
Ooh not Lars, LastName, like so.
And then if you click execute again, you can see that it'll then retrieve only those columns
from the data set.
We still have the same number of rows, 19,972, but we have now selected only the columns
that we find relevant for this particular query.
And isn't quite enough to be able to merely select specific columns from a data set.
Potentially that could return a huge number of records.
So we want to be able to only select a certain subset of the data based on conditions that
match what we need.
That is what the WHERE clause is for.
So here's our statement from before, and we want to now be more selective about the data
that we get out.
We don't need 19,972 rows.
So we can add the keyword WHERE, and this means that we are going to have a condition
on the data that we pull out.
So before we chose the columns, so that's the data values, but now we want to be conditional
on what are the rows that we retrieve.
So you're going to say from WHERE, FirstName.
So here's another trick, you can see here as I type there are suggestions, this is the
IntelliSense.
So I could just use my down arrow, and then hit enter on FirstName, and it autocompletes
it for me.
So it's a little bit quicker way of doing it.
Now, this is not a condition.
This is just a column.
So we need to make it a condition where FirstNames equal, and then we put in a name such as Mary,
and then we can then execute the command again.
So there are 42 records in this table that has the data value of FirstName equal Mary.
Great.
We can now select data conditionally, which means we can choose the data more accurately.
However, what if we want two conditions to be true, and not just one as we saw before?
What about three or four or five?
Let's learn how AND and OR conditionals work.
Okay.
So you wanna add more conditions, not just the one WHERE, we can use AND or OR.
So in this case I'm going to put in AND, let's say LastName, and then there's a new keyword
here such as like, so this is when we wanna not have a full match, but just a partial
match.
And we can say like H percentage.
So in this case we're saying we want all the first names with Mary and last names that
starts with H. So like is a condition for a partial match.
And the percentage sign means anything after H doesn't matter.
So we just want the H. And then we then execute that query, we now have four records.
So we've now narrowed down the 19,972 rows to just four with just a few lines of SQL.
Now, if you wanna use the OR, I could then change this into you can put things in bracket,
and group them that way.
So this would be the same, but I can then add into here, say OR, title equal Ms. So
what I'm saying here is I want all the records, and I want the columns Title, FirstName, LastName,
from the Person.Person table, where the first name is Mary, and either the last name starts
with H or the title is Ms. So all we want all the Mary H's, and all the Ms. Mary's.
So I'm gonna then execute that query again, click on execute.
You can now see we get 13 records.
So we have a bunch of Mary's with Ms. And a bunch of Mary's with H at the end.
So that's a way that you combine conditionals with WHERE, AND, and OR.
This is starting to get pretty powerful, don't you think?
The last step we will do in this tutorial is ordering the data.
If you want to see highest to lowest, alphabetical order, chronological order, or any other order,
you use the ORDER BY clause.
So if you have a lot of data that you want to retrieve, or in this case 13 rows, but
we wanna have it in an order, we can use the ORDER BY clause, and you could use the number
one that would be column number one or more correctly, I would use the column name.
So in this case, I'm gonna put LastName.
So we order it by LastName, and then I click execute and you can see that now they are
sorted by LastName in alphabetical order.
What you can also add then is you could add an ASC for ascending.
That's what we're doing now, they go from A to higher, so A to V. Or we can put descending,
DESC and click execute.
You will then retrieve the records with alphabetical order, but highest to lowest.
So V down to A. So that's ORDER BY.
SQL queries is a very approachable way to select and sort data from any data set.
This episode showed you how to use SELECT WHERE, AND, OR, and ORDER BY, as well as how
to combine them all to retrieve just the data you need.
Of course, there is a lot more to SQL queries, but you can find the information on the A
Cloud Guru platform.
If you enjoyed this video, consider giving it a thumbs up and subscribing to the channel for everything cloud computing.
All there's left to say is, keep being awesome Cloud Gurus.