Oracle Database 10g SQL
Authors: Price M.
Published year: 2004
Pages: 65-69/217

<< Previous page
Table of contents
Next page >>

Automatically Generating SQL Statements

In this last section, I ll briefly show you a technique of writing SQL statements that produce other SQL statements. This is very useful and can save you a lot of typing when writing SQL statements that are similar. One simple example is a SQL statement that produces DROP TABLE statements that remove tables from a database. The following query produces a series of DROP TABLE statements that drop the tables in the store schema:


SELECT 'DROP TABLE '  table_name  ';'


FROM user_tables;

'DROPTABLE'TABLE_NAME';'
------------------------------------------
DROP TABLE COUPONS;
DROP TABLE CUSTOMERS;
DROP TABLE EMPLOYEES;
DROP TABLE PRODUCTS;
DROP TABLE PRODUCT_TYPES;
DROP TABLE PROMOTIONS;
DROP TABLE PURCHASES;
DROP TABLE PURCHASES_TIMESTAMP_WITH_TZ;
DROP TABLE PURCHASES_WITH_LOCAL_TZ;
DROP TABLE PURCHASES_WITH_TIMESTAMP;
DROP TABLE SALARY_GRADES;

Note	user_tables contains the details of the tables in the user s schema. The table_name column contains names of the tables.

You can spool the generated SQL statements to a file and use them later.

Summary

In this chapter, you learned how to

View the structure of a table
Edit a SQL statement
Save, retrieve, and run files containing SQL and SQL*Plus commands
Format column output and set the page and line sizes
Use variables in SQL*Plus
Create simple reports
Write SQL statements that generate other SQL statements

In the next chapter, you ll learn how to nest one query within another. The nested query is known as a subquery.

Chapter 6: Subqueries

Overview

All the queries you ve seen so far in this book have contained just one SELECT statement. In this chapter, you will

Learn how to place a SELECT statement within an outer SELECT , UPDATE , or DELETE statement. The inner SELECT statement is known as a subquery .
Learn about the different types of subqueries and see how to use them.
See how subqueries allow you to build up very complex statements from simple components .

Types of Subqueries

There are two basic types of subqueries:

Single row subqueries Return zero or one row to the outer SQL statement.
Multiple row subqueries Return one or more rows to the outer SQL statement.

In addition, there are three subtypes of subqueries that may return single or multiple rows:

Multiple column subqueries Return more than one column to the outer SQL statement.
Correlated subqueries Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
Nested subqueries Are placed within another subquery. You can nest subqueries to a depth of 255.

You ll learn about each of these types of subqueries in this chapter, and see how to add subqueries to SELECT , UPDATE , and DELETE statements. Let s plunge in and look at how to write single row subqueries.

Writing Single Row Subqueries

A single row subquery is one that returns zero or one row to the outer SQL statement. As you ll see in this section, you may place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement. You ll also see some errors you might encounter when issuing subqueries.

Subqueries in a WHERE Clause

You may place a subquery in the WHERE clause of another query. Let s take a look at a very simple example of a query that contains a subquery placed in its WHERE clause; notice the subquery is placed within parentheses (...):


SELECT first_name, last_name


FROM customers


WHERE customer_id =


(SELECT customer_id


FROM customers


WHERE last_name = 'Brown');

FIRST_NAME LAST_NAME
---------- ----------
John Brown

This example retrieves the first_name and last_name of the row from the customers table whose last_name is Brown. Let s break this query down and analyze what s going on. The subquery in the WHERE clause is as follows :

SELECT customer_id
FROM customers
WHERE last_name = 'Brown';

This subquery is executed first (and only once) and returns the customer_id for the row whose last_name is Brown. The customer_id for this row is 1, which is passed to the WHERE clause of the outer query. Therefore, the outer query may be considered to be identical to the following query:

SELECT first_name, last_name
FROM customers
WHERE customer_id = 1;

Using Other Single Row Operators

The previous example used the equality operator ( = ) in the WHERE clause. You may also use other comparison operators such as <>, <, >, < = , and > = with a single row subquery. The following example uses > in the outer query s WHERE clause; the subquery uses the AVG() function to get the average price of the products, which is passed to the WHERE clause of the outer query. The final result of the entire query is to get the product_id , name , and price of products whose price is greater than that average price.


SELECT product_id, name, price


FROM products


WHERE price

>

(SELECT AVG(price)


FROM products);

PRODUCT_ID NAME PRICE
---------- ------------------------------ ----------
 1 Modern Science 19.95
 2 Chemistry 30
 3 Supernova 25.99
 5 Z Files 49.99

Let s break the example down to understand how it works. The following shows the output from the subquery when it s run on its own:


SELECT AVG(price)


FROM products;

AVG(PRICE)
----------
19.7308333

The value of 19.7308333 is used in the WHERE clause of the outer query shown earlier to get the products whose price is greater than that average.

Subqueries in a HAVING Clause

As you saw in Chapter 3, you use the HAVING clause to filter groups of rows. You may place a subquery in a HAVING clause of an outer query. This allows you to filter groups of rows based on the result returned by your subquery.

The following example uses a subquery in the HAVING clause of the outer query. The example retrieves the product_type_id and the average price for products whose average price is less than the maximum of the average for the groups of the same type product type:


SELECT product_type_id, AVG(price)


FROM products


GROUP BY product_type_id


HAVING AVG(price)

<

(SELECT MAX(AVG(price))


FROM products


GROUP BY product_type_id);

PRODUCT_TYPE_ID AVG(PRICE)
--------------- ----------
 1 24.975
 3 13.24
 4 13.99
 13.49

Notice the subquery uses AVG() to first compute the average price for each product type. The result returned by AVG() is then passed to MAX() , which returns the maximum of the averages.

Let s break the example down to understand how it works. The following shows the output from the subquery when it is run on its own:


SELECT MAX(AVG(price))


FROM products


GROUP BY product_type_id;

MAX(AVG(PRICE))
---------------
 26.22

This value of 26.22 is used in the HAVING clause of the outer query shown earlier to filter the group s rows to those having an average price less than 26.22. The following query shows a version of the outer query that retrieves the product_type_id and average price of the products grouped by product_type_id :


SELECT product_type_id, AVG(price)


FROM products


GROUP BY product_type_id;

PRODUCT_TYPE_ID AVG(PRICE)
--------------- ----------
 1 24.975
 2 26.22
 3 13.24
 4 13.99
 13.49

You can see that that the groups with a product_type_id of 1, 3, 4, and null have an average price less than 26.22. As expected, these are the same groups returned by the query containing the subquery at the start of this section.

Subqueries in a FROM Clause (Inline Views)

You may place a subquery in the FROM clause of an outer query. These types of subqueries are also known as inline views because the subquery provides data inline with the FROM clause. The following simple example retrieves the products whose product_id is less than 3:


SELECT product_id


FROM


(SELECT product_id


FROM products


WHERE product_id

<

3);

PRODUCT_ID
----------
 1
 2

Notice the subquery returns the rows from the products table whose product_id is less than 3 to the outer query, which then retrieves and displays those product_id values. As far as the FROM clause of the outer query is concerned , the output from the subquery is just another source of data.

The next example is more useful and retrieves the product_id and price from the products table in the outer query, and the subquery retrieves the number of times a product has been purchased:


SELECT prds.product_id, price, purchases_data.product_count


FROM products prds,


(SELECT product_id, COUNT(product_id) product_count


FROM purchases


GROUP BY product_id) purchases_data


WHERE prds.product_id = purchases_data.product_id;

PRODUCT_ID PRICE PRODUCT_COUNT
---------- ---------- -------------
 1 19.95 4
 2 30 4
 3 25.99 1

Notice the subquery retrieves the product_id and COUNT(product_id) from the purchases table and returns them to the outer query. As you can see, the output from subquery is just another source of data to the FROM clause of the outer query.

A Couple of Errors You Might Encounter

In this section, you ll see some errors you might encounter. Specifically, you ll see that a single row subquery may return a maximum of one row, and you ll see a subquery may not contain an ORDER BY clause.

Single Row Subqueries May Return a Maximum of One Row

If your subquery returns more than one row, you ll get the following error:

ORA-01427: single-row subquery returns more than one row.

For example, the subquery in the following statement attempts to pass multiple rows to the equality operator (=) in the outer query:

SQL>

SELECT product_id, name

2

FROM products

3

WHERE product_id =

4

(SELECT product_id

5

FROM products

6

WHERE name LIKE '%e%');

(SELECT product_id
 *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

There are nine rows in the products table whose name contains the letter e , and the subquery attempts to pass these rows to the equality operator in the outer query. Since the equality operator can only handle a single row, the query is invalid and an error is returned.

You ll learn how to return multiple rows from a subquery later in the section Writing Multiple Row Subqueries.

Subqueries May Not Contain an ORDER BY Clause

A subquery may not contain an ORDER BY clause. Instead, you must do any ordering in your outer query. For example, the following outer query has an ORDER BY clause at the end that sorts on the product_id column:


SELECT product_id, name, price


FROM products


WHERE price

>

(SELECT AVG(price)


FROM products)


ORDER BY product_id DESC;

PRODUCT_ID NAME PRICE
---------- ------------------------------ ----------
 5 Z Files 49.99
 3 Supernova 25.99
 2 Chemistry 30
 1 Modern Science 19.95