Create a query that shows the last name, employee ID, hours worked, and overtime amount owed...

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.

Expert Solution

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.

venereology answered 2 years ago

Write an SQL query that will output the employee id, first name and hire date from...

Write an SQL query that will output the employee id, first name and hire date from the employee table. Pick only those employees whose employee ID is specified in the employee table (no nulls). If the employee id is 777, name is ABC and hire date is 01-JAN-2016, the output should be like - ' ABC (Employee ID - 777) was hired on 1, January of 2016'. Note - The date should not have preceding zeros.

Problem 44 Write a query to display the employee number, last name, first name, and sum...

Problem 44 Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name (Partial results shown in Figure P7.44).

Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department...

Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department Phone # 0001 John Smith jsmith Accountant 1300 West st 5775 Accounting 2100 8/1998 407-366-5700 0002 Brian Miller badams Admin Assistant 1552 Palm dr 5367 Human resource 2300 4/1995 407-366-5300 0003 James Miller miller Inventory Manager 2713 Buck rd 5432 Production 2520 8/1998 407-366-5400 0004 John Jackson jackson_sam Sales Person 433 tree dr 5568 Sales 2102 6/1997 407-366-5500 0005 Robert Davis Davis Manager 713...

Leticia Alvarez worked 40 hours straight time and 20 hours overtime at an hourly rate of...

Leticia Alvarez worked 40 hours straight time and 20 hours overtime at an hourly rate of $10.50. Of the 20 hours overtime, 8 hours were at double time. Compute the following: 30. Straight-time earnings = 31. Overtime earnings = 32. Gross earnings =

Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth...

Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth Create a table ‘ClassInfo’ table: ID Class Name Class Description Create a table ‘RegisteredClasses’ table: StudentID ClassID The RegisteredClasses table should have a foreign key relationship to StudentInfo and ClassInfo tables for the respective IDs. Also the IDs in StudentInfo and ClassInfo need to be primary keys. When you submit the file your email should also contain the following SQL Queries: Query to show...

List department name, employee id, and employee name for all employees in department name order. Repeat...

List department name, employee id, and employee name for all employees in department name order. Repeat for department #10 only. List the course ID, course name, section, instructor name, day, time, and room for all course sections. List the course ID, course name, section, student ID, and student name for CRN 1003. Display the list in ascending order of student last and first names. DROP TABLE registration; DROP TABLE sections; DROP TABLE courses; DROP TABLE students; DROP TABLE instructors; CREATE...

An employee works 48 hours (48 - 40 were overtime hours) during a workweek in December...

An employee works 48 hours (48 - 40 were overtime hours) during a workweek in December of 2019. He earns $8,000/month, with his employer paying 1.5 times the regular rate of pay for overtime hours. To date, he has earned $110,300 during the year. He has requested that his employer withhold 11.5% of gross pay to contribute to a 403(b) plan. Taxable income for federal income tax withholding = $ Taxable income for social security tax = $ Taxable income...

JAVA PROGRAMMING Part 1 Create a class Student, with attributes id, first name, last name. (All...

JAVA PROGRAMMING Part 1 Create a class Student, with attributes id, first name, last name. (All the attributes must be String) Create a constructor that accepts first name and last name to create a student object. Create appropriate getters and setters Create another class StudentOperationClient, that contains a main program. This is the place where the student objects are created and other activities are performed. In the main program, create student objects, with the following first and last names. Chris...

Create a program that allows a user to input customer records (ID number, first name, last...

Create a program that allows a user to input customer records (ID number, first name, last name, and balance owed) and save each record to a file. When you run the main program, be sure to enter multiple records. Once you create the file, open it and display the results to the user Save the file as CustomerList.java

The system will accept the employee ID, the employee name and his/her gross salary.

The system will accept the employee ID, the employee name and his/her gross salary.Based on the gross salary provided, if it is equal to or greater than OMR 2500, the system should apply a tax rate of 6% when calculating the income tax otherwise a tax rate of 4% should be applied when calculating.calculate the income tax and the net salary,If (gross salary > = 2500) Tax rate = 6%Else Tax rate = 4%Income tax = Net salary =

Question