Page 115
select Department_ID, Title_ID, Description from Course where upper(Description) like `%TOPICS';
Earlier in this lesson, 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 an inclusive operator and is quite flexible; it works with numeric, string, and date values. For example, suppose that the current cost of a unit at Flugle College is $250. Because the cost of a course is the sum of the unit cost and any additional fees,
the list of courses whose cost is between $760 and $800 can be derived from the query in Listing 5.22.
Listing 5.22. Using the BETWEEN operator.
SQL> select Title, Units*250 + Additional_Fees 2 from Course 3 where 4 (Units*250 + Additional_Fees) between 760 and 800; TITLE UNITS*250+ADDITIONAL_FEES ---------------------------------------- ------------------------- ADVANCED ARITHMETIC 760 INTRO TO PSYCHOLOGY 775 ABNORMAL PSYCHOLOGY 770 INTRO TO ECONOMICS 775 INTRO TO CIRCUIT THEORY 795 INTRO TO DYNAMICS 785 6 rows selected.
The preceding query is really the same as that shown in Listing 5.23.
Listing 5.23. Alternative to using the BETWEEN operator.
SQL> select Title, Units*250 + Additional_Fees 2 from Course 3 where 4 (Units*250 + Additional_Fees) >= 760 and
continues
Page 116
Listing 5.23. continued
5 (Units*250 + Additional_Fees) <= 800; TITLE UNITS*250+ADDITIONAL_FEES ---------------------------------------- ------------------------- ADVANCED ARITHMETIC 760 INTRO TO PSYCHOLOGY 775 ABNORMAL PSYCHOLOGY 770 INTRO TO ECONOMICS 775 INTRO TO CIRCUIT THEORY 795 INTRO TO DYNAMICS 785 6 rows selected.
As you can see, the BETWEEN operator is the equivalent of two conditions that are ANDed together. When used appropriately, the BETWEEN operator simplifies a query.
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)
The variables are defined as follows:
expression is a valid SQL expression.
expression1 through expressionN is a list of valid SQL expressions.
The IN operator returns a Boolean value that is either TRUE or FALSE:
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 those instructors in the math, anthropology, or psychology departments. 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. Listing 5.24 shows the code.
Page 117
Listing 5.24. Using the IN operator.
SQL> select Last_Name, First_Name, Department_ID 2 from Instructor 3 where 4 Department_ID in (`MATH','ANTHRO','PSYCH'); LAST_NAME FIRST_NAME DEPARTMENT_ID ------------------------ ------------------------ -------------------- HITCHCOCK BORIS ANTHRO DANIELS LAURA ANTHRO CHANG ROGER MATH JASON JERROLD PSYCH YOUNG ALLAN MATH NILAND MARTINA MATH BATES JOSEPH PSYCH 7 rows selected.
The alternative to the IN operator is shown in Listing 5.25.
Listing 5.25. Alternative to using the IN operator.
SQL> select Last_Name, First_Name, Department_ID 2 from Instructor 3 where 4 Department_ID = `MATH' or 5 Department_ID = `ANTHRO' or 6 Department_ID = `PSYCH'; LAST_NAME FIRST_NAME DEPARTMENT_ID ------------------------ ------------------------ -------------------- HITCHCOCK BORIS ANTHRO DANIELS LAURA ANTHRO CHANG ROGER MATH JASON JERROLD PSYCH YOUNG ALLAN MATH NILAND MARTINA MATH BATES JOSEPH PSYCH 7 rows selected.
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. See Listing 5.26.
Page 118
Listing 5.26. Using the NOT IN operator.
SQL> select Last_Name, First_Name, Department_ID 2 from Instructor 3 where 4 Department_ID not in (`MATH','ANTHRO','PSYCH'); LAST_NAME FIRST_NAME DEPARTMENT_ID ------------------------ ------------------------ -------------------- EDWARDS SAMANTHA BIO TORRES PETER HIST ANGELO ARTHUR PHILO RICHARDSON NANCY PHILO PARKER WILLIAM ECON CHERNOW BESS ENGL CHU STEVEN ENG WEISS ROBERTA BIO RESTON RAPHAEL HIST BILLINGS BENJAMIN PHILO 10 rows selected.
If you choose not to use the NOT IN operator, you must list each value that the expression or column should not equal, "tied" together with the and logical operator. Listing 5.27 shows an example.
Listing 5.27. Alternative to using the NOT IN operator.
SQL> select Last_Name, First_Name, Department_ID 2 from Instructor 3 where 4 Department_ID != `MATH' and 5 Department_ID != `ANTHRO' and 6 Department_ID != `PSYCH'; LAST_NAME FIRST_NAME DEPARTMENT_ID ------------------------ ------------------------ -------------------- EDWARDS SAMANTHA BIO TORRES PETER HIST ANGELO ARTHUR PHILO RICHARDSON NANCY PHILO PARKER WILLIAM ECON CHERNOW BESS ENGL CHU STEVEN ENG WEISS ROBERTA BIO RESTON RAPHAEL HIST BILLINGS BENJAMIN PHILO 10 rows selected.
Page 119
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 as follows:
expression_name1 [ [AS] alias_name1], ... , expression_nameN Â[ [AS] alias_nameN]
The variables are defined as follows:
expression_name is an expression that references zero or more column names.
alias_name is an alias used to reference expression_name in other parts of the SELECT statement.
Please note that the keyword AS is optional.
You can use the example in Listing 5.23 to illustrate how you can use an alias. To reiterate, the current cost of a course at Flugle College is $250 per unit plus any additional fees, which is expressed with the query in Listing 5.28.
Listing 5.28. Specifying an alias in a SELECT statement.
SQL> select Title, Units*250 + Additional_Fees as Course_Cost 2 from Course 3 where 4 Department_ID = `ECON'; TITLE COURSE_COST ---------------------------------------- ----------- INTRO TO ECONOMICS 775 MONETARY POLICY 1500 WORKSHOP ON MARX 750
By assigning the alias Course_Cost to the expression Units*250 + Additional_Fees, you gain two benefits:
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.