DB
DB:오라클DB， SQL， 001, 환경셋팅
최무회
2020. 9. 1. 15:35
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;
------------------------------------------------------------------------------