SQL Lab 11

/* 1. STORE PROCEDURE: count_backwards
IN: Number to start from (x)
IN: Number to go to (Y)
Display all the numbers (inclusive) from x to y as one comma separated value
Ex: x = 10, y = 5, Display: 10,9,8,7,6,5 */
   

drop procedure count_backwards|

call count_backwards(10, 5)|


CREATE PROCEDURE count_backwards(IN x INT, in y int)
BEGIN
DECLARE i INT DEFAULT 0;
    DECLARE output TEXT;
   
    set i = x;
   
if x < y then
    WHILE i < y DO
SET output = CONCAT_WS(", ", output, i);
        SET i = i + 1;
    END WHILE;
    else
    WHILE i > y DO
SET output = CONCAT_WS(", ", output, i);
        SET i = i - 1;
    END WHILE;
    end if;
   
    SELECT output AS Result;
END |


/* 2. STORE PROCEDURE: count_funny
IN: Number to start from (x)
IN: Number to go to (Y)
Display all the numbers (inclusive) from x to y and a special message for any number divisible by 5
Ex: x = 10, y = 0
Display: 10 <- funny
9
8
7
6
5 <- funny
4
3
2
1
0 <- funny */


CREATE PROCEDURE count_backwards1(IN x INT, in y int)
BEGIN
DECLARE i INT;
    DECLARE output TEXT;
   
    set i = x;
   
if x < y then
    WHILE i < y DO
SET output = CONCAT_WS(", ", output, i);
        SET i = i + 1;
    END WHILE;
    else
    WHILE i >= y DO
if i % 5 = 0 then
SET output = CONCAT_WS("",output, i, "<- funny \n");
        else
SET output = CONCAT_WS("",output, i,"\n");
        end if;
        SET i = i - 1;
    END WHILE;
    end if;
   
    SELECT output AS Result;
END |

drop procedure count_backwards1|
call count_backwards1(20,0)|


/* 3. STORE PROCEDURE: Every_two_days
IN: Number of days
OUT: Every two days from now up to the number of days
Hint: While or Repete */

CREATE PROCEDURE Every_two_days(IN num INT)
BEGIN
DECLARE i INT DEFAULT 0;
 
#create temporary table
    CREATE TEMPORARY TABLE IF NOT EXISTS whileloop(
num INT
    );
   
    #empty table
    TRUNCATE TABLE whileloop;
   
    WHILE i < num DO
IF i MOD 2 = 0 THEN
INSERT INTO whileloop VALUES(i);
END IF;
       
        SET i = i + 1;
    END WHILE;
   
    SELECT * FROM whileloop;
END |

drop procedure Every_two_days|
CALL Every_two_days(30)|


/* 4. STORE PROCEDURE: Say_ma_name
IN: Animal id
OUT: Animal's name as much as it's age.
ex: I'm 3 -> George, George, George */
   
  CREATE PROCEDURE Say_ma_name (IN p_animal_id INT, OUT p_num_age INT) 
BEGIN

declare dt varchar(50);

    SELECT name,TIMESTAMPDIFF(YEAR, dob, NOW()) INTO dt, p_num_age
    FROM animal
    where animal.id = p_animal_id;
   
    SELECT concat_ws(" ", "I' m" , p_num_age, repeat(concat_ws(", ",dt, ""), p_num_age)) as result;
END |

SELECT id, TIMESTAMPDIFF(YEAR, dob, NOW()) INTO @var3, @var4
FROM animal
WHERE id = 7;
SELECT @var3, @var4|

drop procedure Say_ma_name|

call Say_ma_name(2, @p_num_age)|


/* 5. STORE PROCEDURE: my_birthday
IN: your birthday (DATE)
Display all the dates between Jan 1 - Dec 31 of this year in a loop.
Stop the loop on your birthday (the variable entered)

Ex: 2017-01-01
2017-01-02
2017-01-03
2017-01-04
2017-01-05 <- My Birthday :D */

CREATE PROCEDURE my_birthday(IN dateStart DATE, IN dateEnd DATE)
BEGIN
declare bd datetime;
    CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);
   
    WHILE dateStart <= dateEnd DO
      INSERT INTO date_range VALUES (dateStart);
      SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
    END WHILE;

    SELECT * FROM date_range;
   
    #if dateEnd = '2019-10-21 00:00:00' then
    select *, if(day = '2019-10-21 00:00:00'," <- My Birthday :D ","") as result from date_range;
    #end if;
   
    DROP TEMPORARY TABLE IF EXISTS date_range;


END|

drop procedure my_birthday|


call my_birthday('2019-01-01 00:00:00', '2019-10-21 00:00:00')|

/* etc */


SELECT
  dob AS BIRTHDAY
 ,FLOOR(DATEDIFF(dd,EMP.dob,GETDATE()) / 365.25) AS AGE_NOW
 ,FLOOR(DATEDIFF(dd,EMP.dob,GETDATE()+7) / 365.25) AS AGE_ONE_WEEK_FROM_NOW
FROM
  dob EMP
WHERE 1 = (FLOOR(DATEDIFF(dd,EMP.dob,GETDATE()+7) / 365.25))
          -
          (FLOOR(DATEDIFF(dd,EMP.dob,GETDATE()) / 365.25))|
         

SELECT
 name,dob,
 FLOOR(DATEDIFF(DATE(NOW()),dob) / 365.25) AS age_now,
 FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 30 DAY),dob) / 365.25) AS age_future

FROM animal

WHERE 1 = (FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 30 DAY),dob) / 365.25)) - (FLOOR(DATEDIFF(DATE(NOW()),dob) / 365.25))

ORDER BY MONTH(dob),DAY(dob);

create procedure my_bday()
begin
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
end|





CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END|

 drop procedure make_intervals|

 call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')|

call my_bday()|


Create procedure bday(begDate datetime, endDate datetime)
BEGIN
declare begDate datetime default '2019-01-01 00:00:00';
declare endDate datetime default '2019-10-12 00:00:00';

select begDate, endDate into begDate, endDate;

end|

drop procedure bday|

call bday('2019-01-01 00:00:00','2019-10-12 00:00:00')|

select now(), current_date()|



CREATE PROCEDURE leave1(IN num INT, IN stopTo INT)
BEGIN
DECLARE i INT DEFAULT 0;
    DECLARE output TEXT;

    r1: REPEAT
SET output = CONCAT_WS(", ", output, i);
        SET i = i + 1;
       
        IF i = stopTo THEN
LEAVE r1;
END IF;
    UNTIL i > num END REPEAT r1;
   
    SELECT output AS Result;
END |


CALL leave1(50, 21)|