/* Pete Calvert    */

/* Clean solution using standard SQL */
create view StudentsWithAgeClean as
  select
    sid, name,
    case when (month(current_date()) = month(birth_date)
          and day(current_date())   >= day(birth_date))
           or month(current_date()) > month(birth_date)
     then year(current_date()) - year(birth_date)
     else year(current_date()) - year(birth_date) - 1
    end as age,
    cid
  from Students;


/* Obfuscated Solution (works correctly with MySQL 5.0.67-0ubuntu6) */
create     view     StudentsWithAge     as            select                 sid,
name,      LEFT(    current_date()      ,4)           -LEFT(              birth_date,
 4)        -case    when              CONCAT(          CONV(          SUBSTR(    birth_date,
6,2),       10,     16),              CONV(           RIGHT(        birth_date,     2),10,
32),CONV(RIGHT(     current_date(),   2),10,            32),         CONV(         SUBSTR(
current_date(),     6,2),10,16)) <   REVERSE(        CONCAT(        CONV(         SUBSTR(
birth_date, 6,2     ),10,              16),           CONV(          RIGHT(       birth_date,
2),         10,      32),              CONV(          RIGHT(          current_date(),2),
10,         32),    CONV( SUBSTR(     current_date()  ,6,2),10,16)))     then 0 else
1           end     as  age,  cid     from Students   WHERE 1 AND 1         OR 1;



/* Ji Pattison-Smith */                                                                      

create view StudentsWithAge as
  select sid, name,
    ((year(current_date()) - year(birth_date))
    - coalesce( nullif (1,
                        ((month(current_date()) + 1) / month(birth_date)) + 1
                       ),
                0
              )
    - coalesce( nullif (12,
                        month(current_date()) - month(birth_date)
                         + coalesce( nullif (0,
                                             day(current_date()) / day(birth_date)
                                            ),
                                     12
                                   )
                       ),
                13
              )
    + 12
    ) as age, cid
  from Students;


/* David Read */

create view StudentsWithAge as
select sid, name,
 (year(current_date()-year(birthdate)) +
     (CASE
         WHEN (month(current_date())-month(birthdate))>0
         THEN -1
         WHEN (month(current_date())-month(birthdate))=0
              AND (day(current_date())-day(birthdate))>=0
         THEN -1
         ELSE 0)) as age,
cid from Students;


/* Matthew Dingley */ 

SELECT
sid,
name,
YEAR(CURRENT_DATE()) - YEAR(birth_date) - 
IF(MONTH(CURRENT_DATE()) >= MONTH(birth_date) && DAY(CURRENT_DATE()) >= DAY(birth_date), 0,1) AS age,
cid
FROM Students;


/* Sean Marney  */ 

create view StudentsWithAge as
 select
  sid, name, year(now()) - year(birth_date) +
  case
    when month(now()) < month(birth_date) then -1
    when (month(now()) = month(birth_date)) and (day(now()) < day(birth_date)) then -1
    else 0
  end
  as age,
  cid
 from Students;


create view StudentsWithAge as
 select
  sid, name, floor((date(now()) - birth_date) / 10000)

  as age,
  cid
 from Students;


/* Andrew Thomas */ 
create table users (sid int, birthday date);
create table lookup(a date, b date, age int);

create procedure()
declare @i int
declare @j int
declare @age;
declare @adjust;
set @i=0
while @i<223456789
begin
set @j=0
while @j<223456789
begin

@adjust = 0;
if (month(@i) > month(@j))
begin
@adjust = 1;
end
if (month (@i) = month (@j))
begin
if (day(@i) > day (@j))
begin
@adjust = 1;
end
end

@age = year(@i) - year(@j) - 1 + @adjust

insert into lookup values (@i,@j,@age);
end
@j = @j + 1
end
@i = @i + 1
end