The rules of precedence determine the order in which expressions are evaluated and calculated The next table lists the default order of precedence You can override the default order by using ID: 600687
Download Presentation The PPT/PDF document "Rules of Precedence" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Rules of Precedence
The rules of precedence determine the
order
in which expressions are evaluated
and
calculated
.
The next table lists the default order of precedence.
You can override the default order by using
parentheses
around the expressions you want to calculate first.Slide2
Rules of Precedence
Order Evaluated
Operator
1
Arithmetic operators
2
Concatenation operator
3
Comparison conditions
4
IS [NOT] NULL, LIKE, [NOT] IN
5
[NOT] BETWEEN
6
NOT logical condition
7
AND logical condition
8
OR logical conditionSlide3
Rules of Precedence (Example)Slide4
Rules of Precedence (Example)
In the previews slide there are two conditions:
The first condition is that the job ID is AD_PRES and the salary is greater than $15,000.
The second condition is that the job ID is SA_REP.
Based on the precedence rules, the SELECT statement reads as follows:
“
Select the row if an employee is a president and earns more than $15,000, or if the employee is a sales representative
.
”Slide5
Rules of Precedence (Example)Slide6
Rules of Precedence (Example)
In
thepreviews
example, there are two conditions:
The first condition is that the job ID is AD_PRES or SA_REP.
The second condition is that salary is greater than $15,000.
Based on the precedence rules, the SELECT statement reads as follows:
“
Select the rows if the employee is president or sales representative, and if the employee earn more than 15,000$
”Slide7
Order by caluseSlide8
Sorting resulted rowsSQL allows sorting resulted rows by using the
ORDER BY
clause
in
:
ASC:
ascending order (the default order) .(see
Example 10
)
DESC:
descending order.(see
Example11
)
The ORDER BY clause comes last in the SELECT statementSlide9
Example 10Slide10
Example 11Slide11
Sorting by Column AliasSlide12
Sorting by Multiple Columns Slide13
Select statement syntax with the (Where & order by clauses)Slide14
Comments on Using Logical operator (NOT)In term of syntax, generally,
NOT
comes between
exper
and
comparison operator
E.g
Select
fname
, age
Feom
emp_table
Where
dept_num
NOT
IN(1,2)
;Slide15
Comments on Using Logical operator (NOT)This syntax is right for the operators (
IN, Between.. And .., LIKE, IS NULl
)
BUT
In case the symbolic comparison operator (>,<, >=,<=,=,<>) there will be an
error
E.g
Select
fname
, age
Feom
emp_table
Where
dept_num
NOT
>3
;Slide16
Comments on Using Logical operator (NOT)The Solution is to use NOT pefore the whole comparsion condition i.e.
NOT
(exper comparison operator)
E.g
Select
fname
, age
Feom
emp_table
Where
NOT
(
dept_num
>3)
;Slide17
Comments on ordering table using more than one column
Assume that we’ve created the following table:
Then fill it with the values
Create table test2( col1 number(2), col2 number(2));
col2
Col1
9
1
8
2
10
3
10
2
5
3
4
4
4
3Slide18
Comments on ordering table using more than one column
The result of the query
Select *
From test2
Oreder
by col2,col1;
col2
Col1
4
3
4
4
5
3
8
2
9
1
10
2
10
3
And it’s NOT the same as ordering based on the last column (col2) which is:
col2
Col1
4
4
4
3
5
3
8
2
9
1
10
3
10
2