[DB] 1. 데이터베이스 소개

Database란?

 - 서로 연관성을 가지며, 중복 없이 지속성(영속적)으로 유지 관리해야 할 유용한 데이터들의 집합

  -- ex) 학사관리 정보, 도서관리 정보, 인사관리 정보 등...

  -- Database에 저장된 데이터는 검색뿐만 아니라 수정, 삭제 등이 용이

 

 - 데이터 저장을 위해 가장 원시적인 방법으로 데이터 파일 사용

  -- 데이터 파일 사용 시 중복된 데이터 저장이 빈번이 발생하며 대용량의 데이터를 관리하기에는 역부족

  -- 또한 다중 사용자 환경의 데이터 공유가 어렵다(주로 하나의 파일은 하나의 응용 프로그램만을 위해 사용)

  -- 보안에 취약

 

 * 이러한 파일시스템의 문제점을 극복하고, 대량의 데이터를 체계쩍으로 저장, 관리하기 위해 DataBase등장

 

DBMS란?

 - Database Managemetn System(데이터베이스 관리 시스템)

  -- 대용량의 데이터를 쉽게 저장하고, 효율적으로 검색, 수정, 삭제(CRUD)할 수 있는 환경을 제공해 주는 소프트웨어

   --- 응용프로그램과 데이터의 중재자로서 모든 응용 프로그램(Client)들이 데이터베이스를 공유하고,

        체계적으로 관리할 수 있게끔 관리

  -- 대표적인 DBMS

   --- Oracle, mySQL, MS-SQL, Informix, Sybase, DB2 등

DBMS 종류

 - 1960년대 : Flat-File(SAM)

 - 1970년대 : Network-DBMS, Hierachical-DBMS

 - 1980년대 : 관계형-DBMS

 - 1990년대 : 객체지향형-DBMS

 - 2000년대 : 객체관계형-DBMS

 

관계형 DBMS

 - 가장 대표적인 DBMS로 실세계 데이터들을 2차원적 표(테이블) 형식으로 표현

 - 데이터 무결성, 트랜잭션 처리 등 기본적 기능이 우수

 - 질의어(Query Language)를 사용한 데이터 접근

 - 데이터를 단순한 표(Relation) 형식으로 표현하여 데이터를 관리

 - 데이터베이스의 기본 데이터 저장 단위로 테이블을 사용, 테이블 간의 관계를 활용

 - 테이블을 행(Row)와 열(Column)의 2차원 행렬 구조를 가짐

 - 행(Row)은 데이터 파일의 레코드에 해당

 - 하나의 행은 서로 구분되는 속성(사원번호, 사원명, 직급, 급여, 부서번호 등)으로 구성

 

Oracle 소개

 - 가장 대표적인 DBMS 중의 하나 (회사 이름인 동시에 DBMS 제품 이름)

 - 1977년 래리 엘린슨이 설립

 - 1983년 회사 이름을 Oracle Corporation으로 변경

 - 이후 지속적으로 기능을 향상시켜 제품을 출시

 - 버전 8부터 버전번호 뒤에 I(internet)가 붙음

 - 버전 10부터 버전번호 뒤에 g(grid)가 붙음

 - 현재 버전 Oracle 11g까지 출시

 

오라클 설치 및 실행

설치 후 계정 로그인 방법 => 실행을 통해 sqlplus 실행 =>

  1. sqlplus/nolog => conn sys as sysdba

  2. sqlplus => id : system, pw:패스워드

 

계정생성

=> create user c##kosta236 identified by 1234;

 

권한부여

=> grant connect, resource, dba to kosta236; 

 

파일 연결

=> conn kosta236/1234

 

파일 불러오기

=> @파일경로 ( ex) @C:\Users\alswk\Downloads@oracle_exam2 => commit;

목록 출력

=> select *from tab; (모든 행 선택)

 

hr 계정 unlock

=> conn /as sysdba => alter user hr identified by hr account unlock; => conn hr/hr

=> select * from tab;

scott 계정 불러오기

=> conn system /1234 or conn /as sysdba

=> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

=> alter user scott identified by tiger;

=> conn scott/tiger

=> select *from tab;

 

--1. 각 학생의 평점을 검색하라(학번, 이름, 학점) : student => 별칭사용
SELECT sno "학번", sname "이 름", avr "학 점" FROM student;

--2. 각 과목의 학점을 검색하라.(과목번호, 과목명, 학점수):course => 별칭 사용
SELECT cno "과목번호", cname "과목명", st_num "학점수" FROM course;

--3. 각 교수의 직위를 검색해라.(교수번호, 교수이름, 직위):professor => 별칭
SELECT pno "교수번호", pname "교수이름", orders "직위" FROM professor;

--4. 급여를 10% 인상했을 때 각 직원마다 연간 지급되는 급여를 검색하라 : emp
--  (사원번호, 사원이름, 연봉) => 별칭
SELECt eno "사원번호", ename "사원이름", sal*1.1 "연봉" FROM emp;

--5. 현재 학생의 평점은 4.0 만점이다. 이를 4.5만점으로 환산해서 검색하라.: student
--  (학번, 이름, 환산학점) => 별칭
SELECT sno "학번", sname "이름", avr*4.5/4.0 "환산학점" FROM student;

-- 정렬 (묶음 검색) ORDER BY (데이터량이 많으면 사용하면 안됨)
SELECT eno "1", ename "2", sal"3" FROM emp
ORDER BY sal DESC;

-- 각 학과별로 교수의 정보를 부임일자 순으로 출력하라
SELECT section, pname, hiredate FROM professor
ORDER BY section, hiredate DESC;

--2. 화학, 물리학과 학생중에 1,2학년 학생을 성적 순으로 검색하라.
SELECT avr FROM student
WHERE major IN('화학', '물리')
AND syear IN(1, 2)
ORDER BY avr(DESC);

--3.화학과 정교수를 검색하라.
SELECT * FROM professor
where section = '화학' AND orders = '정교수';

--mission kosta236
--1. 2,3학년 학생 중에서 학점이 2.0에서 3.0사이의 학생을 검색하라.
SELECT * FROM student
WHERE syear IN(2, 3)
AND avr BETWEEN 2.0 AND 3.0;

-- <mission kosta236>
--1. 화학과 학생 중에 성이 '관'씨인 학생을 검색
SELECT sname, major FROM student
where major = '화학' AND sname like '관%';

--2. 부임일이 1995년 이전의 정교수를 검색
SELECT pname, orders, hiredate from professor
where hiredate < '1995/1/1'
--where hiredate <= TO_DATE('1995-01-01', 'YYYY-MM-DD')
AND orders = '정교수';

--3. 성과 이름이 각각 1글자인 교수를 검색 (이름이 2글자)
select pname from professor
where pname like '__';

--4. 화학과 학생 중에 4.5환산 학점이 3.5이상인 학생을 검색
SELECT sname, major, avr FROM student
where avr >= 3.5 / 4.5 *4.0 and Major = '화학'
order by avr desc;

--5. 화학과 이외 학과 학생의 평점을 각 학과별 그리고 학년별 순서로 출력
SELECT sname, major, syear, avr from student
where major not like '화학'
order by major, syear, avr desc;

<kosta236.sql>

 

--조건절에서 조건에 맞는 일부 데이터 추출
SELECT employee_id, last_name, hire_date FROM employees
WHERE hire_date >= '03/01/01'
AND last_name = 'King';

--연산자
--AND
--연봉 5000~10000 사이의 직원들 내역을 출력
SELECT employee_id, last_name, salary FROM employees
WHERE salary >= 5000 AND salary <= 10000
ORDER BY salary DESC;

--OR
SELECT employee_id, last_name, job_id FROM employees
WHERE job_id = 'FI_MGR' OR job_id = 'FI_ACCOUNT';

--IN
SELECT employee_id, last_name, job_id from employees
where job_id in('FI_MGR', 'FI_ACCOUNT');


--NOT 연산자
SELECT department_id, department_name FROM departments
where department_id ^= 10;
--where department_id <> 10
--where NOT department_id = 10
--where department_id != 10

/*LIKE 연산자
'김%' => "김"으로 시작하는 모든 문자열
'%과' => "과"로 끝나는 모든 문자열
'%김%' => "김"이라는 문자를 포함하는 모든 문자열
'화_' => 화로 시작하는 2글자 문자열
'_등-' => '등'이 가운데 들어간 3글자 문자열
*/

--07년도 입사한 사원의 목록을 출력
SELECT employee_id, last_name, hire_date FROM employees
WHERE hire_date LIKE '07%';

--hr>last_name 컬럼에 'a'와 'A'가 이름에 들어있지 않은 사원을 출력
SELECT employee_id, last_name FROM employees
WHERE last_name  NOT like '%a%' AND last_name NOT like '%A%';

<hr.sql>

 

--1. 문제) 부서번호가 10번인 부서의 사람 중 사원번호, 이름, 월급을 출력하라
SELECT DEPTNO, EMPNO, ENAME, SAL FROM EMP
WHERE DEPTNO = 10;

--2. 문제) 사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하라.
SELECT EMPNO, ENAME, HIREDATE, DEPTNO FROM EMP
WHERE EMPNO = 7369;

--3. 문제) 이름이 ALLEN인 사람의 모든 정보를 출력하라.
SELECT * FROM EMP
WHERE ENAME = 'ALLEN';

--5. 문제) 직업이 MANAGER가 아닌 사람의 모든 정보를 출력하라.
SELECT * FROM EMP
--WHERE JOB <> 'MANAGER';
WHERE NOT JOB = 'MANAGER';

--6. 문제) 입사일이 81/04/02 이후에 입사한 사원의 정보를 출력하라.
SELECT * FROM EMP
WHERE HIREDATE > '81/04/02'
ORDER BY HIREDATE ASC;

--7. 문제) 급여가 $800 이상인 사람의 이름, 급여, 부서번호를 출력하라.
SELECT ENAME, SAL, DEPTNO FROM EMP
WHERE SAL >= 800 
ORDER BY SAL ASC;

--8. 문제) 부서번호가 20번 이상인 사원의 모든 정보를 출력하라.
SELECT * FROM EMP
WHERE DEPTNO >= 20
ORDER BY DEPTNO ASC;

--10. 문제) 입사일이 81/12/09 보다 먼저 입사한 사람들의 모든 정보를 출력하라.
SELECT * FROM EMP
WHERE HIREDATE < '81/12/09'
ORDER BY HIREDATE ASC;

--11. 문제) 입사번호가 7698보다 작거나 같은 사람들의 입사번호와 이름을 출력하라.
SELECT EMPNO, ENAME FROM EMP
WHERE EMPNO <= 7698
ORDER BY EMPNO ASC;
 
--12. 문제) 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름,
--          월급, 부서번호를 출력하라.
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP
WHERE HIREDATE BETWEEN '81/04/02' AND '82/12/09'
ORDER BY HIREDATE ASC;

--13. 문제) 급여가 $1,600보다 크고 $3,000보다 작은 사람의 이름, 직업, 급여를 출력하라.
SELECT ENAME, JOB, SAL FROM EMP
WHERE SAL > 1600 AND SAL <3000
ORDER BY SAL ASC;

--14. 문제) 사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를 출력하라.
SELECT * FROM EMP
--WHERE EMPNO NOT BETWEEN 7654 AND 7782
WHERE NOT EMPNO >= 7654 OR NOT EMPNO <= 7782
ORDER BY EMPNO ASC;

--*****
--15. 문제) 이름이 B와 J 사이의 모든 사원의 정보를 출력하라.
SELECT * FROM EMP
WHERE ENAME BETWEEN 'B%' AND 'J%'
ORDER BY ENAME;
--*****
--16. 문제) 입사일이 81년 이외에 입사한 사람의 모든 정보를 출력하라.
SELECT * FROM EMP
WHERE HIREDATE NOT LIKE '81%';

--17. 문제) 직업이 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하라.
SELECT * FROM EMP
WHERE JOB IN ('MANAGER', 'SALESMAN')
ORDER BY JOB ASC;

--18. 문제) 부서번호와 20, 30번을 제외한 모든 사람의 이름, 사원번호, 부서번호를 출력하라.
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE NOT DEPTNO IN (20, 30);
--WHERE DEPTNO NOT IN (20, 30);

--19. 문제) 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 부서번호를 출력하라.
SELECT EMPNO, ENAME, HIREDATE, DEPTNO FROM EMP
WHERE ENAME LIKE 'S%'
ORDER BY ENAME ASC;

--20. 문제) 입사일이 81년도인 사람의 모든 정보를 출력하라
   SELECT * FROM EMP
   WHERE HIREDATE LIKE '81%'
   ORDER BY HIREDATE ASC;
   
--21. 문제) 이름 중 S자가 들어가 있는 사람만 모든 정보를 출력하라
SELECT * FROM EMP
WHERE ENAME LIKE 'S%'
ORDER BY ENAME DESC;
  
--23. 문제) 첫 번째 문자는 관계없고, 두 번째 문자가 A인 사람의 정보를 출력하라.
SELECT * FROM EMP
WHERE ENAME LIKE '_A%'
ORDER BY ENAME ASC;

--24. 문제) 커미션이 NULL인 사람의 정보를 출력하라.
SELECT * FROM EMP
WHERE COMM IS NULL;

--*****
--25. 문제) 커미션이 NULL이 아닌 사람의 모든 정보를 출력하라.
SELECT * FROM EMP
WHERE COMM IS NOT NULL;

--26. 문제) 부서가 30번 부서이고 급여가 $1,500 이상인 사람의 이름, 부서 ,월급을 출력하라.
SELECT ENAME, DEPTNO, SAL FROM EMP
WHERE DEPTNO = 30 AND SAL >= 1500
ORDER BY SAL ASC;

--27. 문제) 이름의 첫 글자가 K로 시작하거나 부서번호가 
--         30인 사람의 사원번호, 이름, 부서번호를 출력하라.
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE ENAME LIKE 'K%' OR DEPTNO = 30
ORDER BY ENAME ASC;

--28. 문제) 급여가 $1,500 이상이고 부서번호가 30번인 사원 중 직업이
--          MANAGER인 사람의 정보를 출력하라
SELECT * FROM EMP
WHERE SAL >= 1500 AND DEPTNO = 30 AND JOB = 'MANAGER';
  
--29. 문제) 부서번호가 30인 사람 중 사원번호 SORT하라.
SELECT * FROM EMP
WHERE DEPTNO = 30
ORDER BY EMPNO ASC;

--30. 문제) 급여가 많은 순으로 SORT하라.
SELECT * FROM EMP
ORDER BY SAL DESC;

<scott.sql>