[KH정보교육원 당산] 23일차 (오라클 - 쿼리문 실습)

실습데이터

학과 테이블  ('920', '컴퓨터공학과', '201호')
create table department
 (
 dept_id varchar2(10) not null,
 dept_name varchar2(20) not null,
 office varchar2(20),
 constraint pk_department
 primary key(dept_id)
 );

학생 테이블 ('1292001', '900424-1825409', '김광식', 3, '서울', '920')
create table student (
 stu_id varchar2(10) not null,
 resident_id varchar2(14) not null,
 name varchar2(10) not null,
 year int,
 address varchar2(10),
 dept_id varchar2(10),
 constraint pk_student primary key(stu_id),
 constraint fk_student foreign key(dept_id)
 references department(dept_id)
 );

교수 테이블 ('92001', '590327-1839240', '이태규', '920', '교수', 1997)
create table professor
 ( prof_id varchar2(10) not null,
 resident_id varchar2(14) not null,
 name varchar2(10) not null,
 dept_id varchar2(10),
 position varchar2(10),
 year_emp int,
 constraint pk_professor primary key(prof_id),
 constraint fk_professor foreign key(dept_id)
 references department(dept_id)
 );


과목 테이블 ('C101', '전산개론', 3)
create table course (
 course_id varchar2(10) not null,
 title varchar2(20) not null,
 credit int,
 constraint pk_course primary key(course_id)
 );


개설과목 테이블 ('C101-01', 'C101', 2012, 1, 'A', '92301', '301호', 40)
create table class (
 class_id varchar2(10) not null,
 course_id varchar2(10),
 year int,
 semester int, division char(1),
 prof_id varchar2(10),
 classroom varchar2(9),
 enroll int,
 constraint pk_class primary key(class_id),
 constraint fk_class1 foreign key(course_id)
 references course(course_id),
 constraint fk_class2 foreign key(prof_id)
 references professor(prof_id)
 );


성적 테이블 ('1292001', 'C101-01', 'B+')
create table takes
 ( stu_id varchar2(10) not null,
 class_id varchar2(10),
 grade char(5),
 constraint pk_takes primary key(stu_id, class_id),
 constraint fk_takes1 foreign key(stu_id)
 references student(stu_id),
 constraint fk_takes2 foreign key(class_id)
 references class(class_id)
 );


insert into department values('920', '컴퓨터공학과', '201호'); 
insert into department values('923', '산업공학과', '207호'); 
insert into department values('925', '전자공학과', '308호');

insert into student values('1292001', '900424-1825409', '김광식', 3, '서울', '920'); 
insert into student values('1292002', '900305-1730021', '김정현', 3, '서울', '920'); 
insert into student values('1292003', '891021-2308302', '김현정', 4, '대전', '920'); 
insert into student values('1292301', '890902-2704012', '김현정', 2, '대구', '923'); 
insert into student values('1292303', '910715-1524390', '박광수', 3, '광주', '923'); 
insert into student values('1292305', '921011-1809003', '김우주', 4, '부산', '923'); 
insert into student values('1292501', '900825-1506390', '박철수', 3, '대전', '925'); 
insert into student values('1292502', '911011-1809003', '백태성', 3, '서울', '925');


insert into professor values('92001', '590327-1839240', '이태규', '920', '교수', 1997); 
insert into professor values('92002', '690702-1350026', '고희석', '920', '부교수', 2003); 
insert into professor values('92301', '741011-2765501', '최성희', '923', '부교수', 2005); 
insert into professor values('92302', '750728-1102458', '김태석', '923', '교수', 1999); 
insert into professor values('92501', '620505-1200546', '박철재', '925', '조교수', 2007); 
insert into professor values('92502', '740101-1830264', '장민석', '925', '부교수', 2005);


insert into course values('C101', '전산개론', 3); 
insert into course values('C102', '자료구조', 3); 
insert into course values('C103', '데이터베이스', 4); 
insert into course values('C301', '운영체제', 3); 
insert into course values('C302', '컴퓨터구조', 3); 
insert into course values('C303', '이산수학', 4); 
insert into course values('C304', '객체지향언어', 4); 
insert into course values('C501', '인공지능', 3); 
insert into course values('C502', '알고리즘', 2);


insert into class values('C101-01', 'C101', 2012, 1, 'A', '92301', '301호', 40); 
insert into class values('C102-01', 'C102', 2012, 1, 'A', '92001', '209호', 30); 
insert into class values('C103-01', 'C103', 2012, 1, 'A', '92501', '208호', 30); 
insert into class values('C103-02', 'C103', 2012, 1, 'B', '92301', '301호', 30); 
insert into class values('C501-01', 'C501', 2012, 1, 'A', '92501', '103호', 45); 
insert into class values('C501-02', 'C501', 2012, 1, 'B', '92502', '204호', 25); 
insert into class values('C301-01', 'C301', 2012, 2, 'A', '92502', '301호', 30); 
insert into class values('C302-01', 'C302', 2012, 2, 'A', '92501', '209호', 45); 
insert into class values('C502-01', 'C502', 2012, 2, 'A', '92001', '209호', 30); 
insert into class values('C502-02', 'C502', 2012, 2, 'B', '92301', '103호', 26);


insert into takes values('1292001', 'C101-01', 'B+'); 
insert into takes values('1292001', 'C103-01', 'A+'); 
insert into takes values('1292001', 'C301-01', 'A'); 
insert into takes values('1292002', 'C102-01', 'A'); 
insert into takes values('1292002', 'C103-01', 'B+'); 
insert into takes values('1292002', 'C502-01', 'C+'); 
insert into takes values('1292003', 'C103-02', 'B'); 
insert into takes values('1292003', 'C501-02', 'A+'); 
insert into takes values('1292301', 'C102-01', 'C+'); 
insert into takes values('1292303', 'C102-01', 'C'); 
insert into takes values('1292303', 'C103-02', 'B+'); 
insert into takes values('1292303', 'C501-01', 'A+');

 

 

기본구조

 

/** 학생의 이름과 학과명을 출력 **/

형식 select 필드리스트 // 선택한 필드명들 // 함수 // 간단한 연산

from 테이블 // 테이블명의 별명

where 조건 //

SQL> select name,dept_name from department,student where department.dept_id = student.dept_id

제일 먼저 from 절 실행 후 where 절

NAME DEPT_NAME

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

김광식 컴퓨터공학과

김정현 컴퓨터공학과

김현정 컴퓨터공학과

김현정 산업공학과

박광수 산업공학과

김우주 산업공학과

박철수 전자공학과

백태성 전자공학과

=> 이름과 학과명 출력

---

SQL> select address from student;

ADDRESS

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

서울

서울

대전

대구

광주

부산

대전

서울

=> 중복값을 제거해 보자 ( 데이터베이스를 통해서 )

 

/** 중복값 제거 : 학생들 주소를 출력 **/

SQL> select distinct address from student;

ADDRESS

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

서울

부산

대전

광주

대구

=> distinct를 통하여 중복값을 제거할 수 있다.

---

SQL> select name,year_emp from professor;

NAME YEAR_EMP

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

이태규 1997

고희석 2003

최성희 2005

김태석 1999

박철재 2007

장민석 2005

=> 근무 연수 구하기

 

/** 교수들 재직연수(2021년 기준) **/

SQL> select name, 2021-year_emp from professor;

 

=> 간다한 연산은 가능 (현재년도 - 해당값을가진컬럼)

: NAME 2021-YEAR_EMP

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

이태규 24

고희석 18

최성희 16

김태석 22

박철재 14

장민석 16

 

---

 

/** 학생들의 이름,학번,그리고 소속 학과의 이름을 출력**/

SQL> select student.name,student.stu_id,department.dept_name

2 from student,department

3 where student.dept_id=department.dept_id;

 

=> 2개 이상의 테이블이 포함된 질의문 ( student와 department )

: 해당 테이블의 값을 꺼내올 때는 . 을 사용하여 가져옴

NAME    STU_ID     DEPT_NAME

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

김광식 1292001 컴퓨터공학과

김정현 1292002 컴퓨터공학과

김현정 1292003 컴퓨터공학과

김현정 1292301 산업공학과

박광수 1292303 산업공학과

김우주 1292305 산업공학과

박철수 1292501 전자공학과

백태성 1292502 전자공학과

 

SQL 주석처리 방법 :  /* ~~~~~~내용~~~~~ */

 

/* 컴퓨터공학과 3학년 학생들의 학번을 검색 */

SQL> select student.stu_id

2 from student,department

3 where student.dept_id=department.dept_id AND student.year='3' AND department.dept_name='컴퓨터공학과';

 

=> 학생 테이블에는 학과가 없기 때문에 학과를 가지고 있는 테이블까지해서 2가지의 테이블을 사용한다.

3개의 조건문

 

STU_ID

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

1292001

1292002

 

다중 테이블을 사용할 경우 테이블명이 너무 길어져서 불편하다.

일시적으로 테이블명을 줄일 수 있다.

 

 

---

재명명 연산

=> 긴 테이블명 줄이는 방법 또는 필드명을 바꾸는방법 (임시적인 방법 실질적인 필드,테이블명은 바뀌지않음. 단지 쿼리문에서만 바뀌는것)

 

SQL> select s.name, d.dept_name

2 from student s, department d

3 where s.dept_id=d.dept_id;

 

=> from절에서 명시해준다.

NAME DEPT_NAME

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

김광식 컴퓨터공학과

김정현 컴퓨터공학과

김현정 컴퓨터공학과

김현정 산업공학과

박광수 산업공학과

김우주 산업공학과

박철수 전자공학과

백태성 전자공학과

 

동일 테이블이 두번 사용되는 예

 

/* '김광식' 학생과 주소가 같은 학생들의 이름과 주소를 검색 */

SQL> select s2.name, s1.address

2 from student s1, student s2

3 where s1.address=s2.address AND s1.name='김광식';

NAME ADDRESS

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

김광식 서울

김정현 서울

백태성 서울

 

 

필드명을 재정의(임시)

SQL> select name 이름, position 직책,2021-year_emp 재직연수

2 from professor;

 

이름 직책 재직연수 ( => name position year_emp 에서 한글로 바뀜 )

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

이태규 교수 24

고희석 부교수 18

최성희 부교수 16

김태석 교수 22

박철재 조교수 14

장민석 부교수 16

 

SQL> /* 3학년 4학년의 이름과 학번 검색 단, 오름 차순으로 출력 */

SQL> /* order by => 정렬 기본값 ASC */

ASC 반대는 DESC ( order by 컬럼 desc )

 

** 모든 데이터베이스에서 order by와 asc, desc는 동일하게 사용된다.

 

SQL> select name,stu_id

2 from student

3 where year=3 OR year=4

4 order by name, stu_id;

 

=> 이름으로 먼저 정렬한 후 동일이름의 데이터가 있을 경우 학번으로 재정렬

 

NAME STU_ID

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

김광식 1292001

김우주 1292305

김정현 1292002

김현정 1292003

박광수 1292303

박철수 1292501

백태성 1292502

 

SQL> select name,stu_id

2 from student

3 where year=3 OR year=4

4 order by name DESC, stu_id;

 

=> 내림차순

 

NAME STU_ID

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

백태성 1292502

박철수 1292501

박광수 1292303

김현정 1292003

김정현 1292002

김우주 1292305

김광식 1292001

---

콘솔창에서 했던걸 이클립스로 가져와서 자바로 실습

 

 

각각의 메소드를 만들어서 쿼리문을 던져줘서 실행 시켜보기.

 

콘솔창에서 나온 값들과 똑같이 이클립스에서도 실행이된다.

 

 

 

오라클 접속 관련해서 한번 더 정리

 

 

변수를 상수로 만들 경우는 대문자로 하는것이 관례. 필수는 아님

 

또한 ORACLE을 붙여준 이유는 다른사람이 봤을때 MySql 아이디 인지 Oracle 아이디 인지 구분을 바로할 수 없기 때문에 보기 쉽게 한 것.

 

주석처리 차이 : // , /* */ 이 2가지 방법은 코드를 볼려고 했을 때 나오지않지만

                    /** **/ 이런 주석은 다른 사람이 볼 수 있다.

 

 

 

 

필드명을 재정의(임시)

SQL> select name 이름, position 직책,2021-year_emp 재직연수

2 from professor;

 

이 부분의 자바 코드에서 새로 추가된 객체

메타 데이터 영역 콘솔창 출력
ResultSetMetaData rsmd = rs.getMetaData();

 

 

 

SQL*Plus에서 나오는 것처럼 컬럼 명까지 출력 됐다.