Amazon Interview Question Analysts

• 0

Given 3 tables: Employee (empid, empname), Employee_timesheet(empid, no_of_hours, date), Employee_hour_rate (empid, hour_rate). Calculate pay check of all employees on monthly basis.

Comment hidden because of low score. Click to expand.
1
of 3 vote

The following a working SQL query. You can try it yourself.
-----------------------------------------------------------------------------------

``````SELECT     TEMP.EmpID, TEMP.EmpName,
Employees_HoursRate.hour_rate *  TEMP.TotalHoursPerMonth AS SAL
FROM         Employees_HoursRate INNER JOIN
(SELECT     Employee.empid AS EmpID, Employee.empname AS EmpName,
SUM(Employee_TimesSheet.no_of_hours) AS TotalHoursPerMonth
FROM        Employee INNER JOIN
Employee_TimesSheet ON Employee.empid = Employee_TimesSheet.empid
GROUP BY Employee.empid, Employee.empname,
DATEPART(month, Employee_TimesSheet.date)) AS TEMP ON
Employees_HoursRate.empid = TEMP.EmpID

ORDER BY TEMP.EmpID``````

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

You might want to include "year" in your group by , otherwise you are adding monthly salary of all years.

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

create table employee(eid smallint,ename varchar(10));

insert into employee values(1 ,'Abhishek');
insert into employee values(2, 'Nikhil');
insert into employee values(3,'Rama');
insert into employee values(4 , 'Vidyut');
insert into employee values(5 , 'Pavan');

select * from timesheet;

create table timesheet(eid smallint,no_hrs smallint,cdate date);

insert into timesheet values(1,100 , '2011-06-30');
insert into timesheet values(2 , 105 , '2011-06-30');
insert into timesheet values(3 , 90 , '2011-06-30');
insert into timesheet values(4 , 85 , '2011-06-30');
insert into timesheet values(5 , 110 , '2011-06-30');

insert into timesheet values(5 , 110 , '2011-04-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-03-30');
insert into timesheet values(2 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2011-08-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(2 , 110 , '2010-06-30');
insert into timesheet values(1 , 110 , '2011-06-30');
insert into timesheet values(1 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');

select * from hr_rate;
+------+---------+
| eid | hr_rate |
+------+---------+
| 1 | 50 |
| 2 | 60 |
| 3 | 40 |
| 4 | 30 |
| 5 | 80 |

create table hr_rate(eid smallint,hr_rate smallint);

insert into hr_rate values(1,50);
insert into hr_rate values(2,60);
insert into hr_rate values(3,40);
insert into hr_rate values(4,30);
insert into hr_rate values(5,80);

select e.ename,month(t.cdate),year(t.cdate),sum(t.no_hrs*hr.hr_rate)
from employee e,timesheet t,hr_rate hr
where e.eid=t.eid and e.eid=hr.eid
group by e.ename,month(t.cdate),year(t.cdate)
order by ename,year(t.cdate),month(t.cdate)

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

``````SELECT
E1.empid,
E1.empname,
E2.Hours_rate*E3.total as SAL
FROM
PWM.EMP as E1,
PWM.emp_rate as E2,
(
SELECT
EMPID,
SUM(HOURS) as total
FROM
PWM.EMP_TIMESHEET
WHERE
DATE     > '2011-01-01'
and DATE < '2011-10-01'
GROUP BY
EMPID
) as E3 where E1.empid = E2.empid and E1.empid =E3.empid``````

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

this looks to be wrong.. so is many other queries that are here...the hours and the hour rate of each employee can differ.. if so you can multiplication like this...dont write any SQL.. just try this in a spreadsheet.. you will get to know sum(hours)* hour_rate can lead you to wrong results if the hour rate is different....

SELECT SUM(HOUR_RATE*number_of_hours) FROM EMPLOYEE where date between date_1 and date_2 would be the correct SQL

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

select emp.empname, ab.yearmonth, total_hours*ehr.hour_rate
from employee emp
inner join
(
select b.yearmonth,b.empid, sum(b.no_of_hours) total_hours
(
select CAST(datepart(year,ts.date) AS VARCHAR(6)) + CAST(datepart(month,ts.date) AS VARCHAR(6)) as yearmonth,
ts.empid, ts.no_of_hours, date
from Employee_timesheet ts
) b
group by b.yearmonth, b.empid
) ab on ab.empid = emp.empid
inner join employee_hour_rate ehr on ehr.empid = emp.empid

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

I believe following query shud work. Natural join. Group by month

select EE.empname, ET.yearmonth, sum(ET.total_hours)*EHR.hour_rate payrate
from Employee EE, Employee_timesheet ET, Employee_hour_rate EHR
group by extract(month from date)

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

select e.empid,
e.empname,
to_date(date,'MM') as month,
sum(et.no_of_hours)*ehr.hour_rate
from Employee as e, Employee_timesheet as et, Employee_hour_rate as ehr
where e.empid = et.empid
and e.empid = ehr.empid
group by e.empid, e.empname, to_date(date,'MM'), ehr.hour_rate

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

SELECT
e.empid,
e.empname,
CAST(YEAR(t.date) AS VARCHAR) + '/' + CAST(MONTH(t.date) AS VARCHAR) AS ReportingMonth,
SUM(no_of_hours) * r.hour_rate AS Total
FROM
Employee AS e INNER JOIN
Employee_timesheet AS t ON e.empid = t.empid INNER JOIN
Employee_hour_rate AS r ON e.empid = r.empid
GROUP BY
e.empid, e.empname, r.hour_rate, CAST(YEAR(t.date) AS VARCHAR) + '/' + CAST(MONTH(t.date) AS VARCHAR)

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

select e.epname,v.total from Employee e JOIN
(select sum(empt.hours)*empr.rate TOTAL_SAL,empid
FROM employee_timesheet empt JOIN employee_hour_rate empr
ON empid
WHERE
empt.date between (select trunc(sysdate, 'MM')as start_date from dual) AND (select add_months(trunc(sysdate, 'MM'), 1) -1 as last_date from dual)
group by empid)
USING empid

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

select e.epname,v.total from Employee e JOIN
(select sum(empt.hours)*empr.rate TOTAL_SAL,empid
FROM employee_timesheet empt JOIN employee_hour_rate empr
ON empid
WHERE
empt.date between (select trunc(sysdate, 'MM')as start_date from dual) AND (select add_months(trunc(sysdate, 'MM'), 1) -1 as last_date from dual)
group by empid)v
USING empid

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

SELECT E.ENAME,SUM(ET.NO_OF_HOURS*ER.HOUR_DATE) FROM EMPLOYEE E,EMPLOYEE_TIMESHEET ET,EMPLOYEE_HOR_RATE EH
WHERE E.EMPID=ET.EMPID AND EH.EMPID=E.EMPID
AND TO_CHAR(DATE,'MM')=TO_CHAR(SYSDATE,'MM')
GROUP BY E.ENAME

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

``````create table employee (
empid int auto_increment primary key,
name varchar(20))TYPE=InnoDB;

create table employee_time(
timeid int(10) auto_increment primary key,
emp_id int,
no_of_hours int(10),
edate datetime,
index(emp_id),
foreign key (emp_id) references employee(empid))TYPE=InnoDB;

create table employee_rate(
rateid int auto_increment primary key,
emp_id int,
rate int,
foreign key (emp_id) references employee(empid))TYPE=InnoDB;

insert into employee(empid, name) values(1, 'adam');
insert into employee(empid, name) values(2, 'eve');

insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02-26');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02,25');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02-24');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-03-01');

insert into employee_time(emp_id,no_of_hours,edate) values(2,8,'2013-02-26');
insert into employee_time(emp_id,no_of_hours,edate) values(2,8,'2013-02,25');

insert into employee_rate(emp_id,rate) values(1,10);
insert into employee_rate(emp_id,rate) values(2,10);

select * from employee;
select * from employee_time;
select * from employee_rate;

select name, sum(no_of_hours*rate) as salary from employee e,employee_time t, employee_rate r
where e.empid = t.emp_id and t.emp_id = r.emp_id and t.edate between '2013-02-01' and '2013-02-28' group by name``````

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

select e.empid, e.empname, T.yearR, T.monthR, T.salary
from guol.emp e left join
(
select h.empid as eid, extract(year from t.dateE) as yearR, extract(month from t.dateE) as monthR, sum(t.no_of_hours*h.rate) as salary
from guol.emp_h h, guol.emp_t t
where h.empid=t.empid
group by h.empid, extract(year from t.dateE), extract(month from t.dateE) ) T on e.empid=T.eid
;

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

mysql> select * from employee;
+-----+----------+
| eid | ename |
+-----+----------+
| 1 | Abhishek |
| 2 | Nikhil |
| 3 | Rama |
| 4 | Vidyut |
| 5 | Pavan |
+-----+----------+
5 rows in set (0.00 sec)

mysql> select * from timesheet;
+------+--------+------------+
| eid | no_hrs | cdate |
+------+--------+------------+
| 1 | 100 | 2011-06-30 |
| 2 | 105 | 2011-06-30 |
| 3 | 90 | 2011-06-30 |
| 4 | 85 | 2011-06-30 |
| 5 | 110 | 2011-06-30 |
+------+--------+------------+
5 rows in set (0.00 sec)

mysql> select * from hr_rate;
+------+---------+
| eid | hr_rate |
+------+---------+
| 1 | 50 |
| 2 | 60 |
| 3 | 40 |
| 4 | 30 |
| 5 | 80 |
+------+---------+
5 rows in set (0.00 sec)

mysql> select Employee.ename,timesheet.no_hrs,hr_rate.hr_rate,timesheet.no_hrs*hr_rate.hr_rate as TOTAL
-> from Employee inner join timesheet using (eid)
-> inner join hr_rate using (eid)
-> where DAY(timesheet.cdate) = DAY(LAST_DAY(CURDATE()));
+----------+--------+---------+-------+
| ename | no_hrs | hr_rate | TOTAL |
+----------+--------+---------+-------+
| Abhishek | 100 | 50 | 5000 |
| Nikhil | 105 | 60 | 6300 |
| Rama | 90 | 40 | 3600 |
| Vidyut | 85 | 30 | 2550 |
| Pavan | 110 | 80 | 8800 |
+----------+--------+---------+-------+
5 rows in set (0.00 sec)

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

understanding the question should have saved u a lot of time.

Name:

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

Books

is a comprehensive book walking you through every aspect of getting a job at a top tech company, while focuses on software engineering interviews.

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.