DB 복습 3
employees 테이블에서 부서를 배정받지 못한 사원들의 이름과 이메일 정보를 검색하라
SELECT Last_Name, email
FROM employees
where department_id is null;
--------------------------------------------------------------------------------------------------------------------------------------------
부서번호 오름차순으로 사원이름, 부서번호, 급여액을 표시하되 부서를 배정받지 못한 사원은 가장 먼저 출력한다
(Order by ~ nulls first/last 사용)
SELECT Last_Name, Department_ID, salary
FROM employees
ORDER BY Department_ID nulls FIRST;
or
SELECT Last_Name, Department_ID, salary
FROM employees
ORDER BY Department_ID nulls FIRST, salary ASC; // 한 컬럼을 기준으로 정렬 후 그 내에서 다른 컬럼을 기준으로 정렬
// 승점이 똑같다면 득실차로 순위를 구분하는 것과 같은 정렬
--------------------------------------------------------------------------------------------------------------------------------------------
사원들을 입사일 기준으로 고참부터 정렬하되
번호, 이름, 급여를 표시하고 컬럼명을 출력하지 않고
번호, 이름, 급여 처럼 별칭을 사용하시오
SELECT employee_id "번호", last_name "이름", salary "급여"
FROM employees
ORDER BY hire_date;
--------------------------------------------------------------------------------------------------------------------------------------------
논리표현식
(SQL은 할당식 "="이 없다) -> '=' 논리비교로서 사용(같냐 다르냐)
'!=' -> C++과 동일
'not' -> 뒤에 오는 논리값을 부정한다
부서번호가 50도 아니고 60도 아닌 사원들 중에서 입사일이 03년 이전이 아닌 사원들의 이름, 부서번호, 입사일을 표시하라
SELECT last_name "이름", department_Id "부서번호", hire_date "입사일"
FROM employees
where department_id != 50 and department_id != 60 and hire_date >= to_date('02/12/31');
--------------------------------------------------------------------------------------------------------------------------------------------
비교표현식
- Column between A and B : Column이 A와 B사이에 있다면 출력 (범위지정)
- Column In(A, B) : 각 Column의 행 값이 A 또는 B와 일치한다면 출력
사원들의 직무(Job)이 ST_Man도 아니고, ST CLERK도 아닌 사원들의 이름, 직무를 표시 (in사용할 것)
SELECT last_name "이름", job_Id "직무"
FROM employees
where job_id not in('ST_MAN', 'ST_CLERK');
--------------------------------------------------------------------------------------------------------------------------------------------
Like 패턴검색 %, _
% : 문자가 한개도 없거나 한개 이상인 경우의 와일드 카드 예) Like 'A%' -> 시작 단어가 A로 시작하는 단어 모두 출력
_ : 문자 한개 예) Like 'A__m' -> A00m(A와 m사이의 글자가 2개인 사람-_가 2개라서)
last_name이 'e'로 끝나는 모든 사원의 last_name을 표시하라
SELECT last_name "성"
FROM employees
where last_name like('%e')
Order By last_name ASC;
--------------------------------------------------------------------------------------------------------------------------------------------
case문
- case + 표현식: 정리하면 값이 나오는 것들?(1+2같은 것)(예- Column) + when + 값 + then + 결과 + (else 결과) + end
-> 표현식과 값을 비교했을때 같으면 then 뒤의 결과 출력
다르면 다음으로 넘어가서 else에서 끝(else에 있는 결과 출력)
- 표현식이 없는 경우
case + when + condition(조건) + then
10~50번 부서번호를 운영팀, 개발팀, 연구팀, 생산팀, 마케팅으로 변경하여 각 사원의 이름, 부서번호, 부서명을 표시하라
SELECT first_name "사원명", department_id "부서번호", case department_id
when 10 then '운영팀'
when 20 then '개발팀'
when 30 then '연구팀'
when 40 then '생산팀'
else '마케팅'
end 부서명
FROM employees
where department_id<=50
Order by department_id;
--------------------------------------------------------------------------------------------------------------------------------------------
03년도 입사자와 50번 부서의 직원들은 5%급여를 인상하여 사원의 이름, 입사일, 원래급여, 인상된 급여를 표시하라
SELECT first_name "사원명", hire_date "입사일", salary "원래 급여",
case when department_id = 50 then salary*1.05
when hire_date between to_date('03/01/01') and to_date('03/12/31') then salary*1.05
end "인상된 급여"
FROM employees
order by hire_date;
--------------------------------------------------------------------------------------------------------------------------------------------
SubQuery = Query 안에 있는 Query(아래에 있는 Query)
select * from employees
where department_id In(select department_id from employees);
select 안에서 select를 한번 더
부서가 50이거나 job_id가 IT_PROG인 모든 사원의 정보를 표시하라
☆☆☆다시풀기☆☆☆
->> 이런 subQuery는 '인라인뷰'라고 한다
SELECT * from
(
SELECT * from employees
where department_id = 50 or job_id = 'IT_PROG'
)
david lee와 같은 부서사람들을 출력하라
SELECT * from employees
where department_id in(select department_id from employees where first_name = 'David' and last_name = 'Lee');
--------------------------------------------------------------------------------------------------------------------------------------------
Allan과 Peter, Oliver와 같은 사무실 직원들의 이름, 부서번호, 급여를 표시하고, 부서번호 순으로 정렬하라
SELECT first_name "사원명", department_id "부서번호", salary "급여"
from employees
where department_id in
(
select department_id from employees
where first_name in('Allan','Peter','Oliver')
)
Order by salary;
또는
SELECT first_name "사원명", department_id "부서번호", salary "급여"
from employees
where department_id = Any
(
select department_id from employees
where first_name in('Allan','Peter','Oliver')
)
Order by salary;
--------------------------------------------------------------------------------------------------------------------------------------------
▣함수
값을 가져오라
예) 오늘의 날짜를 가져오라 sysdate
Select Sysdate as "오늘의 날짜" From dual;
->함수지만 파라미터가 없기 때문에 괄호가 없다
▣Ceil / floor
- Select CEIL(1.234), Floor(1.234) from dual;
-> 2 1
Ceil(A) = A바로 위의 정수
Floor(A) = A바로 밑의 정수
▣ 수학관련 함수 - 모든 언어에 있다
- ABS : 절댓값 출력
예) ABS(-10) => 10
- Sign : 부호 출력 ( -1, 0 ,1)
예) Sign(-10), Sign(0), Sign(10) -> -1, 0, 1
- Exp : 지수함수의 지수 (e^n 에서 n) -> e = 자연상수? 2.718281828....
예) Exp(1) -> 1.71818
- Sqor : 제곱근
예) SQRT(9) -> 3
- Log : 로그함수
예)Log(3,9) -> 2
- LN : 밑이 e(자연상수)인 로그
예) LN(2.718281828)
- Mod(modulor [모듈러]): 나눗셈의 나머지
예) Mod(10, 3) ->
- sin, cos, tan : 삼각함수
- Asin, ACos, ATan : ?
▣ 문자열 함수
- Length : 문자열의 길이 출력
예) Select Length('Hello') From dual; -> 5
- ASCII : 해당 문자의 아스키코드 출력
예) ASCII('A'), ASCII('a') from dual -> 65 97
- Chr : 아스키코드를 입력받아 해당하는 문자 출력
예) Chr(65) from dual -> A
- Upper / Lower : 소->대문자 / 대 -> 소문자로 변형해서 추력
예) Upper('abc'), Lower('Hello') from dual -> ABC hello
- Ltrim /Rtrim -> (좌/우)공백 없애기
- Trim -> 공백 없애기
예) 로그인시 공백이 있을 경우 공백 인식 방지
- LPad/RPad('A', B, 'C') : A 왼쪽/오른쪽으로 C를 덧붙여 B가 되게 하라
예) LPad('hello', 10, '-') from dual -> -----hello
모든 first_name에 '-' 2개를 왼쪽에 덧대서 표시하시오
Select LPAD(first_name, Length(first_name) + 2, '-')From employees;
--------------------------------------------------------------------------------------------------------------------------------------------
- Substr('A', B, C) : A를 B번째 부터 C번째 까지 잘라줭
예) (first_name, 0, 3) from employees -> 3글자
Substr을 이용하여 각 사원의 입사 연도만 추출하여 표시하라
Select SubStr(Hire_date, 0 ,2) from employees;
입사연도가 07연도인 사원의 이름과 부서번호, 입사연월일을 표시하라
Select first_name "사원명", department_id "부서번호", hire_date "입사일"
from employees
where substr(hire_Date, 0, 2) = '07'
Order by hire_date;
--------------------------------------------------------------------------------------------------------------------------------------------
Instr('A','b') : A안에 b가 있는지 -> 있으면 1 없으면0?
Replace('A', 'B') : A를 B로 대체한다
Concat('A', 'B') : A와 B를 붙여서 1개의 문자열로 만든다
first_name, last_name을 붙여서 "이름"으로 표시할 때 12자가 넘는 문자열은 13번째에 ...을 붙여서 표시하라
Select Case When Length(Concat(first_name, last_name)) > 12
then Concat(Substr(Concat(first_name,last_name),0,12), '...')
else Concat(first_name, last_name)
end "이름"
from employees;
--------------------강사님 답안------------------------------
Select Case When Length(Concat(first_name, last_name)) > 12
then Substr(Concat(first_name,last_name),0,12) || '...'
else first_name || last_name
end "이름"
from employees;
--------------------------------------------------------------------------------------------------------------------------------------------Shanta 보다 급여를 많이 받는 사원들의 이름, 급여액을 표시하라
Select first_name || ' ' || last_name "이름", salary "급여액"
From employees
where salary >
(
Select salary
from employees
where first_name = 'Shanta'
);
--------------------------------------------------------------------------------------------------------------------------------------------
▣ NVL(x, y) : x가 null이라면 y를 return, null이 아니면 x가 return
예) manager_id가 null인 자료를 null 대신 0을 출력되게하고 싶다
-> SELECT first_name, NVL(manager_id, 0) "관리자 번호"
FROM employees;
- NVL2(A, x, y) : A(Column)가 null이면 x, 아니면 y를 출력
▣ Decode(x,a,b, c,d, e,f, ......) : x가 a와 같으면 b가 return, x가 c와 같으면 d가 return, e와 같으면 f가 return.....
10 ~50 부서의 부서번호를 부서명으로 변경할 때 Decode함수를 활용하시오
SELECT last_name "사원명", Decode(department_id,10,'언리얼', 20,'자바', 30,'그래픽', 40,'파이썬', 50,'나가리') "부서명"
FROM employees
WHERE department_id <= 50
ORDER BY department_id;
--------------------------------------------------------------------------------------------------------------------------------------------
▣ 문자열을 숫자, 날짜 등으로
- To_Char(n) : 숫자(n)을 문자로
- To_Char(d, 'yy_mm_dd') : 날짜(d)를 문자로
- To_Number(t) : 텍스트(t)를 숫자로
- To_Date(t) 텍스트(t)를 날짜로
각 사원의 입사일을 조사하여 이번달이 기념일이 들어 있는 사원의 이름과 입사 월/일을 표시하라
예) Allan 08/23
☆☆☆☆☆☆다시풀기☆☆☆☆☆☆
----------------------------------------------강사님 답안------------------------------------------------------------------
Select first_name "이름", To_Char(hire_date,'mm/dd') "입사일"
From employees
where Substr(hire_date, 4,2) = Substr(sysdate,4,2)
Order by "입사일";
--------------------------------------------------------------------------------------------------------------------------------------------
2개의 테이블을 합친다
How? Join! -> A Table의 행과 B Table의 행을 연결한다
Select first_name, e.department_id, department_name
From employees e, departments d
Where e.department_id = d.department_id // 결합조건, 연결조건(국제 표준 조건은 아니다)
Order By e.department_id;
국제 표준 조건은 행조건과 연결조건을 구분하고 있다
Select first_name, e.department_id, department_name
From employees e INNER JOIN departments d // Iner join : 연결조건 안에 들어오는 것(충족하는 것)만 출력한다
ON e.department_id = d.department_id // On : 테이블간 연결조건
Order By e.department_id;
※ Inner Join / Outer Join / Full Join -> Inner는 On조건에 부합하는 요소만, Outer는 부합하지 않는 요소만, Full은 구분없이 다
각 사원의 이름, 직무명, 부서명, 최대 급여액을 표시하시오
Select first_name || ' ' || last_name "사원명", e.job_id "직무명", job_Title "상세직무명", e.department_id "부서번호", department_name "부서명", max_salary "최대 급여액"
From employees e
inner join jobs j
on e.job_id = j.job_id
inner join departments d
on e.department_id = d.department_id;
--------------------------------------------------------------------------------------------------------------------------------------------
50번 부서의 직원이름, 부서명, 근무지를 화면에 표시하라
Select first_name || ' ' || last_name "사원명", e.department_id "부서번호", d.department_name "부서명", d.location_id "지번", l.city"근무지"
From employees e
inner join departments d
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id;
-> 더 간략하게
Select first_name || ' ' || last_name "사원명", department_name "부서명", city"근무지"
From employees e
inner join departments d
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id;
--------------------------------------------------------------------------------------------------------------------------------------------
Alana의 근무지 주소
Select first_name || ' ' || last_name "사원명", street_address"근무지주소"
From employees e
inner join departments d
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id
where first_name = 'Alana';
--------------------------------------------------------------------------------------------------------------------------------------------
중복해서는 안된다 -> Constraint(table 내에 '제약조건' 탭에 있다)
- name 관례(제약조건의 타입)
1. PK(Primery Key, 주키) : 절대 중복 불가, null값도 불가 (Unique + Not Null)
2. FK(Foreign Key, 외국키) : PK를 다른 테이블에서 참조한다
employees 안에 있는 department_id는 departments(외부테이블)에 필히 존재해야 하기 때문에 foreign key로 설정해있다
3. Unique : 절대 중복 불가, Null값 가능
4. Check : 지정된 몇개의 값만 추가할 수 있다
5. Not Null: Null 불가
--------------------------------------------------------------------------------------------------------------------------------------------
Table 만드는 방법
CREATE TABLE dept_emp_names // 테이블 이름 = dept_emp_names
(
deptname VARCHAR2() // VARCHAR2 = 오라클 내에서 string으로 쓰인다, 가변적문자
)
예) Player 라는 이름의 Table을 만들어 column 지정하기
Create Table Player
(
pid VARCHAR2(10) Primary Key,
phone VARCHAR2(15),
email VARCHAR2(15),
regdate DATE,
socore NUMBER(4)
);
--------------------------------------------------------------------------------------------------------------------------------------------
새로 만든 Table에 자료 넣기
'INSERT INTO' Player (pid, phone, email, regdate, score) // 자료를 넣을 Column을 먼저 선언해주고
VALUES(100, '010-5478-9543', 'pl@naver.com', '2019/02/12', 0); // 날짜는 자동으로 날짜형으로 들어간다
// pid에 100이 아직 없으므로 넣을수 있다
자료 업데이트 하기
UPDATE Player SET score = 10 + Score where pid = 101; // where가 없는 update 문구는 없다(자료 망치는 지름길)
--------------------------------------------------------------------------------------------------------------------------------------------
Commit -> DB File System에 보내 영구적으로 변동사안을 저장한다
삭제하기
Delete From Player Where pid = 102;
커밋전 복구하는 법
ROLLBACK;
->
Delete From Player Where pid = 102; // 102의 자료 삭제
Select * From Player; // 실제로 사라져 있다
ROLLBACK; // 복구
Select * From Player; // 실제로 복구되어 있다
--------------------------------------------------------------------------------------------------------------------------------------------
게임 날짜 , 게임명, 게임시간, 팀, 점수 등은 Player 정보이긴 하나 번호, 메일 등과 같은 정보와는 궤를 달리 한다
-> GameHistory 테이블 생성
-> 누구의 정보인지가 필요하므로 게임 ID를 추가해준다
Create Table GameHistory
(
pid VARCHAR2(10) References Player(pid), // Foreign Key를 적용하여 외부(Player) Table과 연동시켰다
Game_Date Date,
Game_name VarChar2(15),
Game_time Number(10),
Game_Team VarChar2(10),
Score Number(4)
);
게임 아이디와 이메일과 게임 날짜를 출력