FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

-- 1. Criação do modelo normalizado

CREATE TABLE Cliente (
    ClienteID INT PRIMARY KEY,
    Nome VARCHAR(100),
    Idade INT
);

CREATE TABLE Cartao (
    CartaoID INT PRIMARY KEY,
    ClienteID INT,
    Tipo_Cartao VARCHAR(50),
    FOREIGN KEY (ClienteID) REFERENCES Cliente(ClienteID)
);

CREATE TABLE Localizacao (
    LocalizacaoID INT PRIMARY KEY,
    Provincia VARCHAR(50),
    Distrito VARCHAR(50)
);

CREATE TABLE Terminal (
    TerminalID VARCHAR(50) PRIMARY KEY,
    Tipo_Terminal VARCHAR(20),
    LocalizacaoID INT,
    FOREIGN KEY (LocalizacaoID) REFERENCES Localizacao(LocalizacaoID)
);

CREATE TABLE Transacao (
    TransacaoID VARCHAR(50) PRIMARY KEY,
    Data DATE,
    Valor DECIMAL(10,2),
    CartaoID INT,
    TerminalID VARCHAR(50),
    FOREIGN KEY (CartaoID) REFERENCES Cartao(CartaoID),
    FOREIGN KEY (TerminalID) REFERENCES Terminal(TerminalID)
);

-- Inserção de dados de exemplo (opcional, para teste)
-- Inserção de dados de exemplo

-- Clientes
INSERT INTO Cliente (ClienteID, Nome, Idade) VALUES
(1, 'João Silva', 35),
(2, 'Maria Santos', 28),
(3, 'Pedro Nunes', 45),
(4, 'Ana Oliveira', 50),
(5, 'Edson Famanda', 29),
(6, 'Luísa Costa', 42),
(7, 'António Mendes', 55),
(8, 'Sofia Rodrigues', 30),
(9, 'Miguel Almeida', 38),
(10, 'Beatriz Sousa', 47);

-- Cartões
INSERT INTO Cartao (CartaoID, ClienteID, Tipo_Cartao) VALUES
(101, 1, 'Débito'),
(102, 2, 'Crédito'),
(103, 3, 'Débito'),
(104, 4, 'Crédito'),
(105, 5, 'Débito'),
(106, 6, 'Crédito'),
(107, 7, 'Débito'),
(108, 8, 'Crédito'),
(109, 9, 'Débito'),
(110, 10, 'Crédito');

-- Localizações
INSERT INTO Localizacao (LocalizacaoID, Provincia, Distrito) VALUES
(201, 'Gaza', 'Xai-Xai'),
(202, 'Maputo', 'Matola'),
(203, 'Sofala', 'Beira'),
(204, 'Nampula', 'Nampula'),
(205, 'Gaza', 'Chibuto'),
(206, 'Inhambane', 'Inhambane'),
(207, 'Tete', 'Tete'),
(208, 'Zambézia', 'Quelimane'),
(209, 'Cabo Delgado', 'Pemba'),
(210, 'Niassa', 'Lichinga');

-- Terminais
INSERT INTO Terminal (TerminalID, Tipo_Terminal, LocalizacaoID) VALUES
('T001', 'ATM', 201),
('T002', 'POS', 202),
('T003', 'ATM', 203),
('T004', 'POS', 204),
('T005', 'ATM', 205),
('T006', 'POS', 206),
('T007', 'ATM', 207),
('T008', 'POS', 208),
('T009', 'ATM', 209),
('T010', 'POS', 210);

-- Transações (50 transações)
INSERT INTO Transacao (TransacaoID, Data, Valor, CartaoID, TerminalID) VALUES
('TR001', '2024-06-25', 500.00, 101, 'T001'),
('TR002', '2024-06-25', 1200.00, 102, 'T002'),
('TR003', '2024-05-15', 800.00, 103, 'T003'),
('TR004', '2024-06-25', 1500.00, 104, 'T004'),
('TR005', '2024-03-01', 300.00, 105, 'T005'),
('TR006', '2024-06-25', 2000.00, 106, 'T006'),
('TR007', '2024-06-01', 100.00, 107, 'T007'),
('TR008', '2024-06-10', 950.00, 108, 'T008'),
('TR009', '2024-06-15', 1100.00, 109, 'T009'),
('TR010', '2024-06-20', 750.00, 110, 'T010'),
('TR011', '2024-06-25', 600.00, 101, 'T001'),
('TR012', '2024-05-30', 1800.00, 102, 'T002'),
('TR013', '2024-04-22', 400.00, 103, 'T003'),
('TR014', '2024-06-25', 2500.00, 104, 'T004'),
('TR015', '2024-02-15', 200.00, 105, 'T005'),
('TR016', '2024-06-25', 3000.00, 106, 'T006'),
('TR017', '2024-05-18', 150.00, 107, 'T007'),
('TR018', '2024-06-05', 1050.00, 108, 'T008'),
('TR019', '2024-06-12', 900.00, 109, 'T009'),
('TR020', '2024-06-19', 1250.00, 110, 'T010'),
('TR021', '2024-06-25', 700.00, 101, 'T001'),
('TR022', '2024-06-02', 1600.00, 102, 'T002'),
('TR023', '2024-05-10', 550.00, 103, 'T003'),
('TR024', '2024-06-25', 2200.00, 104, 'T004'),
('TR025', '2024-01-20', 350.00, 105, 'T005'),
('TR026', '2024-06-25', 2800.00, 106, 'T006'),
('TR027', '2024-04-30', 180.00, 107, 'T007'),
('TR028', '2024-06-08', 1150.00, 108, 'T008'),
('TR029', '2024-06-14', 980.00, 109, 'T009'),
('TR030', '2024-06-22', 1450.00, 110, 'T010'),
('TR031', '2024-06-25', 850.00, 101, 'T001'),
('TR032', '2024-05-28', 2100.00, 102, 'T002'),
('TR033', '2024-04-18', 480.00, 103, 'T003'),
('TR034', '2024-06-25', 3200.00, 104, 'T004'),
('TR035', '2024-02-10', 280.00, 105, 'T005'),
('TR036', '2024-06-25', 3500.00, 106, 'T006'),
('TR037', '2024-05-22', 220.00, 107, 'T007'),
('TR038', '2024-06-03', 1350.00, 108, 'T008'),
('TR039', '2024-06-11', 1020.00, 109, 'T009'),
('TR040', '2024-06-18', 1650.00, 110, 'T010'),
('TR041', '2024-06-25', 920.00, 101, 'T001'),
('TR042', '2024-06-01', 2400.00, 102, 'T002'),
('TR043', '2024-05-08', 630.00, 103, 'T003'),
('TR044', '2024-06-25', 2900.00, 104, 'T004'),
('TR045', '2024-01-15', 380.00, 105, 'T005'),
('TR046', '2024-06-25', 3800.00, 106, 'T006'),
('TR047', '2024-04-25', 250.00, 107, 'T007'),
('TR048', '2024-06-07', 1550.00, 108, 'T008'),
('TR049', '2024-06-13', 1080.00, 109, 'T009'),
('TR050', '2024-06-21', 1850.00, 110, 'T010');

-- 2. Query para clientes inativos da província de Gaza, com mais levantamentos
SELECT c.ClienteID, c.Nome, COUNT(*) as NumLevantamentos
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
JOIN Localizacao l ON te.LocalizacaoID = l.LocalizacaoID
WHERE l.Provincia = 'Gaza'
  AND te.Tipo_Terminal = 'ATM'
  AND t.Data < DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY c.ClienteID, c.Nome
ORDER BY NumLevantamentos DESC;

-- 3. Província do cliente menos rentável e com mais transações
SELECT l.Provincia
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
JOIN Localizacao l ON te.LocalizacaoID = l.LocalizacaoID
GROUP BY l.Provincia
ORDER BY SUM(CASE WHEN t.Valor > 1000 THEN 1 ELSE 0 END) ASC, COUNT(*) DESC
LIMIT 1;

-- 4. Província com maior número de clientes ativos e menos rentáveis
SELECT l.Provincia
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
JOIN Localizacao l ON te.LocalizacaoID = l.LocalizacaoID
WHERE t.Data >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY l.Provincia
ORDER BY COUNT(DISTINCT c.ClienteID) DESC, SUM(CASE WHEN t.Valor <= 1000 THEN 1 ELSE 0 END) DESC
LIMIT 1;

-- 5. Cliente ativo mais velho, com mais pagamentos no dia da independência
SELECT c.ClienteID, c.Nome, c.Idade, COUNT(*) as NumPagamentos
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
WHERE te.Tipo_Terminal = 'POS'
  AND DAY(t.Data) = 25 AND MONTH(t.Data) = 6
  AND t.Data >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY c.ClienteID, c.Nome, c.Idade
ORDER BY c.Idade DESC, NumPagamentos DESC
LIMIT 1;

ClienteID	Nome	NumLevantamentos
5	Edson Famanda	5

Provincia
Gaza

Provincia
Gaza

ClienteID	Nome	Idade	NumPagamentos
4	Ana Oliveira	50	5