***These questions are for MySQL for the AdventureWorks2017 database.
Use a temporary table to save the DepartmentID and the highest pay rate of each employee in that department. This will use a subquery. Use the table HumanResources.EmployeePayHistory. Minimize the rows that will be added to the table by using DISTINCT. Make sure all columns in the temp table have a name.
Use a correlated subquery to find the Department Name (using HumanResources.Department) for each department in the temp table from the previous question. Show the largest pay rate for each department and sort the results by this rate descending. Make sure that all columns in the result have a name.
Table HumanResources.Department (16 rows) Lookup table containing the departments within the Adventure Works Cycles company. Identity Nullable Default Column Data Type PK DepartmentID smallint Primary key for Department records. UK Name age N ame(nvarchar(50) Name of the department. GroupName Name(nvarchar(50)) Name of the group to which the department belongs. ModifiedDate datetime Date and time the record was last updated. getdate() Table Human Resources.EmployeePay History (316 rows) Employee pay history. Nullable Default PK, FK PK Column Data Type Identity BusinessEntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. RateChangeDate Rectangular Ship datetime Date the change in pay is effective Rate money Salary hourly rate. PayFrequency tinyint 1 = Salary received monthly, 2 = Salary received biweekly ModifiedDate datetime Date and time the record was last updated. getdate() Table Human Resources. EmployeeDepartment History. (296 rows) Employee department transfers. Nullable Default Column Data Type Identity PK, FK Business EntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. PK, FK DepartmentID smallint Department in which the employee worked including currently. Foreign key to Department. DepartmentID. PK, FK ShiftID tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift. Shift.ID. PK StartDate date Date the employee started work in the department. EndDate date Date the employee left the department. NULL = Current department. ModifiedDate datetime Date and time the record was last updated. X getdate() Table HumanResources.Department (16 rows) Lookup table containing the departments within the Adventure Works Cycles company. Identity Nullable Default Column Data Type PK DepartmentID smallint Primary key for Department records. UK Name age N ame(nvarchar(50) Name of the department. GroupName Name(nvarchar(50)) Name of the group to which the department belongs. ModifiedDate datetime Date and time the record was last updated. getdate() Table Human Resources.EmployeePay History (316 rows) Employee pay history. Nullable Default PK, FK PK Column Data Type Identity BusinessEntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. RateChangeDate Rectangular Ship datetime Date the change in pay is effective Rate money Salary hourly rate. PayFrequency tinyint 1 = Salary received monthly, 2 = Salary received biweekly ModifiedDate datetime Date and time the record was last updated. getdate() Table Human Resources. EmployeeDepartment History. (296 rows) Employee department transfers. Nullable Default Column Data Type Identity PK, FK Business EntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. PK, FK DepartmentID smallint Department in which the employee worked including currently. Foreign key to Department. DepartmentID. PK, FK ShiftID tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift. Shift.ID. PK StartDate date Date the employee started work in the department. EndDate date Date the employee left the department. NULL = Current department. ModifiedDate datetime Date and time the record was last updated. X getdate()
