sqlplus "/sysdba"
startup
conn ejb/ejb
++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ADDR TABLE
BONUS TABLE
DIVISION TABLE
PART TABLE
PAYGRADE TABLE
PERSONNEL TABLE
SCORE TABLE
STUDENT TABLE
TECH TABLE
+++++++++++++++++++++++++++++++++++++++++++++++++
--번호,이름,학교,수능,내신,수능+내신
select s.bunho,s.irum,s.school,c.s1,c.s2,c.s1+c.s2 from student s,score c
where s.bunho=c.bunho;
BUNHO IRUM SCHOOL S1 S2 C.S1+C.S2
---------- ---------- ---------- ---------- ---------- ----------
2003120001 강감찬 백제고 90 80 170
2003120002 산만해 강원 50 78 128
2003120003 김희선 서울여고 77 96 173
2003120004 최진실 서여고 88 88 176
2003120005 최부자 서울 95 80 175
2003120006 강영실 고구려 69 50 119
2003120007 소찬휘 한라 87 82 169
2003120008 김주희 한성 75 96 171
2003120009 박두열 남고 98 92 190
2003120010 박문수 고구려 90 95 185
10 개의 행이 선택되었습니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--번호,이름,학교,학과명,수능,내신
--번호,이름,학교,학과명,수능,내신,면접,체력장,총점,평균
select s.bunho,s.irum,s.school,p.partname,c.s1,c.s2,t.s3,t.s4,c.s1+c.s2+t.s3+t.s4 tot,(c.s1+c.s2+t.s3+t.s4)/4 avg from student s,part p, score c, tech t where s.bunho=c.bunho and s.bunho=t.bunho and s.partcd=p.partcd;
BUNHO IRUM SCHOOL PARTNAME S1 S2 S3 S4 TOT AVG
---------- ------ ---------- --------------- --- --- --- --- ---- ----
2003120001 강감찬 백제고 컴퓨터공학과 90 80 88 95 353 88
2003120002 산만해 강원 멀티미디어학과 50 78 70 78 276 69
2003120003 김희선 서울여고 가정관리학과 77 96 69 88 330 83
2003120004 최진실 서여고 컴퓨터공학과 88 88 79 55 310 78
2003120005 최부자 서울 멀티미디어학과 95 80 85 68 328 82
2003120006 강영실 고구려 컴퓨터공학과 69 50 90 89 298 75
2003120007 소찬휘 한라 국어국문학과 87 82 66 78 313 78
2003120008 김주희 한성 가정관리학과 75 96 77 89 337 84
2003120009 박두열 남고 원예학과 98 92 88 95 373 93
2003120010 박문수 고구려 컴퓨터공학과 90 95 95 90 370 93
10 개의 행이 선택되었습니다.
BUNHO IRUM SCHOOL PARTNAME S1 S2 S3 S4 TOT AVG
---------- ------ ---------- --------------- --- --- --- --- ---- ----
2003120001 강감찬 백제고 컴퓨터공학과 90 80 88 95 353 88
2003120002 산만해 강원 멀티미디어학과 50 78 70 78 276 69
2003120003 김희선 서울여고 가정관리학과 77 96 69 88 330 83
2003120004 최진실 서여고 컴퓨터공학과 88 88 79 55 310 78
2003120005 최부자 서울 멀티미디어학과 95 80 85 68 328 82
2003120006 강영실 고구려 컴퓨터공학과 69 50 90 89 298 75
2003120007 소찬휘 한라 국어국문학과 87 82 66 78 313 78
2003120008 김주희 한성 가정관리학과 75 96 77 89 337 84
2003120009 박두열 남고 원예학과 98 92 88 95 373 93
2003120010 박문수 고구려 컴퓨터공학과 90 95 95 90 370 93
10 개의 행이 선택되었습니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create view v_st
as
select s.bunho,s.irum,s.school,p.partname,c.s1,c.s2,t.s3,t.s4,c.s1+c.s2+t.s3+t.s4 tot,(c.s1+c.s2+t.s3+t.s4)/4 avg from student s,part p, score c, tech t where s.bunho=c.bunho and s.bunho=t.bunho and s.partcd=p.partcd;
+++++++++++++++++++++++++++++
--학과명별 지원학생수
SQL> select partname, count(*) as 명 from v_st group by partname;
PARTNAME 명
--------------- ----------
가정관리학과 2
국어국문학과 1
멀티미디어학과 2
원예학과 1
컴퓨터공학과 4
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--보너스를 널인 사원의 사번, 이름,급여, 보너스 출력
select pno,pname,pay,bonus from personnel
where bonus is null;
re:
PNO PNAME PAY BONUS
------ --------------- ---------- ----------
1111 SMITH 1000
1114 JAMES 3975
1001 BILL 7000
1116 JOHNSON 3550
1118 MARTIN 3450
1121 KIM 4000
1123 PARK 2500
2000 HELLO_KIM 5000
+++++++++++++++++++++++++++++++++++++++++++
-- 세일즈맨이 아닌 사람 출력
select * from personnel where job != 'SALESMAN';
re:
__
--
--
7 개의 행이 선택되었습니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 보너스를 받는 사람중 굽여액이 1500미만인 사람의 번호 이름 급여, 보너스 출력
select pno,pname,pay,bonus from personnel
where bonus is not null and pay<1500;
PNO PNAME PAY BONUS
----- --------------- ---------- ----------
1113 WORD 1450 300
1123 LEE 1200 0
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
--conn scott/tiger
--카데시안 조인 (모든 경우 의 수가 다 출력)
select e.empno,e.ename,e.job,d.dname from emp e,dept d;
re:
56 개의 행이 선택되었습니다.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--크로스 조인 (모든 경우의 수가 출력)
select e.empno,e.ename,e.job,d.dname
from emp e cross join dept d;
re:
56 개의 행이 선택되었습니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-equl 조인
select e.empno,e.ename,e.job,d.dname
from emp e,dept d where e.deptno=d.deptno;
re:
EMPNO ENAME JOB DNAME
------ ---------- --------- --------------
7369 SMITH CLERK RESEARCH
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7566 JONES MANAGER RESEARCH
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7782 CLARK MANAGER ACCOUNTING
7788 SCOTT ANALYST RESEARCH
7839 KING PRESIDENT ACCOUNTING
7844 TURNER SALESMAN SALES
7876 ADAMS CLERK RESEARCH
EMPNO ENAME JOB DNAME
------ ---------- --------- --------------
7900 JAMES CLERK SALES
7902 FORD ANALYST RESEARCH
7934 MILLER CLERK ACCOUNTING
+++++++++++++++++++++++++++++++++++++++++++
--non equl 조인 (등급
select * from salgrade;
re:
GRADE LOSAL HISAL
------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
+++++++++++++++++++++++++++++++++++
-- losal 와 hisal 사이
select empno,ename,grade,sal from emp e,salgrade s
where e.sal between s.losal and s.hisal;
re:
EMPNO ENAME GRADE SAL
------ ---------- ---------- ----------
7369 SMITH 1 800
7876 ADAMS 1 1100
7900 JAMES 1 950
7521 WARD 2 1250
7654 MARTIN 2 1250
7934 MILLER 2 1300
7499 ALLEN 3 1600
7844 TURNER 3 1500
7566 JONES 4 2975
7698 BLAKE 4 2850
7782 CLARK 4 2450
EMPNO ENAME GRADE SAL
------ ---------- ---------- ----------
7788 SCOTT 4 3000
7902 FORD 4 3000
7839 KING 5 5000
++++++++++++++++++++++++++++++++++++++++++++++
conn ejb/ejb
--- 테이블 만들기
create table insa(
no number,
irum varchar2(10),
pay number
);
+++++++++++++++++++++++++++++++++++++++++++++++
create table bank(
no number,
savemoney number
);
-------------------------------
--값 넗기
begin
for z in 1.. 10 loop
insert into insa values(z,'kim'||z,z*5);
end loop;
end;
/
-----------------------------
begin
for z in 3.. 13 loop
insert into bank values(z,z*3);
end loop;
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select * from insa;
re:
NO IRUM PAY
--- ------ ----------
1 kim1 5
2 kim2 10
3 kim3 15
4 kim4 20
5 kim5 25
6 kim6 30
7 kim7 35
8 kim8 40
9 kim9 45
10 kim10 50
++++++++++++++++++++++++++++++
select * from bank;
NO SAVEMONEY
------ ----------
3 9
4 12
5 15
6 18
7 21
8 24
9 27
10 30
11 33
12 36
13 39
++++++++++++++++++++++++++++
--번호,이름,급여,저축액(공통적으로 존재 하는 것만 출력(양쪽 테이블에 서로 존재하는 내용))
select i.no,i.irum,i.pay,b.savemoney from insa i,bank b
where i.no=b.no;
re:
NO IRUM PAY SAVEMONEY
--- ------ ---------- ----------
3 kim3 15 9
4 kim4 20 12
5 kim5 25 15
6 kim6 30 18
7 kim7 35 21
8 kim8 40 24
9 kim9 45 27
10 kim10 50 30
++++++++++++++++++++++++++++++++++++++++++++
--특정 테이블의 내용 전부 출력(인사테이블)
select i.no,i.irum,i.pay,b.savemoney from insa i,bank b
where i.no=b.no(+);
re:
NO IRUM PAY SAVEMONEY
---- ------ ---------- ----------
1 kim1 5
2 kim2 10
3 kim3 15 9
4 kim4 20 12
5 kim5 25 15
6 kim6 30 18
7 kim7 35 21
8 kim8 40 24
9 kim9 45 27
10 kim10 50 30
+++++++++++++++++++++++++++++++++++++++++++++
--특정 테이블의 내용 전부 출력(뱅크테이블)
select i.no,i.irum,i.pay,b.savemoney from insa i,bank b
where i.no(+)=b.no;
NO IRUM PAY SAVEMONEY
----- ------ ---------- ----------
3 kim3 15 9
4 kim4 20 12
5 kim5 25 15
6 kim6 30 18
7 kim7 35 21
8 kim8 40 24
9 kim9 45 27
10 kim10 50 30
33
36
39
++++++++++++++++++++++++++++++++++++++++++++++
--left outer join
select i.no,i.irum,i.pay,b.savemoney
from insa i left outer join bank b
on i.no=b.no;
re:
NO IRUM PAY SAVEMONEY
---- ------ ---------- ----------
3 kim3 15 9
4 kim4 20 12
5 kim5 25 15
6 kim6 30 18
7 kim7 35 21
8 kim8 40 24
9 kim9 45 27
10 kim10 50 30
1 kim1 5
2 kim2 10
--right outer join
select i.no,i.irum,i.pay,b.savemoney
from insa i right outer join bank b
on i.no=b.no;
re:
NO IRUM PAY SAVEMONEY
--- ------ ---------- ----------
3 kim3 15 9
4 kim4 20 12
5 kim5 25 15
6 kim6 30 18
7 kim7 35 21
8 kim8 40 24
9 kim9 45 27
10 kim10 50 30
39
36
33
--full outer join
select i.no,i.irum,i.pay,b.savemoney
from insa i full outer join bank b
on i.no=b.no;
re:
NO IRUM PAY SAVEMONEY
--- ------ ---------- ----------
3 kim3 15 9
4 kim4 20 12
5 kim5 25 15
6 kim6 30 18
7 kim7 35 21
8 kim8 40 24
9 kim9 45 27
10 kim10 50 30
1 kim1 5
2 kim2 10
39
NO IRUM PAY SAVEMONEY
--- ------ ---------- ----------
36
33
+++++++++++++++++++++++++++++++++++++++++++
conn scott/tiger
--natural join(같은 컬럼을 찾아서 관계를 맺음)
select e.empno,e.ename,e.job,d.dname
from emp e natural join dept d;
re:
EMPNO ENAME JOB DNAME
------ ---------- --------- --------------
7369 SMITH CLERK RESEARCH
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7566 JONES MANAGER RESEARCH
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7782 CLARK MANAGER ACCOUNTING
7788 SCOTT ANALYST RESEARCH
7839 KING PRESIDENT ACCOUNTING
7844 TURNER SALESMAN SALES
7876 ADAMS CLERK RESEARCH
EMPNO ENAME JOB DNAME
------ ---------- --------- --------------
7900 JAMES CLERK SALES
7902 FORD ANALYST RESEARCH
7934 MILLER CLERK ACCOUNTING
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--using 구문
select e.empno,e.ename,e.job,d.dname
from emp e join dept d using(deptno);
re:
EMPNO ENAME JOB DNAME
------ ---------- --------- --------------
7369 SMITH CLERK RESEARCH
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7566 JONES MANAGER RESEARCH
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7782 CLARK MANAGER ACCOUNTING
7788 SCOTT ANALYST RESEARCH
7839 KING PRESIDENT ACCOUNTING
7844 TURNER SALESMAN SALES
7876 ADAMS CLERK RESEARCH
EMPNO ENAME JOB DNAME
------ ---------- --------- --------------
7900 JAMES CLERK SALES
7902 FORD ANALYST RESEARCH
7934 MILLER CLERK ACCOUNTING
++++++++++++++++++++++++++++++++++++++++++++++++++
--self 조인 (자기 자신 조인)
ex)
select empno,ename,job,mgr from emp;
re:
EMPNO ENAME JOB MGR
------ ---------- --------- ----------
7369 SMITH CLERK 7902
7499 ALLEN SALESMAN 7698
7521 WARD SALESMAN 7698
7566 JONES MANAGER 7839
7654 MARTIN SALESMAN 7698
7698 BLAKE MANAGER 7839
7782 CLARK MANAGER 7839
7788 SCOTT ANALYST 7566
7839 KING PRESIDENT
7844 TURNER SALESMAN 7698
7876 ADAMS CLERK 7788
EMPNO ENAME JOB MGR
------ ---------- --------- ----------
7900 JAMES CLERK 7698
7902 FORD ANALYST 7566
7934 MILLER CLERK 7782
-----------------------------------------------------------------
--자기 자신을 조인
select e1.empno,e1.ename,e1.job,e2.ename manager
from emp e1,emp e2 where e1.mgr=e2.empno;
re:
EMPNO ENAME JOB MANAGER
------ ---------- --------- ----------
7369 SMITH CLERK FORD
7499 ALLEN SALESMAN BLAKE
7521 WARD SALESMAN BLAKE
7566 JONES MANAGER KING
7654 MARTIN SALESMAN BLAKE
7698 BLAKE MANAGER KING
7782 CLARK MANAGER KING
7788 SCOTT ANALYST JONES
7844 TURNER SALESMAN BLAKE
7876 ADAMS CLERK SCOTT
7900 JAMES CLERK BLAKE
EMPNO ENAME JOB MANAGER
------ ---------- --------- ----------
7902 FORD ANALYST JONES
7934 MILLER CLERK CLARK
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--조인문으로 변경하기(여러 테이블 과 조인 할 경우 연속 조인 사용가능)
select st.bunho,st.irum,st.school,p.partname,sc.s1,sc.s2,t.s3,t.s4,sc.s1+sc.s2+t.s3+t.s4 tot,(sc.s1+sc.s2+t.s3+t.s4)/4 ave
from student st join part p on st.partcd=p.partcd
join score sc on st.bunho=sc.bunho
join tech t on st.bunho=t.bunho;
re:
BUNHO IRUM SCHOOL PARTNAME S1 S2 S3 S4 TOT AVE
---------- ------ ---------- --------------- --- --- --- --- ---- ----------
2003120001 강감찬 백제고 컴퓨터공학과 90 80 88 95 353 88.25
2003120002 산만해 강원 멀티미디어학과 50 78 70 78 276 69
2003120003 김희선 서울여고 가정관리학과 77 96 69 88 330 82.5
2003120004 최진실 서여고 컴퓨터공학과 88 88 79 55 310 77.5
2003120005 최부자 서울 멀티미디어학과 95 80 85 68 328 82
2003120006 강영실 고구려 컴퓨터공학과 69 50 90 89 298 74.5
2003120007 소찬휘 한라 국어국문학과 87 82 66 78 313 78.25
2003120008 김주희 한성 가정관리학과 75 96 77 89 337 84.25
2003120009 박두열 남고 원예학과 98 92 88 95 373 93.25
2003120010 박문수 고구려 컴퓨터공학과 90 95 95 90 370 92.5
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--집합 연산
conn scott/tiger
select * from dept;
re:
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
+++++++++++++++++++++++++++++++++++++++
--새로 생성
create table dept2
as
select * from dept where deptno>20;
-------------------------
select * from dept2;
re:
DEPTNO DNAME LOC
------ -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
--데이터 삽입
insert into dept2 values(77,'기획부','서울');
insert into dept2 values(88,'관리부','평양');
commit;
select * from dept2;
re:
DEPTNO DNAME LOC
-------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
77 기획부 서울
----합집합
select * from dept
union
select * from dept2;
re:
DEPTNO DNAME LOC
------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
77 기획부 서울
88 관리부 평양
-- 중복을 모두 출력
select * from dept
union all
select * from dept2;
re:
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
30 SALES CHICAGO
40 OPERATIONS BOSTON
77 기획부 서울
88 관리부 평양
--차집합
select * from dept
minus
select * from dept2;
re:
DEPTNO DNAME LOC
------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
--교집합
select * from dept
intersect
select * from dept2;
re:
DEPTNO DNAME LOC
------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
+++++++++++++++++++++++++++++++++++++++++++++++++
--set 연산자 계속
'ORACLE' 카테고리의 다른 글
| 오라클 우편번호 만들는 법/제약조건/삭제,수정/데이터 입력 (1) | 2024.01.16 |
|---|---|
| 오라클 연산/급여계산 (1) | 2024.01.16 |
| 오라클 문자열 검사 (2) | 2024.01.16 |
| 가지치기//날짜&시간 형식 바꾸기//계층쿼리//출력형식 맞추기 (0) | 2024.01.16 |
| 오라클 사용자 접속/ 테이블 생성/ 데이터 삽입/ 라인조절/ 중복제거/ 찾기 (0) | 2024.01.16 |