1.panmae테이블을 사용하여 아래와 같이 출력하세요
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;
2.
3장 그룹함수 35p
professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력
-- 된 부분은 토드에서 주석으로 설명을 달아놓은 부분이다.
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;
3
5장 ddl
emp 테이블에서 empno, ename, sal 컬럼을 가져와
emp10 이름으로 테이블 생성,
데이터는 가져오지 않도록 하는 쿼리문
데이블의 구조(컬럼)만 가져오고 데이터 가져오지 않기
CREATE TABLE emp10
AS
SELECT empno, ename, sal
WHERE 1=2;
(WHERE 구문에 틀린 조건 아무거나 주면 껍데기만 복사된다.)
4
5장 ddl 13p
test10 테이블에 address 컬럼을 추가하는 쿼리
address는 varchar2(30) 이고, 기본값으로 "서울"이 자동입력 되도록 한다.
테이블에 컬럼 추가
ALTER TABLE test10
ADD(ADDRESS varchar2(30) DERAULT '서울');
5
5장 ddl 13p
4번에서 만든 address 컬럼의 이름을 location 으로 변경하는 쿼리
테이블 컬럼 이름 변경
ALTER TABLE test10 RENAME COLUMN ADDRESS TO LOCATION;
테이블명 원래컬럼 변경할 컬럼명
테이블 이름 변경
RENAME test10 To dept5;
현재테이블명 변경할테이블명
6
7장 제약조건 4p
emp4 테이블의 no 컬럼이 emp2 테이블의 empno 컬럼의 값을 참조하도록 참조키 제약조건을
설정하세요.(emp4 테이블이 자식테이블입니다)
ALTER TABLE emp4
ADD CONSTRAINT emp4_no_fk FOREIGN KEY(no)
REFERENCES emp2(empno);
7
7장 제약조건 14p
test10 테이블의 name 컬럼에 만들어져 있는 unique 제약조건을 사용안함으로 변경,
또, 해당테이블의 데이터에 DML 명령도 실행되지 않도록 변경하는 쿼리
(제약조건 이름은 test10_name_uk 입니다)
ALTER TABLE test10
DISABLE VALIDATE CONSTRAINT test10_name_uk;
8
7장 제약조건 19~20p
7번에서 사용 안 함으로 설정한 제약조건을 사용함으로 변경,
기존에 있던 내용과 새로 들어올 내용 모두를 체크하는 옵션으로 변경하세요
그리고 문제가 되는 데이터들은 sys.exceptions 테이블에 저장하도록 하세요
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를 이용, 에러를 확인할 수 있다.
9
7장 제약조건 25p
emp테이블에 설정되어 있는 제약조건 중 자신이 생성한 제약 조건들을
테이블명, 컬럼명, 제약조건명으로 검색하는 쿼리를 쓰세요
SELECT owner,constraint_name, table_name, column_name
FROM user_cons_columns
WHERE table_name='EMP';
10
3장 복수행함수 38p
emp 테이블의 hiredate 컬럼을 참조, 아래와 같이 월별로 입사인원수를 출력하세요
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;
11
3장 복수행함수 38p
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);
12
3장 복수행함수 38p
SELECT deptno "부서번호", ename "이름", sal "급여",
SUM(sal) OVER(ORDER BY sal) "누적급여금액"
FROM emp;
13
3장 복수행함수 41p
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;
14
3장 복수행함수 40p
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;
15
9장 view 9p 인라인뷰
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;
16
9장 view 10p 인라인뷰
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);
17
9장 view 16p 연습문제
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;
18
9장 view 연습문제 5번
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);
19
10장 subquery 7p
SELECT name "이름", position "직급",
TO_CHAR(pay,'999,999,999')||' 원' "연봉"
FROM emp2
WHERE pay > ANY (SELECT pay
FROM emp2
WHERE position='과장');
20
10장 sub query 9p
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;
21
11장 sequence와 synonym1~2p
CREATE SEQUENCE seq_jumun_no
INCREMENT BY 1
START WITH 1000
MAXVALUE 1010
MINVALUE 990
CYCLE
CACHE 2;
*시퀀스 설명
22
7장 제약조건 3p
아래 조건에 만족하는 테이블을 생성하세요
*테이블 이름은 emp3
*no 컬럼은 number 4로 하고 primary key 이며
제약조건명은 emp3_no_pk로 하세요
*name 컬럼은 varchar2 10바이트로 하고 not null로 설정하고
제약조건명은 emp3_name_nn 으로 하세요
*deptno 컬럼은 varchar2(6)으로 하고
deptno 테이블의 dcode 컬럼을 참조하는 foreign key로 설정하세요
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' ;
23
11장 sequence와 synonym12p - 시너님 관련
Scott 사용자의 department 테이블의 동의어를 d2 로 생성하되
모든 사용자들이 사용할 수 있도록 생성하세요
(Scott 사용자가 동의어를 생성할 수 있도록 권한도 없다고 가정하고 권한도 부여하세요)
동의어 생성할 수 있도록 권한 부여하기
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.
24
2장 단일행함수 45p
SELECT name, jumin, DECODE(SUBSTR(jumin,7,1),'1', ' 남자','여자 ') "성별"
FROM student
WHERE deptno1=101;
25
2장 단일행함수 56p 정규식함수
reg_test 테이블에서 소문자가 들어있는 모든 행을 제거한 후 아래와 같이 표현하세요
SELECT * FROM reg_test
WHERE NOT REGEXP_LIKE(text,'[a-z]');
26
2장 단일행함수 60p
reg_test 테이블에서 ? 가 들어있는 모든 행을 아래와 같이 출력하세요
SELECT * FROM reg_test
WHERE REGEXP_LIKE(text,'\?');
27
2장 단일행함수 65p 정규식부분
맨 아래예제 변형문제
reg_test2 테이블에서 ip주소에서 두번째 .을 *로 바꾸어 출력하세요
SELECT no, ip, REGEXP_REPLACE(ip,'\.', '*', 1, 2) "replace"
FROM reg_test2
28
2장 단일행함수 67p
사용자로 부터 id 값을 입력 받은 후 student 테이블에서 studno, name, id 를 출력하되
입력된 id 값은 ' 75 TRUE(첫 글자 공백과 가운데 공백, 대문자) 로 입력했을 때
공백을 모두 제거하고, 영어는 소문자로 변환해 조회하는 쿼리를 쓰세요
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 했기 때문이다.
29
2장 단일행함수 71p 정규식 부분
교수테이블(professor )테이블에서 홈페이지(hpage) 주소가 있는 교수들만 조사해서 아래의 화면
처럼 나오게 출력하세요.
SELECT name, LTRIM(REGEXP_SUBSTR(hpage,'/([[:alnum:]]+\.?){3,4}?'),'/') "URL"
FROM professor
WHERE hpage IS NOT NULL;
--/www.abc.net를 찾음, .이 있는 약 3~4 덩어리글자, 물음표는 뒤에 글자가 있어도 되고 없어도 되는 것
--/를 ltrim으로 자름
30
12장 계층형 쿼리 8p
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;
