USE MASTER
GO
CREATE DATABASE HR
GO
USE HR 
GO

CREATE TABLE [dbo].[Department](
    [DNAME] [varchar](20) NULL,
    [DLOCATION] [varchar](20) NULL,
    [DEPTNO] BIGINT IDENTITY (1, 1) NOT NULL,    
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([DEPTNO] ASC)
);

SET IDENTITY_INSERT Department ON
GO

INSERT INTO dbo.Department(DEPTNO,DNAME,DLOCATION) VALUES(1001,'IT Department','Paris');
INSERT INTO dbo.Department(DEPTNO,DNAME,DLOCATION) VALUES(4500,'HR & Talents','Paris');
INSERT INTO dbo.Department(DEPTNO,DNAME,DLOCATION) VALUES(5007,'Warehouses','Lille');
INSERT INTO dbo.Department(DEPTNO,DNAME,DLOCATION) VALUES(6750,'Logistics','London');
INSERT INTO dbo.Department(DEPTNO,DNAME,DLOCATION) VALUES(9888,'Finance & Accounting','New York');

SET IDENTITY_INSERT Department OFF
GO

CREATE TABLE [dbo].[Employee](
    [ELASTNAME] [varchar](20) NULL,
    [EFIRSTNAME] [varchar](20) NULL,
    [SAL] [numeric](20, 12) NULL,
    [EMPNO] BIGINT IDENTITY (1, 1) NOT NULL,
    [DEPTNO] BIGINT FOREIGN KEY REFERENCES Department(DEPTNO)
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EMPNO] ASC)
);
GO

SET IDENTITY_INSERT Employee ON
GO

INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7369,'SMITH','John',800, 1001);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7499,'ALLEN','George',1600, 5007);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7521,'WARD','Ashley',1250, 9888);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7566,'JONES','Henri',2975, 1001);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7654,'MARTIN','Jacque',1250, 4500);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7698,'BLAKE','James',2850, 6750);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7782,'CLARK','Ed',2450, 5007);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7788,'SCOTT','Thomas',3000, 1001);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7839,'KING','Larry',5000, 9888);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7844,'TURNER','Emma',1500, 6750);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7876,'ADAMS','Gomez',1100, 4500);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7900,'JAMES','Barbara',950, 4500);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7902,'FORD','Gerald',3000, 5007);
INSERT INTO dbo.Employee(EMPNO,ELASTNAME,EFIRSTNAME,SAL,DEPTNO) VALUES (7934,'MILLER','Arthur',1300, 9888);