/*===============================================================
WHERE CLAUSE AND OPERATORS
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 02A β WHERE CLAUSE AND OPERATORS
Topics Covered
--------------
β’ WHERE clause basics
β’ Relational operators
β’ Logical operators
β’ IN operator
β’ BETWEEN operator
β’ LIKE operator
β’ NULL handling
Schema Used
-----------
HR
===============================================================*/
/*---------------------------------------------------------------
WHERE CLAUSE INTRODUCTION
---------------------------------------------------------------*/
/*
The WHERE clause filters rows returned by a query.
Syntax
SELECT columns
FROM table
WHERE condition;
*/
/*---------------------------------------------------------------
QUESTION 1
Display employees earning more than 10000
---------------------------------------------------------------*/
SELECT
employee_id,
first_name,
salary
FROM hr.employees
WHERE salary > 10000;
/*---------------------------------------------------------------
QUESTION 2
Employees earning less than 5000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary < 5000;
/*---------------------------------------------------------------
QUESTION 3
Employees earning exactly 9000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary = 9000;
/*---------------------------------------------------------------
RELATIONAL OPERATORS
---------------------------------------------------------------*/
/*
=
>
<
>=
<=
<>
*/
/*---------------------------------------------------------------
QUESTION 4
Employees not earning 5000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <> 5000;
/*---------------------------------------------------------------
QUESTION 5
Employees earning at least 10000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >= 10000;
/*---------------------------------------------------------------
QUESTION 6
Employees earning at most 3000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <= 3000;
/*---------------------------------------------------------------
LOGICAL OPERATORS
---------------------------------------------------------------*/
/*
AND
OR
NOT
*/
/*---------------------------------------------------------------
QUESTION 7
Employees in department 60 earning more than 5000
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
department_id
FROM hr.employees
WHERE department_id = 60
AND salary > 5000;
/*---------------------------------------------------------------
QUESTION 8
Employees in department 60 or 90
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id = 60
OR department_id = 90;
/*---------------------------------------------------------------
QUESTION 9
Employees not in department 90
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE NOT department_id = 90;
/*---------------------------------------------------------------
IN OPERATOR
---------------------------------------------------------------*/
/*
IN replaces multiple OR conditions.
*/
/*---------------------------------------------------------------
QUESTION 10
Employees in departments 60, 90, 100
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id IN (60,90,100);
/*---------------------------------------------------------------
QUESTION 11
Employees NOT in departments 60 and 90
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id NOT IN (60,90);
/*---------------------------------------------------------------
BETWEEN OPERATOR
---------------------------------------------------------------*/
/*
BETWEEN includes both limits.
*/
/*---------------------------------------------------------------
QUESTION 12
Employees earning between 5000 and 10000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary BETWEEN 5000 AND 10000;
/*---------------------------------------------------------------
QUESTION 13
Equivalent BETWEEN query
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >= 5000
AND salary <= 10000;
/*---------------------------------------------------------------
LIKE OPERATOR
---------------------------------------------------------------*/
/*
% = any number of characters
_ = single character
*/
/*---------------------------------------------------------------
QUESTION 14
Employees whose names start with 'S'
---------------------------------------------------------------*/
SELECT
first_name
FROM hr.employees
WHERE first_name LIKE 'S%';
/*---------------------------------------------------------------
QUESTION 15
Employees whose names end with 'n'
---------------------------------------------------------------*/
SELECT
first_name
FROM hr.employees
WHERE first_name LIKE '%n';
/*---------------------------------------------------------------
QUESTION 16
Employees whose names contain 'ar'
---------------------------------------------------------------*/
SELECT
first_name
FROM hr.employees
WHERE first_name LIKE '%ar%';
/*---------------------------------------------------------------
QUESTION 17
Employees with second letter 'a'
---------------------------------------------------------------*/
SELECT
first_name
FROM hr.employees
WHERE first_name LIKE '_a%';
/*---------------------------------------------------------------
NULL VALUES
---------------------------------------------------------------*/
/*
NULL represents unknown values.
Must use
IS NULL
IS NOT NULL
*/
/*---------------------------------------------------------------
QUESTION 18
Employees with NULL commission
---------------------------------------------------------------*/
SELECT
employee_id,
commission_pct
FROM hr.employees
WHERE commission_pct IS NULL;
/*---------------------------------------------------------------
QUESTION 19
Employees with commission
---------------------------------------------------------------*/
SELECT
employee_id,
commission_pct
FROM hr.employees
WHERE commission_pct IS NOT NULL;
/*---------------------------------------------------------------
QUESTION 20
Employees without manager
---------------------------------------------------------------*/
SELECT
employee_id,
manager_id
FROM hr.employees
WHERE manager_id IS NULL;
/*---------------------------------------------------------------
ADVANCED FILTERING
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 21
Employees hired after 2005
---------------------------------------------------------------*/
SELECT
employee_id,
hire_date
FROM hr.employees
WHERE hire_date > TO_DATE('01-JAN-2005','DD-MON-YYYY');
/*---------------------------------------------------------------
QUESTION 22
Employees hired before 2003
---------------------------------------------------------------*/
SELECT
employee_id,
hire_date
FROM hr.employees
WHERE hire_date < DATE '2003-01-01';
/*---------------------------------------------------------------
QUESTION 23
Employees in IT department
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id =
(
SELECT department_id
FROM hr.departments
WHERE department_name = 'IT'
);
/*---------------------------------------------------------------
QUESTION 24
Employees earning more than 15000
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary > 15000;
/*---------------------------------------------------------------
QUESTION 25
Employees hired in year 2007
---------------------------------------------------------------*/
SELECT
employee_id,
hire_date
FROM hr.employees
WHERE EXTRACT(YEAR FROM hire_date) = 2007;
/*---------------------------------------------------------------
PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Best Practices
1 Use indexed columns in WHERE clause
2 Avoid functions on indexed columns
3 Filter rows early
4 Use bind variables for applications
*/
/*---------------------------------------------------------------
END OF PART 02A
---------------------------------------------------------------*/
/*===============================================================
PART 02B β ADVANCED WHERE CLAUSE
Continuation of PART 02A
Topics Covered
--------------
β’ ANY operator
β’ ALL operator
β’ EXISTS
β’ Row comparisons
β’ Advanced filtering
β’ HR schema practice queries
===============================================================*/
/*---------------------------------------------------------------
ANY OPERATOR
---------------------------------------------------------------*/
/*
ANY means comparison with at least one value
returned by the subquery.
*/
/*---------------------------------------------------------------
QUESTION 26
Employees earning more than ANY employee in dept 60
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
ANY
(
SELECT salary
FROM hr.employees
WHERE department_id = 60
);
/*---------------------------------------------------------------
QUESTION 27
Employees earning less than ANY salary in department 50
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <
ANY
(
SELECT salary
FROM hr.employees
WHERE department_id = 50
);
/*---------------------------------------------------------------
ALL OPERATOR
---------------------------------------------------------------*/
/*
ALL means comparison must be true
for every value returned by the subquery.
*/
/*---------------------------------------------------------------
QUESTION 28
Employees earning more than ALL employees in department 60
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
ALL
(
SELECT salary
FROM hr.employees
WHERE department_id = 60
);
/*---------------------------------------------------------------
QUESTION 29
Employees earning less than ALL employees in department 100
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <
ALL
(
SELECT salary
FROM hr.employees
WHERE department_id = 100
);
/*---------------------------------------------------------------
EXISTS OPERATOR
---------------------------------------------------------------*/
/*
EXISTS checks whether the subquery returns rows.
*/
/*---------------------------------------------------------------
QUESTION 30
Departments that contain employees
---------------------------------------------------------------*/
SELECT
department_name
FROM hr.departments d
WHERE EXISTS
(
SELECT 1
FROM hr.employees e
WHERE e.department_id = d.department_id
);
/*---------------------------------------------------------------
QUESTION 31
Departments with no employees
---------------------------------------------------------------*/
SELECT
department_name
FROM hr.departments d
WHERE NOT EXISTS
(
SELECT 1
FROM hr.employees e
WHERE e.department_id = d.department_id
);
/*---------------------------------------------------------------
ROW COMPARISON OPERATORS
---------------------------------------------------------------*/
/*
Row comparisons allow comparison
of multiple columns.
*/
/*---------------------------------------------------------------
QUESTION 32
Find employees matching department and salary combination
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
department_id
FROM hr.employees
WHERE (salary, department_id) =
(
10000,
90
);
/*---------------------------------------------------------------
QUESTION 33
Find employees with highest salary per department
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
department_id
FROM hr.employees
WHERE (department_id, salary)
IN
(
SELECT
department_id,
MAX(salary)
FROM hr.employees
GROUP BY department_id
);
/*---------------------------------------------------------------
ADVANCED FILTERING
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 34
Employees hired after their manager
---------------------------------------------------------------*/
SELECT
employee_id,
hire_date,
manager_id
FROM hr.employees e
WHERE hire_date >
(
SELECT hire_date
FROM hr.employees m
WHERE m.employee_id = e.manager_id
);
/*---------------------------------------------------------------
QUESTION 35
Employees earning above company average
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
);
/*---------------------------------------------------------------
QUESTION 36
Employees earning below company average
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary <
(
SELECT AVG(salary)
FROM hr.employees
);
/*---------------------------------------------------------------
QUESTION 37
Employees in departments located in Seattle
---------------------------------------------------------------*/
SELECT
employee_id
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.departments
WHERE location_id IN
(
SELECT location_id
FROM hr.locations
WHERE city = 'Seattle'
)
);
/*---------------------------------------------------------------
QUESTION 38
Employees working in departments with more than 5 employees
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.employees
GROUP BY department_id
HAVING COUNT(*) > 5
);
/*---------------------------------------------------------------
QUESTION 39
Employees earning top salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary =
(
SELECT MAX(salary)
FROM hr.employees
);
/*---------------------------------------------------------------
QUESTION 40
Employees earning lowest salary
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary =
(
SELECT MIN(salary)
FROM hr.employees
);
/*---------------------------------------------------------------
QUESTION 41
Employees with same salary as employee 100
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
WHERE salary =
(
SELECT salary
FROM hr.employees
WHERE employee_id = 100
);
/*---------------------------------------------------------------
QUESTION 42
Employees working in same department as employee 101
---------------------------------------------------------------*/
SELECT
employee_id,
department_id
FROM hr.employees
WHERE department_id =
(
SELECT department_id
FROM hr.employees
WHERE employee_id = 101
);
/*---------------------------------------------------------------
QUESTION 43
Employees earning above department average
---------------------------------------------------------------*/
SELECT
employee_id,
salary,
department_id
FROM hr.employees e
WHERE salary >
(
SELECT AVG(salary)
FROM hr.employees
WHERE department_id = e.department_id
);
/*---------------------------------------------------------------
QUESTION 44
Employees hired before their department average hire date
---------------------------------------------------------------*/
SELECT
employee_id,
hire_date
FROM hr.employees e
WHERE hire_date <
(
SELECT AVG(hire_date)
FROM hr.employees
WHERE department_id = e.department_id
);
/*---------------------------------------------------------------
QUESTION 45
Employees with highest salary in company
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 1 ROW ONLY;
/*---------------------------------------------------------------
QUESTION 46
Employees with lowest salary in company
---------------------------------------------------------------*/
SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary
FETCH FIRST 1 ROW ONLY;
/*---------------------------------------------------------------
PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Filtering Best Practices
1 Use indexes on filtering columns
2 Prefer EXISTS for large subqueries
3 Avoid unnecessary nested subqueries
4 Use bind variables in applications
5 Avoid functions on indexed columns
*/
/*---------------------------------------------------------------
END OF PART 02
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/