Created
June 1, 2019 21:19
-
-
Save barraponto/ffeb6b6e39b41c0698abc57ac0f6be4d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* exercicios 1 e 2 */ | |
CREATE TABLE clientes ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
nome VARCHAR(64) NOT NULL, | |
endereco VARCHAR(256), | |
cidade VARCHAR(128), | |
telefone VARCHAR(13) NOT NULL, | |
rg VARCHAR(13), | |
inscricao DATE NOT NULL, | |
fidelidade INT DEFAULT 0 | |
); | |
CREATE TABLE categorias ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
nome VARCHAR(32) NOT NULL, | |
diaria FLOAT UNSIGNED, | |
fidelidade INT DEFAULT 0 | |
); | |
CREATE TABLE seguros ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
tipo VARCHAR(32) NOT NULL, | |
valor FLOAT | |
); | |
CREATE TABLE danos ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
descricao VARCHAR(256) NOT NULL, | |
valor FLOAT | |
); | |
CREATE TABLE veiculos ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
placa VARCHAR(7) UNIQUE NOT NULL, | |
marca VARCHAR(32) NOT NULL, | |
categoria INT, | |
FOREIGN KEY fk_veiculos_categoria(categoria) REFERENCES categorias(id) | |
); | |
CREATE TABLE locacoes ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
cliente INT NOT NULL, | |
veiculo INT NOT NULL, | |
locacao DATE NOT NULL, | |
devolucao DATE, | |
valor FLOAT UNSIGNED, | |
cartao VARCHAR(16), | |
km_entrada INT UNSIGNED NOT NULL, | |
km_saida INT UNSIGNED, | |
FOREIGN KEY fk_locacoes_cliente(cliente) REFERENCES clientes(id), | |
FOREIGN KEY fk_locacoes_veiculo(veiculo) REFERENCES veiculos(id) | |
); | |
CREATE TABLE locacao_danos ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
dano INT NOT NULL, | |
locacao INT NOT NULL, | |
FOREIGN KEY fk_locacao_danos_dano(dano) REFERENCES danos(id), | |
FOREIGN KEY fk_locacao_danos_locacao(locacao) REFERENCES locacoes(id) | |
); | |
CREATE TABLE locacao_seguros ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
seguro INT NOT NULL, | |
locacao INT NOT NULL, | |
FOREIGN KEY fk_locacao_seguros_seguro(seguro) REFERENCES seguros(id), | |
FOREIGN KEY fk_locacao_seguros_locacao(locacao) REFERENCES locacoes(id) | |
); | |
/* exercicio 3 */ | |
INSERT INTO clientes (nome, endereco, cidade, telefone, rg, inscricao) | |
VALUES ('Pedro Santos', 'R. Reta, 123', 'Campinas', '5511988881111', '124564511', '2019-05-12'); | |
INSERT INTO clientes (nome, endereco, cidade, telefone, rg, inscricao) | |
VALUES ('Marcos Almeida', 'R. Sinuosa, 222', 'Campinas', '5511988881112', '124564522', '2019-05-14'); | |
INSERT INTO clientes (nome, endereco, cidade, telefone, rg, inscricao) | |
VALUES ('Carla Rodriguez', 'R. Reta, 333', 'Campinas', '5511988881113', '124564533', '2019-05-16'); | |
INSERT INTO clientes (nome, endereco, cidade, telefone, rg, inscricao) | |
VALUES ('Abel Luiz', 'R. Torta, 444', 'Campinas', '5511988881114', '124564544', '2019-05-18'); | |
INSERT INTO clientes (nome, endereco, cidade, telefone, rg, inscricao) | |
VALUES ('Gustavo Pimenta', 'R. Curva, 666', 'Campinas', '5511988881115', '124564555', '2019-05-20'); | |
INSERT INTO categorias (nome, diaria, fidelidade) | |
VALUES('ECONOMICO', 50, 80); | |
INSERT INTO categorias (nome, diaria, fidelidade) | |
VALUES('STANDARD', 70, 100); | |
INSERT INTO categorias (nome, diaria, fidelidade) | |
VALUES('PREMIUM', 100, 200); | |
INSERT INTO categorias (nome, diaria, fidelidade) | |
VALUES('VIP', 200, 500); | |
INSERT INTO categorias (nome, diaria, fidelidade) | |
VALUES('UTILITARIO', 80, 100); | |
INSERT INTO categorias (nome, diaria, fidelidade) | |
VALUES('OBSOLETO', 100, 0); | |
INSERT INTO seguros (tipo, valor) VALUES ('CDW', 100); | |
INSERT INTO seguros (tipo, valor) VALUES ('SLI', 100); | |
INSERT INTO seguros (tipo, valor) VALUES ('PAI', 100); | |
INSERT INTO seguros (tipo, valor) VALUES ('PEC', 50); | |
INSERT INTO seguros (tipo, valor) VALUES ('ALL', 350); | |
INSERT INTO danos (descricao, valor) VALUES ('1 Pneu furado', 100); | |
INSERT INTO danos (descricao, valor) VALUES ('2 Pneus furados', 200); | |
INSERT INTO danos (descricao, valor) VALUES ('4 Pneus furados', 400); | |
INSERT INTO danos (descricao, valor) VALUES ('1 retrovisor quebrado', 50); | |
INSERT INTO danos (descricao, valor) VALUES ('1 farol quebrado', 100); | |
INSERT INTO veiculos (placa, marca, categoria) | |
VALUES ('ABC1234', 'fiat', 1); | |
INSERT INTO veiculos (placa, marca, categoria) | |
VALUES ('ABC2234', 'ford', 1); | |
INSERT INTO veiculos (placa, marca, categoria) | |
VALUES ('ABC3234', 'volkswagen', 1); | |
INSERT INTO veiculos (placa, marca, categoria) | |
VALUES ('ABC4234', 'porsche', 4); | |
INSERT INTO veiculos (placa, marca, categoria) | |
VALUES ('ABC5234', 'volkswagen', 5); | |
INSERT INTO locacoes (cliente, veiculo, locacao, devolucao, valor, cartao, km_entrada, km_saida) | |
VALUES(1, 2, '2019-05-27', '2019-05-30', 250, '1324567812345678', 1000, NULL); | |
INSERT INTO locacoes (cliente, veiculo, locacao, devolucao, valor, cartao, km_entrada, km_saida) | |
VALUES(2, 2, '2019-05-28', NULL, 300, '1234567812345678', 1100, NULL); | |
INSERT INTO locacoes (cliente, veiculo, locacao, devolucao, valor, cartao, km_entrada, km_saida) | |
VALUES(3, 2, '2019-05-28', NULL, 350, '1324568712345678', 1200, NULL); | |
INSERT INTO locacoes (cliente, veiculo, locacao, devolucao, valor, cartao, km_entrada, km_saida) | |
VALUES(4, 4, '2019-05-28', NULL, 420, '1324567234485678', 1300, NULL); | |
INSERT INTO locacoes (cliente, veiculo, locacao, devolucao, valor, cartao, km_entrada, km_saida) | |
VALUES(5, 5, '2018-05-28', NULL, 150, '1324567812675678', 1400, NULL); | |
INSERT INTO locacao_danos (locacao, dano) VALUES (1, 1); | |
INSERT INTO locacao_danos (locacao, dano) VALUES (2, 2); | |
INSERT INTO locacao_danos (locacao, dano) VALUES (3, 3); | |
INSERT INTO locacao_danos (locacao, dano) VALUES (4, 4); | |
INSERT INTO locacao_danos (locacao, dano) VALUES (5, 5); | |
INSERT INTO locacao_seguros (locacao, seguro) VALUES (1, 1); | |
INSERT INTO locacao_seguros (locacao, seguro) VALUES (2, 2); | |
INSERT INTO locacao_seguros (locacao, seguro) VALUES (3, 3); | |
INSERT INTO locacao_seguros (locacao, seguro) VALUES (4, 4); | |
INSERT INTO locacao_seguros (locacao, seguro) VALUES (5, 5); | |
/* exercicio 4 */ | |
ALTER TABLE clientes ADD COLUMN inclusao DATE; | |
/* exercicio 5 */ | |
SELECT clientes.nome, veiculos.placa, veiculos.marca, categorias.nome AS categoria | |
FROM locacoes | |
INNER JOIN clientes ON locacoes.cliente = clientes.id | |
INNER JOIN veiculos ON locacoes.veiculo = veiculos.id | |
INNER JOIN categorias ON veiculos.categoria; | |
/* exercicio 6 */ | |
SELECT marca, categorias.nome AS categoria FROM veiculos | |
INNER JOIN categorias ON veiculos.categoria = categorias.id; | |
/* exercicio 7 */ | |
SELECT * FROM veiculos | |
WHERE categoria = (SELECT categoria FROM veiculos WHERE id = 2); | |
/* exercicio 8 */ | |
UPDATE veiculos | |
SET categoria = (SELECT id FROM categorias WHERE nome = 'OBSOLETO') | |
WHERE id NOT IN (SELECT veiculo FROM locacoes); | |
/* exercicio 9 */ | |
SELECT cliente, categorias.diaria, | |
DATEDIFF(devolucao, locacoes.locacao) AS quantidade, | |
categorias.diaria * DATEDIFF(devolucao, locacoes.locacao) AS diarias, | |
SUM(danos.valor) AS danos, | |
categorias.diaria * DATEDIFF(devolucao, locacoes.locacao) + SUM(danos.valor) as TOTAL | |
FROM locacoes | |
INNER JOIN veiculos ON locacoes.veiculo = veiculos.id | |
INNER JOIN categorias ON veiculos.categoria = categorias.id | |
INNER JOIN locacao_danos ON locacao_danos.locacao = locacoes.id | |
INNER JOIN danos ON locacao_danos.dano = danos.id | |
WHERE cliente = 1; | |
/* exercicio 10 */ | |
DELETE FROM locacoes where DATEDIFF(CURDATE(), locacao) > 365; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment