Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
You've seen keywords and operators, now it's time to see a new concept in the SQL programming language: functions.
Syntax definitions
Keywords: Commands issued to a database. The data presented in queries is unaltered.
Operators: Performs comparisons and simple manipulation
Functions: Presents data differently through more complex manipulation
A function looks like:
<function name>(<value or column>)
Example:
UPPER("Andrew Chalkley")
Cheat Sheets
[MUSIC]
0:00
Throughout this course, we're going to use
some things that look like SQL keywords
0:05
but are different in a key way.
0:08
They're called functions.
0:10
Functions are different from normal
keywords because they can manipulate
0:13
the results of a query in different ways.
0:17
Also, they aren't restricted to being
used in just one part of a query.
0:20
But before I fully explain functions,
0:25
let's review some of the syntax
definition you already know.
0:28
A key word is a command you issue
to a database to do something.
0:32
The data in the results isn't altered,
generally you'll see them in all caps.
0:36
Then we have operators.
0:42
You've seen operators that perform
simple actions for comparisons.
0:44
They can also be used for
0:48
simple manipulation tasks,
I'll introduce you to some later.
0:49
Then there's functions.
0:54
This may be a new concept to you.
0:56
They are like keywords, as they have names
and they do tell databases to do things.
0:59
When you've used the select statement
up until now, the data in the database
1:04
is simply returned in the same format
as it appears in the database.
1:08
Functions, on the other hand,
transform the results.
1:13
Functions are used for more complex
operations, such as making a string
1:16
all uppercase, or getting the sum of
all numbers in a particular column.
1:20
The underlying data in
the table isn't changed.
1:25
Just the presentation is different.
1:28
We'll look at lots of
functions in this course.
1:31
Let's take a look at what
the function looks like.
1:34
The function name,
generally in upper case,
1:37
a pair of parenthesis and then the data or
the column of data you want transforming.
1:41
What's presented to you when the SQL
query is executed is the transformed or
1:47
modified data.
1:52
Let's quickly look at a real life example
1:54
of using a function to
uppercase a string of text.
1:56
Many official documents
like your passport or
2:00
government issued ID cards
has your name in uppercase.
2:03
Your name is in a database somewhere and
it may not be in uppercase.
2:07
So let say you want to uppercase
the string of my name, Andrew Chalkley.
2:11
We'd put that in parenthesis and
then use the function named upper.
2:17
This creates a new string of
Andrew Chalkley all in uppercase.
2:23
Let's see this in context
of the SQL query.
2:28
Here's a regular query
without any functions,
2:31
here's the results of that query.
2:34
Once we use a function,
the results change.
2:37
We look at this function again
with some more examples later.
2:39
There are differences in functions and
2:46
operators between different
relational database systems.
2:48
SQL playground runs a version
of SQLite under the hood.
2:51
What works in SQLite might not work the
same in MySQL, PostgreSQL or SQL server.
2:54
Many of the functions and
operators we're using are universal.
3:00
However, check the teacher's notes with
every video for any differences and
3:04
gotchas in other database implementations.
3:08
You need to sign up for Treehouse in order to download course files.
Sign up