DAY03. SQL Basic (Sort, Subquery)

SQL Sort (레코드 정렬)

1. Order by

default 정렬은 오름차순 (숫자:적은 값, 날짜:빠른 값, 문자:알파벳 순서)

null 값은 오름차순에서 제일 나중에, 내림차순에서는 제일 먼저

​

ASC : 행의 오름차순 정렬 (default)

DESC : 행의 내림차순 정렬 (Order by 절에서 열 이름 뒤에 명시)​

​

​

​

1) 레코드 정렬(sort)

SELECT hiredate,empno,ename,job,sal,deptno
FROM emp
ORDER BY hiredate; -- 날짜형 : 오름차순(default)

SELECT hiredate,empno,ename,job,sal,deptno
FROM emp
ORDER BY hiredate desc; -- 날짜형 : 내림차순

SELECT hiredate,empno,ename,job,sal,deptno
FROM emp
ORDER BY sal; -- 숫자형 : 오름차순

SELECT hiredate,empno,ename,job,sal,deptno
FROM emp
ORDER BY sal desc; -- 숫자형 : 내림차순

​

2) 연봉에 대한 오름차순 정렬방식 ( 기본 원칙은 칼럼을 기준으로 정렬)

SELECT empno,ename,job,sal,sal*12 annsal FROM emp ORDER BY annsal; -- 별칭을 기준으로 정렬
SELECT empno,ename,job,sal,sal*12 annsal FROM emp ORDER BY sal*12; -- 수식을 기준으로 정렬
SELECT empno,ename,job,sal,sal*12 annsal FROM emp ORDER BY 5; -- SELECT 절의 5번째 칼럼을 기준으로 정렬

​

​3) 2개 이상 칼럼을 이용한 정렬

SELECT deptno,sal,empno,ename,job
FROM emp
ORDER BY deptno, sal DESC; -- 1차 정렬 : deptno (ASC 생략) 2차 정렬 : sal DESC

 

 

 

 

 

연습문제

1. EMP 테이블에서 hiredate가 1981년 2월 20과 1981년 5월 1일 사이에 입사한 사원의 ename, job, hiredate을 출력하는 SELECT 문장을 작성(단 hiredate 순으로 정렬)

SELECT ename, job, hiredate
FROM emp
WHERE hiredate BETWEEN '1981/02/20' AND '1981/05/01'
ORDER BY hiredate;

​

2. EMP 테이블에서 deptno가 10, 20인 사원의 모든 정보를 출력하는 SELECT 문장을작성 (단 ename순으로 정렬)

SELECT *
FROM emp
WHERE deptno IN (10,20)
ORDER BY ename;

 

 

 

 

연습문제

[문1] emp에서 30번 부서에 근무하는 사원의 이름, 급여, 사번 출력하기

-- 사용 칼럼명 : 이름(ename),급여(sal),사번(empno) 출력하기

SELECT * FROM emp;

SELECT empno, ename, sal
FROM emp
WHERE deptno = 30;

​

[문2] student 테이블의 전체 학생들을 대상으로 다음 예)와 같이 출력하기

-- 예) 홍길동의 키는 175cm, 몸무게는 65kg입니다.

-- 사용 컬럼명 : 키(height), 몸무게(weight)

SELECT * FROM student;

SELECT name ||'의 키는'|| height ||'cm, 몸무게는'||weight||'kg 입니다.'
FROM student;

​​

[문3] student 테이블을 사용하여 2학년 중에서 키가 180cm 보다 크고,

-- 몸무게가 70kg 보다 큰 학생들의 이름,학년,키,몸무게 출력하기

-- 사용 컬럼명 : 이름(name), 학년(grade), 키(height), 몸무게(weight)

SELECT * FROM student;

SELECT name, grade, height, weight
FROM student
WHERE grade = 2 AND (height >180 AND weight > 70);

​​

[문4] student 테이블을 사용하여 1학년 학생의 이름, 키, 몸무게 출력하기

-- (단, 키는 오름차순, 몸무게는 내림차순으로 정렬하여 출력)

SELECT * FROM student;

SELECT name, weight, height
FROM student
WHERE grade=1
ORDER BY weight, height DESC; -- 1차 정렬에서 동일한 키를 갖는 학생이 없기 때문에 2차 정렬 적용되지 않았음

​​

[문5] student 테이블을 사용하여 1학년 학생의 '이름'과 '키' 출력(별칭 이용)

-- (단, 이름 오름차순으로 정렬)

SELECT * FROM student;​

SELECT name AS "이름", height "키" -- 칼럼(열)의 이름 변경
FROM student
WHERE grade=1
ORDER BY 이름; --별칭으로 정렬

​​

[문6] professor 테이블에서 교수들의 이름을 조회하여

-- 성 부분에 '김'이 포함된 사람의 이름을 오름차순으로 출력

SELECT * FROM professor;

SELECT *
FROM professor
WHERE name LIKE '김%'
ORDER BY name;

​​

[문7] professor 테이블를 대상으로 '전임'으로 검색하여 전임강사를 모두 검색하기

-- 사용 칼럼명 : position

SELECT * FROM professor;

SELECT *
FROM professor
WHERE position LIKE '전임%';

​​

[문8] 장바구니에 '우유'와 '빵'를 동시에 구입한 장바구니 ID 출력(table 별칭 이용)

​/*

* <장바구니 ID>

* 101

* 103

*/

SELECT * FROM cart_products;

SELECT *
FROM cart_products
WHERE NAME = '우유' AND NAME = '빵'; -- SELECT 특성때문에 검색 레코드 없음.
-- 선행사건 '우유'를 먼저 조회 -> 커서이동(순서대로) -> 검색 후 커서 8번에 위치 -> 후행사건 조회 -> 레코드가 없으므로 검색결과 조회 안 됨

​

-- table 별칭

-- 하나의 물리 테이블을 두 개로 쪼개어 각각의 사건대로 선행사건과 후행사건을 검색하고 AND로 묶어주어야 함

SELECT DISTINCT c1.cart_id
FROM cart_products c1, cart_products c2 -- (물리적 구분 카테고리)c1, c2는 칼럼과 테이블에서 모두 사용 가능
WHERE c1.name='우유' AND c2.name='빵' -- 선행사건 c1 조회 -> 후행사건 c2 조회
ORDER BY c1.cart_id;
* AND 연산자가 작동한 것이 아니라, '우유'를 구매한 id를 출력하고, '빵'을 구매한 id를 출력한 것이다.

 

 

 

 

 

​서브쿼리

서브쿼리?

질문할 수 있는 쿼리.

메인 쿼리 안에서 괄호로 묶여 우선적으로 처리되는 쿼리문.

​

왜 사용하나?

두 번 이상 단일 쿼리를 사용해야하는 문장을 한 번에 처리할 수 있음.

​

​

​

​

1. 테이블 생성

형식1) main query AS sub query;

형식1) 테이블 생성 (내용+구조)

CREATE table dept01
AS
SELECT * FROM dept; -- 조건에 해당하는 것만 새 테이블로 생성할 수 있음

 

형식2) main query SQL연산자 (sub query);

ex) main : dept(부서정보), sub : emp(사원정보) ->사원정보를 이용해서 부서정보를 찾고자 할 때

WHERE deptno =
(SELECT deptno FROM emp WHERE ename='SCOTT'); -- SCOTT 대소문자 구분

​

​

​

​

2. 단일행 서브쿼리 (서브쿼리에서 SELECT한 단위가 한 가지일 때)

형식) main query 비교연산자 (sub query);

​

1. 실습

-- 1. SCOTT과 같은 부서에서 근무하는 사원의 이름과 부서 번호를 출력하는 SQL 문을 작성해 보시오. (EMP)

SELECT ename, deptno -- 서브쿼리에서 넘어 온 결과값 20을 2차 처리
FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename='SCOTT'); -- 서브쿼리 결과값 20

FROM emp

WHERE deptno =

(SELECT deptno FROM emp WHERE ename='SCOTT'); -- 서브쿼리 결과값 20

​

-- SCOTT을 제외한 사원 이름 출력&정렬

SELECT ename, deptno -- 서브쿼리에서 넘어 온 결과값 20을 2차 처리
FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename='SCOTT')
AND ename != 'SCOTT' -- 여기까지 WHERE절. 괄호 바깥은 무조건 메인 쿼리에 해당된다.
ORDER BY ename;

FROM emp

WHERE deptno =

(SELECT deptno FROM emp WHERE ename='SCOTT')

AND ename != 'SCOTT' -- 여기까지 WHERE절. 괄호 바깥은 무조건 메인 쿼리에 해당된다.

ORDER BY ename;

​

-- 2. SCOTT와 동일한 직속상관(MGR)을 가진 사원을 출력하는 SQL문을 작성해 보시오. (EMP)

SELECT *
FROM emp
WHERE mgr =
(SELECT mgr FROM emp WHERE ename='SCOTT');

FROM emp

WHERE mgr =

(SELECT mgr FROM emp WHERE ename='SCOTT');

​

-- 3. SCOTT의 급여와 동일하거나 더 많이 받는 사원 명과 급여를 출력하시오.(EMP)

SELECT ename, sal
FROM emp
WHERE sal >=
(SELECT sal FROM emp WHERE ename='SCOTT');

FROM emp

WHERE sal >=

(SELECT sal FROM emp WHERE ename='SCOTT');

​

-- 4. DALLAS에서 근무하는 사원의 이름, 부서 번호를 출력하시오. (서브쿼리 : DEPT01, 메인쿼리 : EMP)

SELECT ename, deptno
FROM emp
WHERE deptno = -- 사원 정보 조회
(SELECT deptno FROM dept01 WHERE loc = 'DALLAS'); -- 부서 정보 (20번 부서) 조회
-- 모든 테이블을 서브쿼리로 나눌 수는 없음. 공통 칼럼이 있어야만 가능하다.
-- JOIN 기능과 관련 있음

FROM emp

WHERE deptno = -- 사원 정보 조회

(SELECT deptno FROM dept01 WHERE loc = 'DALLAS'); -- 부서 정보 (20번 부서) 조회

-- 모든 테이블을 서브쿼리로 나눌 수는 없음. 공통 칼럼이 있어야만 가능하다.

-- JOIN 기능과 관련 있음

​

-- 5. SALES(영업부) 부서에서 근무하는 모든 사원의 이름과 급여를 출력하시오.(서브쿼리 : DEPT01, 메인쿼리 : EMP)

SELECT ename, sal
FROM emp
WHERE deptno =
(SELECT deptno FROM dept01 WHERE dname = 'SALES');

​

​

2. 단일행 서브쿼리 (그룹함수 사용 예)

SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);

​​

​

3. 다중행 서브쿼리 (서브쿼리에서 SELECT한 단위가 2행 이상인 경우)

형식) main query IN/ANY/ALL

​

1) IN 연산자 : 메인 쿼리의 비교조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 TURE

SELECT ename, sal, deptno
FROM emp
WHERE deptno IN
(SELECT DISTINCT deptno
FROM emp
WHERE sal>=3000);

형광펜 = 다중 행

​

-- 단일 SQL문

SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (10,20);

​

--7. 직급(JOB)이 MANAGER인 사람이 속한 부서의 부서 번호와 부서명과 지역을 출력하시오.(DEPT01과 EMP 테이블 이용)

SELECT deptno, dname, loc
FROM dept01
WHERE deptno IN
(SELECT deptno FROM emp WHERE job = 'MANAGER')
ORDER BY deptno;

​

-- 단일 SQL문

SELECT deptno, dname, loc
FROM dept01
WHERE deptno IN (10, 20, 30);

​​

2) ALL 연산자 : 서브 쿼리의 검색 결과와 모든 값이 일치하면 TURE (AND와 유사)

-- 다중행 -> 최대값을 기준으로 비교를 할 때 사용

SELECT ename, sal
FROM emp
WHERE sal >
ALL(SELECT sal -- 급여가 30번 부서의 최댓값보다 큰 사원
FROM emp
WHERE deptno =30); --30번 부서에 있는 사원들 중 (최대값:2850이 기준이 됨)

​

-- 8. 영업 사원들 보다 급여를 많이 받는 사원들의 이름과 급여와 직급

-- 추가 사항 (담당 업무)를 출력하되 ANALYST는 출력하지 않습니다.

SELECT ename, sal, job
FROM emp
WHERE sal >
ALL(SELECT sal
FROM emp
WHERE job='SALESMAN')
AND job != 'ANALYST';

​​

3. ANY 연산자 : 서브 쿼리의 검색 결과와 하나 이상이 일치하면 TURE (OR와 유사)

-- 다중행 -> 최소값을 기준으로 비교를 할 때 사용

SELECT ename, sal
FROM emp
WHERE sal > ANY
(SELECT sal
FROM emp
WHERE deptno = 30); -- 950~2850 중 최소값 950이 기준이 됨

​

-- 9. 영업 사원들의 최소 급여보다 많이 받는 사원들의 이름과 급여와 직급 (담당 업무)를 출력하되

-- 추가 사항 : 영업 사원은 출력하지 않습니다.

SELECT ename, sal
FROM emp
WHERE sal > ANY
(SELECT sal
FROM emp
WHERE job = 'SALESMAN')
AND job != 'SALESMAN';

 

 

 

 

 

 

연습문제

select * from STUDENT; -- profno(교수번호), deptno01(주전공)
select * from PROFESSOR; -- 공통칼럼 : profno(교수번호)

​

-- [문1] STUDENT 테이블 검색 결과(sub)를 이용하여 STUDENT01 테이블 생성(main)

-- Sub(STUDENT), Main(STUDENT01)

CREATE TABLE student01
AS
SELECT * FROM student;

​

-- [문2] 교수번호가 2001인 지도교수를 모시는 전체 학생 명부 출력

-- Sub(PROFESSOR), Main(STUDENT01)

SELECT *
FROM student01
WHERE profno =
(SELECT profno FROM professor WHERE profno = 2001);

​

-- [문3] 보너스를 받는 교수들의 이름, 직위, 급여, 보너스 출력

-- 조건) IN()함수 이용 : 다중 행 처리

SELECT name, position, pay, bonus
FROM professor
WHERE profno IN
(SELECT profno FROM professor WHERE bonus IS NOT NULL);

​

-- [문4] 301 학과(DEPTNO) 교수들 보다 더 많은 급여를 받는 교수들의 이름, 직위, 급여, 학과 출력

-- 조건) ALL()함수 이용 : 다중 행 처리

SELECT name, position, pay, deptno
FROM professor
WHERE pay > ALL
(SELECT pay FROM professor WHERE deptno=301);

​​

--[문5] 장바구니에 '우유'와 '빵'를 동시에 구입한 장바구니 ID 출력(subQuery 이용)

/*

* <장바구니 ID>

* 101

* 103

*/

select * from CART_PRODUCTS;

SELECT DISTINCT cart_id
FROM cart_products
WHERE cart_id IN
(SELECT cart_id FROM cart_products WHERE name = '빵')
AND name = '우유'
ORDER BY cart_id;
-- [해설] 서브쿼리에서 '빵'을 구매자(1001,1002,1003)와 메인쿼리에서 '우유' 구매자(1001,1003) 대상 AND연산
* table 별칭을 이용해서 결과값을 도출했던 것은 페이크성