Chapter 9. Date Manipulation

select max(case dw when 2 then dm end) as Mo,
       max(case dw when 3 then dm end) as Tu,
        max(case dw when 4 then dm end) as We,
        max(case dw when 5 then dm end) as Th,
        max(case dw when 6 then dm end) as Fr,
        max(case dw when 7 then dm end) as Sa,
        max(case dw when 1 then dm end) as Su
   from (
 select date_format(dy,'%u') wk,
        date_format(dy,'%d') dm,
        date_format(dy,'%w')+1 dw
   from (
 select adddate(x.dy,t500.id-1) dy,
        x.mth
   from (
 select adddate(current_date,-dayofmonth(current_date)+1) dy,
        date_format(
            adddate(current_date,
                    -dayofmonth(current_date)+1),
                    '%m') mth
    from t1
         ) x,
           t500
  where t500.id <= 31
    and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
        ) y
        ) z
  group by wk
  order by wk

select quarter(adddate(dy,-1)) QTR, date_add(dy,interval -3 month) Q_start,
       adddate(dy,-1) Q_end
FROM
 (
   select date_add(dy,interval (3*id) month) dy
   from
      (
      select id, adddate(current_date,-dayofyear(current_date)+1) dy
      from t500
      where id <= 4
      ) x
  ) y

select date_add(
        adddate(trim(q_end),-day(q_end)+1),
        interval -2 month) q_start,
        q_end
from
	(select last_day(
     str_to_date(
          concat(
          substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end
   from (
    select 20161 as yrq from t1 union all
	select 20162 as yrq from t1 union all
	select 20163 as yrq from t1 union all
	select 20164 as yrq from t1
    ) x
    ) y

select z.mth, count(e.hiredate) num_hired
   from (
 select date_add(min_hd,interval t500.id-1 month) mth
   from (
 select min_hd, date_add(max_hd,interval 11 month) max_hd
   from (
 select adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,
        adddate(max(hiredate),-dayofyear(max(hiredate))+1) max_hd
   from emp
        ) x
        ) y,
        t500
 where date_add(min_hd,interval t500.id-1 month) <= max_hd
       ) z left join emp e
    on (z.mth = adddate(
               date_add(
               last_day(e.hiredate),interval -1 month),1))
 group by z.mth
 order by 1

select concat(a.ename,
        ' was hired on the same month and weekday as ',
        b.ename) msg
   from emp a, emp b
  where date_format(a.hiredate,'%w%M') =
        date_format(b.hiredate,'%w%M')
    and a.empno < b.empno
 order by a.ename

Chapter 10. Working with Ranges

select deptno, ename, hiredate, sal,
        coalesce(cast(sal-next_sal as char(10)), 'N/A') as diff
   from (
 select e.deptno,
        e.ename,
        e.hiredate,
        e.sal,
        (select min(sal) from emp d
          where d.deptno=e.deptno
            and d.hiredate =
                 (select min(hiredate) from emp d
                   where e.deptno=d.deptno
                     and d.hiredate > e.hiredate)) as next_sal
   from emp e
        ) x

CREATE VIEW v6 AS
select PROJ_ID, PROJ_START,
       DATE_ADD(PROJ_START, interval 1 day) PROJ_END
from (
      select ID PROJ_ID, 
        DATE_ADD('2004-12-31', interval id day) PROJ_START
      from t100 where id <= 4
      ) x
UNION ALL
select PROJ_ID, PROJ_START,
       DATE_ADD(PROJ_START, interval 1 day) PROJ_END
from (
      select ID PROJ_ID, 
        DATE_ADD('2005-01-01', interval id day) PROJ_START
      from t100 where id = 5
      ) x
UNION ALL
select PROJ_ID, PROJ_START,
       DATE_ADD(PROJ_START, interval 1 day) PROJ_END
from (
      select ID PROJ_ID, 
        DATE_ADD('2005-01-10', interval id day) PROJ_START
      from t100 where id between 6 and 9
      ) x
UNION ALL
select PROJ_ID, PROJ_START,
       DATE_ADD(PROJ_START, interval 1 day) PROJ_END
from (
      select ID PROJ_ID, 
        DATE_ADD('2005-01-11', interval id day) PROJ_START
      from t100 where id = 10
      ) x
UNION ALL
select PROJ_ID, PROJ_START,
       DATE_ADD(PROJ_START, interval 1 day) PROJ_END
from (
      select ID PROJ_ID, 
        DATE_ADD('2005-01-15', interval id day) PROJ_START
      from t100 where id between 11 and 14
      ) x

create view v7
	as
	select a.*,
	       case
	         when (
	            select b.proj_id
	              from V6 b
	            where a.proj_start = b.proj_end
	             )
	             is not null then 0 else 1
	          end as flag
	from V6 a

select proj_grp,  min(proj_start) as proj_start, max(proj_end) as proj_end
from
   (
   select a.proj_id,a.proj_start,a.proj_end,
        (select sum(b.flag)
           from V7 b
          where b.proj_id <= a.proj_id) as proj_grp
   from V7 a
   ) x
group by proj_grp

select y.yr, coalesce(x.cnt,0) as cnt
from
  (
     select min_year-mod(cast(min_year as unsigned),10)+rn as yr
     from
       (
        select (select min(extract(year from hiredate)) from emp) as min_year,  id-1 as rn
        from t10
       ) a
   ) y
left join
  (
 select extract(year from hiredate) as yr, count(*) as cnt
   from emp
  group by extract(year from hiredate)
  ) x
using (yr)

Chapter 11. Advanced Searching

select x.ename
from (
   select a.ename,
    (select count(*)
            from emp b
            where b.ename <= a.ename) as rn
     from emp a
     ) x
where mod(x.rn,2) = 1
ORDER BY 1

select e.ename, d.deptno, d.dname, d.loc
	  from dept d left join emp e
	    on (d.deptno = e.deptno)
	 where e.deptno = 10
	    or e.deptno = 20
	 order by 2

select e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
   on (d.deptno = e.deptno
      and (e.deptno=10 or e.deptno=20))
order by 2

select a.ename, a.sal, a.hiredate,
        (select min(hiredate) from emp b
          where b.hiredate > a.hiredate
            and b.sal > a.sal ) as next_sal_grtr,
        (select min(hiredate) from emp b
          where b.hiredate > a.hiredate) as next_hire
from emp a

select ename, sal, hiredate
from
 (
    select a.ename, a.sal, a.hiredate,
        (select min(hiredate) from emp b
          where b.hiredate > a.hiredate
            and b.sal > a.sal ) as next_sal_grtr,
        (select min(hiredate) from emp b
          where b.hiredate > a.hiredate) as next_hire
    from emp a
  ) x
where next_sal_grtr = next_hire

select e.ename, e.sal,
       coalesce(
         (select min(sal) from emp d where d.sal > e.sal),
         (select min(sal) from emp)
       ) as forward,
       coalesce(
         (select max(sal) from emp d where d.sal < e.sal),
         (select max(sal) from emp)
       ) as rewind
    from emp e
    order by 2

select (select count(distinct b.sal)
           from emp b
          where b.sal <= a.sal) as rnk,
        a.sal
from emp a
order by 2

select e.deptno,
         e.ename,
         e.sal,
         e.hiredate, 
         ( select max(d.sal)
           from emp d
           where d.deptno = e.deptno
               and d.hiredate =
               (select max(f.hiredate)
                   from emp f
                  where f.deptno = e.deptno)) as latest_sal
from emp e
order by 1, 4 desc

Chapter 12. Reporting and Warehousing

select max(case when job='CLERK'
                  then ename else null end) as clerks,
	   max(case when job='ANALYST'
				then ename else null end) as analysts,
	   max(case when job='MANAGER'
				then ename else null end) as mgrs,
	   max(case when job='PRESIDENT'
				then ename else null end) as prez,
	   max(case when job='SALESMAN'
				then ename else null end) as sales
FROM
  (
     select e.job,
         e.ename,
         (select count(*) from emp d
           where e.job=d.job and e.empno < d.empno) as rnk
     from emp e
     order by rnk
   ) x
group by rnk

Chapter 12. Reporting and Warehousing

select sum(case when deptno=10 then sal end) as d10_sal,
        sum(case when deptno=20 then sal end) as d20_sal,
        sum(case when deptno=30 then sal end) as d30_sal
   from emp

select ceil(rnk/5.0) as grp,
       empno, ename
from
(
select e.empno, e.ename,
        (select count(*) from emp d
          where e.empno > d.empno)+1 as rnk
from emp e
) x
order by grp

select mod(count(*),4)+1 as grp,
        e.empno,
        e.ename
from emp e, emp d
where e.empno >= d.empno
group by e.empno,e.ename
order by 1

select deptno,
        lpad('*',count(*),'*') as cnt
   from emp
  group by deptno

select max(deptno_10) as d10,
       max(deptno_20) as d20,
       max(deptno_30) as d30
from 
   (
   select case when e.deptno=10 then '*' else null end deptno_10,
        case when e.deptno=20 then '*' else null end deptno_20,
        case when e.deptno=30 then '*' else null end deptno_30,
        (select count(*) from emp d
          where e.deptno=d.deptno and e.empno < d.empno ) as rnk
   from emp e
   ) x
group by rnk
order by 1 desc, 2 desc, 3 desc

select e.deptno,e.ename,e.job,e.sal,
        (select max(sal) from emp d
          where d.deptno = e.deptno) as max_by_dept,
        (select max(sal) from emp d
          where d.job = e.job) as max_by_job,
        (select min(sal) from emp d
          where d.deptno = e.deptno) as min_by_dept,
        (select min(sal) from emp d
          where d.job = e.job) as min_by_job
from emp e

 select deptno,ename,job,sal, 
    case when sal = max_by_dept
             then 'TOP SAL IN DEPT'
             when sal = min_by_dept
             then 'LOW SAL IN DEPT'
        end as dept_status,
        case when sal = max_by_job
             then 'TOP SAL IN JOB'
             when sal = min_by_job
            then 'LOW SAL IN JOB'
         end as job_status
 from
    (
     select e.deptno,e.ename,e.job,e.sal,
        (select max(sal) from emp d
          where d.deptno = e.deptno) as max_by_dept,
        (select max(sal) from emp d
          where d.job = e.job) as max_by_job,
        (select min(sal) from emp d
          where d.deptno = e.deptno) as min_by_dept,
        (select min(sal) from emp d
          where d.job = e.job) as min_by_job
     from emp e
   ) x
   where sal in (max_by_dept,max_by_job,
                 min_by_dept,min_by_job)

select coalesce(job,'TOTAL') job,
        sum(sal) sal
from emp
group by job with rollup

select deptno, job,
    'TOTAL BY DEPT AND JOB' as category,
     sum(sal) as sal
from emp
group by deptno, job

select deptno, job,
    'TOTAL BY DEPT AND JOB' as category,
     sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
union all
select null,null,'GRAND TOTAL FOR TABLE', sum(sal)
from emp

select ename,
        case when job = 'CLERK'
             then 1 else 0
       end as is_clerk,
        case when job = 'SALESMAN'
             then 1 else 0
        end as is_sales,
        case when job = 'MANAGER'
             then 1 else 0
        end as is_mgr,
        case when job = 'ANALYST'
             then 1 else 0
        end as is_analyst,
        case when job = 'PRESIDENT'
             then 1 else 0
        end as is_prez
from emp
order by 2,3,4,5,6

select case deptno when 10 then ename end as d10,
        case deptno when 20 then ename end as d20,
        case deptno when 30 then ename end as d30,
        case job when 'CLERK' then ename end as clerks,
        case job when 'MANAGER' then ename end as mgrs,
        case job when 'PRESIDENT' then ename end as prez,
        case job when 'ANALYST' then ename end as anals,
        case job when 'SALESMAN' then ename end as sales
from emp

select e.ename,
        e.deptno,
        (select count(*) from emp d
          where d.deptno = e.deptno) as deptno_cnt,
        job,
        (select count(*) from emp d
          where d.job = e.job) as job_cnt,
        (select count(*) from emp) as total
from emp e

select e.hiredate,
       e.sal,
        (select sum(sal) from emp d
          where d.hiredate between e.hiredate-90
                              and e.hiredate) as spending_pattern
from emp e
order by 1

Chapter 13. Hierarchical Queries

select concat(a.ename, ' works for ',b.ename) as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno

select CONCAT(a.ename,'-->',b.ename
                ,'-->',c.ename) as leaf___branch___root
from emp a, emp b, emp c
where a.ename = 'MILLER'
      and a.mgr = b.empno
      and b.mgr = c.empno

select emp_tree
from
   (
  select ename as emp_tree
    from  emp
   where mgr is null
   UNION
   select concat(a.ename,' - ',b.ename)
   from emp a
         join
         emp b on (a.empno=b.mgr)
   where a.mgr is null
   UNION
    select concat(a.ename,' - ',
                b.ename,' - ',c.ename)
    from emp a
         join
         emp b on (a.empno=b.mgr)
         left join
         emp c on (b.empno=c.mgr)
    where a.ename = 'KING'
    UNION
    select concat(a.ename,' - ',b.ename,' - ',
                c.ename,' - ',d.ename)
    from emp a
         join
         emp b on (a.empno=b.mgr)
         join
         emp c on (b.empno=c.mgr)
         left join
         emp d on (c.empno=d.mgr)
   where a.ename = 'KING'
   ) x
   where emp_tree is not null
   order by 1

select a.ename as root,
        b.ename as branch,
        c.ename as leaf
   from emp a, emp b, emp c
  where a.ename = 'JONES'
    and a.empno = b.mgr
    and b.empno = c.mgr

select distinct case t100.id
             when 1 then root
             when 2 then branch
              else        leaf
         end as JONES_SUBORDINATES
from
   (
  select a.ename as root,
        b.ename as branch,
        c.ename as leaf
   from emp a, emp b, emp c
  where a.ename = 'JONES'
    and a.empno = b.mgr
    and b.empno = c.mgr
    ) x, t100
  where t100.id <= 6

select e.ename,
       (select sign(count(*)) from emp d
          where 0 =
            (select count(*) from emp f
              where f.mgr = e.empno)) as is_leaf,
        (select sign(count(*)) from emp d
          where d.mgr = e.empno
           and e.mgr is not null) as is_branch,
        (select sign(count(*)) from emp d
          where d.empno = e.empno
            and d.mgr is null) as is_root
from emp e
order by 4 desc,3 desc

Chapter 5. Metadata Queries

select table_name
   from information_schema.tables
 where table_schema = 'SMEAGOL'

select column_name, data_type, ordinal_position
from information_schema.columns
where table_schema = 'SMEAGOL'
    and table_name   = 'EMP'