Created
July 29, 2022 20:52
-
-
Save tiagodavi70/7a6e257086ac93046d76be25bae76673 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
CREATE DATABASE banco; | |
USE banco; | |
CREATE TABLE agencia( | |
numero VARCHAR(5) NOT NULL PRIMARY KEY | |
); | |
CREATE TABLE conta( | |
numero VARCHAR(7) NOT NULL, | |
numero_agencia VARCHAR(5), | |
FOREIGN KEY (numero_agencia) | |
REFERENCES agencia(numero), | |
PRIMARY KEY (numero, numero_agencia) | |
); | |
CREATE TABLE cliente( | |
cpf VARCHAR(11) NOT NULL PRIMARY KEY, | |
nome TEXT, | |
numero_conta VARCHAR(7) NOT NULL, | |
numero_agencia VARCHAR(5) NOT NULL, | |
FOREIGN KEY (numero_conta, numero_agencia) | |
REFERENCES conta(numero, numero_agencia) | |
); | |
INSERT INTO agencia(numero) VALUES ("12345"), ('45678'); | |
INSERT INTO conta(numero, numero_agencia) | |
VALUES ("1234567", '12345'), ("7654321", '12345'), ("1234567", '45678'); | |
INSERT INTO cliente(cpf, nome, numero_conta, numero_agencia) | |
VALUES ("11111111111", "Tiago", "1234567", '12345'), | |
("22222222222","Davi","7654321", '12345'), | |
("33333333333","Matheus", "1234567", '45678'); | |
## Execute para encontrar um erro | |
# INSERT INTO conta(numero, numero_agencia) VALUES ("1234567", '89089'); | |
# DROP TABLE cliente; | |
SELECT * from cliente; | |
DELETE FROM agencia WHERE agencia.numero = "12345"; | |
SELECT nome, c.cpf FROM cliente AS c; | |
SELECT a.numero AS agencia, c.numero AS conta FROM agencia AS a, conta AS c; | |
SELECT | |
a.numero AS agencia, c.numero AS conta | |
FROM | |
agencia AS a, conta AS c | |
WHERE | |
a.numero = c.numero_agencia; | |
SELECT * FROM cliente WHERE nome <> "Tiago"; | |
SELECT * FROM conta AS c JOIN agencia AS a ON a.numero = c.numero_agencia; | |
(SELECT * FROM cliente WHERE nome <> "Tiago") | |
UNION | |
(SELECT * FROM cliente WHERE nome = "Tiago"); | |
CREATE TABLE teste( | |
dataadmissao DATE | |
); | |
INSERT INTO teste(dataadmissao) VALUES ("2022-07-27"); | |
INSERT INTO teste(dataadmissao) VALUES ( str_to_date("27-08-2022", "%d-%m-%Y")); | |
SELECT * FROM teste; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment