Comprehensive Database Administrator (DBA) Practical Interview Tasks

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Phone VARCHAR(15) UNIQUE
);

-- Create Loans table (1-to-Many with Customers)
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT,
    Amount DECIMAL(10,2) NOT NULL,
    IssueDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Create Payments table (1-to-Many with Loans)
CREATE TABLE Payments (
    PaymentID INT PRIMARY KEY AUTO_INCREMENT,
    LoanID INT,
    Amount DECIMAL(10,2) NOT NULL,
    PaymentDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (LoanID) REFERENCES Loans(LoanID)
);

-- Insert into Customers
INSERT INTO Customers (Name, Phone) VALUES 
('John Doe', '255712345678'),
('Jane Smith', '255765432100'),
('Alice Johnson', '255788877766');

-- Insert into Loans
INSERT INTO Loans (CustomerID, Amount, IssueDate) VALUES 
(1, 500000.00, '2023-01-15'),
(1, 200000.00, '2023-03-20'),
(2, 1000000.00, '2023-02-10');

-- Insert into Payments
INSERT INTO Payments (LoanID, Amount) VALUES 
(1, 100000.00),
(1, 150000.00),
(2, 50000.00);

-- Add a new column
ALTER TABLE Customers ADD COLUMN Email VARCHAR(100);

-- Rename a column
ALTER TABLE Loans RENAME COLUMN IssueDate TO LoanDate;

-- Drop a constraint (remove FK temporarily)
ALTER TABLE Payments DROP FOREIGN KEY payments_ibfk_1;

-- Delete a customer with no loans
DELETE FROM Customers WHERE CustomerID = 3;

-- Update a loan amount
UPDATE Loans SET Amount = 550000.00 WHERE LoanID = 1;

CREATE USER 'microfinance_admin'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Grant full access to the database
GRANT ALL PRIVILEGES ON microfinance_db.* TO 'microfinance_admin'@'localhost';

-- Grant read-only access to a specific table
GRANT SELECT ON microfinance_db.Customers TO 'reports_user'@'localhost';

mysqldump -u root -p microfinance_db > full_backup.sql

FLUSH LOGS;  -- Rotates binary logs for incremental backup

# Run every 2 minutes (for testing)
*/2 * * * * mysqldump -u root -p microfinance_db > /backups/microfinance_$(date +\%Y\%m\%d_\%H\%M\%S).sql

mysql -u root -p microfinance_db < full_backup.sql

CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    Amount DECIMAL(10,2) CHECK (Amount > 0),
    TransactionDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    Status ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending',
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- INNER JOIN (Customers with Loans)
SELECT c.Name, l.Amount 
FROM Customers c 
INNER JOIN Loans l ON c.CustomerID = l.CustomerID;

-- LEFT JOIN (All Customers, including those without loans)
SELECT c.Name, COUNT(l.LoanID) AS TotalLoans
FROM Customers c 
LEFT JOIN Loans l ON c.CustomerID = l.CustomerID 
GROUP BY c.Name;

-- Subquery (Customers who haven’t taken loans)
SELECT Name FROM Customers 
WHERE CustomerID NOT IN (SELECT CustomerID FROM Loans);

-- UNION ALL (Combine Customers & Loans data)
SELECT 'Customer' AS Type, Name AS Detail FROM Customers
UNION ALL
SELECT 'Loan' AS Type, CONCAT('Amount: ', Amount) FROM Loans;

-- CASE (Categorize loans)
SELECT LoanID, 
    CASE 
        WHEN Amount > 500000 THEN 'Large Loan' 
        ELSE 'Small Loan' 
    END AS LoanType 
FROM Loans;

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = microfinance_db

CHANGE MASTER TO 
MASTER_HOST='master_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
START SLAVE;

CREATE TRIGGER log_loan_changes
AFTER UPDATE ON Loans
FOR EACH ROW
INSERT INTO AuditLog (Action, LoanID, OldAmount, NewAmount)
VALUES ('Loan Updated', OLD.LoanID, OLD.Amount, NEW.Amount);

CREATE PROCEDURE CalculateInterest(IN loan_id INT)
BEGIN
    SELECT Amount * 0.1 AS Interest FROM Loans WHERE LoanID = loan_id;
END;

CREATE EVENT monthly_report
ON SCHEDULE EVERY 1 MONTH
DO
    CALL GenerateMonthlyReport();

CREATE VIEW CustomerLoanSummary AS
SELECT c.Name, SUM(l.Amount) AS TotalLoans
FROM Customers c 
LEFT JOIN Loans l ON c.CustomerID = l.CustomerID 
GROUP BY c.Name;

ALTER TABLE Payments 
PARTITION BY RANGE (YEAR(PaymentDate)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

[ndbd]
HostName=node1
[ndbd]
HostName=node2
[mysqld]
HostName=sql_node

const express = require('express');
const mysql = require('mysql');
const app = express();

const db = mysql.createConnection({ host: 'localhost', user: 'root', password: '', database: 'microfinance_db' });

app.get('/api/loans', (req, res) => {
    db.query('SELECT * FROM Loans', (err, results) => {
        if (err) throw err;
        res.json(results);
    });
});

app.listen(3000, () => console.log('API running on port 3000'));