Valuing Lesson - Task 1-4 - WEEK 3-4 - PERFORMANCE TASK: 2-3

DROP TABLE employees;

-- VALUING LESSON
CREATE TABLE employees(
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(15),
      last_name VARCHAR(15),
    emp_department VARCHAR(10),
      hire_date DATE,
    emp_salary DECIMAL (10,4)
);

INSERT INTO employees VALUES
(1, 'Gaudenz', 'Padullon', 'IT', DATE '2023-01-11' , 511000.0000);

INSERT INTO employees VALUES
(2, 'Je Ar', 'Raguingan', 'Sales', DATE  '2023-01-12', 20000.0000);

INSERT INTO employees VALUES
(3, 'Khier', 'Lapurga', 'IT', DATE  '2023-01-23', 60000.0000);

INSERT INTO employees VALUES
(4, 'John', 'Credo', 'Sales', DATE  '2023-01-31', 70000.0000);

INSERT INTO employees VALUES
(5, 'Frederick', 'Santigo', 'HR', DATE '2023-01-31', 40000.0000);

INSERT INTO employees VALUES
(6, 'Barron', 'Obana', 'IT', DATE  '2023-07-12', 20000.0000);

INSERT INTO employees VALUES
(7, 'Azer', 'Marayag', 'IT', DATE  '2023-07-12', 80000.0000);

INSERT INTO employees VALUES
(8, 'Cedric', 'Acong', 'Sales', DATE  '2024-06-13', 10000.0000);

INSERT INTO employees VALUES
(9, 'Jules', 'Manila', 'IT', DATE  '2024-06-13', 50000.0000);

INSERT INTO employees VALUES
(10, 'Khenjie', 'Amosco', 'HR', DATE '2024-06-13', 45000.0000);


SELECT * FROM employees;

-- TASK 1 : String Function
SELECT LOWER(first_name) AS "Lowercase name"
FROM employees;

-- TASK 2: Number Function
SELECT
    emp_id,
    first_name || ' ' || last_name AS "Full name",
    ROUND(emp_salary) AS "Rounded Salary"
FROM employees;

-- TASK 3: Date Function
SELECT
    emp_id,
    first_name || ' ' || last_name AS "Full name",
    ROUND(
      MONTHS_BETWEEN(SYSDATE,hire_date)
          ) AS "Months Worked"
FROM employees;

-- TASK 4: Conversion Function
SELECT
    emp_id,
    first_name || ' ' || last_name AS "Full name",
    TO_CHAR(emp_salary, '$999,999.99') AS "Formatted Salary"
FROM employees;


-- Performance Task 2-2
SELECT
  UPPER(last_name) AS "Uppercase Last Names",
  ROUND(emp_salary) AS "Rounded Salary",
  ROUND(MONTHS_BETWEEN(SYSDATE,hire_date)) AS "Months Worked",
  '₱' || TO_CHAR(emp_salary, 'FM999,999,999') AS "Formatted Salary"
FROM employees;

-- https://onecompiler.com/oracle/42va7rf97