💖 [ORACLE]_23.02.23

🧡 복습

select
from
where
order by
offset 5 rows
fetch first 5 row only

🧡 2day 복습 문제 풀이

🔽 스스로 풀이

-- 스스로 풀이 
-- 1.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE = '2014-03-15';

-- 2. 
SELECT LAST_NAME, ROUND(SYSDATE-HIRE_DATE) WORKING_DAYS
FROM EMPLOYEES;

-- 3. 
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'YYYY') HIRE
FROM EMPLOYEES ; 

-- 4. 
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'YYYY') HIRE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2011';

-- 5. 
SELECT LAST_NAME, SALARY, UPPER(LAST_NAME)
FROM EMPLOYEES
WHERE UPPER(LAST_NAME) = 'KING';

-- 6. 
SELECT FIRST_NAME, LAST_NAME, substr(FIRST_name, 1, 3)||substr(last_name, -2)USERID
FROM EMPLOYEES; 

🔽 해설 비교

-- 해설 비교 

-- 1. 
//AS(알리아스, 별칭)은 WHERE 절에 사용 불가 
// 날짜 데이터를 문자형으로 변환하면 세션을 변경할 필요없이 원하는 형태의 날짜 데이터로 변경 가능 
select last_name, to_char(hire_date, 'yyyy/mm/dd') hire_date
from employees
where hire_date=to_date
('15-MAR-14','DD-MON-RR');

-- 2. 
select last_name, trunc(sysdate- hire_date) working_days
from employees;
// 출력 값이 다를 수 있음 

--3. 
select last_name, to_char(hire_date, 'yyyy') hire_year
from employees;

--4. 
//AS(알리아스, 별칭)은 WHERE 절에 사용 불가 
select last_name, to_char(hire_date, 'yyyy') hire_year
from employees
where to_char(hire_date,'yyyy')='2011';

--5. 
//AS(알리아스, 별칭)은 WHERE 절에 사용 불가 
select last_name, salary
from employees
where upper(last_name) ='KING';
// where LOWER(last_name) ='king';

--6. 
// SUBSTR 함수: 문자열 일부 추출
// 문자열 결합 : || OR CONCAT 함수 사용 

select first_name, last_name,              
    substr(first_name, 1,3)||substr(last_name,-2) 
    userid
from employees;

select first_name, last_name,
concat(substr(first_name, 1,3),substr(last_name, -2)) 
userid
from employees;

🧡 SQL 스크립트 실행 실습

🔽 DATETYPE.SQL

-- DATETYPE.SQL 
alter session set nls_date_format='yyyy/mm/dd';
update emp
    set hiredate=to_date('1980/12/17 13:20:19','yyyy/mm/dd hh24:mi:ss')
    where empno=7369;
select * from emp;

-- 
SELECT ENAME, TO_CHAR(HIREDATE, 'YY/MM/DD') HIREDATE
FROM EMP 
WHERE TO_CHAR(HIREDATE, 'YY/MM/DD') = '80/12/17'; 

-- SELECT * FROM EMP 
-- WHERE EMPLOYEES;
-- HIREDATE 데이터 타입은 ORACLE 내 hh:mi:ss 도 인식됨. 
-- 따라서 세션(SESSION) 형식이 동일하더라도 반드시 TO_CHAR 로 데이터 타입 변환 후 WHERE 절 내 날짜 데이터 비교 필 

-- SQL 튜닝 고려하지 않은 예
SELECT * 
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYY/MM/DD') = TO_DATE('1980/12/17', 'YYYY/MM/DD'); 

SELECT * 
FROM EMP
WHERE HIREDATE between 
to_date('19801217010000', 'YYYYMMDDHHMISS') 
and to_date('19801217120000', 'YYYYMMDDHHMISS');

SELECT * 
FROM EMP
WHERE HIREDATE between
to_date('1980/12/17', 'yyyy/mm/dd') 
and to_date('1980/12/17', 'yyyy/mm/dd')- 1/86400;
-- 1초 : 1 / (24*60*60) = 1 / 86400

rollback; 

-- emp 테이블에서 11월에 입사한 사원 추출
-- 컬럼 변경 필요 like, to_char 활용 가능 

SELECT ename, hiredate 
FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM') = '11'; 
 
-- or 

SELECT ename, hiredate 
FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM') like '11'; 

-- or 

select * from emp
where hiredate like '%/11/%';  

-- 질의문을 충족하지 못한 예시 
select * from emp
where hiredate like '%11%' ; 
-- 11을 가진 모든 hiredate가 출력됨. 

-- 테이블 상 변화 발생시 to_char 사용
WHERE TO_CHAR(HIREDATE, 'MM') = '11';

🧡 5장 변환 함수 및 일반 함수

🤍 CASE 표현식

• IF-THEN-ELSE 문 작업을 수행하여 조건부 조회를 편리하게 수행하도록 함

 
--* CASE 문의 사용법
--CASE문은 여타 다른 프로그래밍 언어들과 같이 조건이나 값에 해당하는 결과값을 RETURN하는데 사용한다.
--CASE문에는 사용법은 같지만 조건이 다른 2가지 방식이 존재한다. 값에 따라 결과값을 RETURN하는 방식과 조건에 따라 결과값을 RETURN하는 방식이 그것이다.
-- null값 표현하여 추출시 case 구문 내  else 생략 가능
--첫번째 방식
/* CASE 대상값 WHEN 값1 THEN 결과1
            WHEN 값2 THEN 결과2
                        .
                        .
                        .
            WHEN 값n THEN 결과m END;*/
--두번째 방식          
/*CASE WHEN 조건1 THEN 결과1
     WHEN 조건2 THEN 결과2
                .
                .
                .
     WHEN 조건n THEN 결과m END;*/

-- 

+) 교재 103p 문구 수정
Scalar Subquery -> searched case expression
Searched Case Expression은 'Condition'을 사용하여 Return 값이 정해짐

🤍 DECODE 함수

• IF-THEN-ELSE 문의 작업을 수행하여 조건부 조회를 수행
• decode 행 as 지정 필
• DECODE 함수는 다양한 언어에서 사용되는 IF-THEN-ELSE 논리와 비슷한 방식으로 표현식을 디코딩합니다.
• DECODE 함수는 표현식(expression)을 각 검색 값(search)과 비교해서 동일한 값이면 result를 반환합니다.
• 기본값(default)이 생략된 경우 검색 값과 일치하는 결과 값이 없으면 null 값이 반환됩니다.
  
  

🤍 DECODE 실습

🔽 SQL_실습 32p

-- 조건부 표현식 실습 
select ename, deptno, sal
    case deptno when 10 then sal *1.1
                when 20 then sal *1.2
                when 30 then sal *1.3
                else sal
    end as inc_sal 
from emp; 
--
select ename, deptno, sal, decode(deptno, 10, sal*1.1
                                          20, sal*1.2
                                          30, sal*1.3, sal) as inc_sal 
from emp; 
--
select ename, deptno, sal, 
        case when deptno in (10, 20) then sal *1.1
             when deptno = 30        then sal *1.2
        end as inc_sal 
from emp; 
--

🧡 6장 그룹 함수

• 사용 가능한 그룹 함수 소개
• GROUP BY 절을 사용하여 데이터 그룹화
• HAVING 절을 사용하여 그룹화된 행 제한

• 그룹 함수는 행 집합 연산을 수행하여 그룹별로 하나의 결과를 산출합니다.

• 그룹 함수 개념

  • 단일 행 함수와 달리 그룹 함수는 행 집합에 대해 실행되어 그룹당 하나의 결과를 산출합니다.
  • 테이블 전체가 행 집합이 되거나 테이블을 여러 그룹들로 분할하여 행 집합들이 될 수 있습니다

• 그룹 함수 유형

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM
  • STDDEV
  • VARIANCE

• 그룹 함수 사용

  • 숫자 데이터에 대해 AVG, SUM, MIN, MAX, COUNT 사용
    가능합니다.
  • 문자 및 날짜 데이터에 대해 MIN, MAX, COUNT 사용 가능합니다
  • 그룹 함수는 열에 있는 null 값을 무시합니다.

• SUM
  해당 컬럼 값들에 대한 총합을 구하는 함수이다.

SELECT SUM(SAL) FROM EMP;
--> EMP테이블에 있는 SAL컬럼 값의 총합을 구한다. 

• AVG
  해당 컬럼 값들에 대한 평균을 구하는 함수이다.

SELECT AVG(SAL) FROM EMP;
--> EMP테이블에 있는 SAL컬럼 값의 평균을 구한다. 

• MAX, MIN
  MAX는 해당 컬럼 값들에 대한 최대값, MIN은 최소값을 구하는 함수이다.

SELECT MAX(SAL), MIN(SAL)
FROM EMP;
--> EMP테이블에 있는 SAL컬럼 값의 최대값과 최소값을 구한다. 

+)

• 그룹 함수의 결과는 1개이기 때문에 그룹 함수를 적용하지 않은 단순 컬럼과는 같이 올 수 없다. (GROUP BY의 기준이 된 컬럼은 올 수 있다. )

SELECT MAX(SAL), ENAME
FROM EMP;
--> MAX(SAL)값은 1개이지만 ENAME값은 값이 여러 개이기 때문에 오류가 발생한다. 

• 그룹 함수 및 Null 값

  • NVL 함수는 강제로 그룹 함수에 null 값이 포함되도록 합니다.

• 1-> null 제외 평균 구하기 (합계/20)

• 2-> null 포함 평균 구하기 (합계/4)

🔽

• COUNT 함수 사용

• COUNT 함수의 세가지 사용법

   COUNT(*)
  SELECT 문의 조건을 충족하는 테이블의 행 수를 반환하며 여기에는 중복 행 과 열에 null 값을 포함한 행이 포함됩니다.
   COUNT(expr)
  expr에 의해 식별되는 열에 있는 null이 아닌 값의 수를 반환합니다.
   COUNT(DISTINCT expr)
  expr에 의해 식별되는 열에 있는 고유하고 null이 아닌값의 수를 반환합니다.

• 데이터 그룹 생성

• 데이터 소그룹 생성 : GROUP BY

  • 지금까지의 설명에서는 모든 그룹 함수가 테이블 전체를 하나의 커다란 정보 그룹으로
    취급했습니다.
    그러나 테이블을 더 작은 그룹들로 나눠야 하는 경우도 있습니다.
    이러한 작업은 GROUP BY 절을 사용하여 수행할 수 있습니다.

• 데이터 그룹 생성 : GROUP BY 절

• GROUP BY 절 사용시 주의사항

• select 항목에 그룹으로 묶이는 속성은 반드시 group by로 묶어 줘야 한다. (튜플 수가 달라 null 발생)

• group by로 묶은 속성을 select 절에 반드시 기재할 필요는 없음(가독성 위함)
  
  
  🔽 그룹 함수에 속하지 않는 SELECT list의 모든 열은 GROUP BY 절에 있어야 함
  
  

• GROUP BY로 투플을 그룹으로 묶은 후 SELECT 절에는 GROUP BY에서 사용한 <속성>과 집 계함수만 나올 수 있다.

  • § 맞는 예
    SELECT custid, SUM(saleprice) FROM Orders
    GROUP BY custid;
    • • 틀린 예
      SELECT bookid, SUM(saleprice) / SELECT 절에 bookid 속성이 올 수 없다 / FROM Orders
      GROUP BY custid;

• 여러 열을 GROUP BY 절에 사용
  🔽
  

• where절에는 group by 사용 불가 (having 함수 사용 필)
  🔽
  

• HAVING 절을 사용하여 그룹 결과 제한
  🔽 HAVING 절을 사용하여 그룹 결과 제한
  

• HAVING 절 주의 사항

• sql_실습 32p

  • WHERE 절과 HAVING 절의 차이점
    
    HAVING 절은 WHERE 절과 비슷하지만 그룹 전체 즉, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있습니다.
    반면, WHERE 절은 개별 행에 적용됩니다. 쿼리에는 WHERE 절과 HAVING 절이 모두 포함될 수 있습니다.
    
    🔽 이미 존재하는 데이터 값에서 그룹핑 실행
    
    🔽 그룹핑 실행 후 새로운 그룹에 조건 부여 (조건 충족하는 데이터가 없을 수 있음)
    

• 그룹 함수 중첩
  
  🔽 최고 평균 급여를 표시
  

• sql_실습 (• GROUP BY, HAVING 절 사용)
  🔽 실습 진행

-- 조건부 표현식 실습 
select ename, deptno, sal
    case deptno when 10 then sal *1.1
                when 20 then sal *1.2
                when 30 then sal *1.3
                else sal
    end as inc_sal 
from emp; 
--
select ename, deptno, sal, decode(deptno, 10, sal*1.1
                                        20, sal*1.2
                                        30, sal*1.3, sal) as inc_sal 
from emp; 
--
select ename, deptno, sal, 
        case when deptno in (10, 20) then sal *1.1
             when deptno = 30        then sal *1.2
        end as inc_sal 
from emp; 
--

-- GROUP BY, HAVING 절 사용
SELECT deptno, SUM(sal)
 FROM emp
 GROUP BY deptno ;
 
 SELECT SUM(sal)
 FROM emp
 GROUP BY deptno ;
 
 -- 
  SELECT deptno, SUM(sal) FROM emp
 GROUP BY deptno ;
 
   SELECT SUM(sal) FROM emp
 GROUP BY deptno ;
 --
  SELECT deptno, job, SUM(sal) FROM emp
 GROUP BY deptno, job ;
-- where 절 그룹함수 사용 불가 
--SELECT deptno, AVG(sal) FROM emp
-- WHERE AVG(sal) > 1500
-- GROUP BY deptno ;

SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno 
 HAVING AVG(sal) > 2500; 
-- or 
 SELECT  AVG(sal) FROM emp
GROUP BY deptno 
 HAVING AVG(sal) > 2500; 
 
-- 
SELECT deptno, SUM(sal) FROM emp
 WHERE deptno IN (20,30) GROUP BY deptno
 HAVING SUM(sal) > 10000 ;

SELECT deptno, SUM(sal) FROM emp
 GROUP BY deptno HAVING SUM(sal) > 10000
 AND deptno IN (20,30) ;
--
 SELECT deptno, AVG(sal) FROM emp
 WHERE JOB = 'CLERK'
 GROUP BY deptno HAVING AVG(sal) > 1000 ;

-- 그룹 함수의 중첩
SELECT SUM(sal)
 FROM emp
 GROUP BY deptno ;
  
 SELECT MAX(SUM(sal))
 FROM emp
 GROUP BY deptno ;
 
 
 SELECT AVG(MAX(SUM(sal)))
 2 FROM emp
 3 GROUP BY deptno ;
SELECT AVG(MAX(SUM(sal)))

🧡 7장. 조인을 사용하여 다중 테이블의 데이터 표시

• 단원 내용

  • Equijoin 및 Nonequijoin 문 작성
  • Outer Join의 이해
  • Self Join의 이해
  • Cartesian Product

• 조인 명령어 구문

• 공통 컬럼명에 조건을 주어 내부조인(INNER JOIN) 수행

• ANSI Join 사용(Equi Join)

  SQL> SELECT e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc
  FROM emp e JOIN dept d
  ON e.deptno = d.deptno ;

• Oracle 조인 구문 : Equijoin

• FK 설정시 참조하는 테이블 명 기재 필수
  🔽
  

• 세 개 이상의 테이블 조인

  SELECT e.last_name, d.department_name, l.city
  FROM employees e, departments d, locations l
  WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id;

• Oracle 조인 구문 : Nonequijoin

  • 1. 정의

  • 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다.

  • Non EQUI JOIN의 경우에는 “=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행하는 것이다.

  • 두 개의 테이블이 PK-FK로 연관관계를 가지거나 논리적으로 같은 값이 존재하는 경우에는 “=” 연산자를 이용하여 EQUI JOIN을 사용한다.

  • 그러나 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에는 EQUI JOIN을 사용할 수 없다.

  • 이런 경우 Non EQUI JOIN을 시도할 수 있으나 데이터 모델에 따라서 Non EQUI JOIN이 불가능한 경우도 있다.

• Nonequijoin 실습

• 부서가 할당되지 않은 사원까지 모든 사원과
  사원이 할당되지 않은 부서까지 모든 부서를 함께 표시
  🔽

SELECT e.last_name, e.department_id, d.department_name 
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION 
SELECT e.last_name, e.department_id, d.department_name
FROM e.last_name, e.department_id, d.department_name
WHERE e.department_id(+) = d.department_id;

• Oracle 조인 구문 : Self Join

• 셀프 조인시 테이블명 AS 필
  🔽
  

• 셀프 조인 예시

-- 중복을 찾아 표시 
SELECT C1.ID, C1.NAME, C2.ID, C2.NAME
FROM COLOR C1, COLOR C2
WHERE C1.NAME = C2.NAME
AND C1.ID < C2.ID;

🔽

• 외부 조인

-- 전 부서에 대한 데이터가 추출되는 외부 조인
-- outer join 실행 후 조건 실행됨 
select e.last_name, e.department_id, d.department_name, e.salary 
from employees e, departments d
where e.department_id(+) = d.department_id
and e.salary > 10000; 

-- outer join 까지 수행하기 위해 아래와 같이 실행 
-- salary 조건 충족 후 outer join 실행됨 
-- 전 부서에 대한 데이터가 추출
select e.last_name, e.department_id, d.department_name, e.salary 
from employees e, departments d
where e.department_id(+) = d.department_id
and e.salary(+) > 10000; 

-- or 
SELECT e.last_name, e.department_id, d.department_name, e.salary                                     
  FROM employees e                             
  RIGHT OUTER JOIN departments d                
  ON (e.department_id = d.department_id)
and e.salary(+) > 10000; 

-- 
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id; 

• NATURAL JOIN

SELECT e.employee_id, e.last_name, e.salary, e.department_id, d.department_id, d.department_name 
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id 

• 오른쪽 외부 조인 예시 (right outer join)
  right (outer) join 사용시 왼쪽 테이블에 null 값이 생성됨

select e.employee_id, e.last_name, e.salary, e.department_id, d.department_id, d.department_name 
from employees e, departments d
where e.department_id (+) = d.department_id; 

-- 5장 연습
-- 6장 연습
-- 7장 연습- oracle / ansi

🤍 5장 연습문제 풀이