[Oracle-Sql] C.8 Restricting Rows and Sorting Data

Date: 2023.07.24 Updated: 2023.07.24

Categories: Oracle-Sql

📋 This is my note-taking from what I learned in the class “Introduction To Database Concept”

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

WHERE Clause Example

List WHERE clause after FROM clause
Enclose nonnumeric data in single quotes

SELECT LASTNAME, STATE
  FROM CUSTOMERS
  WHERE STATE = 'FL';

Comparison Operators

Indicate how the data should relate to the given search value

SELECT TITLE, RETAIL
  FROM BOOKS
  WHERE RETAIL > 55;

Arithmetic Comparison Operators

Operator	Description
`=`	Equality or “equal to” ─ for example, cost `=` 55.95
`<`	Greater than ─ for example, cost `>` 20
`>`	Less than ─ for example, cost `<` 20
`<>, !=, ^=`	Not equal to ─ for example, cost `<>` 55.95, or cost `!=` 55.95, or cost `^=` 55.95
`<=`	Less than or equal to ─ for example, cost `<=` 20
`>=`	Greater than or equal to ─ for example, cost `>=` 20

Other Comparison Operators

Operator	Description
`[NOT] BETWEEN x AND y`	Used to express a range — for example, searching for numbers BETWEEN 5 AND 10. The optional NOT is used when searching for numbers that are NOT BETWEEN 5 AND 10.
`[NOT] IN (x,y,...)`	Similar to the OR logical operator. Can search for records meeting at least one condition inside the parentheses — for example, Pubid IN (1, 4, 5) returns only books with a publisher ID of 1, 4, or 5. The optional NOT keyword instructs Oracle to return books not published by Publisher 1, 4, or 5.
`[NOT] LIKE`	Used when searching for patterns if you aren’t certain how something is spelled — for example, title LIKE ‘TH%’. Using the optional NOT means records that do contain the specified pattern shouldn’t be included in the results.
`IS [NOT] NULL`	Used to search for records that don’t have an entry in the specified field — for example, Shipdate IS NULL. Include the optional NOT to find records that do have an entry in the field — for example, Shipdate IS NOT NULL.

1. BETWEEN…AND Operator

Finds values in a specified range

SELECT TITLE, PUBID
  FROM BOOKS
  WHERE PUBID BETWEEN 1 AND 3;

2. IN Operator

Returns records that match a value in a specified list
List must be in parentheses
Values are separated by commas

SELECT TITLE, PUBID
  FROM BOOKS
  WHERE PUBID IN (1,2,5);

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

SELECT LASTNAME
  FROM CUSTOMERS
  WHERE LASTNAME LIKE 'P%';

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

SELECT TITLE, PUBID, CATEGORY
  FROM BOOKS
  WHERE PUBID = 3 AND CATEGORY = 'COMPUTER';

OR Logical Operator Example

SELECT TITLE, PUBID, CATEGORY
  FROM BOOKS
  WHERE PUBID = 3 OR CATEGORY = 'COMPUTER';

Multiple Logical Operators

Resolved in order of NOT, AND, OR:

SELECT *
  FROM BOOKS
  WHERE CATEGORY = 'FAMILY LIFE'
    OR PUBID = 4
    AND COST > 15;

Use parentheses to override the order of evaluation:

SELECT *
  FROM BOOKS
  WHERE (CATEGORY = 'FAMILY LIFE'
    OR PUBID = 4)
    AND COST > 15;

Resolving Multiple Types of Operators

Arithmetic operators
Comparison operators
Logical operators

Treatment of NULL Values

Absence of data
Requires use of IS NULL operator

SELECT ORDER#, SHIPDATE
  FROM ORDERS
  WHERE SHIPDATE IS NULL;

A common error is using = NULL, which does not raise an Oracle error but also does not return any rows:

SELECT ORDER#, SHIPDATE
  FROM ORDERS
  WHERE SHIPDATE = NULL;

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

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:

SELECT LASTNAME, FIRSTNAME, STATE, CITY
  FROM CUSTOMERS
  WHERE STATE IN ('FL', 'CA')
  ORDER BY STATE DESC, CITY;

ORDER BY can reference column position:

SELECT LASTNAME, FIRSTNAME, STATE, CITY
  FROM CUSTOMERS
  WHERE STATE IN ('FL', 'CA')
  ORDER BY 3 DESC, 4;

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

C.8 Demo

-- Week 08

-- SEARCH CONDITIONS

SELECT * FROM CUSTOMERS;
SELECT * FROM CUSTOMERS WHERE CUSTOMER#>1005 ;
SELECT * FROM CUSTOMERS WHERE CUSTOMER# BETWEEN 1005 AND 1010;
SELECT * FROM CUSTOMERS WHERE CUSTOMER# IN(1005 ,1006,1007,1008,1009, 1010);
SELECT * FROM CUSTOMERS WHERE STATE IN ('NY','WA','CA');

SELECT  * FROM  CUSTOMERS WHERE  customer#>=1005 and customer#<=1010;
SELECT  * FROM CUSTOMERS  WHERE  customer#>1004 and customer#<1011;
SELECT * FROM  CUSTOMERS WHERE  customer#<1011 and customer#>1004;

-- LIST OF CUSTOMERS IN THE CITY OF AUSTIN
SELECT  * FROM CUSTOMERS where city ='AUSTIN';

--LIST OF CUSTOMERS WHO IS NOT IN CIYT OF  AUSTIN
SELECT  * FROM CUSTOMERS WHERE city!='AUSTIN';
SELECT *  FROM CUSTOMERS  WHERE city<>'AUSTIN';
SELECT* FROM  CUSTOMERS  WHERE CITY ^='AUSTIN';

--LIST OF CUSTOMERS WHO DID NOT PROVIDE EMAIL ID
SELECT * FROM  CUSTOMERS WHERE email is NULL;

-- INCORRECT QUERY: select * from customers where email='null';
SELECT * FROM  CUSTOMERS WHERE email is NOT null;

-- LIST OF CUSTOMERS BY CITY

SELECT * FROM CUSTOMERS ORDER BY 5 ,2 DESC;
SELECT * FROM CUSTOMERS ORDER BY 3;

SELECT * FROM CUSTOMERS WHERE REFERRED IS NOT NULL ;
SELECT * FROM CUSTOMERS WHERE REFERRED IS NOT NULL ORDER BY EMAIL,2;


-- % ANY NUMBER OF CHARACTER ,_ IS EXACTLY ONE CHARACTER

SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE'%N%';
SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE'N%';
SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE '%N';

-- FIND CUSTOMERS WHOSE FIRSTNAME STARTS WITH L AND LASTNAME ENDS WITH H.
SELECT * FROM CUSTOMERS where firstname like 'L%'and lastname like '_M%H';

SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'M_R%';
SELECT * FROM CUSTOMERS WHERE LASTNAME
LIKE '%N' AND STATE IN ('FL','MI') AND CITY LIKE 'C%';

SELECT * FROM BOOKS;

-- LIST OF BOOKS WITH IN THIS RANGE $30 -$50 IN FAMILY LIFE  CATEGORY
SELECT * FROM  BOOKS WHERE cost BETWEEN 30 AND 50 AND CATEGORY LIKE 'F%';
SELECT * FROM BOOKS  WHERE cost BETWEEN 30 AND 50 AND CATEGORY='FAMILY LIFE';

-- DATE
SELECT * FROM BOOKS WHERE PUBDATE <'17-JUL-04';

SELECT * FROM BOOKS WHERE COST >20 AND retail< 50;
SELECT * FROM BOOKS WHERE COST >20 OR  retail< 50;

-- LIST OF ORDERS FROM AUSTIN BUT THE SHIPMENT IS NOT MADE
SELECT * FROM ORDERS WHERE SHIPCITY = 'AUSTIN';

-- LIST OF CITIES WHOSE SHIPMENT IS NOT MADE YET IN FL STATE
SELECT * FROM ORDERS WHERE SHIPDATE IS NULL AND SHIPSTATE='FL';


SELECT * FROM CUSTOMERS WHERE FIRSTNAME LIKE 'M%';
SELECT * FROM CUSTOMERS;

-- INSERT YOUR RECORD IN THE CUSTOMER TABLE AND RETRIEVE OTHER CUSTOMER WITH THE SAME LETTER START
INSERT INTO CUSTOMERS(CUSTOMER#, FIRSTNAME, LASTNAME) VALUES(1209, 'SEYEON', 'JO');
DESC CUSTOMERS;

UPDATE CUSTOMERS SET ADDRESS='PROGRESS CMS', CITY='TORONTO', STATE='ON' WHERE CUSTOMER#=1209;

SELECT * FROM CUSTOMERS WHERE FIRSTNAME LIKE 'S%';
SELECT * FROM CUSTOMERS WHERE FIRSTNAME LIKE 'C%Y';

-- LIST OF CUSTOMERS WHOSE FIRSTNAME ENDS WITH A
SELECT * FROM CUSTOMERS WHERE FIRSTNAME LIKE '%A';

-- LIST OF CUSTOMERS WHOSE FIRSTNAME STARTS WITH B AND ENDS WITH A
SELECT * FROM CUSTOMERS WHERE FIRSTNAME LIKE 'B%A';

-- LIST OF CUSTOMERS WHOSE FIRSTNAME WHOSE HAS ONLY 4 LETTERS
SELECT * FROM CUSTOMERS WHERE FIRSTNAME LIKE 'J___';

SELECT * FROM CUSTOMERS;

SELECT ISBN FROM BOOKS WHERE PUBID IN (1,4);

SELECT TITLE, COST FROM BOOKS WHERE ISBN IN (SELECT ISBN FROM BOOKS WHERE PUBID IN (1,4)) ORDER BY COST;

-- LIST OF COOKING BOOKS
SELECT PUBID FROM BOOKS WHERE CATEGORY='COOKING';

-- LIST OF OTHER BOOKS THAT ARE PUBLISHED BY THE SAME PUBLISHER
SELECT * FROM BOOKS WHERE PUBID IN (SELECT PUBID FROM BOOKS WHERE CATEGORY='COOKING');

Seyeon