summaryrefslogtreecommitdiffstats
path: root/sandbox/lresende/backup/company-das-webapp/company.sql
blob: 8fb5c2d4cd1d11de462143fcafc0398fe535c80b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;
DROP TABLE COMPANY;

CREATE TABLE COMPANY (
   ID INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY, 
   NAME VARCHAR(30));

CREATE TABLE DEPARTMENT (ID INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY, 
   NAME VARCHAR(30),
   LOCATION VARCHAR(30), 
   DEPNUMBER VARCHAR(10),
   COMPANYID INT);

CREATE TABLE EMPLOYEE (ID INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
   NAME VARCHAR(30),
   SN VARCHAR(10), 
   MANAGER SMALLINT, 
   DEPARTMENTID INT);


INSERT INTO COMPANY (NAME) VALUES('ACME Publishing');
INSERT INTO COMPANY (NAME) VALUES('Do-rite plumbing');
INSERT INTO COMPANY (NAME) VALUES('MegaCorp');

INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Advanced Technologies','NY','123', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 2','','', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 3','','', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 4','','', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 5','','', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 6','','', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 7','','', 1);
INSERT INTO DEPARTMENT (NAME, LOCATION, DEPNUMBER, COMPANYID) VALUES('Default Name 8','','', 1);

INSERT INTO EMPLOYEE (NAME, SN, MANAGER, DEPARTMENTID) VALUES('John Jones','E0001', 0, 1);
INSERT INTO EMPLOYEE (NAME, SN, MANAGER, DEPARTMENTID) VALUES('Mary Smith','E0002', 1, null);
INSERT INTO EMPLOYEE (NAME, SN, MANAGER, DEPARTMENTID) VALUES('Jane Doe','E0003', 0, 1);
INSERT INTO EMPLOYEE (NAME, SN, MANAGER, DEPARTMENTID) VALUES('Al Smith','E0004', 1, 1);