Skip to content

Instantly share code, notes, and snippets.

@barraponto
Created June 1, 2019 21:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save barraponto/ffeb6b6e39b41c0698abc57ac0f6be4d to your computer and use it in GitHub Desktop.
Save barraponto/ffeb6b6e39b41c0698abc57ac0f6be4d to your computer and use it in GitHub Desktop.
/* 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