-- Section III queries


-- Great query to start with
-- CLUSTERED INDEX SCAN > TOP > SELECT

SELECT TOP 10 id, fname, lname
FROM ACTOR
WHERE fname LIKE 'Siena';



-- Like the previous but more complicated
-- Adds in nested loops and scanning from multiple tables

-- Get all actors in the movie "Pi"
SELECT a.fname, a.lname
FROM ACTOR a, CASTS c, MOVIE m
WHERE a.id = c.pid
  AND m.id = c.mid
  AND m.name = 'Pi';



-- More nested loops but introduces Hash Match (can be faster than nested loops)

-- All actors that appeared in both Kill Bill Vol1 AND Vol 2
SELECT a.fname, a.lname
FROM ACTOR a, CASTS c1, MOVIE m1, CASTS c2, MOVIE m2
WHERE c1.pid = a.id AND m1.id = c1.mid AND c2.pid = a.id AND m2.id = c2.mid
  AND m1.name = 'Kill Bill: Vol. 1' AND m2.name = 'Kill Bill: Vol. 2';

-- The same except with nested query and only uses nested loops
-- Point out the increased number of disk reads (magnifying glass()) due to the nested query versus joins
SELECT a.fname, a.lname
FROM ACTOR a, CASTS c, MOVIE m
WHERE c.pid = a.id AND m.id = c.mid AND m.name = 'Kill Bill: Vol. 1'
  AND a.id IN (SELECT a.id
               FROM ACTOR a, CASTS c, MOVIE m
               WHERE c.pid = a.id AND m.id = c.mid AND m.name = 'Kill Bill: Vol. 2');
