1 / 43

# Chapter 8 Restricting Rows and Sorting Data (from a Single Table) - PowerPoint PPT Presentation

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 [email protected] Database Development. ORACLE (SQL Components). DDL. JL_D.B. DML. DCL.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Chapter 8 Restricting Rows and Sorting Data (from a Single Table)' - shelby

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

Jason C. H. Chen, Ph.D.

Professor of MIS

Gonzaga University

Spokane, WA 99258 USA

ORACLE

(SQL Components)

DDL

JL_D.B.

DML

DCL

(Retrieve Data and Produce Information)

• 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

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

• Run the following script file

• Note that we will first study “Logical Operator” follows by “Comparison Operator”.

• 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

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

• List WHERE clause after FROM clause

• Enclose nonnumeric data in single quotes

Figure 8-3 Results of a state-based search

-- 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';

• 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

Figure 8-24 Searching with multiple conditions and the AND logical operator

Figure 8-25 Searching with multiple conditions and the OR logical operator

• 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)

• Use parentheses to override the order of evaluation

Figure 8-27 Using parentheses to control the evaluation order for logical operators

• Arithmetic operators

• Comparison operators

• Logical 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

Table 8-2 Comparison Operators

Table 8-2 Comparison Operators (cont.)

• 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';

• 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

• 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

-- 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');

• 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

-- 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';

• 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

• 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

You need to download (create a new folder of \NW_CW\) and run the following command to make the example work:

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));

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

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

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

• 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

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

Figure 8-32, 34 Sorting results by publisher name in ascending and descending orders

Figure 8-37 Using the NULL FIRST option in the ORDER BY clause

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

Figure 8-39 Sorting on the State and City columns

ORDER BY Can Reference Column Position

Figure 8-40 Referencing positions of sort columns in the ORDER BY clause

Homework - Hands-On Assignments

Email me with one attachment

(Oracle_ch8_Spool_Lname_Fname.) to:

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.

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)

• 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:

with subject title of

Bmis441-01_Oracle_ch8 (or Bmis441-02_Oracle_ch8)

• 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

• 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

• 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