- [Instructor] The select statement returns results
or a set of results from a query.
Let's start by selecting the World database.
We'll be using that a little later in this lesson
and I'm gonna type a very simple select statement.
Select statements are at the very core of SQL,
so it's essential that you understand
how it's put together.
This is how you retrieve data from an SQL database
using the select statement.
So, I'm gonna type select,
I tend to write keywords in all caps,
you don't have to.
I think it's a good idea
and so, I always do that.
And I'll put in single quotes the words hello, world
and a semicolon to terminate the statement
and so, when I press Go, you see we get one row returned,
and that row has the string hello, world in it
and the identifier for the row here
is hello, world in quotes as we have here
because the source of the data here
is the literal string.
So, let's look at the parts of the statement.
First the select keyword
identifies this as a select statement.
Select, like I said, it doesn't need to be capitalized,.
I consider it generally good practice
to capitalize SQL keywords.
A select statement retrieves data from a data source
and in this case, the data source
is this literal string, hello, world.
This is the very simplest possible data source in SQL
and then there's a semicolon.
The semicolon is actually required in standard SQL
but in practice it's usually optional
and in most cases, if I omit this here,
you'll notice that it still works.
I consider it good practice
to include the semicolon as often as possible.
So, you'll notice that our identifier for the column
is hello, world in quotes like that.
We could if we like we could say As Result
and when I run this, you'll see that the result
is now that identifier,
so we've created an identifier.
It's basically an alias for the column
with the as keyword
and you'll see the as keyword used a lot
and you'll see a lot more examples of this
throughout the course.
Now, generally, select is used to return the results
of a query, so if I instead say select asterisk
from and from is a keyword,
so I'll put it in caps, country, semicolon
and press Go, we now get all of the rows
and all of the columns.
That's what the asterisk says.
All of the rows and all of the columns
from the country table in the World database.
So, the country table has 239 rows.
And you see if we scroll down here
that's 239 rows of data.
You also notice that it's in no particular order,
so if I wanna order it,
I can say order by and again, SQL keywords
and I can select a column
and in this case, I'm gonna select the column Name
and so, I'll say order by Name like that
and now we still get the same 239 rows
but they're in alphabetical order, or ASCII order rather
by the name and in cases where the name
has characters that are not in the ASCII dataset,
they're not necessarily gonna be in order.
Most database systems will allow you
to set a collation, it'll allow you to do that
but most of them default to ASCII.
You can also specify which columns to retrieve.
You don't have to select all of the columns like this.
Instead of the asterisk I can say Name, LifeExpectancy
and I'll just get those two columns,
Name and LifeExpectancy ordered by name.
Now, a couple more notes on the as clause here.
If I wanted this LifeExpectancy to have a space in it,
of course, I can say as
and put it in quotes, Life Expectancy
and I need to put this in quotes
because there's a space there
and I need to use double quotes
because the SQL standard says that single quotes
are just for literal strings.
For everything else you use double quotes.
In this case, it's an identifier,
it's an alias identifier
and so, I'm using double quotes here
and so, when I say Go,
you'll notice that the Life Expectancy
now has a space in it.
Now, in practice, many database systems
will allow you to use single quotes here
and some will allow you to use brackets
and other things but the standard says double quotes
and what we're learning here is the SQL standard,
so single quote for literal strings,
double quotes for everything else
and we have Life Expectancy like that.
One final note on the as clause.
You'll see this now and then.
I think again it's bad practice.
You can actually eliminate the as keyword
and you'll notice that it still works
and it gives us that alias there.
I consider this a very bad practice.
As is only two characters of typing
and of storage and it seems to me a very small price
to pay for clarity.
Otherwise you could just have, for example here,
if I didn't have this alias
and I just sort of accidentally eliminated that comma,
now I don't know is LIfeExpectancy an alias?
I'm not even sure what this will do
when I press the Go button.
Well, there it is, it gave me the Name column
with LifeExpectancy as an alias
and that's not at all what I meant.
So, always be clear
and use the as clause with the as keyword
'cause clarity is more important
than a couple of characters of typing.
The select statement has many more options and uses
and we will cover it in much more detail
throughout the rest of this course.