tailieunhanh - Database Systems - Part 14

ANSI standard SQL allows the use of special operators in conjunction with the WHERE clause. These special operators (see Day 17, page26) include: | COP 4710: Database Systems Spring 2004 Introduction to SQL – Part 2 BÀI 14, 2 ngày School of Electrical Engineering and Computer Science University of Central Florida Instructor : Mark Llewellyn markl@ CC1 211, 823-2790 Phd, MS, Under An Example Database(+) Special Operators in SQL ANSI standard SQL allows the use of special operators in conjunction with the WHERE clause. These special operators (see Day 17, page26) include: BETWEEN – Used to check whether an attribute value is within a range. IS NULL – Used to determine if an attribute value is null. LIKE – Used to match an attribute value to a string pattern. Many wildcard options are available. IN – Used to determine if an attribute value is within a list of values. EXISTS – Used to determine if a subquery returns an empty set or not. The BETWEEN Special Operator(+) Suppose that we want to see a listing for all products whose prices are between $50 and $100. The BETWEEN . | COP 4710: Database Systems Spring 2004 Introduction to SQL – Part 2 BÀI 14, 2 ngày School of Electrical Engineering and Computer Science University of Central Florida Instructor : Mark Llewellyn markl@ CC1 211, 823-2790 Phd, MS, Under An Example Database(+) Special Operators in SQL ANSI standard SQL allows the use of special operators in conjunction with the WHERE clause. These special operators (see Day 17, page26) include: BETWEEN – Used to check whether an attribute value is within a range. IS NULL – Used to determine if an attribute value is null. LIKE – Used to match an attribute value to a string pattern. Many wildcard options are available. IN – Used to determine if an attribute value is within a list of values. EXISTS – Used to determine if a subquery returns an empty set or not. The BETWEEN Special Operator(+) Suppose that we want to see a listing for all products whose prices are between $50 and $100. The BETWEEN operator can be used for this query expression. If your RDBMS does not support BETWEEN you would need to express this query as: SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN AND ; SELECT * FROM PRODUCT WHERE P_PRICE > AND P_PRICE < ; The IS NULL Special Operator(+) Suppose that we want to see a listing for all products that do not currently have a vendor assigned, ., V_CODE = null. The null entries could be found with the following query expression. NOTE: SQL uses a special operator for testing for nulls. You cannot use a condition such as V_CODE = NULL. The reason is that NULL is technically not a “value”, but a special property of an attribute that represents precisely the absence of any value at all. SELECT P_CODE, P_DESCRIPT, V_CODE FROM PRODUCT WHERE V_CODE IS NULL; The LIKE Special Operator(+) The LIKE special operator is used in conjunction with wildcards to find patterns within string attributes. Standard SQL allows you to use the percent sign (%) and .