Chapter 3 The WHERE clause

1. Data WarehousingTarleton state universityMath 586Instructors: keithemmert, Sean perry, Micha Roberson, and Michael Schuckingcourse materials: oracle SQL By Example, 4th Edition by Alice RischerTSoftware: Oracle 11GTextbook resource website:http://www.oraclesqlbyexample.com/course Objectives:Understand and apply fundamental SQL functions, expressions, commands etc.A. Create/drop tables/databaseB. Query tablESC. Use aggregate functionsD. USE INNER/OUTER/LEFT/RIGHT JOINSE. Write queries with STRING, NUMERIC, CONVERSION, and date/time functions. CONDUCT QA and integrity checks.

2. Chapter 3 The WHERE clause • The WHERE clause, also called the predicate, provides the power to narrow down the scope of the data retrieved. • Comparison Operators

3. The equality and inequality operators • Equality, denoted by = Select first_name, last_name, phone from instructor where last_name = ‘Schorin’; • Inequality, denoted by != • Data types must be the same.

4. The Greater Than or Less Than Operators • >,<, >=,<= Select description, cost from course where cost >= 1195;

5. The between operator • Tests for a range of values. Select description, cost from course where cost between 1000 and 1100;

6. The in operator • Works with a list of value, separated by commas, contained within a set of parentheses. Select description, cost from course where cost in (1095, 1595);

7. The LIKE operator • Performs pattern matching using wild cards % and _ Select first_name, last_name, phone from instructor where last_name like ‘%S%’; Select first_name, last_name, phone from instructor where last_name like ‘_o%’;

8. The Not operator • All previously mentioned operators can be negated with the NOT comparison operator. Select phone from instructor where last_name not like ‘%S%’;

9. The Is null and is not null operators • Is Null and Is Not Null determines whether there is an unknown value in the data. Select description, prerequisite from course where prerequisite Is Null;

10. Logical Operators • Comparison operators can be combined with the help of the logical operators AND and OR Select description, cost from course where cost = 1095 or cost = 1195 and description LIKE ‘I%’; • AND always takes precedence over OR, but precedence can be changed with parentheses. Select description, cost from course where (cost = 1095 or cost = 1195) and description like ‘I%’;

11. Nulls and logical operators • SQL uses tri-value logic; this means a condition can evaluate to true, false, or unknown(null); • Null values cannot be compared so they cannot be returned for queries using non-null values with comparison operators.

12. And truth table OR TRUTH TABLE

13. NOT Truth table

14. Lab 3.1 Excercises • Go to page 111 in book • Answer and discuss answers in class.

15. Lab 3.2 The order by clause • Data is not stored in any particular order • Result sets are displayed in whatever order they are returned from the database • The ORDER BY clause to is used to order data any way you wish. • You can use ASC or DESC and the sequence number of the column instead of the name. Select course_no, description from course where prerequisite is NULL ORDER BY DESCRIPTION.

16. Distinct and order by • If the SELECT list contains DISTINCT, the column(s)the keyword pertains to must also be listed in the ORDER BY clause. SELECT distinct first_name, last_name from student where zip =‘10025’ order by first_name, last_name;

17. Nulls first and nulls last • The default sort order is to have nulls listed last. • This can be changed by using NULLS FIRST or NULLS LAST in the ORDER BY clause. Select DISTINCT cost from course order by cost nulls first;

18. Sorting Data using sql developer’s gui functionality • Double-click one of the columns in the column header to sort. • You can also use the SQL Developer Data tab, which allows you to retrieve data from a table without writing a SQL statement. • Sort and filters can be done using this tab.

19. Column alias • A column alias can be used in the SELECT list to give a column or an expression an alias. • You can order by an alias name. Select first_namefirst, first_name “First Name”, first_name as “First” From student Where zip = ‘10025’;

20. Comments in sql statements • Placing comments or remarks in a SQL statement is very useful for documenting purpose, thought, and ideas. • Very handy for when you have developed multiple statements saved in a script. • You must identify a comment with either a – or /* */ --This is a single line comment /*This is a multi-line comment */

21. Saving your sql statements • Click CTRL+S on the keyboard, the save icon, or go to FileSave to save the sql script . • To open the file, use CTRL+O, the open icon, or go to FileOpen.

22. Oracle error messages • You will inevitably make mistakes. Oracle returns an error number and an error message to inform you of any mistake. • Errors can be one or many and the error message is not always indicative of the problem. • The parser works from the front of the query and works backward; therefore, the first error message will be for the first error the parser encounters. • You can look up the Oracle error message in the Oracle Database Error Messages manual at Oracle.com or refer to Appendix G and Appendix H.

23. Lab 3.2: Exercises • Go to page 127 in book • Answer and discuss answers in class.

24. Chapter 3 complete! • Quiz will be given at the beginning of our next class