-- This script builds the tables in Exercise 4.4
/* this is another way to comment in SQL script */

DROP TABLE Emp;
DROP TABLE Assign;
DROP TABLE Proj;

CREATE TABLE Emp(
	empID VarChar(5) NOT NULL,
	lastName Char(25) NOT NULL,
	CONSTRAINT EMPLOYEE_PK PRIMARY KEY(empID)
);

CREATE TABLE Assign(
	empID VarChar(4),
	projNo VarChar(4),
	hours Int
);

CREATE TABLE Proj(
	projNo VarChar(4),
	projName Char(15),
	budget Int
);

/******  Adding data to the Employee table ******/  
INSERT INTO Emp(empID, lastName)
	VALUES
	('E101', 'Smith'),
	('E105', 'Jones'),
	('E110', 'Adams'),
	('E115', 'Smith')
	;

/******  Adding data to the Assign table ******/  
INSERT INTO Assign(empID, projNo, hours)
	VALUES
	('E101', 'P10', 200),
	('E101', 'P15', 300),
	('E105', 'P10', 400), 
	('E110', 'P15', 700), 
	('E110', 'P20', 350),
	('E115', 'P10', 300), 
	('E115', 'P20', 400)  
;

/******  Adding data to the Proj table ******/  
INSERT INTO Proj(projNo, projName, budget)
	VALUES
	('P10', 'Hudson', 500000),
	('P15', 'Columbia', 350000),
	('P20', 'Wabash', 350000), 
	('P23', 'Arkansas', 600000) 
;

SELECT Emp.lastName, Assign.projNo FROM Emp, Assign WHERE Emp.empID=Assign.empID;