Preview
Video Player
00:00
00:00
Categorizing Your Output with 'AS'
4:33You're not restricted to displaying the column names from the table at the top of your results. You can specify your own names for result sets.
Let's select everything
from our books table again.
0:00
We have an ID, which is a unique
identifier for the database entry.
0:03
This is a common practice and guarantees
that every row is not a duplicate of
0:07
another, and can be isolated
from all other rows if needed.
0:12
We have the title, author,
genre, and first_published.
0:16
Remember, querying a table's
contents allows us to present
0:22
the results of a database
in many different ways.
0:26
The results are designed for
humans to read.
0:29
But sometimes the column
names aren't that friendly.
0:32
Let's say I was running a query for
the library administrator.
0:35
They want a report of all the books in
the database along with the year that they
0:38
were first published.
0:42
The column headers are all in lower case.
0:47
This may be distracting
to the report reader.
0:50
We can change the title of any column,
by using the keyword AS.
0:53
After the AS, you can write what you
want to alias the column name to.
0:58
Let's use Title with an uppercase T.
1:02
When we run the query now, we get
the results back with a capital T
1:06
in the title column.
1:10
How about the first_published column?
1:12
We can use the AS keyword again but
with the words First Published.
1:15
If you try and execute this query,
you'll get an error.
1:23
Error: near "Published": syntax error.
1:26
The key for
us here is that it's a syntax error.
1:29
Remember the syntax is the grammar or
1:33
the rules surrounding the way
we write programming code.
1:35
The computer doesn't
understand the word Published.
1:39
It's expecting a FROM or a comma,
followed by another column name.
1:42
We can't use multiple words like
this when writing SQL queries.
1:47
So what do we do for something such as
our first_published alias, in our reports?
1:52
There is a way we can
use some punctuation,
1:57
which is part of the SQL syntax,
to solve this problem.
2:00
We can define column names with spaces
by wrapping them in quotes like this.
2:03
The opening quote is just
before the first letter, and
2:10
the closing quote after the last letter.
2:13
But here's another got ya.
2:17
Writing SQL in different
databases like Microsoft SQL, or
2:19
PostgreSQL my require you to use different
punctuation for writing aliases.
2:22
Some require single quotes,
others even require square brackets.
2:28
The SQL we're using in SQL Playground can
use double quotes, but just be mindful.
2:32
SQL in different settings may have
these slight variations in syntax.
2:38
Most database documentations have help
to guide you around these difficulties.
2:43
When we execute this query now,
we see Title and
2:47
First Published with a space
as the column titles.
2:51
There is a short hand for this syntax,
where you remove the AS keyword.
2:55
You can try it too.
3:02
See?
3:05
It's the same results.
3:06
I avoid this syntax because
it's not very clear.
3:08
I just wanted to bring you up so
3:11
you weren't confused if
you saw it somewhere else.
3:12
To recap, to alias a column name
to something of your choosing,
3:15
type SELECT <column name> the keyword AS,
followed by the new name.
3:19
So, if I were to bring back all patron
names in a more user-friendly format,
3:27
I could do something like this.
3:32
With quotes around the first name and
last name.
3:34
These characters surrounded by
quote marks are called strings,
3:42
meaning strings of characters,
3:46
letters, numbers,
special characters and spaces.
3:48
One final note about the AS keyword.
3:52
When you bring back data with
a new label using the AS keyword
3:55
it does not change the name of
the column in the actual database.
3:59
For example, when we aliased our
first_published column
4:03
as First Published in our report.
4:07
The column name is always first_published.
4:10
Only in our report do we see the column
represented as First Published.
4:14
This is why we call it an alias.
4:18
As an aside, the AS keyword can
be used to alias table names for
4:22
more complex queries working
with multiple tables.
4:27
We'll be covering that in another course.
4:30