# 6. Restricting rows and sorting (Oracle column)

```April 13, 2022 23:00:24
Through this chapter, you will be able to:
Filter rows in the query.
Sort the rows in the query.
```

💠  💠  💠CHAPTER 8 RESTRICTING ROWS & SORTING DATA 💠  💠  💠

🏆 1. WHERE

⭐️1.1, data preparation

⭐️1.2. Arithmetic comparison operators

⭐️1.3,BETWEEN AND

⭐️1.4,IS (NOT) NULL

⭐️1.5,IN and NOT IN

⭐️1.6,LIKE , NOT LIKE

⭐️1.7 Operator precedence reference table

🏆 2. ORDER BY

🏆 3. Summary

# 💠  💠  💠CHAPTER 8 RESTRICTING ROWS & SORTING DATA 💠  💠  💠

• Use the WHERE clause to limit the rows returned by a query
• Create a search condition using mathematical comparison operators
• Use the BETWEEN…AND comparison operator to identify a range of record values ​​in it
• List of values ​​operator specifying a search condition using IN comparison
• Search for patterns using the LIKE comparison operator
• Identify the purpose of the % &_ wildcard
• Concatenate multiple search criteria using appropriate logical operators
• perform a search for NULL values
• Use ORDER BY to specify the display order of query results (ascending and descending)

# 🏆 1. WHERE

The WHERE clause is used to retrieve rows according to the specified conditions. Use the WHERE clause to filter out rows that do not meet the conditions. The WHERE clause immediately follows the FROM clause.

SYNTAX

```SELECT [DISTINCT | UNIQUE] (*,columnname [AS alias],...)
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY columnname [ASC|DSC]];
---
​
DROP TABLE book_pricing Purge;
CREATE TABLE book_pricing ( id, cost, retail, category)
AS (SELECT isbn, cost, retail, category FROM books WHERE 1=2); ```
meaningoperator
comparison operator= ，>，<，>=，<=，!=，<>,^=；
range operatorBETWEEN AND，NOT BETWEEN AND
at....IN，NOT IN
describe portrayalLIKE，NOT LIKE
nullIS NULL，IS NOT NULL
Boolean logicAND，OR，NOT

## ⭐️1.1, data preparation

Open PL/SQL Developer, find the corresponding table under tables, right-click and select the "View" menu, there is a "View SQL" button in the lower right corner

```-- Create table book list
create table BOOKS
(
isbn     VARCHAR2(10) not null,
title    VARCHAR2(30),
pubdate  DATE,
pubid    NUMBER(2),
cost     NUMBER(5,2),
retail   NUMBER(5,2),
discount NUMBER(4,2),
category VARCHAR2(12)
)

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('1059831198', 'BODYBUILD IN 10 MINUTES A DAY', to_date('21-01-2005', 'dd-mm-yyyy'), 4, 18.75, 30.95, null, 'FITNESS');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('0401140733', 'REVENGE OF MICKEY', to_date('14-12-2005', 'dd-mm-yyyy'), 1, 14.20, 22.00, null, 'FAMILY LIFE');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('4981341710', 'BUILDING A CAR WITH TOOTHPICKS', to_date('18-03-2006', 'dd-mm-yyyy'), 2, 37.80, 59.95, 3.00, 'CHILDREN');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('8843172113', 'DATABASE IMPLEMENTATION', to_date('04-06-2003', 'dd-mm-yyyy'), 3, 31.40, 55.95, null, 'COMPUTER');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('3437212490', 'COOKING WITH MUSHROOMS', to_date('28-02-2004', 'dd-mm-yyyy'), 4, 12.50, 19.95, null, 'COOKING');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('3957136468', 'HOLY GRAIL OF ORACLE', to_date('31-12-2005', 'dd-mm-yyyy'), 3, 47.25, 75.95, 3.80, 'COMPUTER');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('1915762492', 'HANDCRANKED COMPUTERS', to_date('21-01-2005', 'dd-mm-yyyy'), 3, 21.80, 25.00, null, 'COMPUTER');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('9959789321', 'E-BUSINESS THE EASY WAY', to_date('01-03-2006', 'dd-mm-yyyy'), 2, 37.90, 54.50, null, 'COMPUTER');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('2491748320', 'PAINLESS CHILD-REARING', to_date('17-07-2004', 'dd-mm-yyyy'), 5, 48.00, 89.95, 4.50, 'FAMILY LIFE');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('0299282519', 'THE WOK WAY TO COOK', to_date('11-09-2004', 'dd-mm-yyyy'), 4, 19.00, 28.75, null, 'COOKING');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('8117949391', 'BIG BEAR AND LITTLE DOVE', to_date('08-11-2005', 'dd-mm-yyyy'), 5, 5.32, 8.95, null, 'CHILDREN');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('0132149871', 'HOW TO GET FASTER PIZZA', to_date('11-11-2006', 'dd-mm-yyyy'), 4, 17.85, 29.95, 1.50, 'SELF HELP');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('9247381001', 'HOW TO MANAGE THE MANAGER', to_date('09-05-2003', 'dd-mm-yyyy'), 1, 15.40, 31.95, null, 'BUSINESS');

insert into books (ISBN, TITLE, PUBDATE, PUBID, COST, RETAIL, DISCOUNT, CATEGORY)
values ('2147428890', 'SHORTEST POEMS', to_date('01-05-2005', 'dd-mm-yyyy'), 5, 21.85, 39.95, null, 'LITERATURE');

-- Create table customer table
create table CUSTOMERS
(
customer# NUMBER(4) not null,
lastname  VARCHAR2(10) not null,
firstname VARCHAR2(10) not null,
city      VARCHAR2(12),
state     VARCHAR2(2),
zip       VARCHAR2(5),
referred  NUMBER(4),
region    CHAR(2),
email     VARCHAR2(30)
)

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', null, 'SE', 'bm225@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1002, 'THOMPSON', 'RYAN', 'P.O. BOX 9835', 'SANTA MONICA', 'CA', '90404', null, 'W ', null);

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1003, 'SMITH', 'LEILA', 'P.O. BOX 66', 'TALLAHASSEE', 'FL', '32306', null, 'SE', null);

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1004, 'PIERSON', 'THOMAS', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', null, 'NW', 'tpier55@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', null, 'NW', 'cing101@zep.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1006, 'CRUZ', 'MESHIA', '82 DIRT ROAD', 'ALBANY', 'NY', '12211', null, 'NE', 'cruztop@axe.com');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1007, 'GIANA', 'TAMMY', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 1003, 'SW', 'treetop@zep.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1008, 'JONES', 'KENNETH', 'P.O. BOX 137', 'CHEYENNE', 'WY', '82003', null, 'N ', 'kenask@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1009, 'PEREZ', 'JORGE', 'P.O. BOX 8564', 'BURBANK', 'CA', '91510', 1003, 'W ', 'jperez@canet.com');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1010, 'LUCAS', 'JAKE', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', null, 'SE', null);

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1011, 'MCGOVERN', 'REESE', 'P.O. BOX 18', 'CHICAGO', 'IL', '60606', null, 'N ', 'reesemc@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1012, 'MCKENZIE', 'WILLIAM', 'P.O. BOX 971', 'BOSTON', 'MA', '02110', null, 'NE', 'will2244@axe.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1013, 'NGUYEN', 'NICHOLAS', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 1006, 'SE', 'nguy33@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1014, 'LEE', 'JASMINE', 'P.O. BOX 2947', 'CODY', 'WY', '82414', null, 'N ', 'jaslee@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1015, 'SCHELL', 'STEVE', 'P.O. BOX 677', 'MIAMI', 'FL', '33111', null, 'SE', 'sschell3@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1016, 'DAUM', 'MICHELL', '9851231 LONG ROAD', 'BURBANK', 'CA', '91508', 1010, 'W ', null);

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1017, 'NELSON', 'BECCA', 'P.O. BOX 563', 'KALMAZOO', 'MI', '49006', null, 'N ', 'becca88@digs.com');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1018, 'MONTIASA', 'GREG', '1008 GRAND AVENUE', 'MACON', 'GA', '31206', null, 'SE', 'greg336@sat.net');

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1019, 'SMITH', 'JENNIFER', 'P.O. BOX 1151', 'MORRISTOWN', 'NJ', '07962', 1003, 'NE', null);

insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1020, 'FALAH', 'KENNETH', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', null, 'NE', 'Kfalah@sat.net');
insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1055, 'FALAH', 'KENNETH_2', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', null, 'NE', 'Kfalah@sat.net');
insert into customers (CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL)
values (1066, 'FALAH', 'KENNETH%2', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', null, 'NE', 'Kfalah@sat.net');```

## ⭐️ 1.2. Arithmetic comparison operators

Example 1:

Find information on books costing more than \$20 in table 'BOOKS'

SELECT TITLE, PUBDATE, COST, RETAIL, CATEGORY FROM BOOKS WHERE COST >= 20;

Example 2:

Look for information on books that retail for less than \$60 and greater than \$10. in table 'BOOKS'

SELECT TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE RETAIL > 10
AND RETAIL < 60;

Example 3:

Look for books whose category is 'COMPUTER' and retails no more than 40.

SELECT title,pubdate,cost,retail,category FROM books WHERE retail <40 and category= 'computer'; wrong example statement

Note: Values ​​are case sensitive.

SELECT TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE RETAIL < 40
AND CATEGORY = 'COMPUTER';

Example 4:

Look for books whose category is not 'COMPUTER' and retails no more than 40.

```SELECT title,pubdate,cost,retail,category FROM books
WHERE retail <40 and category != 'COMPUTER';
OR
SELECT title,pubdate,cost,retail,category FROM books
WHERE retail <40 and category ^= 'COMPUTER';
OR
SELECT ROWNUM,title,pubdate,cost,retail,category FROM books
WHERE retail <40 and category <> 'COMPUTER';```

Example 5:

Look for books in categories that are 'COMPUTER' or 'COOKING' and retail no more than 40.

SELECT TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE NOT RETAIL < 40
AND (CATEGORY = 'COMPUTER' OR CATEGORY = 'COOKING');

## ⭐️1.3,BETWEEN AND

Find values ​​in a specified range

Example 1:

Find books that retail under \$60 and under \$10 in table 'BOOKS'

SELECT ROWNUM, TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE RETAIL > 10
AND RETAIL < 60;

Note: ROWNUM is an implicit column in each table

SELECT ROWNUM, TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE RETAIL BETWEEN 10 AND 60;

Example 2:

Find books that retail for at least \$60 and no less than \$10 in table 'BOOKS'

SELECT ROWNUM, TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE NOT RETAIL BETWEEN 10 AND 60;

Example 3:

Find book information with dates between 2003-05-01 and 2005-12-01 in table 'BOOKS'

Note: 'BETWEEN AND' can be used not only for numbers, but also for dates.

SELECT ROWNUM, TITLE, PUBDATE, COST, RETAIL, CATEGORY
FROM BOOKS
WHERE PUBDATE BETWEEN TO_DATE('2003-05-01', 'yyyy-mm-dd') AND
TO_DATE('2005-12-01', 'yyyy-mm-dd');

## ⭐️1.4,IS (NOT) NULL

EXAMPLE 1:

Query customer information whose REFERRED column is not empty

```SELECT ROWNUM, CUSTOMER#, LASTNAME, STATE, REFERRED
FROM CUSTOMERS
WHERE REFERRED IS NOT NULL;
OR
SELECT ROWNUM, CUSTOMER#, LASTNAME, STATE, REFERRED
FROM CUSTOMERS
WHERE NOT REFERRED IS NULL;
```

EXAMPLE 2:

Query the customer information whose REFERRED column is empty

SELECT ROWNUM, CUSTOMER#, LASTNAME, STATE, REFERRED
FROM CUSTOMERS
WHERE REFERRED IS NULL;

SELECT ROWNUM, customer#,lastname,state,referred FROM customers WHERE referred = NULL; error example

Note: If set to =NULL, IT cannot return any value.

## ⭐️1.5,IN and NOT IN

Returns records that match a value in the specified list

• Lists must be enclosed in parentheses ()
• The values ​​are separated by ","

Query the customer information of FL, CA, NY displayed in the STATE column.

SELECT ROWNUM, CUSTOMER#, LASTNAME, CITY, STATE
FROM CUSTOMERS
WHERE STATE = 'FL'
OR STATE = 'CA'
OR STATE = 'NY';

```SELECT ROWNUM,customer#,lastname,city,state FROM customers
WHERE state IN( 'FL' ,'CA','NY');

SELECT ROWNUM,customer#,lastname,city,state FROM customers
WHERE state IN( 'FL' ,'CA',
'KT'); /* KT is not in state column*/

SELECT ROWNUM,customer#,lastname,city,state FROM customers
WHERE state IN( 'FL' ,'CA',
'NULL');```

## ⭐️1.6,LIKE , NOT LIKE

• perform a pattern search
• Use with wildcards

Underscore (_) is only one character at the specified position

The percent sign (%) represents any number of characters

'%' and '_' can be used together

two wildcards

EXAMPLE 1:

Look for those who have a "Y" in the second letter of their name

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE FIRSTNAME LIKE '_Y%';

EXAMPLE 2:

Look for people whose first name is "K" in table 'CUSTOMERS'

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE FIRSTNAME LIKE 'K%';

EXAMPLE 3:

Find people whose names contain 'A' in table 'CUSTOMERS'

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE FIRSTNAME LIKE '%A%';

EXAMPLE 4:

Find people whose names do not contain 'A' in table 'CUSTOMERS'

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE FIRSTNAME NOT LIKE '%A%';

EXAMPLE 5:

Find people whose name or number contains the number 2 in table 'CUSTOMERS'

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE FIRSTNAME LIKE '%2%'
OR CUSTOMER# LIKE '%2%';

Note: "LIKE" queries can be used for character, number and date types.

Escape character "\"

What if we want to search for these wildcards ' _ ', ' % '?

ESCAPE

To escape special symbols: use escape characters. For example: convert [%] to [\%], [_] to [\_], and then add [ESCAPE ‘\’].

EXAMPLE 6:

Find those people whose names contain special characters '%', '_' in table 'CUSTOMERS'

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE FIRSTNAME LIKE '%\%%' ESCAPE '\'-- '%  \%  %' escape '\';
OR FIRSTNAME LIKE '%\_%' ESCAPE '\'; -- '%  \_  %' escape '\';

## ⭐️1.7 Operator precedence reference table

 priority 1 arithmetic operators 2 Joiner 3 comparator 4 IS [NOT] NULL, LIKE, [NOT] IN 5 [NOT] BETWEEN 6 NOT 7 AND 8 OR

You can use parentheses to change the priority order

# 🏆 2. ORDER BY

ORDER BY clause displays data in ordered order

ASC (ascend): Ascending order DESC (descend): Descending order. ASC (ascend): Ascending DESC (descend): Descending

• The default is ascending order
• Override column default with DESC keyword
• Values ​​will be listed in ascending order:
• numeric value
• character value
• zero value
• Sort in descending order, reverse order
• Up to 255 columns

SYNTAX

```SELECT [DISTINCT | UNIQUE] (*,columnname [AS alias],...)
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY columnname [ASC|DSC]];```

Sort column FIRSTNAME

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
ORDER BY FIRSTNAME;

Filter customer information with status 'FL' and sort by name in ascending order

SELECT ROWNUM,customer#,firstname,city,state FROM customers ORDER BY firstname WHERE state='FL'; Error example

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
WHERE STATE = 'FL'
ORDER BY FIRSTNAME;

Note: ' ORDER BY ' should be in the last position of the sql command

ORDER BY can refer to the position of the column

```SELECT ROWNUM,customer#,firstname,city,state FROM customers
ORDER BY 3;

SELECT ROWNUM,customer#,firstname,city,state FROM customers
ORDER BY 3 DESC,6; ```

Note: ORDER BY item must be the number of a select list expression

SELECT ROWNUM, CUSTOMER#, FIRSTNAME, CITY, STATE
FROM CUSTOMERS
ORDER BY 3 DESC, 5;

NULL for ASC/DSC

Default ascending order

SELECT ROWNUM, CUSTOMER#, REFERRED FROM CUSTOMERS ORDER BY REFERRED;

Sort descending

SELECT ROWNUM, CUSTOMER#, REFERRED FROM CUSTOMERS ORDER BY REFERRED DESC;

# 🏆 3. Summary

Through this chapter, you should be able to:

• Filter data using the WHERE clause
• Use comparison operators
• Use BETWEEN AND, IN, LIKE and NULL operations
• Using logical operators AND, OR and NOT
• Use the ORDER BY clause to sort.

Tags: Database Oracle

Posted by benW on Sun, 16 Oct 2022 06:21:19 +0530