SELECT
  FNAME + ' ' + LNAME AS 'Employee Name', ISNULL(QUALID, 'NoQual') AS 'Qualifications'
FROM
  EMPLOYEE, QUALIFICATION
WHERE
  EMPLOYEE.QUALID = QUALIFICATION.QUALID;

CREATE TABLE emplevel 
	(LevelNo	int,
 	LowSalary	int,
	HighSalary	int,
	CONSTRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo));  
GO

CREATE TABLE position
	(PositionId	int,
	PosDesc	VARCHAR (10),
	CONSTRAINT position_positionid_pk PRIMARY KEY (PositionId));
GO

CREATE TABLE qualification
	(QualId	int,
	QualDesc	VARCHAR (11),
	CONSTRAINT qualification_qualid_pk PRIMARY KEY (QualId)
);
GO

CREATE TABLE dept
	(DeptId	int,
	DeptName	VARCHAR (12) ,
	Location	VARCHAR (15),
	EmployeeId	int,
	CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId)
);
GO

CREATE TABLE employee 
	(EmployeeId   int,
      	Lname		VARCHAR (15) CONSTRAINT employee_lname_nn NOT NULL,
	Fname 		VARCHAR (15) CONSTRAINT employee_fname_nn NOT NULL, 
	PositionId 	int, 
	Supervisor	int,
      	HireDate  	DATETIME,
	Salary		int,
      	Commission	int,
      	DeptId		int,
	QualId		int,
	CONSTRAINT employee_employeeid_pk 
		PRIMARY KEY (EmployeeId)
	);
GO

CREATE TABLE dependent 	
	(EmployeeId    	int,
      	DependentId	int,
	DepDOB	DATETIME, 
	Relation	VARCHAR (8),
	CONSTRAINT dependent_empiddepid_pk PRIMARY KEY (EmployeeId, DependentId)
);
GO

INSERT INTO position VALUES (1, 'President');
INSERT INTO position VALUES (2, 'Manager');
INSERT INTO position VALUES (3, 'Programmer');
INSERT INTO position VALUES (4, 'Accountant');
INSERT INTO position VALUES (5, 'Salesman');

INSERT INTO emplevel VALUES (1, 1, 25000);
INSERT INTO emplevel VALUES (2, 25001, 50000);
INSERT INTO emplevel VALUES (3, 50001, 100000);
INSERT INTO emplevel VALUES (4, 100001, 500000);

INSERT INTO qualification VALUES (1, 'Doctorate');
INSERT INTO qualification VALUES (2, 'Masters');
INSERT INTO qualification VALUES (3, 'Bachelors');
INSERT INTO qualification VALUES (4, 'Associates');
INSERT INTO qualification VALUES (5, 'High School');

INSERT INTO dept VALUES (10, 'Finance', 'Charlotte', 123);
INSERT INTO dept VALUES (20, 'InfoSys', 'New York', 543);
INSERT INTO dept VALUES (30, 'Sales', 'Woodbridge', 135);
INSERT INTO dept VALUES (40, 'Marketing', 'Los Angeles', 246);

INSERT INTO employee VALUES (111, 'Smith', 'John', 1, NULL,'04/15/1960', 265000, 35000, 10, 1);
INSERT INTO employee VALUES (246, 'Houston', 'Larry', 2, 111,'05/19/1967', 150000, 10000, 40, 2);
INSERT INTO employee VALUES (123, 'Roberts', 'Sandi', 2, 111,'12/02/1991',75000, NULL, 10, 2);
INSERT INTO employee VALUES (433, 'McCall', 'Alex', 3, 543,'05/10/1997',66500, NULL, 20, 4);
INSERT INTO employee VALUES (543, 'Dev', 'Derek', 2, 111,'03/15/1995',80000, 20000, 20, 1);
INSERT INTO employee VALUES (200, 'Shaw', 'Jinku', 5, 135,'01/03/00',24500, 3000, 30, NULL);
INSERT INTO employee VALUES (135, 'Garner', 'Stanley', 2, 111,'02/29/1996',45000, 5000, 30, 5);
INSERT INTO employee VALUES (222, 'Chen', 'Sunny', 4, 123,'08/15/1999',35000, NULL, 10, 3);

INSERT INTO dependent VALUES (543, 1,'09/28/1958','Spouse');
INSERT INTO dependent VALUES (543, 2,'10/14/1988','Son');
INSERT INTO dependent VALUES (200, 1,'06/10/1976','Spouse');
INSERT INTO dependent VALUES (222, 1,'02/04/1975','Spouse');
INSERT INTO dependent VALUES (222, 2,'08/23/1997','Son');
INSERT INTO dependent VALUES (222, 3,'07/10/1999','Daughter');
INSERT INTO dependent VALUES (111, 1,'12/12/1945','Spouse');

ALTER TABLE dept
ADD CONSTRAINT dept_employeeid_fk FOREIGN KEY(EmployeeId)
	REFERENCES employee(EmployeeId);
GO
--ALTER TABLE employee
--ADD CONSTRAINT employee_supervisor_fk FOREIGN KEY(Supervisor)
--	REFERENCES employee(EmployeeId);

ALTER TABLE employee ADD CONSTRAINT employee_positionid_fk FOREIGN KEY (PositionId)
		REFERENCES position (PositionId);
GO

ALTER TABLE employee ADD CONSTRAINT employee_deptid_fk FOREIGN KEY (DeptId)
		REFERENCES dept (DeptId);
GO

ALTER TABLE employee ADD CONSTRAINT employee_qualid_fk FOREIGN KEY (QualId)
		REFERENCES qualification (QualId);
GO

ALTER TABLE dependent ADD CONSTRAINT dependent_employeeid_fk FOREIGN KEY (EmployeeId)
		REFERENCES employee (EmployeeId);
GO