Operators in Oracle SQL*Plus with Examples
Operators in Oracle SQL*Plus with Examples.
The following are the operators supported by SQL*Plus:
◆ Arithmetic operators
◆ Comparison operators
◆ Logical operators
◆ Set operators
Let us discuss them in detail.
Arithmetic operators
To perform calculations based on number values, we include arithmetic expressions in SQL command. An arithmetic expression consists of column names with number datatype and an arithmetic operator connecting them. The arithmetic operators are + (addition), – (subtraction), * (multiplication) and /(division). Consider the following example.
To verify the quantity required, of each item under the category of spares, to reach the maximum level the select command shown in the following example is used.
Example :
SQL> select itemdesc, max_level-qty_hand avble_limit from itemfile where p_category=’spares’;
The output of this query will be as follows.
ITEMDESC AVBLE_LIMIT
——————– ————–
nuts 200
bolts 205
holders 57
brackets 50
If there are several operators in an arithmetic expression then the precedence of each operator must be known. * and / have equal higher precedence whereas + and – have equal lower precedence.
Example given below illustrates the precedence of operators.
Example :
SQL> select itemdesc, itemrate* (max_level-qty_hand) +itemrate *re_level
from itemfile where p_category=’spares’;
In the above example, the result obtained from subtracting qty_hand from max_level is multiplied by itemrate. If parenthesis is omitted then multiplication will be performed first followed by subtraction. Thus we can control the order of evaluation by using parenthesis.
Comparison operators
Comparison operators are used in conditions to compare one expression with another. The comparison operators are =, !=, <, >, <=,>=, between (to check between any two values), in (to match with any of the values in the list), like(to match a character pattern) and is Null (to check whether it is Null).
The last four operators mentioned above can also be used for checking the NOT conditions like NOT BETWEEN, NOT LIKE and so on. The following example illustrates the comparison operator” lesser than”.
To avoid delay in delivery of items a policy decision is taken by the marketing division at t XYZ ENTERPRISES to keep a constant check on the stock. In case the quantity at hand falls below the maximum level then priority is to be given to the manufacture of those items. The query in the following example can be used to check this.
Example :
SQL> select itemdesc,re_level from itemfile where qty_hand<max_level/2;
This query gives the following output.
ITEMDESC RE_LEVEL
—————— ———-
nuts 50
The information displayed as a result of this query indicates that the item ‘nuts’ is to be given top priority.
The next example discussed below is used to check for negations (i.e. check for NOT conditions)
Example :
SQL> select itemdesc,re_level from itemfile where not qty_hand<max_level/2);
The above command will display all other columns not displayed in example 2.9.
ITEMDESC RE_LEVEL
—————— ———-
bolts 125
holders 30
covers 15
panels 30
brackets 73
The IN operator can be used to select rows that match one of the values in a list.
To check for fast approaching deliveries the staff in the marketing division use queries similar to the one shown below.
Example :
SQL> select orderno from order_master where del_date in (’06-jan-99′,’05-feb-99′);
The above example lists all the ordernos from the order_master table for which del_date lies
in the given list.
ORDER
——–
0001
0003
When we search for character values using the IN operator, the column name must exactly match with the values present in the list. In the case of the LIKE operator which is used to characters, search a character pattern, we need not know the exact character value. The LIKE operator recognizes special characters like % and _ . The former can match zero or more while the latter matches exactly one character.
Note: % cannot be used to match a Null character.
The select statement in the next example will display vendors whose names start with the letter ‘R’.
Example :
SQL> select vencode, venname, tel_no from vendor_master where venname like R%’;
The above command will display fields of the columns vencode,venname and tel_no for those names in venname that begins with ‘R’.
VENCO VENNAME TEL NO
——- —————— ——–
v003 Ramesh 231218
v005 Raju 621453
Example :
SQL> select vencode, venname, tel_no from vendor_master where venname like ‘R_j’;
The above command lists all the columns from customer table whose names are three letters long, starting with ‘R’ and ending with ‘j’.
VENCO VENNAME TEL_NO
——- ——————- ——–
V005 Raj 621453
Logical operators
A logical operator is used to combine the results of two conditions to produce a single result. The logical operators are AND, NOT and OR. The following exemplifies the use of AND operator. All the columns from order_master table are displayed provided both the conditions mentioned below are satisfied.
The query in the next example is used frequently by the accounts department of XYZ ENTERPRISES to get information about orders between two particular dates so that the required bills can be prepared well in advance.
Example :
SQL> select * from order_master where odate>’10-may-98′ and del_date< ’26-may-98′;
The records displayed will be
ORDER ODATE VENCO O DEL_DATE
——- ———– ——- – ———-
0001 12-MAY-98 v002 c 25-MAY-98
0002 14-MAY-98 v001 P 25-MAY-98
We have now gone through the various operators supported by SQL*Plus. Now let us have a look at the order in which these operators are evaluated.
Set Operators
Set operators combine the results of two queries into a single one. The following set operators aid SQL in joining queries to retrieve rows. Now let us discuss them in detail.
◆ Union
◆ Union all
◆ Intersect
◆ Minus
The columns in the select statements joined using the set operators should adhere strictly to the rules mentioned below.
◆ The queries, which are related by a set operator should have the same number of columns and the corresponding columns, must be of the same datatype.
◆ Such a query should not contain any column of type long.
◆ The label under which the rows are displayed are those from the first select statement.
Union
The union operator returns all distinct rows selected by both queries. The following example combines the result of two queries with the union operator, which eliminates duplicate rows.
It is now safely assumed that usage of DDL and DML commands have been familiarized with. A table order_detail should be created, and records inserted into it. The structure of the table is available in the appendix
XYZ enterprises would like to have a consolidated look of those orders which are currently bring processed.
Example :
SQL> select orderno from order_master union select orderno from order_detail;
The union operator returns all distinct column values from the order_master and order_detail table respectively.
ORDER
——–
O001
O002
O003
O006
o015
Union all
The ‘union all’ operator returns all rows selected by either query including duplicates. The following example combines the result with the aid of the ‘union all’ operator, which does not eliminate duplicate rows.
Example :
SQL> select orderno from order_master union all select orderno from order_detail;
The output for this will be
ORDER
——-
o001
o002
o003
o006
o015
o001
o001
o001
o002
On taking a closer look it can be noticed that while the query in example 3.1 returns only a unique set of values the query in the previous example returns all including duplicates.
Note: It is not necessary that the columns specified in the two select statements be the same but the datatype must necessarily match. For instance instead of orderno any other column can be specified in one of the select statements but the datatypes should match.
While using the ‘order by’ clause, it must follow the last select statement and we must order by integer and not by column name. Consider the following example.
Example :
SQL> select qty_hand, max_level from itemfile union
select qty_ord, qty_deld from order_detail order by 2;
The above example displays distinct rows selected by either query, ordered by the second column stated in both the queries. Since the column names are different in the above queries, we use an integer in the ‘order by’ clause instead of a column name.
Intersect
Intersect operator returns only rows that are common to both the queries.
The following example is illustrative of the above statement.
Consider the following situation. Due to a severe resource crunch in XYZ ENTERPRISES, immediate attention is to be given to those orders which are in the pipeline. Information about orders which are in the process of completion or being delivered can be got using the following query.
Example :
SQL> select orderno from order_master intersect select orderno from order_detail;
The orderno common to both tables will be listed.
ORDER
——-
o001
o002
Minus
Minus operator returns all distinct rows selected only by the first query and not by the second. The following example illustrates this.
To find out the orders for which delivery has not started as yet the following simple query will provide the necessary information
Example :
SQL> select orderno from order_master minus
select orderno from order_detail;
The result of this query will be all rows selected by the first select statement except those common to the statement following the minus operator.
ORDER
——-
o003
o006
o015
Operator precedence
The precedence of the operators discussed above are given below:-
Arithmetic operators ——– highest precedence
Comparison operators
NOT logic operator
AND logic operator
OR logic operator ——–lowest precedence
Note: The order of precedence could be altered using parenthesis.