Query suppliers in “VITORIA” that provide “MOTOR” for “KOMBI”,

suppliers.sql

-- Create database structure
CREATE TABLE SUPPLIER (
    SUPPLIER_CODE VARCHAR(255),
    SUPPLIER_NAME VARCHAR(255),
    CITY VARCHAR(255)
);

CREATE TABLE PART (
    PART_CODE VARCHAR(255),
    NAME_PART VARCHAR(255),
    PRICE DECIMAL
);

CREATE TABLE CAR (
    CAR_CODE VARCHAR(255),
    NAME_CAR VARCHAR(255),
    TYPE VARCHAR(255)
);

CREATE TABLE SUPPLY (
    SUPPLIER_CODE VARCHAR(255),
    PART_CODE VARCHAR(255),
    CAR_CODE VARCHAR(255)
);

-- Insert data
INSERT INTO SUPPLIER (SUPPLIER_CODE, SUPPLIER_NAME, CITY) VALUES ('S1', 'Supplier A', 'VITORIA');
INSERT INTO SUPPLIER (SUPPLIER_CODE, SUPPLIER_NAME, CITY) VALUES ('S2', 'Supplier B', 'VITORIA');
INSERT INTO SUPPLIER (SUPPLIER_CODE, SUPPLIER_NAME, CITY) VALUES ('S3', 'Supplier C', 'OUTRA CIDADE');

INSERT INTO PART (PART_CODE, NAME_PART, PRICE) VALUES ('P1', 'MOTOR', 1000);
INSERT INTO PART (PART_CODE, NAME_PART, PRICE) VALUES ('P2', 'RODA', 500);
INSERT INTO PART (PART_CODE, NAME_PART, PRICE) VALUES ('P3', 'VIDRO', 300);

INSERT INTO CAR (CAR_CODE, NAME_CAR, TYPE) VALUES ('C1', 'KOMBI', 'VAN');
INSERT INTO CAR (CAR_CODE, NAME_CAR, TYPE) VALUES ('C2', 'FUSCA', 'SEDAN');

INSERT INTO SUPPLY (SUPPLIER_CODE, PART_CODE, CAR_CODE) VALUES ('S1', 'P1', 'C1');
INSERT INTO SUPPLY (SUPPLIER_CODE, PART_CODE, CAR_CODE) VALUES ('S2', 'P1', 'C1');
INSERT INTO SUPPLY (SUPPLIER_CODE, PART_CODE, CAR_CODE) VALUES ('S3', 'P2', 'C2');

-- Query the expected data
SELECT S.SUPPLIER_NAME, P.PRICE
FROM SUPPLIER S
JOIN SUPPLY SU ON S.SUPPLIER_CODE = SU.SUPPLIER_CODE
JOIN PART P ON SU.PART_CODE = P.PART_CODE
JOIN CAR C ON SU.CAR_CODE = C.CAR_CODE
WHERE S.CITY = 'VITORIA' AND P.NAME_PART = 'MOTOR' AND C.NAME_CAR = 'KOMBI';