it개발자스터디공간

SELECT employee_id, last_name,

        (CASE

           WEHN department_id IN

                       (SELECT department_id FROM departments

                       WHERE location_id = 1800)

                  THEN 'Canada' ELSE 'other'

           END) location

       FROM employees

SELECT last_name, SUBSTR (

        (SELECT department_name

         FROM departments d

         WHERE d.department_id = e.department_id),

                     1, 10) department

    FROM employees e;  

SELECT employee_id, last_name

   FROM employees e

  WHERE ( ( SELECT location_id

                      FROM departments d

                    WHERE e.department_id = d.dapartment_id)

                    =

                  ( SELECT location_id

                    FROM locations I

                   WHERE STATE_province = 'California')

          )

SELECT employee_id, last_name

  FROM employees e

ORDER BY (

     SELECT department_name

     FROM departments d

     WHERE e.department_id = d.department_id

   )

SELECT employee_id, last_name,

               (

                 SELECT department_name

                 FROM departments d

                WHERE e.department_id = d.department_id

                 ) department_name

  FROM employees e

ORDER BY department

SQL> SELECT ename, deptno, sal

    2 FROM emp

    3 WHERE(deptno, sal) IN

    4                           (SELECT deptno, sal

    5                           FROM emp

    6                           WEHRE comm IS NOT NULL);

SQL> SELECT ename, hiredate

      2   FROM emp

      3   WHERE deptno = (SELECT deptno

      4                                     FROM emp

      5                                     WHERE INITCAP(ename) = 'Blake');

SQL> SELECT empno, ename, sal

      2    FROM emp

      3    WHERE sal > (SELECT AVG(sal)

      4                                        FROM  emp)

      5    ORDER BY sal desc;