/* SQL Fundamentals SELF-JOINS and TABLE ALIASES */ /* EMPLOYEE table used for the SELF-JOIN tutorial. */ /* Create table with UNARY relationship. */ CREATE TABLE dbo.employee( emp_ID varchar(4) NOT NULL, emp_Fname varchar(10) NULL, emp_Lname varchar(10) NULL, emp_Supv varchar(4) NULL, CONSTRAINT PK_employee PRIMARY KEY(emp_ID) , CONSTRAINT FK_supervisor FOREIGN KEY(emp_Supv) REFERENCES employee(emp_ID)); /* Insert data */ insert into employee (emp_id, emp_fname, emp_lname, emp_supv) values ('1050', 'Carlo', 'Mora', NULL); insert into employee (emp_id, emp_fname, emp_lname, emp_supv) values ('1062', 'Robert', 'Block', 1050); insert into employee (emp_id, emp_fname, emp_lname, emp_supv) values ('1063', 'Teresa', 'Roberts', 1062); insert into employee (emp_id, emp_fname, emp_lname, emp_supv) values ('1077', 'Carla', 'Stevens', 1050); insert into employee (emp_id, emp_fname, emp_lname, emp_supv) values ('1080', 'Wilma', 'Washington', 1050); insert into employee (emp_id, emp_fname, emp_lname, emp_supv) values ('1081', 'Rory', 'Block', 1062); /* Show the employee data. */ select * from employee; /* Show employees and their supervisors. Show IDs and last names. */ select ... from employee subordinate, employee supv where subordinate.emp_supv = supv.emp_id; select sub.emp_id as "Sub ID", sub.emp_fname + ' ' + sub.emp_lname as "Subordinate", supv.emp_id as "Supv ID", supv.emp_fname + ' ' + supv.emp_lname as "Supervisor" from employee sub, employee supv where sub.emp_supv = supv.emp_id; /* List evaluatees and evaluators. Show the evaluation ID, student IDs and full names. */ select eval_ID, evaluatee.stdid as "Evaluatee ID", evaluatee.stdfname + ' ' + evaluatee.stdlname as "Evaluatee ID", evaluator.stdid as "Evaluator ID", evaluator.stdfname + ' ' + evaluator.stdlname as "Evaluator ID" from students evaluatee, students evaluator, evaluations where evaluatee.stdid = evaluations.evaluateeID and evaluator.stdid = evaluations.evaluatorID order by evaluateeID;