To build a Personal Oracle7 application, it's crucial for you to develop a working knowledge of Structured Query Language (SQL). SQL is a powerful language that differs from traditional third-generation languages, such as C and Pascal, in several significant areas:
One similarity between SQL and a traditional programming language is that both usually give you more than one way to accomplish the same goal--;particularly when retrieving information. Various SQL statements may achieve the same results (but differ in processing efficiency and clarity of code).
At the heart of relational theory is the concept of the table. A table consists of a set of attributes, or columns, and zero or more rows. Each row is a set of attribute values.
Before relational databases were available, organizations depended upon database management systems that were categorized as hierarchical or network. Both types had the same major drawback: A change to an existing data element or the addition of a new data element usually involved extensive changes to existing programs. Because almost 70 percent of the cost for a typical information system is incurred during the maintenance phase of its life cycle, any reduction in software maintenance costs due to database changes saves an organization time and money.
In a relational database, the effect of changing a data element--;a column--;is often minimal. For example, suppose the original system requirements called for a customer number that was a maximum of five digits long. As the business grows, it becomes obvious that a five-digit customer number is too short and that the customer number needs eight digits. In a relational database, the increase or decrease in size of a column is independent of the other columns in the table; as far as a user is concerned, changing the size of one column has no effect on the other columns. Of course, you will probably need to change data entry screens and reports to reflect the modification to the customer number column, but the software maintenance effort is localized to the modified column.
One of the rules of relational theory is that the order of columns is unimportant. What does this statement really mean? It means that at the conceptual level, the columns of a table can be retrieved in whatever order you like. That's it. It doesn't mean that the physical storage of the data has no order; there is an order. The ordering information is maintained, however, at the physical level; the developer and user don't need to know about those details.
Another rule of relational theory is that the order of rows is unimportant. Again, that rule doesn't mean that rows aren't physically stored in some particular order, but rather that no implied order is imposed on the way that rows will be retrieved from a table. This subtle difference is important: If you want to return the rows of a table in a particular order, you always have to specify what that order is.
At the highest level, SQL statements can be broadly categorized as follows:
The category of DML contains four basic statements:
These statements are used most often by application developers.
Here are some grammatical requirements to keep in mind when you're working with SQL.
Of the four DML statements, theSELECT statement is the one that is executed most often in a real application because records are usually read more often than they are changed. A SELECT statement can also exist as a subquery in an UPDATE, INSERT, or DELETE statement--;see Chapter 7, "Accessing Personal Oracle7 with SQL*Plus."
The SELECT statement is a tremendously powerful tool, and its syntax is complicated because of the many ways you can combine tables, columns, functions, and operators into legal statements. Therefore, instead of looking at the full syntax of the SELECT statement, this section starts with some basic examples.
At a minimum, a SELECT statement contains the following two elements:
A simple SELECT statement--;a query that retrieves only the Customer ID from the Customer table--;is shown below:
SQL> select Customer_ID
2 from Customer;
CUSTOMER_ID
-----------
1001
1002
1003
1004
1005
1006
1007
1008
1009
6101
6102
6103
6104
6105
2222
15 rows selected.
If you want to retrieve both the Customer ID and the customer's last name, simply list the columns in the desired order:
SQL> select Customer_ID, Last_Name
2 from Customer;
CUSTOMER_ID LAST_NAME
----------- ------------------------------
1001 Johnson
1002 Martinez
1003 Smyth
1004 Richardson
1005 Horace
1006 Pareski
1007 McClintock
1008 Moran
1009 Chen
6101 Sorrel
6102 Fleming
6103 Kramden
6104 Jensen
6105 Hernandez
2222 Fray
15 rows selected.
If you want to retrieve all columns in a table, you can use a SQL shortcut--;the *:
SQL> select *
2 from Customer;
CUSTOMER_ID LAST_NAME FIRST_NAME SALUT
----------- ------------------------------ ------------------------------ -----
STREET_ADDRESS CITY
------------------------------------------------------------ ------------------
ZIPCODE WORK_TELEP HOME_TELEP FAX_NUMBER EARLIEST_ LATEST_TI LOC CREATED_D
--------- ---------- ---------- ---------- --------- --------- --- ---------
CREATED_BY MODIFIED_ MODIFIED_BY
------------------------------ --------- ------------------------------
1001 Johnson Samuel Mr.
1922 Bedford Blvd. Santa Margherina
91010 7145559876 7145550123
1002 Martinez Steve Mr.
9303 Channel Drive. Williamshire
12912 6025559133 6025553811 6025553833
1003 Smyth Julie Ms.
39121 Pillar Ave. Portsmith
03991 5035553843 5035551283
If the select list contains multiple columns, the columns must be separated by commas. The select list can also contain valid expressions that may or may not contain columns. In addition, you can use a column more than once in a select list. The following query is completely valid:
SQL> select Customer_ID, Customer_ID
2 from Customer;
CUSTOMER_ID CUSTOMER_ID
----------- -----------
1001 1001
1002 1002
1003 1003
1004 1004
1005 1005
1006 1006
1007 1007
1008 1008
1009 1009
6101 6101
6102 6102
6103 6103
6104 6104
6105 6105
2222 2222
15 rows selected.
NoteYou can perform arithmetic computations by selecting the problem from a utility table named DUEL, for example:
select 3.14159*20 from duel;
which returns
62.8318
The results returned by every SELECT statement constitute a temporary table. Each retrieved record is a row in this temporary table, and each element of the select list is a column. If a query doesn't return any records, the temporary table can be thought of as empty. This behavior is a fundamental principle of the relational model.
Along with specifying columns, you can also specify expressions in the select list. Expressions fall into the same datatypes as columns: character, numeric, and date. Through the use of operators, built-in functions, and constants, you can construct complex expressions to meet the needs of your application.
Keep in mind that Oracle treats each element in the select list as a separate column, even if that expression references multiple columns.
The arithmetic operators used in SQL are the same as those used in C:
Description Operator
Addition + Subtraction - Multiplication * Division /
In this example, the Product table stores the current retail value of a product and the year that the product was manufactured. A user wants a list of products in which the current used value is depreciated by 10 percent:
SQL> select Product_ID, Description, Current_Used_Value*0.90
2 from Product
3 order by Product_ID;
PRODUCT_ID
------------
DESCRIPTION
---------------------------------------------------------------------
CURRENT_USED_VALUE*0.90
-----------------------
A2001
AMPLIFIER, 100W PER CHANNEL
225
A504
AMP 300W PER CHAN
351
A509
AMP 600 WATTS PER CHANNEL
607.5
A903
AMP, PROFESSIONAL 800W RMS PER CHANNEL
742.5
B311
PRODUCT_ID
------------
DESCRIPTION
---------------------------------------------------------------------
CURRENT_USED_VALUE*0.90
-----------------------
Pre-amp, 120 W per channel
108
B384
PREAMP, 460 W/RMS
432
B801
PRE AMPLIFIER 150 WATTS/CHANNEL
225
TipInstead of trying to remember the precedence rules for arithmetic operators used in SQL statements, you should always use parentheses if you are uncertain about the correct way to evaluate an expression.
One of the most important string operators in SQL is the concatenation operator ||. SQL syntax requires that string constants be enclosed in single quotes. This operator enables you to concatenate two or more strings, as in the following code:
SQL> select City || ', ' || State 2 from Customer; CITY||','||STATE ---------------------------------- Santa Margherina, CA Williamshire, AZ Portsmith, OR Boston, MA Chicago, IL Nome, AZ Redlawn, WA Johnson, MI
Here are several reasons why you would want to concatenate strings:
select Salutation || ' ' || Last Name
from Customer
order by Name;
You can use the concatenation operator with more than two strings, as shown here:
SQL> select Salutation || ' ' || First_Name || ' ' || Last_Name 2 from Customer; SALUTATION||''||FIRST_NAME||''||LAST_NAME ------------------------------------------------------------------- Mr. Samuel Johnson Mr. Steve Martinez Ms. Julie Smyth Mrs. Mary Richardson Ms. Michelle Horace Ms. Monica Pareski Mr. Harvey McClintock Ms. Sarah Moran Mrs. Laura Chen
Oracle provides a rich set of built-in functions that you can be use to manipulate and convert different types of data. These functions can be categorized as Character functions
Number functions
Date functions
Conversion functions
Group functions
Miscellaneous functions
You'll find explanations of many of these functions in Chapter 11, "Manipulating
Strings," Chapter 12, "Dealing with Dates," and Chapter 13, "Handling
Numbers."
You usually don't want to retrieve all the rows in a table, particularly if the table has many rows. SQL provides a WHERE clause in which you specify the criteria to be used for retrieving records.
A WHERE clause consists of one or more conditions that must be satisfied before a row is retrieved by the query. For example, Tesla Corp., a major consumer electronics manufacturer, has been assigned TES801 as a Manufacturer ID in the repair store database. If you want a list of the products produced by Tesla, you would specify Tesla's Manufacturer ID in the WHERE clause, as shown here:
SQL> select Product_ID, Description, Date_of_Manufacture 2 from Product 3 where 4 Manufacturer_ID = 'TES801'; PRODUCT_ID ------------ DESCRIPTION ----------------------------------------------------------------------------- DATE_OF_M --------- A2001 AMPLIFIER, 100W PER CHANNEL 01-APR-87 A903 AMP, PROFESSIONAL 800W RMS PER CHANNEL 01-APR-92 B901 Preamplifier, 200 W PER CHANNEL 01-APR-92 B311 Pre-amp, 120 W per channel 01-APR-92 B384 PRODUCT_ID ------------ DESCRIPTION ----------------------------------------------------------------------------- DATE_OF_M --------- PREAMP, 460 W/RMS 01-APR-93 TR901 Tuner 01-JAN-91 6 rows selected.
You can use the keywords AND and OR to combine multiple conditions that need to be satisfied in a query. For example, to see the Seny products whose descriptions include the phrase CD, you would specify both conditions in the WHERE clause:
SQL> select Product_ID, Description, Date_of_Manufacture 2 from Product 3 where 4 Manufacturer_ID = 'SEN101' and 5 Description like '%CD%'; PRODUCT_ID ------------ DESCRIPTION -------------------------------------------------------------- DATE_OF_M --------- C3002 JUKEBOX, CD - 100 DISK CAPACITY 01-APR-94
Notice the word LIKE in the fourth line of the preceding example. This operator is one of SQL's most powerful tools. The basic syntax for using the LIKE operator is
column_name LIKE 'pattern'
where column_name is a valid column in the table referenced in the FROM clause and pattern is a string pattern for which you are searching. ***Production: Please make sure the underscore character shows up in the paragraph below (a mono single underscore). Thanks.*** The % serves as a wildcard in this context; it is the equivalent of zero or more characters. The _ (underscore) is used to signify a placeholder of any single character. ***Begin Note***
NoteIn the examples that follow, the keyword NULL is all uppercase. I've used uppercase to emphasize the word, but it isn't mandatory; you can use whatever case you choose with any of the Oracle SQL reserved words.
The ORDER BY clause designates which columns should be used to order the rows that are returned by the query. The ORDER BY clause is optional, but remember this: The order in which rows are returned by a query is always arbitrary (whether the table has been indexed or not). Therefore, you'll usually want to specify an ORDER BY clause in a SELECT statement.
For example, you might want to retrieve products that are ordered first by the Manufacturer ID and then by Product ID, as shown:
SQL> select Manufacturer_ID, Product_ID 2 from Product 3 where 4 Manufacturer_ID like 'TES%' 5 order by Manufacturer_ID, Product_ID; MANUFAC PRODUCT_ID ------- ------------ TES801 A2001 TES801 A903 TES801 B311 TES801 B384 TES801 B901 TES801 TR901 6 rows selected.
NoteYou can specify columns in an ORDER BY clause even if they are not selected from the table, for example:
Select Last NAme
From Customer
Order by Customer ID
You can also specify columns in the ORDER BY clause whether or not the column is part of an index on the table.
By default, Oracle orders the rows in ascending order. To order the rows in descending order, you must add the keyword DESC (for descending) after the column name. You can specify ascending columns and descending columns in the same ORDER BY clause as shown in this example:
SQL> select Product_ID, Manufacturer_ID 2 from Product 3 order by Manufacturer_ID DESC, Product_ID; PRODUCT_ID MANUFAC ------------ ------- fA2001 TES801 A903 TES801 B311 TES801 B384 TES801 B901 TES801 TR901 TES801 A504 SEN101 A509 SEN101 B801 SEN101 B9310 SEN101 C3002 SEN101 D301 SEN101 C2002 MIT501 C2005 MIT501 14 rows selected.
If you want to know how many rows in a table satisfy the specified criteria, but you really don't need to retrieve the rows themselves, you can use the COUNT function. COUNT returns a single row that reports the number of rows that satisfy the specified criteria. Here's an example:
SQL> select count(*)
2 from Product
3 where
4 Description like '%CD%';
COUNT(*)
---------
3
NoteCOUNT is a group function (which you can read about in greater depth in Chapter 15, "More Sophisticated Queries"). The asterisk instructs Oracle to return all rows that satisfy the criteria. Instead of the asterisk, you can specify a column name, but if you do, Oracle returns only those rows where the specified column name has been assigned a value (in other words, is not null).
A subquery is defined as a SELECT statement that appears in some other DML statement--;another SELECT statement, an UPDATE statement, a DELETE statement, or an INSERT statement.
In a SELECT statement, a subquery is part of a condition in the WHERE clause. The following code selects the Product ID and description for products whose current used value is less than or equal to the average current used value for all products:
select Product_ID, Description from Product where Current_Used_Value <= (select avg(Current_Used_Value) from Product)
You should be aware of several things when you use subqueries:
As you have already seen, Personal Oracle7 has two tools for creating a new table: Object Manager and SQL*Plus. Object Manager is easy and intuitive, but doesn't offer the flexibility of SQL*Plus. An example of this flexibility is the use of the CREATE TABLE statement in conjunction with the SELECT statement, as explained in the following paragraph.
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 and contains several rows--;say 100,000--;but you want only a subset of those rows for your experiment. Specifically, you are interested only in 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:
SQL> create table Customer_Subset 2 as 3 select * 4 from Customer 5 where 6 State = 'CA'; Table created.
The syntax is
CREATE TABLE new_table_name AS select_stmt
where new_table_name is the name of the table to be created and 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--;all the same column definitions--;but none of the data, you can use this 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.
When you specify a complex expression in a select list, you can document what the expression represents by assigning an alias to it. The syntax for a select list is
expression_name1 [ [AS] alias_name1],..., expression_nameN [ [AS] alias_nameN]
where expression_name is an expression that references zero or more column names and alias_name is an alias used to reference expression_name in other parts of the SELECT statement. The keyword AS is optional.
As an example, Helmholtz, the owner of our fictitious consumer electronics repair store, Frayed Wires, wants to know the percentage of depreciation of each product by dividing its current used value by its initial retail value. Here's how it's done:
SQL> select Product_ID, 2 (Current_Used_Value/Initial_Retail_Value)*100 Pct_Depreciation 3 from Product 4 order by Product_ID; PRODUCT_ID PCT_DEPRECIATION ------------ ---------------- A2001 71.428571 A504 66.666667 A509 79.411765 A903 78.571429 B311 64.864865 B384 64.864865 B801 73.529412 B901 88.372093 B9310 76.744186 C2002 34 C2005 71.111111 C3002 86.046512 D301 TR901 14 rows selected.
By assigning the alias Pct Depression to the expression Current Used Value/Iniytial Retail Value*100, you gain two benefits:
One major difference between a newer relational database management system (RDMS) and the older database management system (DBMS) technology is the concept of the null value. In nonrelational database systems, a special value indicates the absence of a value in a character or numeric field.
In a relational database, a null value for a column represents different things:
If you want to retrieve records from a table where a specific column value is not defined, you can specify the criterion in the WHERE clause. Here is a query that retrieves customers for whom a fax number has not been assigned:
SQL> select Customer_ID, Last_Name, First_Name
2 from Customer
3 where
4 Fax_Number is NULL;
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ------------------------------
6101 Sorrel James
6102 Fleming Harry
6103 Kramden Ralph
6104 Jensen Rachel
6105 Hernandez Ruby
2222 Fray
6 rows selected.
Understanding the reason a value is NULL is important. In the case of the customer's fax number, the value may be NULL because the customer doesn't own a fax machine. However, the Fax_Number might also be NULL because, even though the customer does own a fax machine, you have not yet obtained that information. To distinguish between these two situations, you would need to add another column to the Customer table to indicate whether the customer does or doesn't own a fax machine. In that case, you could query the Customer table for customers who do own fax machines but whose fax number is not known.
NoteThis discussion of the meaning of a NULL value may sound like hair-splitting, but it really isn't. You need a thorough understanding of the meaning of your organization's data to be able to maximize its value.
You can also use the NOT operator to retrieve rows whose column values are not NULL. For example, you can count the number of customers with known fax numbers with this query:
select count(*) from Customer where Fax_Number is not NULL;
You should be aware of how NULL values are processed by arithmetic operations. Here's an example that shows how a NULL value differs from a value of O. Suppose the table named Intelligence has two columns--;Last_Name and IQ:
SQL> select Last_Name, IQ 2 from Intelligence; LAST_NAME IQ ------------------------- --------- SMITH 100 GORDON 125 JONES 150 WILSON RICHARDS
It so happens that IQ is NULL for Wilson and Richards. To see the average IQ for the records in the Intelligence table, enter the following query:
SQL> select avg(IQ) from Intelligence;
AVG(IQ)
---------
125
As you can see, the rows containing the NULL value for IQ were not used to compute the average IQ. Oracle computed the average IQ by calculating (100+125+150)/3. If you change the NULL values to 0, the results will be different.
SQL> update Intelligence
2 set IQ = 0
3 where
4 IQ is NULL;
2 rows updated.
SQL> select Last_Name, IQ
2 from Intelligence;
LAST_NAME IQ
------------------------- ---------
SMITH 100
GORDON 125
JONES 150
WILSON 0
RICHARDS 0
SQL> select avg(IQ) from Intelligence;
AVG(IQ)
---------
75
You've already seen an example of the use of the LIKE operator. Oracle users rely on the LIKE operator to search through a table when they're not sure of the exact spelling for the item they're interested in finding.
The manager of Frayed Wires, Jean Smith, wants to find all the products in the Product table that are preamplifiers. Using the Description column, here's how she constructs a SELECT statement to find these products:
SQL> select Product_ID, Manufacturer_ID, Description 2 from Product 3 where 4 Description like '%Preamplifier%'; PRODUCT_ID MANUFAC ------------ ------- DESCRIPTION ---------------------------------------------------------------------------- B901 TES801 Preamplifier, 200 W PER CHANNEL
Jean knows that the Product table contains more than one preamp. She realizes that the reason the query returned only one product is that the descriptions for the other preamps do not contain the phrase Preamplifier; some of the descriptions are in uppercase, and others contain only the abbreviation Preamp or Pre-amp. After pondering this problem for a while, Jean submits this query to Oracle:
SQL> select Product_ID, Manufacturer_ID, Description 2 from Product 3 where 4 upper(Description) like '%PRE%AMP%'; PRODUCT_ID MANUFAC ------------ ------- DESCRIPTION -------------------------------------------------------------------------- B901 TES801 Preamplifier, 200 W PER CHANNEL B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNEL B311 TES801 Pre-amp, 120 W per channel B9310 SEN101 Pre amp, 250 W/channel B384 TES801 PREAMP, 460 W/RMS
Jean was correct. Instead of returning only one record, the query now returns five records. A closer look at the WHERE clause follows.
By applying the UPPER function to the Description column, you are able to compare the contents of the column without worrying about upper- or lowercase. The use of the additional wildcard symbol % also helps to broaden the retrieval of records. The % between PRE and AMP means that zero or more characters can exist between PRE and AMP, thereby retrieving: PRE-AMP
PREAMP
PRE AMP
Also, by using the phrase AMP rather than AMPLIFIER, Jean is able to retrieve products where the description is abbreviated to AMP. ***Production: Please make sure the mono single underscore shows up twice in the paragraph below. Thanks.*** In addition to %, you can use _ (underscore) as a placeholder in a string when you want to indicate the presence of a single character between two other strings but you don't care (or know) what that character is. For instance, if Jean knew that all of the preamps were described as Pre:Amp or Pre-Amp or Pre Amp or Pre.Amp, she could modify her query to use the _, as shown here:
SQL> select Product_ID, Manufacturer_ID, Description 2 from Product 3 where 4 upper(Description) like '%PRE_AMP%'; PRODUCT_ID MANUFAC ------------ ------- DESCRIPTION ------------------------------------------------------------------------- B801 SEN101 PRE AMPLIFIER 150 WATTS/CHANNEL B311 TES801 Pre-amp, 120 W per channel B9310 SEN101 Pre amp, 250 W/channel
Sometimes you really want to search for a string that contains the % character. In this case, you can inform Oracle that you are using an escape character in the specified string, as in this example:
select Product_ID, Manufacturer_ID, Description from Product where
upper(Description) like '%POWER SUPPLY%50\% SETTING%' escape '\';
This query uses the backslash character (\) to tell Oracle that the % that follows the \ should be interpreted literally. You can use the same method when you want to search for an underscore (_), rather than have it represent any single character.
Here are some suggestions for using the LIKE operator in your searches:
select Product ID, manufacturer ID,
Description
from Product
where
upper(Description) like'TUNER%';
select Product ID, manufacturer ID, Description
from Product
where
upper(Description)like'%POWER SUPPLY';
Earlier in this chapter, I explained that a SELECT statement can be structured in more than one way to obtain the same result. The BETWEEN operator is a good example of this flexibility.
The BETWEEN operator is quite flexible; it works with numeric, string, and date values. For example, to retrieve products whose current retail value is between $200 and $250, you would use:
select Product_ID, Manufacturer_ID, Description from Product where Current_Retail_Value between 200 and 250;
The preceding query is really the same as
select Product_ID, Manufacturer_ID, Description from Product where Current_Retail_Value >= 200 and Current_Retail_Value <= 250;
As you can see, the BETWEEN operator is the equivalent of two conditions that are joined with an AND. When used appropriately, the BETWEEN operator simplifies a query. Of course, you can also combine the BETWEEN conditions with AND and OR operators. Here's an example of a SELECT statement that will retrieve a list of products whose value falls into two ranges--;$200 to $250 and $700 to $750.
select Product_ID, Manufacturer_ID, Description from Product where Current_Used_Value between 200 and 250 or Current_Used_Value between 700 and 750;
If you wanted to assemble a list of repair depots in which the first letter of the depot name was in the range A through F, you could structure the query this way:
select Depot_ID, Company_Name from Depot where Company_Name between 'A' and 'F';
Another operator that compares the value of a column or expression with a list of possible values is the IN operator. The syntax for the IN operator is
expression IN (expression1, expression2, ... expressionN)
where expression is a valid SQL expression and expression1 through expressionN is a list of valid SQL expressions.
The IN operator returns a Boolean value: TRUE if the expression is equal to one of the values in the expression list
FALSE if the expression is not equal to one of the values in the expression list
As an example of how to use the IN operator, suppose you want to retrieve only customers whose state is one of several values. If you have a long list of possible values to check, the IN operator saves you some typing and saves Oracle's SQL statement parser some processing time. Here's the code:
SQL> select Last_Name, First_Name, State
2 from Customer
3 where
4 State IN ('CA', 'WA', 'AZ');
LAST_NAME FIRST_NAME ST
------------------------------ ------------------------------ --
Johnson Samuel CA
Martinez Steve AZ
Pareski Monica AZ
McClintock Harvey WA
The alternative to the IN operator is this:
SQL> select Last_Name, First_Name, State 2 from Customer 3 where 4 State = 'CA' or 5 State = 'WA' or 6 State = 'AZ'; LAST_NAME FIRST_NAME ST ------------------------------ ------------------------------ -- Johnson Samuel CA Martinez Steve AZ Pareski Monica AZ McClintock Harvey WA
You can combine the keyword NOT with the IN operator so that a condition is true if an expression is not equal to any of the expressions in the expression list, for example:
SQL> select Last_Name, First_Name, State
2 from Customer
3 where
4 State NOT IN ('CA', 'WA', 'AZ');
LAST_NAME FIRST_NAME ST
------------------------------ ------------------------------ --
Smyth Julie OR
Richardson Mary MA
Horace Michelle IL
Moran Sarah MI
Chen Laura MI
If you choose not to use the NOT IN operator, your WHERE clause would look like this:
SQL> select Last_Name, First_Name, State 2 from Customer 3 where 4 State != 'CA' and 5 State != 'WA' and 6 State != 'AZ'; LAST_NAME FIRST_NAME ST ------------------------------ ------------------------------ -- Smyth Julie OR Richardson Mary MA Horace Michelle IL Moran Sarah MI Chen Laura MI
Chapter 6 deals with the following fundamental concepts of SQL: