1 / 31

Chapter 4 : Adding Power to Queries

Chapter 4 : Adding Power to Queries. LOGICAL OPERATORS (AND, OR, AND NOT). AND : Joins two or more conditions, and returns results only when all of the conditions are true. OR : Joins two or more conditions, and returns results when any of the conditions are true.

hedy
Download Presentation

Chapter 4 : Adding Power to Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 : Adding Power to Queries

  2. LOGICAL OPERATORS (AND, OR, AND NOT) AND : Joins two or more conditions, and returns results only when all of the conditions are true. OR : Joins two or more conditions, and returns results when any of the conditions are true. NOT : Negates the expression that follows it.

  3. AND OPERATOR SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_gender "Gender" FROM employee WHERE emp_gender = 'F' AND emp_last_name > ‘E' ORDER BY emp_last_name; Last Name First Name Gender ---------- ------------- ------ Joyner Suzanne F Markis Marcia F Prescott Sherri F

  4. OR OPERATOR SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_gender "Gender" FROM employee WHERE emp_gender = 'F' OR emp_last_name > 'M' ORDER BY emp_last_name; Last Name First Name Gender --------------- --------------- ------ Joyner Suzanne F Markis Marcia F Prescott Sherri F Zhu Waiman M

  5. NOT OPERATOR SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_dpt_number "Dept" FROM employee WHERE NOT emp_dpt_number = 7 ORDER BY emp_last_name; Last Name First Name Dept --------------- --------------- ----- Amin Hyder 3 Bordoloi Bijoy 1 Joyner Suzanne 3 Markis Marcia 3

  6. Combining OR and AND Operators When the AND operator is combined with OR, the Oracle server will evaluate the condition connected by the AND first before any conditions connected with OR. Parenthesis must be used to force an order of operation.

  7. Combining OR and AND Operators SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_dpt_number "Dept", emp_gender "Gender" FROM employee WHERE emp_last_name > 'E' AND emp_gender = 'F‘ OR emp_dpt_number = 1 ORDER BY emp_last_name; Last Name First Name Dept Gender --------------- -------------- ----- ------ Bordoloi Bijoy 1 M Joyner Suzanne 3 F Markis Marcia 3 F Prescott Sherri 7 F

  8. Combining OR and AND Operators SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_dpt_number "Dept", emp_gender "Gender" FROM employee WHERE emp_last_name > 'E' AND (emp_gender = 'F' OR emp_dpt_number = 1) ORDER BY emp_last_name; Last Name First Name Dept Gender --------------- --------------- ----- ------ Joyner Suzanne 3 F Markis Marcia 3 F Prescott Sherri 7 F

  9. LISTS (IN AND NOT IN) There are two operators that are designed for testing to determine if data stored in a table column is either in or not in a list or set of values. These are the IN and NOT IN operators. These operators greatly simplify the task of writing queries that might otherwise require a large number of either OR logical operators or an unwieldy use of the NOT logical operator.

  10. Using IN Operator SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary "Salary" FROM employee WHERE emp_salary = 43000 OR emp_salary = 30000 OR emp_salary = 25000 ORDER BY emp_salary; Last Name First Name Salary --------------- --------------- ----------- Markis Marcia $25,000.00 Amin Hyder $25,000.00 Prescott Sherri $25,000.00 Bock Douglas $30,000.00 Joyner Suzanne $43,000.00 Zhu Waiman $43,000.00 6 rows selected.

  11. Using IN Operator SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary "Salary" FROM employee WHERE emp_salary IN (43000, 30000, 25000) ORDER BY emp_salary; Last Name First Name Salary --------------- --------------- ----------- Markis Marcia $25,000.00 Amin Hyder $25,000.00 Prescott Sherri $25,000.00 Bock Douglas $30,000.00 Joyner Suzanne $43,000.00 Zhu Waiman $43,000.00 6 rows selected.

  12. Using IN Operator SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_city "City" FROM employee WHERE emp_city IN ('Marina', 'Edwardsville', 'St. Louis') ORDER BY emp_city; Last Name First Name City --------------- --------------- ------------- Bordoloi Bijoy Edwardsville Prescott Sherri Edwardsville Joyner Suzanne Marina Amin Hyder Marina Zhu Waiman St. Louis Bock Douglas St. Louis

  13. Using the NOT IN Operator NOT can precede IN to form negative. To get the list of employees who did not earn the three salary figures listed above SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary "Salary" FROM employee WHERE emp_salary NOT IN (43000, 30000, 25000) ORDER BY emp_salary; Last Name First Name Salary --------------- --------------- ----------- Joshi Dinesh $38,000.00 Bordoloi Bijoy $55,000.00

  14. RANGES (BETWEEN AND NOT BETWEEN) Oracle provides two operators, BETWEEN and NOT BETWEEN that can simply the range of values in a query. This eliminates the need to use a more complex WHERE clause involving the use of the AND logical operator.

  15. Using the BETWEEN Operator The following query uses the AND logical operator. SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary "Salary" FROM employee WHERE emp_salary >= 25000 AND emp_salary <= 40000 ORDER BY emp_salary; Last Name First Name Salary -------------- --------------- ----------- Markis Marcia $25,000.00 Amin Hyder $25,000.00 Prescott Sherri $25,000.00 Bock Douglas $30,000.00 Joshi Dinesh $38,000.00

  16. Using the BETWEEN Operator The query can be rewritten using the BETWEEN operator. SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary "Salary" FROM employee WHERE emp_salary BETWEEN 25000 AND 40000 ORDER BY emp_salary; Last Name First Name Salary --------------- --------------- ----------- Markis Marcia $25,000.00 Amin Hyder $25,000.00 Prescott Sherri $25,000.00 Bock Douglas $30,000.00 Joshi Dinesh $38,000.00

  17. Using the NOT BETWEEN Operator SELECT emp_last_name "Last Name", emp_salary "Salary" FROM employee WHERE emp_salary NOT BETWEEN 28000 AND 50000 ORDER BY emp_salary; Last Name Salary --------------- ----------- Markis $25,000.00 Amin $25,000.00 Prescott $25,000.00 Bordoloi $55,000.00

  18. LIKE AND NOT LIKE The LIKE and NOT LIKE operators can be used to search for data rows containing incomplete or partial character strings within a data column. The next query searches the employee table for employee names that begin with the characters 'Bo'. The search is case-sensitive meaning that 'Bo' is not equivalent to 'BO'.

  19. LIKE AND NOT LIKE SELECT emp_last_name "Last Name", emp_first_name "First Name" FROM employee WHERE emp_last_name LIKE 'Bo%'; Last Name First Name ------------- ------------- Bordoloi Bijoy Bock Douglas

  20. LIKE AND NOT LIKE Wild card Meaning % (percent)any string of zero or more characters _ (underscore) any single character [ ] (brackets) any single character within a specified range such as 'a' to 'd', inclusive [a-d] or a set of characters such as [aeiouy] [^] (not brackets) any single character not in the specified range or set. (e.g., [^a-f] ) Oracle supports only the first two, SQL-Server supports all four. SQL Server and Sybase supports all four.

  21. MORE EXAMPLES LIKE '%inger' will search for every name that ends with 'inger' (Ringer, Stringer). LIKE '%en%' will search for every name that has the letters 'en' in the name (Bennet, Green, McBadden). LIKE '_heryl' will search for every six-letter name ending with 'heryl' (Cheryl). Notice how this is different than '%heryl' which would return names that are six characters or more.

  22. MORE EXAMPLES LIKE '[CK]ars[eo]n' will search for every six-letter name that begins with a 'C' or 'K' and has the letter 'e' or 'o' between 'ars' and 'n' (e.g., 'Carsen,' 'Karsen,' 'Carson,' and 'Karson'. LIKE '[M-Z]inger' will search for all the names ending with 'inger' that begin with any single letter 'M' thru 'Z' (Ringer). LIKE 'M[^c]%' will search for all the names that begin with 'M' not having 'c' as the second letter.

  23. MORE EXAMPLES The SELECT statement shown below generates a result table that includes all DISTINCT rows where the employee social security number in the assignment table ends with the numbers 555. SELECT DISTINCT work_emp_ssn "Emp SSN" FROM assignment WHERE work_emp_ssn LIKE '%555'; Emp SSN ------------ 999555555

  24. UNKNOWN VALUES (IS NULL AND IS NOT NULL) NULL value is not synonymous with "zero" (numerical values) or "blank" (character values). NULL values allow users to distinguish between a deliberate entry of zero/blank and a non-entry of data. SELECT * FROM assignment WHERE work_hours IS NULL; WORK_EMP_ WORK_PRO_NUMBER WORK_HOURS --------- --------------- ---------- 999444444 1 999666666 20

  25. MORE EXAMPLES SELECT * FROM assignment WHERE work_hours = 0; no rows selected The query did not return a result table because none of the rows in the assignment table have a zero value for work_hours. Thus, you can see that zero (0) is a value, not an "unknown value."

  26. MORE EXAMPLES The NOT NULL condition can also be tested. SELECT * FROM assignment WHERE work_hours IS NOT NULL; 15 rows selected. The result table contains all the rows where work_hours column contains a value.

  27. EXPRESSIONS IN SELECT CLAUSES An expression is formed by combining a column name or constant with an arithmetic operator. The arithmetic operators used in SQL are

  28. MORE EXAMPLES SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary/12 "Monthly Salary" FROM employee WHERE emp_salary/12 > 3500 ORDER BY emp_last_name; Last Name First Name Monthly Salary --------------- --------------- -------------- Bordoloi Bijoy $4,583.33 Joyner Suzanne $3,583.33 Zhu Waiman $3,583.33 

  29. EXPRESSIONS • When expression is used in select with a column name it has no effect on the table’s underlying values. • The data contained in each column must be defined as int, smallint, tinyint, float, money, and smallmoney. • The result of an arithmetic operation on NULL is NULL.

  30. Examples of Operations on NullsTable: Compensation EMP# JOBCODE SALARY COMMISSION E10 SALES 12500 32090 E11 NULL 25000 8000 E12 SALES 44000 0 E13 SALES 44000 NULL E14 PROG 19500 NULL E15 CLERK NULL NULL

  31. Examples of Operations on Nulls List the employees whose total earnings exceed $30000.00. SELECT EMP# FROM COMPENSATION WHERE JOBCODE = “SALES” AND (SALARY + COMMISSION) > 30000 EMP# E10 E12 E13 is missing because?

More Related