Single-Row Functions

• Write a query to display the current date. Label the column Date.

SELECT sysdate "DATE" 

FROM dual;

• For each employee display the employee ID number, last name, salary and salary increased by 15% and expressed as a whole number. Label the column New Salary.

SELECT employee_id, last_name, salary, ROUND(salary * 1.15, 0) "New Salary" 

FROM employees;

• Modify above query to add a column that subtracts the old salary from the new salary. Label the column Increase.

SELECT employee_id, last_name, salary, ROUND(salary * 1.15, 0) "New Salary", ROUND(salary * 1.15, 0) - salary "Increase" 

FROM employees;

• Write a query that displays the employee's last name with the first letter capitalized and all other letters lower case and the length of the names, for all employees whose name starts with J, A or M. Give each column an appropriate label. Sort the results by employee's last names.

SELECT INITCAP(last_name) "Name", LENGTH(last_name) "Length" 

FROM employees 

WHERE last_name LIKE 'J%' 

OR last_name LIKE 'A%' 

OR last_name LIKE 'M%' 

ORDER BY last_name;

• For each employee display the employee's last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.

SELECT last_name, ROUND(MOTHS_BETWEEN(sysdate, hire_date)) "MONTHS_WORKED" 

FROM employees 

ORDER BY MONTHS_BETWEEN(sysdate, hire_date);

•  Write a query that produces the following for each employee:
  <employee last name> earns <salary> monthly but wants <3 times salary>.
   Label the column Dream Salaries.

  Dream Salaries

  King earns $24,000.00 monthly but wants $72.000.00.

  Kochhar earns $17,000.00 monthly but wants $51.000.00.

  De Haan earns $17,000.00 monthly but wants $51.000.00.

  Hunold earns $9,000.00 monthly but wants $27.000.00.

  Emst earns $6,000.00 monthly but wants $18.000.00.

  Lorentz earns $4,200.00 monthly but wants $12.600.00.

  Morgous earns $5,800.00 monthly but wants $17.400.00.

SELECT last_name ||' earns '|| TO_CHAR(salary, 'fm$99,999.00') ||' monthly but wants '|| TO_CHAR(salary * 3, 'fm$99,999.00') ||'.' 

FROM employees;

•  Create a query to display the last name and salary for all employees. Format the salary to be 15.
    

  LAST_NAME	SALARY
  King	$$$$$$$$$$24000
  Kochhar	$$$$$$$$$$17000
  De Haan	$$$$$$$$$$17000
  Hunold	$$$$$$$$$$$9000
  Emst	$$$$$$$$$$$6000
  Lorentz	$$$$$$$$$$$4200
  Morgous	$$$$$$$$$$$5800

SELECT last_name, LPAD(salary, 15, '$') "SALARY" 

FROM employees;

• Display each employee's last name, hire date and salary review date, which is the first Monday after six months of service. Label the column Review. Format the dates to appear similar to "Monday, the Thirty-First of July, 2000."

SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY'), 'fmDay, "the" Ddspth "of" Month, YYYY') "REVIEW" 

FROM employees; 

• Display the last name, hire date and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.

SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') "DAY" 

FROM employees 

ORDER BY TO_CHAR(hire_date -1, 'd');

• Create a query that displays the employees last names and commission amounts. If an employee does not earn commission, put "No Commission." Label the column COMM. 

SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') "COMM" 

FROM employeees;

•  Create a query that displays the employees last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.

SELECT last_name ||' '|| RPAD(' ', salary/1000+1, '*') "EMPLOYEES_AND_THEIR_SALARIES"

FROM employees 

ORDER BY salary DESC;

• Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID,as per the following data.
   

  Job	Grade
  AD_PRES	A
  ST_MAN	B
  IT_PROG	C
  SA_REP	D
  ST_CLERK	E
  None of the above	0

 SELECT job_id, decode (job_id, 

                                             'ST_CLERK',       'E', 

                                             'SA_REP',           'D', 

                                             'IT_PROG',          'C', 

                                             'ST_MAN',           'B', 

                                             'AD_PRES',         'A', 

                                                                        '0') "G"     

FROM employees;

• Rewrite the statement in the preceding question using the CASE syntax

SELECT job_id, CASE job_id 

               WHEN   'ST_CLERK'     THEN 'E' 

               WHEN   'SA_REP'         THEN 'D' 

               WHEN   'IT_PROG'       THEN 'C' 

               WHEN   'ST_MAN'        THEN 'B' 

               WHEN   'AD_PRES'      THEN 'A' 

               ELSE      '0'    END "G" 

FROM employees;