/* EXERCICIO */ Crie um sistema para oficina

CREATE DATABASE PROJETO;

USE PROJETO;

CREATE TABLE CLIENTE(
    IDCLIENTE INT PRIMARY KEY AUTO_INCREMENT,
    NOME VARCHAR(30) NOT NULL,
    SEXO ENUM('M','F') NOT NULL,
    ID_CARRO INT UNIQUE
);

CREATE TABLE TELEFONE(
    IDTELEFONE INT PRIMARY KEY AUTO_INCREMENT,
    NUMERO VARCHAR(30) NOT NULL,
    TIPO ENUM('CEL','RES','COM') NOT NULL,
    ID_CARRO INT UNIQUE
);

CREATE TABLE MARCA(
    IDMARCA INT PRIMARY KEY AUTO_INCREMENT,
    MARCA VARCHAR(30) UNIQUE
);

CREATE TABLE CARRO(
    IDCARRO INT PRIMARY KEY AUTO_INCREMENT,
    MODELO VARCHAR(30) NOT NULL,
    PLACA VARCHAR(10) NOT NULL UNIQUE,
    ID_MARCAR INT UNIQUE
);

CREATE TABLE COR(
    IDCOR INT PRIMARY KEY AUTO_INCREMENT,
    COR VARCHAR(30) UNIQUE
);

CREATE TABLE CARRO_COR(
    ID_CARRO INT,
    ID_COR INT,
    PRIMARY KEY (ID_CARRO,ID_COR)
);
/* 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_MARCAR)
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);

INSERT INTO CARRO VALUES(NULL,'POLO 1.6','GVO-1015',NULL);
INSERT INTO CLIENTE VALUES(NULL,'DANIEL','M',1);
INSERT INTO TELEFONE VALUES(NULL,'3331-3734','CEL',1);
INSERT INTO MARCA VALUES(NULL,'VOLKSWAGEN');
INSERT INTO COR VALUES(NULL,'PRETO');
INSERT INTO CARRO_COR VALUES(1,1);
UPDATE CARRO
SET ID_MARCAR = 1
WHERE IDCARRO = 1;

SELECT C.MODELO, C.PLACA,
       M.MARCA, CR.COR AS "COR",
       CL.NOME, T.NUMERO,
       T.TIPO
FROM CARRO C
INNER JOIN CLIENTE CL
ON C.IDCARRO = CL.ID_CARRO
INNER JOIN TELEFONE T
ON CL.IDCLIENTE = T.ID_CARRO
INNER JOIN MARCA M
ON C.ID_MARCAR = M.IDMARCA
INNER JOIN CARRO_COR CC
ON C.IDCARRO = CC.ID_CARRO
INNER JOIN COR CR
ON CC.ID_COR = CR.IDCOR;