Amazon Interview Question
Quality Assurance EngineersTeam: AWS
Country: United States
Interview Type: Phone Interview
SELECT spt.name,avg.average
FROM DEPT AS dpt
LEFT OUTER JOIN
(
SELECT deptno,AVG(salary) as average FROM EMP
GROUP BY deptno
)as avg
ON avg.deptno = spt.deptno
create table #emp(empno int not null,name varchar(30),deptno int,salary decimal(9,3))
create table #dept (deptno int,deptname varchar(50))
insert into #dept (deptno,deptname)
values (1,'IT')
,(2,'Ops')
,(3,'fin')
,(4,'maint')
insert into #emp(empno,name,deptno,salary)
values (1,'sai',1,70000)
,(2,'mike',1,'90000')
,(3,'mark',3,'90000')
,(4,'linn',3,'80000')
,(5,'mill',2,'70000')
,(6,'jazz',2,'80000')
select * from #emp
--Direct Solution : 1
select 'm1'
SELECT D.deptno,AVG(E.salary) AS AvgDeptSalary
FROM #emp E right JOIN #dept D ON D.deptno = E.deptno
GROUP BY D.deptno
--Using a sub-query
select'm-2'
SELECT e.deptname,d1.AvgDeptSalary
FROM #dept E
LEFT JOIN (SELECT D.deptno,AVG(E.salary) AS AvgDeptSalary
FROM #emp E LEFT JOIN #dept D ON D.deptno = E.deptno
GROUP BY D.deptno
) D1 ON D1.deptno = E.deptno
drop table #emp
drop table #dept
create table employee
(
id number(5),
name char(20),
dept char(20),
age number(2),
salary number(10),
location char(10),
deptid number(5)
);
create table department
(
id number(5),
name char(20)
);
INSERT INTO employee values (1 , "Abhi" , "Science" , 30 , 100 , "Delhi",1);
INSERT INTO employee values (2 , "Dishika" , "Commerce" , 27 , 50 , "Varanasi",2);
INSERT INTO employee values (3 , "Umesh" , "Science" , 32 , 110 , "Meerut",1);
INSERT INTO employee values (4 , "Ishan" , "Science" , 22 , 10 , "Varanasi",1);
INSERT INTO employee values (5 , "Divyani" , "Commerce" , 21 , 5 , "Varanasi",2);
INSERT INTO department values (1, "computer");
INSERT INTO department values (2, "finance");
INSERT INTO department values (3, "human resource");
SELECT d.name, avg(e.salary) AvG_Sal
from department d LEFT OUTER JOIN employee e
on d.id = e.deptid
Group by d.name;
---- To get AVG salary by dept name [Using inbuilt function]
select
dt.name as 'dept Name'
, AVG(ep.salary) as 'Average Salary'
from empTab ep join deptTab dt
on ep.deptNum = dt.deptNum
group by dt.name
-- To get AVG salary by dept name [Without using inbuilt function]
; with ctetst
as
(
select
sum(ep.salary) as totalSal
, count(empNum) as CntEmp
, deptNum
from empTab ep
group by ep.deptNum
)
select
dte.name as 'Department Name'
, (totalSal/CntEmp) as 'Average Salary'
from ctetst ct join deptTab dte
on ct.deptNum = dte.deptNum
select name,avg(salary)
- cobra December 24, 2012from emp e join dept d on e.deptno = d.deptno
group by name;