Question

Employee EmployeeAddress PK EmployeelD Address PK EmployeelD PK AddressID PK AddressID ContactID LoginID Title Gender HireDate BirthDate SalariedFLG VacationHours SickLeaveHours CurrentFLG ModifiedDate MaritalStatus AddressLine1 AddressLine2 City StateProvincelD PostalCode ModifiedDate AddressDate AddressType

Assume that Flags are 0 and 1. Please note all other assumptions with inline code comments.

a. Write a SQL query that will show them one value which represents the number of employees with more than 150 hours accrued vacation.

b. Write SQL ONE query that returns two rows: The first is a count of salaried employees with over 125 hours accrued vacation and the second result row is the count of non-salaried employees with over 125 hours accrued vacation.

c. Please write a SQL query that the administrative assistant can use to create mailing labels for their HOME address and fill in some variables for a form letter. Assume your query result window will allow the results to be saved to a tab delimited file format that the admin can import and auto-create labels/letters. The format should be as follows: EmployeeID, LoginID, Mr or Mrs, HireDate, VacationHours, AddressLine1, AddressLine2, City+State+Zip. Please sort the results by City Name in ascending alphabetical order. NOTE: Legal has confirmed non- salaried employees are not subject to accrual limits and should therefore NOT be included in the result set.

Answer 1

A) SELECT count(EmployeeID) FROM `employee` WHERE vacationHours>150.

This command Will count all the employee with count() functions whose vacationsHours are more than 150.

Below is the screenShot of the executed command

B) SELECT * FROM(
(Select Count(EmployeeId) as Salaried from employee where SalariedFlg=1 and vacationHours >125) Salaried,
(Select Count(EmployeeID) as NONSalaried from employee where SalariedFlg=0 and vacationHours >125) NOMSalaried
)

This command is basically combining 2 queries into 1 as stated in the que we have to write 1 query in order to achive our goal. here select * from will display everything that will proceed the query. ist query will check if salariedFlg is 1 and vacationhours are greater than 125 than then it count those employees and will print them as salaried. same goes for NON salaried Employee, Secreenshot of this query

C) select employee.EmployeeID,employee.LoginID,employee.Title,employee.HireDate,employee.vacationHours,address.Addressline1,address.AddressLine2,concat(address.city, ", ",address.StateProvisionID,", ",address.postalcode) as address from employee, employee_address, address where employee.EmployeeId=employee_address.EmployeeID and employee_address.AddressId=address.AddressId and salariedFlg=1

In the above query we are selecting columns from respective tables like we have written employee.EmployeeID it means select employeeID from employee table. then we are checking the condition and here we are matching employeeID from employee table and employeeID from Employee_address table and addressID from adress table and addressId from employee_address Table in order to match address with their respective employees. concat function concatinates multiple columns into single column. screenshot for the command is below: