Need help writing T-SQL statements?

[ldphill]

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

[iburyak]

1. Write a T-SQL statement that will return benefits that are not terminated.
[PHP]
SELECT SSN, b.BenefitName
FROM BenefitDetail bd
JOIN Benefit b on bd.BenefitID = b.BenefitID
WHERE bd.[Termination Date] is null[/PHP]

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.

[PHP]
SELECT e.EmpSSN, e.LastNm, e.FirstNm
FROM Employee e
JOIN BenefitDetail bd on e.EmpSSN = bd.SSN
WHERE bd.[Termination Date] is null -- terminated benefits
and e.EmpSSN not in (SELECT EmpSSN from Dependent) -- have no dependents[/PHP]

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



[PHP]--GET EMPLOYEES
SELECT EmpSSN, '' DepSSN, LastNm, FirstNm
FROM Employee
UNION
--GET DEPENDENTS
SELECT d.EmpSSN, d.DepSSN, d.LastNm, d.FirstNm
FROM Employee e
JOIN Dependent d on e.EmpSSN = d.EmpSSN
ORDER BY 1,2

--ORDER BY will always put employee first because he has dependent ''[/PHP]

Good Luck

[ldphill]

Iburyak,

Thank you for your help. I really appreciate it.

Ldphill