In a SELECT statement, a subquery is part of a condition in the WHERE clause. The following query selects the title of each course whose additional fees are less than or equal to the average additional fees for all courses:

select Title
from Course
where
Additional_Fees <=
(select avg(Additional_Fees) from Course)

You should be aware of several things when you use subqueries:

• The subquery must be enclosed in parentheses.
• The number of rows returned by the subquery must match the number of values that the function or operator expects. In the preceding example the <= operator expects a single value to compare against, and the AVG function (which is a group function) returns a single value.
• The number of columns returned by the subquery must match the number of columns that the function or operator expects.
• The ORDER BY clause is not used within a subquery.

Creating a New Table with the SELECT Statement

Let's look at a form of the CREATE TABLE statement that uses a query to specify the structure of the table to be created. You may find this statement to be handy during application development and testing.

If you wanted to experiment with the contents of a table—add, delete, and update various rows—you would be well advised to create a copy of the table that you want to experiment with. Suppose that the existing table is a list of customers for the past 10 years and contains many records—say 100,000—but you want only a subset of those rows for your experiment. Specifically, you are interested only in those customers who live in California. You can create a table containing a subset of all customers by combining the CREATE TABLE statement with the SELECT statement, as shown in Listing 5.29.

Listing 5.29. Creating a table from a subset of rows in another table.

SQL> create table Customer_Subset
  2  as
  3  select *
  4  from Customer
  5  where
  6  State = `CA';

Table created.

Now, look closely at the syntax:

CREATE TABLE new_table_name
AS
select_stmt

The variables are defined as follows:

new_table_name is the name of the table to be created.
select_stmt is a valid SELECT statement.

In addition, you can use the CREATE TABLE statement and the SELECT statement together for another purpose. If you want to create another table that has the same structure as an existing table—with all the same column definitions but none of the data—you can use the following statement:

CREATE TABLE my_new_empty_table
AS
SELECT *
FROM existing_table
WHERE
1 = 2

Now, you're probably saying, "1 is never equal to 2." That's right. And that's why none of the rows in existing_table are copied into my_new_empty_table. The new table has the same set of column definitions as existing_table but no data. You could use any false statement to accomplish the same thing.

Summary

Today's lesson introduced some basic facts about SQL:

• Data Manipulation Language (DML) is a category of the SQL language that consists of the SELECT, INSERT, UPDATE, and DELETE statements
• Every SELECT statement contains a select list and a FROM clause that identifies the table from which the records will be retrieved.
• Typically, the SELECT statement also includes a WHERE clause that specifies criteria that the rows returned by the SELECT statement must satisfy. Also, the ORDER BY clause is used to specify the columns that should be used to determine the order in which the rows are returned by the query.
• The results returned by every SELECT statement can be thought of as a temporary table.
• Always explicitly specify the columns to be retrieved in the SELECT statement.

What Comes Next?

On Day 6, you learn how to use the other DML statements—INSERT, UPDATE, and DELETE—to change the contents of a table. You will see how a SELECT statement can be referenced in the UPDATE and DELETE statements to determine which rows to affect.

Q&A

Q Can the BETWEEN operator also be used for both numeric and character expressions?

A Yes. You have already seen an example of how the BETWEEN operator is used with numeric expression. Listing 5.31 is an example of how the BETWEEN operator can be used with a character expression to obtain a list of course titles that are between the character values S and W. Notice that the query in Listing 5.31 won't return any titles that begin with the phrase Workshop because that is "above" W.

Listing 5.31. Using the BETWEEN operator with strings.

SQL> select Title
  2  from Course
  3  where
  4  Title between `S' and `W';

TITLE
----------------------------------------
SEMINAR ON CHAOS
SEMINAR ON NACIMERA
SEMINAR ON THEME ANALYSIS

Q What is the internal value that Oracle uses to store a NULL value for a record?

A Generally, you won't be able to "see" that value. Furthermore, you really don't need to know what that value is because you will only be able to test a column for a NULL value using the IS NULL and IS NOT NULL comparisons. Alternatively, you could use a built-in function, NVL, that has two arguments: the first argument is the column or expression to be evaluated, and the second argument is the value to be returned if the first argument is equal to NULL.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercise before you continue with tomorrow's lesson.

Quiz

1. True or false? You must include a column in the select list if you want to sort the rows returned by the SELECT statement by that column.
2. What is wrong with this statement:

   select First_Name
   from Student
   order by Last_Name
   where
   Last_Name like `%IN%';
   

3. True or false? A column must be indexed before it can be specified in the ORDER BY clause.

Exercise

Using the Course table that was discussed in this lesson, construct a SELECT statement that will return the Department ID, Course ID, and Course Title, sorted by Department ID and Course ID, for any course whose description contains the phrase introduc, regardless of capitalization.