SELECT query to find top 15 active employees

1

I build an SQL query to find the active employees.

Directives

Sort the output in descending order based on employee id column and fetch only the first 15 records.

Display employee id, employee name, city, state, country,salary, active date, status, department name, manager name.

Special formatting:

  • employee name: concatenate firstname & lastname with space character and fetch only first 10 letters from employee name
  • manager name: display firstname and lastname (separated by space)

Tables

  • EMPLOYEE dept_id,emp_activefrom, emp_dob, emp_fname, emp_lname, emp_sal, emp_status, emp_terminationdate, loc_id, mgr_id
  • LOCATION : city, country, loc_id, state
  • DEPARTMENT : dept_head, dept_id, dept_name

This is the code I used

SELECT 
    e.emp_id, 
    SUBSTR(e.emp_fname || ' ' || e.emp_lname, 1, 10) AS emp_name, 
    l.city, l.state, l.country, 
    e.emp_sal, e.emp_activefrom, e.emp_status,
    d.dept_name, d.dept_head, 
    m.emp_fname || ' ' || m.emp_lname AS manager_name
FROM 
    employee e 
INNER JOIN
    department d ON e.dept_id = d.dept_id 
INNER JOIN
    location l ON e.loc_id = l.loc_id 
INNER JOIN 
    employee m ON e.Mgr_id = m.Emp_Id
WHERE
    e.emp_status = 'Active' AND rownum <= 15
ORDER BY
    e.Emp_Id DESC;

this code not working

2

Welcome to the forum.
I am not sure which SQL you are using. My answer is for T-SQL (Microsoft SQL Server).
Note this is one way to do it another would be use the Row_Number function. Something else to watch for is nulls in the name to which the ISNULL function might be of help.

SELECT TOP(15)
    e.emp_id, 
    LEFT(e.emp_fname + ' ' + e.emp_lname, 10) AS emp_name, 
    l.city, l.state, l.country, 
    e.emp_sal, e.emp_activefrom, e.emp_status,
    d.dept_name, d.dept_head, 
    m.emp_fname + ' ' + m.emp_lname AS manager_name
FROM 
    employee e 
INNER JOIN
    department d ON e.dept_id = d.dept_id 
INNER JOIN
    location l ON e.loc_id = l.loc_id 
INNER JOIN 
    employee m ON e.Mgr_id = m.Emp_Id
WHERE
    e.emp_status = 'Active' AND rownum <= 15
ORDER BY
    e.Emp_Id DESC;
3

Welcome

Can you please define what does not work means?

4

Found my mistake thanks :slight_smile: