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

Expressions, Conditions, and Operators


You will

Know what an -expression is and how to use it

Know what a condition is and how to use it

Be familiar with the basic uses of the WHERE clause

Be able to use arithmetic, comparison, character, logical, and set operators

Have a working knowledge of some miscellaneous operators


NOTE: We used Oracle's Personal Oracle7 to generate today's examples. Other implementations of SQL may differ slightly in the way in which commands are entered or output is displayed, but the results are basically the same for all implementations that conform to the ANSI standard.

Expressions


    The definition of a -expression is simple: A -expression returns a value. -Expression types are very broad, covering different data types such as String, Numeric, and Boolean. In fact, pretty much anything following a clause (SELECT or FROM, for example) is an -expression. In the following example amount is a -expression that returns the value contained in the amount column.

SELECT amount FROM checks;

    In the following statement NAME, ADDRESS, PHONE and ADDRESSBOOK are expressions:

SELECT NAME, ADDRESS, PHONE FROM ADDRESSBOOK;

    Now, examine the following -expression:

WHERE NAME = 'BROWN'

    The condition, NAME = 'BROWN', which is an example of a Boolean -expression. NAME = 'BROWN' will be either TRUE or FALSE, depending on the condition =.

Conditions


    If you ever want to find a particular item or group of items in your database, you need one or more conditions. Conditions are contained in the WHERE clause. In the preceding example, the condition is

NAME = 'BROWN'

    To find everyone in your organization who worked more than 100 hours last month, your condition would be

NUMBEROFHOURS > 100

    Conditions enable you to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator. In the first example the variable is NAME, the constant is 'BROWN', and the comparison operator is =. In the second example the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >. You need to know about two more elements before you can write conditional queries: the WHERE clause and operators.

The WHERE Clause


SYNTAX:

WHERE SEARCH CONDITION

    SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective. Without the WHERE clause, the most useful thing you could do with a query is display all records in the selected table(s). For example:

INPUT:

SQL> SELECT * FROM BIKES;

    Lists all rows of data in the table BIKES. If you wanted a particular bike, you could type

INPUT:

SQL> SELECT * FROM BIKES WHERE NAME = 'BURLEY';

ANALYSIS:

    This simple example shows how you can place a condition on the data that you want to retrieve.

Operators


    Operators are the elements you use inside a -expression to articulate how you want specified conditions to retrieve data. Operators fall into six groups: arithmetic, comparison, character, logical, set, and miscellaneous.

Arithmetic Operators


    The arithmetic operators are plus (+), minus (-), divide (/), multiply (*), and modulo (%). The first four are self-explanatory. Modulo returns the integer remainder of a division.

    The modulo operator does not work with data types that have decimals, such as Real or Number. If you place several of these arithmetic operators in a -expression without any parentheses, the operators are resolved in this order: multiplication, division, modulo, addition, and subtraction.


    Watch where you put those parentheses! Sometimes the -expression does exactly what you tell it to do, rather than what you want it to do. The following sections examine the arithmetic operators in some detail and give you a chance to write some queries.

Plus (+)


    You can use the plus sign in several ways. Type the following statement to display the PRICE table: Here the + adds 15 cents to each price to produce the following:

INPUT:

SQL> SELECT ITEM, WHOLESALE, WHOLESALE + 0.15 FROM PRICE;

ANALYSIS:

    What is this last column with the unattractive column heading WHOLESALE+0.15? It's not in the original table. (Remember, you used * in the SELECT clause, which causes all the columns to be shown.) SQL allows you to create a virtual or derived column by combining or modifying existing columns. The original data has not been changed and that the column heading WHOLESALE+0.15 is not a permanent part of it. In fact, the column heading is so unattractive that you should do something about it.

INPUT:

SQL> SELECT ITEM, WHOLESALE, (WHOLESALE + 0.15) RETAIL FROM PRICE;

    This is wonderful! Not only can you create new columns, but you can also rename them on the fly. You can rename any of the columns using the syntax column-name alias (note the space between column-name and alias). Renames the columns Item to Produce:

INPUT:

SQL> SELECT ITEM PRODUCE, WHOLESALE, WHOLESALE + 0.25 RETAIL FROM PRICE;

NOTE: Some implementations of SQL use the syntax (column name = alias). The preceding example would be written as follows:

SQL> SELECT ITEM = PRODUCE,
WHOLESALE,
WHOLESALE + 0.25 = RETAIL,
FROM PRICE;

    Check your implementation for the exact syntax.


    You might be wondering what use aliasing is if you are not using command-line SQL. Fair enough. Have you ever wondered how report builders work? Someday, when you are asked to write a report generator, you'll remember this and not spend weeks reinventing what Dr. Codd and IBM have wrought.

    So far, you have seen two uses of the plus sign. The first instance was the use of the plus sign in the SELECT clause to perform a calculation on the data and display the calculation. The second use of the plus sign is in the WHERE clause. Using operators in the WHERE clause gives you more flexibility when you specify conditions for retrieving data.

    In some implementations of SQL, the plus sign does double duty as a character operator. You'll see that side of the plus a little later today.

Minus (-)


    Minus also has two uses. First, it can change the sign of a number. You can use the table HILOW to demonstrate this function.

INPUT:

SQL> SELECT STATE, -HIGHTEMP LOWS, -LOWTEMP HIGHS FROM HILOW;

    The second (and obvious) use of the minus sign is to subtract one column from another. For example:

INPUT:

SQL> SELECT STATE,
HIGHTEMP LOWS,
LOWTEMP HIGHS,
(LOWTEMP - HIGHTEMP) DIFFERENCE
FROM HILOW;

    Notice the use of aliases to fix the data that was entered incorrectly. This remedy is merely a temporary patch, though, and not a permanent fix. You should see to it that the data is corrected and entered correctly in the future. This query not only fixed (at least visually) the incorrect data but also created a new column containing the difference between the highs and lows of each state.

Divide (/)


    The division operator has only the one obvious meaning. Using the table PRICE, type the following: You can show the effects of a two-for-one sale by typing the next statement:

INPUT:

SQL> SELECT ITEM, WHOLESALE, (WHOLESALE/2) SALEPRICE FROM PRICE;

    The use of division in the preceding SELECT statement is straightforward (except that coming up with half pennies can be tough).

Multiply (*)


    The multiplication operator is also straightforward. Again, using the PRICE table, type the following: This query changes the table to reflect an across-the-board 10 percent discount:

INPUT:

SQL> SELECT ITEM, WHOLESALE, WHOLESALE * 0.9 NEWPRICE FROM PRICE;

    These operators enable you to perform powerful calculations in a SELECT statement.

Modulo (%)


    The modulo operator returns the integer remainder of the division operation. Using the table REMAINS, type the following: You can also create a new column, REMAINDER, to hold the values of NUMERATOR % DENOMINATOR:

INPUT:

SQL> SELECT NUMERATOR,
DENOMINATOR,
NUMERATOR%DENOMINATOR REMAINDER
FROM REMAINS;

    Some implementations of SQL implement modulo as a function called MOD. The following statement produces results that are identical to the results in the preceding statement:

SQL> SELECT NUMERATOR,
DENOMINATOR,
MOD(NUMERATOR,DENOMINATOR) REMAINDER
FROM REMAINS;

Precedence


    This section examines the use of precedence in a SELECT statement. Using the database PRECEDENCE, type the following: Use the following code segment to test precedence:

INPUT:

SQL> SELECT
N1+N2*N3/N4,
(N1+N2)*N3/N4,
N1+(N2*N3)/N4
FROM PRECEDENCE;

    Notice that the first and last columns are identical. If you added a fourth column N1+N2* (N3/N4), its values would also be identical to those of the current first and last columns.

Comparison Operators


    True to their name, comparison operators compare expressions and return one of three values: TRUE, FALSE, or Unknown. Wait a minute! Unknown? TRUE and FALSE are self-explanatory, but what is Unknown?

    To understand how you could get an Unknown, you need to know a little about the concept of NULL. In database terms NULL is the absence of data in a field. It does not mean a column has a zero or a blank in it. A zero or a blank is a value. NULL means nothing is in that field. If you make a comparison like Field = 9 and the only value for Field is NULL, the comparison will come back Unknown. Because Unknown is an uncomfortable condition, most flavors of SQL change Unknown to FALSE and provide a special operator, IS NULL, to test for a NULL condition.

    Here's an example of NULL: Suppose an entry in the PRICE table does not contain a value for WHOLESALE. The results of a query might look like this: Notice that nothing is printed out in the WHOLESALE field position for oranges. The value for the field WHOLESALE for oranges is NULL. The NULL is noticeable in this case because it is in a numeric column. However, if the NULL appeared in the ITEM column, it would be impossible to tell the difference between NULL and a blank.

INPUT:

SQL> SELECT *
FROM PRICE
WHERE WHOLESALE IS NULL;

ANALYSIS:

    As you can see by the output, ORANGES is the only item whose value for WHOLESALE is NULL or does not contain a value. What if you use the equal sign (=) instead?

INPUT:

SQL> SELECT *
FROM PRICE
WHERE WHOLESALE = NULL;

ANALYSIS:

    You didn't find anything because the comparison WHOLESALE = NULL returned a FALSE--the result was unknown. It would be more appropriate to use an IS NULL instead of =, changing the WHERE statement to WHERE WHOLESALE IS NULL. In this case you would get all the rows where a NULL existed.

    This example also illustrates both the use of the most common comparison operator, the equal sign (=), and the playground of all comparison operators, the WHERE clause. You already know about the WHERE clause, so here's a brief look at the equal sign.

Equal (=)


    Earlier today you saw how some implementations of SQL use the equal sign in the SELECT clause to assign an alias. In the WHERE clause, the equal sign is the most commonly used comparison operator. Used alone, the equal sign is a very convenient way of selecting one value out of many. Try this: Let's find JD's row. (On a short list this task appears trivial, but you may have more friends than we do--or you may have a list with thousands of records.)

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE FIRSTNAME = 'JD';

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE FIRSTNAME = 'AL';

NOTE:Here you see that = can pull in multiple records. Notice that ZIP is blank on the second record. ZIP is a character field (you learn how to create and populate tables on Day 8, "Manipulating Data"), and in this particular record the NULL demonstrates that a NULL in a character field is impossible to differentiate from a blank field.


INPUT:

SQL> SELECT * FROM FRIENDS
WHERE FIRSTNAME = 'BUD';

INPUT:

SQL> select * from friends
where firstname = 'Bud';

ANALYSIS:

    Even though SQL syntax is not case sensitive, data is. Most companies prefer to store data in uppercase to provide data consistency. You should always store data either in all uppercase or in all lowercase. Mixing case creates difficulties when you try to retrieve accurate data.

Greater Than (>) and Greater Than or Equal To (>=)

The greater than operator (>) works like this:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE AREACODE > 300;

ANALYSIS:

    This example found all the area codes greater than (but not including) 300. To include 300, type this:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE AREACODE >= 300;

ANALYSIS:

    With this change you get area codes starting at 300 and going up. You could achieve the same results with the statement AREACODE > 299.

NOTE:Notice that no quotes surround 300 in this SQL statement. Number-defined fieldsnumber-defined fields do not require quotes.


Less Than (<) and Less Than or Equal To (<=)


    As you might expect, these comparison operators work the same way as > and >= work, only in reverse:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE STATE < 'LA';

NOTE:How did STATE get changed to ST? Because the column has only two characters, the column name is shortened to two characters in the returned rows. If the column name had been COWS, it would come out CO. The widths of AREACODE and PHONE are wider than their column names, so they are not truncated.


ANALYSIS:

    Wait a minute. Did you just use < on a character field? Of course you did. You can use any of these operators on any data type. The result varies by data type. For example, use lowercase in the following state search:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE STATE < 'la';

ANALYSIS:

    Uppercase is usually sorted before lowercase; therefore, the uppercase codes returned are less than 'la'. Again, to be safe, check your implementation.

TIP:To be sure of how these operators will behave, check your language tables. Most PC implementations use the ASCII tables. Some other platforms use EBCDIC.


    To include the state of Louisiana in the original search, type

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE STATE <= 'LA';

Inequalities (< > or !=)


    When you need to find everything except for certain data, use the inequality symbol, which can be either < > or !=, depending on your SQL implementation. For example, to find everyone who is not AL, type this:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE FIRSTNAME < > 'AL';

    To find everyone not living in California, type this:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE STATE != 'CA';

NOTE:Notice that both symbols, < > and !=, can express "not equals."


Character Operators


    You can use character operators to manipulate the way character strings are represented, both in the output of data and in the process of placing conditions on data to be retrieved. This section describes two character operators: the LIKE operator and the || operator, which conveys the concept of character concatenation.

I Want to Be Like LIKE


    What if you wanted to select parts of a database that fit a pattern but weren't quite exact matches? You could use the equal sign and run through all the possible cases, but that process would be boring and time-consuming. Instead, you could use LIKE. Consider the following:

INPUT:

    How can you find all the parts located in the back? A quick visual inspection of this simple table shows that it has two parts, but unfortunately the locations have slightly different names. Try this:

SQL> SELECT *
FROM PARTS
WHERE LOCATION LIKE '%BACK%';

ANALYSIS:

    You can see the use of the percent sign (%) in the statement after LIKE. When used inside a LIKE -expression, % is a wildcard. What you asked for was any occurrence of BACK in the column location.

INPUT:

    If you queried you would get any occurrence that started with BACK:

SQL> SELECT *
FROM PARTS
WHERE LOCATION LIKE 'BACK%';

INPUT:

    If you queried you would get any name that starts with A:

SQL> SELECT *
FROM PARTS
WHERE NAME LIKE 'A%';

INPUT:

    Is LIKE case sensitive? Try the next query to find out.

SQL> SELECT *
FROM PARTS
WHERE NAME LIKE 'a%';

ANALYSIS:

    The answer is yes. References to data are always case sensitive. What if you want to find data that matches all but one character in a certain pattern? In this case you could use a different type of wildcard: the underscore.

Underscore (-)


    The underscore is the single-character wildcard. Using a modified version of the table FRIENDS, type this:

INPUT:

    To find all the records where STATE starts with C, type the following:

SQL> SELECT *
FROM FRIENDS
WHERE STATE LIKE 'C-';

INPUT:

    You can use several underscores in a statement:

SQL> SELECT *
FROM FRIENDS
WHERE PHONE LIKE'555-6-6-';

Concatenation (||)


    The || (double pipe) symbol concatenates two strings. Try this:

INPUT:

SQL> SELECT FIRSTNAME || LASTNAME ENTIRENAME
FROM FRIENDS;

ANALYSIS:

    Notice that || is used instead of +. If you use + to try to concatenate the strings, the SQL interpreter used for this example (Personal Oracle7) returns the following error:

NOTE:Some implementations of SQL use the plus sign to concatenate strings. Check your implementation.


INPUT:

SQL> SELECT LASTNAME || ',' || FIRSTNAME NAME
FROM FRIENDS;

ANALYSIS:

    This statement inserted a comma between the last name and the first name.

NOTE:Notice the extra spaces between the first name and the last name in these examples. These spaces are actually part of the data. With certain data types, spaces are right-padded to values less than the total length allocated for a field. See your implementation. Data types will be discussed on Day 9, "Creating and Maintaining Tables."


    So far you have performed the comparisons one at a time. That method is fine for some problems, but what if you need to find all the people at work with last names starting with P who have less than three days of vacation time?

Logical Operators


    logical operators separate two or more conditions in the WHERE clause of an SQL statement.Vacation time is always a hot topic around the workplace. Say you designed a table called VACATION for the accounting department:

    Suppose your company gives each employee 12 days of leave each year. Using what you have learned and a logical operator, find all the employees whose names start with B and who have more than 50 days of leave coming.

INPUT:

SQL> SELECT LASTNAME,
YEARS * 12 - LEAVETAKEN REMAINING
FROM VACATION
WHERE LASTNAME LIKE 'B%'
AND
YEARS * 12 - LEAVETAKEN > 50;

ANALYSIS:

    This query is the most complicated you have done so far. The SELECT clause (lines 1 and 2) uses arithmetic operators to determine how many days of leave each employee has remaining. The normal precedence computes YEARS * 12 - LEAVETAKEN. (A clearer approach would be to write (YEARS * 12) - LEAVETAKEN.)

    LIKE is used in line 4 with the wildcard % to find all the B names. Line 6 uses the > to find all occurrences greater than 50.The new element is on line 5. You used the logical operator AND to ensure that you found records that met the criteria in lines 4 and 6.

Logical AND


    AND means that the expressions on both sides must be true to return TRUE. If either -expression is false, AND returns FALSE. For example, to find out which employees have been with the company for 5 years or less and have taken more than 20 days leave, try this:

INPUT:

SQL> SELECT LASTNAME
FROM VACATION
WHERE YEARS <= 5
AND
LEAVETAKEN > 20 ;

    If you want to know which employees have been with the company for 5 years or more and have taken less than 50 percent of their leave, you could write:

INPUT:

SQL> SELECT LASTNAME WORKAHOLICS
FROM VACATION
WHERE YEARS >= 5
AND
((YEARS *12)-LEAVETAKEN)/(YEARS * 12) <0.50;

    Check these people for burnout. Also check out how we used the AND to combine these two conditions.

Logical OR


    You can also use OR to sum up a series of conditions. If any of the comparisons is true, OR returns TRUE. To illustrate the difference, conditions run the last query with OR instead of with AND:

INPUT:

SQL> SELECT LASTNAME WORKAHOLICS
FROM VACATION
WHERE YEARS >= 5
OR
((YEARS *12)-LEAVETAKEN)/(YEARS * 12) >= 0.50;

ANALYSIS:

    The original names are still in the list, but you have three new entries (who would probably resent being called workaholics). These three new names made the list because they satisfied one of the conditions. OR requires that only one of the conditions be true in order for data to be returned.

Logical NOT


    NOT means just that. If the condition it applies to evaluates to TRUE, NOT make it FALSE. If the condition after the NOT is FALSE, it becomes TRUE. For example, the following SELECT returns the only two names not beginning with B in the table:

INPUT:

SQL> SELECT *
FROM VACATION
WHERE LASTNAME NOT LIKE 'B%';

    NOT can also be used with the operator IS when applied to NULL. Recall the PRICES table where we put a NULL value in the WHOLESALE column opposite the item ORANGES. To find the non-NULL items, type this:

INPUT:

SQL> SELECT *
FROM PRICE
WHERE WHOLESALE IS NOT NULL;

Set Operators


UNION and UNION ALL


    UNION returns the results of two queries minus the duplicate rows. The following two tables represent the rosters of teams: How many different people play on one team or another?

INPUT:

SQL> SELECT NAME FROM SOFTBALL
UNION
SELECT NAME FROM FOOTBALL;

    UNION returns 10 distinct names from the two lists. How many names are on both lists (including duplicates)?

INPUT:

SQL> SELECT NAME FROM SOFTBALL
UNION ALL
SELECT NAME FROM FOOTBALL;

ANALYSIS:

    The combined list--courtesy of the UNION ALL statement--has 14 names. UNION ALL works just like UNION except it does not eliminate duplicates. Now show me a list of players who are on both teams. You can't do that with UNION--you need to learn INTERSECT.

INTERSECT


    INTERSECT returns only the rows found by both queries. The next SELECT statement shows the list of players who play on both teams:

INPUT:

SQL> SELECT * FROM FOOTBALL
INTERSECT
SELECT * FROM SOFTBALL;

ANALYSIS:

    In this example INTERSECT finds the short list of players who are on both teams by combining the results of the two SELECT statements.

MINUS (Difference)


    Minus returns the rows from the first query that were not present in the second. For example:

INPUT:

SQL> SELECT * FROM FOOTBALL
MINUS
SELECT * FROM SOFTBALL;

ANALYSIS:

    The preceding query shows the three football players who are not on the softball team. If you reverse the order, you get the three softball players who aren't on the football team:

Miscellaneous Operators: IN and BETWEEN


    The two operators IN and BETWEEN provide a shorthand for functions you already know how to do. If you wanted to find friends in Colorado, California, and Louisiana, you could type the following:

INPUT:

SQL> SELECT *
FROM FRIENDS
WHERE STATE IN('CA','CO','LA');

ANALYSIS:

    The second example is shorter and more readable than the first. You never know when you might have to go back and work on something you wrote months ago. IN also works with numbers. Consider the following, where the column AREACODE is a number:

    Or using BETWEEN, you would write this:

INPUT:

SQL> SELECT *
FROM PRICE
WHERE WHOLESALE BETWEEN 0.25 AND 0.75;

    Again, the second example is a cleaner, more readable solution than the first.

NOTE: If a WHOLESALE value of 0.25 existed in the PRICE table, that record would have been retrieved also. Parameters used in the BETWEEN operator are inclusive parameters inclusive.


Summary


    You learned how to use arithmetic, comparison, character, logical, and set operators. This powerful set of tools provides the cornerstone of your SQL knowledge.