SQL 3개의 테이블로 조인
-- 세 개의 테이블로 조인
SELECT jikwon_name, buser_name, gogek_name
FROM jikwon,buser,gogek
WHERE jikwon.buser_num=buser_no AND jikwon.jikwon_no=gogek.gogek_damsano;
SELECT jikwon_name, buser_name, gogek_name
FROM jikwon
INNER JOIN buser ON jikwon.buser_num=buser.buser_no
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano;
3개의 조인을 활용한 문제풀이
-- 문1) 총무부에서 관리하는 고객수 출력 (고객 30살 이상만 작업에 참여)
SELECT buser_name 부서, COUNT(gogek_no) '고객 수' FROM jikwon
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano
INNER JOIN buser ON jikwon.buser_num=buser.buser_no WHERE buser_name='총무부'
AND TIMESTAMPDIFF(YEAR,CAST(SUBSTR(gogek_jumin,1,6) AS DATE), NOW()) >= 30;
-- 문2) 부서명별 고객 인원수 (부서가 없으면 "무소속")
SELECT nvl(buser_name,'무소속') 부서, COUNT(gogek_no) 인원수 FROM jikwon
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano
INNER JOIN buser ON jikwon.buser_num=buser.buser_no GROUP BY buser_name;
SELECT SUBSTR(gogek_jumin,8,1) FROM gogek;
-- 문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면 담당직원 자료 출력
-- : ~ WHERE GOGEK_NAME='강나루'
-- 출력 ==> 직원명 직급 부서명 부서전화 성별
SELECT jikwon_name 직원명, jikwon_jik 직급, buser_name 부서명, buser_tel 부서전화, jikwon_gen 성별
FROM jikwon
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano
INNER JOIN buser ON jikwon.buser_num=buser.buser_no WHERE GOGEK_NAME='강나루';
-- 문4) 부서와 직원명을 입력하면 관리고객 자료 출력
-- ~ WHERE BUSER_NAME='영업부' AND JIKWON_NAME='이순신'
-- 출력 ==> 고객명 고객전화 성별
-- 강나루 123-4567 남
SELECT gogek_name 고객명, gogek_tel 고객전화, case
when SUBSTR(gogek_jumin,8,1) = '1' then '남' ELSE '여' END AS 성별
from jikwon
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano
INNER JOIN buser ON jikwon.buser_num=buser.buser_no
WHERE BUSER_NAME='영업부' AND JIKWON_NAME='이순신';유니온(Union) : 구조가 일치하는 두 테이블 자료 합쳐보기
-- union : 구조가 일치하는 두 개 이상의 테이블 자료 합쳐보기
-- 원래 테이블은 그대로 유지된다.
CREATE TABLE sangpum1(bun INT, pummok VARCHAR(20) NOT NULL);
INSERT INTO sangpum1 VALUES(1,'사과');
INSERT INTO sangpum1 VALUES(2,'오렌지');
INSERT INTO sangpum1 VALUES(3,'바나나');
SELECT * FROM sangpum1;
CREATE TABLE sangpum2(num INT, sangirum VARCHAR(20) NOT NULL);
INSERT INTO sangpum2 VALUES(10,'토마토');
INSERT INTO sangpum2 VALUES(11,'참외');
INSERT INTO sangpum2 VALUES(12,'딸기');
INSERT INTO sangpum2 VALUES(13,'오이');
INSERT INTO sangpum2 VALUES(14,'수박');
SELECT * FROM sangpum2;
SELECT bun AS 번호, pummok 상품명 FROM sangpum1
UNION
SELECT num, sangirum FROM sangpum2;
서브쿼리(subquery) : SQL명령문에 포함된 SELECT 명령문.
-- subquery : SUBQUERY 란 SQL명령문에 포함된 SELECT 명령문이다.
-- 이때 서브쿼리는 메인쿼리보다 먼저 실행 될 수 있으며, 서브쿼리가 위치한 곳에 먼저 실행 될 수 있다.
-- >> 반환 값에 따른 서브쿼리
-- - Single row Subquery : 서브쿼리 결과가 단일 행
-- * 단일 행 비교 연산자에는 =, <, <=, > , >=, <>을 사용한다.
-- - Multiple row Subquery : 서브쿼리 결과가 여러 행
-- * 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
SELECT * FROM jikwon;
-- 박별나 직원과 직급이 직원 출력
SELECT jikwon_jik FROM jikwon WHERE jikwon_name='박별나'; -- 문1 : 과장
SELECT * FROM jikwon WHERE jikwon_jik='과장'; -- 문2 : 조건에 맞는 결과 출력
-- 문1의 결과를 근거로 문2를 실행함
-- subquery를 사용 - where 조건
SELECT * FROM jikwon WHERE jikwon_jik=(SELECT jikwon_jik FROM jikwon WHERE jikwon_name='박별나');
-- 직급이 대리 중 가장 먼저 입사한 직원은?
SELECT MIN(jikwon_ibsail) FROM jikwon WHERE jikwon_jik='대리'; -- 2013-02-05
SELECT * FROM jikwon WHERE jikwon_ibsail='2013-02-05';
SELECT * FROM jikwon WHERE jikwon_ibsail=(SELECT MIN(jikwon_ibsail) FROM jikwon
WHERE jikwon_jik='대리'); -- 틀린 문장
SELECT * FROM jikwon WHERE jikwon_jik='대리' and jikwon_ibsail=(SELECT MIN(jikwon_ibsail)
FROM jikwon WHERE jikwon_jik='대리'); -- 답
-- 인천에 근무하는 직원 출력
SELECT * FROM buser;
SELECT * FROM jikwon WHERE buser_num=(SELECT buser_no FROM buser WHERE buser_loc='인천');
-- 인천 이외의 지역에 근무하는 직원 출력
SELECT * FROM buser;
SELECT * FROM jikwon WHERE buser_num in(SELECT buser_no FROM buser WHERE NOT buser_loc='인천');
SELECT * FROM jikwon WHERE buser_num <> (SELECT buser_no FROM buser WHERE NOT buser_loc='인천');
SELECT * from gogek;
-- 김혜순 고객과 담당 직원이 같은 고객을 출력
SELECT * from gogek WHERE gogek_damsano=(SELECT gogek_damsano
FROM gogek WHERE gogek_name='김혜순');
-- 고객 중 박상운과 나이가 같은 모든 고객을 출력
SELECT * FROM gogek WHERE SUBSTR(gogek_jumin,1,2)=(SELECT SUBSTR(gogek_jumin,1,2)
FROM gogek WHERE gogek_name='박상운');
서브쿼리를 활용한 문제풀이
-- 문1) 2010년 이후에 입사한 남자 중 급여를 가장 많이 받는 직원은?
SELECT jikwon_name 직원명, MAX(jikwon_pay) 급여 FROM jikwon
WHERE jikwon_gen='남'
AND jikwon_ibsail in(SELECT jikwon_ibsail FROM jikwon WHERE jikwon_ibsail >= '2010-01-01');
SELECT * FROM jikwon
WHERE jikwon_ibsail >= '2010-01-01' AND jikwon_gen='남'
AND jikwon_pay=(SELECT MAX(jikwon_pay) FROM jikwon WHERE jikwon_ibsail >= '2010-01-01'
AND jikwon_gen ='남');
-- 문2) 평균급여보다 급여를 많이 받는 직원은?
SELECT * FROM jikwon WHERE jikwon_pay > (SELECT avg(jikwon_pay) FROM jikwon);
-- 문3) '이미라' 직원의 입사 이후에 입사한 직원은?
SELECT * FROM jikwon WHERE jikwon_ibsail>=(SELECT jikwon_ibsail FROM jikwon WHERE jikwon_name='이미라');
-- 문4) 2010 ~ 2015년 사이에 입사한 총무부(10),영업부(20),전산부(30) 직원 중 급여가 가장 적은 사람은? (직급이 NULL인 자료는 작업에서 제외)
SELECT * FROM jikwon WHERE jikwon_pay in(SELECT MIN(jikwon_pay) FROM jikwon
WHERE jikwon_ibsail BETWEEN '2010-01-01' AND '2015-12-31' and buser_num='10'OR'20'OR'30') AND jikwon_jik IS NOT NULL;
-- 문5) 한송이, 이순신과 직급이 같은 사람은 누구인가?
SELECT * FROM jikwon WHERE jikwon_jik in(SELECT jikwon_jik FROM jikwon
WHERE jikwon_name IN('한송이','이순신')) ORDER BY jikwon_jik desc;
-- 문6) 과장 중에서 최대급여, 최소급여를 받는 사람은?
SELECT * FROM jikwon WHERE jikwon_pay=(SELECT MAX(jikwon_pay) FROM jikwon) OR jikwon_pay=(SELECT MIN(jikwon_pay) FROM jikwon);
-- 문7) 10번 부서의 최소급여보다 많은 사람은?
SELECT * FROM jikwon WHERE jikwon_pay > (SELECT MIN(jikwon_pay) FROM jikwon WHERE buser_num='10');
-- 문8) 30번 부서의 평균급여보다 급여가 많은 '대리' 는 몇명인가?
SELECT jikwon_jik 직급, COUNT(*) 인원 FROM jikwon
WHERE jikwon_pay > (SELECT avg(jikwon_pay) FROM jikwon WHERE buser_num='30')
AND jikwon_jik='대리';
-- 문9) 고객을 확보하고 있는 직원들의 이름, 직급, 부서명을 입사일 별로 출력하라.
SELECT DISTINCT jikwon_name 이름, jikwon_jik 직급, buser_name 부서명 FROM jikwon
INNER JOIN buser ON jikwon.buser_num=buser.buser_no
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano
WHERE jikwon_no = gogek_damsano;
SELECT jikwon_name AS 직원명, jikwon_jik AS 직급, buser_num AS 부서명 , jikwon_ibsail AS 입사일 FROM jikwon
WHERE jikwon_no IN (SELECT jikwon_no FROM jikwon
INNER JOIN gogek ON jikwon.jikwon_no = gogek.gogek_damsano
GROUP BY jikwon_no) ORDER BY jikwon_ibsail;
-- 문10) 이순신과 같은 부서에 근무하는 직원과 해당 직원이 관리하는 고객 출력
-- (고객은 나이가 30 이하면 '청년', 50 이하면 '중년', 그 외는 '노년'으로 표시하고, 고객 연장자 부터 출력)
-- 출력 ==> 직원명 부서명 부서전화 직급 고객명 고객전화 고객구분
-- 한송이 총무부 123-1111 사원 백송이 333-3333 청년
SELECT jikwon_name 직원명, buser_name 부서명, buser_tel 부서전화, jikwon_jik 직급, gogek_name 고객명, gogek_tel 고객전화, case
when TIMESTAMPDIFF(YEAR,cast(SUBSTR(gogek_jumin,1,6) AS DATE),NOW()) <= 30 then '청년'
when TIMESTAMPDIFF(YEAR,cast(SUBSTR(gogek_jumin,1,6) AS DATE),NOW()) <= 50 then '중년'
ELSE '노년' END AS 고객구분
FROM jikwon
INNER JOIN buser ON jikwon.buser_num=buser.buser_no
INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano
WHERE buser_num=(SELECT buser_num FROM jikwon WHERE jikwon_name='이순신')
ORDER BY 고객구분;