-
Notifications
You must be signed in to change notification settings - Fork 0
/
2guiatur.sql
177 lines (118 loc) · 4.2 KB
/
2guiatur.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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
CREATE DATABASE IF NOT EXISTS guiatur
DEFAULT CHARSET = utf8
DEFAULT COLLATE = utf8_general_ci;
SHOW CREATE DATABASE guiatur;
USE guiatur;
CREATE TABLE IF NOT EXISTS pais (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50) NOT NULL DEFAULT '',
continente ENUM('Ásia', 'Europa', 'América', 'África',
'Oceania', 'Antártida') NOT NULL DEFAULT 'América',
codigo CHAR(3) NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS estado (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50) NOT NULL DEFAULT '',
sigla CHAR(2) NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS cidade (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50) NOT NULL DEFAULT '',
populacao INT(11) NOT NULL DEFAULT '0'
);
CREATE TABLE IF NOT EXISTS ponto_tur (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50) NOT NULL DEFAULT '',
tipo ENUM('Atrativo', 'Serviço', 'Equipamento',
'Infraestrutura', 'Instituição', 'Organização'),
publicado ENUM('Não', 'Sim') NOT NULL DEFAULT 'Não'
);
CREATE TABLE coordenada (
latitude FLOAT(10,6),
longitude FLOAT(10,6)
);
USE guiatur;
SHOW TABLES FROM guiatur;
DESCRIBE pais;
INSERT INTO pais (nome, continente, codigo)
VALUES
('Brasil', 'América', 'BRA'),
('Índia', 'Ásia', 'IDN'),
('China', 'Ásia', 'CHI'),
('Japão', 'Ásia', 'JPN');
SELECT * FROM pais;
describe estado;
INSERT INTO estado (nome, sigla)
VALUES('Maranhão', 'MA'),
('São Paulo', 'SP'),
('Santa Catarina', 'SC'),
('Rio de Janeiro', 'RJ');
SELECT * FROM estado;
INSERT INTO cidade (nome, populacao)
values('Sorocaba', 700000),
('Déli', 26000000),
('Xangai', 22000000),
('Tóquio', 38000000);
SELECT * FROM cidade;
INSERT INTO ponto_tur (nome, tipo)
VALUES('Quinzinho de Barros', 'Instituição'),
('Parque Estadual do Jalapão', 'Atrativo'),
('Torre Eiffel', 'Atrativo'),
('Fogo de Chão', 'Serviço');
Describe ponto_tur;
SELECT * FROM ponto_tur;
-- alterar para atrativo o primeiro ponto turístico
UPDATE ponto_tur SET tipo = 'Atrativo'
WHERE id = 1;
-- alterar o segundo país (Índia) para ter o cód. 'IND'
SELECT * FROM pais;
UPDATE pais SET codigo = 'IND'
WHERE id = 2;
-- deletar a primeira cidade
SELECT * FROM cidade;
DELETE FROM cidade WHERE id = 1;
USE guiatur;
/* determinados países terem mais de
um idioma, embora um seja determinado como o idioma oficial.
Nessa relação, temos a cardinalidade de 1 para N */
CREATE TABLE IF NOT EXISTS linguagemPais (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
codigoPais INT(11),
linguagem VARCHAR(30) NOT NULL DEFAULT '',
oficial ENUM('Sim', 'Não') NOT NULL DEFAULT'Não'
);
DESCRIBE pais;
/*Criar integridade referencial antre as tebelas linguagemPais e país*/
ALTER TABLE linguagempais ADD CONSTRAINT FK_linguagempais
FOREIGN KEY (codigoPais) REFERENCES pais(id);
DESCRIBE linguagempais;
/*Modificar o código do país para ser obrigatória a inclusão*/
ALTER TABLE linguagempais MODIFY codigoPais INT(11) NOT NULL;
SHOW TABLES FROM guiatur;
DESCRIBE pais;
SELECT * FROM pais;
/*Teste de exclusão - inserindo dados na tabela com chave estrangeira*/
INSERT INTO linguagempais VALUES (0,4,'japones','sim');
SELECT * FROM linguagempais;
DROP TABLE pais;
/*Excluir a restrição de chave estrangeira e tabela*/
ALTER TABLE linguagemPais DROP FOREIGN KEY FK_linguagemPais;
/*agora a tabela país pode ser excluída*/
DROP TABLE pais;
/*listar todas as constraints do banco*/
SELECT *
FROM information_schema.key_column_usage
WHERE constraint_schema='guiatur';
SHOW TABLES;
-- Alterar tabela de Elementos Turísticos, adicionando campos latitude e longitude.
DESCRIBE ponto_tur;
ALTER TABLE ponto_tur ADD coordenada POINT;
DROP TABLE coordenada;
-- Alterar a tabela “Países”, adicionando uma nota de 0 a 10 com o nível de interesse para o turista ('0','1','2','3','4','5','6','7','8','9','10')
ALTER TABLE pais ADD interesse ENUM('0','1','2','3','4','5','6','7','8','9','10');
-- Alterar tabela “Cidades”, incluindo uma lista com os três melhores restaurantes.
ALTER TABLE cidade ADD melhoresRest VARCHAR(300) DEFAULT '';
INSERT INTO ponto_tur (nome, coordenada)
VALUES ('Ponte da Amizade',POINT(1.123456,3.434343));
SELECT * FROM ponto_tur;
SELECT *, AsText(coordenada) FROM ponto_tur;