New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

SQL Clauses


SQL has the following clauses:


• WHERE

• STARTING WITH

• ORDER BY

• GROUP BY

• HAVING


NOTE:In my experience with SQL, the ANSI standard is really more of an ANSI "suggestion." The preceding syntax will generally work with any SQL engine, but you may find some slight variations.

NOTE:You haven't yet had to deal with a complicated syntax diagram. Because many people find syntax diagrams more puzzling than illuminating when learning something new, this book has used simple examples to illustrate particular points. However, we are now at the point where a syntax diagram can help tie the familiar concepts to today's new material.

    Don't worry about the exact syntax--it varies slightly from implementation to implementation anyway. Instead, focus on the relationships. At the top of this statement is SELECT, which you have used many times in the last few days. SELECT is followed by FROM, which should appear with every SELECT statement you typed. (You learn a new use for FROM tomorrow.) WHERE, GROUP BY, HAVING, and ORDER BY all follow. (The other clauses in the diagram--UNION, UNION ALL, INTERSECT, and MINUS--, "Expressions, Conditions, and Operators.") Each clause plays an important part in selecting and manipulating data.

NOTE:We have used two implementations of SQL to prepare today's examples. One implementation has an SQL> prompt and line numbers (Personal Oracle7), and the other (Borland's ISQL) does not. You will also notice that the output displays vary slightly, depending on the implementation.


The WHERE Clause


    Using WHERE in select statement can be more selective (Row wise Filter).

INPUT:

SQL> SELECT * FROM CHECKS WHERE AMOUNT > 100;

The STARTING WITH Clause


    STARTING WITH is an addition to the WHERE clause that works exactly like LIKE (<exp> %). Compare the results of the following query:

INPUT:

SQL> SELECT PAYEE, AMOUNT, REMARKS FROM CHECKS WHERE PAYEE
STARTING WITH ('Ca');

INPUT:

SQL> SELECT PAYEE, AMOUNT, REMARKS FROM CHECKS WHERE PAYEE
STARTING WITH ('Ca') OR REMARKS LIKE 'G%';

WARNING:STARTING WITH is a common feature of many implementations of SQL. Check your implementation before you grow fond of it.


The ORDER BY Clause


    From time to time you will want to present the results of your query in some kind of order. As you know, however, SELECT FROM gives you a listing, and unless you have defined a primary key, your query comes out in the order the rows were entered. Consider a beefed-up CHECKS table:

INPUT:

SQL> SELECT * FROM CHECKS ORDER BY PAYEE;

INPUT:

SQL> SELECT * FROM CHECKS ORDER BY PAYEE DESC;

ANALYSIS:

    The DESC at the end of the ORDER BY clause orders the list in descending order instead of the default (ascending) order. The rarely used, optional keyword ASC appears in the following statement:

INPUT:

SQL> SELECT PAYEE, AMOUNT FROM CHECKS ORDER BY PAYEE ASC;

ANALYSIS:

    The ordering in this list is identical to the ordering of the list at the beginning of the section (without ASC) because ASC is the default. This query also shows that the -expression used after the ORDER BY clause does not have to be in the SELECT statement. You can also use ORDER BY on more than one field. To order CHECKS by PAYEE and REMARKS, you would query as follows:

INPUT:

SQL> SELECT * FROM CHECKS ORDER BY PAYEE, REMARKS;

    Here's how to list one column in alphabetical order and list the second column in reverse alphabetical order:

INPUT:

SQL> SELECT * FROM CHECKS ORDER BY PAYEE ASC, REMARKS DESC;

TIP: If you know that a column you want to order your results by is the first column in a table, then you can type ORDER BY 1 in place of spelling out the column name.


INPUT:

SQL> SELECT * FROM CHECKS ORDER BY 1

ANALYSIS:

    This result is identical to the result produced by the SELECT statement earlier.


The GROUP BY Clause


INPUT:

SQL> SELECT PAYEE, SUM (AMOUNT) FROM CHECKS GROUP BY PAYEE;

ANALYSIS:

    The SELECT clause has a normal column selection, PAYEE, followed by the aggregate function SUM (AMOUNT) with group clause.

INPUT:

SQL> SELECT PAYEE, SUM (AMOUNT) FROM CHECKS;

ANALYSIS:

    SQL is complaining about the combination of the normal column and the aggregate function. This condition requires the GROUP BY clause. GROUP BY runs the aggregate function described in the SELECT statement for each grouping of the column that follows the GROUP BY clause. The table CHECKS returned 14 rows when queried with SELECT * FROM CHECKS. The query on the same table, SELECT PAYEE, SUM (AMOUNT) FROM CHECKS GROUP BY PAYEE, took the 14 rows in the table and made seven groupings, returning the SUM of each grouping.

    Suppose you wanted to know how much you gave to whom with how many checks. Can you use more than one aggregate function?

INPUT:

SQL> SELECT PAYEE, SUM (AMOUNT), COUNT (PAYEE) FROM CHECKS GROUP BY PAYEE;

ANALYSIS:

    This SQL is becoming increasingly useful! In the preceding example, you were able to perform group functions on unique groups using the GROUP BY clause. Also notice that the results were ordered by payee. GROUP BY also acts like the ORDER BY clause. What would happen if you tried to group by more than one column? Try this:

INPUT:

SQL> SELECT PAYEE, SUM (AMOUNT), COUNT (PAYEE) FROM CHECKS
GROUP BY PAYEE, REMARKS;

The HAVING Clause


    How can you qualify the data used in your GROUP BY clause? Use the table ORGCHART and try this:

INPUT:

SQL> SELECT TEAM, AVG (SALARY) FROM ORGCHART
GROUP BY TEAM HAVING AVG (SALARY) < 38000;

ANALYSIS:

    HAVING enables you to use aggregate functions in a comparison statement, providing for aggregate functions what WHERE provides for individual rows. Does HAVING worked with non aggregate expressions? Try this:

INPUT:

SQL> SELECT TEAM, AVG (SALARY) FROM ORGCHART
GROUP BY TEAM HAVING SALARY < 38000

ANALYSIS:

    Why is this result different from the last query? The HAVING AVG(SALARY) < 38000 clause evaluated each grouping and returned only those with an average salary of under 38000, just what you expected. HAVING SALARY < 38000, on the other hand, had a different outcome. Take on the role of the SQL engine again. If the user asks you to evaluate and return groups of divisions where SALARY < 38000, you would examine each group and reject those where an individual SALARY is greater than 38000. In each division except PR, you would find at least one salary greater than 38000:

WARNING:Some implementations of SQL return an error if you use anything other than an aggregate function in a HAVING clause. Don't bet the farm on using the previous example until you check the implementation of the particular SQL you use.


    Can you use more than one condition in your HAVING clause? Try this:

INPUT:

SQL> SELECT TEAM, AVG(SICKLEAVE),AVG(ANNUALLEAVE) FROM ORGCHART
GROUP BY TEAM HAVING AVG(SICKLEAVE)>25 AND AVG(ANNUALLEAVE)<20;

ANALYSIS:

    The following table is grouped by TEAM. It shows all the teams with SICKLEAVE averages above 25 days and ANNUALLEAVE averages below 20 days.

Combining Clauses


    Nothing exists in a vacuum, so this section takes you through some composite examples that demonstrate how combinations of clauses perform together.

    Find all the checks written for Cash and Gas in the CHECKS table and order them by REMARKS.

INPUT:

SQL> SELECT PAYEE, REMARKS FROM CHECKS WHERE PAYEE = ‘Cash’ OR REMARKS
LIKE'Ga%’ ORDER BY REMARKS;

ANALYSIS:

    Note the use of LIKE to find the REMARKS that started with Ga. With the use of OR, data was returned if the WHERE clause met either one of the two conditions.

    What if you asked for the same information and group it by PAYEE? The query would look something like this:

INPUT:

SQL> SELECT PAYEE, REMARKS FROM CHECKS WHERE PAYEE = ‘Cash’ OR REMARKS
LIKE'Ga%’ GROUP BY PAYEE ORDER BY REMARKS;

ANALYSIS:

    This query would not work because the SQL engine would not know what to do with the remarks. Remember that whatever columns you put in the SELECT clause must also be in the GROUP BY clause--unless you don't specify any columns in the SELECT clause.

INPUT:

SQL> SELECT PAYEE, SUM (AMOUNT) TOTAL, COUNT (PAYEE)
NUMBER-WRITTEN FROM CHECKS
GROUP BY PAYEE HAVING SUM (AMOUNT) > 50;

INPUT:

SQL> SELECT PAYEE, SUM (AMOUNT) TOTAL, COUNT (PAYEE)
NUMBER-WRITTEN FROM CHECKS WHERE AMOUNT >= 100
GROUP BY PAYEE HAVING SUM (AMOUNT) > 50;