CREATE TABLE EMPLOYEE(ID int constraint PK_EMPLOYEE primary key,
					 DEPARTAMENT_ID int NOT NULL,
					 CHIEF_ID int constraint EMPLOYEE_ID_FK foreign key (CHIEF_ID) references EMPLOYEE (ID),
					 NAME nvarchar(200) NOT NULL,
					 POSITION nvarchar(200) NOT NULL,
					 SALARY int NOT NULL);



INSERT INTO EMPLOYEE (ID,DEPARTAMENT_ID,NAME,POSITION,SALARY)
					VALUES(1,25,'Albert Thegenius','Director',1800)

insert into EMPLOYEE (ID,DEPARTAMENT_ID,CHIEF_ID,NAME,POSITION,SALARY)
			values(2,15,1,'Oleg Kovalev','Top manager of Outsource Develoment',1200),
					(4,15,2,'Ignat Myasnikov','C# developer',600),
					(5,15,2,'Jason Stepanov','SQL developer',500),
					(6,15,2,'Elena Berezova','HR',400),
					(7,15,2,'Pasha Nekrasov','JS developer',500),
					(8,15,2,'Georgiy Vasiliev','JS developer',500),
					(9,15,2,'Olga Mikhailova','HR',400),
					(10,15,2,'Elisa Skinder','HR',400)


insert into EMPLOYEE (ID,DEPARTAMENT_ID,CHIEF_ID,NAME,POSITION,SALARY)
			values(3,10,1,'Dmitriy Petrov','Top manager of Opensource Development',1200),
					(11,10,3,'Jerry Bondarchik','business analyst',800),
					(12 ,10,3,'Yuri Ulasevich','Python developer',700),
					(13,10,3,'Vladislav Bogdanovski','Ruby developer',400),
					(14,10,3,'Jason Fedorov','JS developer',500)

					select * from EMPLOYEE


select * from EMPLOYEE where DEPARTAMENT_ID=15

use ALFA;

/*task1*/
SELECT DISTINCT EM1.NAME, EM2.POSITION FROM EMPLOYEE EM1, EMPLOYEE EM2 WHERE EM1.DEPARTAMENT_ID = 15 AND EM2.DEPARTAMENT_ID = 15;
/*task2*/
SELECT NAME FROM EMPLOYEE EMP1 WHERE EMP1.SALARY = 2 * (SELECT AVG(SALARY)
FROM EMPLOYEE EMP2 WHERE EMP2.CHIEF_ID = EMP1.ID) group by name;

drop table EMPLOYEE