Chapter 8 restricting rows and sorting data from a single table
This presentation is the property of its rightful owner.
Sponsored Links
1 / 43

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


  • 127 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

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.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 8 restricting rows and sorting data from a single table

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

[email protected]


Database development

Database Development

ORACLE

(SQL Components)

DDL

JL_D.B.

DML

DCL

(Retrieve Data and Produce Information)


Objectives

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


Objectives continued

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.


Refresh the database

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


Where clause syntax

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


More on select

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


Where clause example

WHERE Clause Example

  • List WHERE clause after FROM clause

  • Enclose nonnumeric data in single quotes

Figure 8-3 Results of a state-based search


Rules for character strings

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


Logical operators

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


And logical operator example

AND Logical Operator Example

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


Or logical operator example

OR Logical Operator Example

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


Multiple logical operators

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)


Multiple logical operators1

Multiple Logical Operators

  • Use parentheses to override the order of evaluation

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


Resolving multiple types of operators

Resolving Multiple Types of Operators

  • Arithmetic operators

  • Comparison operators

  • Logical operators


Comparison operators

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


Arithmetic comparison operators

Arithmetic Comparison Operators

Table 8-2 Comparison Operators


Other comparison operators

Other Comparison Operators

Table 8-2 Comparison Operators (cont.)


Practice

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


Between and operator

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


In operator and example

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


Other examples in not in

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


Like operator

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


More examples

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


Treatment of null values

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


Treatment of null values continued

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


An extra table for group by and having

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


Group by clauses extra examples

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


Group by clauses extra examples1

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


Chapter 8 restricting rows and sorting data from a single table

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


Order by clause syntax

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


Order by clause syntax sort sequence

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


Examples on order by

Examples on ORDER BY

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


Chapter 8 restricting rows and sorting data from a single table

Figure 8-36 The default sort order for NULL values

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


Secondary sort

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


Order by example

ORDER BY Example

Figure 8-39 Sorting on the State and City columns


Order by can reference column position

ORDER BY Can Reference Column Position

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


Chapter 8 restricting rows and sorting data from a single table

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


Homework hands on assignments

Homework - Hands-On Assignments

Email me with one attachment

(Oracle_ch8_Spool_Lname_Fname.) to:

[email protected]

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.


How to spool your script and output files

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:

    [email protected]

    with subject title of

    Bmis441-01_Oracle_ch8 (or Bmis441-02_Oracle_ch8)


    Summary

    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


    Summary continued

    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


    Summary continued1

    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


  • Login