1 / 24

Chapter 3 The WHERE clause

ryo
Download Presentation

Chapter 3 The WHERE clause

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. 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

More Related