DAY23
📝 지난시간
1) 오라클의 기본 쿼리 구문
- 별칭, || (연결문자열) : Value에 DATA의 연결을 유도하는 문자열 키워드 , 산술연산 컬럼형식 (DATA), NULL 처리 (내장함수 NVL)
Select 컬럼리스트 [*] AS 별칭 // 컬럼 별칭 // 컬럼 AS별칭 // 컬럼 AS "별 칭" // 컬럼 "별 칭" -> 공백이 있을 때 ""만 주면됨
별칭은 컬럼리스트에 줄 수 있지만 테이블명에도 가능
2) SYS -> SYSTEM -> SYSDBA
- SYS , SYSTEM : 프로그램을 설치할 때 권한을 가지고 설치하는 상위계정
- SYSDBA : 데이터베이스를 시스템을 통해서 액세스할 때 발생/ 암호가 없어도 계정생성 가능, 계정이 lock이 걸렸을 때 접근할 수 있는 관리자의 ADMIN 계정
3) .SQL(SQL쿼리문을 담는 파일) , .DBF(저장소)
SQL > @".SQL" -> 쿼리실행
4) 테이블의 구조 확인
DESC 테이블명;
5) SHOW USER; -> 현재 접속한 계정 확인
6) SQL> CONNECT 계정명 / 비밀번호 -> 다른계정으로 접속
// USER_NAME은 대소문자 구분 X, 비밀번호는 대소문자 구분
7) 현재계정으로 생성된 테이블 확인
SQL > SELECT * FROM TAB; //와일드카드 *-
주석 : --주석이야 (단일 주석) , /* */(다중주석) , Rem
-
SET 명령
Q1) 생성된 모든 테이블의 내용을 확인해보자
Q2) 사원테이블의 구조를 확인해보자
DESC EMP;
Q3) 사원테이블의 사원의 이름, 사원의 봉급을 봉급으로 별칭을 주고 출력하자
SELECT ENAME 이름 , SAL 봉급
FROM EMP;
Q4) 사원의 테이블과 부서테이블의 내용을 출력하자 //TABLE = Entity
SELECT *
FROM EMP , DEPT; // 풀조인
Q5) 사원의 이름과 부서번호, 부서명을 출력해보자
SELECT ENAME , DEPTNO , DNAME
FROM EMP , DEPT; ---------------> 오류 ORA-00918;
SELECT E.ENAME,E.DEPTNO,D.DNAME
// 별칭을 주지 않았을 때는 테이블명으로 찾아가도 되지만 별칭을 주었을 때는 별칭으로 찾아가야함
FROM EMP E, DEPT D ; //Entity
SELECT E.ENAME,E.DEPTNO,DEPT.DNAME
FROM EMP E, DEPT ; //별칭을 하나만 줘도 됨
SELECT E.ENAME,E.DEPTNO,DEPT.DNAME AS 부서이름 // 컬럼에도 별칭 가능
FROM EMP E, DEPT ; //별칭을 하나만 줘도 됨
- 번외편
Q6) USER_000 형식의 테이블이 있다 // C:\oraclexe\app\oracle\oradata\XE\USERS.DBF
생성된 테이블을 사전테이블에서 확인할 수 있다. USER_TABLES 구조를 살펴보자.
DESC USER_TABLES;
Q7) USER_TABES의 테이블 이름만 출력해보자.
SQL> SELECT TABLE_NAME FROM USER_TABLES;
Q8) USER_TABLES의 테이블의 내용을 보고 테이블의 이름, 상태 , 줄번호를 출력해보자.
SQL> SELECT TABLE_NAME,STATUS,NUM_ROWS FROM USER_TABLES;
Q9) ROWID, ROWNUM -> 테이블 생성 후 데이터를 ROW단위로 입력 할 때 자동으로 생성되는 인덱싱되는 컬럼의 속성값
SQL> SELECT ROWID, ROWNUM FROM EMP;
Q10) 테이블마다 데이터 무결성(데이터 보전)에 해당하는 제약조건이 테이블생성시 추가 되게 된다.
- 누군가가 SQL을 준다면
1. 구조를 본다 (DESC)
2. ROWID, ROWNUM 확인한다
3. 제약조건을 본다
USER_CONSTRAINTS -> 제약조건 확인 테이블
SELECT TABLE_NAME , CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS;
Q11) 테이블생성을 해보자
CREATE TABLE USER_NAME(
컬럼명 데이터타입 제약조건...
)
CREATE TABLE USER_NAME AS
대상테이블
-CASE1 : TEST라는 테이블을 생성해보자 ID,NAME
CREATE TABLE TEST(
ID NUMBER,
NAME VARCHAR2(10)
);
-CASE2 : TEST01라는 테이블을 생성해보자 ID,NAME
CREATE TABLE TEST01(
ID NUMBER NOT NULL,
NAME VARCHAR2(10)
);
-CASE3 : TEST02라는 테이블을 생성해보자 ID,NAME,HIREDATE
CREATE TABLE TEST02(
ID NUMBER NOT NULL,
NAME VARCHAR2(10),
HIREDATE DATE
);
-CASE4 : TEST03라는 테이블을 생성해보자
CREATE TABLE TEST03(
AS
SELECT * FROM EMP
); // 제약조건은 안따라온다
-CASE5 : TEST04라는 테이블을 생성해보자
CREATE TABLE TEST04(ENAME ,SAL)
AS
SELECT ENAME,SAL FROM EMP;
-CASE6 : TEST05라는 테이블을 생성해보자
CREATE TABLE TEST05(MYNAME,MYSAL)
AS
SELECT ENAME,SAL FROM EMP;
-CASE7 : TEST06라는 테이블을 생성해보자 / 데이터 없이 구조만 동일하게 테이블이 생성된다.
CREATE TABLE TEST06
AS
SELECT * FROM EMP
WHERE 1 = 0;
- 데이터가 없으면 ROWID, ROWNUM 안생긴다
Q12) 테이블삭제
DROP TABLE 테이블명;
생성된 TEST시리즈 테이블을 모두 삭제한다.
Q13) 휴지통을 확인하자.
SQL> DESC RECYCLEBIN;
SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TS_NAME FROM RECYCLEBIN;
Q14) TEST 계정으로 접속한다
CONNECT TEST / 비밀번호
=> 샘플용 테이블을 만들자
Q15) 컬럼의 내용을 표시할 때 중복데이터 배제하고 단일데이터만 출력하자
- DISTINCT 컬럼명
SELECT DISTINCT JOB
FROM EMP;
Q16) 조건에 맞는 데이터를 찾을 때 사용하는 쿼리문
SELECT 컬럼리스트
FROM 테이블
WHERE 조건문 ; -> 조건문의 결과가 TRUE가 되어야 한다
사원테이블에서 사원의 봉급이 1000원 이상인 사원의 이름과 봉급을 출력하자
SELECT ENAME,SAL
FROM EMP
WHERE SAL >= 1000;
Q17) 사원테이블에서 부서번호가 10번인 사원의 이름과 부서번호를 출력하자
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO =10;
Q18) 부서번호가 10번인 월급이 1000이상인 사원의 이름과 월급을 출력하되 별칭으로 처리
SELECT ENAME 사원의이름, SAL 월급
FROM EMP
WHERE DEPTNO = 10 AND SAL >=1000;
Q19) 부서번호가 10 또는 20번인 사원의 월급과 부서번호를 출력하자
SELECT SAL, DEPTNO
FROM EMP
WHERE DEPTNO =10 OR DEPTNO = 20;
Q20) 사원의 입사년도가 80년도 이후에 입사한 사원의 이름과 입사년도를 출력하자
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE >='1980/01/01'; // WHERE HIREDATE >='1980-01-01'; //WHERE HIREDATE >='80-01-01';
Q21) TEST 테이블을 생성해서 데이터를 입력해보자
CREATE TABLE TEST(
ID NUMBER(4) NOT NULL
HIREDATE DATE);
DESC TEST;
Q22) TEST 테이블에 데이터 입력해보자
INSERT 테이블명
VALUES(값..);
오라클에서는 INSERT INTO 테이블명 VALUES(값..);
INSERT INTO 테이블명 (컬럼명)VALUES(값..); // 1:1로 데이터를 줄 수 있다.
INSERT INTO TEST VALUES(111,SYSDATE);
INSERT INTO TEST VALUES(222,'1980/08/07');
INSERT INTO TEST VALUES(333,'80/08/07');
INSERT INTO TEST VALUES(444,'2080/08/07');
INSERT INTO TEST VALUES(555,'1980/08/07');
Q23) TEST 테이블에 있는 내용을 년도를 4자리로 출력해서 확인해보자
SELECT ID,TO_CHAR(HIREDATE,'YYYY-MM-DD') FROM TEST;
SELECT 'ABCD' FROM DUAL; => 가상테이블
TO_DATE('13-JUL-87')-51 => 캐스팅
문자열 LIKE %[모든],_[한글자]
Q24) 사원테이블에서 사원의 이름이 A로 시작하는 사원의 이름을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%';
Q25) 사원테이블에서 사원의 이름이 T자가 2개 들어간 사원의 이름을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%T%T%';
사원테이블에서 사원의 이름이 T자가 1개 들어간 사원의 이름을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%_T%';
Q26) 사원테이블에서 사원의 이름이 L자가 2개 들어간 사원의 이름을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%L%L%';
사원테이블에서 사원의 이름이 L자가 1개 들어간 사원의 이름을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%_L%';
사원테이블에서 사원의 이름이 L자가 1개 들어간 사원의 이름을 출력해보자
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_L%';
Q27) 사원테이블에서 사원의 이름이 S로 끝나는 사원의 이름을 출력해보자
Q28)
CREATE TABLE TEST01(
ENAME VARCHAR2(10));
INSERT INTO TEST01 VALUES('A_111');
INSERT INTO TEST01 VALUES('B1%1');
INSERT INTO TEST01 VALUES('C1%1');
INSERT INTO TEST01 VALUES('D11_1');
SELECT ENAME FROM TEST01 WHERE ENAME LIKE'%A^_1%' ESCAPE '^';
SELECT ENAME FROM TEST01 WHERE ENAME LIKE '%*%%' ESCAPE '*';
//와일드카드를 VALUE로 처리할 수 있는것이 ESCAPE SELECT 결과의 정렬을 구현하기 위한 쿼리
SELECT 컬럼1, 컬럼2, 컬럼3..
FROM 테이블명
ORDER BY 컬럼1 [DESC|ASC]; => DEFAULT : ASC
Q29) 사원의 테이블에서 사원의 이름을 오름차순으로 출력해보자
SELECT ENAME
FROM EMP
ORDER BY ENAME; //ORDER BY ENAME ASC; // ORDER BY 1 ASC;
*확인
SELECT ROWID , ROWNUM , ENAME
FROM EMP
ORDER BY ENAME ASC;
- CREATE AS~EMP 테이블을 복사 생성할 경우
CREATE TABLE MY_EMP
AS
SELECT ENAME , SAL, COMM FROM EMP;
Q30) 사원테이블에서 사원의 이름을 내림차순으로 출력해보자
SELECT ENAME
FROM EMP
ORDER BY 1 DESC;
Q31) 사원테이블에서 사원의 이름을 오름차순으로 구현하고 봉급을 내림차순으로 출력해보자
SELECT ENAME
FROM EMP
ORDER BY ENAME ASC, SAL DESC;숫자함수, 문자함수, 날짜함수 => TO_NUMBER() , TO_CHAR(), TO_DATE();
집계함수 (분석함수) => GROUP BY쿼리랑 사용
Q32) 사원테이블의 봉급을 이용해서 간단한 집계함수를 확인해보자
SELECT COUNT(SAL), SUM(SAL), AVG(SAL) , MAX(SAL), MIN(SAL),MEDIAN(SAL) // COUNT만 괄호안에 와일드카드 가능
FROM EMP;
Q33) 사원테이블에서 평균 월급을 구해라
SELECT AVG(SAL)
FROM EMP;
Q34) 사원테이블에서 부서번호가 10번의 사원의 평균 월급을 구하자
SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = 10;
Q35) 직업이 SALESMAN인 사원의 평균월급과 합을 구해라
SELECT AVG(SAL),SUN(SAL)
FROM EMP
WHERE JOB = 'SALESMAN';
Q36) 부서별 평균 월급을 구해라
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
Q37) 직업별 평균 월급을 구해라
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB;
Q38) 부서별 평균 월급을 구하되 10번 부서만 출력해보자
SELECT DEPTNO , AVG(SAL)
FROM EMP
WHERE DEPTNO = 10
GROUP BY DEPTNO;
Q39) 직업별 월급의 합을 구해라
SELECT JOB , SUM(SAL)
FROM EMP
GROUP BY JOB;
Q40) 직업이 SALESMAN인 사원들 중에서 최대 월급을 구하자
SELECT JOB , MAX(SAL) // SELECT JOB, MAX(SAL),ENAME => GROUP BY 오류
FROM EMP
WHERE JOB = 'SALESMAN'
GROUP BY JOB;
//이거는 가능
SELECT JOB , MAX(SAL),DEPTNO
FROM EMP
WHERE JOB = 'SALESMAN'
GROUP BY JOB , DEPTNO;
- GROUP BY의 특징
1. GROUP BY문 다음에는 데이터를 구분짓기 위한 표현식으로 해당 테이블의 컬럼명이나 변수 값 등이 올 수 있으며 그룹함수를 사용한 형태는 올 수 없다.
< GROUP BY AVG(SAL) -> X>
2. SELECT ~ LIST 에는 GROUP BY문에 명시된 표현식과 그 외 그룹함수를 사용한 표현식만 올 수 있다.
3. 출력된 결과를 정렬하기 위해 ORDER BY 문을 사용한다.
단 ORDER BY문 다음에는 SELECT ~ LIST에서 명시된 컬럼 또는 표현식과 컬럼의 별칭, 컬럼 번호 등만 사용된다.
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 1;
IS [NOT] NULL
Q41) 사원테이블에서 커미션이 책정되어 있는 사원의 이름과 커미션을 출력하자
SELECT ENAME, COMM
FROM EMP
WHERE COMM IS NOT NULL;
Q42) 사원테이블에서 커미션이 책정이 안된 사원의 이름과 커미션을 출력하자
SELECT ENAME, COMM
FROM EMP
WHERE COMM IS NULL;
HAVING
- GROUP BY로 집계된 조건을 줄 때 사용되는 쿼리문
- HAVING 연산자는 GROUP BY 연산에 의해서 나누어진 데이터들을 다시 필터링 하기 위해 사용한다.
- 제 2의 조건문이라고도 부르며 조건문에서 그룹함수가 사용이 가능하다.
- HAVING 문 다음에는 SELECT ~ LIST 에서 사용한 컬럼과 그룹함수를 사용한 컬럼에 대해서만 조건을 사용할 수 있다.
수행순서
SELECT -----------------5
FROM --------------------1
WHERE -------------------2
GROUP BY ----------------3
HAVING ------------------4
ORDER BY ----------------6
Q43) 직업별 전체 급여를 구하고 월급의 합이 3500이상인 것만 출력해라
SELECT JOB , SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) >=3500;
Q44) 부서별 월급의 합을 구하고 그 합이 8000이상인 것만 출력해라
SELECT DEPTNO , SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) >=8000;