SQL Non-Character Functions - Exercise

Contact Us or call 1-877-932-8228
SQL Non-Character Functions - Exercise

SQL Non-Character Functions

Duration: 10 to 15 minutes.
  1. Create a query that selects the department and average salary from the emp_details_view.
  2. Modify the query so that the average salary is truncated.
  3. Modify the query so that the average salary is rounded.
  4. Format the salary so that it includes a dollar sign, decimal point and commas (Hint: $999,999.00).
  5. Create a query to select the job_id, start_date, end_date from the job_history table.
  6. Modify the query so that the start date appears as MM/DD/YYYY format and the end date appears in DAY MONTH D, YYYY format.
  7. Round the months between the start date and end date for each record.

Solution:

SQL-Functions-Non-Character/Solutions/solutions_non_char.sql
SELECT 
department_name, 
avg(salary) 
FROM emp_details_view 
GROUP BY department_name;

SELECT department_name, trunc(avg(salary)) 
FROM emp_details_view 
GROUP BY department_name;

SELECT department_name, round(avg(salary)) 
FROM emp_details_view 
GROUP BY department_name;

SELECT department_name,to_char(avg(salary),'$999,999.00') 
FROM emp_details_view 
GROUP BY department_name;

SELECT job_id, start_date, end_date 
FROM job_history;

SELECT 
	job_id, 
	to_char(start_date,'MM/DD/YYYY'), 
	TO_CHAR(end_date,'DAY MONTH D, YYYY') 
FROM job_history;

SELECT 
	job_id, 
	round(months_between(end_date, start_date)) 
FROM job_history;
Next