Different Types of SQL-02

Filter:

SELECT e.first_name, e.salary, d.department_id, d.department_name
FROM employees e
     JOIN departments d ON e.department_id= d.department_id
WHERE d.department_id=20
AND e.salary >=2000
ORDER BY e.first_name ;

IN and NOT IN Conditions:
IN condition is the value for true from the list of values.
NOT IN condition is the value for false from the list of values.

SELECT e.department_id, e.employee_id, e.first_name
FROM employees e
WHERE e.department_id IN (10,20,50)
ORDER BY e.department_id, e.employee_id ;

SELECT e.department_id, e.employee_id, e.first_name
FROM employees e
WHERE e.department_id NOT IN (10,20,50)
ORDER BY e.department_id, e.employee_id ;

EXISTS and NOT EXISTS Conditions:

SELECT d.department_id, d.department_name
FROM   departments d
WHERE  EXISTS (SELECT 1
               FROM   employees e
               WHERE  d.department_id = e.department_id)
ORDER BY d.department_id;

BETWEEN and NOT BETWEEN Conditions:

SELECT d.department_id, d.department_name
FROM   departments d
WHERE  department_id NOT BETWEEN 20 AND 40
ORDER BY d.department_id;


SELECT d.department_id, d.department_name
FROM   departments d
WHERE  department_name NOT LIKE '%n'
ORDER BY d.department_id;