What You’ll Learn in This Hour:
▶ Introduction to character functions
▶ How and when to use character functions
▶ Examples of ANSI SQL functions
▶ Examples of common implementation-specific functions
▶ Overview of conversion functions
▶ How and when to use conversion functions
In this hour, you learn how to restructure the appearance of output results using some American National Standards Institute (ANSI) standard functions, other functions based on the standard, and several variations used by the major SQL implementations.
Note
The ANSI Standard Is Not Rigid
The ANSI concepts discussed in this book are just that—concepts. Standards provided by ANSI are simply guidelines for how to use SQL in a relational database. Keep in mind, therefore, that the specific functions discussed in this hour are not necessarily the exact functions that you might use in your particular implementation. Yes, the concepts are the same and the functions generally work the same, but function names and actual syntax might differ.
Character functions are functions that transform strings in SQL into different formats than the way they are stored in the table. The first part of this hour discusses the concepts for character functions as covered by ANSI. The second part of this hour shows real-world examples using functions that are specific to various SQL implementations. Some of the most common forms of ANSI character functions deal with operations for concatenation, substrings, and translation.
Concatenation is the process of combining two strings into one. For example, you might want to concatenate an individual’s first and last names into a single string for the complete name. For example, JOHN concatenated with SMITH produces JOHN SMITH.
The concept of substrings is the capability to extract part of a string, or a “sub” of the string. For example, the following values are substrings of Bald Eagle:
▶ B
▶ Bald
▶ Ba
▶ E
▶ Eagle
▶ d E
The following example illustrates how a literal string might appear in a query. The literal string Somewhere in the world is selected here from the MIGRATION_LOCATIONS table. The literal string itself is irrelevant to the data in this case; it could have been anything. This is merely an example to show that if you select a literal string from a table, the literal string is shown in the results for every row of data in the table—or at least every row of data returned by the results according to the conditions of the query. This simple example is important to remember as you progress through this hour with SQL functions.
SQL> select 'Somewhere in the world.' "MIGRATION LOCATION" 2 from migration locations; MIGRATION LOCATION ----------------------- Somewhere in the world. Somewhere in the world. Somewhere in the world. Somewhere in the world. Somewhere in the world. Somewhere in the world. 6 rows selected.
You use character functions mainly to compare, join, search, and extract a segment of a string or a value in a column. Several character functions are available to SQL programmers.
The following sections illustrate the application of ANSI concepts in some of the leading implementations of SQL. Although this book uses Oracle for the examples, they apply to most other implementations as well, although you might find slight variations of syntax.
CONCAT FunctionThe CONCAT function, along with most other functions, is represented slightly differently among various implementations. The following examples show the use of concatenation in Oracle and SQL Server.
Say you want to concatenate Bald and Eagle to produce Bald Eagle. In Oracle, your code looks like this:
SELECT 'Bald' | | ' Eagle'
In SQL Server, your code appears as follows:
SELECT 'Bald' + ' Eagle'
In SQL Server or Oracle, your code using CONCAT looks like this:
SELECT CONCAT('Bald' , ' Eagle')
Now for an overview of the syntaxes. The syntax for Oracle follows:
COLUMN_NAME | | [ '' | | ] COLUMN_NAME [ COLUMN_NAME ]
The syntax for SQL Server is this:
COLUMN_NAME + [ '' + ] COLUMN_NAME [ COLUMN_NAME ]
The syntax for the CONCAT function appears as follows:
CONCAT(COLUMN_NAME , [ '' , ] COLUMN_NAME [ COLUMN_NAME ])
Both SQL Server and Oracle employ the CONCAT function. You can use it to get the concatenation of pairs of strings, just like the shortened syntax of + for SQL Server and the double pipe (||) for Oracle. In addition, remember that because this operation is for string values, any numeric values must be converted to strings before concatenation. Some examples of utilizing concatenation in its various formats are shown next.
This SQL Server statement concatenates the values for city and state into one value:
SELECT CITY + STATE FROM BIRD_RESCUES;
This Oracle statement concatenates the values for city and state into one value, placing a comma between the values for city and state:
SELECT CITY | |', '| | STATE FROM BIRD_RESCUES;
Alternatively, for Oracle, if you want to use the CONCAT statement to achieve the preceding result, you cannot do so because you are concatenating more than two values.
Note
Use of Quotation Marks for Special Characters
Notice the use of single quotation marks and a comma in the preceding SQL statement. Most characters and symbols are allowed if they are enclosed by single quotation marks. Some implementations use double quotation marks for literal string values.
This SQL Server statement concatenates the values for city and state into one value, placing a space between the two original values:
SELECT CITY + '' + STATE FROM BIRD_RESCUES;
The following example consists of two queries. The first query selects the literal string Great Blue Heron from the NICKNAMES table, where the BIRD_ID is equal to 1. The BIRD_ID of 1 is associated with the Great Blue Heron, which you know at this point. This is simply an example to reiterate the previous example showing the selection of a literal string from a table as opposed to an actual data value in a table. It just so happens in this case that the BIRD_ID with the value of 1 is associated with the Great Blue Heron.
SQL> select 'Great Blue Heron' 2 from nicknames 3 where bird_id = 1; 'GREATBLUEHERON' ---------------- Great Blue Heron Great Blue Heron 2 rows selected.
In this query, the concept of concatenation is used to essentially create a sentence from the database that states, “We combine the literal string 'Another name for' with the value for BIRD_NAME, which is collectively concatenated with ' is: ' and the value for NICKNAME associated with each specific value for BIRD_NAME.” The result set here shows nicknames only for the Great Blue Heron because the WHERE clause has a condition that limits the data returned to the Great Blue Heron on line 4 of the query.
SQL> select 'Another name for ' || birds.bird_name || ' is: ' || nicknames.nickname "NICKNAMES" 2 from birds, nicknames 3 where birds.bird_id = nicknames.bird_id 4 and birds.bird_name = 'Great Blue Heron'; NICKNAMES ------------------------------------------------------- Another name for Great Blue Heron is: Big Cranky Another name for Great Blue Heron is: Blue Crane 2 rows selected.
UPPER FunctionMost implementations have a way to control the case of data by using functions. The UPPER function converts lowercase letters to uppercase letters for a specific string.
The syntax follows:
UPPER(character string)
The following query simply converts the output of the migration location to all upper case. Remember that we have chosen to store data in mixed case in this database, which is simply a matter of preference.
SQL> select upper(migration_location) 2 from migration; UPPER(MIGRATION_LOCATION) ------------------------------ CENTRAL AMERICA MEXICO NO SIGNIFICANT MIGRATION PARTIAL, OPEN WATER SOUTH AMERICA SOUTHERN UNITED STATES 6 rows selected.
Microsoft SQL Server, MySQL, and Oracle all support this syntax. In addition, MySQL supports an alternative to the UPPER function called UCASE. Because both functions accomplish the same task, you are better off following the ANSI syntax.
LOWER FunctionThe converse of the UPPER function, the LOWER function, converts uppercase letters to lowercase letters for a specific string.
The syntax follows:
LOWER(character string)
The following query is the same as the previous query, except that you are converting the migration location names to all lower case from mixed case, as they are stored in the database.
SQL> select lower(migration_location) 2 from migration; LOWER(MIGRATION_LOCATION) ------------------------------ central america mexico no significant migration partial, open water south america southern united states 6 rows selected.
The LOWER function is supported in Microsoft SQL Server, Oracle, and MySQL. As with the UPPER function, MySQL supports an alternative, LCASE. However, as discussed with the UPPER function, following the ANSI standard is generally preferred.
DECODE FunctionDECODE is more specific to Oracle. Even though most implementations do a good job of using the SQL standard as a guide, some implementations have functions that provide additional functionality. The DECODE function supplies an IF/THEM option in a query. The DECODE function is performed on values in a column and searches for a value within the list of values that you provide in the query. If value1 is found, then new_value1 is shown in the query results. If value2 is found in the column, then new_value2 is shown the results, and so forth through any number of values that you choose. Finally, if none of the values listed is found, the default value that you specified is the output for the column. Study the syntax here:
DECODE (column, value1, new1, value2, new2, default_value)
The following query selects the DISTINCT value of data in the NEST_BUILDER column of the BIRDS table. The NEST_BUILDER column has the following potential values: M for a male bird, F for a female bird, B if both parents build the nest, and N for neither (which means that the birds do not build a nest—they take over another nest).
SQL> select distinct(nest_builder) from birds; N - B F N 3 rows selected.
In the following query, the DECODE function is applied to the NEST_BUILDER column. The DECODE function tells the database engine to return the literal string Neither if a value of N is found in the column, return the literal string Female if F is found, and return the literal string Male if M is found. No default is included in this case, nor is a value for B. This is because, in the WHERE clause, you are looking for only nest builders that do not have a value of B. As you can see in the results, you have successfully changed the way the data appears using the DECODE function.
SQL> SQL> select distinct(decode(nest_builder, 'N', 'Neither', 'F', 'Female', 'M', 'Male')) "NESTER" 2 from birds 3 where nest_builder != 'B'; NESTER ------- Neither Female 2 rows selected.
SUBSTR FunctionTaking an expression’s substring is common in most implementations of SQL, but the function name might differ, as shown in the following Oracle and SQL Server examples.
The syntax for Oracle follows:
SUBSTR(COLUMN NAME, STARTING POSITION, LENGTH)
The syntax for SQL Server is shown here:
SUBSTRING(COLUMN NAME, STARTING POSITION, LENGTH)
The following query uses the SUBSTR function to display only the first three characters of a location’s name from our database where birds might be photographed.
SQL> select location_name, substr(location_name,1, 3) 2 from locations; LOCATION_NAME SUBSTR(LOCAT ------------------------------ ------------ Eagle Creek Eag Fort Lauderdale Beach For Heron Lake Her Loon Creek Loo Sarasota Bridge Sar White River Whi 6 rows selected.
TRANSLATE FunctionThe TRANSLATE function searches a string of characters and checks for a specific character, makes note of the position found, searches the replacement string at the same position, and then replaces that character with the new value. The syntax follows:
TRANSLATE(CHARACTER SET, VALUE1, VALUE2)
The next SQL statement substitutes every occurrence of I in the string with A, every occurrence of N with B, and all occurrences of D with C:
SELECT TRANSLATE (CITY,'IND','ABC' FROM EMPLOYEES) CITY_TRANSLATION
The following example illustrates the use of TRANSLATE with data in the BIG_BIRDS table:
SQL> select bird_name, 2 translate(bird_name, 'BGH', 'ZZZ') "TRANSLATED NAME" 3 from big_birds; BIRD_NAME TRANSLATED NAME ------------------------------ ---------------------------- Great Blue Heron Zreat Zlue Zeron Common Loon Common Loon Bald Eagle Zald Eagle Golden Eagle Zolden Eagle Osprey Osprey Canadian Goose Canadian Zoose Ring-billed Gull Ring-billed Zull Double-crested Cormorant Double-crested Cormorant Turkey Vulture Turkey Vulture Mute Swan Mute Swan Brown Pelican Zrown Pelican Great Egret Zreat Egret 12 rows selected.
Notice that all occurrences of B were replaced with Z, G with Z, and H with Z.
Both MySQL and Oracle support the use of the TRANSLATE function. Microsoft SQL Server does not currently support the use of TRANSLATE.
REPLACE FunctionThe REPLACE function replaces every occurrence of a character or string with another specified character or string. The use of this function is similar to the TRANSLATE function, except that only one specific character or string is replaced within another string. The syntax follows:
REPLACE('VALUE', 'VALUE', [ NULL ] 'VALUE')
The following statement returns the MIGRATION_LOCATION and a new version of the MIGRATION_LOCATION using the REPLACE function, which basically does a search and replace. In this example, it replaces every occurrence of a space with a dash. Study the results:
SQL> select migration_location, replace(migration_location, ' ', '-') "NEW LOCATION" 2 from migration; MIGRATION_LOCATION NEW LOCATION ------------------------------ ---------------------------- Central America Central-America Mexico Mexico No Significant Migration No-Significant-Migration Partial, Open Water Partial,-Open-Water South America South-America Southern United States Southern-United-States 6 rows selected.
Microsoft SQL Server, MySQL, and Oracle all support the ANSI version of the syntax.
LTRIM FunctionThe LTRIM function provides another way of clipping part of a string. This function and SUBSTRING are in the same family. LTRIM trims characters from the left of a string. The syntax follows:
LTRIM(CHARACTER STRING [ ,'set' ])
The following SQL statement trims all characters to the left that have a value of 'Bald ' in the BIRD_NAME column. Note that a single space is included in the application of the LTRIM function.
SQL> select bird_name, ltrim(bird_name, 'Bald ') "TRIMMED NAME" 2 from birds 3 where bird_name like '%Eagle%'; BIRD_NAME TRIMMED NAME ------------------------------ --------------------- Bald Eagle Eagle Golden Eagle Golden Eagle 2 rows selected.
The LTRIM function is supported in Microsoft SQL Server, MySQL, and Oracle.
RTRIM FunctionSimilar to LTRIM, the RTRIM function trims characters, but this time from the right of a string. The syntax follows
RTRIM(CHARACTER STRING [ ,'set' ])
Remember that the SQL Server version removes only the blank spaces from the right side of the string; therefore, it does not require the [,'set' ] portion of the syntax.
RTRIM(CHARACTER STRING)
This SQL statement trims everything to the right of the literal string ' Eagle'. Thus, the result set shows a second column that indicates a type of eagle.
SQL> select bird_name, rtrim(bird_name, ' Eagle') "TYPE OF EAGLE" 2 from birds 3 where bird_name like '%Eagle%'; BIRD_NAME TYPE OF EAGLE ------------------------------ ---------------------- Bald Eagle Bald Golden Eagle Golden 2 rows selected.
The RTRIM function is supported in Microsoft SQL Server, MySQL, and Oracle.
The following sections show a few other character functions worth mentioning. Again, these are functions that are fairly common among major implementations. Remember that each implementation of SQL has different syntax and different options to communicate with a relational database. When it comes to character functions, a wide variety of options are available among implementations. For any implementation that you choose to use to store your data, you must work within the limits of the functions and other options that are available. Still, you can capitalize on those options to maximize the use of the data that is stored within your database.
LENGTH FunctionThe LENGTH function is a common function that finds the length of a string, number, date, or expression in bytes. The syntax follows:
LENGTH(CHARACTER STRING)
The following query returns the length and characters of a bird’s name.
SQL> select bird_name, length(bird_name) "LENGTH OF NAME" 2 from big_birds; BIRD_NAME LENGTH OF NAME ------------------------------ -------------- Great Blue Heron 16 Common Loon 11 Bald Eagle 10 Golden Eagle 12 Osprey 6 Canadian Goose 14 Ring-billed Gull 16 Double-crested Cormorant 24 Turkey Vulture 14 Mute Swan 9 Brown Pelican 13 Great Egret 11 12 rows selected.
The LENGTH function is supported in both MySQL and Oracle. Microsoft SQL Server uses the shortened version LEN instead, but the functionality is the same.
COALESCE FunctionThe COALESCE function replaces NULL values within the result set.
The following example demonstrates the COALESCE function by replacing every NULL value of WINGSPAN with a 0. This gives you the first non-NULL value of WINGSPAN, or the string value of 0:
SQL> select bird_name, coalesce(wingspan, 0) wingspan 2 from birds; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Mallard 0 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Red Tailed Hawk 48 Osprey 72 Belted Kingfisher 0 Canadian Goose 72 Pied-billed Grebe 0 American Coot 0 Common Sea Gull 0 Ring-billed Gull 50 Double-crested Cormorant 54 Common Merganser 0 Turkey Vulture 72 American Crow 0 Green Heron 0 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 Anhinga 0 Black Skimmer 0 23 rows selected.
The COALESCE function is supported in Microsoft SQL Server, Oracle, and MySQL.
LPAD FunctionLPAD (left pad) adds characters or spaces to the left of a string. The syntax follows:
LPAD(CHARACTER SET)
The following example pads periods to the left of MIGRATION_LOCATION in the second column to fill a total of 25 characters. This is also a way to right-justify a value if you pad it with spaces.
SQL> select migration_location, 2 lpad(migration_location, 25, '.') "LEFT PADDED LOCATION" 3 from migration; MIGRATION_LOCATION LEFT PADDED LOCATION ------------------------------ ---------------------------------------------------- ------------------------------------ Central America ..........Central America Mexico ...................Mexico No Significant Migration .No Significant Migration Partial, Open Water ......Partial, Open Water South America ............South America Southern United States ...Southern United States 6 rows selected.
The LPAD function is supported in both MySQL and Oracle. Unfortunately, no alternative is available for Microsoft SQL Server.
RPAD FunctionThe RPAD (right pad) function adds characters or spaces to the right of a string. The syntax follows:
RPAD(CHARACTER SET)
As with LPAD, the following query pads periods to the right of MIGRATION_LOCATION in the second column to fill a total of 25 characters.
SQL> select migration_location, 2 rpad(migration_location, 25, '.') "RIGHT PADDED LOCATION" 3 from migration; MIGRATION_LOCATION RIGHT PADDED LOCATION ------------------------------ ---------------------------------------------------- -------------------------------------------- Central America Central America.......... Mexico Mexico................... No Significant Migration No Significant Migration. Partial, Open Water Partial, Open Water...... South America South America............ Southern United States Southern United States... 6 rows selected.
The RPAD function is available in both MySQL and Oracle. Unfortunately, no substitute is available for Microsoft SQL Server.
ASCII FunctionThe ASCII function returns the ASCII representation of the leftmost character of a string. The syntax follows:
ASCII(CHARACTER SET)
Following are some examples:
▶ ASCII('A') returns 65.
▶ ASCII('B') returns 66.
▶ ASCII('C') returns 67.
▶ ASCII('a') returns 97.
For more information, refer to the ASCII chart located at www.asciitable.com.
The ASCII function is supported in Microsoft SQL Server, MySQL, and Oracle.
Mathematical functions are standard across implementations. These functions enable you to manipulate numeric values in a database according to mathematical rules.
The most common functions include the following:
▶ Absolute value (ABS)
▶ Rounding (ROUND)
▶ Square root (SQRT)
▶ Sign values (SIGN)
▶ Power (POWER)
▶ Ceiling and floor values (CEIL(ING), FLOOR)
▶ Exponential values (EXP)
▶ SIN, COS, TAN
The general syntax of most mathematical functions follows:
FUNCTION(EXPRESSION)
All the mathematical functions are supported in Microsoft SQL Server, MySQL, and Oracle, as well as most other implementations.
Conversion functions convert a data type into another data type. For example, you might have data that is normally stored in character format, but occasionally you want to convert the character format to numeric so that you can make calculations. Mathematical functions and computations are not allowed on data that is represented in character format.
The following are general types of data conversions:
▶ Numeric to character
▶ Character to numeric
▶ Character to date
▶ Date to character
The first two types of conversions are discussed in this hour. The remaining conversion types are discussed in Hour 16, “Understanding Dates and Times.”
Converting numeric values to character strings is precisely the opposite of converting characters to numbers.
Following is an example of converting the height, which is stored as a numeric value, into a character value. Notice that the height column was selected twice, and the third column is left-justified because the height is now seen as a character value for the purpose of this query. You’re not changing the data type of the column; you’re simply changing the way it is displayed in the output.
SQL> select bird_name, height, to_char(height) "HEIGHT AS A CHARACTER" 2 from big_birds; BIRD_NAME HEIGHT HEIGHT AS A CHARACTER ------------------ ---------- ----------------------- ------ -------------------- Great Blue Heron 52 52 Common Loon 36 36 Bald Eagle 37 37 Golden Eagle 40 40 Osprey 24 24 Canadian Goose 43 43 Ring-billed Gull 19 19 Double-crested Cormorant 33 33 Turkey Vulture 32 32 Mute Swan 60 60 Brown Pelican 54 54 Great Egret 38 38 12 rows selected.
Tip
Different Data Is Output in Different Ways
The data’s justification is the simplest way to identify a column’s data type. Character data is most often left-justified, whereas numeric data is often right-justified. You can quickly determine what kind of data is returned from a given query.
You should notice two differences between numeric data types and character string data types:
▶ You can use arithmetic expressions and functions on numeric values.
▶ Numeric values are right-justified in the output results, whereas character string data types are left-justified.
Note
Converting to Numeric Values
For a character string to be converted to a number, the characters must typically be 0 through 9. The addition symbol (+), minus symbol (-), and period (.) can also be used to represent positive numbers, negative numbers, and decimals. For example, the string STEVE cannot be converted to a number, whereas an individual’s Social Security number can be stored as a character string but can easily be converted to a numeric value using a conversion function.
When a character string is converted to a numeric value, the value takes on the two attributes just mentioned.
Some implementations might not have functions to convert character strings to numbers; others offer such conversion functions. In either case, consult your implementation documentation for the specific syntax and rules for conversions.
Note
Some Systems Do the Conversions for You
Some implementations implicitly convert data types when necessary. In these cases, the system makes the conversion for you when changing between data types and the use of conversion functions is unnecessary. Check your implementation’s documentation to see which types of implicit conversions are supported.
In this example, you use the TO_NUMBER function within the exact same query as before, to convert HEIGHT back to a numeric value. In the result set, this value in the third column is right-justified as a number. This is also an example of embedding functions within other functions.
SQL> select bird_name, height, to_number(to_char(height)) "HEIGHT BACK TO A NUMBER" 2 from big_birds; BIRD_NAME HEIGHT HEIGHT BACK TO A NUMBER ------------------------ ------ ----------------------- Common Loon 36 36 Bald Eagle 37 37 Golden Eagle 40 40 Osprey 24 24 Canadian Goose 43 43 Ring-billed Gull 19 19 Double-crested Cormorant 33 33 Turkey Vulture 32 32 Mute Swan 60 60 Brown Pelican 54 54 Great Egret 38 38 12 rows selected.
The employee identification is right-justified following the conversion.
You can combine most functions in a SQL statement. After all, SQL would be far too limited if function combinations were not allowed. When combining functions in an SQL statement, all functions are resolved separately. When you embed a function within another function, the innermost function is resolved first. Virtually no limit governs what you can do with a SELECT statement to look at data and control how the data appears when it is returned as output.
Note
How Embedded Functions Are Resolved
When embedding functions within functions in a SQL statement, remember that the innermost function is resolved first; and then each function is resolved from the inside out.
This hour introduced you to various functions used in a SQL statement—usually a query—to modify or enhance the way output is represented. Those functions include character, mathematical, and conversion functions. It is important to realize that the ANSI standard is a guideline for how vendors should implement SQL, but it does not dictate the exact syntax or necessarily place limits on vendor innovations. Most vendors have standard functions and conform to the ANSI concepts, but each vendor has its own specific list of available functions. The function name and exact syntax might differ, but the concepts among the functions are the same.
Q. Are all functions in the ANSI standard?
A. No, not all functions are exactly ANSI SQL. As with data types, functions are often implementation dependent. Most implementations contain supersets of the ANSI functions; many have a wide range of functions with extended capability, whereas other implementations seem to be somewhat limited. This hour included several examples of functions from various implementations. However, because so many implementations use similar functions (with perhaps slight differences), check your particular implementation for the available functions and their usage.
Q. Is the data actually changed in the database when using functions?
A. No, data is not changed in the database when using functions. Functions are typically used in queries to manipulate the output’s appearance.
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. Match the descriptions with the possible functions:
Descriptions |
Functions |
a. Selects a portion of a character string |
|
b. Trims characters from either the right or the left of a string |
|
c. Changes all letters to lower case |
|
d. Finds the length of a string |
|
e. Combines strings |
|
2. True or false: Using functions in a SELECT statement to restructure the appearance of data in output also affects the way the data is stored in the database.
3. True or false: The outermost function is always resolved first when functions are embedded within other functions in a query.
1. Write a query to select (display) the word Somewhere for every MIGRATION_LOCATION in the MIGRATION_LOCATIONS table.
2. Write a query to produce results for every bird in the BIRDS table that looks like the following:
The Bald Eagle eats Fish. The Bald Eagle eats Mammals. Etc.
3. Write a query to convert all nicknames to upper case.
4. Use the REPLACE function to replace the occurrence of every MIGRATION_LOCATION that has the word United States in it with US.
5. Write a query using the RPAD function to produce output to display every numeric column in the BIRDS table as a character, or left-justify.
6. Write a query to produce the following results for types of herons in the BIRDS table.
BIRD_NAME TYPE OF HERON ------------------------------ ------------------ Great Blue Heron Great Blue Green Heron Green 2 rows selected.
7. Experiment with the functions in this hour on your own. Trial and error is a good way to learn with queries because you do not affect any data that is actually stored in the database.