select * from emp;
select * from dept;
select * from salgrade;
select ename || sal from emp;
select distinct deptno from emp;
select distinct deptno, job from emp;
select * from emp where deptno = 10;
select * from emp where deptno <> 10;
select * from emp where sal between 500 and 1500;
select *
  from emp
 where sal >= 500
   and sal <= 1500;
select * from emp where comm is null;
select * from emp where comm is not null;
select * from emp where sal in (800, 1500, 2000);
select * from emp where ename in ('SMITH', 'TURNER', 'CHEN');
select * from emp where ename like '%ALL%';
select * from emp where ename like '%_A%';
select * from emp where ename like '%$%%' escape '$';
select * from emp order by empno asc;
select * from emp order by deptno asc, empno desc;
select substr(ename, 2, 3) from emp;
select ename, sal * 12 from emp;
select ename, sal * 12 annual_salary from emp;
select 2 * 3 from dual;
select sysdate from dual;
select chr(65) from dual;
select ascii('A') from dual;
select round(25.863) from dual;
select round(25.863, 1) from dual;
select round(25.863, -1) from dual;
select to_char(sal, '$99,999.9999') from emp;
select to_char(sal, 'L99,999.9999') from emp;
select to_char(sal, 'L0000.0000') from emp;
select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
select *
  from emp
 where hiredate > to_date('1985-6-8 18:30:20', 'YYYY-MM-DD HH24:MI:SS');
select * from emp where sal > to_number('$1,500.00', '$9,999.99');
select ename, sal * 12 + nvl(comm, 0) from emp;
select round(avg(sal), 2) from emp;
select count(*) from emp where deptno = 10;
select count(comm) from emp;
select count(distinct deptno) from emp;
select deptno, avg(sal) from emp group by deptno;
select deptno, job, max(sal) from emp group by deptno, job;
select ename, sal from emp where sal = (select max(sal) from emp);
select deptno, max(sal) from emp group by deptno;
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
select deptno, avg(sal)
  from emp
 where sal > 1200
 group by deptno
having avg(sal) > 1500
 order by avg(sal) desc;
select ename, sal from emp where sal > (select avg(sal) from emp);
select ename, sal
  from emp
  join (select max(sal) max_sal, deptno from emp group by deptno) t
    on (emp.sal = t.max_sal and emp.deptno = t.deptno);
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;
select ename, dname from emp, dept where emp.deptno = dept.deptno;
select ename, dname from emp join dept on (emp.deptno = dept.deptno);
select ename, dname from emp join dept using (deptno);
--
select ename, grade
  from emp e
  join salgrade s
    on (e.sal between s.losal and s.hisal);
--
select ename, dname, grade
  from emp e
  join dept d
    on (e.deptno = d.deptno)
  join salgrade s
    on (e.sal between s.losal and s.hisal)
 where e.ename not like '_A%';
--
select e1.ename, e2.ename
  from emp e1
  left join emp e2
    on e1.mgr = e2.empno;
--
select e.ename, d.dname
  from emp e
 right join dept d
    on e.deptno = d.deptno;
--
select e.ename, d.dname from emp e full join dept d on e.deptno = d.deptno;
--
select deptno, avg_sal, grade
  from (select deptno, avg(sal) avg_sal from emp group by deptno) t
  join salgrade s
    on (t.avg_sal between s.losal and s.hisal);
--
select deptno, avg(grade)
  from (select deptno, grade
          from emp
          join salgrade s
            on (emp.sal between s.losal and s.hisal))
 group by deptno;
--
select ename from emp where empno in (select mgr from emp);
select ename from emp where empno in (select distinct mgr from emp);
--
select distinct sal
  from emp
 where sal not in
       (select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
--
select deptno, avg_sal
  from (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
       (select max(avg_sal)
          from (select avg(sal) avg_s