Tables and inserts:
CREATE TABLE CLIENTE(
IDCLIENTE INT PRIMARY KEY AUTO_INCREMENT,
NOME VARCHAR(30) NOT NULL,
SEXO ENUM('M','F') NOT NULL,
ID_CARRO INT UNIQUE
);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL','M',1);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL2','M',2);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL3','M',3);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL4','M',4);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL5','M',5);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL6','M',6);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL7','M',7);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL8','M',8);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL9','M',9);
INSERT INTO CLIENTE VALUES (NULL,'GABRIEL10','M',10);
CREATE TABLE TELEFONE(
IDTELEFONE INT PRIMARY KEY AUTO_INCREMENT,
TIPO ENUM('RESIDENCIAL','CELULAR') NOT NULL,
NUMERO VARCHAR(15) NOT NULL,
ID_CLIENTE INT
);
CREATE TABLE MARCA(
IDMARCA INT PRIMARY KEY AUTO_INCREMENT,
MARCA VARCHAR(30) NOT NULL UNIQUE
);
INSERT INTO MARCA VALUES (NULL,'FIAT'); -- 1
INSERT INTO MARCA VALUES (NULL,'VOLKSWAGEM'); -- 2
INSERT INTO MARCA VALUES (NULL,'TOYOTA'); -- 3
INSERT INTO MARCA VALUES (NULL,'RENAULT'); -- 4
INSERT INTO MARCA VALUES (NULL,'LAMBORGHINI'); -- 5
CREATE TABLE CARRO(
IDCARRO INT PRIMARY KEY AUTO_INCREMENT,
MODELO VARCHAR(30) NOT NULL,
PLACA VARCHAR(30) NOT NULL UNIQUE,
ID_MARCA INT
);
INSERT INTO CARRO VALUES (NULL,'PALIO','CSW-6202',1);
INSERT INTO CARRO VALUES (NULL,'PALIO FIRE','AAA-0000',1);
INSERT INTO CARRO VALUES (NULL,'ONIX','BBB-1111',2);
INSERT INTO CARRO VALUES (NULL,'ONIX FIRE','CCC-2222',2);
INSERT INTO CARRO VALUES (NULL,'SPIN','DDD-4444',3);
INSERT INTO CARRO VALUES (NULL,'SPIN FIRE','EEE-5555',3);
INSERT INTO CARRO VALUES (NULL,'SANDERO','FFF-6666',1);
INSERT INTO CARRO VALUES (NULL,'SANDERO FIRE','GGG-7777',1);
INSERT INTO CARRO VALUES (NULL,'GALLIARDO','HHH-8888',1);
INSERT INTO CARRO VALUES (NULL,'GALLIARDO FIRE','III-9999',1);
CREATE TABLE COR(
IDCOR INT PRIMARY KEY AUTO_INCREMENT,
COR VARCHAR(30) NOT NULL UNIQUE
);
INSERT INTO COR VALUES (NULL,'CINZA'); -- 1
INSERT INTO COR VALUES (NULL,'VERMELHO'); -- 2
CREATE TABLE CARRO_COR(
ID_CARRO INT,
ID_COR INT,
PRIMARY KEY (ID_CARRO,ID_COR)
);
INSERT INTO CARRO_COR VALUES (1,1);
INSERT INTO CARRO_COR VALUES (1,2);
INSERT INTO CARRO_COR VALUES (2,1);
INSERT INTO CARRO_COR VALUES (2,2);
INSERT INTO CARRO_COR VALUES (3,1);
INSERT INTO CARRO_COR VALUES (3,2);
INSERT INTO CARRO_COR VALUES (4,1);
INSERT INTO CARRO_COR VALUES (4,2);
INSERT INTO CARRO_COR VALUES (5,1);
INSERT INTO CARRO_COR VALUES (5,2);
FOREIGN KEY AND CONSTRAINTS
-- FOREIGN KEY E CONSTRAINTS
ALTER TABLE TELEFONE
ADD CONSTRAINT FK_TELEFONE_CLIENTE
FOREIGN KEY (ID_CLIENTE)
REFERENCES CLIENTE(IDCLIENTE);
ALTER TABLE CLIENTE
ADD CONSTRAINT FK_CLIENTE_CARRO
FOREIGN KEY (ID_CARRO)
REFERENCES CARRO(IDCARRO);
ALTER TABLE CARRO
ADD CONSTRAINT FK_CARRO_MARCA
FOREIGN KEY (ID_MARCA)
REFERENCES MARCA(IDMARCA);
ALTER TABLE CARRO_COR
ADD CONSTRAINT FK_COR
FOREIGN KEY (ID_COR)
REFERENCES COR(IDCOR);
ALTER TABLE CARRO_COR
ADD CONSTRAINT FK_CARRO
FOREIGN KEY (ID_CARRO)
REFERENCES CARRO(IDCARRO);
* QUERY: *
SELECT C.NOME, CAR.MODELO, CO.COR,
FROM CLIENTE C
INNER JOIN CARRO CAR
ON CAR.IDCARRO = C.ID_CARRO;
INNER JOIN CARRO_COR CC
ON CC.ID_CARRO = CAR.IDCARRO
INNER JOIN COR CO
ON CO.IDCOR = CC.ID_COR;
I'm having syntax error, can you help me with this?