SELECT * FROM 제품;
SELECT 제조업체
FROM 제품
WHERE 제품명 = '매운쫄면';
SELECT *, (SELECT COUNT(*) FROM 제품)
FROM 제품
WHERE 제조업체 = (SELECT 제조업체
FROM 제품
WHERE 제품명 = '매운쫄면');
SELECT *, (SELECT ENAME FROM EMP WHERE EMPNO = E.MGR)
FROM EMP E;
SELECT ENAME, DEPTNO, (SELECT DNAME
FROM DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO)
FROM EMP;
SELECT * FROM 주문;
SELECT *
FROM 주문
WHERE 주문고객 = 'banana';
SELECT *
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품
FROM 주문
WHERE 주문고객 = 'banana');ㅣ
--바나나 고객이 주문한 상품과 같은 상품을 주문한 고객의 정보 검색
SELECT *
FROM 고객
WHERE 고객아이디 IN (SELECT 주문고객
FROM 주문
WHERE 주문제품 IN (SELECT 주문제품
FROM 주문
WHERE 주문고객 = 'banana')
);
-----
SELECT *
FROM EMP;
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'ALLEN');
SELECT *
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = 10);
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE MGR IS NULL);
SELECT *
FROM EMP
WHERE SAL < (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE COMM = 0));
SELECT *
FROM EMP
WHERE SAL < (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE COMM = 0))
AND DEPTNO = 30
ORDER BY ENAME ASC;
SELECT *
FROM EMP
WHERE SAL < (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE COMM = 0))
-- AND DEPTNO = 30
ORDER BY SAL DESC
LIMIT 0, 2;
-----
SELECT *
FROM VISITOR_TB
-- WHERE
-- GROUP BY HAVING
ORDER BY VST_ID ASC
LIMIT 0, 10;
-----
SELECT *
FROM ACCIDENT;
SELECT WEEK, SUM(DIE)
FROM ACCIDENT
GROUP BY WEEK;
SELECT AREA1, SUM(DIE)
FROM ACCIDENT
GROUP BY AREA1
HAVING SUM(DIE) < 100;
SELECT AREA1, AREA2, SUM(DIE)
FROM ACCIDENT
GROUP BY AREA1, AREA2;
SELECT KIND, COUNT(*)
FROM ACCIDENT
GROUP BY KIND
ORDER BY COUNT(*) DESC
LIMIT 0, 5;
SELECT WEEK, COUNT(*)
FROM ACCIDENT
GROUP BY WEEK
ORDER BY COUNT(*) ASC
LIMIT 0, 1;
'IT > Mysql' 카테고리의 다른 글
sql 기본용어 정리 (0) | 2018.07.23 |
---|---|
Mysql - substr, in, left outer join, group by, FOREIGN KEY (0) | 2018.07.23 |
Mysql - JOIN, OUTER JOIN (0) | 2018.07.23 |
Mysql - AVG, COUNT, SUM, MIN, MAX, GROUP BY, HAVING, DATE_FORMAT (0) | 2018.07.23 |
Mysql - heidi autocommit - commit 비활성 명령어 (0) | 2018.07.23 |