Question

Create a query that shows the last name, employee ID, hours worked, and overtime amount owed for hourly employees who earned overtime during week 2. Overtime is paid at 1.5 times the normal hourly rate for all hours worked over 40. Note that the amount shown in the query should be just the overtime portion of the wages paid. Also, this is not a totals query- amounts should be shown for individual workers. QBE grid template. This is the exact question from the book and the only information it gives me.

Answer 1

So the fact that ther is no schema , the solution is based on these pretty basic assumptions :

• there is a employee table with eid as primary key.
• there is a hours table with eid as foreign key.
• there is wage table where rate and eid is stored.

now the query we want is :

SELECT E.lastname , H.hours , (H.hours - 40 * 1.5 * W.rate)

from employee E , hours H , wage W

where E.eid = W.eid AND E.eid = H.eid AND E.eid IN (SELECT E.eid from

from employee E , hours H

where E.eid = H.eid AND H.week_no = 2 AND H.hours > 40);

So the above query is a nested query .The inner query will search for the all the records whose week_no = 2 and hours wokred > 40 and return their ids as result. Then in the outer query we are searching for those ids and printing the values.

NOTE : the attributes and table may differ so please change them accordingly.