Ultimate DBA Interview Practical Test: 15 Tasks to Master (SQL, Backups, Replication & More)

Database Administrator (DBA) Practical Interview Tasks

1. Database Creation & Relationships

Task:

• Create 3 related tables (e.g., Users, Orders, Products) with PK/FK relationships.

• Insert 10+ records per table.

• Modify tables:

  • Add/drop columns.

  • Rename tables/columns.

  • Remove PK/FK constraints.

• Write queries to:

  • Delete specific records.

  • Update data (e.g., change a user’s email).

Example:

-- Create tables with PK/FK  
CREATE TABLE Users (UserID INT PRIMARY KEY, Name VARCHAR(50));  
CREATE TABLE Orders (OrderID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES Users(UserID));  

2. User Management & Permissions

Task:

• Create a user and set a password.

• Grant specific privileges (e.g., SELECT on Orders table only).

Example:

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'Password123!';  
GRANT SELECT ON dbname.Orders TO 'admin'@'localhost';  

3. Backup & Recovery

Task:

• Perform backups:

  • Full, Incremental, Differential, Transaction Log, Snapshot.

  • Schedule automated backups (e.g., daily at 2 AM).

• Restore from a backup file.

Example (MySQL):

# Full backup  
mysqldump -u root -p --all-databases > full_backup.sql  

# Restore  
mysql -u root -p < full_backup.sql  

4. Advanced Constraints & Data Types

Task:
Create a database using:

• Constraints: PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL, UNIQUE.

• Data Types: INT, VARCHAR, CHAR, DATE, TIMESTAMP, BOOLEAN, ENUM.

Example:

CREATE TABLE Employees (  
  EmployeeID INT PRIMARY KEY,  
  Name VARCHAR(50) NOT NULL,  
  Gender ENUM('Male', 'Female', 'Other'),  
  JoinDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);  

5. Data Retrieval (Complex Queries)

Task: Write queries for:

• Joins (INNER, LEFT, RIGHT, FULL OUTER).

• Subqueries (e.g., find users with no orders).

• UNION ALL (combine datasets).

Example:

-- Inner Join  
SELECT Users.Name, Orders.OrderID  
FROM Users  
INNER JOIN Orders ON Users.UserID = Orders.UserID;  

6. Database Replication (Master-Slave)

Task:

• Configure Master-Slave replication (e.g., MySQL).

• Test data sync between servers.

Example (MySQL Config):

# Master my.cnf  
[mysqld]  
server-id = 1  
log-bin = mysql-bin  

7. Triggers

Task: Create triggers for:

• BEFORE/AFTER INSERT/UPDATE/DELETE.

• Example: Log deleted records to an AuditLog table.

Example:

CREATE TRIGGER log_deletion  
AFTER DELETE ON Orders  
FOR EACH ROW  
INSERT INTO AuditLog VALUES ('Order deleted', OLD.OrderID);  

8. Stored Procedures & Events

Task:

• Write a stored procedure to calculate monthly sales.

• Schedule an event to purge old logs weekly.

Example:

CREATE PROCEDURE GetMonthlySales(IN month INT)  
BEGIN  
  SELECT SUM(Amount) FROM Orders WHERE MONTH(OrderDate) = month;  
END;  

9. Database Normalization (1NF–4NF)

Task:

• Design a database and normalize it to 4NF.

• Explain how each NF reduces redundancy.

Example:

• 1NF: Eliminate repeating groups (e.g., split Orders and OrderItems).

• 2NF: Remove partial dependencies (e.g., move ProductName to a Products table).

10. Entity-Relationship Diagrams (ERD)

Task:

• Draw an ERD for an e-commerce system (include Customers, Products, Orders).

• Label cardinality (1:1, 1:N, M:N).

Tool Suggestions:

• Lucidchart, Draw.io, MySQL Workbench.

11. Database Views

Task:

• Create a view joining 3+ tables (e.g., CustomerOrderSummary).

Example:

CREATE VIEW CustomerOrderSummary AS  
SELECT Users.Name, COUNT(Orders.OrderID) AS TotalOrders  
FROM Users  
LEFT JOIN Orders ON Users.UserID = Orders.UserID  
GROUP BY Users.Name;  

12. CASE Statements

Task:

• Use CASE to categorize data (e.g., "High/Low Priority" based on order amount).

Example:

SELECT OrderID,  
  CASE  
    WHEN Amount > 1000 THEN 'High Priority'  
    ELSE 'Standard'  
  END AS Priority  
FROM Orders;  

13. Data Partitioning

Task:

• Partition a table by DATE or REGION for performance.

Example (Range Partitioning):

CREATE TABLE Sales (  
  SaleID INT,  
  SaleDate DATE  
) PARTITION BY RANGE (YEAR(SaleDate)) (  
  PARTITION p2023 VALUES LESS THAN (2024),  
  PARTITION p2024 VALUES LESS THAN (2025)  
);  

14. Database Clustering

Task:

• Set up a MySQL Cluster or MongoDB Replica Set.

• Explain failover and load balancing.

15. API Integration

Task:

• Build a REST API endpoint (e.g., Node.js + Express) to fetch data from your DB.

Example (Node.js):

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

Key Skills Tested:

✅ SQL Mastery (DDL, DML, TCL).
✅ Backup Strategies (Full/Incremental/Log).
✅ Security (User privileges, encryption).
✅ Performance (Indexing, partitioning).
✅ High Availability (Replication, clustering).
✅ API/Integration (REST, JSON).