국비 지원 IT(웹앱개발) 취업반 강의 27일차 (DB, JDBC)

Coding History

국비 지원 IT(웹앱개발) 취업반 강의 27일차 (DB, JDBC)

BlackBirdIT 2024. 7. 12. 21:19

sql 풀이부터 시작했다

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;
# 부서(홍보, 기획)
CREATE TABLE dept(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    dName CHAR(100) NOT NULL UNIQUE
);
INSERT INTO dept
SET regDate = now(),
    dName = '홍보';

INSERT INTO dept
SET regDate = now(),
    dName = '기획';

SELECT *
FROM dept;




# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
CREATE TABLE emp(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    eName CHAR(10) NOT NULL,
    deptNo INT(10) UNSIGNED NOT NULL,
    salary INT(100) NOT NULL
);

INSERT INTO emp (eName, regDate, deptNo, salary)
VALUES             ('홍길동', now(), 1, 5000);

INSERT INTO emp (eName, regDate, deptNo, salary)
VALUES             ('홍길순', now(), 1, 6000);

INSERT INTO emp (eName, regDate, deptNo, salary)
VALUES             ('임꺽정', now(), 2, 4000);

SELECT *
FROM emp;
# 사원 수 출력
SELECT COUNT(*) AS '사원수'
FROM emp;

# 가장 큰 사원 번호 출력
SELECT *
FROM emp
ORDER BY id DESC
LIMIT 1;

SELECT MAX(id)
FROM emp;

# 가장 고액 연봉
SELECT *
FROM emp
ORDER BY salary DESC
LIMIT 1;

SELECT MAX(salary)
FROM emp;


# 가장 저액 연봉
SELECT *
FROM emp
ORDER BY salary
LIMIT 1;

SELECT MIN(salary)
FROM emp;

# 회사에서 1년 고정 지출(인건비)
SELECT SUM(e.salary) AS '회사 년 인건비 합'
FROM emp AS e
GROUP BY '회사 년 인건비 합';


# 부서별, 1년 고정 지출(인건비)
SELECT SUM(e.salary) AS '홍보인건비' 
FROM dept AS d
INNER JOIN emp AS e
ON e.deptNo = d.id
WHERE d.dName = '홍보'
GROUP BY '홍보인건비';

SELECT SUM(e.salary) AS '기획인건비' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '기획'
GROUP BY '기획인건비';

SELECT deptNo, SUM(salary)
FROM emp
GROUP BY deptNo;

# 부서별, 최고연봉
SELECT e.eName, d.dName, e.salary AS '홍보 최고연봉' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '홍보'
ORDER BY e.salary DESC
LIMIT 1;

SELECT e.eName, d.dName, e.salary AS '기획 최고연봉' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '기획'
ORDER BY e.salary DESC
LIMIT 1;

SELECT deptNo, MAX(salary)
FROM emp
GROUP BY deptNo;

# 부서별, 최저연봉
SELECT e.eName, d.dName, e.salary AS '홍보 최저연봉' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '홍보'
ORDER BY e.salary
LIMIT 1;

SELECT e.eName, d.dName, e.salary AS '기획 최저연봉' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '기획'
ORDER BY e.salary
LIMIT 1;

SELECT deptNo, MIN(salary)
FROM emp
GROUP BY deptNo;

# 부서별, 평균연봉
SELECT d.dName, round(AVG(e.salary), 0) AS '홍보 평균연봉' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '홍보'
GROUP BY '홍보 평균연봉';

SELECT d.dName, round(AVG(e.salary), 0) AS '기획 평균연봉' 
FROM dmpt AS d
INNER JOIN emp AS e
ON e.dmptNo = d.id
WHERE d.dName = '기획'
GROUP BY '기획 평균연봉';

SELECT deptNo, AVG(salary)
FROM emp
GROUP BY deptNo;

# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V1(조인 안한 버전)
SELECT ROUND(AVG(salary)) AS '평균연봉'
FROM emp;

SELECT salary AS '최고연봉'
FROM emp
ORDER BY salary DESC
LIMIT 1;

SELECT salary AS '최소연봉'
FROM emp
ORDER BY salary
LIMIT 1;

SELECT COUNT(*) AS '사원수'
FROM emp;

#정답
SELECT e.deptNo AS '부서번호', GROUP_CONCAT(e.eName) AS '사원명',truncate(AVG(e.salary),1) AS '평균연봉'
, MAX(e.salary) AS '최고연봉', MIN(e.salary) AS '최소연봉', COUNT(e.salary) AS '사원수'
FROM emp AS e
GROUP BY deptNo;
# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V2(조인해서 부서명까지 나오는 버전)
SELECT *
FROM dept AS d
INNER JOIN emp AS e;

SELECT ROUND(AVG(e.salary)) AS '평균연봉'
FROM emp AS e
INNER JOIN dept AS d
GROUP BY '평균연봉';

SELECT d.dName, round(AVG(e.salary), 0) AS '부서별 평균연봉' 
FROM dept AS d
INNER JOIN emp AS e
ON e.deptNo = d.id
GROUP BY d.dName;

SELECT e.eName, d.dName, e.salary AS '최고연봉'
FROM dept AS d
INNER JOIN emp AS e
ON d.id = e.deptNo
ORDER BY e.salary DESC
LIMIT 1;

SELECT e.eName, d.dName, e.salary AS '최소연봉'
FROM dept AS d
INNER JOIN emp AS e
ON d.id = e.deptNo
ORDER BY e.salary
LIMIT 1;

SELECT d.dName AS '부서명', COUNT(e.eName) AS '사원수'
FROM dept AS d
INNER JOIN emp AS e
ON d.id = e.deptNo
GROUP BY d.dName;

SELECT *
FROM emp AS e
INNER JOIN dept AS d
ON emp.deptNo = dept.id;

#정답
SELECT e.deptNo AS '부서번호',d.dName AS '부서명', GROUP_CONCAT(e.eName) AS '사원명',truncate(AVG(e.salary),1) AS '평균연봉'
, MAX(e.salary) AS '최고연봉', MIN(e.salary) AS '최소연봉', COUNT(e.salary) AS '사원수'
FROM emp AS e
INNER JOIN dept AS d
ON e.deptNo = d.id
GROUP BY deptNo;



## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT d.dName AS '부서이름', ROUND(AVG(e.salary), 0) AS '부서별 평균연봉' 
FROM dept AS d
INNER JOIN emp AS e
ON e.deptNo = d.id
GROUP BY d.dName
HAVING ROUND(AVG(e.salary), 0) >= 5000;

#정답
SELECT e.deptNo AS '부서번호',d.dName AS '부서명', GROUP_CONCAT(e.eName) AS '사원명',truncate(AVG(e.salary),1) AS '평균연봉'
, MAX(e.salary) AS '최고연봉', MIN(e.salary) AS '최소연봉', COUNT(e.salary) AS '사원수'
FROM emp AS e
INNER JOIN dept AS d
ON e.deptNo = d.id
GROUP BY deptNo
HAVING AVG(e.salary) > 5000;

내가 생각한 것과 의도가 달라서 생각보다 많이 틀린 것 같은데 뭐 어찌됐든 원하는 결과를 보여주는 쿼리를 작성하긴 해서 만족한다.

DROP DATABASE IF EXISTS scott;

CREATE DATABASE scott;

USE scott;

CREATE TABLE DEPT (
    DEPTNO DECIMAL(2),
    DNAME VARCHAR(14),
    LOC VARCHAR(13),
    CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) 
);
CREATE TABLE EMP (
    EMPNO DECIMAL(4),
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR DECIMAL(4),
    HIREDATE DATE,
    SAL DECIMAL(7,2),
    COMM DECIMAL(7,2),
    DEPTNO DECIMAL(2),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
CREATE TABLE SALGRADE ( 
    GRADE TINYINT,
    LOSAL SMALLINT,
    HISAL SMALLINT 
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-7-1987','%d-%m-%Y')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-7-1987', '%d-%m-%Y'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);


#1. 사원 테이블의 모든 레코드를 조회하시오.
SELECT *
FROM DEPT;

SELECT *
FROM EMP;

SELECT *
FROM SALGRADE;


#2. 사원명과 입사일을 조회하시오.
SELECT ENAME, HIREDATE
FROM EMP;

#3. 사원번호와 이름을 조회하시오.
SELECT MGR, ENAME
FROM EMP;

#4. 사원테이블에 있는 직책의 목록을 조회하시오. (hint : distinct, group by)
SELECT DISTINCT JOB
FROM EMP
GROUP BY JOB;

#5. 총 사원수를 구하시오. (hint : count)
SELECT COUNT(*)
FROM EMP;

#6. 부서번호가 10인 사원을 조회하시오.

SELECT *
FROM DEPT;

SELECT *
FROM EMP;

SELECT E.DEPTNO AS '부서번호', E.ENAME AS '사원이름', E.JOB AS '직무'
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE D.DEPTNO = 10;


#7. 월급여가 2500이상 되는 사원을 조회하시오.
SELECT *
FROM DEPT;

SELECT ENAME, SAL
FROM EMP
WHERE SAL >= 2500;

#8. 이름이 'KING'인 사원을 조회하시오.
SELECT *
FROM EMP
WHERE ENAME = 'KING';
#9. 사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오. (hint : like)
SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME LIKE 'S%';

#10. 사원 이름에 T가 포함된 사원의 사원번호와 이름을 조회하시오. (hint : like)
SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME LIKE '%T%';


#11. 커미션이 300, 500, 1400 인 사원의 사번,이름,커미션을 조회하시오. (hint : OR, in )
SELECT *
FROM DEPT;

SELECT *
FROM EMP;

SELECT *
FROM SALGRADE;

SELECT EMPNO AS '사번', ENAME AS '이름', COMM AS '커미션'
FROM EMP
WHERE COMM IN (300, 500, 1400);

SELECT EMPNO AS '사번', ENAME AS '이름', COMM AS '커미션'
FROM EMP
WHERE COMM = 300 OR 500 OR 1400;


#12. 월급여가 1200 에서 3500 사이의 사원의 사번,이름,월급여를 조회하시오. (hint : AND, between)
SELECT EMPNO AS '사번', ENAME AS '이름', SAL AS '월급여'
FROM EMP
WHERE SAL BETWEEN 1200 AND 3500;

SELECT EMPNO AS '사번', ENAME AS '이름', SAL AS '월급여'
FROM EMP
WHERE SAL >= 1200 AND SAL <= 3500;

#13. 직급이 매니저이고 부서번호가 30번인 사원의 이름,사번,직급,부서번호를 조회하시오. 
SELECT *
FROM DEPT;

SELECT *
FROM EMP;

SELECT *
FROM SALGRADE;

SELECT ENAME, EMPNO, JOB, DEPTNO
FROM EMP
WHERE JOB = 'MANAGER' AND DEPTNO = 30;


#14. 부서번호가 30인 아닌 사원의 사번,이름,부서번호를 조회하시오. (not)
SELECT ENAME, EMPNO, JOB, DEPTNO
FROM EMP
WHERE NOT DEPTNO = 30;

SELECT ENAME, EMPNO, JOB, DEPTNO
FROM EMP
WHERE DEPTNO != 30;

SELECT ENAME, EMPNO, JOB, DEPTNO
FROM EMP
WHERE DEPTNO <> 30;

#15. 커미션이 300, 500, 1400 이 모두 아닌 사원의 사번,이름,커미션을 조회하시오. (hint : not in)
SELECT EMPNO AS '사번', ENAME AS '이름', COMM AS '커미션'
FROM EMP
WHERE COMM NOT IN (300, 500, 1400);

#16. 이름에 S가 포함되지 않는 사원의 사번,이름을 조회하시오. (hint : not like)
SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME NOT LIKE '%S%';


#17. 급여가 1200보다 미만이거나 3700 초과하는 사원의 사번,이름,월급여를 조회하시오. (hint : not, between)
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL NOT BETWEEN 1200 AND 3700;

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL < 1200 OR sal > 3700;


#18. 직속상사가 NULL 인 사원의 이름과 직급을 조회하시오. (hint : is null, is not null)
SELECT ENAME, JOB
FROM EMP
WHERE MGR IS NULL;


#19. 부서별 평균월급여를 구하는 쿼리 (hint : group by, avg())

SELECT D.DNAME AS '부서', AVG(E.SAL) AS '평균급여'
FROM DEPT AS D
INNER JOIN EMP AS E
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO;
#사원수까지 (부서명이 아니니까 INNER JOIN 필요없었음)
SELECT DEPTNO, AVG(E.SAL) AS '평균급여', COUNT(*)
FROM EMP AS E
GROUP BY DEPTNO;


#20. 부서별 전체 사원수와 커미션을 받는 사원들의 수를 구하는 쿼리 (hint : group by, count())

SELECT D.DEPTNO, COUNT(*) AS '총사원수',COUNT(COMM) AS '커미션받는 사원수'
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO;

# IF(COMM = 0, NULL, COMM)는 삼향연산자('?') 생각하면 됨.
SELECT DEPTNO, COUNT(*), COUNT(COMM), COUNT(IF(COMM = 0, NULL, COMM))
FROM EMP AS E
GROUP BY DEPTNO;


#21. 부서별 최대 급여와 최소 급여를 구하는 쿼리 (hint : group by, min(), max())
SELECT D.DEPTNO, MIN(E.SAL) AS '최소 급여',MAX(E.SAL) AS '최대 급여'
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO;

SELECT deptno, MIN(SAL), MAX(SAL)
FROM EMP
GROUP BY DEPTNO;

#22. 부서별로 급여 평균 (단, 부서별 급여 평균이 2000 이상만) (hint : group by, having)
SELECT D.DEPTNO, AVG(E.SAL) AS '급여평균'
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO
HAVING AVG(E.SAL) >= 2000;

SELECT DEPTNO, AVG(SAL), COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) >= 2000;

#23. 월급여가 1000 이상인 사원만을 대상으로 부서별로 월급여 평균을 구하라. 단, 평균값이 2000 이상인 레코드만 구하라. (hint : group by, having)
SELECT D.DEPTNO, AVG(E.SAL) AS '월급여 평균'
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE E.SAL >= 1000
GROUP BY D.DEPTNO
HAVING AVG(E.SAL) >= 2000;

SELECT DEPTNO, AVG(SAL) AS '월급여 평균', COUNT(*)
FROM EMP
WHERE SAL >= 1000
GROUP BY DEPTNO
HAVING AVG(SAL) >= 2000;

#24. 사원명과 부서명을 조회하시오. (hint : inner join)
SELECT E.ENAME, D.DNAME
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO;


#25. 이름,월급여,월급여등급을 조회하시오. (hint : inner join, between)
SELECT *
FROM SALGRADE;
SELECT *
FROM DEPT;
SELECT *
FROM EMP;

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP AS E
INNER JOIN SALGRADE AS S
WHERE E.SAL BETWEEN S.LOSAL AND HISAL;

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP AS E
INNER JOIN SALGRADE AS S
ON E.SAL BETWEEN S.LOSAL AND HISAL;

#26. 이름,부서명,월급여등급을 조회하시오. 
SELECT E.ENAME, D.DNAME, S.GRADE
FROM EMP AS E
INNER JOIN SALGRADE AS S
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE E.SAL BETWEEN S.LOSAL AND HISAL;

SELECT E.ENAME, D.DNAME, S.GRADE
FROM EMP AS E
INNER JOIN SALGRADE AS S
ON E.SAL BETWEEN S.LOSAL AND HISAL
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO;



#27. 이름,직속상사이름을 조회하시오. (hint : self join)
SELECT E.ENAME AS '이름', R.ENAME AS '직속상사이름'
FROM EMP AS E
INNER JOIN EMP AS R
WHERE E.MGR = R.EMPNO;

SELECT E.ENAME AS '이름', R.ENAME AS '직속상사이름'
FROM EMP AS E
INNER JOIN EMP AS R
ON E.MGR = R.EMPNO;


SELECT *
FROM EMP;
SELECT *
FROM DEPT;

#28. 이름,직속상사이름을 조회하시오.(단 직속 상사가 없는 사람도 직속상사 결과가 null값으로 나와야 함) (hint : outer join)
###외부OUTER 조인. A LEFT JOIN B는 조인 조건에 만족하지 못하더라도 왼쪽 테이블 A의 행을 나타내고 싶을 때 사용한다.
# 반대로 A RIGHT JOIN B는 조인 조건에 만족하지 못하더라도 오른쪽 테이블 B의 행을 나타내고 싶을 때
SELECT E.ENAME AS '이름', R.ENAME AS '직속상사이름'
FROM EMP AS E 
LEFT OUTER JOIN EMP AS R
ON E.MGR = R.EMPNO;

SELECT E.ENAME AS '이름', R.ENAME AS '직속상사이름'
FROM EMP AS E 
LEFT JOIN EMP AS R
ON E.MGR = R.EMPNO;

###############################################################################################
#29. 이름,부서명을 조회하시오.단, 사원테이블에 부서번호가 40에 속한 사원이 없지만 부서번호 40인 부서명도 출력되도록 하시오. (hint : outer join)
SELECT E.ENAME AS '이름', D.DNAME AS '부서명'
FROM EMP AS E
RIGHT OUTER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO;

SELECT * 
FROM EMP;
SELECT *
FROM DEPT;

#서브 쿼리는 SELECT 문 안에서 ()로 둘러싸인 SELECT 문을 말하며 쿼리문의 결과를 메인 쿼리로 전달하기 위해 사용된다.
#사원명 'JONES'가 속한 부서명을 조회하시오.
#부서번호를 알아내기 위한 쿼리가 서브 쿼리로 사용.

SELECT D.DNAME
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.ENAME = 'JONES';

SELECT E.ENAME, (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO)
FROM EMP E
WHERE E.ENAME = 'JONES';

#30. 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명을 조회하시오. (hint : sub query)
SELECT E.ENAME, D.DNAME, D.DEPTNO
FROM EMP AS E
INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO = 10;

SELECT E.ENAME, D.DNAME, D.DEPTNO
FROM EMP AS E
INNER JOIN DEPT AS D 
ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO IN (SELECT DEPTNO FROM EMP WHERE DEPTNO = 10);

SELECT E.ENAME, A.DNAME
FROM EMP AS E
INNER JOIN 
(SELECT * FROM DEPT WHERE DEPTNO = 10) AS A
ON E.DEPTNO = A.DEPTNO;

서브쿼리는 필수는 아니라고 하셨다. 안그래도 어제 이거 풀 때 안써도 되는데 왜 써라고 하는지 이해가 안됐는데 가독성을 위해서 쓴다는 것 같았다. 서브쿼리는 데이터량이 감당 가능할 때, 웬만하면 INNER JOIN 을 사용하는 게 성능은 더 좋다고 한다. 그냥 겉으로만 좀 봐야겠다.

다음은 다시 JDBC로 넘어왔다. 이제는 DB가 연결되어있는 프로젝트에 회원기능을 넣기 시작할 것이다.

였는데 나는 구현하자마자 병원을 가야되서 밖으로 나와서 이후 수업은 듣지 못했다. 지금 레슨 끝내고 확인해보니까 잘 되더라. 코드는 다음 포스트에서 올리도록 하겠다!

'Coding History' 카테고리의 다른 글

2024. 07. 14. JDBC 리팩토링 (0)	2024.07.14
JDBC 회원 기능 추가중 (1)	2024.07.12
2024. 07. 12 SQL 문제풀이 (0)	2024.07.12
국비 지원 IT(웹앱개발) 취업반 강의 26일차 (DB) (0)	2024.07.11
2024. 07. 10 SQL 문제 풀이 (0)	2024.07.10

현재글국비 지원 IT(웹앱개발) 취업반 강의 27일차 (DB, JDBC)

함께 알아가는 IT 정보

IT 정보를 함께 알아가는 블로그입니다.

갤럭시 s23 스펙, 갤럭시, 오블완, 아이폰14프로, 티스토리챌린지, 갤럭시 S23 플러스, 최신 IT 정보, 맥북M2, 갤럭시 S23, 중저가형 스마트폰, 아이폰14pro, 애플페이, 갤럭시 A54, 삼성, 갤럭시 S23 울트라, 코스매틱 이슈, S23 사전예약, 갤럭시 s23 출시일, 애플, S23,

Today :
Yesterday :

함께 알아가는 IT 정보