Amazon Interview Question
Analystscreate 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)
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
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
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
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)
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
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
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
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
;
CREATE TABLE #EMPLOYEE(EMPID INT,EMPNAME VARCHAR(30))
CREATE TABLE #EMPLOYEE_TIMESHEET(EMPID INT,NOOFHOURS NUMERIC(9,3),DATE SMALLDATETIME)
CREATE TABLE #EMPLOYYEHOURRATE (EMPID INT,HOUR_RATE NUMERIC(9,3))
INSERT INTO #EMPLOYEE (EMPID,EMPNAME)
VALUES (1,'SAI')
,(2,'MIKE')
,(3,'JOHN')
SELECT'EMPLOYEE' SELECT * FROM #EMPLOYEE
INSERT INTO #EMPLOYEE_TIMESHEET (EMPID,NOOFHOURS,DATE)
VALUES (1,40,GETDATE())
,(1,45,'4/30/2013')
,(1,45,'4/29/2013')
,(2,40,GETDATE())
,(2,50,'12/1/2012')
,(2,75,'12/31/2012')
,(3,40,GETDATE())
SELECT'EMPLOYEE_TIMESHEET' SELECT * FROM #EMPLOYEE_TIMESHEET
INSERT INTO #EMPLOYYEHOURRATE (EMPID,HOUR_RATE)
VALUES (1,'45')
,(2,'50')
,(3,60)
SELECT'EMPLOYEE_HOURRATE' SELECT * FROM #EMPLOYYEHOURRATE
SELECT E.EMPNAME,SUM(ET.NOOFHOURS * EHR.HOUR_RATE) AS PAY,MONTH(ET.DATE) AS Month,YEAR(ET.DATE) AS Year
FROM #EMPLOYEE E
INNER JOIN #EMPLOYEE_TIMESHEET ET ON ET.EMPID = E.EMPID
INNER JOIN #EMPLOYYEHOURRATE EHR ON EHR.EMPID = E.EMPID
GROUP BY E.EMPNAME,MONTH(ET.DATE),YEAR(ET.DATE)
ORDER BY 1
DROP TABLE #EMPLOYYEHOURRATE
DROP TABLE #EMPLOYEE_TIMESHEET
DROP TABLE #EMPLOYEE
CREATE TABLE #EMPLOYEE(EMPID INT,EMPNAME VARCHAR(30))
CREATE TABLE #EMPLOYEE_TIMESHEET(EMPID INT,NOOFHOURS NUMERIC(9,3),DATE SMALLDATETIME)
CREATE TABLE #EMPLOYYEHOURRATE (EMPID INT,HOUR_RATE NUMERIC(9,3))
INSERT INTO #EMPLOYEE (EMPID,EMPNAME)
VALUES (1,'SAI')
,(2,'MIKE')
,(3,'JOHN')
SELECT'EMPLOYEE' SELECT * FROM #EMPLOYEE
INSERT INTO #EMPLOYEE_TIMESHEET (EMPID,NOOFHOURS,DATE)
VALUES (1,40,GETDATE())
,(1,45,'4/30/2013')
,(1,45,'4/29/2013')
,(2,40,GETDATE())
,(2,50,'12/1/2012')
,(2,75,'12/31/2012')
,(3,40,GETDATE())
SELECT'EMPLOYEE_TIMESHEET' SELECT * FROM #EMPLOYEE_TIMESHEET
INSERT INTO #EMPLOYYEHOURRATE (EMPID,HOUR_RATE)
VALUES (1,'45')
,(2,'50')
,(3,60)
SELECT'EMPLOYEE_HOURRATE' SELECT * FROM #EMPLOYYEHOURRATE
SELECT E.EMPNAME,SUM(ET.NOOFHOURS * EHR.HOUR_RATE) AS PAY,MONTH(ET.DATE) AS Month,YEAR(ET.DATE) AS Year
FROM #EMPLOYEE E
INNER JOIN #EMPLOYEE_TIMESHEET ET ON ET.EMPID = E.EMPID
INNER JOIN #EMPLOYYEHOURRATE EHR ON EHR.EMPID = E.EMPID
GROUP BY E.EMPNAME,MONTH(ET.DATE),YEAR(ET.DATE)
ORDER BY 1
DROP TABLE #EMPLOYYEHOURRATE
DROP TABLE #EMPLOYEE_TIMESHEET
DROP TABLE #EMPLOYEE
select
emp.eid,
to_char(emp_timesheet.edate, 'MM-YYYY') month_year,
sum(emp_timesheet.no_hrs) total_hrs_month,
(sum(emp_timesheet.no_hrs) * emp_hour_rate.hour_rate) pay_check
from
emp, emp_timesheet, emp_hour_rate
where
emp.eid = emp_timesheet.eid and emp.eid = emp_hour_rate.eid
group by
emp.eid, to_char(emp_timesheet.edate, 'MM-YYYY'), emp_hour_rate.hour_rate
create database amazon
go
use amazon
go
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');
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 hr.eid,t.totalworkhrs,hr.hr_rate, t.totalworkhrs*hr.hr_rate totalsal from hr_rate hr
inner join (
select eid, sum(no_hrs) as totalworkhrs
from timesheet
where year(cdate)=2011 and month(cdate)=6
group by eid) t
on t.eid = hr.eid
create database amazon
go
use amazon
go
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');
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 hr.eid,t.totalworkhrs,hr.hr_rate, t.totalworkhrs*hr.hr_rate totalsal from hr_rate hr
inner join (
select eid, sum(no_hrs) as totalworkhrs
from timesheet
where year(cdate)=2011 and month(cdate)=6
group by eid) t
on t.eid = hr.eid
How about something primitive like this? if there are any performance issues/better way to write this query please correct me.
SELECT
EMP.EMPID, EMPNAME, TS_YEAR, TS_MONTH, (TS_TOTAL_HOURS*HOURLY_RATE) AS SALARY
FROM
EMPLOYEE EMP
LEFT JOIN
(SELECT EMP_ID,EXTRACT(YEAR FROM TS_DATE) AS TS_YEAR,
EXTRACT(MONTH FROM TS_DATE) AS TS_MONTH,
SUM(NO_OF_HOURS) AS TS_TOTAL_HOURS
FROM EMPLOYEE_TIMESHEET
GROUP BY EMP_ID,EXTRACT(YEAR FROM TS_DATE),EXTRACT(MONTH FROM TS_DATE)) TS
ON TS.EMP_ID = EMP.EMPID
JOIN EMPLOYEE_HOUR_RATE EHR
ON EMP.EMP_ID = EHR.EMPID
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)
The following a working SQL query. You can try it yourself.
-----------------------------------------------------------------------------------
- Ayad Barsoum January 02, 2012