연습용 데이터 베이스 생성
####데이터 초기화
DROP DATABASE employees;
USE `cgh`;
SHOW TABLES;
###############################
#TABLE 생성
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);
COMMIT;
##########################################################################################
셀렉트문들과 각종 연습문제
SELECT *
FROM emp;
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno
FROM emp;
SELECT ename,hiredate
FROM emp;
SELECT deptno, ename
FROM emp;
SELECT DISTINCT(job)
FROM emp;
SELECT COUNT(empno)
FROM emp;
SELECT *
FROM emp
WHERE deptno = 10;
SELECT *
FROM emp
WHERE sal >= 2500;
SELECT *
FROM emp
WHERE ename = 'king';
SELECT empno,ename
FROM emp
WHERE ename LIKE 's%';
SELECT empno,ename
FROM emp
WHERE ename LIKE '%T%';
SELECT empno,ename,comm
FROM emp
WHERE comm IN(300,500,1400);
SELECT empno,ename,sal
FROM emp
WHERE sal BETWEEN 1200
AND 3500;
SELECT ename,empno,job,deptno
FROM emp
WHERE deptno = 30 AND job = 'manager';
SELECT empno,ename,deptno
FROM emp
WHERE NOT deptno = 30;
SELECT empno,ename,comm
FROM emp
WHERE comm NOT IN (300,500,1400);
SELECT empno,ename
FROM emp
WHERE ename NOT LIKE '%S%';
SELECT empno,ename,sal
FROM emp
WHERE sal NOT BETWEEN 1200 AND 3700;
SELECT ename,job
FROM emp
WHERE mgr IS NULL;
SELECT deptno,AVG(sal)
FROM emp;
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
SELECT deptno,COUNT(*),COUNT(comm)
FROM emp
GROUP BY deptno;
SELECT deptno,MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) >= 2000;
SELECT deptno,AVG(sal)
FROM emp
WHERE sal >= 1000
GROUP BY deptno
HAVING AVG(sal) >= 2000;
SELECT empno,ename,sal
FROM emp
ORDER BY sal DESC,ename ASC;
SELECT emp.ename,dept.dname
FROM emp,dept;
SELECT ename,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;
SELECT e.ename,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno = d.deptno;
SELECT e.ename , m.ename
FROM emp AS e
INNER JOIN emp m
ON e.mgr = m.empno;
SELECT e.ename,e.sal,s.grade
FROM emp AS e,salgrade AS s
WHERE e.sal >= s.losal
AND e.sal <= s.hisal;
SELECT e.ename,e.sal,s.grade
FROM emp AS e,salgrade AS s
WHERE e.sal BETWEEN s.losal
AND s.hisal;
SELECT e.ename,d.dname,s.grade
FROM emp AS e, dept AS d,salgrade AS s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal
AND s.hisal;
SELECT e.ename,m.ename
FROM emp AS e, emp AS m
WHERE e.mgr = m.empno;
SELECT e.ename,m.ename
FROM emp AS e
LEFT JOIN emp AS m
ON e.mgr = m.empno;
SELECT e.ename,d.dname
FROM dept AS d
LEFT JOIN emp AS e
ON d.deptno = e.deptno;
SELECT ename,e.deptno,dname
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno;
SELECT ename,job,e.deptno,loc
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND d.deptno = 30;
SELECT ename,job,e.deptno,loc
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE d.deptno = 30;
SELECT ename,comm,dname,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NOT NULL AND emp.comm <>0;
SELECT ename,comm,dname,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NOT NULL
AND emp.comm <> 0;
SELECT e.ename,e.job,d.deptno,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno = d.deptno
AND d.loc = 'dallas';
SELECT e.ename,d.dname
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND e.ename LIKE '%A%';
SELECT e.ename,e.job,e.sal,s.grade
FROM emp AS e,salgrade AS s
WHERE e.sal BETWEEN s.losal
AND s.hisal;
SELECT c.ename,c.deptno
FROM emp AS e, emp AS c
WHERE e.empno <> c.empno
AND e.deptno = c.deptno
AND e.ename = 'allen'
ORDER BY c.ename;
SELECT c.ename,c.deptno
FROM emp AS e
INNER JOIN emp c ON e.deptno = c.deptno
WHERE e.empno <> c.empno
AND e.ename = 'allen'
ORDER BY c.ename;
SELECT dname
FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'jones');
SELECT e.ename,d.dname
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND d.deptno = 10;
SELECT e.ename,d.dname
FROM emp AS e,
(SELECT deptno , dname
FROM dept
WHERE deptno = 10
)AS d
WHERE e.deptno = d.deptno;
SELECT empno,ename,sal
FROM emp
WHERE sal >( SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;
SELECT empno,ename
FROM emp
WHERE sal = (SELECT MAX(sal)
FROM emp
WHERE deptno = 10);
SELECT e.ename, d.dname, e.sal
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN 3000 AND 5000;
SELECT e.ename, d.dname, e.sal
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND e.job = 'manager';
SELECT e.ename, e.hiredate
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno AND d.dname = 'accounting';
SELECT e.ename,e.hiredate, e.comm
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND e.comm IS NOT NULL
AND e.comm != 0;
SELECT e.ename , e.sal
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND d.loc = 'NEW YORK';
SELECT e.ename , e.sal , d.loc
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND e.sal <= 1200
ORDER BY e.sal ASC;
SELECT friend.ename AS "스미스 동료"
FROM emp `work` , emp friend
WHERE work.deptno = friend.deptno
AND work.ename = 'SMITH'
AND friend.ename = 'SMITH';
SELECT work.ename, work.job
FROM emp `work`, emp manager
WHERE work.mgr = manager.empno
AND manager.ename = 'KING';
SELECT e.ename , d.dname, e.sal
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
AND e.sal BETWEEN 3000 AND 5000;
SELECT e.ename , d.dname, e.sal
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
AND e.job = 'manager';
SELECT e.ename, e.hiredate
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE d.dname = 'ACCOUNTING';
SELECT * FROM dept;
SELECT * FROM emp;
SHOW TABLES;
SELECT e.ename , e.hiredate , e.comm
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE e.comm IS NOT NULL
AND e.comm <> 0;
SELECT e.ename , e.sal
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE d.loc = 'new york'
ORDER BY e.sal ASC;
SELECT e.ename, e.sal , d.loc
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE e.sal <= 1200
ORDER BY e.sal ASC;
SELECT friend.ename AS "스미스 동료"
FROM emp WORK
INNER JOIN emp friend
ON work.deptno = friend.deptno
WHERE work.ename = 'smith' AND friend.ename <> 'smith';
SELECT *
FROM emp;
SELECT SUM(DISTINCT(sal)) , SUM(DISTINCT(comm)),SUM(sal),SUM(sal)
FROM emp;
SELECT COUNT(*)
FROM emp
WHERE deptno = 30;
SELECT COUNT(*)
FROM emp
WHERE comm IS NOT NULL;
SELECT ROUND(AVG(sal),1)
FROM emp;
SELECT SUM(sal),AVG(sal),ROUND(AVG(sal)),MAX(sal),MIN(sal)
FROM emp;
SELECT deptno , ROUND(AVG(sal))
FROM emp
GROUP BY deptno;
SELECT deptno , job , ROUND(AVG(sal)) desc_sal
FROM emp
GROUP BY deptno , job
ORDER BY deptno DESC,desc_sal DESC;
SELECT ROUND(AVG(sal))
FROM emp
GROUP BY deptno;
SELECT COUNT(*)
FROM emp;
SELECT *
FROM emp;
SELECT *
FROM dept;
1. 문제) 부서번호가 10번인 부서의 사람 중 사원번호, 이름, 월급을
출력하라
SELECT empno, ename, sal
FROM emp
WHERE deptno=10;
2. 문제) 사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하라.
SELECT ename, hiredate, deptno
FROM emp
WHERE empno = 7369;
3. 문제) 이름이 ALLEN인 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE ename = 'allen';
4. 문제) 입사일이 83/01/12인 사원의 이름, 부서번호, 월급을 출력하라.
SELECT ename,deptno,sal
FROM emp
WHERE hiredate ='1983-01-12';
5. 문제) 직업이 MANAGER가 아닌 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE job <> 'manager';
6. 문제) 입사일이 81/04/02 이후에 입사한 사원의 정보를 출력하라.
SELECT *
FROM emp
WHERE hiredate > '1981-04-02';
7. 문제) 급여가 $800 이상인 사람의 이름, 급여, 부서번호를 출력하라.
SELECT ename, sal, deptno
FROM emp
WHERE sal >800;
8. 문제) 부서번호가 20번 이상인 사원의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE deptno>=20;
9. 문제) 이름이 K로 시작하는 사람보다 높은 이름을 가진 사람의 모든
정보를 출력하라.
SELECT *
FROM emp
WHERE ename >= 'l';
10. 문제) 입사일이 81/12/09 보다 먼저 입사한 사람들의 모든 정보를
출력하라.
SELECT *
FROM emp
WHERE hiredate <'1981-12-09';
11. 문제) 입사번호가 7698보다 작거나 같은 사람들의 입사번호와 이름을
출력하라.
SELECT empno, ename
FROM emp
WHERE empno<7698;
12. 문제) 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름,
월급, 부서번호를 출력하라.
SELECT ename, sal, deptno
FROM emp
WHERE hiredate < '1982-12-09'
AND hiredate > '1981-12-09';
13. 문제) 급여가 $1,600보다 크고 $3,000보다 작은 사람의 이름, 직업,
급여를 출력하라.
SELECT ename, job, sal
FROM emp
WHERE sal
BETWEEN 1600 AND 3000;
14. 문제) 사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를
출력하라.
SELECT *
FROM emp
WHERE empno BETWEEN 7654 AND 7782;
15. 문제) 이름이 B와 J 사이의 모든 사원의 정보를 출력하라.
SELECT *
FROM emp
WHERE ename BETWEEN 'b' AND 'j';
16. 문제) 입사일이 81년 이외에 입사한 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE YEAR(hiredate) <> '1981';
17. 문제) 직업이 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE job ='manager' OR job ='salesman';
18. 문제) 부서번호와 20, 30번을 제외한 모든 사람의 이름, 사원번호, 부서번호를 출력하라.
SELECT ename, empno, deptno
FROM emp
WHERE deptno <> 20 AND deptno <> 30;
19. 문제) 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 부서번호
를 출력하라.
SELECT ename, hiredate, empno, deptno
FROM emp
WHERE ename LIKE 's%';
20. 문제) 입사일이 81년도인 사람의 모든 정보를 출력하라
SELECT *
FROM emp
WHERE YEAR(hiredate) = '1981';
21. 문제) 이름 중 S자가 들어가 있는 사람만 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE ename LIKE '%s%';
22. 문제) 이름이 S로 시작하고 마지막 글자가 T인 사람의 모든 정보를
출력하라(단, 이름은 전체 5자리이다)
SELECT *
FROM emp
WHERE ename
LIKE 's%'
AND ename LIKE '%t';
23. 문제) 첫 번째 문자는 관계없고, 두 번째 문자가 A인 사람의 정보를
출력하라.
SELECT *
FROM emp
WHERE ename LIKE '_a%';
24. 문제) 커미션이 NULL인 사람의 정보를 출력하라.
SELECT *
FROM emp
WHERE comm IS NULL;
25. 문제) 커미션이 NULL이 아닌 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE comm IS NOT NULL;
26. 문제) 부서가 30번 부서이고 급여가 $1,500 이상인 사람의 이름,
부서 ,월급을 출력하라.
SELECT ename, deptno, sal
FROM emp
WHERE deptno =30 AND sal>= 1500;
27. 문제) 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의
사원번호, 이름, 부서번호를 출력하라.
SELECT empno,ename, deptno
FROM emp
WHERE ename LIKE 'k%'OR deptno =30;
28. 문제) 급여가 $1,500 이상이고 부서번호가 30번인 사원 중 직업이
MANAGER인 사람의 정보를 출력하라
SELECT *
FROM emp
WHERE sal>=1500
AND deptno=30 AND job='manager';
29. 문제) 부서번호가 30인 사람 중 사원번호 SORT하라.
SELECT *
FROM emp
WHERE deptno=30 ORDER BY empno;
30. 문제) 급여가 많은 순으로 SORT하라.
SELECT *
FROM emp
ORDER BY sal DESC;
31. 문제) 부서번호로 ASCENDING SORT한 후 급여가 많은 사람 순으로
출력하라.
SELECT *
FROM emp
ORDER BY deptno ASC, sal DESC;
32. 문제) 부서번호가 DESCENDING SORT하고, 이름 순으로 ASCENDING SORT,
급여 순으로 DESCENDING SORT 하라.
SELECT *
FROM emp
ORDER BY deptno DESC, ename ASC, sal DESC;
SHOW TABLES;
SELECT *
FROM dept;
SELECT *
FROM emp;
SELECT *
FROM salgrade;