--1 DESTINCT

SELECT

  DISTINCT A.DEPARTMENT_NAME

FROM

  DEPARTMENTS A

  , EMPLOYEES B

WHERE B.HIRE_DATE >= '20060101'

  AND B.DEPARTMENT_ID = A.DEPARTMENT_ID;


--2 INNER JOIN (ON)

SELECT

  DISTINCT A.DEPARTMENT_NAME

FROM

  DEPARTMENTS A INNER JOIN EMPLOYEES B

  ON B.DEPARTMENT_ID = A.DEPARTMENT_ID

WHERE B.HIRE_DATE >= '20060101';


--3 NATURAL JOIN

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS A NATURAL JOIN EMPLOYEES B

WHERE B.HIRE_DATE >= '20060101';


--4 NATURAL JOIN (USING)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS A JOIN EMPLOYEES B

USING (DEPARTMENT_ID)

WHERE B.HIRE_DATE >= '20060101';


--5 SUBQUERY (IN)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');


--6 SUBQUERY (IN - GROUP)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101'

                       GROUP BY DEPARTMENT_ID

                       );


--7 SUBQUERY (ANY)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = ANY (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');

--8 SUBQUERY (SOME)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = SOME (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');


--9 SUBQUERY (EXITST)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE EXISTS(

  SELECT 'X' FROM EMPLOYEES

  WHERE HIRE_DATE >= '20060101'

    AND DEPARTMENT_ID = A.DEPARTMENT_ID

    );


--10 SUBQUERY (연관서브쿼리)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE DEPARTMENT_ID IN (

  SELECT DEPARTMENT_ID FROM EMPLOYEES

  WHERE HIRE_DATE >= '20060101'

    AND DEPARTMENT_ID = A.DEPARTMENT_ID

    );


--11 Scalar subquery

SELECT

  DISTINCT

(SELECT DEPARTMENT_NAME FROM  DEPARTMENTS A WHERE  A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME

FROM EMPLOYEES B

WHERE  B.HIRE_DATE >= '20060101'

AND B.DEPARTMENT_ID IS NOT NULL;


--12 Scalar subquery + FROM 절 (GROUP BY)

SELECT

  (SELECT DEPARTMENT_NAME FROM  DEPARTMENTS A WHERE  A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME

FROM (SELECT DEPARTMENT_ID

      FROM EMPLOYEES

      WHERE HIRE_DATE >= '20060101'

      GROUP BY DEPARTMENT_ID) B

WHERE  B.DEPARTMENT_ID IS NOT NULL;


--13 Scalar subquery + FROM 절 (DISTINCT)

SELECT

  (SELECT DEPARTMENT_NAME FROM  DEPARTMENTS A WHERE  A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME

FROM (SELECT DISTINCT DEPARTMENT_ID

      FROM EMPLOYEES

      WHERE HIRE_DATE >= '20060101'

      ) B

WHERE  B.DEPARTMENT_ID IS NOT NULL;

   

--14 FROM절

SELECT

  DISTINCT A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101') B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;



--15 FROM절 (DISTINCT)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DISTINCT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101') B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;


--16 FROM절 (GROUP BY)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101'

                       GROUP BY DEPARTMENT_ID) B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;


--17 FROM절 (GROUP BY)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101') B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID

GROUP BY A.DEPARTMENT_NAME;


--18 HAVING절

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

GROUP BY A.DEPARTMENT_NAME, A.DEPARTMENT_ID

HAVING A.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');


--19 GROUP BY

SELECT

   A.DEPARTMENT_NAME

FROM

  DEPARTMENTS A

  , EMPLOYEES B

WHERE B.HIRE_DATE >= '20060101'

  AND B.DEPARTMENT_ID = A.DEPARTMENT_ID

GROUP BY A.DEPARTMENT_NAME;


--20 ROWNUM         (젤빠름)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE DEPARTMENT_ID IN (

  SELECT DEPARTMENT_ID FROM EMPLOYEES

  WHERE HIRE_DATE >= '20060101'

    AND DEPARTMENT_ID = A.DEPARTMENT_ID

    AND ROWNUM <= 1

    );


더 있으면 알려주시요^^

Posted by 빨강토끼
TAG ,