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;

Different Types of SQL-01

Inline Views:

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

WITH Clause:

WITH emp_dept AS (
  SELECT e.first_name, d.department_name
  FROM   employees e, departments d
  WHERE  d.department_id = e.department_id
)
SELECT ed.first_name, ed.department_name
FROM   emp_dept ed
ORDER BY ed.first_name;

Views:

CREATE OR REPLACE VIEW emp_dept_join_v AS
  SELECT e.first_name, d.department_name
  FROM   employees e, departments d
  WHERE  d.department_id = e.department_id;
 
SELECT ed.first_name, ed.department_name
FROM   emp_dept_join_v ed
ORDER BY ed.first_name;

Different Types of SQL-00

Wildcard "*":

SELECT e.*, d.*
FROM employees e
    JOIN departments d ON d.department_id=e.department_id
ORDER BY e.employee_id ;

Column Aliases:

SELECT employee_id AS Employee_No, first_name||' '||last_name AS Full_Name
FROM employees
ORDER BY employee_id ;

Table Aliases:

SELECT e.employee_id,
       e.first_name ||' '|| e.last_name AS Full_Name,
       d.department_id,
       d.department_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;

Functions:

SELECT UPPER('Lowercase text') AS text FROM dual ;

Expressions:

SELECT 1+2 AS addition
FROM dual ;

Scalar Subqueries:

SELECT d.department_id, d.department_name,
(SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id) AS Emp_Count
FROM departments d
ORDER BY d.department_id ;


SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS emp_count
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;