-
Notifications
You must be signed in to change notification settings - Fork 0
/
Povoamento.sql
97 lines (71 loc) · 3.16 KB
/
Povoamento.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
USE ecomboios;
-- -----------------------------------------------------
-- Tabela CLIENTE
-- -----------------------------------------------------
INSERT INTO cliente(nome, email, nif, password)
VALUES ('Alberto Caeiro', '[email protected]', 111111111, 'heteronimo'),
('Ricardo Reis', '[email protected]', 222222222, 'souMedico'),
('Álvaro de Campos', '[email protected]', 333333333, 'EngenheiroSouEu'),
('Fernando Pessoa', '[email protected]', 102348201, 'oOriginal'),
('Pedro Moreira', '[email protected]', 238881920, 'qwerty123'),
('Pedro Ferreira', '[email protected]', 231772893, 'asdfg456'),
('Diogo Sobral', '[email protected]', 182992102, 'zxcvb789'),
('Henrique Pereira', '[email protected]', 231983210, 'qazwsx123');
-- SELECT * FROM cliente;
-- -----------------------------------------------------
-- Tabela ESTACAO
-- -----------------------------------------------------
INSERT INTO estacao(nome)
VALUES ('Braga'), ('Porto'), ('Lisboa');
-- SELECT * FROM estacao;
-- -----------------------------------------------------
-- Tabela COMBOIO
-- -----------------------------------------------------
INSERT INTO comboio
VALUES (), (), (), (), (), ();
-- SELECT * FROM comboio;
-- -----------------------------------------------------
-- Tabela LUGAR
-- -----------------------------------------------------
CALL adiciona_lugares(1);
CALL adiciona_lugares(2);
CALL adiciona_lugares(3);
CALL adiciona_lugares(4);
CALL adiciona_lugares(5);
CALL adiciona_lugares(6);
-- SELECT * FROM lugar;
-- -----------------------------------------------------
-- Tabela VIAGEM
-- -----------------------------------------------------
CALL adiciona_workday('2018-12-01');
-- SELECT * FROM viagem;
/*
SELECT v.id_viagem, v.data_partida, v.data_chegada, v.duracao, eo.nome AS origem, ed.nome AS destino
FROM viagem AS v INNER JOIN estacao AS eo
ON v.origem = eo.id_estacao
INNER JOIN estacao AS ed
ON v.destino = ed.id_estacao; */
-- -----------------------------------------------------
-- Tabela BILHETE
-- -----------------------------------------------------
INSERT INTO bilhete(data_aquisicao, classe, numero, cliente, viagem)
VALUES ('2018-11-15 10:02:34', 'P', 1, 1, 1),
('2018-11-15 10:02:34', 'P', 20, 1, 28),
('2018-11-16 21:10:54', 'E', 3, 2, 1),
('2018-11-19 13:20:34', 'E', 20, 3, 1),
('2018-11-21 15:43:21', 'E', 79, 4, 1),
('2018-11-21 20:20:34', 'P', 19, 5, 1),
('2018-11-25 23:56:12', 'E', 31, 6, 1),
('2018-11-26 14:20:37', 'P', 22, 7, 3),
('2018-11-26 15:24:14', 'P', 1, 8, 3);
-- SELECT * FROM bilhete;
/*
SELECT c.nome, eo.nome AS origem, ed.nome AS destino, v.duracao, b.preco, b.classe, b.numero, b.id_bilhete
FROM cliente AS c INNER JOIN bilhete AS b
ON c.id_cliente = b.cliente
INNER JOIN viagem AS v
ON b.viagem = v.id_viagem
INNER JOIN estacao AS eo
ON v.origem = eo.id_estacao
INNER JOIN estacao AS ed
ON v.destino = ed.id_estacao; */