Tasks - A project management tool keeps data in the... SQL code example

Example: Tasks - A project management tool keeps data in the... SQL

/*
A project management tool keeps data in the following two tables: TABLE emp ...
*/

Create table employee3 (
id int not null Primary key,
name nvarchar(50) not null)

drop table if exists tasks
create table tasks (
id int not null primary key,
auhtoiId int not null REFERENCEs EMPLOYEE3(id),
assigneeId int REFERENCEs EMPLOYEE3(id)
)

insert into employee3(id, name) 
			values (1,'Richard'),
				(2, 'Lily')

insert into tasks(id, auhtoiId, assigneeId) 
			values (1,1, NULL),
			(2,2,1)
-- Write a query that selects task id, author name and assignee name for easch task
-- If there is no assignee for a task, the query should return NULL 
-- instead of assign name
select * from employee3;
select * from tasks;

Select t.id as 'Task Id', 
	(Select e1.name 
		from employee3 e1
		where e1.id = t.auhtoiId) as 'Auther Name' ,
	(Select e2.name 
		from employee3 e2
		where e2.id = t.assigneeId) as 'Assigne Name'
	from tasks t