Working with Functions
Database Fundamentals
© 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
What you will learn
At the core of the lesson
You will learn how to do the following: Key terms:
• Identify built-in functions. • Aggregate functions
• Examine the DATE functions that can be • Conversion functions
used in calculations. • Date functions
• Calculate data by using aggregate • String functions
functions.
• Mathematical functions
• Manipulate string values. • Control flow functions
• DISTINCT
• COUNT
• Character strings
2 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Functions
© 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Built-in functions
Some common functions include aggregate functions, conversion functions, date functions, string functions,
mathematical functions, and control flow and window functions.
Aggregate functions Conversion functions Date functions String functions
Control flow and
Mathematical functions window functions
4 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Built-in functions: Example syntax
The CURRENT_DATE() function returns the current date as a value in ‘YYYY-MM-DD’ format.
SELECT CURRENT_DATE(); -> 'YYYY-MM-DD'
Function Returns current date
5 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Built-in functions: Another example syntax
The DATE_ADD() function adds a time or date interval to a date and returns a value.
Query
DATE_ADD (date, INTERVAL value addunit);
Output
SELECT DATE_ADD (‘YYYY-MM-DD’, INTERVAL 3 DAY);
Function Date Value Add unit
6 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Aggregate functions
© 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Common aggregate functions
Aggregate Function Use Case and Example
• Returns the average of a set
AVG
• Can be used to find the average population for cities within a specified country
• Returns the number of items in a set
COUNT
• Can be used to find the total number of cities listed within a specified country
• Returns the maximum value in a set
MAX
• Can be used to find the city with the greatest number or the highest population
• Returns the minimum value in a set
MIN
• Can be used to find the city with the smallest number or the lowest population
• Returns the total of all values in a set
SUM • Can be used to find the total population for all of the cities that are listed for a specified
country
8 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Aggregate functions: Example syntax
Query Aggregate function name
SELECT COUNT(*) AS ‘Total Number of Rows’ FROM countrylanguage;
AS clause creates an alias Table
Output
Total Number of Rows
--------------------
984
Aggregate function return value
9 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Aggregate functions: Example syntax (continued)
Query
SELECT AVG(LifeExpectancy) Aggregate function
FROM country;
Aggregate function name
Output
AVG(LifeExpectancy)
------------------- Aggregate function return value
72.51200
10 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Activity
© 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
In this activity: Time: 10 mins
Activity: Using the SUM • AnyCompany Publishing House Country
function is examining their country
table. Column Headings Type
• Discuss how to query the
country table by using the SUM Code Character
function. Feel free to annotate Name Character
your findings to begin your
discussion. Continent Character
Region Character
To do: SurfaceArea Float
• Review the column headings, IndepYear Integer
and annotate which columns
you could use with the SUM Population Integer
function.
LifeExpectancy Float
• Create a query that sums the
total population for all of the GNP Float
countries.
GNPOId Float
• After creating your list and
query, be ready to explain your LocalName Character
findings to the class.
Capital Character
Code2 Character
Hint: Review slides 7–9 for information about
12 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved. aggregate functions.
DISTINCT keyword
© 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
DISTINCT (different) keyword
Query
DISTINCT keyword Column names
SELECT DISTINCT CountryCode, District
FROM city;
Output
CountryCode District
----------- ----------------
BRA Distrito Federal
BRA Rio de Janeiro
BRA Sao Paulo Distinct (unique) value combinations
CHN Guangdong for the selected columns
CHN Shanghai
GBR England
GBR Scotland
14 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
DISTINCT in a COUNT function
Query DISTINCT keyword
SELECT COUNT(DISTINCT CountryCode) AS Unique_Country_Codes
FROM city;
Output
Unique_Country_Codes The query returns the number of unique
-------------------- country code values in the table.
118
15 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Character strings and string functions
© 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
String function: CHAR_LENGTH()
Query
A string literal is enclosed in single quotation
SELECT CHAR_LENGTH('District');
marks (' ') or double quotation marks (" ").
String parameter
Returns the length of the string
measured in characters
Output
CHAR_LENGTH(‘District’)
-----------------------
8
17 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
String function: INSERT()
Query
String to be modified. String to be inserted
SELECT INSERT ("Population", 1, 2, “Mani”);
String function Number of characters to replace
Output Position where to insert the second string
INSERT ("Population", 1, 2, “Mani”)
-----------------------------------
Manipulation
18 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Leading and trailing spaces in a string
Extra spaces in a string can cause issues when querying for specific data.
T E X T
Leading space Character string data Trailing space
19 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
TRIM functions: RTRIM() and LTRIM()
• The RTRIM() function removes blank spaces to the right of a string.
• The LTRIM() function removes blank spaces to the left of a string.
Query
RTRIM removes blank spaces on the right end of the string data values.
SELECT ID, RTRIM(District) AS District
FROM city;
Output
ID District
----- -------- No spaces to the right of the string values
1024 Maharashtra for the District column
2331 Seoul
3793 New York
20 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Checkpoint questions
Which functions remove leading and trailing spaces on strings?
What is an aggregate function?
What are five common aggregate functions?
21 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Key takeaways
• SQL has built-in functions. Common functions include the
following:
– Aggregate functions
– String functions
– Date functions
22 © 2022, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Thank you
© 2022 Amazon Web Services, Inc. or its affiliates. All rights reserved. This work may not be reproduced or redistributed, in whole or in part, without prior written permission from Amazon Web
Services, Inc. Commercial copying, lending, or selling is prohibited. Corrections, feedback, or other questions? Contact us at https://support.aws.amazon.com/#/contacts/aws-training. All
trademarks are the property of their owners.
