MS SQL Server
Assignment # 2
Topic: Group, Joins, Coalesce, Union Time: 2 Hr
SQL Assignment # 2 – Questions
1. Display the number of Employee by each Job from Employees table.
2. Display the Empno and number of assign Project Tasks if the Employee assigns more than 1
project Tasks in EmpProjectTasks table.
3. Display the number of Employee in each department from the table Employees &
Departments tables.
4. Display department wise count and sum of salary of employees.
5. Display employee numbers present in both Employees and EmpProjectTasks table.
6. Display the project name with highest budget.
7. Display department number, names and salaries of employees who are earning max salary in
their departments.
8. Display name of department with highest SUM of salary.
9. Display department number, names and salaries of employees who are earning max salary in
their departments
10. Display client name whose project’s ‘Coding’ task is ‘In Progress’.
11. Display names of employees doing ‘System Analysis’ along with project name
12. Display department name, employee name and job.
13. Display all employee numbers present in both Employees and EmpProjectTasks table.
MS SQL Server
Assignment # 2
Topic: Submitted By:Nikhil
[Link] the number of Employee by each Job from Employees table.
Ans - SELECT JOB, count(*) [No of Employee] from acroschema_17.EMPLOYEES group by JOB
[Link] the Empno and number of assign Project Tasks if the Employee assigns more than 1
project Tasks in EmpProjectTasks table.
Ans - SELECT TASK,count(*) [No of assign] from acroschema_17.EMPLOYEES a left join
acroschema_17.EMPPROJECTTASKS b on [Link] = [Link] group by TASK having count(*)
>1
[Link] the number of Employee in each department from the table Employees &
Departments tables.
Ans - SELECT DNAME, count(*) [No of employees] from acroschema_17.EMPLOYEES a inner join
acroschema_17.DEPARTMENTS b on
[Link] = [Link] group by DNAME
[Link] department wise count and sum of salary of employees.
Ans - SELECT DNAME,sum(SALARY)[Sum of salary], count(*) [No of employees] from
acroschema_17.EMPLOYEES a inner join acroschema_17.DEPARTMENTS b on
[Link] = [Link] group by DNAME
[Link] employee numbers present in both Employees and EmpProjectTasks table.
Ans - SELECT count(*) [no of employee] from acroschema_17.EMPLOYEES a left join
acroschema_17.DEPARTMENTS b
on [Link] = [Link]
[Link] the project name with highest budget.
ANS - SELECT DESCR,MAX(BUDGET) highest from acroschema_17.PROJECTS group by DESCR
Q7..Display department number, names and salaries of employees who are earning max salary in
their departments.
ANS - SELECT [Link] ,ENAME, max(SALARY) [MAX SALARY] from acroschema_17.EMPLOYEES a
left join acroschema_17.DEPARTMENTS
b on [Link] = [Link] group by [Link],ENAME order by [MAX SALARY] desc
[Link] name of department with highest SUM of salary.
ANS-SELECT JOB, sum(SALARY) TotalSalary from acroschema_17.EMPLOYEES group by JOB
[Link] department number, names and salaries of employees who are earning max salary in
their departments.
ANS - SELECT [Link] ,ENAME, max(SALARY) [MAX SALARY] from acroschema_17.EMPLOYEES a
left join acroschema_17.DEPARTMENTS
b on [Link] = [Link] group by [Link],ENAME order by [MAX SALARY] desc
[Link] client name whose project’s ‘Coding’ task is ‘In Progress’.
ANS - SELECT CNAME from acroschema_17.CLIENTS t1 inner join
acroschema_17.PROJECTS t2 on t1.CLIENT_ID = t2.CLIENT_ID
inner join acroschema_17.EMPPROJECTTASKS t3 on t2.PROJECT_ID = t3.PROJECT_ID
and [Link] = 'Coding' and [Link] = 'In Progress'
[Link] names of employees doing ‘System Analysis’ along with project name
ANS - SELECT ENAME, DESCR from acroschema_17.EMPLOYEES t1 inner join
acroschema_17.EMPPROJECTTASKS
t2 on [Link] = [Link] inner join acroschema_17.PROJECTS t3 on t3.PROJECT_ID = t2.PROJECT_ID
where TASK = 'System Analysis'
[Link] department name, employee name and job.
ANS - SELECT DNAME ,ENAME,JOB from acroschema_17.EMPLOYEES t1 left join acroschema_17.DEPARTMENTS t2 on
[Link]=[Link] order by DNAME
[Link] all employee numbers present in both Employees and EmpProjectTasks table.
ANS - SELECT [Link] from acroschema_17.EMPLOYEES t1 full join acroschema_17.EMPPROJECTTASKS t2 on
[Link] = [Link]