1 / 43

Chapter 8 Restricting Rows and Sorting Data (from a Single Table)

Chapter 8 Restricting Rows and Sorting Data (from a Single Table). Jason C. H. Chen , Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu. Database Development. ORACLE (SQL Components). DDL. JL_D.B. DML. DCL.

shelby
Télécharger la présentation

Chapter 8 Restricting Rows and Sorting Data (from a Single Table)

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 8Restricting Rows and Sorting Data (from a Single Table) Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

  2. Database Development ORACLE (SQL Components) DDL JL_D.B. DML DCL (Retrieve Data and Produce Information)

  3. Objectives • Use a WHERE clause to restrict the rows returned by a query • Create a search condition using mathematical comparison operators • Use the BETWEEN…AND comparison operator to identify records within a range of values • Specify a list of values for a search condition using the IN comparison operator

  4. Objectives (continued) • Search for patterns using the LIKE comparison operator • Identify the purpose of the % and _ wildcard characters • Join multiple search conditions using the appropriate logical operator • Perform searches for NULL values • Specify the order for the presentation of query results using an ORDER BY clause • Extra GROUP BY and HAVING examples are also introduced.

  5. Refresh the Database • Run the following script file • Start c:\oradata\chapter8\JLDB_Build_8.sql • Note that we will first study “Logical Operator” follows by “Comparison Operator”.

  6. WHERE Clause Syntax • A WHERE clause is used to retrieve rows based on a stated condition • Requires: • Column name • Comparison operator • Value or column for comparison • Values are case sensitive (within ‘…’) SELECT [DISTINCT | UNIQUE] (*, field-1 [As alias], field-2, …) FROM tablename [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY columname]; Figure 8-1 Syntax of the SELECT statement

  7. MORE ON SELECT SELECT [DISTINCT | UNIQUE] (*, field-1 [As alias], field-2, …) FROM tablename [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY columname]; Single Table What is the difference between these two ‘SELECT” SELECT [DISTINCT | UNIQUE] (*, field-1 [As alias], field-2, …) FROM tablename-1, tablename-2, … [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY columname]; Multiple Tables Figure 8-1 Syntax of the SELECT statement

  8. WHERE Clause Example • List WHERE clause after FROM clause • Enclose nonnumeric data in single quotes Figure 8-3 Results of a state-based search

  9. Rules for Character Strings -- chapter 8, Figure 8-4; p.247 SELECT * FROM customers WHERE customer# = 1010; -- chapter 8, Figure 8-6; p.248 SELECT * FROM books WHERE pubdate = '21-JAN-05'; -- chapter 8, Figure 8-5; p.247 SELECT * FROM books WHERE isbn = 1915762492; --****************************** -- chapter 8, Figure 8-5(b); p.247 SELECT * FROM books WHERE isbn = '1915762492';

  10. Logical Operators • Used to combine conditions • Evaluated in order of NOT, AND, OR • NOT – reverses meaning • AND – both conditions must be TRUE • OR – at least one condition must be TRUE

  11. AND Logical Operator Example Figure 8-24 Searching with multiple conditions and the AND logical operator

  12. OR Logical Operator Example Figure 8-25 Searching with multiple conditions and the OR logical operator

  13. Multiple Logical Operators • Resolved in order of NOT, AND, OR Figure 8-26 Searching with both AND and OR operators (NOT A GOOD WAY – why? See next example)

  14. Multiple Logical Operators • Use parentheses to override the order of evaluation Figure 8-27 Using parentheses to control the evaluation order for logical operators

  15. Resolving Multiple Types of Operators • Arithmetic operators • Comparison operators • Logical operators

  16. Comparison Operators • Indicate how the data should relate to the given search value -- chapter 8, Figure 8-8; p.250 SELECT title FROM books WHERE title > 'HO'; Figure 8-7 Searching for books with a retail price greater than $55

  17. Arithmetic Comparison Operators Table 8-2 Comparison Operators

  18. Other Comparison Operators Table 8-2 Comparison Operators (cont.)

  19. Practice … • Figure 8-9 to Figure 8-13 (pp.250-255) -- chapter 8, Figure 8-9; p.251 SELECT title, retail-cost profit FROM books WHERE retail-cost < cost*.2; -- chapter 8, Figure 8-10; p.252 SELECT firstname, lastname, state FROM customers WHERE state <= 'GA'; -- chapter 8, Figure 8-11; p.253 SELECT firstname, lastname, state FROM customers WHERE state >= 'GA'; -- chapter 8, Figure 8-12; p.254 SELECT firstname, lastname, state FROM customers WHERE state <> 'GA'; -- chapter 8, Figure 8-13; p.255 SELECT order#, orderdate FROM orders WHERE orderdate < '01-APR-09';

  20. BETWEEN…AND Operator • Finds values in a specified range -- chapter 8, Figure 8-15; p.256 SELECT title FROM books WHERE title BETWEEN 'A' AND 'D'; Figure 8-14 Searching Pubid with the BETWEEN … AND operator

  21. IN Operator and Example • Returns records that match a value in a specified list • List must be in parentheses • Values are separated by commas • What logical operator can be used to replace IN? Figure 8-16 Searching Pubid with the IN operator

  22. Other Examples (IN, NOT IN) -- chapter 8, Figure 8-17; p.257 SELECT firstname, lastname, state FROM customers WHERE state IN ('CA', 'TX'); -- chapter 8, Figure 8-17(b) an alternate way SELECT firstname, lastname, state FROM customers WHERE state = 'CA‘ state = 'TX'; ??? -- chapter 8, Figure 8-18; p.258 SELECT firstname, lastname, state FROM customers WHERE state NOT IN ('CA', 'TX');

  23. LIKE Operator • Performs pattern searches • Used with wildcard characters • Underscore (_) for exactly one character in the indicated position • Percent sign (%) represents any number of characters Figure 8-19 Searching with the LIKE operator and the % wildcard character

  24. More Examples -- chapter 8, Figure 8-20; p.259 SELECT customer#, lastname, firstname FROM customers WHERE customer# LIKE '10_9'; -- chapter 8, Figure 8-22; p.261 SELECT * FROM testing; -- chapter 8, Figure 8-23; p.261 SELECT * FROM testing WHERE tvalue LIKE '\%__A%T' ESCAPE '\'; -- chapter 8, Figure 8-21; p.260 SELECT isbn, title FROM books WHERE isbn LIKE '_4%0';

  25. Treatment of NULL Values • Absence of data • Requires use of IS NULL operator Figure 8-28 Searching for NULL values with the IS NULL operator -- chapter 8, Figure 8-29; p.266 SELECT order#, shipdate FROM orders WHERE shipdate IS NOT NUL

  26. Treatment of NULL Values (continued) • A common error is using = NULL, which does not raise an Oracle error but also does not return any rows Figure 8-30 Using the = NULL operator by mistake

  27. An extra table for GROUP BY and HAVING You need to download (create a new folder of \NW_CW\) and run the following command to make the example work: @ c:\oradata\NW_CW\northwoods.sql location CREATE TABLE location (loc_id NUMBER(6), bldg_code VARCHAR2(10), room VARCHAR2(6), capacity NUMBER(5), CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));

  28. GROUP BY Clauses – Extra Examples The group by clause is used to form groups of rows of a resulting table based on column clauses. When the group by clause is used, all aggregate operations are computed on the individual groups, not on the entire table. --Figure G-0 (wrong version) -- NOT a group by expression: -- ROOM SELECT bldg_code, room, SUM(capacity), AVG(capacity), MAX(capacity) FROM location WHERE capacity >= 5 GROUP BY bldg_code; --Figure G-1 -- right versionof group by SELECT bldg_code, SUM(capacity), AVG(capacity), MAX(capacity) FROM location WHERE capacity >= 5 GROUP BY bldg_code; Output from Figure G-1 BLDG_CODE SUM(CAPACITY) AVG(CAPACITY) MAX(CAPACITY) ---------- ------------- ------------- ------------- BUS 167 41.75 55 CR 260 65 150

  29. GROUP BY Clauses – Extra Examples The group by clause is used to form groups of rows of a resulting table based on column clauses. When the group by clause is used, all aggregate operations are computed on the individual groups, not on the entire table. --Figure G-0 -- NOT a group by expression: ROOM SELECT bldg_code, room, SUM(capacity), AVG(capacity), MAX(capacity) FROM location WHERE capacity >= 5 GROUP BY bldg_code; --Figure G-1 -- right versionof group by SELECT bldg_code, SUM(capacity), AVG(capacity), MAX(capacity) FROM location WHERE capacity >= 5 GROUP BY bldg_code; Output from Figure G-1 BLDG_CODE SUM(CAPACITY) AVG(CAPACITY) MAX(CAPACITY) ---------- ------------- ------------- ------------- BUS 167 41.75 55 CR 260 65 150

  30. The Group by and Having Clauses The having clause is used to eliminate certain groups from further consideration. The following query will produce the same results but with the AVGcapacity of at least 42. --Figure G-2 -- the right version of group by w/ having SELECT bldg_code, SUM(capacity) TOTAL_CAPACITY, AVG(capacity), MAX(capacity) FROM location WHERE capacity >= 5 GROUP BY bldg_code HAVING AVG(capacity) >=42; --Figure G-1 -- the right version of group by SELECT bldg_code, SUM(capacity), AVG(capacity), MAX(capacity) FROM location WHERE capacity >= 5 GROUP BY bldg_code; Output from Figure G-1 BLDG_CODE SUM(CAPACITY) AVG(CAPACITY) MAX(CAPACITY) ---------- ------------- ------------- ------------- BUS 167 41.75 55 CR 260 65 150 Output from Figure G-2 BLDG_CODE TOTAL_CAPACITY AVG(CAPACITY) MAX(CAPACITY) ---------- ------------- ------------- ------------- CR 260 65 150

  31. ORDER BY Clause Syntax • The ORDER BY clause presents data in sorted order • Ascending order is default • Use DESC keyword to override column default • 255 columns maximum Figure 8-31 Syntax of the SELECT statement

  32. ORDER BY Clause Syntax Sort Sequence • In ascending order, values will be listed in the following sequence: • Numeric values • Character values • NULL values • In descending order, sequence is reversed

  33. Examples on ORDER BY Figure 8-32, 34 Sorting results by publisher name in ascending and descending orders

  34. Figure 8-36 The default sort order for NULL values Figure 8-37 Using the NULL FIRST option in the ORDER BY clause

  35. Secondary Sort In the previous examples, only one column was specified in the ORDER BY clause, which is called a primary sort. In some cases, you might want to include a secondary sort, which specifies a second filed to sort b if an exact match occurs between two or more rows in the primary sort. Figure 8-38 Using primary and secondary sort columns

  36. ORDER BY Example Figure 8-39 Sorting on the State and City columns

  37. ORDER BY Can Reference Column Position Figure 8-40 Referencing positions of sort columns in the ORDER BY clause

  38. Practice all the examples in the text. • A Script file is available on the Bb (file name: Ch8Queries.sql) • After completing all examples, do the HW.

  39. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch8_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch8 (or Bmis441-02_Oracle_ch8) Read and Practice all examples on Chapters 8 • 1. Run the script files (in the folder \oradata\chapter8\): JLDB_Build_8.sql • 2. Read Oracle assignment and create a script file Oracle_ch8_Lname_Fname.sql for questions (all EVEN numbers; p.281) on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs if needed. • 3. Execute and test one problem at a time and make sure they are all running successfully. • 4. When you done, spool the script files (see next slide for spooling instructions) and email the file (Oracle_ch8_Spool_Lname_Fname.txt) to me by the midnight before the next class.

  40. How to Spool your Script and Output Files After you tested the script file of Oracle_ch8_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following script file from SQL*Plus (since you have created JLDB tables) • Start c:\oradata\chapter5\JLDB_Build_8.sql • 1. type the following on SQL> • Spool c:\oradata\Oracle_ch8_Spool_Lname_Fname.txt (make sure your name is entered) • 2. open Oracle_ch8_Lname_Fname.sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the .txt file and saved in C: drive (oradata\ folder) Email me with the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch8 (or Bmis441-02_Oracle_ch8)

  41. Summary • The WHERE clause can be included in a SELECT statement to restrict the rows returned by a query to only those meeting a specified condition • When searching a nonnumeric field, the search values must be enclosed in single quotation marks • Comparison operators are used to indicate how the record should relate to the search value • The BETWEEN...AND comparison operator is used to search for records that fall within a certain range of values

  42. Summary (continued) • The LIKE comparison operator is used with the percent and underscore symbols (% and _) to establish search patterns • Logical operators such as AND and OR can be used to combine several search conditions • When using the AND operator, all conditions must be TRUE for a record to be returned in the results • However, with the OR operator, only one condition must be TRUE • A NULL value is the absence of data, not a field with a blank space entered

  43. Summary (continued) • Use the IS NULL comparison operator to match NULL values; the IS NOT NULL comparison operator finds records that do not contain NULL values in the indicated column • You can sort the results of queries by using an ORDER BY clause; when used, the ORDER BY clause should be listed last in the SELECT statement • By default, records are sorted in ascending order; entering DESC directly after the column name sorts the records in descending order • A column does not have to be listed in the SELECT clause to serve as a basis for sorting

More Related