use Lab1 CREATE TABLE Employee emp_id int PRIMARY KEY first_name varch

 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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
use [Lab1.2]
CREATE TABLE Employee (
emp_id int PRIMARY KEY,
first_name varchar(40),
last_name varchar(40),
birth_day date,
sex varchar(1),
salary int,
super_id int,
branch_id int
)
CREATE TABLE Branch (
branch_id int PRIMARY KEY,
branch_name varchar(40),
mgr_id int,
mgr_start_date date,
FOREIGN KEY(mgr_id) REFERENCES Employee(emp_id)
)
ALTER TABLE Employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ALTER TABLE Employee
ADD FOREIGN KEY(super_id)
REFERENCES Employee(emp_id)
CREATE TABLE Client (
client_id int PRIMARY KEY,
client_name varchar(40),
branch_id int,
FOREIGN KEY(branch_id) REFERENCES Branch(branch_id)
)
CREATE TABLE Works_with (
emp_id int,
client_id int,
total_sales int,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY(emp_id) REFERENCES Employee(emp_id),
FOREIGN KEY(client_id) REFERENCES Client(client_id)
)
CREATE TABLE Branch_Supplier (
branch_id int,
supplier_name varchar(40),
supply_type varchar(40)
PRIMARY KEY(branch_id, supplier_name),
FOREIGN KEY(branch_id) REFERENCES Branch(branch_id),
)
INSERT INTO Employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL)
INSERT INTO Branch VALUES(1, 'Corporate', 100, '2006-02-09')
UPDATE Employee
SET branch_id = 1
WHERE emp_id = 100;
INSERT INTO Employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1)
INSERT INTO Employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL)
INSERT INTO Branch VALUES(2, 'Scranton', 102, '1992-04-06')
UPDATE Employee
SET branch_id = 2
WHERE emp_id = 102
INSERT INTO Employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2)
INSERT INTO Employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2)
SELECT * FROM Employee
SELECT * FROM Branch