Amazon Interview Question for Quality Assurance Engineers


Team: AWS
Country: United States
Interview Type: Phone Interview




Comment hidden because of low score. Click to expand.
5
of 9 vote

select name,avg(salary)
from emp e join dept d on e.deptno = d.deptno
group by name;

- cobra December 24, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

what if the dept doesn't have any employee.

- aileen March 01, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

Referential integrity has been enforced here.

- Anil March 01, 2013 | Flag
Comment hidden because of low score. Click to expand.
4
of 4 vote

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

- moin December 23, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

substiture spt to dpt

- Vib January 07, 2015 | Flag
Comment hidden because of low score. Click to expand.
4
of 6 vote

SELECT: d.name, AVG(e.salary)
FROM: DEPT d, EMP e
WHERE: d.deptno = e.deptno
GROUP By: d.name

- SRB December 27, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

SELECT d.name AS DeptName, AVG(e.salary) AS AvgSalary FROM DEPT AS d LEFT OUTER JOIN EMP AS e ON d.deptno = e.deptno GROUP BY d.name

- src June 16, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Anonymous June 20, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select d.name,AVG(e.salary) from emp12 e
join dept12 d
on e.deptno=d.deptno
group by d.name

- Sushil September 02, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT (SUM(e.salary)/COUNT(d.dep_id)) AS Average, d.dep_name, d.dep_id FROM employee e, department d WHERE e.dept_no = d.Dep_id GROUP BY d.dep_id

SELECT AVG(e.salary) AS Average, d.dep_name, d.dep_id FROM employee e, department d WHERE e.dept_no = d.Dep_id GROUP BY d.dep_id

- 2010prashant September 20, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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;

- abhi.20dec September 26, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select dept.name, coalesce(avg(emp.salary), 0) as avg_salary
from dept
left join emp
on dept.deptno = emp.deptno
group by dept.name

- Some code December 12, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT DISTINCT D.DNAME, AVG_SAL FROM (SELECT DEPTNO, AVG(SAL) OVER (PARTITION BY DEPTNO) AVG_SAL FROM EMP )E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;

- Satya July 09, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Select D.name, AVG(E.salary) from Dept D join Emp E on D.deptno = E.deptno
Group By d.name

- Vikram July 11, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

---- 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

- Niraj September 20, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

70131266

- manoj November 18, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

14821

- MANVENDRA PATEL February 09, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

11020014692

- Anonymous June 02, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

00144770

- Rajesh kumar pandit June 27, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

EMP NO : 809653

- LaduRamSiyak September 08, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

03252668

- chander singh April 11, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

48250

- Anonymous May 22, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Chanchal Kumar sah emo No 27223004890

- Chanchal Kumar sah March 14, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

334np001288

- roopsingh May 09, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

451601

- Jayeshkumar kunlik phapale September 09, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Emp900053 pF no Ap,HyD,1007148,85.mobil 7981640568

- Ananda shill March 24, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

AP060510075

- hiraman hari londhe February 21, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

AP060510075

- Hiraman hari londhe February 21, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

2959

- gadhaguti bixapathi June 28, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

2959

- gadhaguti bixapathi June 28, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Emp no 1012754533

- Rana Pratap November 15, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

hello

- hello November 25, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

"hello"

- hello November 25, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Using SQL commands Create a Table Employee(Name, Age, DOB, DOJ, Post, Salary). After
creating this table, Add one more column ‘Department’ to the table Employee.

- Anonymous April 10, 2020 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Using SQL commands Create a Table Employee(Name, Age, DOB, DOJ, Post, Salary). After
creating this table, Add one more column ‘Department’ to the table Employee.

- Anonymous April 10, 2020 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

1331068

- Sunil Kumar April 20, 2020 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Epf no GJ/SRT/31099/068882

- Sunil Kumar April 20, 2020 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 5 vote

SELECT d.deptno, avg(e.salary) from emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.name

- Minnu December 23, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
-2
of 2 vote

SELECT d.deptno, avg(r.salary) from emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.name

- suman December 24, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
-2
of 4 vote

select deptname, avg(salary)
from dept 
group by deptname;

- Raj February 09, 2013 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More