Skip to content

Answers to SQL problems

  1. SELECT LOWER(email) || '@hr.co.uk' FROM employees;

  2. SELECT 'Salaries in the company range from $' || TO_CHAR(MIN(salary)) || ' to $' || TO_CHAR(MAX(salary)) || ' per month' FROM employees;

  3. SELECT last_name || ', ' || SUBSTR(first_name,1,1) AS name FROM employees WHERE job_id = 'IT_PROG' ORDER BY name;

  4. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

  5. SELECT department_id, ROUND(AVG(salary)/1000,1) AS "Average salary" FROM employees GROUP BY department_id;

  6. SELECT job_title, min_salary, max_salary, max_salary-min_salary AS Range FROM jobs;

  7. SELECT job_title, min_salary, max_salary, max_salary-min_salary AS Range FROM jobs ORDER BY Range DESC;

  8. SELECT TO_CHAR(hire_date,'YYYY') AS Hired, COUNT(*) AS "New employees" FROM employees GROUP BY to_char(hire_date,'YYYY') ORDER BY Hired;

  9. SELECT last_name, NVL(department_id,0) AS Dept FROM employees WHERE job_id = 'SA_REP';

  10. SELECT last_name, NVL(TO_CHAR(department_id),'No dept id') AS Dept FROM employees WHERE job_id = 'SA_REP';