Restricting rows, BETWEEN ,IN,LIKE,NULL Conditions and Sorting in Oracle

WHERE Clause in SQL

WHERE clause is used to restrict rows. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true, the row meeting the condition is returned.

Syntax: SELECT <Columns Name> from <Table Name> WHERE <Conditions>;

In Oracle SQL query is not case sensitive but data values are case sensitive. for example in employee table ename column value 'vimal' is different from value 'VIMAL'. 

When you write where clause for string(varchar, char etc) and date data type, values must enclosed data in single quotation('string') otherwise query will generate error message.

Comparison Conditions in Query

Comparison operator like 
   =            equal to
   <            less than
   >            greater than
   <=          less than equal to
   >=          greater than equal to
can be used in WHERE clause to restrict rows.

BETWEEN .......AND Condition

This is also a comparison condition. comparison based on two values both are inclusive.

Syntax : Select <colums> from <table name> Where <Col Name> BETWEEN <Min inclusive value> AND <Max. inclusive value>; 

IN Condition
IN Condition use multiple values to restrict rows. Multiple values are comma separated and inside the bracket .
Ex. shows IN condition for numeric values.


Ex. Shows IN Condition for string value.

LIKE Condition
Like Condition perform character pattern matching operation. This operation can only be used for string. 
Two symbol % and _ is used in LIKE Conditions. these character is called wildcard character.

%     denotes zero or many characters.
_      denotes one character.

Suppose you want to get all employee names whose names begin with 'S' or suppose a condition where you want to get employee names where second character is 'A'.
It is widely used command in programming for searching string.

You can also search date values by LIKE Condition.

ESCAPE Option in LIKE Condition.

ESCAPE Option is used to include symbol % and _ into search pattern. imagine a condition when you want to search employee ename whose second character is '_', now if you write query like

Select * from employee where ename like '_ _%'. 

It won't display desire output because '_' is a special symbol in LIKE Condition. Now you can change the definition of '_' using ESCAPE Option.

Now you can see i have inserted two rows in employee table. here column desig contain '_' and '%' . 

Search Employee designation where designation contain '_'.
 here '\' is used before '_' and escape '\' removes the definition of '_'. you can use other symbol in place of '\'
for Ex. All these query are similar. 
Select * from employee where desig like '%\_%' escape '\';
Select * from employee where desig like '%+_%' escape '+'; 
Select * from employee where desig like '%#_%' escape '#';
Select * from employee where desig like '%&_%' escape '&';

NULL Condition
Null value means value is 

• Unavailable
• Unassigned
• Unknown
• Inapplicable

Two keywords for null or not null values are

1. is null
2. is not null 

Note : Mathematical operation with null value always return null.

Is null return value based on column null values.

is not null display value that doesn't contain null value. 

Logical Condition 
AND Operator
Syntax: Select <columns> from <table> where <Condition1> AND <Condition2>;

Query returns value if both condition are true.

OR Operator
Syntax: Select <columns> from <table> where <Condition1> OR <Condition2>;

Query returns value if either Condition1 is true or Condition2 is true or both are true.

NOT Operator
Syntax: Select <columns> from <table> where NOT in <Condition>; 

Query returns value only if given condition is unmatched.

Operator Precedence

Sorting in Oracle Select Query 
Order By Clause
Order By Clause is used in oracle for sorting. By default order by sort record in ascending order. 

Syntax: Select <Columns> from <table name> ORDER BY <column1,column2..> Asc/Desc;

Order by Example in string values.

Order By example in numeric values.

You can either use asc keyword or not result is equal. by default sorting is in ascending order.

Sorting in Descending Order 
desc keyword is used to sort record in descending order.

Sorting by Multiple Columns

SELECT statement in Oracle

SELECT Statement retrieves information from database. Capabilities of SELECT statements are

• Projection : Selection of columns.
• Selection  :  Selection of rows. 
• Join          :  Combining two or more tables columns.

Select all columns from table

Syntax : Select * from <table name>;

* keyword is used to get all columns from table.

Note: set pagesize and set linesize is used to display your result properly is sql*plus.

Select specific column from table

Syntax : Select <column_1, column_2, ...> from <table name>;

Arithmetic operation in select query 

You can perform arithmetic operation on numeric values. It is only used for display doesn't affect actual table. Arithmetic operation like addition, subtraction, multiplication and division can easily perform on each rows. 

Operator precedence in select query

Operator precedence works like simple operator precedence works in mathematics. 

if you want more specific separate your calculation in bracket. It has highest priority and calculation inside bracket execute first. 

Column name in select query

Column alias in select query

In your select query result you can alias the name of column. 

Column alias in inverted comma 

Space is not recognized in column alias until you use inverted comma. Ex. desig is define as employee desig with inverted comma. 

Concatenation operator in sql

Concatenation operator (||) simply concatenate different column values into single column. you can add desire text in it. 

Distinct keyword in sql

Distinct keywords remove duplicates values in result. It is useful command in some cases you may want to how many unique values your column contain at that time you can use distinct keyword. You can also apply distinct keyword on no of column. 

CREATE TABLE statement in Oracle

Table is a basic unit of storage. When you create table you define the column name and their behavior. You must know the naming rules, data types and create table syntax to create table.

Data Types :

Data Types define the behavior of data values. Every column name must have a datatype. It is useful for example if you define amount column name datatype is float or number you can perform various mathematical operation in select query as well.

Most popular datatypes in oracle are

• Varchar2(size) : variable length character data.
• Number            : define numeric values
• Date                  : date and time values.

Create Table Syntax :

This is the simple syntax of create table statement. default and not null are two optional keywords. 

Create Table example in Oracle Sql Plus:

Open Sql Plus

Type username and password to connect oracle 

Write create table statement in oracle*sql plus:

Once the table is created you can see the structure of table by desc <table Name>  command.

Create table by sub query:

Syntax 1:
Create table <table name> as Select * from <table name>;

create new table based on sub query. It will copy table structure and data.

Ex.
Create table emp_new as select * from employee;

Syntax 2:

Create table <table name> as select <col1,col2,..> from <table name>;               

create table based on given column reference. It also copy data.

Ex.       
Create table emp_new1 as select eno,ename from employee;

Syntax 3:
Create table <table name> as Select * from <table name> where 1<>1;       

create table based on sub query but it copy only the structure of table not data.

Ex.    

Create table emp_new2 as select * from employee where 1<>1;

           

Oracle Database Certification List