use Lab1 CREATE TABLE Employee emp_id int PRIMARY KEY first_name varch

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