loading..
Русский    English
19:59

WHERE clause

The WHERE clause restricts a result set to those rows which satisfy the predicate in this clause. Let us suppose that there are such conformities between a memory size of the personal computer and a type of used operational system (conditionally):

RAM < 64 W95
RAM >=64 и < 128 W98
RAM >= 128 W2k

If we shall want to choose computers in accordance with the type of OS (note that such column isn't in PC table) it is possible to write the following query:

Console
Execute
  1. SELECT *
  2. FROM PC
  3. WHERE CASE
  4. WHEN ram < 64
  5. THEN 'W95'
  6. WHEN ram < 128
  7. THEN 'W98'
  8. ELSE 'W2k'
  9. END ='W98'

Here we choose the models fit to the operational system W98. The result of the execution of this query is given below.

code model speed ram hd cd price
1 1232 500 64 5 12x 600.0
3 1233 500 64 5 12x 600.0
8 1232 450 64 8 24x 350.0

It can appear more useful than it seems at first sight if you'll keep in mind confidentiality of the information. For example, on the client side it is possible to issue queries which will operate with such categories as high-, mid-, or low-paid expert. Real criteria (salaries) will be hidden, say, in stored procedure which will take something like character string "average"  as an input parameter.

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100