Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

What Are Functions?

3:12 with Andrew Chalkley

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

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    [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's say you want to uppercase the string of my name, Andrew Chalkley. 2:11
    We'd put that in parentheses 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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up