Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Conditional Logic and Loops

Explore how to create stored procedures using variables, loops, and conditional statements in MySQL. This lesson helps you understand iterative processing with WHILE loops and IF conditions to classify data and manage row-by-row operations within routines.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
DELIMITER //
DROP PROCEDURE IF EXISTS LoopClassifySuppliers //
CREATE PROCEDURE LoopClassifySuppliers()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE total INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_name VARCHAR(50);
  DECLARE v_rate DECIMAL(5,2);
  DECLARE v_lead INT;
  -- Temporary table is already provided (do not modify)
  CREATE TEMPORARY TABLE IF NOT EXISTS SupplierAudit (
  SupplierID INT,
    SupplierName VARCHAR(50),
    PerformanceBand VARCHAR(20),
    ReviewFlag BOOLEAN
  );
  -- Determine total number of suppliers
  SELECT COUNT(SupplierID) 
  INTO   total 
  FROM   Suppliers;
  -- Loop through all suppliers
  WHILE i < total DO
    -- Fetch the i-th supplier’s data into variables
    SELECT   SupplierID, SupplierName, OnTimeDeliveryRate, AvgLeadTime 
    INTO     v_id, v_name, v_rate, v_lead 
    FROM     Suppliers 
    ORDER BY SupplierID 
    LIMIT    i, 1;
    -- Apply conditional logic for classification
    IF v_rate >= 98 AND v_lead <= 3 THEN
      INSERT INTO SupplierAudit VALUES (v_id, v_name, 'Excellent', FALSE);
    ELSEIF v_rate >= 95 AND v_lead <= 4 THEN
      INSERT INTO SupplierAudit VALUES (v_id, v_name, 'Good', FALSE);
    ELSE
      INSERT INTO SupplierAudit VALUES (v_id, v_name, 'Needs Attention', TRUE);
    END IF;
    -- Increment loop counter
    SET i = i + 1;
  END WHILE;
  -- Display audit table (already provided)
  SELECT   * 
  FROM     SupplierAudit 
  ORDER BY SupplierID;
END //
DELIMITER ;
-- Execute the routine
CALL LoopClassifySuppliers();

Explanation

The explanation of the solution code is given below:

  • Line 1: Switch the statement delimiter so the routine body can contain semicolons safely.

  • Line 3: Drop the procedure if it already exists so the new definition can be created cleanly.

  • Line 4: Create the procedure LoopClassifySuppliers.

  • Line 5: Start the block with BEGIN, which defines the scope for all declarations and executable statements inside the procedure.

  • Lines 6–11: Declare a loop counter i, a total row count total, and per-row variables for supplier fields.

  • Lines 14–19: Create a temporary table SupplierAudit if it does not already exist. This session-scoped table stores each supplier’s classification results.

  • Lines 22–24: Count all suppliers and store that count in total.

  • Lines 27–33: Start a WHILE loop that runs until i reaches total. Each iteration fetches the i-th supplier using ORDER BY plus LIMIT i, 1, and assigns values into variables with ...