-- ex) 급여 평균값 이상의 급여를 받는 사원을 검색하시오
SELECT AVG(SALARY) FROM EMPLOYEE;
SELECT * FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE);
-- ex) 전지연 직원의 관리자 이름을 출력하여라.
SELECT MANAGER_ID FROM EMPLOYEE
WHERE EMP_NAME = '전지연';
SELECT EMP_NAME FROM EMPLOYEE
WHERE EMP_ID = (SELECT MANAGER_ID FROM EMPLOYEE WHERE EMP_NAME = '전지연');--@실습문제
--1. 윤은해와 급여가 같은 사원들을 검색해서, 사원번호,사원이름, 급여를 출력하라.
select emp_name, emp_id, salary from employee
where salary = (select salary from employee where emp_name = '윤은해') and emp_name != '윤은해';
--2. employee 테이블에서 기본급여가 제일 많은 사람과 제일 적은 사람의 정보를
-- 사번, 사원명, 기본급여를 나타내세요.
select emp_id, emp_name, salary from employee
where salary = (select max(salary) from employee) or salary =(select min(salary) from employee);
--3. D1, D2부서에 근무하는 사원들 중에서
--기본급여가 D5 부서 직원들의 '평균월급' 보다 많은 사람들만
--부서번호, 사원번호, 사원명, 월급을 나타내세요.
select avg(salary) from employee
where dept_code = 'D5';
select dept_code, emp_id, emp_name, salary
from employee
where salary > (select avg(salary) from employee where dept_code = 'D5')
and dept_code in ('D1','D2');
--dept_code = 'D1' or dept_code = 'D2'where 문에 () 안에 selcet 문을 다시 넣어서 맞는 조건문을 넣는것
다중행 서브쿼리
- 서브쿼리의 조회 결과 값이 여러개 일때
- 다중행 서브쿼리 앞에는 일반 비교연산자 사용불가
- 사용가능 연산자
- in/not in
- any
- all
- exists
select dept_code from employee
where emp_name in ('송종기', '박나라');
select * from employee
where dept_code in (select dept_code from employee where emp_name in ('송종기','박나라'));- 송종기나 박나라와 같은 dept_code를 가진 사람을 찾는 쿼리문이다.
- in을 이용해 2가지 조건을 충족시킨다, 다중 쿼리문 안쪽도 in을 이용한 2가지 조건이 나와야 한다
--
select * from employee
where dept_code not in (select dept_code from employee where emp_name in ('송종기' , '박나라'));- not in을 하면 반대로 송종기나 박나라와 dept_code가 일치하지 않는 사람이 나오게 된다.
서브쿼리 위치는where 뿐만이 아니라 from, select 뒤에도올수있다.
상관쿼리는 select뒤에 온다 상관쿼리의 결과 값이 1개인것을 스칼라 쿼리라고 한다
from 뒤에 오는것은 인라인 뷰
상관 서브쿼리
- 메인 쿼리의 값을 서브쿼리에 주고 서브쿼리르 수령한 다음 그 결과를 다시 메인쿼리로 반환해서 수행하는 쿼리 (일종의 루프문)
- 서브쿼리의 where 수행을 위해서는 메인쿼리가 먼저 수행되는 구조
- 메인쿼리 테이블의 레코드(행)에 서브쿼리의 결과값도 바뀜
- 메인 쿼리에서 처리되는 각 행의 컬럼값에 따라 흥답이 달라져야 하는 경우에 유용
EXISTS
- 서브쿼리의 결과 중에서 만족하는 행이 하나라도 존재하면 참
select * from employee
where 1 = 1;
select * from employee
where exists (select 1 from employee where dept_code = 'D5')- 위 아래는 같은 쿼리문이다.
- 서브쿼리안의 select는 상관없다 그 안에where문 만이 중요하다
select emp_name, dept_code
from employee e
where exists (select 1 from department where dept_id = e.dept_code);
--> where dept_code is null서브 쿼리를 사용했지만 dept_code is null과 같은 조건문이라고 할수있다.
--3. 직급이 J1, J2, J3이 아닌 사원중에서 자신의 부서별 평균급여보다 많은 급여를 받는 사원출력.
-- 부서코드, 사원명, 급여, 부서별 급여평균
select e.dept_code, emp_name, salary
from employee e join (select dept_code, round(avg(salary))"평균" from employee
group by dept_code)a
on e.dept_code = a.dept_code
where job_code not in ('J1','J2','J3') and salary > "평균";
select dept_code, emp_name, salary, (select round(avg(salary)) from employee where dept_code = e.dept_code) "평균"
from employee e
where job_code not in('J1','J2','J3') and salary > (select round(avg(salary)) from employee where dept_code = e.dept_code);스칼라 서브쿼리
- 단 한개의 값만 출력되는 서브쿼리
- select문, 칼럼절에 주로 사용됨
- where절 order by 절에도 사용가능
select e.emp_id, e.emp_name, e.manager_id,
nvl((select emp_name from employee m where e.manager_id = m.emp_id),'없음') "관리자명"
from employee e;인라인 뷰
- from절에서의 서브쿼리
select * from
(select emp_name, emp_no, decode(substr(emp_no,8,1),'1','남',3,'남','여') "성별"
from employee)
where "성별" = '남';- from절에 있기에 “성별”을 컬럼으로 인식한다
rownum
-- rownum을 이용하여 랭킹 5 구하기
select rownum, emp_name, salary
from (select emp_name, salary from employee order by salary )
where rownum<6;서브쿼리를 이용한 인서트
- 표를 만들때 insert안에 다른 표 값을 넣어 채운다
create table emp_01(
emp_id number,
emp_name varchar2(30),
dept_title varchar2(20)
);
select * from emp_01;
insert into emp_01(
select emp_id, emp_name, dept_title from employee
left join DEPARTMENT on dept_code = dept_id);- 테이블 복사 : 컬럼의 갯수, 컬럼명, 컬럼의 타입… 모두 복사
- 스키마 확인 (표의 유형과 컬럼)
desc employee;
--------출력결과-------
이름 널? 유형
---------- -------- ------------
EMP_ID NOT NULL VARCHAR2(3)
EMP_NAME NOT NULL VARCHAR2(20)
EMP_NO NOT NULL CHAR(14)
EMAIL VARCHAR2(25)
PHONE VARCHAR2(12)
DEPT_CODE CHAR(2)
JOB_CODE NOT NULL CHAR(2)
SAL_LEVEL NOT NULL CHAR(2)
SALARY NUMBER
BONUS NUMBER
MANAGER_ID VARCHAR2(3)
HIRE_DATE DATE
ENT_DATE DATE
ENT_YN CHAR(1)
- 표 유형(스키마)를 그대로 복사하는 법
create table employee_copy
as select emp_id,emp_name,dept_code,hire_date
from employee
where 1=0; --거짓 이 값을 참으로쓰면 내용까지 복사된다TCL
- commit
- rollback
savepoint
- savepont 변수명
- 임시저장하는 포인트
- commit을 하지 않으면 최종저장 되지 않음
- rollbakc을 이용해서 세이브 포인트로 지정한 시점으로 갈수있따
- rollbakc 세이브포인트이름
- rollbakc : commit전으로 돌아감
제약조건
create table user_notnull(
user_no number not null,
user_id varchar2(20) not null,
user_pwd varchar2(30) not null,
user_name varchar2(30),
gender varchar2(10),
phone varchar(30),
email varchar(50));
insert into user_notnull
VALUES(NULL, 'khuser01', 'pass01', '일용자', '남', '01082728333', 'khuser01@iei.or.kr');
CREATE TABLE USER_UNIQUE(
USER_NO NUMBER,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
insert into user_unique values('220','khuser02','pass02','이용자','여','01062923939','khuser02@iei.or.kr');- 제약조건을 컬럼 레벨에서 설정함
CREATE TABLE USER_UNIQUE( USER_NO NUMBER, USER_ID VARCHAR2(20), -- 컬럼 레벨
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
UNIQUE(USER_ID) -- 테이블 레벨);- 제약조건(_컬럼이름) - 이런식으로도 제약조건을 걸수있다.
-
이 경우 테이블 레벨에서 제약조건을 걸었다고 할수있다
체크 제약조건
CREATE TABLE USER_CHECK( USER_NO NUMBER PRIMARY KEY, USER_ID VARCHAR2(20) UNIQUE, USER_PWD VARCHAR2(30) NOT NULL, USER_NAME VARCHAR2(30), GENDER VARCHAR2(10) CHECK (GENDER IN ('남', '여')), PHONE VARCHAR2(30), EMAIL VARCHAR2(50), GRADE_CODE NUMBER ); -
젠더에서 ‘남’,’여’외엔 입력할수 없도록 체크되어있다.
-
hello world