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