728x90
반응형
/*
group 함수
레코드들 기준컬럼(전체)으로 그룹화 하여 원하는 결과 구하는 함수들
having 그룹함수 조건문
null 값은 제외됨.
count : 레코드의 건수 리턴, null 값은 제외
count(컬럼명) : 해당 컬럼의 값이 null아닌 갯수
count(*) : 전체 레코드의 갯수
sum : 컬럼값들의 합계 리턴
sum(숫자형컬럼명)
avg : 컬럼값들의 평균 리턴. null값인 경우는 평균산출시 제외. null이 아닌 값의 평균.
avg(숫자형컬럼명), avg(nvl(컬럼명,0))=> null인 경우도 평균산출시 처리됨
max : 컬럼값들 중 최대값 리턴.
min : 컬럼값들 중 최소값 리턴
stddev : 컬럼값들의 표준편차 값 리턴
variance : 컬럼값들의 분산 값 리턴
*/
/*
join
cross join : 사용시 주의를 요함. 결과가 m*n개의 갯수로 리턴.
Equi Join : 조인컬럼의 조건이 =인 조인을 등가조인이라함.
Non Equi Join : 조인컬럼의 조건이 =가 아닌 조인을 비등가조인이라함.
inner join : 기본. 조인컬럼의 조건을 만족하는 레코드만 조회.
outer join : 조인컬럼의 조건을 만족하지 않아도 한개/두개 테이블의 내용을 모두 조회가능한 조인
left outer join : 왼쪽 테이블의 내용을 모두 조회.
오라클방식: 조인컬럼의 오른쪽에 (+) 표시
ANSI방식 : left outer join
right outer join : 오른쪽 테이블의 내용을 모두 조회.
오라클방식: 조인컬럼의 왼쪽에 (+) 표시
ANSI방식 : right outer join
full outer join : 양쪽 테이블의 내용을 모두 조회.
오라클방식: left outer join, right outer join을 union 방식으로 구현
ANSI방식 : full outer join
self join
조인되는 테이블이 같은 경우를 self join 이라함.
테이블명에 반드시 별명을 지정해야 함.
조회되는 모든 컬럼도 테이블의 별명을 붙여야 함.
*/
/*
SUBQUERY
SELECT 구문 내부에 SELECT 구문이 존재.
단일행 서브쿼리 : 서브쿼리의 결과가 1개인 경우.
사용가능 연산자 : =, >, < ...
다중행 서브쿼리 : 서브쿼리의 결과가 여러개인 경우.
사용가능 연산자 : in
*/
-- EMP 테이블에서 'SCOTT'사원의 급여보다 급여가 많은 사람의 이름과 급여 출력하기
SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT'
;
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 3000
;
SELECT ENAME, SAL
FROM EMP
WHERE SAL >
(SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT') --단일행 서브쿼리
;
-- 홍길동 학생과 같은 학년의 이름, 학년, 학과번호를 출력하기
SELECT GRADE
FROM STUDENT
WHERE NAME = '홍길동'
;
SELECT NAME, GRADE, DEPTNO1
FROM STUDENT
WHERE GRADE = 4
;
SELECT NAME, GRADE, DEPTNO1
FROM STUDENT
WHERE GRADE =
(SELECT GRADE
FROM STUDENT
WHERE NAME = '홍길동') --단일행 서브쿼리
;
-- 김진욱, 홍길동 학생과 같은 학년의 이름, 학년, 학과번호 출력하기
SELECT NAME, GRADE, DEPTNO1
FROM STUDENT
WHERE GRADE IN
(SELECT GRADE
FROM STUDENT
WHERE NAME IN ('홍길동','김진욱')) --다중행 서브쿼리
;
-- EMP2, DEPT2 테이블에서 근무지역이 서울 지사인 모든 사원의 사원번호, 이름, 부서번호, 부서명 출력하기
SELECT *
FROM DEPT2
;
-- 1. 근무지역이 서울 지사인 부서코드 출력하기
SELECT DCODE
FROM DEPT2
WHERE AREA = '서울지사'
;
-- 2. 근무지역이 서울 지사인 모든 사원의 사원번호, 이름, 부서번호, 부서명 출력하기
SELECT E.EMPNO, E.NAME, E.DEPTNO, D.DNAME
FROM EMP2 E, DEPT2 D
WHERE E.DEPTNO = D.DCODE
AND E.DEPTNO IN (1000,1001,1002,1010)
;
-- 1+2
SELECT E.EMPNO, E.NAME, E.DEPTNO, D.DNAME
FROM EMP2 E, DEPT2 D
WHERE E.DEPTNO = D.DCODE
AND E.DEPTNO IN
(SELECT DCODE
FROM DEPT2
WHERE AREA = '서울지사')
;
-- 문제 1 : EMP2 테이블에서 전체 직원 중 과장 직급의 최소연봉자보다 연봉이 높은 사람의 이름, 직급, 연봉(PAY)을 출력하기
-- 1. 과장 직급의 최소 연봉자
SELECT MIN(PAY)
FROM EMP2
WHERE POSITION = '과장'
;
-- 2. 최소연봉자보다 연봉이 높은 사람의 이름, 직급, 연봉(PAY)을 출력하기
SELECT NAME, POSITION, PAY
FROM EMP2
WHERE PAY > 4900000
;
-- 1+2
SELECT NAME, POSITION, PAY
FROM EMP2
WHERE PAY >
(SELECT MIN(PAY)
FROM EMP2
WHERE POSITION = '과장')
;
/*
DML : Data Manipulation Language
INSERT : 테이블에 새로운 레코드 추가 명령어
UPDATE : 테이블에 기존 레코드 변경 명령어
DELETE : 테이블에 기존 레코드 제거 명령어
DDL : Data Definitation Language
CREATE : 객채(테이블, 인덱스, 사용자..)를 생성 명령어
ALTER : 객채(테이블, 인덱스, 사용자..)를 변경 명령어
DROP : 객채(테이블, 인덱스, 사용자..)를 제거 명령어
TCL : Transaction Control Language
COMMIT : 물리적으로 DML 명령 실행 완료
ROLLBACK : 물리적으로 DML 명령 실행 취소
*/
SELECT *
FROM DDL_TEST
;
-- DDL_TEST 테이블 생성하기
CREATE TABLE DDL_TEST
(
NO NUMBER(3),
NAME VARCHAR2(10),
BIRTH DATE DEFAULT SYSDATE
)
;
-- DDL_TEST 테이블에 레코드 추가하기
INSERT INTO DDL_TEST (NO, NAME, BIRTH)
VALUES (1, '홍길동', '')
;
/*
INSERT : 레코드 추가
INSERT INTO 테이블명 [(컬럼명1, 컬럼명2,...)] VALUES (값1, 값2,...)
컬럼명 생략시 데이터베이스에 정의된 스키마(데이터정의부분) 순서대로 임. => 권장하지 않음.
-- 반드시 컬럼명을 기술해야 하는경우
1. 모든 컬럼에 값을 입력하지 않는 경우
2. 스키마 순서를 모를 때
3. DB구조의 변경이 자주 될 때. 컬럼명을 기술하는 것이 안전함.
*/
INSERT INTO DDL_TEST
VALUES (2, '김삿갓', '')
;
INSERT INTO DDL_TEST (NO, NAME)
VALUES (3, '이몽룡')
;
INSERT INTO DDL_TEST (NAME, NO)
VALUES ('춘향이', 4)
;
SELECT *
FROM DDL_TEST
;
-- 문제 2 : DEPT2 테이블에 9000번 부서를 추가하기
-- 코드:9000, 부서명:특판매팀, 상위부서:1000, 지역:NULL
INSERT INTO DEPT2 (DCODE, DNAME, PDEPT, AREA)
VALUES(9000, '특판매팀', 1000)
;
SELECT *
FROM DEPT2
;
-- DEPT2 테이블에 9001번 부서를 추가하기
-- 코드:9001, 부서명:특수판매1팀, 상위부서:9000, 지역:NULL
-- 단, 컬럼명 생략
INSERT INTO DEPT2
VALUES(9001, '특수판매1팀', 9000, '')
;
SELECT *
FROM DEPT2
WHERE DCODE >= 9000
;
-- DEPT2 테이블에 9002번 부서를 추가하기
-- 코드:9002, 부서명:특수판매2팀, 상위부서:9000, 지역:NULL
-- 단, 컬럼명 생략
INSERT INTO DEPT2
VALUES(9002, '특수판매2팀', 9000, NULL)
;
SELECT *
FROM DEPT2
WHERE DCODE >= 9000
;
/*
NULL : 값이 없음
-- NULL 값 입력방법
1. 컬럼을 기술하지 않기. 컬럼부분 생략 불가
2. null, NULL 직접입력
3. ''(빈문자열)로 입력
*/
/*
UPDATE : 기존 레코드의 값을 수정 명령어
UPDATE 테이블명 SET 컬럼명1 = 값1, 컬럼명2 = 값2, ...
[WHERE 조건문] => 레코드 선택 조건.
=> WHERE 조건문이 구현되지 않으며 모든 레코드의 값이 수정됨.
*/
--교수테이블에서 직급이 조교수인 교수의 보너스를 99로 수정하기
SELECT *
FROM PROFESSOR
WHERE POSITION = '조교수'
;
UPDATE PROFESSOR
SET BONUS = 99
WHERE POSITION = '조교수'
;
COMMIT
;
-- DEPT2 테이블에 9003번 부서를 추가하기
-- 코드:9003, 부서명:특수판매3팀, 상위부서:9000, 지역:NULL
-- 단, 컬럼명 생략
INSERT INTO DEPT2
VALUES('9003', '특수판매3팀', '9000', NULL)
;
SELECT *
FROM DEPT2
WHERE DCODE >= 9000
;
ROLLBACK
;
/*
COMMIT : 변경내용을 확정
ROLLBACK : 변경내용을 취소
*/
-- 문제 3 : 장혜진 교수와 같은 직급의 교수 중 급여가 230 이하인 교수의 급여만 15% 인상하기
-- 장혜진 교수와 같은 직급의 교수의 이름, 급여, 인상예상급여 출력하기
SELECT *
FROM PROFESSOR
;
SELECT POSITION
FROM PROFESSOR
WHERE NAME = '장혜진'
;
SELECT NAME, PAY, PAY*1.15
FROM PROFESSOR
WHERE POSITION =
(SELECT POSITION
FROM PROFESSOR
WHERE NAME = '장혜진')
AND PAY <= 300
;
UPDATE PROFESSOR
SET PAY = PAY * 1.15
WHERE POSITION =
(SELECT POSITION
FROM PROFESSOR
WHERE NAME = '장혜진')
AND PAY <= 300
;
ROLLBACK
;
-- 문제 4 : 전임강사이면서 보너스가 없는 교수의 보너스를 조교수의 평균 보너스의 50%로 변경하기.
-- 조교수의 보너스가 없는 경우는 0으로 계산하기. 소숫점이하는 절삭하여 보너스로 변경하기.
-- 1. 전임강사이면서 보너스가 없는 교수
SELECT NAME, POSITION
FROM PROFESSOR
WHERE POSITION = '전임강사'
AND BONUS IS NULL
;
-- 2. 조교수의 평균 보너스의 50%
SELECT AVG(NVL(BONUS,0)) / 2
FROM PROFESSOR
WHERE POSITION = '조교수'
;
--UPDATE
UPDATE PROFESSOR SET BONUS = (SELECT AVG(NVL(BONUS,0)) / 2
FROM PROFESSOR
WHERE POSITION = '조교수')
WHERE POSITION = '전임강사'
AND BONUS IS NULL
;
SELECT NAME, POSITION, BONUS
FROM PROFESSOR
WHERE POSITION = '전임강사'
;
ROLLBACK
;
/*
DELETE : 조건에 맞는 레코드 제거
DELETE FROM 테이블 명
[WHERE 조건문] => 삭제될 레코드의 조건.
=> WHERE 조건문이 없는 경우 모든 레코드가 삭제됨. 주의 요함.
*/
-- DEPT2 테이블에서 부서코드가 9000번대인 부서의 정보 삭제하기
SELECT *
FROM DEPT2
WHERE DCODE BETWEEN 9000 AND 9999
;
DELETE FROM DEPT2
WHERE DCODE BETWEEN 9000 AND 9999
;
ROLLBACK
;
-- 심슨 교수와 같은 부서의 교수를 퇴직시키기
SELECT DEPTNO
FROM PROFESSOR
WHERE NAME = '심슨'
;
DELETE FROM PROFESSOR
WHERE DEPTNO = (SELECT DEPTNO
FROM PROFESSOR
WHERE NAME = '심슨')
;
ROLLBACK
;
/*
DML : INSERT, UPDATE, DELETE, SELECT
CRUD : 데이터 처리의 기본 기능
(C)reate : 데이터 추가. INSERT
(R)ead : 데이터 조회. SELECT
(U)pdate : 데이터 수정. UPDATE
(D)elete : 데이터 삭제. DELETE
Transaction 처리 가능 : COMMIT, ROLLBACK 가능
DDL : Transaction 처리 불가능 : AUTO COMMIT 됨, ROLLBACK 불가능
*/
SELECT *
FROM DDL_TEST
;
-- DDL_TEST 테이블을 복사하여 DDL_TEST2 테이블 생성하기
CREATE TABLE DDL_TEST2
AS SELECT * FROM DDL_TEST
;
SELECT *
FROM DDL_TEST3
;
-- DEPT2 테이블과 똑같은 테이블 DEPT3를 생성하기
CREATE TABLE DEPT3
AS SELECT * FROM DEPT2
;
SELECT *
FROM DEPT3
;
/*
CREATE TABLE : 테이블 생성
CREATE USER : 사용자 생성
-- 새로운 테이블 생성
CREATE TABLE 테이블이름
(
컬럼명1 자료형1 [제약조건(기본키, 기본값, NOT NULL ...)]
컬럼명2 자료형2 [제약조건(기본키, 기본값, NOT NULL ...)]
...
)
--기존테이블을 이용한 새로운 테이블 생성
CREATE TABLE 테이블이름
AS SELECT 구문
*/
-- DROP : 테이블 제거하기
SELECT *
FROM DDL_TEST
;
DROP TABLE DDL_TEST
;
-- DEPT3 : 테이블 제거하기
SELECT *
FROM DEPT3
;
DROP TABLE DEPT3
; -- 복구 방법이 없다.
ROLLBACK
;
/*
Data Dictionary : 오라클에서 제공해주는 시스템 데이터
USER_XXX 형태 테이블 조회하기
*/
-- 생성된 테이블 목록 조회하기
SELECT TABLE_NAME
FROM USER_TABLES
;
DESC USER_TABLES
;
DESC USER_INDEXES
;
CREATE TABLE DEPT3
AS SELECT * FROM DEPT2
;
INSERT INTO DEPT3 (DCODE, DNAME)
SELECT DCODE, DNAME
FROM DEPT2
WHERE DCODE = 0001
;
SELECT *
FROM DEPT3
ORDER BY DCODE
;728x90
반응형