SCALAR FUNCTION: DATE, NUMERIC, STRING, CONVERSION, GENERAL & NULL

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name, hire date, and current date. Also calculate the number of days the employee has worked.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and hire date in the format: Monday, January 01, 2024.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Find employees who joined in the same month as the current system date (ignore year).

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and the next Monday after their hire date.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and the last day of their joining month.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Find employees who have completed more than 10 years in the company.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and number of months worked in the company.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name, hire date, and hire date after adding 3 years and 6 months.

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Find employees hired between two given dates (use TO_DATE).

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display the difference in years between SYSDATE and hire date (rounded).

(DATE FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and salary rounded to the nearest 1000.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display salary truncated to 2 decimal places.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and square root of salary.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Find the absolute difference between salary and 5000.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display salary rounded up to the nearest integer.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display salary rounded down to the nearest integer.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Generate a random number between 1 and 500 for each employee.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display remainder when salary is divided by 2000.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display salary raised to the power of 3.

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Round salary to nearest hundred and thousand (two columns).

(NUMERIC FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee names in uppercase and lowercase in two columns.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and its length.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display first 4 characters of employee name.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Concatenate employee name and job using - symbol.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Replace all occurrences of letter ‘A’ with ‘#’ in employee names.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display position of first occurrence of ‘E’ in employee name.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Remove leading and trailing spaces from employee name.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name with first letter capital and rest lowercase.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display last 3 characters of employee name.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Pad employee name with * on the left to make total length 10.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name with spaces removed.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee names starting with ‘S’.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Count number of characters excluding spaces.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name reversed.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Extract domain name from email column.

(STRING FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert current date into format: DD/MM/YYYY.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert string '15-AUG-2020' into DATE format.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert salary into character with currency format (₹10,000).

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert number 12345 into string with commas.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert hire date into format: Month DD, YYYY.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert string column to number and perform arithmetic operation.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Handle invalid date format using TO_DATE safely.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert SYSDATE into year only.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Convert salary into string and concatenate with text.

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display hire date in multiple formats (3 columns).

(CONVERSION FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and salary. If salary is NULL, display 0.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and commission. If NULL, display ‘No Commission’.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use NVL2 to show commission status (Has/No Commission).

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use COALESCE to display first non-null value among salary, commission, bonus.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee salary; if NULL replace with average salary.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use NULLIF to compare salary and commission.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Replace NULL department name with ‘No Department’.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NULL.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use DECODE to categorize salary (Low/Medium/High).

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use CASE to categorize employees based on experience.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display job; if NULL show ‘Unknown Job’.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use NVL with date columns.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and bonus; if NULL calculate 10% of salary.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Use COALESCE with multiple NULL columns.

(GENERAL & NULL FUNCTION)

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Combine NVL and TO_CHAR for formatted output.

(GENERAL & NULL FUNCTION)