Quete subqueries

gistfile1.txt

-- Question 1 :

SELECT `first_name`, `last_name`
FROM `wizard`
WHERE wizard.id IN (
	SELECT `wizard_id` 
	FROM `player`
	WHERE `enrollment_date`
	BETWEEN '1995-01-01' AND '1998-12-31'
);

-- Question 2 :

SELECT `first_name`, `last_name`
FROM `wizard`
WHERE wizard.id IN (
	SELECT `wizard_id` 
	FROM `player`
	WHERE `enrollment_date`
	BETWEEN '1995-01-01' AND '1998-12-31'
    AND `role` = 'seeker'
);

-- Question 3 :

SELECT `first_name`, `last_name`, `role`
FROM `wizard` INNER JOIN `player`
ON wizard.id = wizard_id
WHERE wizard.id IN (
	SELECT `wizard_id` 
	FROM `player`
	WHERE `enrollment_date`
	BETWEEN '1995-01-01' AND '1998-12-31'
    AND `role` = 'chaser'
);

-- Question 4 :

CREATE OR REPLACE VIEW role_team AS (
	SELECT `role`, team.name, count(wizard_id) as N_players
	FROM `player`, `team`
	WHERE team.id = player.id
	GROUP BY team.name
);

SELECT * from role_team;