Database Administrator MySQL Practice Problems with Real-World Scenarios & Solutions

SELECT name, email 
FROM customers 
WHERE YEAR(join_date) = 2023 
AND premium_member = TRUE;

SELECT name, price 
FROM products 
WHERE category = 'Electronics' 
AND stock_quantity < 50 
ORDER BY price DESC;

SELECT p.name, COUNT(*) AS cardiology_visits
FROM patients p
JOIN visits v ON p.patient_id = v.patient_id
JOIN doctors d ON v.doctor_id = d.doctor_id
WHERE d.specialty = 'Cardiology' 
AND YEAR(v.visit_date) = 2023
GROUP BY p.patient_id, p.name
HAVING COUNT(*) > 2;

SELECT c.title, COUNT(*) AS enrolled_students
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE c.department = 'Computer Science'
AND e.semester = 'Fall2023'
GROUP BY c.course_id, c.title
HAVING COUNT(*) > 50;

SELECT a.account_id, c.name, COUNT(*) AS large_withdrawals
FROM accounts a
JOIN transactions t ON a.account_id = t.account_id
JOIN customers c ON a.customer_id = c.customer_id
WHERE t.type = 'withdrawal'
AND t.amount > 5000
GROUP BY a.account_id, c.name, t.transaction_date
HAVING COUNT(*) >= 3
AND DATEDIFF(MAX(t.transaction_date), MIN(t.transaction_date)) <= 7;

WITH business_seats AS (
    SELECT 
        f.route_id,
        r.origin,
        r.destination,
        COUNT(CASE WHEN b.seat_class = 'Business' THEN 1 END) AS business_booked,
        (SELECT COUNT(*) FROM aircraft_seats WHERE aircraft_id = f.aircraft_id AND class = 'Business') AS total_business_seats
    FROM flights f
    JOIN routes r ON f.route_id = r.route_id
    JOIN bookings b ON f.flight_id = b.flight_id
    WHERE f.departure_time BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND NOW()
    GROUP BY f.route_id, r.origin, r.destination, f.aircraft_id
)
SELECT 
    origin, 
    destination,
    business_booked,
    total_business_seats,
    (business_booked/total_business_seats)*100 AS occupancy_rate
FROM business_seats
WHERE (business_booked/total_business_seats) < 0.4;

WITH current_stats AS (
    SELECT 
        p.product_id,
        p.name,
        p.current_price,
        AVG(cp.comp_price) AS avg_comp_price,
        (p.current_price - p.cost)/p.current_price AS current_margin,
        SUM(CASE WHEN s.sale_date BETWEEN DATE_SUB(NOW(), INTERVAL 2 MONTH) AND DATE_SUB(NOW(), INTERVAL 1 MONTH) THEN s.quantity ELSE 0 END) AS prev_month_sales,
        SUM(CASE WHEN s.sale_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() THEN s.quantity ELSE 0 END) AS last_month_sales
    FROM products p
    LEFT JOIN sales s ON p.product_id = s.product_id
    LEFT JOIN competitor_prices cp ON p.product_id = cp.product_id
    GROUP BY p.product_id, p.name, p.current_price, p.cost
)
SELECT 
    product_id,
    name,
    current_price,
    ROUND(avg_comp_price, 2) AS competitor_avg,
    ROUND(current_margin*100, 1) AS margin_percent,
    CASE 
        WHEN last_month_sales = 0 THEN -100 
        ELSE ROUND((last_month_sales-prev_month_sales)/prev_month_sales*100, 1) 
    END AS sales_change_percent,
    CONCAT('Recommended new price: $', ROUND(avg_comp_price*1.1, 2)) AS price_recommendation
FROM current_stats
WHERE current_price > avg_comp_price*1.2
AND ((last_month_sales-prev_month_sales)/prev_month_sales) < -0.15
AND current_margin > 0.3;

WITH historical_avg AS (
    SELECT 
        sensor_id,
        AVG(speed) AS avg_speed,
        AVG(vehicle_count) AS avg_vehicles
    FROM traffic_readings
    WHERE timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND DATE_SUB(NOW(), INTERVAL 1 DAY)
    GROUP BY sensor_id
),
current_readings AS (
    SELECT 
        r.sensor_id,
        s.location,
        s.highway,
        r.speed,
        r.vehicle_count,
        h.avg_speed,
        h.avg_vehicles
    FROM traffic_readings r
    JOIN traffic_sensors s ON r.sensor_id = s.sensor_id
    JOIN historical_avg h ON r.sensor_id = h.sensor_id
    WHERE r.timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
SELECT 
    cr.location,
    cr.highway,
    cr.speed AS current_speed,
    ROUND(cr.avg_speed, 1) AS historical_speed,
    cr.vehicle_count AS current_vehicles,
    ROUND(cr.avg_vehicles) AS historical_vehicles,
    CASE 
        WHEN (cr.avg_speed - cr.speed)/cr.avg_speed > 0.3 
        AND (cr.vehicle_count - cr.avg_vehicles)/cr.avg_vehicles > 0.2
        THEN 'CONGESTION WARNING'
        ELSE 'Normal'
    END AS status
FROM current_readings cr
WHERE NOT EXISTS (
    SELECT 1 FROM incidents i
    WHERE i.location = cr.location
    AND i.end_time IS NULL
)
HAVING status = 'CONGESTION WARNING';