Minibank-4.sql

-- 0️⃣ Temporarily disable foreign key checks
-- Purpose: Avoid foreign key errors when dropping tables
SET FOREIGN_KEY_CHECKS = 0;


-- 1️⃣ Drop old tables (if they exist)
DROP TABLE IF EXISTS transactions; -- The transactions table must be dropped first because it references users
DROP TABLE IF EXISTS users;        -- Drop the users table


-- 2️⃣ Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;


-- 3️⃣ Create users table
CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY,         -- Unique ID for each user
    username VARCHAR(50) UNIQUE NOT NULL,      -- Username, must be unique
    password VARCHAR(100) NOT NULL,            -- Password (store hashed in real applications)
    balance DECIMAL(10,2) DEFAULT 0.00,       -- Balance, default value 0.00
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Creation timestamp
) ENGINE=InnoDB; -- InnoDB supports foreign keys


-- 4️⃣ Create transactions table
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,         -- Unique ID for each transaction
    user_id INT NOT NULL,                      -- User who performs the transaction
    type ENUM('deposit','withdraw','transfer') NOT NULL, -- Transaction type
    amount DECIMAL(10,2) NOT NULL,            -- Transaction amount
    details VARCHAR(255),                      -- Description of the transaction
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Transaction timestamp
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) -- Reference to users table
) ENGINE=InnoDB;


-- 5️⃣ Insert example users
INSERT INTO users (username, password, balance)
VALUES
('Ali', '1234', 500.00),
('Ayse', 'abcd', 1000.00),
('Mehmet', 'pass', 750.00);


-- 6️⃣ Insert example transactions
INSERT INTO transactions (user_id, type, amount, details)
VALUES
(1, 'deposit', 200.00, 'Salary deposited'),     -- Ali received money
(1, 'withdraw', 50.00, 'Grocery shopping'),     -- Ali withdrew money
(2, 'transfer', 100.00, 'Sent to Ali');         -- Ayse transfers money (Note: recipient info missing)


-- 7️⃣ Display all users
SELECT * FROM users;


-- 8️⃣ Display balance of a single user (by name)
SELECT username, balance FROM users WHERE username='Ali';


-- 9️⃣ List all transactions with usernames (using JOIN)
SELECT u.username, t.type, t.amount, t.details, t.created_at
FROM transactions t
JOIN users u ON u.id = t.user_id
ORDER BY t.created_at DESC;