- Working with Query Expressions
- Placing Conditions on Queries
- Learning How to Use Operators
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Placing Conditions on Queries
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
Syntax
NAME = 'BROWN'
To find everyone in your organization who worked more than 100 hours last month, your condition would be
Syntax
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 syntax of the WHERE clause is
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.
Output
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE ----------- --------- ------------ ----------- -------- TREK 2300 22.5 CARBON FIBER 3500 RACING BURLEY 22 STEEL 2000 TANDEM GIANT 19 STEEL 1500 COMMUTER FUJI 20 STEEL 500 TOURING SPECIALIZED 16 STEEL 100 MOUNTAIN CANNONDALE 22.5 ALUMINUM 3000 RACING 6 rows selected.
If you wanted a particular bike, you could type
Input
SQL> SELECT * 2 FROM BIKES 3 WHERE NAME = 'BURLEY';
which would yield only one record:
Output
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE ------------- --------- ----------- ----------- ------ BURLEY 22 STEEL 2000 TANDEM 1 rows selected.
These simple examples show how you can place a condition on the data that you want to retrieve.