3/5 sql

select * from emp;
select * from dept;

desc dept;

select empno,ename from emp;

select ename, q'[tom's book]' as col2 from emp;

 

select deptno from emp;
select distinct deptno, ename from emp;

 

select ename,job from emp;

select ename || '_' || job as name_job from emp;

 

select empno,ename,sal from emp
where sal<1000;

select empno,ename from emp
where empno=342423423;

select id from member where id='hkd' and pw='1234';

select ename,hiredate
from emp
where hiredate='80/12/17';

-- 숫자 : 작은따옴표 없음
-- 문자열,날짜 : 작은따옴표로 표시.

select ename,sal*2+100 as sal
from emp;

select * from emp
where sal>=2000 and sal<=3000;

select * from emp
where deptno = 20 or deptno=30;

select * from emp
where deptno in (20,30);

select * from emp
where deptno in (select deptno from dept where loc='BOSTON');

select * from emp where comm is not null;

-- null 과 ''(빈문자열) 은 다르다.
-- ''(빈문자열)을 컬럼에 넣을 수 있다.  ename ='';

-- deptno로 먼저 sort한 후,  depno가 같은 행을 다시 job로 sort함
select * from emp order by deptno asc, job desc,ename desc ;

-- 자동차회사에서 지역별로 판매실적 테이블을 여러개 만든 후
-- 하나로 합쳐서 결과를 출력할 때 union을 사용할 수 있다.

create table seoulSales(
  no int,
  carName varchar(10)
);

insert into seoulSales values(1,'소나타');
insert into seoulSales values(2,'그랜져');
insert into seoulSales values(3,'아반떼');

create table jejuSales(
  no int,
  carName varchar(10)
)

insert into jejuSales values(1,'소나타');
insert into jejuSales values(2,'그랜져');
insert into jejuSales values(3,'아반떼');

select *
from
  (select * from seoulSales
  union all
  select * from jejuSales)
order by carName;

 

 

 

select carname from seoulsales;

select carname from jejusales;

insert into seoulsales values(5,'스타렉스');
insert into seoulsales values(6,'제네시스');

insert into jejusales values(5,'싼타페');
insert into jejusales values(6,'투산');

select carname from seoulsales

 

minus
select carname from jejusales;

select * from emp;

select count(*) 직원수 from emp;
select count(mgr) "상사가 있는 직원 수" from emp;

select count(comm) from emp;

select sum(comm) from emp;

select avg(comm) from emp;-- null 제외

select max(comm) from emp;

select min(comm) from emp;

select * from emp;
select sum(sal) from emp;
select avg(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;

 

select * from emp;

select deptno, job,max(sal) from emp
group by deptno,job
order by 1,2;

select * from emp;

 

select job, sum(sal) as sumSalary
from emp
group by job
having sum(sal)>=5000;

 

select job, sal as sumSalary
from emp
where sal>2500
group by job;

select empno,nvl(comm,0) from emp;

select avg(nvl(comm,0)) from emp;-- null을 0으로 변경.분모가 14
select avg(comm) from emp;-- null을 빼고 분모가 4

select deptno,nvl(job,' '),round(avg(sal),1) avg_sal,count(*) cnt_emp
from emp
group by rollup(deptno,job);

select deptno,nvl(job,' ') job,round(avg(sal),1) avg_sal,count(*) cnt_emp
from emp
group by cube(deptno,job)
order by deptno, job desc;