/* 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;