Why is MySQL code not accepting the relation for my table on SQL fiddle?

When you add a reference to another table, that other table must exist. In this case the error can be solved by reordering the creation of tables.

For example:

CREATE TABLE COFFEE_SHOP(
    SHOP_ID INT AUTO_INCREMENT,
    SHOP_NAME VARCHAR(50),
    CITY VARCHAR(50),
    STATE CHAR(2),
    PRIMARY KEY(SHOP_ID)
);

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID INT,
    FIRST_NAME VARCHAR(30),
    LAST_NAME VARCHAR(30),
    HIRE_DATE DATE,
    JOB_TITLE VARCHAR(30),
    SHOP_ID INT,
    PRIMARY KEY(EMPLOYEE_ID),
    FOREIGN KEY (EMPLOYEE_ID) REFERENCES COFFEE_SHOP(SHOP_ID)
);

CREATE TABLE SUPPLIER (
    SUPPLIER_ID INT AUTO_INCREMENT,
    COMPANY_NAME VARCHAR(50),
    COUNTRY VARCHAR(30),
    SALES_CONTACT_NAME VARCHAR(60),
    EMAIL VARCHAR(50) NOT NULL,
    PRIMARY KEY(SUPPLIER_ID)
);

CREATE TABLE COFFEE (
    COFFEE_ID INT AUTO_INCREMENT,
    SHOP_ID INT,
    SUPPLIER_ID INT,
    COFFEE_NAME VARCHAR(30),
    PRICE_PER_POUND NUMERIC(5,2),
    PRIMARY KEY(COFFEE_ID),
    FOREIGN KEY (SHOP_ID) REFERENCES COFFEE_SHOP(SHOP_ID),
    FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID)
);

See running example at DB Fiddle.