-- create a table
CREATE TABLE location (
    location_id INTEGER PRIMARY KEY,
    city TEXT,
    state_province TEXT,
    postal_code TEXT(5),
    country TEXT);

CREATE TABLE supplier (
    supplier_id INTEGER PRIMARY KEY,
    city TEXT,
    state_province TEXT,
    postal_code TEXT(5),
    country TEXT,
    name text);

-- insert some values
INSERT INTO location VALUES (1, 'Paris', 'FRANCE',75000,'FRANCE'),
    (2, 'Bagnolet', 'FRANCE',93170,'FRANCE') ;
INSERT INTO supplier VALUES (1, 'Torcy', 'FRANCE',77200,'FRANCE','SUPPLIER_1'),
    (2, 'Bagnolet', 'FRANCE',93170,'FRANCE','SUPPLIER_2'),(3, 'Les Lilas', 'FRANCE',93260,'FRANCE','SUPPLIER_3');

--SOLUTION
select postal_code, CASE 
    WHEN postal_code in (SELECT s.postal_code FROM location l, supplier s where l.postal_code=s.postal_code) THEN 'both'
    WHEN postal_code in (SELECT postal_code FROM location) THEN 'location' 
    WHEN postal_code in (SELECT postal_code FROM supplier) THEN 'supplier' 
    END as 'SOURCE'
    FROM (SELECT postal_code FROM location union SELECT postal_code FROM supplier) 
    order by postal_code ASC; 

/*Extract all the unique used in the or table along with a
postal_code location supplier source
column indicating its source.
If the postal code appears only in the location source
table, then must be ' location '. If it appears
only in supplier source source
, then must be ' supplier '. If it appears on the two tables, then must
be ' both '.
Expected columns: postal_code source in that order.
Requirements , , Sort the rows by numerical
postal_code
ascending. You are guaranteed that all the postal code in the data can be converted to
integers. The expected column postal_code
must be returned as VARCHAR, even though its sorting
must be numerical. Remove duplicates. For example, if many suppliers have the same postal code, you
must return it only once. Expected data:
Here are the 5 first rows that should be returned by your query (mind the numeric ordering).*/