Chapter 7. Working with Numbers

select e.ename, e.sal, (select exp(sum(ln(d.sal))) from emp d
 where d.empno <= e.empno and e.deptno=d.deptno) as running_prod
 FROM
  emp e
  where e.deptno=10

select a.empno, a.ename, a.sal,
        (select case when a.empno = min(b.empno) then sum(b.sal)
                     else sum(-b.sal)
                end
           from emp b
          where b.empno <= a.empno
            and b.deptno = a.deptno ) as rnk
from emp a
where a.deptno = 10

create view empsal as
select * from emp where deptno=10 order by sal;

select a.empno, a.ename, a.sal,
        (select sum(-b.sal)               
           from empsal b
          where b.sal <= a.sal
            and b.deptno = a.deptno ) as rnk
from empsal a
where a.deptno = 10

select sal
from emp
where deptno = 20
group by sal
having count(*) >= all ( select count(*)
                         from emp
                         where deptno = 20
                         group by sal )

/* Maximum Mode */
select max(sal) sal
from
(select sal
from emp
where deptno in (10,20)
group by sal
having count(*) >= all ( select count(*)
                         from emp
                         where deptno in (10,20)
                         group by sal )
						) x

select avg(sal)
	from 
    ( select e.sal
	from emp e
    INNER JOIN emp d
	ON e.deptno = d.deptno
	WHERE e.deptno = 20
    group by e.sal
    having sum(case when e.sal = d.sal then 1 else 0 end)
	            >= abs(sum(sign(e.sal - d.sal)))
	) x

select (sum(case when deptno = 10 then sal end)/sum(sal))*100 as pct
 from emp

create view V5 (id,amt,trx)
	as
	select 1, 100, 'PR' from t1 union all
	select 2, 100, 'PR' from t1 union all
	select 3, 50,  'PY' from t1 union all
	select 4, 100, 'PR' from t1 union all
	select 5, 200, 'PY' from t1 union all
	select 6, 50,  'PY' from t1

Chapter 8. Date Arithmetic

select hiredate - interval 5 day   as hd_minus_5D,
        hiredate + interval 5 day   as hd_plus_5D,
        hiredate - interval 5 month as hd_minus_5M,
        hiredate + interval 5 month as hd_plus_5M,
        hiredate - interval 5 year  as hd_minus_5Y,
        hiredate + interval 5 year  as hd_plus_5Y
   from emp
  where deptno=10

select max(case when ename = 'BLAKE'
                 then hiredate
            end) as blake_hd,
        max(case when ename = 'JONES'
                 then hiredate
             end) as jones_hd
from emp

CREATE TABLE `t500` (
  `ID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t500`
(`ID`)
select id FROM t100
UNION ALL
select id+100 FROM t100
UNION ALL
select id+200 FROM t100
UNION ALL
select id+300 FROM t100
UNION ALL
select id+400 FROM t100;

SELECT ones.num + tens.num + hundreds.num + 1
 FROM
  (SELECT 0 num UNION ALL
   SELECT 1 num UNION ALL
   SELECT 2 num UNION ALL
   SELECT 3 num UNION ALL
   SELECT 4 num UNION ALL
   SELECT 5 num UNION ALL
   SELECT 6 num UNION ALL
   SELECT 7 num UNION ALL
   SELECT 8 num UNION ALL
   SELECT 9 num) ones
   CROSS JOIN
  (SELECT 0 num UNION ALL
   SELECT 10 num UNION ALL
   SELECT 20 num UNION ALL
   SELECT 30 num UNION ALL
   SELECT 40 num UNION ALL
   SELECT 50 num UNION ALL
   SELECT 60 num UNION ALL
   SELECT 70 num UNION ALL
   SELECT 80 num UNION ALL
   SELECT 90 num) tens
   CROSS JOIN
  (SELECT 0 num UNION ALL
   SELECT 100 num UNION ALL
   SELECT 200 num UNION ALL
   SELECT 300 num UNION ALL
   SELECT 400 num) hundreds
   ORDER BY 1;

select sum(case when date_format(
                        date_add(jones_hd,
                                  interval t500.id-1 DAY),'%a')
                   in ( 'Sat','Sun' )
                then 0 else 1
           end) as days
FROM (select max(case when ename = 'BLAKE'
                 then hiredate
            end) as blake_hd,
        max(case when ename = 'JONES'
                 then hiredate
             end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' ) ) x
INNER JOIN
t500
where t500.id <= datediff(blake_hd,jones_hd)+1

select date_format(
           date_add(
               cast(
             concat(year(current_date),'-01-01')
                    as date),
                    interval t500.id-1 day),
                    '%W') day,
        count(*)
   from t500
  where t500.id <= datediff(
                          cast(
                        concat(year(current_date)+1,'-01-01')
                               as date),
                          cast(
                        concat(year(current_date),'-01-01')
                               as date))
 group by date_format(
             date_add(
                 cast(
               concat(year(current_date),'-01-01')
                      as date),
                      interval t500.id-1 day),
                      '%W')

Chapter 9. Date Manipulation

select  day(last_day(
		  date_add(
             date_add(
                 date_add(current_date,interval -dayofyear(current_date) day)
                 , interval 1 day)
		     , interval 1 month)
		  )) dy

select datediff((curr_year + interval 1 year),curr_year)
   from (
 select adddate(current_date,-dayofyear(current_date)+1) curr_year
   from t1
        ) x

select date_add(current_date,
                 interval -day(current_date)+1 day) firstday,
        last_day(current_date) lastday
   from t1

select dy
   from (
 select adddate(x.dy,interval t500.id-1 day) dy  
   from (
 select dy, year(dy) yr
   from (
 select adddate(
        adddate(current_date,
                interval -dayofyear(current_date) day),
                interval 1 day ) dy
        ) tmp1
        ) x,
        t500
  where year(adddate(x.dy,interval t500.id-1 day)) = x.yr
        ) tmp2
  where dayname(dy) = 'Friday'

select first_monday,
        case month(adddate(first_monday,28))
             when mth then adddate(first_monday,28)
                      else adddate(first_monday,21)
        end last_monday
from 
(select case sign(dayofweek(dy)-2)
            when 0 then dy
            when -1 then adddate(dy,abs(dayofweek(dy)-2))
             when 1 then adddate(dy,(7-(dayofweek(dy)-2)))
        end first_monday,
        mth
from (select adddate(adddate(current_date,-day(current_date)),1) dy,
        month(current_date) mth) x
        ) y