Mysql - select, subquery, group by, having

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;