Cacher is the code snippet organizer for pro developers

DROP PROCEDURE IF EXISTS five_tests $
DROP TABLE IF EXISTS results $
CREATE PROCEDURE five_tests()

BEGIN

DECLARE i INT; 
SET i = 1;

CREATE TABLE results (
CustomerID INT NOT NULL,
NameTest INT,
StateTest INT,
AveTest INT,
StTest INT,
CountryTest INT,
TestSum INT,
PRIMARY KEY (CustomerID)
);

WHILE i  <= (SELECT MAX(CustomerID) from Customers2) DO

IF (SELECT LastName from Customers2 WHERE CustomerID = i) = 'Smith' THEN INSERT INTO results (CustomerID, NameTest) VALUES (i, 1);
  ELSE INSERT INTO results (CustomerID, NameTest) VALUES (i, 0);
  END IF;

IF (SELECT State from Customers2 WHERE CustomerID = i) = 'IL' THEN UPDATE results SET StateTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET StateTest = 0 WHERE CustomerID = i;
 END IF;
 
IF (SELECT Address from Customers2 WHERE CustomerID = i) LIKE '%ave%' THEN UPDATE results SET AveTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET AveTest = 0 WHERE CustomerID = i;
 END IF;
 
IF (SELECT Address from Customers2 WHERE CustomerID = i) LIKE '%st%' THEN UPDATE results SET StTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET StTest = 0 WHERE CustomerID = i;
 END IF;
 
IF (SELECT Country from Customers2 WHERE CustomerID = i) = 'USA' THEN UPDATE results SET CountryTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET CountryTest = 0 WHERE CustomerID = i;
 END IF;
 
SET  i = i + 1; 
END WHILE;

UPDATE results SET TestSum = (NameTest + StateTest + AveTest + StTest + CountryTest);
SELECT CustomerID from results WHERE TestSum >=3;

DROP TABLE results;

END $

DELMITER ;

CALL five_tests();


-- DETAIL!!!

-- Changes delimiter to $
DELIMITER $

-- Drops this procedure if it already exists so we can re-define it
DROP PROCEDURE IF EXISTS five_tests $

-- Drops the temporary table results if it already exists
DROP TABLE IF EXISTS results $

-- Creates the procedure five_tests with no arguments
CREATE PROCEDURE five_tests()

-- Begins the procedure
BEGIN

-- Declares variable i as an integer and sets it to a value of 1
DECLARE i INT; 
SET i = 1;

-- Creates the table results which will hold the results of the five tests, which will be recorded as 1s (TRUE) or 0s (FALSE)
CREATE TABLE results (
CustomerID INT NOT NULL,
NameTest INT,
StateTest INT,
AveTest INT,
StTest INT,
CountryTest INT,
TestSum INT,
PRIMARY KEY (CustomerID)
);

-- Begins the WHILE LOOP, tells it to continue looping as long as variable i is between the initial value 1 and the largest value found in the CustomerID column (primary key) of the Customers2 table (in other words, keep looping for each row in Customers2 table based on the primary key until you run out of rows, then stop)
WHILE i  <= (SELECT MAX(CustomerID) from Customers2) DO

-- Starts the first test, looks at LastName column in Customers2 based on the iteration of i (CustomerID) that we're on in the loop, and checks if the name is 'Smith' - if it is, it inserts a line into the temporary table results (including CustomerID) and records a 1, if it isn't Smith, it inserts a line in the temporay table results (including CustomerID) and records a 0
IF (SELECT LastName from Customers2 WHERE CustomerID = i) = 'Smith' THEN INSERT INTO results (CustomerID, NameTest) VALUES (i, 1);
  ELSE INSERT INTO results (CustomerID, NameTest) VALUES (i, 0);
  END IF;

-- Starts the second test, looks at State column in Customers2 based on the iteration of i (CustomerID) that we're on in the loop, and checks if the state is 'IL' - if it is, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 1 in the StateTest column, if it isn't IL, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 0
IF (SELECT State from Customers2 WHERE CustomerID = i) = 'IL' THEN UPDATE results SET StateTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET StateTest = 0 WHERE CustomerID = i;
 END IF;

-- Starts the third test, looks at Address column in Customers2 based on the iteration of i (CustomerID) that we're on in the loop, and checks if it contains the string 'ave' - if it does, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 1 in the AveTest column, if it doesn't, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 0
IF (SELECT Address from Customers2 WHERE CustomerID = i) LIKE '%ave%' THEN UPDATE results SET AveTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET AveTest = 0 WHERE CustomerID = i;
 END IF;
 
-- Starts the fourth test, looks at Address column in Customers2 based on the iteration of i (CustomerID) that we're on in the loop, and checks if it contains the string 'st' - if it does, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 1 in the StTest column, if it doesn't, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 0
IF (SELECT Address from Customers2 WHERE CustomerID = i) LIKE '%st%' THEN UPDATE results SET StTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET StTest = 0 WHERE CustomerID = i;
 END IF;

-- Starts the fifth test, looks at Country column in Customers2 based on the iteration of i (CustomerID) that we're on in the loop, and checks if it's 'USA' - if it is, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 1 in the CountryTest column, if it isn't, it finds the line in the temporary results table for the CustomerID that was created during test 1 and records a 0
IF (SELECT Country from Customers2 WHERE CustomerID = i) = 'USA' THEN UPDATE results SET CountryTest = 1 WHERE CustomerID = i;
 ELSE UPDATE results SET CountryTest = 0 WHERE CustomerID = i;
 END IF;

-- After administering the five tests for the iteration of i (CustomerID) that we're on in the loop and recording all of the results into the temporary results table, it increments the loop up by one
SET  i = i + 1; 

-- Ends the WHILE LOOP after reaching the maximum limit
END WHILE;

-- After the WHILE LOOP has looped through each row, this tells it to sum all of the values recorded in the other columns - a value of 1 means that CustomerID had TRUE for 1 of the 5 tests, if it records 3, that means that CustomerID was TRUE for 3 of the 5 tests, etc.
UPDATE results SET TestSum = (NameTest + StateTest + AveTest + StTest + CountryTest);

-- This is what returns the results to the viewer - it select all CustomerIDs who passed 3 or more of the tests
SELECT CustomerID from results WHERE TestSum >=3;


-- Deletes the temporary table results
DROP TABLE results;


-- Ends the stored procedure
END $

-- Changes the delimiter back to ;
DELMITER ;

-- CALLS the procedure we've just created, returns the results to the user
CALL five_tests();