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.
Table of contents
💠 💠 💠CHAPTER 8 RESTRICTING ROWS & SORTING DATA 💠 💠 💠
⭐️1.2. Arithmetic comparison operators
⭐️1.7 Operator precedence reference table
💠 💠 💠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);
| meaning | operator |
|---|---|
| comparison operator | = ，>，<，>=，<=，!=，<>,^=； |
| range operator | BETWEEN AND，NOT BETWEEN AND |
| at.... | IN，NOT IN |
| describe portrayal | LIKE，NOT LIKE |
| null | IS NULL，IS NOT NULL |
| Boolean logic | AND，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,
address VARCHAR2(20),
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.