Assignment 4 (Single Row Functions)

-- 1. Display current date labeled as Date
SELECT SYSDATE AS "Date"
FROM dual;

-- Expected Output:
-- Date
-- 31-OCT-25


-- 2. Emp no, last_name, salary & new salary (15% increment, whole number)
SELECT employee_id,
       last_name,
       salary,
       TRUNC(salary * 1.15) AS "New Salary"
FROM employees;

-- Expected Output Format:
-- EMPLOYEE_ID | LAST_NAME | SALARY | New Salary
-- 101         | Smith     | 4000   | 4600


-- 3. Last names formatted, length, starts with J/A/M & sorted
SELECT INITCAP(last_name) AS "Name",
       LENGTH(last_name) AS "Length"
FROM employees
WHERE UPPER(last_name) LIKE 'J%'
   OR UPPER(last_name) LIKE 'A%'
   OR UPPER(last_name) LIKE 'M%'
ORDER BY last_name;

-- Expected Output Format:
-- Name    | Length
-- James   | 5
-- Miller  | 6


-- 4. Last name & months worked since hire
SELECT last_name,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS "MONTHS_WORKED"
FROM employees
ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date);

-- Expected Output Format:
-- LAST_NAME | MONTHS_WORKED
-- Brown     | 122.5


-- 5. "<name> earns <salary> monthly but wants <3x salary>"
SELECT last_name || ' earns ' || salary ||
       ' monthly but wants ' || (salary * 3) AS "Dream Salaries"
FROM employees;

-- Expected Output Format:
-- Smith earns 4000 monthly but wants 12000


-- 6. Last name & salary padded left with $, total width 15
SELECT last_name,
       LPAD(salary, 15, '$') AS "SALARY"
FROM employees;

-- Expected Output Format:
-- LAST_NAME | SALARY
-- Smith     | $$$$$$$$$$$4000


-- 7. Review date = 1st Monday after 6 months of hire
SELECT last_name,
       hire_date,
       TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY'),
               'FMDay, "the" DDth "of" Month, YYYY') AS "REVIEW"
FROM employees;

-- Expected Output Format:
-- LAST_NAME | HIRE_DATE | REVIEW
-- Smith     | 12-JAN-24 | Monday, the 15th of July, 2024


-- 8. Last name & commission - show "No Commission" if null
SELECT last_name,
       NVL(TO_CHAR(commission_pct), 'No Commission') AS "COMM"
FROM employees;

-- Expected Output Format:
-- LAST_NAME | COMM
-- Smith     | No Commission
-- Miller    | 0.2