-- Work on 3 tables of the form Ta(aid, a2, …), Tb(bid, b2, …), Tc(cid, aid, bid, …), where:

-- aid, bid, cid, a2, b2 are integers;
-- the primary keys are underlined;
-- a2 is UNIQUE in Ta;
-- aid and bid are foreign keys in Tc, referencing the primary keys in Ta and Tb, respectively.
-- a. Write queries on Ta such that their execution plans contain the following operators:

-- clustered index scan;
-- clustered index seek;
-- nonclustered index scan;
-- nonclustered index seek;
-- key lookup.
-- b. Write a query on table Tb with a WHERE clause of the form WHERE b2 = value and analyze its execution plan. Create a nonclustered index that can speed up the query. Examine the execution plan again.

-- c. Create a view that joins at least 2 tables. Check whether existing indexes are helpful; if not, reassess existing indexes / examine the cardinality of the tables.

--a. Write queries on Ta such that their execution plans contain the following operators:

-- clustered index scan;

--create unique constraint on name column
 
SELECT * FROM Players;

-- clustered index seek;

SELECT * FROM Players WHERE hmy = 22003;

-- nonclustered index seek;

-- add nonclustered index on name
CREATE NONCLUSTERED INDEX IX_Players_name ON Players(hmy, name) INCLUDE (totalMatches);

DROP INDEX IX_Players_name ON Players;

SELECT name, totalMatches FROM Players WHERE name = 'lerio2';

-- nonclustered index scan;

SELECT name, totalMatches FROM Players WHERE totalMatches > 0;

-- key lookup.

-- write a query that uses the nonclustered index and a key lookup
SELECT * FROM Players  WHERE name = 'lerio2';

--b. Write a query on table Tb with a WHERE clause of the form WHERE b2 = value and analyze its execution plan. Create a nonclustered index that can speed up the query. Examine the execution plan again.

-- create nonclustered index on b2
CREATE NONCLUSTERED INDEX IX_Cards_faction_cardset ON Cards(hmy, faction, cardset) INCLUDE (name, cost);

DROP INDEX IX_Cards_faction_cardset ON Cards;

-- examine execution plan
SELECT hmy, name, cost, faction, cardset FROM Cards WHERE faction = (SELECT hmy FROM Factions WHERE name='Neutral') AND cardset = (SELECT hmy FROM CardSet WHERE name='Base Set');

--c. Create a view that joins at least 2 tables. Check whether existing indexes are helpful; if not, reassess existing indexes / examine the cardinality of the tables.

-- create view
GO

ALTER VIEW CardsOwners AS
SELECT Cards.name AS cardName, Players.name AS playerName, Cards.hmy AS cardId, Players.hmy AS playerId
FROM Cards
INNER JOIN CardsOwnership ON Cards.hmy = CardsOwnership.hCard
INNER JOIN Players ON Players.hmy = CardsOwnership.hPlayer

GO

-- check if indexes are helpful

SELECT * FROM CardsOwners;