I have the following database model:
Employee -Table
PK EmpSSN varchar (13)
FirstNm varchar (40)
LastNm varchar (40)
Dependent - Table
PK DepSSN varchar (13)
FirstNm varchar (40)
LastNm varchar (40)
EmpSSN varchar (13)
BenefitDetail - Table
PK BenefitID int
PK SSN varchar (13)
PK EffectiveDate datetime
Termination Date datetime
Benefit - Table
PK BenefitID int
BenefitName varchar(25)
The Employee table describes the employee eligible for health care benefits. The Dependent table has a foreign key relationship with the Employee. The Benefit table has an identity primary key id which assigns a unique value. The BenefitName column will be populated with values like MEDICAL, DENTAL, and VISION. The BenefitDetail table lists the benefits a person has whether they are an employee or dependent, the effective date of the benefit, and the termination date. The termination date is not a required field (the field will be null if the benefit is not terminated).
1. Write a T-SQL statement that will return benefits that are not terminated.
2. Write a T-SQL statement that will return all of the employees who have terminated benefits, have no active benefits, and have no dependents. List the employee social security number, last name, and firstname.
3. Write a T-SQL statement that will list in order all of employees followed by their dependents. List the employee social security number, dependent social security number(if the person is a dependent), last name, and firstname.
Example:
EmpSSN DepSSN Lastname Firstname
123-45-6789 Doe John
123-45-6789 567-34-3432 Doe John
987-43-9746 Smith Rob
987-43-9746 345-34-3434 Smith Jane
I have been away from technology for awhile. I am trying to re-enter the industry. I have attempted to write the queries. I would appreciate any help you could give me.
Thanks,
Leah Phillips