/* SQL Fundamentals SET OPERATORS */ /* Show a list of students who got 90 or higher on their CONTRIBUTE evaluation item (they were evaluatees). */ select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90; /* Show a list of students who got 90 or higher on their RELIABLE evaluation item (they were evaluatees). */ select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; /* UNION the lists. */ select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 UNION select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; /* Remove the eval_item_ID column from the SELECT clause. There are fewer rows because some students were listed more than once before. */ select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 UNION select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; /* INTERSECT the two lists. Show students who got 90 or higher for both CONTRIBUTE and RELIABLE. */ select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 INTERSECT select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; /* Use EXCEPT. Show students who got 90 or higher for CONTRIBUTE but not RELIABLE. */ select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 EXCEPT select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90;