Join Related SQL Query Help

4. JOIN related SQL Query -

EMPLOYEE TABLE

ASSIGNMENT TABLE

1. get employee name, project name order by firstname from "EMPLOYEE" and "ASSIGNMENT" for those employee which have assigned project already.

SELECT FIRST_NAME, ASSIGNMENT_NAME FROM EMPLOYEE A INNER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME

2. order by first_name "EMPLOYEE" and "ASSIGNMENT" for all employee even they have not assigned assignment.

SELECT FIRST_NAME, ASSIGNMENT_NAME FROM EMPLOYEE A LEFT JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME

3. get all assignment name even they have not matching any employeeid, in left table, order by firstname from employee and assignment.

SELECT FIRSTNAME, ASSIGNMENTNAME FROM EMPLOYEE A RIGHT OUTER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRSTNAME

4. get complete record from both table, if no match found in any table then show null.

SELECT FIRSTNAME, ASSIGNMENT_NAME FROM EMPLOYEE A FULL OUTER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME

5. query to get the employee name who has not assigned any assignment, and display “no project assigned”

SELECT FIRST_NAME, ISNULL(ASSIGNMENT_NAME-‘NO ASSIGNMENT ASSIGNED’) AS ASSIGNMENT FROM EMPLOYEE A LEFT OUTER JOIN ASSIGNMENT B ON A.EMP_ID = B.ASSIGN_ID WHERE ASSIGNMENT_NAME IS NULL