SQL exercises on employee Database: List the employees with their location and grade for MARKETING department who comes from MELBOURNE or PERTH within the grade 3 to 5 and experience over 25 years

SQL employee Database: Exercise-62 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

62. From the following table, write a SQL query to find those employees of MARKETING department come from MELBOURNE or PERTH within the grade 3 ,4, and 5 and experience over 25 years. Return department ID, employee ID, employee name, salary, department name, department location and grade.

Pictorial Presentation:

Sample table: employees

Sample table: salary_grade

Sample table: department

Sample Solution:

SELECT e.dep_id,
       e.emp_id,
       e.emp_name,
       e.salary,
       d.dep_name,
       d.dep_location,
       s.grade
FROM employees e,
     salary_grade s,
     department d
WHERE e.dep_id = d.dep_id
  AND e.salary BETWEEN s.min_sal AND s.max_sal
  AND s.grade IN (3,4,
                  5)
  AND EXTRACT(YEAR
              FROM age(CURRENT_DATE, hire_date)) > 25
  AND (d.dep_name = 'MARKETING'
       AND D.dep_location IN ('MELBOURNE',
                              'PERTH'));

Sample Output:

dep_id |emp_id |emp_name |salary  |dep_name  |dep_location |grade |
-------|-------|---------|--------|----------|-------------|------|
3001   |66928  |BLAZE    |2750.00 |MARKETING |PERTH        |4     |
3001   |64989  |ADELYN   |1700.00 |MARKETING |PERTH        |3     |
3001   |68454  |TUCKER   |1600.00 |MARKETING |PERTH        |3     |

Practice Online

Sample Database: employee

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those employees whose department location is SYDNEY or MELBOURNE with a salary range of 2000, 5000 (Begin and end values are included.) and joined in 1991. Return employee ID, employee name, department ID, salary, and department location.
Next: From the following table, write a SQL query to find those employees who are senior to their manager. Return complete information about the employees.