DB:오라클DB， SQL， 001, 환경셋팅

1. 계정 만들기

sqlplus/nolog

conn sys as sysdba/1234


*user 생성

2. 계정 생성

create user TEST192 identified by 1234;


3. 권한 수여

grant connect, resource, dba to TEST192;

conn TEST192/1234


*oracle_exam2.sql 불러오기

sqlplus/nolog

conn TEST192/1234


파일 불러오기

@D:\oracle_exam2.sql


* hr 락을 풀기 unlock

conn/ as sysdba

alter user hr identified by hr account unlock;

conn hr/hr

select*from tab;



* scott 계정 불러오기

conn system/1234


@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;


- 테이블 목록 불러오기

select*from tab;


- 테이블 구조 불러오기

DESC 테이블명


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

- SELECT 질의문 구조

-> SELECT 컬럼들 FROM 테이블

     WHERE 조건내용

     GROUP BY 그룹 조건

     ORDER BY 정렬기준


-> employees 테이블의 전체 데이터를 불러오기

-> SELECT * FROM employees



퀴즈 > HR 계정에서 employees 테이블 employee_id, last_name

     컬럼에 대한 모든 데이터를 출력하라


     select EMPLOYEE_ID, last_name from employees;


-> 별칭 as , ""

-> select employee_id as 사원번호,

     last_name "성" from employees;


- 중복된 데이터를 제거

->  SELECT DISTINCT job_id FROM employees;


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


* 정렬 묶음 검색(order by 절)

* 기본 order by 는 오름차순

-> select eno, ename, sal from emp;

     order by sal desc , eno


퀴즈 192>각 학과별로 교수의 정보를 부임일자 순으로 검색하라.

  select * from PROFESSOR;


  select section, pname, hiredate from professor

   order by section,hiredate;


* 조건에 맞는 일부데이터(row) 불러오기( WHERE 절)

* sql 문은 소대문자 구분시 문자열은 홀 다음표 -> 'King' 사용


-> SELECT employee_id, last_name, hire_date

     from employees

     where last_name = 'King';


-> SELECT employee_id, last_name, hire_date

     from employees

     where hire_date >= '03/01/01';


퀴즈

-> and 연산자는 2조건을 모두 만족할때 연산자

- hr  계정에서 사용

연봉이 5천에서 1만불 사이의 직원 내역을 출력하라.

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

-- 특정된 row 만 출력 하기

->  SELECT employee_id, last_name, SALARY

  from employees

  where salary >= 5000 and SALARY <= 10000

  order by salary;


- BETWEEN AND 연산자 사용시 조금더 편리

-> SELECT employee_id, last_name, SALARY

  from employees

  where salary BETWEEN 5000 and 10000


- OR 연산자

->   SELECT employee_id, last_name, SALARY

  FROM employees

  WHERE job_id = 'FI_MGR' OR job_id = 'FI_ACCOUNT';



- IN 연산자 (OR 연산자 동일 효과)

- OR 연산자의 반복 연산자를 줄이기 위해 사용

->   SELECT employee_id, last_name, SALARY

  FROM employees

  WHERE job_id IN('FI_MGR' ,'FI_ACCOUNT');



<mission TEST192>

1. 2,3,학년 학생 중에서 학점이 2.0에서 3.0 사이의 학생을 검색하라.

->   select sname, syear, avr from student

  where syear BETWEEN 2 and 3 and avr between 2.0 and 3.0;


2. 화학,물리학과 학생중에 1,2학년 학생을 성적 순으로 검색하라.

->   select syear,major,avr from student

  where major in('화학','물리')

  order by avr desc; 


3. 화학과 정교수를 검색하라.

  select * from PROFESSOR

   where section = ('화학') and orders in('정교수');


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

* NOT 연산자

  - 10번 부서 이외의 내역을 보고 싶을때

-> SELECT department_id, departmet_name

     FROM departments

     WHERE NOT department_id = 10;



-> SELECT department_id, departmet_name

     FROM departments

     WHERE department_id <> 10;


* IS Not null


-> SELECT employee_id, last_name, commission_pct

     FROM employees

       WHERE COMMISSION_PCT is not null;

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

* LIKE 연산자

- 문자열 검색시 % 연산자와 많이 사용됨, 예: LIKE %a% ,

- 예2: '김%' => 김으로 시작하는 모든 문자열 예) 김길동, 김박사, 김삿갓

- 예3: '%과' =>'과'로 끝나는 모든 문자열 예) 화학과, 인사과

- 예4: '%김%' => '김'이라는 문자를 포함하는 모든 문자열 예) 김씨, 돌김, 삼각김밥,

'화_' => '화'로 시작하는 2글자 문자열 예) 화약, 화분

'_등_' => '등'이 가운데 들어간 3글자 문자열 예) 고등어, 영등포, 우등생,

-- //////////////////////////////


예제:

- 07년도에 입사한 사원의 목록을 출력하라.

->  SELECT employee_id, last_name, hire_date

     FROM EMPLOYEES

       where hire_date BETWEEN '07/01/01'

     AND '07/12/31';


-> SELECT employee_id, last_name, hire_date

     FROM EMPLOYEES

     where hire_date LIKE '07%';


퀴즈 hr 계정에서 사용

퀴즈 hr > last_name 예 'a' 가 들어가지 않는 사원을 출력하라.


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

--   퀴즈

--1. 화학과 학생 중에 성이 '관'씨 인 학생을 검색하라.

  select *from STUDENT;

  select sname, major from student

  where sname like '관%'

  and major = '화학';


--2. 부임일이 1995년 이전의 정교수를 검색하라.

  select *from PROFESSOR;

  select pname, orders, hiredate

  from PROFESSOR

  where hiredate <= '95/01/01';


--3. 성과 이름이 각각 1글자인 교수를 검색하라. (이름이 2자)

  select pname , ORDERS

  from PROFESSOR

  where pname like '__';


--4. 화학과 학생 중에 4.5환산 학점이 3.5이상인 학생을 검색하라.

  select *from STUDENT;

  select sname, major, AVR

  from student

  where AVR/4*4.5 >= 3.5

  and major = '화학';


--5. 화학과 이외 학과 학생의 평점을 각 학과별 그리고 학년별 순서로 출력하라.

  select *from student;

  select sname , syear, major,avr

  from student

--  where major != '화학'

--  where not major = '화학'

  where major <> '화학'

  order by 3,2;


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

* 그룹함수


- sum(), avg(), max(), count()

- select sum(salary) from employees;


select sum(salary) from employees;

  select avg(salary) from employees;

  select max(salary) from employees;

  select MIN(salary)  from employees;

  select count(salary) from employees;

  select count(*) from employees;


* GROUP BY


- 부서별 평균급여를 검색하라 .

->  SELECT department_id, FIRST_NAME, avg(SALARY)

     FROM employees

     GROUP BY department_id, FIRST_NAME;


MISSION

--1. 화학과 학년별 평균 학점을 검색하라.


  SELECT SYEAR, AVG(avr)

  from student

  group by syear;

--2. 각 학과별 학생수를 검색하라.

  select major, count(major)

  from student

  group by major;

--3. 화학과 생물학과 학생을 4.5환산 학점의 평균을 각각 검색하라.

  select major,syear, avr, avg(avr*4.5/4)

  from student

  where major in('화학','생물')

  and avr is not null

  group by major,syear, avr

  order by major desc,syear;




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