공부흔적

SCOTT>SELECT p_store "판매점", p_date "판매날짜", p_code "제품코드",  

  2          p_qty "당일판매수량",

  3       LAG(p_qty,1) over (partition by p_store order by p_date) "전일판매량",

  4          p_qty - LAG(p_qty,1) over (partition by p_store order by p_date) "수량차이",

  5          p_total "당일판매금액",

  6          LAG(p_total,1) over (partition by p_store order by p_date) "전일판매금액",

  7          p_total - LAG(p_total,1) over (partition by p_store order by p_date) "금액차이"

  8  from panmae;

-- 된 부분은 토드에서 주석으로 설명을 달아놓은 부분이다. 

SELECT deptno "부서번호", name "이름", pay "급여",

--pay의 총 합을 구한다.

       SUM(pay) OVER() "총급여",       

--오라클에서 제공하는 비율을 구하는 함수를 pay의 총합을 이용해서

--구한 후 곱하기 100을 한다. 소수점 2자리까지 반올림을 해준다.

       ROUND((RATIO_TO_REPORT(SUM(pay)) OVER())*100,2) "급여비중-%"

FROM professor

GROUP BY deptno, name, pay

--급여비중을 내림차순으로 정렬한다. 

ORDER BY 5 DESC;

데이블의 구조(컬럼)만 가져오고 데이터 가져오지 않기

CREATE TABLE emp10

AS

SELECT empno, ename, sal

WHERE 1=2; 

(WHERE 구문에 틀린 조건 아무거나 주면 껍데기만 복사된다.)

테이블에 컬럼 추가

ALTER TABLE test10

ADD(ADDRESS varchar2(30) DERAULT '서울');

테이블 컬럼 이름 변경

ALTER TABLE test10 RENAME COLUMN ADDRESS TO LOCATION;

                    테이블명                          원래컬럼        변경할 컬럼명 

테이블 이름 변경

RENAME test10 To dept5;

        현재테이블명   변경할테이블명 

ALTER TABLE emp4

ADD CONSTRAINT emp4_no_fk FOREIGN KEY(no)

REFERENCES emp2(empno);

ALTER TABLE test10

DISABLE VALIDATE CONSTRAINT test10_name_uk;

ALTER TABLE test10 ENABLE VALIDATE CONSTRAINT test10_name_uk

EXCEPTIONS INTO sys.exceptions;

참고)

exceptions table가 없기 때문에 

sys계정으로 들어가서 이셉션 테이블을 생성하는 쿼리를 실행시켜줘야 한다. 

SCOTT> CONN / AS SYSDBA ;  <- 관리자 계정으로 접속합니다 

SYS> @?/rdbms/admin/utlexcpt.sql   <- ?는  $ORACLE_HOME 디렉토리를 의미합니다. 

Table created. 

실행한 후에 위의 답과 같이 exceptnons table를 이용, 에러를 확인할 수 있다. 

SELECT owner,constraint_name, table_name, column_name

FROM user_cons_columns

WHERE table_name='EMP';

SELECT COUNT(*)||'명' "합계",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'01',0))||'명' "1월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'02',0))||'명' "2월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'03',0))||'명' "3월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'04',0))||'명' "4월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'05',0))||'명' "5월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'06',0))||'명' "6월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'07',0))||'명' "7월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'08',0))||'명' "8월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'09',0))||'명' "9월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'10',0))||'명' "10월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'11',0))||'명' "11월",

       COUNT(DECODE(TO_CHAR(hiredate,'MM'),'12',0))||'명' "12월"

FROM emp; 

SELECT deptno,

       SUM(DECODE(job, 'CLERK', sal, 0))     "CLERK",

       SUM(DECODE(job, 'MANAGER', sal, 0))   "MANAGER",

       SUM(DECODE(job, 'PRESIDENT', sal, 0)) "PRESIDENT",

       SUM(DECODE(job, 'ANALYST', sal, 0))   "ANALYST",

       SUM(DECODE(job, 'SALESMAN', sal, 0))  "SALESMAN",

       SUM(NVL2(job,sal,0)) "합계"

FROM emp

GROUP BY ROLLUP(deptno);

SELECT deptno "부서번호", ename "이름", sal "급여",

       SUM(sal) OVER(ORDER BY sal) "누적급여금액"

FROM emp;

SELECT deptno, ename, sal "급여",SUM(sal) OVER() "전체급여",

       ROUND((RATIO_TO_REPORT(SUM(sal)) OVER())*100,2) "급여비중-%"

FROM emp

GROUP BY deptno, ename, sal

ORDER BY 5 DESC;

SELECT COUNT(name)||'명 ('||(COUNT(name)/COUNT(name)*100)||'%)' "합계",

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'02','서울'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'02','서울'))/COUNT(name)*100)||'%)' "서울",

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'031','경기'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'031','경기'))/COUNT(name)*100)||'%)' "경기",

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'051','부산'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'051','부산'))/COUNT(name)*100)||'%)' "부산",

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'052','울산'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'052','울산'))/COUNT(name)*100)||'%)' "울산",       

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'052','울산'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'052','울산'))/COUNT(name)*100)||'%)' "울산",      

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'053','대구'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'053','대구'))/COUNT(name)*100)||'%)' "대구",      

       COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'055','경남'))||'명 ('||

       (COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')',1,1)-1),'055','경남'))/COUNT(name)*100)||'%)' "경남"

FROM student; 

SELECT d.dname "학과명",

       s.max_height "최대키",

       s.max_weight "최대몸무게"

FROM (SELECT deptno1, MAX(height) max_height, MAX(weight) max_weight

      FROM student

      GROUP BY deptno1) s, department d

WHERE s.deptno1=d.deptno;

SELECT DECODE(deptno, ndeptno, null, deptno) deptno, profno, name,pay

FROM (SELECT LAG(deptno) OVER (ORDER BY deptno) ndeptno, deptno, profno, name, pay

      FROM professor);

SELECT s.grade "학년",

       s.name "이름",

       s.height "키",

       a.avg_height "평균키"

FROM(SELECT grade, AVG(height) avg_height

     FROM student

     GROUP BY grade) a, student s

WHERE a.grade = s.grade

AND s.height > a.avg_height

ORDER BY 1;

SELECT num "순번", profno "교수번호", name "교수명",

       pay "급여", SUM(pay) "급여합계", ROUND(AVG(pay),1) "급여평균"

FROM (SELECT profno, name, pay, ROWNUM num

      FROM professor)

GROUP by CEIL(num/3), ROLLUP((profno, name, pay, num))

ORDER BY CEIL(num/3);

SELECT name "이름", position "직급",

       TO_CHAR(pay,'999,999,999')||' 원' "연봉"

FROM emp2

WHERE pay > ANY (SELECT pay

                 FROM emp2

                 WHERE position='과장');

SELECT p.profno "교수번호", p.name "교수명",

       p.hiredate "입사일", d.dname "학과명"

FROM professor p, department d

WHERE p.deptno=d.deptno

AND (p.deptno, p.hiredate) IN (SELECT deptno, MIN(hiredate)

                               FROM professor

                               GROUP BY deptno)

ORDER BY 3;

CREATE SEQUENCE seq_jumun_no

INCREMENT BY 1

START WITH 1000

MAXVALUE 1010

MINVALUE 990

CYCLE

CACHE 2;

*시퀀스 설명

CREATE TABLE emp3

(no NUMBER(4)

        CONSTRAINT emp3_no_pk PRIMARY KEY,

 name VARCHAR2(10)

        CONSTRAINT emp_name_nn NOT NULL,

 deptno VARCHAR2(6) REFERENCES dept2(dcode)

);

포린키는 자식테이블에서 레퍼런스만 적어주면 저절로 포린키로 설정이 된다.

궁금하면 아래의 포린키 확인 쿼리를 돌려보자

7장 26P 제약조건

* 포린키에 대한 정보를 확인하고 싶다면 쿼리 실행할 것

SELECT a.table_name    "Child_Table", 

       c.column_name    "Child_Column", 

       a.constraint_name    "Child_Cons_name", 

       b.table_name    "Parent_Table" , 

       a.r_constraint_name    "Parent_Cons_name" , 

       d.column_name    "Parent_Column" 

FROM   user_constraints a , user_constraints b , user_cons_columns c, 

      (SELECT constraint_name, column_name, table_name 

       FROM  user_cons_columns) d 

WHERE a.r_constraint_name=b.constraint_name 

AND   a.constraint_name=c.constraint_name 

AND   a.r_constraint_name=d.constraint_name 

AND   a.constraint_type='R' ; 

동의어 생성할 수 있도록 권한 부여하기

SCOTT>conn / as sysdba

Connected.

SYS>GRANT create synonym TO scott;

Grant succeeded.

SYS>GRANT create public synonym TO scott;

Grant succeeded.

SYS>conn scott/tiger

Connected.

모든 사용자가 사용할 수 있도록 테이블 동의어 만들기

SCOTT>CREATE PUBLIC SYNONYM d2 FOR department;

Synonym created.

SELECT name, jumin, DECODE(SUBSTR(jumin,7,1),'1', ' 남자','여자 ') "성별"

FROM student

WHERE deptno1=101;    

SELECT * FROM reg_test

WHERE NOT REGEXP_LIKE(text,'[a-z]');

SELECT * FROM reg_test

WHERE REGEXP_LIKE(text,'\?');

SELECT no, ip, REGEXP_REPLACE(ip,'\.', '*', 1, 2) "replace"

FROM reg_test2

  1  SELECT studno, name, id

  2  FROM student

  3* WHERE id=LOWER(REGEXP_REPLACE('&id','( ){1,}',''))

SCOTT>/

Enter value for id:  75 TRUE

old   3: WHERE id=LOWER(REGEXP_REPLACE('&id','( ){1,}',''))

new   3: WHERE id=LOWER(REGEXP_REPLACE(' 75 TRUE','( ){1,}',''))

    STUDNO NAME ID

---------- -------------------- ------------------------------

      9411 서진수 75true

* SET verify off : 기본값 on, 

sql이나 pl/sql 에서 &를 이용한 입력, 치환 변수 사용시 전 후의 값을 보여줄 것인지 선택하는 것

위와 같이 old/new 가 나오는 것은 off 했기 때문이다. 

SELECT name, LTRIM(REGEXP_SUBSTR(hpage,'/([[:alnum:]]+\.?){3,4}?'),'/') "URL"

FROM professor

WHERE hpage IS NOT NULL;

--/www.abc.net를 찾음, .이 있는 약 3~4 덩어리글자, 물음표는 뒤에 글자가 있어도 되고 없어도 되는 것

--/를 ltrim으로 자름

SELECT empno, name||' '||d.dname||' '|| NVL(a.position, '사원') "사원명",

       (SELECT COUNT(*)

        FROM emp2

        START WITH empno=a.empno

        CONNECT BY PRIOR empno=pempno)-1 "부하직원수"

        FROM emp2 a,

            (SELECT dname,dcode FROM dept2) d

             WHERE a.deptno=d.dcode

ORDER BY 3 desc;