SQL 정리

posted by donghyun

2 min read

태그

인덱스를 사용하지 못하는 SQL 문장은 지양

  1. INDEX COLUMN의 변형

    SELECT *
    FROM DEPT
    WHERE SUBSTR(DNAME, 1, 3) = 'ABC'
  2. NOT Operator

    SELECT *
    FROM DEPT
    WHERE SUBSTR(DNAME, 1, 3) = 'ABC'
  3. NULL or NOT NULL VALUE의 비교

    SELECT *
    FROM EMP
    WHERE ENAME IS NOT NULL
  4. Optimizer의 취사선택

     SELECT  *
     FROM    EMP
     WHERE   JOB   LIKE 'AB%'
     AND     EMPNO = '7890'

SQL 연습

-- 10th October 2012의 평균 주문량보다 큰 주문량을 모두 구하시오
SELECT   *
FROM  ORDERS
WHERE   PURCH_AMT > (
     SELECT  AVG(PURCH_AMT)
     FROM  ORDERS
     WHERE ORD_DATE = '10/10/2012');
--Write a query in SQL to list all the information of the actors who played a role in the movie 'Annie Hall'
SELECT *
FROM  ACTOR A
WHERE  ACT_ID IN (
   SELECT  ACT_ID
   FROM  MOVIE_CAST B
   WHERE  MOV_ID IN (
               SELECT MOV_ID
               FROM MOVIE
               WHERE MOV_TITLE = 'Annie Hall'
            ));
-- Write a query in SQL to find the titles of all movies directed by the director whose first and last name sare Woddy Allen.
SELECT  MOV_TITLE
FROM MOVIE
WHERE MOV_ID IN (
   SELECT  MOV_ID
   FROM MOVIE_DIRECTION
   WHERE DIR_ID IN (
               SELECT  DIR_ID
               FROM DIRECTOR
               WHERE  DIR_FNAME = 'Woody' AND DIR_LNAME = 'Allen'));