2021년 12월 21일 화 💨안개
프롤로그
개강한지 벌써 일주일이 지났습니다. 오늘은 중간평가테스트를 보아서 테스트 내용위주로 지금까지 배운내용을 다시한번 정리해보도록 하겠습니다.
1. Oracle DBMS를 설치하여 환경 세팅을 한 후 SQL-Plus를 활용하여 관리자계정으로 접근할 수 있도록 구현하라.
<설치>
- 오라클 사이트 접속 > oracle 21c XE Version 다운로드
<접속>
- sql plus를 실행하여 접속확인, 관리자계정으로 접속
사용자명: system/ 비밀번호:
2. 관리자 계정으로 Oracle에 접속한 후 다음과 같은 셋팅을 진행한다.
가. 계정은 kitri, 암호는 kitri로 접근 가능한 계정을 DDL문을 통해 생성하라.
DDL문 생성하기 전에 c## 해제하기 위해서
ALTER SESSION SET "_ORACLE_SCRIPT" = true;
<계정생성>
- CREATE USER kitri IDENTIFIED BY kitri;
나. kitri 계정에게 DBA 권한을 부여할 수 있도록 하라.
1) cmd에서 접속하기
- cmd 실행> sqlplus 접속> sqlplus system/1234
2) 관리자권한으로 접속
- cmd 실행> sqlplus "/sysdba
3) 계정 생성
- CREATE USER sampleid IDENTIFIED BY samplepw;
4) 권한 부여
- 계정을 생성 한 후에 권한필요
- CONNECT(접속 권한), RESOURCE(객체 및 데이터 조작 권한), DBA를 설정했습니다.
GRANT CONNECT, RESOURCE, DBA to sampleid;
5) commit
6) 계정 생성 확인
SELECT * FROM all_users;
3. 다음 조건에 맞는 제약조건을 설정한다.
<테이블 생성>
CTEATE TABLE sugang
(
studno number(5),
subno number(5),
regdate date,
result number(3)
);
<제약조건 추가>
ALTER TABLE sugang ADD CONSTRAINT PK_sugang PRIMARY KEY (studno, subno);
4. 다음 실습 문항에 대한 쿼리를 작성하시오. (실습 환경, scott/tiger 계정 샘플 DB Table)
가. 사원수가 3명 이상인 부서명과 인원수를 출력하시오.
SELECT d.dname, COUNT()
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING COUNT() >= 3;
나. 직무 중 가장 적게 수입을 가지는 직무의 평균 월급을 출력하시오.
SELECT AVG(sal + NVL(comm,0)) AS "평균 월급"
FROM emp
WHERE job = (SELECT job
FROM emp
GROUP BY job
HAVING MIN(sal) = (SELECT MIN(sal)
FROM emp));
다. 전체 사원 번호, 사원 이름, 부서 번호, 부서 이름을 출력하시오.
SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
라. 부서가 30이고, 급여가 1500 이상인 사원의 이름, 급여, 부서명, 부서번호를 출력하시오.
SELECT e.ename, e.sal, d.dname, d.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.deptno = 30
AND e.sal >= 1500;
마. 사원수가 5명이 넘는 부서의 부서명과 사원수를 출력하시오.
SELECT d.dname, COUNT()
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING COUNT() > 5;
바. ADAMS 사원이 근무하는 부서이름과 지역이름을 출력하시오.
ADAMS 사원이 테이블에 없어서 SELECT 하기 전에INSERT 먼저 실행
INSERT INTO emp
VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('13-JUL-87', 'DD-MON-YY', 'NLS_DATE_LANGUAGE=ENGLISH')-51, 1100, NULL, 20);
COMMIT;
SELECT d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'ADAMS';
사. NEW YORK이나 DALLAS지역에 근무하는 사원들의 사원번호, 사원이름을 사원번호 순으로 검색하시오.
SELECT e.empno, e.ename
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.loc In ('NEW YORK', 'DALLAS')
ORDER BY e.empno ASC;
