sqldeveloper-16

npm init -y; npm pkg set type ="module";

 

:: Node.js 백엔드 프레임워크

1) express.js : 확장기능(60~70%)

2) Koa.js : 확장기능 (20~30%)

3) 내장 http 모듈 : 기본 기능

 

 

-- 서브쿼리(Subquery)

-- 쿼리문 안에 작성하는 또다른 쿼리문 (SQL 문장 안에 또다른 SELECT ~ 쿼리문)

-- * 서브쿼리 없이도 SQL 문장으로 데이터를 조회/입력/수정/삭제

 

-- 서브쿼리 종류

-- 1) 사용위치에 따른 구분 : SELECT, FROM, WHERE (일반적인 서브쿼리 사용위치)

-- 2) 쿼리 실행 결과에 따른 구분 : 단일 행, 다중 행, 다중 컬럼 서브쿼리

-- 3) 연관성(메인 -서브쿼리의 관련성)에 따른 구분 : 상호연관 서브쿼리

 

:: 서브쿼리

1. 정의

SELECT 문 안에 또다른 SELECT 문, 보통 WHERE 절에
많이 사용함

2. 서브쿼리 사용시 이점

:: 데이터베이스 활용한 CRUD 웹앱
1. 요구사항 분석
2. 설계
3. 구현
4. 에러수정,..유지보수

:: 토이 프로젝트
- 할일
- 메모
- 블로그
- 게시판
- 성적처리
- 병원 환자관리
- 숙소 관리
- XXX 등록/관리 시스템
└ 데이터베이스 연동 + 프런트엔드 기술
 - MariaDB             +  HTML/CSS/JS                             
                              jQuery 4.0  -->   React.js / Vue.js / Angular2.0
- [ Back End ]     +     [ Front End] ==> Fullstack

:: Node.js 백엔드 프레임워크
1) express.js   : 확장기능 (60~70%)
2) Koa.js        : 확장기능 (20~30%)
3) 내장 http 모듈 : 기본 기능

https://meet.google.com/you-aixi-naw

Meet

 

-- 서브쿼리(Subquery)

-- 쿼리문 안에 작성하는 또다른 쿼리문 (SQL 문장 안에 또다른 SELECT ~ 쿼리문)

-- ※ 서브쿼리 없이도 SQL 문장으로 데이터를 조회/입력/수정/삭제

-- 서브쿼리 종류

-- 1) 사용위치에 따른 구분 : SELECT, FROM, WHERE (★ 일반적인 서브쿼리 사용위치)

-- 2) 쿼리 실행 결과에 따른 구분 : 단일 행, 다중 행, 다중 컬럼 서브쿼리

-- 3) 연관성(메인-서브쿼리의 관련성)에 따른 구분 : 상호연관 서브쿼리

-- Q. 사원 테이블에서 가장 많은 급여를 받는 사원의 정보를 조회하시오

-- 1) MAX(salary) : 가장 많은 급여를 파악

-- 2) 급여가 최고금액인 사원을 필터링

-- 1) 최고 급여 조회

SELECT  MAX(salary) AS max_sal

FROM    employees; -- 24000

-- 2) 급여가 최고 급여와 일치하는 사원을 조회

SELECT  employee_id, last_name, department_id

FROM    employees

WHERE   salary = 24000;

-- 3) 서브쿼리를 이용해 1) 과 2)를 하나의 쿼리로 작성

-- 메인 쿼리

-- 서브 쿼리 : WHERE 절에 작성하는 위치에 따른 SQL 문장, () 사이에 SELECT 이하 작성

SELECT  employee_id, last_name, department_id

FROM    employees

WHERE   salary = ( SELECT  MAX(salary) AS max_sal

                   FROM    employees );

-- Q. 가장 작은 급여를 받는 사원의 정보중 사번, 이름(성), 부서번호를 조회하시오

SELECT  employee_id, last_name, department_id

FROM    employees

WHERE   salary = ( SELECT  MIN(salary) AS max_sal

                   FROM    employees );

-- 서브쿼리 종류

-- I. 쿼리 실행 결과행에 따른 구분

-- 1) 단일 행 (단일 컬럼) 서브쿼리 : 결과행이 하나, MAX(), MIN() 그룹함수를 사용하는 특성(★)

-- 1) 다중 행 (단일 컬럼) 서브쿼리 : 결과행이 여러개(★)

-- 1) 다중 컬럼 서브쿼리 : 여러 컬럼을 동시에 입력, 수정하는 경우에 사용 ==> 마감성 테이블에 데이터를 입력

-- II. 사용위치에 따른 구분

-- 1) WHERE 절 : 가장 일반적인 사용위치(★)

-- 2) SELECT 절 : 컬럼명 처럼 서브쿼리 실행결과가 단일 컬럼으로 ==> 스칼라(Scalar) 서브쿼리

-- 3) FROM 절 : 테이블처럼 서브쿼리 실행결과가 임시 테이블 형태로 ==> 인라인 뷰(Inline View) 서브쿼리

-- II. 연관성에 따른 구분

-- 1) 상호연관 서브쿼리 : 메인쿼리와 서브쿼리 컬럼이 연관(=조인연산)성을 갖는 경우

-- ============================================================================

--- 서브쿼리 사용예시

-- ============================================================================

-- I. 쿼리 실행 결과행에 따른 구분

-- 1. 단일 행 서브쿼리

-- Q. 사원중에서 사번이 108번에 해당하는 사원이 받는 급여보다 많은 (적은) 급여를 받는 사원을 조회하시오

-- 1-1) 사번이 108번에 해당하는 사원의 급여 조회

SELECT  salary

FROM    employees

WHERE   employee_id = 108; -- 12008

-- 1-2) 전체 사원중에서 108번 사원 급여와 비교해서 많은 (적은) 급여를 받는 사원을 조회

SELECT  *

FROM    employees

WHERE    salary > 12008; -- 12008

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary > ( SELECT salary

                   FROM employees

                   WHERE    employee_id = 108 );

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary < ( SELECT salary

                   FROM employees

                   WHERE    employee_id = 108 );

-- 2. 다중 행 서브쿼리 : IN, ANY/SOME, ALL, EXISTS

-- 2-1) IN 연산자

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary IN ( SELECT salary

                   FROM employees

                   WHERE    department_id = 80 );

-- 비교연산자 + 추가 연산자 필요

--ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.                  

-- 2-2) ANY/SOME : 어느~도, 어떤~든지   |   일부, 몇몇의~

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary > ANY ( SELECT salary

                   FROM employees

                   WHERE    department_id = 80 );

                   

                   

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary > ( SELECT MIN(salary)

                   FROM employees

                   WHERE    department_id = 80 );                  

                   

SELECT  MAX(salary), MIN(salary)

FROM    employees

WHERE   department_id = 80; -- MAX: 14000,  MIN: 6000

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary < ANY ( SELECT salary

                   FROM employees

                   WHERE    department_id = 80 );

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary < ( SELECT MAX(salary)

                   FROM employees

                   WHERE    department_id = 80 );

                   

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary = ANY ( SELECT salary

                   FROM employees

                   WHERE    department_id = 80 );

       

-- 2-3) ALL : 서브쿼리 반환 결과와 모두 비교해서~

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary > ALL ( SELECT salary

                       FROM employees

                       WHERE    department_id = 80 ); -- 12008 ~ 6900

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary >  ( SELECT MAX(salary)

                       FROM employees

                       WHERE    department_id = 80 );

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary < ALL ( SELECT salary

                       FROM employees

                       WHERE    department_id = 80 ); -- 12008 ~ 6900

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary <  ( SELECT MIN(salary)

                       FROM employees

                       WHERE    department_id = 80 );

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   salary = ALL ( SELECT salary

                       FROM employees

                       WHERE    department_id = 80 );

/*                      

DELETE FROM employees

WHERE last_name = 'Tomas';

DELETE FROM job_history

WHERE employee_id = 403;

--207, 401, 402, 403 번 사원정보 삭제 : 실습용 데이터

*/

-- 2-4) EXISTS

-- ※ 결과행이 존재하는지만 따지는 서브쿼리

SELECT  employee_id, last_name, salary, department_id

FROM    employees

WHERE   EXISTS ( SELECT salary

                 FROM employees

                 WHERE    department_id = 80 );

-- 3. 다중 컬럼 서브쿼리

-- 데이터 조회보다 어떤 테이블의 여러 컬럼을 동시에 처리하기 위한 용도?

-- ex> 월말 마감용 테이블에 ==> 부서별 사원들의 최고급여, 최저급여, 평균급여, 사원수등등 데이터를 입력한다고 가정하면

/*

부서번호     날짜      최저급여   최고급여   평균급여   사원수

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

 10     24/8/30     2100     24000      6462      107

 20     24/9/30     2200     24000      6562      108

 30     24/10/30    2200     24000      6562      108

          ...             ...            ...

*/

UPDATE  monthly_deadline

SET max_sal = (서브쿼리1),

    min_sal = (서브쿼리2),

    avg_sal = (서브쿼리3),

    count = (서브쿼리4);

--WHERE

SELECT  department_id, MAX(salary), MIN(salary), SUM(salary), ROUND(AVG(salary)), COUNT(*)

FROM    employees

GROUP BY department_id

ORDER BY 1;

-- II. 사용위치에 따른 구분 예시

-- 1. WHERE 절 : 가장 일반적인 위치 --> 단일 행, 다중 행, 다중컬럼 서브쿼리

-- 2. SELECT 절 : 스칼라(Scalar) 서브쿼리 --> 코드성 테이블에서 코드명을 조회하거나  그룹함수의 결과를 조회하거나 할때

-- 2-1. 조인연산을 사용해서 사원과 부서를 연결해서 사원의 부서정보를 함께 조회

SELECT  e.employee_id, e.last_name, e.job_id,

        d.department_name

FROM    employees e, departments d

WHERE   e.department_id = d.department_id(+)

ORDER BY 1;

-- ANSI 조인

SELECT  e.employee_id, e.last_name, e.job_id,

        d.department_name

FROM    employees e LEFT OUTER JOIN departments d

ON   e.department_id = d.department_id

-- USING (department_id)

ORDER BY 1;

-- 2-2. 스칼라 서브쿼리를 사용해서

SELECT  e.employee_id, e.last_name, e.job_id,

        ( SELECT    department_name

          FROM  departments d

          WHERE d.department_id = e.department_id ) AS dept_name

FROM    employees e

ORDER BY e.employee_id;

-- ※ 스칼라: 숫자, 벡터: 숫자 배열, 행렬(matrix): 2차원 배열, 텐서(tensor): 2차원 이상의 배열

--SELECT  컬럼명, (서브쿼리)

--FROM    테이블

--WHERE   조건절

-- 3. FROM 절 : 인라인 뷰 서브쿼리

-- 서브쿼리 실행결과가 어떤 테이블(물리적으로 존재x, 메모리에만 생성되었다가 사라짐 ==> MariaDB의 Temporary Table)

-- ※ 메인 쿼리에 독립적이다.

SELECT  컬럼명, 컬럼명,..

FROM    테이블명, (서브쿼리)

WHERE   조건절(JOIN)

-- Q. 사원들중 급여가 평균급여 이상, 최대급여 이하인 사원들의 정보를 조회

-- employees 테이블에는 평균급여(avg_sal), 최대급여(max_sal)은 없음!!

-- ※ NON-EQUI JOIN : 동등 비교 연산자(=) 이외의 연산자(>,<, BETWEEN ~ AND) 를 사용하는 조인형식

SELECT  employee_id, last_name, salary,

        avg_sal, max_sal

FROM    employees,

        ( SELECT ROUND(AVG(salary)) AS avg_sal, MAX(salary) AS max_sal

          FROM  employees )

WHERE   salary BETWEEN avg_sal AND max_sal