Practice Questions — Views, Stored Procedures, and Functions

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, status
FROM customers
WHERE status = 'ACTIVE' AND deleted_at IS NULL;

SELECT status, COUNT(*) AS cnt
FROM active_customers
GROUP BY status;

DELIMITER $
CREATE PROCEDURE transfer(
    IN from_id INT,
    IN to_id INT,
    IN amt DECIMAL(10,2)
)
BEGIN
    DECLARE bal DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN ROLLBACK; RESIGNAL; END;

    START TRANSACTION;
    SELECT balance INTO bal FROM accounts WHERE id = from_id FOR UPDATE;
    IF bal < amt THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    UPDATE accounts SET balance = balance - amt WHERE id = from_id;
    UPDATE accounts SET balance = balance + amt WHERE id = to_id;
    COMMIT;
END$
DELIMITER ;

DELIMITER $
CREATE FUNCTION gst(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(price * rate / 100, 2);
END$
DELIMITER ;

SELECT name, price,
       gst(price, gst_rate) AS tax,
       price + gst(price, gst_rate) AS total
FROM products;

DELIMITER $
CREATE PROCEDURE fill_calendar(IN start_date DATE, IN num_days INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_days DO
        INSERT IGNORE INTO calendar(d)
        VALUES (DATE_ADD(start_date, INTERVAL i DAY));
        SET i = i + 1;
    END WHILE;
END$
DELIMITER ;

CREATE TABLE mv_daily_sales (
    d DATE PRIMARY KEY,
    rev DECIMAL(12,2)
);

DELIMITER $
CREATE PROCEDURE refresh_daily_sales()
BEGIN
    TRUNCATE TABLE mv_daily_sales;
    INSERT INTO mv_daily_sales(d, rev)
    SELECT DATE(created_at), SUM(total)
    FROM orders
    GROUP BY DATE(created_at);
END$
DELIMITER ;

SET GLOBAL event_scheduler = ON;
CREATE EVENT refresh_daily_sales_event
ON SCHEDULE EVERY 1 HOUR
DO CALL refresh_daily_sales();

DELIMITER $
CREATE PROCEDURE give_bonus(IN emp_id INT, OUT new_sal DECIMAL(10,2))
BEGIN
    DECLARE r INT;
    DECLARE mult DECIMAL(5,2);
    SELECT rating INTO r FROM employees WHERE id = emp_id;
    CASE r
        WHEN 5 THEN SET mult = 1.20;
        WHEN 4 THEN SET mult = 1.10;
        WHEN 3 THEN SET mult = 1.05;
        ELSE SET mult = 1.00;
    END CASE;
    UPDATE employees SET salary = salary * mult WHERE id = emp_id;
    SELECT salary INTO new_sal FROM employees WHERE id = emp_id;
END$
DELIMITER ;

DELIMITER $
CREATE PROCEDURE audit_zero_balance()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a_id INT;
    DECLARE a_bal DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, balance FROM accounts;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO a_id, a_bal;
        IF done = 1 THEN LEAVE read_loop; END IF;
        IF a_bal = 0 THEN
            INSERT INTO audit_log(account_id, message, logged_at)
            VALUES (a_id, 'Account has zero balance', NOW());
        END IF;
    END LOOP;
    CLOSE cur;
END$
DELIMITER ;

DELIMITER $
CREATE DEFINER = 'admin'@'localhost'
PROCEDURE dept_total(IN dept_name VARCHAR(20), OUT total DECIMAL(12,2))
SQL SECURITY DEFINER
BEGIN
    SELECT SUM(salary) INTO total
    FROM employees WHERE dept = dept_name;
END$
DELIMITER ;

-- Grant execute to the reporting user.
-- The user has NO direct SELECT on employees:
-- GRANT EXECUTE ON PROCEDURE shop.dept_total TO 'reporter'@'%';
-- Now the reporter can only call the procedure, not read the table directly.