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

Comment hidden because of low score. Click to expand.
0

what if the dept doesn't have any employee.

Comment hidden because of low score. Click to expand.
0

Referential integrity has been enforced here.

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

Comment hidden because of low score. Click to expand.
0

substiture spt to dpt

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

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

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

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

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

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

Comment hidden because of low score. Click to expand.
0

6

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

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;

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

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

Comment hidden because of low score. Click to expand.
0
of 0 vote

70131266

Comment hidden because of low score. Click to expand.
0
of 0 vote

14821

Comment hidden because of low score. Click to expand.
0
of 0 vote

11020014692

Comment hidden because of low score. Click to expand.
0
of 0 vote

00144770

Comment hidden because of low score. Click to expand.
0
of 0 vote

EMP NO : 809653

Comment hidden because of low score. Click to expand.
0
of 0 vote

03252668

Comment hidden because of low score. Click to expand.
0
of 0 vote

48250

Comment hidden because of low score. Click to expand.
0
of 0 vote

Chanchal Kumar sah emo No 27223004890

Comment hidden because of low score. Click to expand.
0
of 0 vote

334np001288

Comment hidden because of low score. Click to expand.
0
of 0 vote

451601

Comment hidden because of low score. Click to expand.
0
of 0 vote

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

Comment hidden because of low score. Click to expand.
0
of 0 vote

AP060510075

Comment hidden because of low score. Click to expand.
0
of 0 vote

AP060510075

Comment hidden because of low score. Click to expand.
0
of 0 vote

2959

Comment hidden because of low score. Click to expand.
0
of 0 vote

2959

Comment hidden because of low score. Click to expand.
0
of 0 vote

Emp no 1012754533

Comment hidden because of low score. Click to expand.
0
of 0 vote

hello

Comment hidden because of low score. Click to expand.
0
of 0 vote

"hello"

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.

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.

Comment hidden because of low score. Click to expand.
0
of 0 vote

1331068

Comment hidden because of low score. Click to expand.
0
of 0 vote

Epf no GJ/SRT/31099/068882

Comment hidden because of low score. Click to expand.
0
of 0 vote

Display the output of the following Queries.
1) Select * from emp, dept where emp.deptnp<>dept.deptno;
2) select * from emp natural join dept;

Comment hidden because of low score. Click to expand.
0
of 0 vote

department

Comment hidden because of low score. Click to expand.
0
of 0 vote

Hjn

Comment hidden because of low score. Click to expand.
0
of 0 vote

insert into emp values(101,'susheel',10,sale,1700,5000, 2-decembar-2011);
insert into emp values (102,'dinesh',20,manger,1701,6000,3-decembar-2012);
select*from emp;

Comment hidden because of low score. Click to expand.
0
of 0 vote

insert into emp values(101,'susheel',10,sale,1700,5000, 2-decembar-2011);
insert into emp values (102,'dinesh',20,manger,1701,6000,3-decembar-2012);
select*from emp;

Comment hidden because of low score. Click to expand.
0
of 0 vote

insert into emp values(101,'susheel',10,sale,1700,5000, 2-decembar-2011);
insert into emp values (102,'dinesh',20,manger,1701,6000,3-decembar-2012);
select*from emp;

Comment hidden because of low score. Click to expand.
0
of 0 vote

insert into emp values(101,'susheel',10,sale,1700,5000, 2-decembar-2011);
insert into emp values (102,'dinesh',20,manger,1701,6000,3-decembar-2012);
select*from emp;

Comment hidden because of low score. Click to expand.
0
of 0 vote

create table emp(
emp_no number (3),
E_name varchar(20),
Dept_no int,
Dept_name varchar(20),
Job_id number (3),
salary number (5),
hiredate date
);

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

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

Comment hidden because of low score. Click to expand.
-2
of 4 vote

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

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.

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