SQL functions and subqueries: A functional friendship

In this reading, you will learn about SQL functions and how they are sometimes used with subqueries. SQL functions are tools built into SQL to make it possible to perform calculations. A subquery (also called an inner or nested query) is a query within another query.

How do SQL functions, function?

SQL functions are what help make data aggregation possible. (As a reminder, data aggregation is the process of gathering data from multiple sources in order to combine it into a single, summarized collection.) So, how do SQL functions work? Going back to W3Schools, let’s review some of these functions to get a better understanding of how to run these queries:

• SQL HAVING : This is an overview of the HAVING clause, including what it is and a tutorial on how and when it works.
• SQL CASE : Explore the usage of the CASE statement and examples of how it works.
• SQL IF : This is a tutorial of the IF function and offers examples that you can practice with.
• SQL COUNT : The COUNT function is just as important as all the rest, and this tutorial offers multiple examples to review.

Subqueries - the cherry on top

Think of a query as a cake. A cake can have multiple layers contained within it and even layers within those layers. Each of these layers are our subqueries, and when you put all of the layers together, you get a cake (query). Usually, you will find subqueries nested in the SELECT, FROM, and/or WHERE clauses. There is no general syntax for subqueries, but the syntax for a basic subquery is as follows:

You will find that, within the first SELECT clause is another SELECT clause. The second SELECT clause marks the start of the subquery in this statement. There are many different ways in which you can make use of subqueries, and resources referenced will provide additional guidance as you learn. But first, let’s recap the subquery rules.

There are a few rules that subqueries must follow:

• Subqueries must be enclosed within parentheses
• A subquery can have only one column specified in the SELECT clause. But if you want a subquery to compare multiple columns, those columns must be selected in the main query.
• Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator which allows you to specify multiple values in a WHERE clause.
• A subquery can’t be nested in a SET command. The SET command is used with UPDATE to specify which columns (and values) are to be updated in a table.

Additional resources

The following resources offer more guidance into subqueries and their usage:

• SQL subqueries: This detailed introduction includes the definition of a subquery, its purpose in SQL, when and how to use it, and what the results will be
• Writing subqueries in SQL: Explore the basics of subqueries in this interactive tutorial, including examples and practice problems that you can work through

As you continue to learn more about using SQL, functions, and subqueries, you will realize how much time you can truly save when memorizing these tips and tricks.

Ungraded Plugin: Subqueries

1. A subquery may be nested in a SELECT clause. True or False? It is true statement. Explain: Subqueries are usually nested in the SELECT, FROM, and/or WHERE clauses. Subqueries can’t be nested in SET queries.
2. You can use comparison operators such as >, <, or = within subqueries. True or False? It is true statement. Explain: Comparison operators such as >, <, or = help you compare data in subqueries. You can also use multiple row operators including IN, ANY, or ALL.
3. A subquery is also called an outer query or outer select. The statement containing a subquery is called an inner query or inner select. It is false statement. Explain: The statement containing a subquery is an outer query or outer select. Subqueries are nested within these statements, called inner queries or inner select.
4. The parent query executes before its inner query. True or False? It is false statement. Explain: The innermost query executes first. Its parent query executes last so it can use the results returned by inner queries.
5. Subqueries don’t have to be enclosed within parentheses. True or False? It is false statement. Explain: Parentheses are used to mark the beginning and end of a subquery.
6. A subquery can have more than one column specified in the SELECT clause. True or False? It is false statement. Explain: For a subquery to compare multiple columns, those columns must be selected in the main query.
7. A subquery can’t be nested in a SET command. True or False? It is true statement. Explain: A SET command can’t have a nested subquery because it is used with UPDATE to adjust specific columns and values in a table.
8. Subqueries that return more than one row can only be used with multiple value operators. True or False? It is true statement. Explain: Subqueries that return more than one row rely on multiple value operators such as the IN command.

Test your knowledge on working with subqueries

Question 1

Which of the following queries contain subqueries? Select all that apply.

• 
• 
• 
• 

Explain: The three queries with statements in parentheses contain subqueries.

Question 2

Fill in the blank: A data analyst uses aliasing to make it easier to read and write a query. Aliasing involves temporarily _____ a table or column in a query.

A. hiding

B. naming

C. copying

D. removing

A data analyst uses aliasing to make it easier to read and write a query. Aliasing involves temporarily naming a table or column in a query

Question 3

When working with subqueries, the outer query executes first. True or False?

A. True

B. False

It is false statement. Explain: The inner query executes first, then the results are passed onto the outer query to use.