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
Q1.Display the number of Employee by each Job from Employees table.
Ans - SELECT JOB, count(*) [No of Employee] from acroschema_17.EMPLOYEES group by JOB
Q2.Display 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 a.EMPNO = b.EMPNO group by TASK having count(*)
>1
Q3.Display 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
a.DEPTNO = b.DEPTNO group by DNAME
Q4.Display 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
a.DEPTNO = b.DEPTNO group by DNAME
Q5.Display 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 a.DEPTNO = b.DEPTNO
Q6.Display 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 a.DEPTNO ,ENAME, max(SALARY) [MAX SALARY] from acroschema_17.EMPLOYEES a
left join acroschema_17.DEPARTMENTS
b on a.DEPTNO = b.DEPTNO group by a.DEPTNO,ENAME order by [MAX SALARY] desc
Q8.Display name of department with highest SUM of salary.
ANS-SELECT JOB, sum(SALARY) TotalSalary from acroschema_17.EMPLOYEES group by JOB
Q9.Display department number, names and salaries of employees who are earning max salary in
their departments.
ANS - SELECT a.DEPTNO ,ENAME, max(SALARY) [MAX SALARY] from acroschema_17.EMPLOYEES a
left join acroschema_17.DEPARTMENTS
b on a.DEPTNO = b.DEPTNO group by a.DEPTNO,ENAME order by [MAX SALARY] desc
Q10.Display 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 t3.TASK = 'Coding' and t3.STATUS = 'In Progress'
Q11.Display 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 t1.EMPNO = t2.EMPNO inner join acroschema_17.PROJECTS t3 on t3.PROJECT_ID = t2.PROJECT_ID
where TASK = 'System Analysis'
Q12.Display department name, employee name and job.
ANS - SELECT DNAME ,ENAME,JOB from acroschema_17.EMPLOYEES t1 left join acroschema_17.DEPARTMENTS t2 on
t2.DEPTNO=t1.DEPTNO order by DNAME
Q13.Display all employee numbers present in both Employees and EmpProjectTasks table.
ANS - SELECT t1.EMPNO from acroschema_17.EMPLOYEES t1 full join acroschema_17.EMPPROJECTTASKS t2 on
t1.EMPNO = t2.EMPNO
