SQL 쿼리03-정렬

데이터를 내맘대로 정렬한다.

3.1) 정렬 (3-9강 P94) : ORDER BY 열명 [ASC] DESC

  SELECT * FROM films  ORDER BY id

  SELECT * FROM  films ORDER BY id DESC

3.2) 다수 정렬 (3-10강 P.102)

  SELECT * FROM  films ORDER BY id , release_year;

  SELECT * FROM  films ORDER BY id DESC , release_year;

3.3) 특정 수만 조회(3-11강 P.108) :  LIMIT 숫자;

  SELECT * FROM films WHERE country="USA" LIMIT 2;

  SELECT * FROM films WHERE country<>'USA" ORDER BY id DESC LIMIT 4;

  SELECT * FROM films WHERE country<>'USA' ORDER BY id DESC LIMIT 4 OFFSET 2;

3.4) 사치연산 (3-12강 P.114) : + - / * %

  SELECT num_user + num_critic AS Fit1  FROM reviews ;

  SELECT num_user + num_critic AS Fit1 FROM reviews WHERE num_user > 600;

  SELECT num_user + num_critic AS fit1 FROM reviews WHERE num_user > 600 ORDER BY id DESC;

  SELECT num_user + num_critic AS fit1 FROM reviews WHERE num_user < 600 ORDER BY id DESC;

  SELECT imdb_score1 FROM reviews;

SELECT * FROM films WHERE duration /60 > 4;

SELECT duration /60,* FROM films WHERE duration /60 > 3 ORDER BY duration /60  DESC

3.5) 문자열 결합 (3-13강 P.130) : ||

SELECT title||country FROM films;

  SELECT substr(title,3) ||  " OF "||country FROM films;

  SELECT substr(title,3) ,trim(title) FROM films;

3.6) 날짜연산 (3-14강 P.136) : CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP

SELECT CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP

  쿼리 실행안됨 --SELECT CURRENT_DATE + INTERVAL 1 DAY

3.7) CASE문(3-15강 P.140) : CASE WHEN  조건 THEN 결과 ELSE 결과 END

SELECT CASE WHEN DURATION/60 > 3 THEN "LING MOVIE" END AS TYPE1,* FROM films;