비전공개미 개발노트
MySQL 3일차 - 문제풀이 본문
반응형
SMALL
-- 테이블 정보 --
SELECT * FROM emp;
+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 2010-12-17 | 1600.00 | 300.00 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 2011-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 2011-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 2011-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 2011-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 2011-06-09 | 2450.00 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 2011-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 2017-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 2011-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 2012-01-23 | 1300.00 | NULL | 10 |
| 8800 | kim | teacher | 7782 | 2022-01-23 | 600.00 | NULL | 10 |
| 8811 | goo | teacher | 7782 | 2022-09-06 | 7900.00 | NULL | 30 |
| 9900 | lee | teacher | 7782 | 2022-10-14 | 350.00 | NULL | 10 |
| 9911 | choi | teacher | 7782 | 2022-09-06 | 4500.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+--------+--------+
-------------------------------------------------------------------------------------
문제1] job컬럼 필드에서 manager가 아닌 데이터조회
SELECT * FROM emp WHERE job NOT IN ('MANAGER');
SELECT * FROM emp WHERE job != 'MANAGER';
+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 2010-12-17 | 1600.00 | 300.00 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 2011-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 2011-02-22 | 1250.00 | 500.00 | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 2011-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 2017-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 2011-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 2012-01-23 | 1300.00 | NULL | 10 |
| 8800 | kim | teacher | 7782 | 2022-01-23 | 600.00 | NULL | 10 |
| 8811 | goo | teacher | 7782 | 2022-09-06 | 7900.00 | NULL | 30 |
| 9900 | lee | teacher | 7782 | 2022-10-14 | 350.00 | NULL | 10 |
| 9911 | choi | teacher | 7782 | 2022-09-06 | 4500.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+--------+--------+
문제2] comm컬럼 필드에서 null인 데이터조회
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NULL OR comm <= 0;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 2011-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 2011-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 2011-06-09 | 2450.00 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 2011-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 2017-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 2011-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 2012-01-23 | 1300.00 | NULL | 10 |
| 8800 | kim | teacher | 7782 | 2022-01-23 | 600.00 | NULL | 10 |
| 8811 | goo | teacher | 7782 | 2022-09-06 | 7900.00 | NULL | 30 |
| 9900 | lee | teacher | 7782 | 2022-10-14 | 350.00 | NULL | 10 |
| 9911 | choi | teacher | 7782 | 2022-09-06 | 4500.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+------+--------+
문제3] deptno컬럼 필드별 sal급여합계 구하기
SELECT deptno, sum(sal) FROM emp GROUP BY deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 30 | 20100.00 |
| 20 | 14575.00 |
| 10 | 7250.00 |
+--------+----------+
문제4] ename컬럼 필드에서 SCOTT, FORD 데이터조회
SELECT * FROM emp WHERE ename IN ('SCOTT', 'FORD');
SELECT * FROM emp WHERE ename = 'SCOTT' OR ename = 'FORD';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
문제5] hiredate컬럼 필드의 2011년도에 입사한 사람 like
SELECT * FROM emp WHERE hiredate LIKE '2011%';
+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 2011-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 2011-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 2011-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 2011-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 2011-06-09 | 2450.00 | NULL | 30 |
| 7839 | KING | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 2011-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 2011-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+--------+--------+
문제6] sal컬럼 필드의 2000~5000사이 데이터조회
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 5000;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 2011-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 2011-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 2011-06-09 | 2450.00 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 |
| 9911 | choi | teacher | 7782 | 2022-09-06 | 4500.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
문제7] ename컬럼 필드의 M/m 포함된 사람
SELECT * FROM emp WHERE ename LIKE '%M%';
+-------+--------+---------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+---------+------+------------+---------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 2010-12-17 | 1600.00 | 300.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 2017-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 2011-12-03 | 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 2012-01-23 | 1300.00 | NULL | 10 |
| 8800 | kim | teacher | 7782 | 2022-01-23 | 600.00 | NULL | 10 |
+-------+--------+---------+------+------------+---------+--------+--------+
문제8] job컬럼 필드 그룹별 sal급여합계 구하기 합계가 많은 순으로 sort
SELECT job, sum(sal) as total FROM emp GROUP BY job ORDER BY total DESC;
+-----------+----------+
| job | total |
+-----------+----------+
| teacher | 13350.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
| CLERK | 4950.00 |
| SALESMAN | 4350.00 |
+-----------+----------+
문제9] job그룹별 급여총합계가 5000이하인 그룹을 조회한다
SELECT job, sum(sal) as total FROM emp GROUP BY job HAVING 5000 >= total ORDER BY total DESC;
+-----------+---------+
| job | total |
+-----------+---------+
| PRESIDENT | 5000.00 |
| CLERK | 4950.00 |
| SALESMAN | 4350.00 |
+-----------+---------+
반응형
LIST
'데이터베이스 > MySQL' 카테고리의 다른 글
| MySQL 5일차 - 프로시저(Stored Procedure) (0) | 2022.09.14 |
|---|---|
| MySQL 4일차 - 조인(JOIN), 서브쿼리, 프로시저(Stored Procedure) (2) | 2022.09.13 |
| MySQL 3일차 - 내장함수, 내부조인, 외부조인, 셀프조인, cross조인, natural조인 (0) | 2022.09.07 |
| MySQL 2일차 - 문제풀이 (0) | 2022.09.06 |
| MySQL 2일차 - 테이블생성, 내부함수, WHERE / GROUP BY / HAVING / ORDER BY / LIMIT, View테이블, JOIN(조인) (0) | 2022.09.06 |
Comments