오라클 데이터베이스
sql 3일차(0712)
피아마수
2023. 7. 12. 16:51
--집계함수 null무시
--부서별 평균 급여를 구하세요
select deptno, avg(sal)
from emp
group by deptno;
--집계함수는 데이터 1건!을 가져오고있기때문에 에러뜨는거
--집계함수 이외의 나머지 컬럼은 group by에 넣어줘야한다.
select deptno, job, avg(sal) --14개 row
from emp
group by deptno,job;
-- 직종별 평균급여를 구하세요
select job, avg(sal)
from emp
group by job;
select job, avg(sal),count(sal)
from emp
group by job;
select job, sum(sal)--group by job 컬럼은 select 절에도 존재
from emp
group by job;
select deptno, avg(sal), sum(sal), max(sal), min(sal), count(sal)
from emp
group by deptno;
/*
group개념을 많이 사용하는 애들
distinct 컬럼명1, 컬럼명2
order by 컬럼명1, 컬럼명2
group by .....
*/
--부서별, 직종별 급여의 합을 구하세요
select deptno, job, sum(sal),count(sal)
from emp
group by deptno , job
order by deptno;
/*
select --5
from --1
where --2
group by --3
having --4
order by --6
단일 테이블을 대상으로 실행되는 쿼리문
*/
--직종별 평균급여가 3000달러이상인 사원의 직종과 평균급여를 구하세요
--having
select job, avg(sal) as avgsal
from emp
--from절의 조건절이 where
group by job
having avg(sal) >=3000; --group by의 조건절이 having
--select이 having보다 더 늦게 컴파일되기때문에 별칭작성이 안됨
/*
사원테이블에서 직종별 급여합을 출력하되 수당은 지급 받고 급여의 합이 5000 이상인
사원들의 목록을 출력하세요 (comm 0인 놈도 받는 것으로 ....)
급여의 합이 낮은 순으로 출력하세요
*/
select job , sum(sal) as sumsal
from emp
where comm is not null
group by job
having sum(sal)>=5000
order by sumsal asc;
/* 사원테이블에서 부서 인원이 4명보다 많은 부서의 부서번호 ,인원수 , 급여의 합을 출력하세요 */
select deptno, count(*) 사원수 , sum(sal) 급여의합
from emp
group by deptno
having count(*)>4;
/* 사원테이블에서 직종별 급여의 합이 5000를 초과하는 직종과 급여의 합을 출력하세요
단 판매직종(salesman) 은 제외하고 급여합으로 내림차순 정렬하세요 */
select job, sum(sal) 급여합
from emp
where job != 'SALESMAN'
group by job
having sum(sal) > 5000
order by 급여합 desc;
--단일 테이블 끝
--------------------------------------------------------------------
create table member2(age number);
insert into member2(age) values(100);
insert into member2(age) values(200);
insert into member2(age) values(300);
insert into member2(age) values(400);
/*
java
@Data
class Member {private int age;} setter, getter --DB의 create table과 같다
-- DB 데이터 1건은 java의 객체 1개
Member memeber = new Member();
member.setAge(100);
다수의 데이터
List<Member> memberList = ArrayList<>();
memberList.add(new Member2(200));
memberList.add(new Member2(300));
memberList.add(new Member2(400));
*/
--컬렉션 , io만 공부해두면 dB 편안
/*
char(10) 고정길이
varchar(10) 가변길이
nchar(10)-한글자를 2byte취급한다. 영문,특수문자상관없이 10글자 이건 20byte이다. 오라클은 한글을 3byte 잡아 먹는다.
nvarchar(10)
*/
--6장 시작 교재는 p214
--------------------------------------------------------------------
--조인(하나 이상의 테이블에서 데이터 가져오기)
--코린이 필수..?
--join의 종류
--Join 방법
--Cartesian Product 모든 가능한 행들의 Join
--Equijoin Join 조건이 정확히 일치하는 경우 사용(일반적으로 PK 와 FK 사용)
--Non-Equijoin Join 조건이 정확히 일치하지 않는 경우에 사용(등급,학점)
--Outer Join Join 조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
--Self Join 하나의 테이블에서 행들을 Join 하고자 할 경우에 사용
--Set Operators 여러 개의 SELECT 문장을 연결하여 작성한다.
--관계형 데이터베이스
--1:1
--1:N : 70%
--N:M
create table M(M1 char(6), M2 char(10));
create table S(S1 char(6), S2 char(10));
create table X(X1 char(6), X2 char(10));
desc m;
desc s;
desc x;
insert into M values('A','1');
insert into M values('B','2');
insert into M values('C','3');
insert into M values(null,'3');
commit;
select *from m;
insert into S values('A','X');
insert into S values('B','Y');
insert into S values(null,'Z');
commit;
select * from s;
insert into x values('A','DATA');
commit;
select * from x;
/*
1. 등가조인(equi join)
원 테이블과 대응되는 테이블에 있는 컬럼의 데이터 1:1 매핑
1. SQL JOIN 문법 (간단) 벤더마다 가지는 고유한 문법
2. ANSI 문법 권장 >> 표준 >> 학습 [inner] join on 조건절
*/
select *
from m, s
where m.m1 = s.s1;
--null은 비교대상이 아니다.
--union은 위아래로 join은 양옆으로 붙인다.
--CRUD
--emp 테이블에서 deptno를 사용하는이유
--update 번호로 안하고 이름으로 놓으면 계속 변경해야함 dname의 변화에도 변경해줘야할것이 적기때문
--건수가 작다면select속도를 빠르게 하기 위해서 deptno대신 dname을 쓰는 게 낫다
--정규화이야기네
--Ansi문법
select *
from m inner join s
on m.m1 = s.s1;
--ANSI
select *
from s join x
on s.s1 = x.x1;
--사원번호, 사원이름, 부서번호, 부서이름을 출력하세요
select e.empno ,e.ename, d.deptno,d.dname
from emp e join dept d on e.deptno = d.deptno;
--테이블에 엘리어스 사용한다.
-------------------------------------------------------------
select *
from m, s, x
where m.m1 = s.s1 and s.s1 = x.x1;
select *
from m join s on m.m1 = s.s1
join x on s.s1 =x.x1;
--from 절에는 as로 별칭 불가능
select * from emp;
select * from salgrade;
--하나의 컬럼으로 매핑(등가)
--비등가 조인(non-equi) 1:1 매핑 컬럼이 없구나
--문법적으로 등가와 동일
select *
from emp e join salgrade s
on e.sal between s.losal and s.hisal;
--조인의 종류를 이야기 하시오
--객체지향의 특징을 말해보시오
--outer join(equi 조인이 선행되고 나서....남아있는 데이터를 가져오는 방법)
--1.주종관계파악(주인이 되는 쪽의 데이터를 가져온다.
--1.2 left outer join
--1.3 right outer join
--1.4 full outer join(left,right >> union)
select *
from m left outer join s
on m.m1 = s.s1;
select *
from m right outer join s
on m.m1 =s.s1;
--self join(자기참조) 문법(X) -> 등가 -> 의미만 존재
--하나의 테이블에 있는 컬럼이 자신의 테이블에 있는 특정 컬럼을 참조하는 경우
select * from emp;
--하나의 테이블을 여러개 있는 것 처럼
select e.empno,e.ename,m.empno,m.ename
from emp e left outer join emp m
on e.mgr = m.empno;
select count(*) from emp where mgr is null;
--같은 이름 컬럼으로 조인
select empno,ename deptno from emp natural join dept;
--같은 이름 컬럼이 많을 경우 지정하기
select e.emptno, e.ename, deptno from emp e join dept d using(deptno);
--실수하지말자(조건이 없는 조인)
select *from emp, dept; -- where emp. = dept.
select * from emp cross join dept;
--데카르트 곱의 원리(행*행)
--이상하게 너무 많은 양의 튜플이 나오면 조건을 안썻나 확인
-- 1. 사원들의 이름, 부서번호, 부서이름을 출력하라.
select ename, deptno, dname
from emp join dept using(deptno);
-- 2. DALLAS에서 근무하는 사원의 이름, 직위, 부서번호, 부서이름을
-- 출력하라.
select ename, job, emp.deptno,dname
from emp join dept on emp.deptno = dept.deptno
where loc='DALLAS';
-- 3. 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하라.
select ename, dname
from emp join dept using(deptno)
where ename like '%A%';
-- 4. 사원이름과 그 사원이 속한 부서의 부서명, 그리고 월급을
--출력하는데 월급이 3000이상인 사원을 출력하라.
select ename, dname, sal
from emp join dept using(deptno)
where sal >= 3000;
-- 5. 직위(직종)가 'SALESMAN'인 사원들의 직위와 그 사원이름, 그리고
-- 그 사원이 속한 부서 이름을 출력하라.
select job, ename, dname
from emp join dept on emp.deptno = dept.deptno
where job='SALESMAN';
-- 6. 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션,
-- 급여등급을 출력하되, 각각의 컬럼명을 '사원번호', '사원이름',
-- '연봉','실급여', '급여등급'으로 하여 출력하라.
--(비등가 ) 1 : 1 매핑 대는 컬럼이 없다
select empno 사원번호, ename 사원이름, sal*12 연봉, sal*12+comm 실급여, grade 급여등급
from emp join salgrade on sal between losal and hisal
where comm is not null;
--where을 먼저 컴파일하고 select절을 수행하기 때문에 nvl함수를 사용하지 않아도 된다.
select * from salgrade;
-- 7. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라.
select dept.deptno, dname, ename, sal, grade
from emp join dept on emp.deptno= dept.deptno join salgrade on sal between losal and hisal
where dept.deptno=10;
-- 8. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름,
-- 사원이름, 월급, 급여등급을 출력하라. 그리고 그 출력된
-- 결과물을 부서번호가 낮은 순으로, 월급이 높은 순으로
-- 정렬하라.
select dept.deptno, dname, ename, sal,grade
from emp join dept on emp.deptno = dept.deptno join salgrade on sal between losal and hisal
where dept.deptno in (10,20)
order by dept.deptno asc, sal desc;
-- 9. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의
-- 사원번호와 사원이름을 출력하되 각각의 컬럼명을 '사원번호',
-- '사원이름', '관리자번호', '관리자이름'으로 하여 출력하라.
--SELF JOIN (자기 자신테이블의 컬럼을 참조 하는 경우)
select e.empno 사원이름, e.ename 사원이름, m.empno 관리자번호,m.ename 관리자이름
from emp e left outer join emp m on e.mgr = m.empno;
--오라클.pdf (99page) 교재 (232page)
-----------------------------------------------------------------
--subquery(sql 꽃)(만능해결사)
--사원테이블에서 사원들의 평균 월급보다 더 많은 월급을 받는 사원의 사번, 이름,급여
--를 출력하세요
select empno, ename, sal
from emp
where sal>2073;
--2개의 쿼리 하나 통합 (하나의 쿼리)
--서브 쿼리
select empno, ename, sal
from emp
where sal>(select avg(sal) from emp);
/*
원칙
1. 함수 > 단일테이블 > 다중테이블(join, union먼저) > 해결안됨 > subquery
2. 문법
2.1 single row subquery :실행 결과가 단일 컬럼에 단일 로우인 경우(한개의 값)
ex) select sum(sal) from emp , select max(sal) from emp
연산자 : =, !=, <, >
2.2 multi row subquery : 실행결과가 단일 컬럼에 여러개의 로우인 경우
ex) select deptno from emp, select sal from emp where deptno=10
연산자 : in, not in, any(어떤것이 와도 상관 없다), ALL()
ALL : sal> 1000 and sal > 4000 and...
ANY: sal>1000 or sal >4000 or ....
3.
3.1 괄호 안에 있어야한다 ex) (select sum(sal)from emp)
3.2 단일 컬럼으로 구성 (select sum(sal), avg(sal) from emp)(X)
3.3 서브 쿼리가 단독으로 실행 가능
서브 쿼리와 메인 쿼리가 있는 경우
1. 서브쿼리 선행되고 그 결과를 가지고
2. 메인쿼리 실행
TIP)
select (select ...scala)
from (select ... in line view)
where (select ...) 조건
*/
--사원테이블에서 jones의 급여보다 더 많은 급여를 받는 사원의 사번, 이름, 급여를 출력하세요
select sal from emp where ename='JONES';
select * from emp where sal > (select sal from emp where ename='JONES');
--부서번호가 30번인 사원과 같은 급여를 받는모든 사원의 정보를 출력하세요
select *
from emp
where sal in(select sal from emp where deptno=30);
--부서번호가 30번인 사원과 다른 급여를 받는모든 사원의 정보를 출력하세요
select *
from emp
where sal not in(select sal from emp where deptno=30);
--where sal !=1600 and sal !=1250 and ...;
--부하 직원이 잇는 사원의 사번과 이름을 출력하세요
select empno, ename
from emp
where empno in(select mgr from emp);
--부하 직원이 없는 사원의 사번과 이름을 출력하세요
--and조건에서 null이 있으면 검색이 안된다.
select empno, ename
from emp
where empno not in(select mgr from emp where mgr is not null);
--not in --> and 조건절
--nvl(mgr,0)/*
HR 계정으로 이동하세요
show user
1. EMPLOYEES 테이블을 이용하여 다음 조건에 만족하는 행을 검색하세요.
2005년이후에 입사한 사원 중에 부서번호가 있고, 급여가 5000~10000 사이인 사원을 검색합니다.
가) 테이블 : employees
나) 검색 : employee_id, last_name, hire_date, job_id, salary, department_id
다) 조건
① 2005년 1월 1일 이후 입사한 사원
② 부서번호가 NULL이 아닌 사원
③ 급여가 5,000보다 크거나 같고, 10,000 보다 작거나 같은 사원
④ 위의 조건을 모두 만족하는 행을 검색
라) 정렬: department_id 오름차순, salary 내림차순
*/
select * from employees;
select employee_id, last_name, hire_date, job_id, salary, department_id
from employees
where hire_date > '2005-01-01' and department_id is not null and salary between 5000 and 10000
order by department_id,salary desc;
/*
2. EMPLOYEES 테이블을 이용하여 다음 조건에 만족하는 행을 검색하세요.
부서번호가 있고, 부서별 근무 인원수가 2명 이상인 행을 검색하여 부서별 최대 급여와 최소 급여를 계산하
고 그 차이를 검색합니다.
가) 테이블 : employees
나) 검색 : department_id, MAX(salary), MIN(salary), difference
- MAX(salary) 와 MIN(salary)의 차이를 DIFFERENCE로 검색
다) 조건
① 부서번호가 NULL이 아닌 사원
② 부서별 근무 인원수가 2명 이상인 집합
라) 그룹 : 부서번호가 같은 행
마) 정렬 : department_id
*/
select department_id, MAX(salary), MIN(salary), MAX(salary)-MIN(salary) difference
from employees
where department_id is not null
group by department_id
having count(*)>=2
order by department_id;
-------------------------------------------------------------------
select * from employees;
select *from departments;
--사번, 이름(last_name), 부서번호, 부서이름을출력하세요
select e.employee_id, e.last_name, d.department_id, d.department_name
from employees e left outer join departments d on e.department_id = d.department_id;
--현업 .. outer join
--temp1, temp2는 백업파일
--from 절에는 as로 별칭 불가능
--null값이 빠져있다.
select * from employees where department_id is null;
--null은 조인 조건에 해당되지 않아서
--outer join을 해야한다.
--사번, 이름(last_name) , 부서번호, 부서이름, 지역코드, 도시명을 출력하세요
select * from employees;
select * from departments;
select * from locations;
select employee_id, last_name, d.department_id, department_name,l.location_id,city
from employees e join departments d on e.department_id=d.department_id join locations l on d.location_id=l.location_id;