Converting Queries into SQL Server syntax

I’ve been getting help from Chegg and most answers given to me don’t work for me. My first task is write a query where it list the employee assigned to the most projects.

One response was:

select emp_name 
from employee 
where dept_ID in ( select dept_ID from project group by dept_ID order by count(proj_ID) desc);

Error I get:

Msg 1033, Level 15, State 1, Line 3 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Second Response is

select w1.emp_id
from work_period w1
group by w1.emp_id
having count(w1.proj_id) = (select max(count(w2.proj_id)) from work_period w2 group by w2.emp_id);

Error I get is:

Msg 130, Level 15, State 1, Line 8 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Third Response was

Select emp_ID,emp_Name 
from Employee inner join Project on Employee.Dept_ID = Project.Dept_ID 
group by emp_ID,emp_Name 
having count(Proj_ID) = max(count(Proj_ID));

Error message:

Msg 130, Level 15, State 1, Line 13 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any help on how to resolve either one of these queries is greatly appreciated

Here is the script to create my database if it helps

create table department
(
  dept_ID int not null ,
  dept_name char(50) NOT NULL,
  manager_ID int not null,
  manager_start_date date not null,

  constraint Dept_PK primary key (dept_ID),
  constraint D_Name_AK unique (dept_name)
);
insert into department values(1,'abc',1,'2019-01-08');
insert into department values(2,'abc2',2,'2019-01-08');
insert into department values(3,'abc3',2,'2019-01-08');
insert into department values(4,'abc4',2,'2019-01-08');

/*project table done*/
create table project
(
  proj_ID int not null,
  proj_name varchar(20) not null,
  dept_ID int not null,
  proj_location varchar(20) not null,

  constraint Proj_ID_PK primary key (proj_ID),
  constraint Proj_Dep_FK foreign key (dept_ID) references department(dept_ID)
);
insert into project values ( 1,'project1',1,'india');
insert into project values ( 2,'project2',2,'US');

/*employee table done*/
create table employee
(
  emp_ID int NOT NULL ,
  emp_name char(50) not null,
  emp_ssn char(11) not null,
  emp_address char(50) not null,
  salary decimal(10,2) not null,
  sex char(1) not null,
  date_of_birth date not null,
  dept_ID int not null,
  supervisor_ID int null,

  constraint emp_PK primary key(emp_ID),
  constraint emp_Name_AK unique (emp_name),
  constraint emp_SSN_AK unique (emp_ssn),
  constraint sup_FK foreign key(supervisor_ID) references employee(emp_ID),
  constraint empDep_FK foreign key(dept_ID) references department(dept_ID)
);

insert into employee values( 1,'jagmeet', 'ssn','patel nagar',300,'M','1997-07-01',1,1);

insert into employee values( 2,'harpreet', 'ssn1','patel nagar2',300,'F','1997-07-01',1,2);
/*Department location table done*/
create table dept_location
(
  dept_ID int not null,
  location char(50) not null,

  constraint dept_location_PK primary key(dept_ID, location),
  constraint dept_FK foreign key (dept_ID) references department(dept_ID)
);
insert into dept_location values(1,'loc1');

insert into dept_location values(2,'loc2');

/*dependent table done*/
create table dependent
(
  dependent_ID int not null ,
  emp_ID int NOT NULL,
  dependent_name varchar(20) NOT NULL,
  dependent_sex char(1) NOT NULL,
  dependent_DOB date not null,
  dep_relation varchar(10) not null,

  constraint dep_ID_PK primary key (dependent_ID),
  constraint deb_emp_ID_FK foreign key (emp_ID) references employee(emp_ID)
);
insert into dependent values (1,2,'deptname','M','2018-01-09','rel1');

insert into dependent values (2,1,'deptname2','F','2018-01-09','rel2');
/*work period table done*/

create table work_period
(
  emp_ID int NOT NULL,
  proj_ID int not null,
  pay_period date not null,
  weekly_work_hrs int not null,

  constraint pay_period_PK primary key (pay_period),
  constraint WP_empID_FK foreign key (emp_ID) references employee(emp_ID),
  constraint WP_projID_FK foreign key (proj_ID) references project(proj_ID)
);
insert into work_period values(1,1,'2012-09-01',7);

insert into work_period values(2,2,'2014-09-01',8);

Answer

You can use subquery to get the employees with the most number of projects, together with max() and count() aggregation functions.

select * from employee where dept_id in (
    select max(ct) from 
        (select count(1) ct, t1.dept_id from department t1
        inner join project t2 on t2.dept_id = t1.dept_id
        group by t1.dept_id) t3 )