Question

MICROSOFT SQL SERVER - Create the following views

1.List the employee assigned to the most projects

2.List the project with the most hours

SCRIPT TO BUILD DATABASE

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 1

1.

Select Top 1 emp_ID,emp_Name from Employee inner join Project on Employee.Dept_ID = Project.Dept_ID group by emp_ID,emp_Name order by COUNT(distinct project.Proj_ID) DESC ;

2.

Select Top 1 project.Proj_ID , proj_Name from Project inner join work_period on Project.Proj_ID = work_period.Proj_Id group by project.Proj_ID , proj_Name order by COUNT(weekly_work_hrs) DESC ;

Do ask if any doubt. Please up-vote.