Create a Query That Will Display Employee Name and Department Number

SQL Query to display a list of employees with the name of the department and the name of the head

You can use the GROUP_CONCAT() function as follows:

SELECT 
    head.name as head ,
    department.name as department,
    GROUP_CONCAT(employee.name) as employess
FROM
    employee  

 INNER JOIN
    employee as head ON employee.chief_id = head.id

INNER JOIN
    department ON department.id = employee.department_id 

group by head.name,department.name

Query to display the the department names manager department name

Hard to tell based on the information given but I think you want:

select d.deptname as WorkerDept, md.deptname as ManagerDept
from Employee e
inner join Department d
   on (e.workdept = d.deptno)
inner join Department md
   on (d.admrdept = md.deptno)
where d.deptno != md.deptno

SQL query to show Name and Department in a table

You can do this with a couple of INNER JOINS. You don't need to reference the Location table in this query because Employee.LocationId is the same as EmployeeDepartment.LocationId.

This simple query will return all employee names and all department names they are related to. The employee name may be repeated, as this query puts only one department name in the column, so if an employee is in two departments, you get the employee name twice.

SELECT 
    EmployeeName = e.Name
    ,DepartmentName = d.Name 
FROM Employee e
INNER JOIN EmployeeDepartment ed ON ed.LocationId = e.LocationId
INNER JOIN Department d ON d.id = ed.DepartmentId

This query is a bit more complicated, but returns each employee name only once, and the department names will be a comma-separated string of names. This is accomplished by using STUFF() in conjunction with FOR XML.

;WITH EmployeesAndDepartments AS
(
    SELECT
        EmployeeName = e.Name
        ,DepartmentName = d.Name
    FROM Employee e
    INNER JOIN EmployeeDepartment ed ON ed.LocationId = e.LocationId
    INNER JOIN Department d ON d.id = ed.DepartmentId
)
SELECT
    ead.EmployeeName
    ,Departments = STUFF((
                             SELECT ',' + DepartmentName
                             FROM EmployeesAndDepartments
                             FOR XML PATH('')
                         ) , 1, 1, ''
                   )
FROM EmployeesAndDepartments ead
GROUP BY ead.EmployeeName

Sql query to print all Department Names and the name of the newest employee in that department

On MySQL 8+ using ROW_NUMBER, we can try:

WITH cte AS (
    SELECT d.DeptName, e.EmpName,
           ROW_NUMBER() OVER (PARTITION BY d.DeptId ORDER BY e.Jod DESC) rn
    FROM dept d
    INNER JOIN employee e ON e.DeptId = d.DeptId
)

SELECT DeptName, EmpName
FROM cte
WHERE rn = 1;

Demo

How to display number of employees of each position in each department

With a LEFT JOIN of department to employee and conditional aggregation:

select
  d.dept_name,
  sum(case when e.position = 'tech_support' then 1 else 0 end) tech_support,
  sum(case when e.position = 'data_entry' then 1 else 0 end) data_entry,
  sum(case when e.position = 'assistant_manager' then 1 else 0 end) assistant_manager
from department d left join employee e
on e.deptid = d.department_id
group by d.department_id, d.dept_name

---

Related Topics