Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE COUNTRY_TABLE (
    COUNTRY_ID VARCHAR(2) NOT NULL,
    COUNTRY_CODE VARCHAR(3) NOT NULL,
    COUNTRY_NAME VARCHAR(40) NOT NULL
);
INSERT INTO COUNTRY_TABLE    (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME) VALUES    
  ('01', 'UKD', 'UNITED KINGDOM'); 
1 rows affected

INSERT INTO COUNTRY_TABLE    (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME) VALUES	  ('02', 'EUR', 'EUROPE');  
1 rows affected

INSERT INTO COUNTRY_TABLE    (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME) VALUES	  ('03', 'ASA', 'ASIA');
1 rows affected

  CREATE TABLE PLAYER_CONTACT (
    PLAYER_ID VARCHAR(2) NOT NULL,
    CONTACT_ID VARCHAR(8) NOT NULL
);
  CREATE TABLE CONTACT_EMAIL (
    CONTACT_ID VARCHAR(8) NOT NULL,
    EMAIL_ID VARCHAR(50) NOT NULL
);	
CREATE TABLE PLAYER (
    PLAYER_ID VARCHAR(2) NOT NULL,
    PLAYER_NUMBER VARCHAR(3) NOT NULL,
    COUNTRY_ID VARCHAR(4) NOT NULL
);
CREATE TABLE PLAYERTYPE (
    PLAYER_ID VARCHAR(2) NOT NULL,
    PLAYER_TYPE VARCHAR(3) NOT NULL,
    FIRST_NAME VARCHAR(40) NOT NULL,	
    LAST_NAME VARCHAR(40) NOT NULL
);
INSERT INTO COUNTRY_TABLE    (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME) VALUES	  ('04', 'AUS', 'AUSTRALIA');
1 rows affected

INSERT INTO COUNTRY_TABLE    (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME) VALUES	  ('05', 'USA', 'UNITED STATES');
1 rows affected

INSERT INTO COUNTRY_TABLE    (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME) VALUES	  ('06', 'AFR', 'AFRICA');  
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES('39', '780');
1 rows affected

   INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES('54', '781');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES  ('65', '782');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('98', '783');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('99', '784');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('11', '785');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('12', '786');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('13', '787');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('14', '788');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('15', '789');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('16', '790');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('17', '791');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('18', '792');
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('19', '793');	
1 rows affected

  INSERT INTO PLAYER_CONTACT    (PLAYER_ID, CONTACT_ID)VALUES    ('31', '794');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES  ('780', 'dfgh@gmail.com');
1 rows affected

INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES  ('780', 'DFGH@GMAIL.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES   ('781', 'tygh@yahoo.com'); 
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES   ('782', 'fgvhb@yandex.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('783', 'byvg@google.com');
1 rows affected

INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('783', 'BYVG@GOOGLE.COM');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('784', 'poi@microsoft.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('785', 'weg@yahoo.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('786', 'gj@yahoo.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('787', 'tew@google.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('788', 'sdw@yahoo.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('789', 'fghwye@microsoft.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('790', 'kjgj@yahoo.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('791', 'ghj@rafs.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('792', 'poiu@yahoo.com');
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('793', 'ghjkjh@sdf.com');	
1 rows affected

  INSERT INTO CONTACT_EMAIL    (CONTACT_ID, EMAIL_ID)VALUES     ('794', 'sdidu@yahoo.com');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES    ('39', '480', '04'); 
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES   ('54', '481', '04');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES   ('65', '482', '04');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('98', '483', '04');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('99', '484', '01');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('11', '485', '01');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('12', '486', '01');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('13', '487', '01');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('14', '488', '01');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('15', '489', '02');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('16', '490', '02');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('17', '491', '02');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('18', '492', '02');
1 rows affected

INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('19', '493', '04');
1 rows affected

	
INSERT INTO PLAYER    (PLAYER_ID, PLAYER_NUMBER, COUNTRY_ID) VALUES     ('31', '494', '06');
	
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES    ('39', 'NEW', 'MARK', 'CLARKSON');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES   ('54', 'NEW', 'CATH', 'SPEARS'); 
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES   ('65', 'NEW', 'FESS', 'LOPEZ');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('98', 'EXE', 'FEXS', 'LOPEZ');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('99', 'NEW', 'MATT', 'LOPEZ');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('11', 'NEW', 'GIFF', 'SMITH');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('12', 'DEL', 'POIU', 'ROVA');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('13', 'DEL', 'JIFF', 'NOVA');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('14', 'NEW', 'TUBR', 'SMITH');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('15', 'DEL', 'COLOC', 'NOVA');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('16', 'DEL', 'MALR', 'MISH');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('17', 'NEW', 'MISH', 'ROVA');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('18', 'DEL', 'OLESY', 'NOVA');
1 rows affected

INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('19', 'NEW', 'EOVA', 'SMITH');
1 rows affected

	
INSERT INTO PLAYERTYPE    (PLAYER_ID, PLAYER_TYPE, FIRST_NAME, LAST_NAME) VALUES     ('31', 'DEL', 'EKTA', 'NOVA'); 	
	
1 rows affected

SELECT DISTINCT T.FIRST_NAME, 
                T.LAST_NAME, 
                LOWER(E.EMAIL_ID) AS EMAIL_ID, 
                CY.COUNTRY_ID 
FROM PLAYER P
INNER JOIN PLAYERTYPE T ON P.PLAYER_ID = T.PLAYER_ID
INNER JOIN PLAYER_CONTACT C ON T.PLAYER_ID = C.PLAYER_ID 
INNER JOIN CONTACT_EMAIL E ON E.CONTACT_ID = C.CONTACT_ID
INNER JOIN COUNTRY_TABLE CY ON P.COUNTRY_ID = CY.COUNTRY_ID
WHERE CY.COUNTRY_CODE='AUS' AND T.PLAYER_TYPE IN ('NEW', 'EXE')


FIRST_NAME
LAST_NAME
EMAIL_ID
COUNTRY_ID


MARK
CLARKSON
dfgh@gmail.com
04

CATH
SPEARS
tygh@yahoo.com
04

FESS
LOPEZ
fgvhb@yandex.com
04

FEXS
LOPEZ
byvg@google.com
04

EOVA
SMITH
ghjkjh@sdf.com
04
FIRST_NAME	LAST_NAME	EMAIL_ID	COUNTRY_ID
MARK	CLARKSON	dfgh@gmail.com	04
CATH	SPEARS	tygh@yahoo.com	04
FESS	LOPEZ	fgvhb@yandex.com	04
FEXS	LOPEZ	byvg@google.com	04
EOVA	SMITH	ghjkjh@sdf.com	04