Question

Consider a database schema consisting of two tables, Employee (ID, Name, Address), Project(PID, Name, Deadline), Assign (EID, PID, Date). Assign.EID is a foreign key referencing employee's ID and Assign.PID is a foreign key reference the project.

Write the SQL query for

1. Find Projects that are not assigned to any employees(PID and Name of the project).

Answer 1

SELECT
p.id,
p.name
FROM
project_tbl p
WHERE
p.id NOT IN (
SELECT
p_id
FROM
project_assignment_tbl ap
inner join employee_table e
WHERE
ap.empid = e.id
AND ap.date IS NULL
)