Database Description

I can not complete the task.
"Display the list of departments whose employees work in the largest project (by the number of participants)"

SELECT Department FROM DEP INNER JOIN EMP ON DEP.ID_Dep=EMP.ID_Dep INNER JOIN PROJECT_EMP ON EMP.ID_Emp=PROJECT_EMP.ID_Emp GROUP BY Department

This query lists the departments involved in the projects. How to continue to do - not enough knowledge.

 SELECT Department FROM DEP INNER JOIN EMP ON DEP.ID_Dep=EMP.ID_Dep INNER JOIN PROJECT_EMP ON EMP.ID_Emp=PROJECT_EMP.ID_Emp GROUP BY Department HAVING COUNT(Name)= (SELECT max(qwe) FROM (SELECT COUNT(Name) as qwe FROM DEP INNER JOIN EMP ON DEP.ID_Dep=EMP.ID_Dep INNER JOIN PROJECT_EMP ON EMP.ID_Emp=PROJECT_EMP.ID_Emp GROUP BY Department) tmp)
  • Please add the code you wrote and tried to execute to your question. Describe what happened and how it differs from the expected. - Igor
  • SELECT Department FROM DEP INNER JOIN EMP ON DEP.ID_Dep = EMP.ID_Dep INNER JOIN PROJECT_EMP ON EMP.ID_Emp = PROJECT_EMP.ID_Emp GROUP BY Department - neko69
  • This query lists the departments involved in the projects. How to continue to do - not enough knowledge - neko69
  • can use the MAX () function? - arcs_host

3 answers 3

Unfortunately, there is no server at hand to check, but something like this:

 select * from DEP d inner join EMP e on d.ID_Dep = e.ID_Dep inner join PROJECT_EMP pe on pe.ID_Emp = e.ID_Emp where pe.ID_Project = (select top 1 ID_Project from PROJECT_EMP group by ID_Project order by count(ID_Emp) desc)

Optionally, you can add a join to the PROJECT table and the Pr_end is not null condition if the job implies current (incomplete) projects.

  • A little bit wrong. The technical department should be displayed. - neko69
  • @ neko69 what is the technical department? - andreycha
  • Department from the DEP table How do you insert the code so that it can be read normally? I want to show my curve code - neko69
  • @ neko69 Department from the DEP table is a column with department names. What is a "technical department"? - andreycha
  • @ neko69 in the question and answer, select the code and press the {} button. The code is not formatted in comments and therefore it is not written in comments. Add it to the question. - andreycha

And if so:

 SELECT Department FROM DEP d INNER JOIN EMP e ON d.ID_Dep = e.ID_Dep INNER JOIN PROJECT_EMP pe ON pe.ID_Emp = e.ID_Emp WHERE pe.ID_Project = ( SELECT ID_Project FROM PROJECT_EMP GROUP BY ID_Project ORDER BY count(ID_Emp) DESC LIMIT 1 ) LIMIT 1 
     SELECT DISTINCT Department FROM dep INNER JOIN emp ON (emp.id_dep=dep.id_dep) INNER JOIN project_emp AS pe ON (pe.id_emp=emp.id_emp) WHERE id_project = ( SELECT id_pe, COUNT(*) AS c FROM project_emp GROUP BY id_pe order by c DESC limit 1);