오라클 SQL - 활용 시험

SQL활용.pdf

0.53MB

시험-SQL-활용-천기훈.sql

0.01MB

---

-- 1. EMP 테이블을 사용하여 사원 중에서 급여(SAL)과 보너스 (COMM) 을 합친 금액이

가장 많은 경우와 가장 적은 경우, 평균 금액을 구하세요.

 단, 보너스가 없을 경우 보너스를 0으로 계산하고 출력 금액은 모두 소수점 첫째자리만 나오게하라.

  

SELECT MAX(SAL+NVL(COMM,0)) "MAX", MIN (SAL+NVL(COMM, 0)) "MIN", ROUND

(AVG(SAL+NVL(COMM, 0)), 1) "AVG" FROM EMP;

---

 

 2번 EMP 테이블에서 데이터2개 입력

-- EMPNO , DEPTNO , ENAME , SAL

 

INSERT INTO EMP(EMPNO, DEPTNO, ENAME, SAL)

VALUES (1000, 10, '이경자', 3000);

INSERT INTO EMP(EMPNO, DEPTNO, ENAME, SAL)

VALUES (2000, 10, '최병민', 4000);

                                                                             

---

 

-- 3번 모든 사원들의 sal 10%인상

UPDATE EMP SET sal = SAL*1.1; -- 10% 인상

 

 

 

---

-- 4번 트랜잭션 관리를 위해 사용하는 명령어 2개를 쓰고 설명

 

ROLLBACK : 현재 트랜잭션에 포함된 DML관련 명령어를 수행 취소한다.

 COMMIT : 현재까지 수행했떤 트랜잭션 명령어를 데이터베이스에 영구히 반영한다.

 COMMIT 명령어는 취소가 불가능하기 때문에 트랜잭션 작업이 정상적으로 이루어졌을 때

 사용한다.

---

-- 5번 데이터 무결성의 위한 제약 조건에 대하여 서술하시오.

 5가지 CONSTRAINT(제약조건)를 제공한다.

 NOT NULL : 필수 입력 사항을 의미, 데이터 입력시에 누락이 되어서는 안된다.

 

 UNIQUE : 중복성 배제, '유일한 값'으로 존재해야 하는 의미, 해당 테이블에 동일한 데이터가 존재하면 오류가 발생

 

 PRIMARY KEY (기본키) : NOT NULL + UNIQUE 속성을 가짐, 테이블에서 대표되는 컬럼 의미

 하나의 테이블에있는 데이터들을 식별하기 위한 기준으로 인식되는 제약조건.

 

 FOREIGN KEY (외래키) : 참조하는 테이블에서 존재하는 값만 사용 가능을 의미

 해당 컬럼에 참조하는 테이블로부터 존재하는 값들만 사용해야 하며, 참조 데이터 값이 없을 경우 오류 발생,

 외래키는 여러개의 컬럼에 중복 적용 가능하며, 부모테이블과 자식테이블로 관계 맺고 있을 경우 자식테이블이 참조 하는 데이터는 부모 테이블에서 삭제 불가능하다.

 

 CHECK : 주어진 조건에 해당하는 값만 입력 가능을 의미함.

 

 

 

---

--6. 다음 ERD를 보고 데이터베이스를 구축하여라.

-- 학생, 교수, 학과 테이블만 만드세요.

select * from STUDENT;

 

create table student (

stuid number(4) primary key,

profid number(4) CONSTRAINT student_profid_fk REFERENCES professor(profid),

subid number(4) CONSTRAINT student_subid_fk REFERENCES subject(subid),

name varchar2(20),

identical  varchar2(20),

semester  varchar2(4),

address  varchar2(20),

tel  varchar2(20),

email varchar2(20),

pic blob

);

 

create table professor(

profid number(4) primary key,

subid number(4) CONSTRAINT professor_subid_fk REFERENCES subject(subid),

identical  varchar2(20),

address  varchar2(20),

name varchar2(20),

tel  varchar2(20),

email varchar2(20),

pic blob

);

create table subject(

subid number(4) primary key,

subname varchar2(20),

tel varchar2(20)

);

 

---

-- 7번. 서브쿼리문을 이용하여 '영업부'에서 근무하는 모든 사원의 이름과 급여를 출력

 

select ename, sal from emp where deptno =

(select deptno from dept where dname='영업부');

---

-- 8번. 경리부에서 근무하는 사원의 이름과 입사일 출력 ( 조인 )

select ename 사원명, HIREDATE 입사일

from emp e join dept d on e.DEPTNO=d.DEPTNO and dname ='경리부';

 

---

 

-- 9번문제  인천 근무하는 사원의 이름과 급여 출력 -조인

select ENAME 사원명, SAL 급여

from emp e join dept d on e.DEPTNO=d.DEPTNO and loc = '인천';

 

또는

select ename 사원명, sal 급여

from emp e, dept d

where e.DEPTNO = d.DEPTNO

and loc = '인천';

---

-- 10. 부서 테이블에서 부서번호를 자동으로 부여하라

create table deptno (

deptno number(4) primary key,

dname varchar2(15),

loc varchar2(15)

);

select * from USER_CONSTRAINTS where TABLE_NAME in ('DEPTNO');

 

drop sequence  DEPTNO_SEQ;

CREATE SEQUENCE DEPTNO_SEQ

INCREMENT BY 500 -- 500 씩 증가

START WITH 2000 -- 2000으로 시작

MAXVALUE 5000 -- 최대 5000까지

CYCLE NOCACHE; -- 사이클, 캐시 설정

INSERT INTO DEPTNO VALUES(deptno_seq.nextval, '김치', '서울');

INSERT INTO DEPTNO VALUES(deptno_seq.nextval, '배추', '남원');

INSERT INTO DEPTNO VALUES(deptno_seq.nextval, '꽁치', '강릉');

select * from deptno;

 

select DEPTNO_SEQ.NEXTVAL FROM DUAL;

 

select * from USER_SEQUENCES;

 

 

 

---

11. 조인의 종류에 대하여 나열하고 2개의 테이블을 임의로 생성

 

테이블 생성

drop table PLAYER;

drop table TEAM;

 

CREATE TABLE  PLAYER (

PLAYER_ID     CHAR(7) NOT NULL,

PLAYER_NAME   VARCHAR2(20) NOT NULL,

TEAM_ID       CHAR(3) NOT NULL,

E_PLAYER_NAME VARCHAR2(40),

NICKNAME      VARCHAR2(30),

JOIN_YYYY     CHAR(4),

POSITION      VARCHAR2(10),

BACK_NO       NUMBER(2),

NATION        VARCHAR2(20),

BIRTH_DATE    DATE,

SOLAR         CHAR(1),

HEIGHT        NUMBER(3),

WEIGHT        NUMBER(3),

CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),

CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)

);

 

CREATE TABLE TEAM (

TEAM_ID     CHAR(3) NOT NULL,

REGION_NAME VARCHAR2(8) NOT NULL,

TEAM_NAME   VARCHAR2(40) NOT NULL,

E_TEAM_NAME VARCHAR2(50),

ORIG_YYYY   CHAR(4),

STADIUM_ID  CHAR(3) NOT NULL,

ZIP_CODE1   CHAR(3),

ZIP_CODE2   CHAR(3),

ADDRESS     VARCHAR2(80),

DDD         VARCHAR2(3),

TEL         VARCHAR2(10),

FAX         VARCHAR2(10),

HOMEPAGE    VARCHAR2(50),

OWNER       VARCHAR2(10),

CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID)

);

 

 

  Team 값 삽입

 

 

 

SELECT * FROM TEAM;

 

n  Player 값 삽입

INSERT INTO player VALUES ('2010044','이철우','K03','LEE, CHULWOO','','2010','FW','9','',TO_DATE('30-NOV-1987','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','185','78');

INSERT INTO player VALUES ('2011057','코난','K03','Goram Petreski','','2010','FW','11','',TO_DATE('23-MAY-1982','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','180','76');

INSERT INTO player VALUES ('2000095','정민규','K03','JEONG, MINJYU','','2012','MF','35','',TO_DATE('29-SEP-1989','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','178','71');

 

 

INSERT INTO player VALUES ('2012125','에드밀손','K05','Edmilson','EDY','2012','FW','20','',TO_DATE('29-MAY-1978','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','184','79');

INSERT INTO player VALUES ('2012003','쿠키','K05','','','','FW','99','',TO_DATE('30-APR-1981','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','170','76');

INSERT INTO player VALUES ('2000087','정은익','K05','JEONG, EUNIK','','2010','FW','35','',TO_DATE('09-MAR-1991','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','176','65');

 

 

INSERT INTO player VALUES ('2012113','김정운','K08','KIM, JUNGWOON','','','FW','28','',TO_DATE('14-MAR-1993','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','170','68');

INSERT INTO player VALUES ('2011094','장동현','K08','JANG, DONGHYUN','','','FW','39','',TO_DATE('19-MAR-1992','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','178','67');

INSERT INTO player VALUES ('2007250','황연석','K08','HWANG, YOUNSUCK','','','FW','16','',TO_DATE('17-OCT-1983','DD_MON_YYYY','NLS_DATE_LANGUAGE = AMERICAN'),'1','192','81');

 

 

SELECT * FROM PLAYER;

 

COMMIT;

 

-- 조인의 종류

 

-- 1. INNER JOIN

select PLAYER_NAME, TEAM_NAME

from PLAYER p inner join TEAM t on p.TEAM_ID=t.team_id;

 

 

 

-- 2. OUTER JOIN

 

--LEFT 조인

select p.PLAYER_NAME 이름 , p.team_id 팀번호, t.team_name 팀명

from player p left join team t

on p.TEAM_ID=t.TEAM_ID;

 

-- RIGHT 조인

 

select p.PLAYER_NAME 이름 , p.team_id 팀번호, t.team_name 팀명

from player p right join team t

on p.TEAM_ID=t.TEAM_ID;

 

 

 

-- 3. CROSS JOIN

CROSS JOIN은 상호 조인이라고도 불리며,

한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능을 한다.

그래서, CROSS JOIN의 결과 개수는 두 테이블의 행의 개수를 곱한 개수가 된다.

 

SELECT *

FROM player

CROSS JOIN team;

-- 4. FULL OUTER JOIN

LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의

결과를 표시한후 한번 중복되는 값(INNER JOIN의 값)의 중복을 제거한 값을 표시한다

select p.player_id, p.player_name, p.position, t.team_name

from player p full outer join team t on p.TEAM_ID=t.TEAM_ID;

 

 

 

 

 

-- 5. SELF JOIN

 

사용하는 경우 :

1. 계층적인 구조를 테이블화 할 경우.

2. 한 테이블에서 일치하는 값을 찾고자 하는 경우.

아울러 반드시 테이블 Alias 명을 사용해 질의해야 한다

 

select p.player_name 선수명, t.team_name 팀명

from player p join team t

on p.TEAM_ID = t.TEAM_ID;