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;