SQL Creates

-- INE 5423 - Banco de Dados I
-- Nome: Rafael Schmidt Melchert
-- Matricula: 07232038
-- Tarefa 1

CREATE TABLE Clientes ( cpf numeric(11) UNIQUE NOT NULL, nome varchar(40) NOT NULL, origem varchar(30) NOT NULL, fone numeric(8),

PRIMARY KEY(cpf)
);

CREATE TABLE Hospedes ( cpf numeric(11), motivo varchar(16) NOT NULL check (motivo IN ('turismo', 'trabalho', 'estudo', 'visita familiar') ), placaVeiculo varchar(7), nroAcomp int NOT NULL, dataEnt date NOT NULL, dataSai date, quarto int,

PRIMARY KEY(cpf), FOREIGN KEY(cpf) REFERENCES Clientes, FOREIGN KEY(quarto) REFERENCES Quartos
);

CREATE TABLE Quartos ( numero int NOT NULL, frigobar varchar(1) check (frigobar IN ('S', 'N') ), tipo int,

PRIMARY KEY(numero), FOREIGN KEY(tipo) REFERENCES TiposQuartos
);

CREATE TABLE TiposQuartos ( codigo int, descricao varchar(16) check(descricao IN ('quartoSimples', 'quartoDuplo', 'quartoTriplo', 'aptoSimples', 'aptoDuplo', 'aptoTriplo') ), diaria int,

PRIMARY KEY(codigo)
);

CREATE TABLE Reservas ( cliente numeric(11), quarto int, dataEnt date, dataSai date,

PRIMARY KEY(cliente), FOREIGN KEY(cliente) REFERENCES Clientes, FOREIGN KEY(quarto) REFERENCES Quartos
);

CREATE TABLE Cardapios ( codigo int, descricao varchar(40) NOT NULL, valor int check(valor > 0),

PRIMARY KEY(codigo)
);

CREATE TABLE Consumo ( codigo int, hospede numeric(11), itemCardapio int, data date NOT NULL, qtde integer check (qtde > 0),

PRIMARY KEY(codigo), FOREIGN KEY(hospede) REFERENCES Hospedes, FOREIGN KEY(itemCardapio) REFERENCES Cardapios
);

--Tarefa 2

SELECT c.nome, c.cpf FROM Clientes c WHERE c.cpf IN (SELECT h.cpf FROM Hospedes h WHERE h.dataEnt = CURRENT_DATE);

SELECT hca.nome, h.cpf FROM Hospedes h JOIN (SELECT c.hospede, c.data FROM Consumo c) AS hc ON h.cpf = hc.hospede AND h.dataEnt = hc.data JOIN (SELECT a.nome, a.cpf FROM Clientes a)