Distinct, Like, Operators, Where, Null & Order by in Oracle

SQL BASICS

The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

For example:
select distinct working_area from AGENTS;

select distinct AGENT_CODE, AGENT_NAME, GENDER, AGE, WORKING_AREA,
SALARY, COMMISSION, PHONE_NO, COUNTRY
FROM AGENTS;

select distinct * FROM AGENTS;
The following SQL statement lists the number of different (distinct) customer countries:
SELECT COUNT(DISTINCT working_area) FROM AGENTS;

The SQL WHERE Clause
The WHERE clause is used to filter records.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition;
The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!
For Example:

SELECT * from AGENTS
where working_area='Bangalore';

SELECT AGENT_CODE, AGENT_NAME from AGENTS
where working_area='Bangalore'

SELECT* FROM AGENTS
WHERE COMMISSION=0.15;

The SQL AND, OR and NOT Operators
▹ The AND and OR operators are used to filter records based on more than one condition:
▹ The AND operator displays a record if all the conditions separated by AND are TRUE.
▹ The OR operator displays a record if any of the conditions separated by OR is TRUE.
▹ The NOT operator displays a record if the condition(s) is NOT TRUE.
Example:

SELECT * from CUSTOMER
where GRADE> 2 AND OPENING_AMT> 6000;

SELECT * FROM CUSTOMER
WHERE GRADE> 2 OR OPENING_AMT> 6000;

SELECT * from CUSTOMER
where CUST_CITY='Bangalore' and CUST_COUNTRY='India';

SELECT * from CUSTOMER
where CUST_CITY='Bangalore' or CUST_COUNTRY='India';

SELECT * FROM CUSTOMER
WHERE NOT CUST_CITY='Bangalore';

SELECT * FROM CUSTOMER
WHERE NOT CUST_CITY='Bangalore' AND NOT CUST_COUNTRY='India';

The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
▹ The percent sign (%) represents zero, one, or multiple characters
▹ The underscore sign (_) represents one, single character

% wildcard character examples:

SELECT first_name, last_name,phone FROM contacts
WHERE last_name LIKE 'St%'; 

whose last names start with ‘St’

SELECT first_name, last_name,phone FROM contacts
WHERE last_name LIKE '%er';

whose last names end with the string ‘er’

The following example uses the NOT LIKE operator

SELECT first_name,last_name, phone FROM contacts
WHERE phone NOT LIKE '+1%';

to find contacts whose phone numbers do not start with ‘+1’:

_ wildcard character examples
The following example finds the phone numbers and emails of contacts whose first names have the following pattern ‘Je_i’:

SELECT first_name,last_name,email,phone FROM contacts
WHERE first_name LIKE 'Je_i';

The pattern ‘Je_i’ matches any string that starts with ‘Je’, followed by one character, and then followed by ‘i’ e.g., Jeri or Jeni, but not Jenni

Mixed wildcard characters example
The following statement finds contacts whose first names that have “r” in the second position:

SELECT first_name, last_name, email, phone FROM contacts
WHERE first_name LIKE '_r%';

Oracle ORDER BY Clause
▹ The ORDER BY keyword is used to sort the result-set in ascending or descending order.
▹ The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
SELECT name, address, credit_limit FROM customers ORDER BY name ASC;
SELECT name, address, credit_limit FROM customers ORDER BY name;
SELECT name, address, credit_limit FROM customers ORDER BY name DESC;
SELECT first_name, last_name FROM contacts ORDER BY first_name,last_name;
SELECT first_name, last_name FROM contacts ORDER BY 1,2; -- Order by first column and second column
SELECT first_name, last_name FROM contacts ORDER BY last_name,first_name;
SELECT first_name, last_name FROM contacts ORDER BY 2,1; -- order by second column , first column
SELECT first_name, last_name FROM contacts ORDER BY 2 DESC 1;

Sorting by date example
This example uses the ORDER BY clause to sort orders by Order Date:

SELECT order_id, customer_id, status, order_date FROM orders
ORDER BY order_date DESC;

IS NULL
▹ NULL is special. It is a marker for missing information or the information is not applicable.
▹ NULL is special in the sense that it is not a value like a number, character string or datetime.
▹ You cannot compare it with any other values like zero (0) or an empty string (”).
Ex: The following query returns all sales orders that do not have a responsible salesman:

SELECT * FROM orders
WHERE salesman_id IS NULL; -- YOU SHOULD NOT USE salesman_id=null

IS NOT NULL
▹ The operator IS NOT NULL returns true if the expression or value in the column is not null. Otherwise, it returns false.
Ex: The following example returns all sales orders which have a responsible salesman:

SELECT * FROM orders
WHERE salesman_id IS NOT NULL;