SHARE
TWEET

Untitled

a guest Sep 16th, 2019 75 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE EMPLOYEE(ID int constraint PK_EMPLOYEE primary key,
  2.                      DEPARTAMENT_ID int NOT NULL,
  3.                      CHIEF_ID int constraint EMPLOYEE_ID_FK foreign key (CHIEF_ID) references EMPLOYEE (ID),
  4.                      NAME nvarchar(200) NOT NULL,
  5.                      POSITION nvarchar(200) NOT NULL,
  6.                      SALARY int NOT NULL);
  7.  
  8.                      
  9.                      
  10. INSERT INTO EMPLOYEE (ID,DEPARTAMENT_ID,NAME,POSITION,SALARY)
  11.                     VALUES(1,25,'Albert Thegenius','Director',1800)
  12.  
  13. insert into EMPLOYEE (ID,DEPARTAMENT_ID,CHIEF_ID,NAME,POSITION,SALARY)
  14.             values(2,15,1,'Oleg Kovalev','Top manager of Outsource Develoment',1200),
  15.                     (4,15,2,'Ignat Myasnikov','C# developer',600),
  16.                     (5,15,2,'Jason Stepanov','SQL developer',500),
  17.                     (6,15,2,'Elena Berezova','HR',400),
  18.                     (7,15,2,'Pasha Nekrasov','JS developer',500),
  19.                     (8,15,2,'Georgiy Vasiliev','JS developer',500),
  20.                     (9,15,2,'Olga Mikhailova','HR',400),
  21.                     (10,15,2,'Elisa Skinder','HR',400)
  22.  
  23.  
  24. insert into EMPLOYEE (ID,DEPARTAMENT_ID,CHIEF_ID,NAME,POSITION,SALARY)
  25.             values(3,10,1,'Dmitriy Petrov','Top manager of Opensource Development',1200),
  26.                     (11,10,3,'Jerry Bondarchik','business analyst',800),
  27.                     (12 ,10,3,'Yuri Ulasevich','Python developer',700),
  28.                     (13,10,3,'Vladislav Bogdanovski','Ruby developer',400),
  29.                     (14,10,3,'Jason Fedorov','JS developer',500)
  30.  
  31.                     select * from EMPLOYEE
  32.  
  33.  
  34. select * from EMPLOYEE where DEPARTAMENT_ID=15
  35.  
  36. use ALFA;
  37.  
  38. /*task1*/
  39. SELECT DISTINCT EM1.NAME, EM2.POSITION FROM EMPLOYEE EM1, EMPLOYEE EM2 WHERE EM1.DEPARTAMENT_ID = 15 AND EM2.DEPARTAMENT_ID = 15;
  40. /*task2*/  
  41. SELECT NAME FROM EMPLOYEE EMP1 WHERE EMP1.SALARY = 2 * (SELECT AVG(SALARY)
  42. FROM EMPLOYEE EMP2 WHERE EMP2.CHIEF_ID = EMP1.ID) group by name;
  43.  
  44. drop table EMPLOYEE
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top