sql practice.txt

4-2
select distinct job from emp;

4-3
select 
empno as employee_no,
ename as employee_name,
mgr as manager,
sal as salary,
comm as commission,
deptno as department_no
from emp
order by deptno desc, ename asc;

5-1
select * 
from emp
where ename like '%S';

5-2
select 
empno, ename, job, sal, deptno
from emp
where deptno = 30
and job = 'SALESMAN';

5-3
select 
empno, ename, sal, deptno
from emp
where deptno between 20 and 30
and sal > 2000;
--------------------------
select 
empno, ename, sal, deptno
from emp
where deptno in (20, 30)
and sal > 2000;
---------------------------
select 
empno, ename, sal, deptno
from emp
where deptno = 20
and sal > 2000
union
select 
empno, ename, sal, deptno
from emp
where deptno = 30
and sal > 2000;

5-4
select * 
from emp
where not sal >=2000
union
select * 
from emp
where not sal <=3000;

5-5
select ename, empno, sal, deptno
from emp
where ename like '%E%' 
and deptno = 30
and not sal between 1000 and 2000;

5-6
select *
from emp
where comm is null 
and mgr is not null
and job in ('CLERK', 'MANAGER')
and ename not like '_L%';

6-1
select 
    empno,
    rpad(substr(empno,1,2), length(empno), '*') as masking_empno,
    ename,
    rpad(substr(ename,1,2), length(ename), '*') as masking_ename
from emp
where length(ename) >= 5 and length(ename) < 6 ;

6-2
select 
    empno,
    ename,
    sal,
    trunc(sal/21.5, 2) as day_pay,
    round(sal/21.5/8, 1) as time_pay
from emp;

6-3
select 
    empno,
    ename,
    hiredate,
    to_char(next_day(add_months(hiredate, 3) ,'monday'), 'YYYY-MM-DD') as r_job,
    nvl(to_char(comm), 'N/A') as comm
from emp;

6-4
select 
    empno,
    ename,
    mgr,
    case
        when mgr is null then '0000'
        when substr(mgr, 1, 2) = 75 then '5555' 
        when substr(mgr, 1, 2) = 76 then '6666' 
        when substr(mgr, 1, 2) = 77 then '7777' 
        when substr(mgr, 1, 2) = 78 then '8888'
        else to_char(mgr)
        end
    as chg_mgr
from emp;

7-1
select
    deptno,
    trunc(avg(sal)) as avg_sal,
    max(sal) as max_sal,
    min(sal) as min_sal,
    count(*) as cnt
from emp
group by deptno;

7-2
select
    job,
    count(*)
from emp
group by job
having count(*) >= 3;

7-3
select
    extract(year from hiredate) as hire_year,
    deptno,
    count(*) as cnt
from emp
group by extract(year from hiredate), deptno;
---------------------------------------------------------
select
    to_char(hiredate, 'YYYY') as hire_year,
    deptno,
    count(*) as cnt
from emp
group by to_char(hiredate, 'YYYY'), deptno;

7-4
select 
    exist_comm,
    count(*)
from
    (select 
        case 
            when comm is null then 'X'
            else 'O'
        end as exist_comm
    from emp)
group by exist_comm;

7-5
select 
    deptno,
    extract(year from hiredate) as hire_year,
    count(*) as cnt,
    max(sal) as max_sal,
    sum(sal) as sum_sal,
    avg(sal) as avg_sal
from emp
group by rollup(deptno, extract(year from hiredate));