Site icon IT Tutorial

AND, OR and NOT Operators in Oracle SQL | Oracle SQL Tutorials -5

Hi,

I will explain AND, OR and NOT Operators in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

SQL WHERE Clause and Comparison Operator in Oracle SQL | Oracle SQL Tutorials -4

 

AND, OR, and NOT operators are used in the Where clause in Oracle SQL. More than two or more Where condition can be combined with these operators.

 

AND Syntax

AND syntax is as follows. You can display only records that Both condition 1 AND condition 2 is TRUE or not.

SELECT column1, column2, ... FROM table_name
WHERE condition1 AND condition2;
List the deparments which are department id 30 AND location id 1700
SQL> 
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID=30 AND LOCATION_ID=1700;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           30 Purchasing                            114        1700

SQL> 
SQL> 


List the deparments which are department id (30 or 40 or 50 ) AND manager id 203

SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID IN(30,40,50) AND MANAGER_ID=203;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           40 Human Resources                       203        2400

SQL>

OR Syntax

OR syntax is as follows. You can display only records that EITHER condition 1 OR condition 2 is TRUE or not.

SELECT column1, column2, ... FROM table_name
WHERE condition1 OR condition2;

List the deparments which are department id less than 30 OR Salary greater than 10000.


SQL> 
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<30 OR SALARY>10000;

DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY
------------- -------------------- ------------------------- ----------
           90 Steven               King                           24000
           90 Neena                Kochhar                        17000
           90 Lex                  De Haan                        17000
          100 Nancy                Greenberg                      12008
           30 Den                  Raphaely                       11000
           80 John                 Russell                        14000
           80 Karen                Partners                       13500
           80 Alberto              Errazuriz                      12000
           80 Gerald               Cambrault                      11000
           80 Eleni                Zlotkey                        10500
           80 Clara                Vishney                        10500

DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY
------------- -------------------- ------------------------- ----------
           80 Lisa                 Ozer                           11500
           80 Ellen                Abel                           11000
           10 Jennifer             Whalen                          4400
           20 Michael              Hartstein                      13000
           20 Pat                  Fay                             6000
          110 Shelley              Higgins                        12008

17 rows selected.



List the Employees which are Salary less than 2500 OR Last name LIKE '%Kin%'

SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE SALARY<2500 OR LAST_NAME LIKE '%Kin%'; 

DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY
------------- -------------------- ------------------------- ----------
           90 Steven               King                           24000
           50 James                Landry                          2400
           50 Steven               Markle                          2200
           50 TJ                   Olson                           2100
           50 Ki                   Gee                             2400
           50 Hazel                Philtanker                      2200
           80 Janette              King                           10000

7 rows selected.

SQL>

 

 

 

NOT Syntax

NOT syntax is as follows. You can display only records that the conditions is NOT TRUE.

 

SELECT column1, column2, ... FROM table_name
WHERE NOT condition;

 

 

List the Employees which are NOT (department id 30 or Salary less than 10000) condition.

SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE NOT (DEPARTMENT_ID<30 OR SALARY<10000);

DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY
------------- -------------------- ------------------------- ----------
           30 Den                  Raphaely                       11000
           70 Hermann              Baer                           10000
           80 John                 Russell                        14000
           80 Karen                Partners                       13500
           80 Alberto              Errazuriz                      12000
           80 Gerald               Cambrault                      11000
           80 Eleni                Zlotkey                        10500
           80 Peter                Tucker                         10000
           80 Janette              King                           10000
           80 Clara                Vishney                        10500
           80 Lisa                 Ozer                           11500

DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY
------------- -------------------- ------------------------- ----------
           80 Harrison             Bloom                          10000
           80 Ellen                Abel                           11000
           90 Steven               King                           24000
           90 Neena                Kochhar                        17000
           90 Lex                  De Haan                        17000
          100 Nancy                Greenberg                      12008
          110 Shelley              Higgins                        12008

18 rows selected.

SQL>

List the Employees which are NOT (Salary less than 12000) condition. Namely, Query result will be Employee which Salary is greater than 12.000

SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE NOT SALARY<12000;

DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY
------------- -------------------- ------------------------- ----------
           90 Steven               King                           24000
           90 Neena                Kochhar                        17000
           90 Lex                  De Haan                        17000
          100 Nancy                Greenberg                      12008
           80 John                 Russell                        14000
           80 Karen                Partners                       13500
           80 Alberto              Errazuriz                      12000
           20 Michael              Hartstein                      13000
          110 Shelley              Higgins                        12008

9 rows selected.

SQL>

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

Exit mobile version