-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathTRAB_BDII_MODELO_FISICO_SQL.txt
162 lines (130 loc) · 4.98 KB
/
TRAB_BDII_MODELO_FISICO_SQL.txt
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
156
157
158
159
160
161
162
/*
* TRABALHO DE BANCO DE DADOS II
* MATEUS HOLZSCHUH @ 5 INFO
*/
/*
* CREATE TABLES
*/
CREATE TABLE BANCO (
BANCO_COD BIGINT PRIMARY KEY,
BANCO_NOME VARCHAR(100) NOT NULL,
BANCO_ENDERECO VARCHAR(150) NOT NULL
);
CREATE TABLE AGENCIA_BANC (
AGENCIA_COD BIGINT PRIMARY KEY,
AGENCIA_ENDERECO VARCHAR(150) NOT NULL,
BANCO_COD BIGINT NOT NULL,
FOREIGN KEY(BANCO_COD) REFERENCES BANCO
);
CREATE TABLE DEPARTAMENTO (
DEPARTAMENTO_COD BIGINT PRIMARY KEY,
DEPARTAMENTO_NOME VARCHAR(100) NOT NULL,
AGENCIA_COD BIGINT NOT NULL,
FOREIGN KEY(AGENCIA_COD) REFERENCES AGENCIA_BANC
);
CREATE TABLE CONTA (
CONTA_COD BIGINT PRIMARY KEY,
CONTA_TIPO CHAR(1) NOT NULL,
CONTA_SALDO NUMERIC(15, 3) NOT NULL,
CONTA_CREDITO NUMERIC(15, 3) NOT NULL,
CONTA_DATAABERT DATE NOT NULL DEFAULT CURRENT_DATE,
CONTA_DATAFECH DATE,
CONTA_ESTADO CHAR(1) NOT NULL,
CONTA_SENHA VARCHAR(256) NOT NULL,
AGENCIA_COD BIGINT NOT NULL,
FOREIGN KEY(AGENCIA_COD) REFERENCES AGENCIA_BANC
);
CREATE TABLE PESSOA (
PESSOA_COD BIGINT PRIMARY KEY,
PESSOA_CNPJ CHAR(14) NOT NULL DEFAULT '00000000000000',
PESSOA_CPF CHAR(11) NOT NULL DEFAULT '00000000000',
PESSOA_NOME VARCHAR(100) NOT NULL,
PESSOA_SOBRENOME VARCHAR(100) NOT NULL,
PESSOA_CIDADE VARCHAR(100) NOT NULL,
PESSOA_RG VARCHAR(20) NOT NULL,
PESSOA_INSCEST CHAR(12) NOT NULL DEFAULT '000000000000',
PESSOA_UF CHAR(2) NOT NULL,
PESSOA_ENDERECO VARCHAR(150) NOT NULL,
DEPARTAMENTO_COD BIGINT,
FOREIGN KEY(DEPARTAMENTO_COD) REFERENCES DEPARTAMENTO
);
CREATE TABLE CLIENTE_CONTA (
CONTA_COD BIGINT NOT NULL,
PESSOA_COD BIGINT NOT NULL,
FOREIGN KEY(CONTA_COD) REFERENCES CONTA,
FOREIGN KEY(PESSOA_COD) REFERENCES PESSOA
);
/*
* INSERTS
*/
/* BANCO */
INSERT INTO BANCO (BANCO_COD, BANCO_NOME, BANCO_ENDERECO)
VALUES (0, 'Banco do Brasil S/A', 'Brasília - DF');
INSERT INTO BANCO (BANCO_COD, BANCO_NOME, BANCO_ENDERECO)
VALUES (1, 'Bradesco S/A', 'Osasco - SP');
/* AGENCIA */
INSERT INTO AGENCIA_BANC (AGENCIA_COD, AGENCIA_ENDERECO, BANCO_COD)
VALUES (00345, 'Av. General Sampaio, Bagé - RS', 0);
INSERT INTO AGENCIA_BANC (AGENCIA_COD, AGENCIA_ENDERECO, BANCO_COD)
VALUES (01547, 'Av. Borges de Medeiros, Porto Alegre - RS', 0);
/* DEPARTAMENTO */
INSERT INTO DEPARTAMENTO (DEPARTAMENTO_COD, DEPARTAMENTO_NOME, AGENCIA_COD)
VALUES (0, 'RH', 00345);
INSERT INTO DEPARTAMENTO (DEPARTAMENTO_COD, DEPARTAMENTO_NOME, AGENCIA_COD)
VALUES (1, 'LIMPEZA', 00345);
/* CONTA */
INSERT INTO CONTA (CONTA_COD, CONTA_TIPO, CONTA_SALDO, CONTA_CREDITO, CONTA_ESTADO, CONTA_SENHA, AGENCIA_COD)
VALUES (778456, 'C', 500.000, 600.000, 'A', 'e10adc3949ba59abbe56e057f20f883e', 01547);
INSERT INTO CONTA (CONTA_COD, CONTA_TIPO, CONTA_SALDO, CONTA_CREDITO, CONTA_ESTADO, CONTA_SENHA, AGENCIA_COD)
VALUES (446456, 'P', 1500.000, 0.000, 'A', 'fd3a6fdc31a95520826c2187404231b9', 01547);
/* PESSOA */
INSERT INTO PESSOA (PESSOA_COD, PESSOA_CPF, PESSOA_NOME, PESSOA_SOBRENOME, PESSOA_CIDADE, PESSOA_RG, PESSOA_UF, PESSOA_ENDERECO)
VALUES (100, '12312312345', 'PEDRO', 'ALBUQUERQUE', 'PORTO ALEGRE', '1245789632', 'RS', 'RUA PORTO ALEGRE, 265');
INSERT INTO PESSOA (PESSOA_COD, PESSOA_CPF, PESSOA_NOME, PESSOA_SOBRENOME, PESSOA_CIDADE, PESSOA_RG, PESSOA_UF, PESSOA_ENDERECO)
VALUES (101, '32145698766', 'RICARDO', 'ALBUQUERQUE', 'PORTO ALEGRE', '4566987745', 'RS', 'RUA PORTO ALEGRE, 267');
/* CLIENTE-CONTA */
INSERT INTO CLIENTE_CONTA(PESSOA_COD, CONTA_COD)
VALUES (100, 446456);
INSERT INTO CLIENTE_CONTA(PESSOA_COD, CONTA_COD)
VALUES (101, 778456);
/*
* SELECTS
*/
/* SELECTs TOSCOS */
SELECT * FROM BANCO;
SELECT * FROM AGENCIA_BANC;
SELECT * FROM DEPARTAMENTO;
SELECT * FROM CONTA;
SELECT * FROM PESSOA;
SELECT * FROM CLIENTE_CONTA;
/* SELECTs MELHORADOS */
/* BANCO */
SELECT * FROM BANCO;
/* AGENCIA_BANCARIA */
SELECT AGENCIA_COD, AGENCIA_ENDERECO, BANCO_NOME FROM AGENCIA_BANC NATURAL JOIN BANCO;
/* DEPARTAMENTO */
SELECT DEPARTAMENTO_COD, DEPARTAMENTO_NOME, BANCO_NOME, AGENCIA_ENDERECO, AGENCIA_COD FROM DEPARTAMENTO NATURAL JOIN (AGENCIA_BANC NATURAL JOIN BANCO);
/* CONTA */
SELECT CONTA_COD, CONTA_TIPO, CONTA_SALDO, CONTA_CREDITO, CONTA_DATAABERT, CONTA_DATAFECH, CONTA_ESTADO, BANCO_NOME, AGENCIA_ENDERECO, AGENCIA_COD FROM CONTA NATURAL JOIN (AGENCIA_BANC NATURAL JOIN BANCO);
/* PESSOA */
SELECT PESSOA_COD, PESSOA_CPF, PESSOA_NOME, PESSOA_SOBRENOME, PESSOA_CIDADE, PESSOA_UF, PESSOA_ENDERECO FROM PESSOA;
/* CLIENTE_CONTA */
SELECT PESSOA_NOME, PESSOA_SOBRENOME, PESSOA_CPF, C.CONTA_COD, C.CONTA_SALDO, BANCO_NOME, AGENCIA_ENDERECO FROM CLIENTE_CONTA CC INNER JOIN CONTA C ON CC.CONTA_COD = C.CONTA_COD INNER JOIN PESSOA P ON CC.PESSOA_COD = P.PESSOA_COD INNER JOIN AGENCIA_BANC A ON C.AGENCIA_COD = A.AGENCIA_COD INNER JOIN BANCO B ON A.BANCO_COD = B.BANCO_COD;
/*
* DELETES (ordem correta de exclusão)
*/
DELETE FROM CLIENTE_CONTA;
DELETE FROM CONTA;
DELETE FROM PESSOA;
DELETE FROM DEPARTAMENTO;
DELETE FROM AGENCIA_BANC;
DELETE FROM BANCO;
/*
* DROPS (ordem correta de exclusão)
*/
DROP TABLE CLIENTE_CONTA;
DROP TABLE CONTA;
DROP TABLE PESSOA;
DROP TABLE DEPARTAMENTO;
DROP TABLE AGENCIA_BANC;
DROP TABLE BANCO;