251 lines
5.7 KiB
SQL
251 lines
5.7 KiB
SQL
--2 queries with the union operation; use UNION [ALL] and OR;
|
|
|
|
-- 1. Find the names of all the cards that are either gold or have a power of 8 or greater.
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE color = 'gold'
|
|
UNION
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE power >= 8;
|
|
|
|
-- 2. Find the names of all the cards that are either bronze or have a power of 7 or less.
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE color = 'bronze' OR power <= 7;
|
|
|
|
--2 queries with the intersection operation; use INTERSECT and IN;
|
|
|
|
-- 1. Find the names of all the cards that are both gold and are unit cards.
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE color = 'gold'
|
|
INTERSECT
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE type = 'unit';
|
|
|
|
-- 2. Find the names of all the cards that are both bronze and are special cards.
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE color = 'bronze' and name IN
|
|
(SELECT name
|
|
FROM Cards
|
|
WHERE type = 'special');
|
|
|
|
--2 queries with the difference operation; use EXCEPT and NOT IN;
|
|
|
|
-- 1. Find the names of all the cards that are not used in any deck.
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
EXCEPT
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE name IN
|
|
(SELECT name
|
|
FROM CardsOwnership);
|
|
|
|
-- 2. Find the names of all cosmetics not owned by lerio2
|
|
|
|
SELECT name
|
|
FROM Cosmetics
|
|
WHERE hmy NOT IN
|
|
(SELECT hCosmetic
|
|
FROM CosmeticsOwnership
|
|
WHERE hPlayer = (SELECT hmy FROM Players WHERE name = 'lerio2'));
|
|
|
|
-- 4 queries with INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN (one query per operator); one query will join at least 3 tables, while another one will join at least two many-to-many relationships;
|
|
|
|
-- 1. Find all player and their decks.
|
|
|
|
SELECT p.name, d.name
|
|
FROM Decks d
|
|
RIGHT JOIN Players p ON p.hmy = d.owner;
|
|
|
|
|
|
-- 2. Find the names of each player, faction they played with and the result of the match.
|
|
|
|
SELECT p.name as Player1, f.name as Faction1, m.result, p2.name as Player2, f2.name as Faction2
|
|
FROM Matches m
|
|
INNER JOIN Players p ON m.hPlayer1 = p.hmy
|
|
INNER JOIN Factions f ON m.hPlayer1Faction = f.hmy
|
|
INNER JOIN Players p2 ON m.hPlayer2 = p2.hmy
|
|
INNER JOIN Factions f2 ON m.hPlayer2Faction = f2.hmy;
|
|
|
|
-- 3. Find the names of all cards and the decks they are used in.
|
|
|
|
SELECT c.name, d.name
|
|
FROM Cards c
|
|
LEFT JOIN DecksCards dc ON c.hmy = dc.hCard
|
|
LEFT JOIN Decks d ON dc.hDeck = d.hmy;
|
|
|
|
-- 4. Find the relationship between players and their cosmetics.
|
|
|
|
SELECT p.name, c.name
|
|
FROM Players p
|
|
FULL JOIN CosmeticsOwnership co ON p.hmy = co.hPlayer
|
|
FULL JOIN Cosmetics c ON co.hCosmetic = c.hmy;
|
|
|
|
-- 2 queries with the IN operator and a subquery in the WHERE clause; in at least one case, the subquery must include a subquery in its own WHERE clause;
|
|
|
|
-- 1. Find the names of all the cards that are used in any deck.
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE hmy IN
|
|
(SELECT hCard
|
|
FROM DecksCards);
|
|
|
|
-- 2. Find the names of all the cards that are used in any deck owned by Sif_Great_Wolf.
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE hmy IN
|
|
(SELECT hCard
|
|
FROM DecksCards
|
|
WHERE hDeck IN
|
|
(SELECT hmy
|
|
FROM Decks
|
|
WHERE owner = (SELECT hmy FROM Players WHERE name = 'Sif_Great_Wolf')));
|
|
|
|
-- 2 queries with the EXISTS operator and a subquery in the WHERE clause;
|
|
|
|
-- 1. Find the names of all the players that have at least one deck.
|
|
|
|
SELECT p.name
|
|
FROM Players p
|
|
WHERE EXISTS
|
|
(SELECT d.hmy
|
|
FROM Decks d
|
|
WHERE d.owner = p.hmy);
|
|
|
|
-- 2. Find all the players that have started the journey
|
|
|
|
SELECT p.name
|
|
FROM Players p
|
|
WHERE EXISTS
|
|
(SELECT j.hPlayer
|
|
FROM JourneyProgress j
|
|
WHERE j.hPlayer = p.hmy)
|
|
|
|
|
|
-- 2 queries with a subquery in the FROM clause;
|
|
|
|
-- 1. Find the decks of all pro players
|
|
|
|
SELECT DISTINCT d.name
|
|
FROM (
|
|
SELECT *
|
|
FROM Players
|
|
WHERE rank = 0
|
|
) as proPlayers
|
|
INNER JOIN Decks d on d.owner = proPlayers.hmy
|
|
|
|
|
|
-- 2. Find the decks of all active players
|
|
|
|
SELECT DISTINCT d.name
|
|
FROM (
|
|
SELECT *
|
|
FROM Players
|
|
WHERE totalMatches <> 0
|
|
) as activePlayers
|
|
INNER JOIN Decks d on d.owner = activePlayers.hmy
|
|
|
|
-- 4 queries with the GROUP BY clause, 3 of which also contain the HAVING clause; 2 of the latter will also have a subquery in the HAVING clause; use the aggregation operators: COUNT, SUM, AVG, MIN, MAX;
|
|
|
|
-- 1. Calculate the total number of unit cards
|
|
|
|
SELECT COUNT(*)
|
|
FROM Cards
|
|
GROUP BY type
|
|
HAVING type = 'unit';
|
|
|
|
-- 2. Calculate the average number of cards in a deck
|
|
|
|
SELECT AVG(cardCount)
|
|
FROM (
|
|
SELECT COUNT(*) as cardCount
|
|
FROM DecksCards
|
|
GROUP BY hDeck
|
|
) as cardCounts;
|
|
|
|
-- 3. Calculate the average cost of special cards
|
|
|
|
SELECT AVG(cost)
|
|
FROM Cards
|
|
GROUP BY type
|
|
HAVING type = 'special';
|
|
|
|
-- 4. Calculate the minimum power of a unit card
|
|
|
|
SELECT MIN(power)
|
|
FROM Cards
|
|
GROUP BY type
|
|
HAVING type = 'unit';
|
|
|
|
-- 4 queries using ANY and ALL to introduce a subquery in the WHERE clause (2 queries per operator); rewrite 2 of them with aggregation operators, and the other 2 with IN / [NOT] IN.
|
|
|
|
-- 1. Find the names of all the cards that are used in any deck.
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE hmy IN
|
|
(SELECT hCard
|
|
FROM DecksCards);
|
|
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE hmy = ANY
|
|
(SELECT hCard
|
|
FROM DecksCards);
|
|
|
|
-- 2. Find the cards that have the minimum cost
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE cost <= ALL(SELECT cost FROM Cards);
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE cost <= (SELECT MIN(cost) FROM Cards);
|
|
|
|
-- 3. Find the names of all the cards that are used in any deck owned by Sif_Great_Wolf.
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE hmy IN
|
|
(SELECT hCard
|
|
FROM DecksCards
|
|
WHERE hDeck IN
|
|
(SELECT hmy
|
|
FROM Decks
|
|
WHERE owner = (SELECT hmy FROM Players WHERE name = 'Sif_Great_Wolf')));
|
|
|
|
SELECT name
|
|
FROM Cards
|
|
WHERE hmy = ANY
|
|
(SELECT hCard
|
|
FROM DecksCards
|
|
WHERE hDeck = ANY
|
|
(SELECT hmy
|
|
FROM Decks
|
|
WHERE owner = (SELECT hmy FROM Players WHERE name = 'Sif_Great_Wolf')));
|
|
|
|
-- 4. Find the player that has the biggest level in the Ciri journey
|
|
|
|
SELECT name
|
|
FROM Players
|
|
WHERE hmy >= ALL(
|
|
SELECT hPlayer
|
|
FROM JourneyProgress
|
|
WHERE hJourney = 1);
|
|
|
|
SELECT name
|
|
FROM Players
|
|
WHERE hmy >= (SELECT MAX(hPlayer) FROM JourneyProgress WHERE hJourney = 1);
|
|
|