https://www.youtube.com/watch?v=dOLBRfwzYcU&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=1

-- Write a query to find number of gold medals per swimmer who only won gold medals.

CREATE TABLE events ( ID int, event varchar(255), YEAR Int, GOLD varchar(255), SILVER varchar(255), BRONZE varchar(255) ); 
delete from events;

INSERT INTO events (ID, event, Year, GOLD, SILVER, BRONZE)
VALUES
    (1, '100m', 2016, 'Amthhew Mcgarray', 'donald', 'barbara'),
    (2, '200m', 2016, 'Nichole', 'Alvaro Eaton', 'janet Smith'),
    (3, '500m', 2016, 'Charles', 'Nichole', 'Susana'),
    (4, '100m', 2016, 'Ronald', 'maria', 'paula'),
    (5, '200m', 2016, 'Alfred', 'carol', 'Steven'),
    (6, '500m', 2016, 'Nichole', 'Alfred', 'Brandon'),
    (7, '100m', 2016, 'Charles', 'Dennis', 'Susana'),
    (8, '200m', 2016, 'Thomas', 'Dawn', 'catherine'),
    (9, '500m', 2016, 'Thomas', 'Dennis', 'paula'),
    (10, '100m', 2016, 'Charles', 'Dennis', 'Susana'),
    (11, '200m', 2016, 'jessica', 'Donald', 'Stefeney'),
    (12, '500m', 2016, 'Thomas', 'Steven', 'Catherine');

SELECT GOLD AS Player_Name, COUNT(*) AS Number_Of_Medals 
FROM events
WHERE GOLD NOT IN (SELECT SILVER FROM events UNION ALL SELECT BRONZE FROM events) 
GROUP BY GOLD;

WITH CTE (Player_Name,Medal_Type) AS (
SELECT GOLD AS Player_Name,'Gold' AS Medal_Type FROM events
UNION ALL
SELECT SILVER,'Silver' AS Medal_Type FROM events
UNION ALL
SELECT BRONZE,'Bronze' AS Medal_Type FROM events) 

SELECT Player_Name, COUNT(Medal_Type) FROM CTE
GROUP BY Player_Name
HAVING COUNT(DISTINCT(Medal_Type)) = 1 AND MAX(Medal_Type )= 'Gold';