-- store_history.sql

DROP TABLE store_history
/
CREATE TABLE store_history(
   store_num NUMBER, 
   emp_count NUMBER,
   dt DATE)
/

DECLARE
   ecount NUMBER;
BEGIN
   FOR s IN 1..7 LOOP 
      SELECT count(id) 
        INTO ecount
        FROM employee
       WHERE store_number = s;
      INSERT INTO store_history
      VALUES(s, ecount, sysdate);
   END LOOP;
END;
/
