MYSQL Database Administrator (DBA) Practical Interview Tasks

This comprehensive guide covers beginner to advanced SQL queries for Business operations, including solutions, explanations, and additional practice questions like in TRA

Interview Chamber Interview Chamber
Jun 25, 2025 - 11:25 Updated: Jun 25, 2025 - 12:38
1
MYSQL Database Administrator (DBA) Practical Interview Tasks

MySQL Practice: Retail Business Operations - Complete Question Bank

Beginner Level Questions

Basic Data Retrieval

  1. List all customers with their contact information

  2. Show all products available in the 'Electronics' category

  3. Display all orders placed in January 2024

  4. Find customers who registered more than 1 year ago

  5. Count the number of products in each category

Simple Filtering

  1. Find all orders with status 'Pending'

  2. Show products with less than 10 items in stock

  3. List customers from New York or Los Angeles

  4. Find orders worth more than $500

  5. Display products priced between $50 and $100

Intermediate Level Questions

Joins and Relationships

  1. Show all orders with customer names and order dates

  2. Find products that have never been ordered

  3. List customers who haven't placed any orders

  4. Show order details with product names and quantities

  5. Find the most popular product categories

Aggregation and Grouping

  1. Calculate total sales by month

  2. Find the average order value by customer

  3. Show the top 5 best-selling products

  4. Calculate inventory turnover by product category

  5. Find customers who placed more than 3 orders

Advanced Level Questions

Complex Analysis

  1. Calculate customer lifetime value (total spend)

  2. Find products frequently bought together

  3. Analyze sales growth month-over-month

  4. Identify seasonal trends in product sales

  5. Calculate average days between customer orders

Data Modification

  1. Update prices for all products in a category

  2. Apply a 10% discount to loyal customers

  3. Archive orders older than 2 years

  4. Merge duplicate customer records

  5. Restock products with inventory below threshold

Expert Level Challenges

Business Intelligence

  1. Predict inventory needs based on sales trends

  2. Calculate customer retention rates

  3. Identify potential VIP customers

  4. Analyze the impact of marketing campaigns

  5. Optimize product pricing based on demand

Database Management

  1. Create a view for daily sales reports

  2. Design a stored procedure for monthly reports

  3. Implement database triggers for inventory updates

  4. Set up table partitioning for order history

  5. Create an API endpoint for order tracking

Scenario-Based Questions

Customer Analysis

  1. Find customers at risk of churning (no orders in 6 months)

  2. Identify customers likely to respond to upsell offers

  3. Segment customers by purchase frequency and value

  4. Calculate referral value from social customers

  5. Analyze customer acquisition channels

Inventory Management

  1. Identify slow-moving inventory

  2. Calculate optimal reorder points

  3. Analyze product return rates

  4. Forecast seasonal inventory requirements

  5. Identify products with shrinking profit margins

Specialized Queries

Time-Based Analysis

  1. Compare weekend vs weekday sales

  2. Analyze hourly sales patterns

  3. Calculate same-store sales growth

  4. Identify peak shopping periods

  5. Measure average order fulfillment time

Financial Analysis

  1. Calculate gross margin by product

  2. Analyze payment method preferences

  3. Identify outstanding customer balances

  4. Calculate days sales outstanding (DSO)

  5. Project quarterly revenue

Bonus: Real-World Case Studies

  1. Promotion Analysis: Measure the impact of a 20% holiday discount

  2. New Product Launch: Track adoption of a recently introduced product

  3. Store Performance: Compare sales across different locations

  4. Customer Service: Identify products with high return rates

  5. Supply Chain: Analyze vendor delivery reliability

Table Definitions

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    registration_date DATE
);

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order items table
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Payments table
CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    amount DECIMAL(10,2),
    payment_date DATE,
    payment_method VARCHAR(30),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Beginner Level Questions

1. List All Customers

SELECT * FROM customers;

2. Customers Registered in 2023

SELECT * FROM customers 
WHERE YEAR(registration_date) = 2023;

3. Count Customers by Registration Year

SELECT 
    YEAR(registration_date) AS year,
    COUNT(*) AS customer_count
FROM customers
GROUP BY YEAR(registration_date);

Intermediate Level Questions

4. Customers Who Haven't Placed Orders

SELECT c.* 
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

5. Total Sales by Product Category

SELECT 
    p.category,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category;

6. Average Order Value

SELECT 
    AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM order_items oi;

Advanced Level Questions

7. Outstanding Payments

SELECT 
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    COALESCE(SUM(p.amount), 0) AS amount_paid,
    (SUM(oi.quantity * oi.unit_price) - COALESCE(SUM(p.amount), 0)) AS balance
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN payments p ON o.order_id = p.order_id
GROUP BY o.order_id
HAVING balance > 0;

8. Top 5 Best-Selling Products

SELECT 
    p.name,
    SUM(oi.quantity) AS total_quantity_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.name
ORDER BY total_quantity_sold DESC
LIMIT 5;

9. Customer Purchase Frequency

SELECT 
    c.name,
    COUNT(o.order_id) AS order_count,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) / COUNT(o.order_id) AS avg_days_between_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
HAVING COUNT(o.order_id) > 1;

Expert Level Challenges

10. Seasonal Sales Analysis

SELECT 
    MONTH(o.order_date) AS month,
    SUM(oi.quantity * oi.unit_price) AS monthly_sales,
    (SUM(oi.quantity * oi.unit_price) - LAG(SUM(oi.quantity * oi.unit_price)) OVER (ORDER BY MONTH(o.order_date)) AS sales_growth
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY MONTH(o.order_date)
ORDER BY MONTH(o.order_date);

11. Customer Lifetime Value

SELECT 
    c.customer_id,
    c.name,
    SUM(oi.quantity * oi.unit_price) AS total_spent,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

12. Inventory Replenishment Alert

SELECT 
    p.product_id,
    p.name,
    p.stock_quantity,
    SUM(oi.quantity) AS monthly_demand,
    p.stock_quantity / NULLIF(SUM(oi.quantity), 0) AS months_of_inventory
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
GROUP BY p.product_id, p.name, p.stock_quantity
HAVING months_of_inventory < 2 OR months_of_inventory IS NULL;

Alternative Business Scenarios

Hospital Management System

Tables:

  • patients

  • doctors

  • appointments

  • treatments

  • bills

Sample Query: 

-- Patients with outstanding bills
SELECT p.name, SUM(b.amount) - SUM(b.amount_paid) AS balance
FROM patients p
JOIN bills b ON p.patient_id = b.patient_id
GROUP BY p.name
HAVING balance > 0;

School Database System

Tables:

  • students

  • teachers

  • courses

  • enrollments

  • grades

Sample Query: 

-- Top performing students
SELECT s.name, AVG(g.score) AS average_grade
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN grades g ON e.enrollment_id = g.enrollment_id
GROUP BY s.name
ORDER BY average_grade DESC
LIMIT 10;

Hotel Reservation System

Tables:

  • guests

  • rooms

  • reservations

  • services

  • payments

Sample Query: 

-- Room occupancy rate
SELECT 
    r.room_number,
    COUNT(res.reservation_id) AS bookings,
    DATEDIFF(SUM(res.check_out_date - res.check_in_date), 0) AS occupied_days
FROM rooms r
LEFT JOIN reservations res ON r.room_id = res.room_id
WHERE res.check_in_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY r.room_number;

MySQL Practice: Tanzania Revenue Authority (TRA) as our case study in  Operations - Complete Guide

This comprehensive guide covers beginner to advanced SQL queries for TRA operations, including solutions, explanations, and additional practice questions.

Table Definitions

-- Taxpayers table
CREATE TABLE taxpayers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    tin VARCHAR(15) UNIQUE,
    region VARCHAR(50),
    registration_date DATE
);

-- Tax returns table
CREATE TABLE tax_returns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    taxpayer_id INT,
    year INT,
    tax_amount DECIMAL(12,2),
    status VARCHAR(20),
    FOREIGN KEY (taxpayer_id) REFERENCES taxpayers(id)
);

-- Payments table
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    return_id INT,
    amount_paid DECIMAL(12,2),
    payment_date DATE,
    FOREIGN KEY (return_id) REFERENCES tax_returns(id)
);

Beginner Level Questions

Question 1: List All Registered Taxpayers

Problem: Display all taxpayer records.
Solution: 

SELECT * FROM taxpayers;

Question 2: Taxpayers Registered in 2023

Problem: Find taxpayers who registered in 2023.
Solution: 

SELECT * FROM taxpayers 
WHERE YEAR(registration_date) = 2023;

Question 3: Count Taxpayers by Region

Problem: Show the number of taxpayers per region.
Solution: 

SELECT region, COUNT(*) AS taxpayer_count
FROM taxpayers
GROUP BY region;

Intermediate Level Questions

Question 4: Taxpayers Who Haven't Filed Returns

Problem: Identify taxpayers without any tax returns.
Solution: 

SELECT t.* 
FROM taxpayers t
LEFT JOIN tax_returns tr ON t.id = tr.taxpayer_id
WHERE tr.id IS NULL;

Question 5: Total Tax Declared by Region

Problem: Calculate sum of declared taxes per region.
Solution: 

SELECT t.region, SUM(tr.tax_amount) AS total_tax
FROM taxpayers t
JOIN tax_returns tr ON t.id = tr.taxpayer_id
GROUP BY t.region;

Question 6: Average Tax Paid by Year

Problem: Find average tax amount per filing year.
Solution: 

SELECT year, AVG(tax_amount) AS avg_tax
FROM tax_returns
GROUP BY year;

Advanced Level Questions

Question 7: Outstanding Balances

Problem: Show returns with unpaid balances (tax_amount - payments).
Solution: 

SELECT 
    tr.id AS return_id,
    tr.tax_amount,
    COALESCE(SUM(p.amount_paid), 0) AS total_paid,
    (tr.tax_amount - COALESCE(SUM(p.amount_paid), 0)) AS balance
FROM tax_returns tr
LEFT JOIN payments p ON tr.id = p.return_id
GROUP BY tr.id, tr.tax_amount
HAVING balance > 0;

Question 8: Top 5 Compliant Taxpayers

Problem: List taxpayers who paid the most taxes.
Solution: 

SELECT 
    t.name, 
    SUM(p.amount_paid) AS total_paid
FROM taxpayers t
JOIN tax_returns tr ON t.id = tr.taxpayer_id
JOIN payments p ON tr.id = p.return_id
GROUP BY t.name
ORDER BY total_paid DESC
LIMIT 5;

Question 9: Payment Compliance Rate

Problem: Calculate % of tax paid vs declared per taxpayer.
Solution: 

SELECT 
    t.name,
    tr.tax_amount AS declared,
    SUM(p.amount_paid) AS paid,
    (SUM(p.amount_paid) / tr.tax_amount * 100) AS compliance_rate
FROM taxpayers t
JOIN tax_returns tr ON t.id = tr.taxpayer_id
JOIN payments p ON tr.id = p.return_id
GROUP BY t.name, tr.tax_amount;

Expert Level Challenges

Question 10: Late Payments Analysis

Problem: Identify payments made after 6 months of filing.
Solution: 

SELECT 
    t.name,
    tr.year,
    tr.tax_amount,
    p.payment_date,
    DATEDIFF(p.payment_date, tr.filing_date) AS days_late
FROM tax_returns tr
JOIN payments p ON tr.id = p.return_id
JOIN taxpayers t ON tr.taxpayer_id = t.id
WHERE DATEDIFF(p.payment_date, tr.filing_date) > 180;

Question 11: Regional Compliance Ranking

Problem: Rank regions by average compliance rate.
Solution: 

SELECT 
    t.region,
    AVG(p.amount_paid / tr.tax_amount * 100) AS avg_compliance,
    RANK() OVER (ORDER BY AVG(p.amount_paid / tr.tax_amount * 100) DESC) AS rank
FROM taxpayers t
JOIN tax_returns tr ON t.id = tr.taxpayer_id
JOIN payments p ON tr.id = p.return_id
GROUP BY t.region;

Question 12: Year-over-Year Growth

Problem: Calculate tax revenue growth by region year-over-year.
Solution: 

WITH yearly_totals AS (
    SELECT 
        t.region,
        tr.year,
        SUM(tr.tax_amount) AS total_tax
    FROM taxpayers t
    JOIN tax_returns tr ON t.id = tr.taxpayer_id
    GROUP BY t.region, tr.year
)
SELECT 
    a.region,
    a.year,
    a.total_tax,
    b.total_tax AS prev_year,
    ((a.total_tax - b.total_tax) / b.total_tax * 100) AS growth_pct
FROM yearly_totals a
JOIN yearly_totals b ON a.region = b.region AND a.year = b.year + 1
ORDER BY a.region, a.year;

Bonus: Data Modification Operations

1. Add New Taxpayer

INSERT INTO taxpayers (name, tin, region, registration_date)
VALUES ('Juma Hassan', '123456789', 'Dar es Salaam', '2024-01-15');

2. Update Tax Return Status

UPDATE tax_returns
SET status = 'Paid'
WHERE id IN (SELECT return_id FROM payments);

3. Record New Payment

INSERT INTO payments (return_id, amount_paid, payment_date)
VALUES (5, 250000.00, '2024-03-20');

Key Takeaways

✅ Mastered basic to advanced SQL queries for tax administration
✅ Learned analytical techniques for compliance monitoring
✅ Practiced real-world TRA data scenarios
✅ Gained skills in financial data analysis