[SQL] DATA MANIPULATION LANGUAGE

SELECT director, AVG(rating) AS avg_rating FROM movies 
  WHERE rating IS NOT NULL AND director IS NOT NULL
  GROUP BY director
  HAVING COUNT(*) >= 5 
  ORDER BY avg_rating DESC;
-- 작품이 5개 이상인 감독들을 평균 rating 순으로 정렬 


SELECT genres, COUNT(*) AS number_of_movies FROM movies
  GROUP BY genres
  ORDER BY number_of_movies DESC;
-- 각 장르에 몇 편의 영화가 있는지 


SELECT COUNT(*) AS higher_than_six FROM movies
  WHERE rating IS NOT NULL AND rating >= 6;
-- 평점이 6보다 높은 영화는 몇 편 있는지 


SELECT rating, COUNT(*) AS rating_count FROM movies
  GROUP BY rating
  ORDER BY rating_count DESC;
-- 가장 많은 rating 점수는?

SELECT director, AVG(rating) AS avg_rating FROM movies 
  WHERE rating IS NOT NULL AND director IS NOT NULL
  GROUP BY director
  HAVING COUNT(*) >= 5 
  ORDER BY avg_rating DESC;
-- 제작영화가 5편 이상인 감독들 중에서 avg_rating 순으로 정렬​

 SELECT * FROM movies WHERE title LIKE 'The%' ORDER BY release_date DESC, revenue DESC; 
-- title이 'The'로 시작하는 영화를 release_date가 큰 순으로 나열 -> revenue가 큰 순으로 나열

update 명령 

INSERT INTO / UPDATE / DELETE 

UPDATE movies SET rating = 9 WHERE title='Emily in Paris';
-- title이 'Emily in Paris'인 데이터의 rating을 9로

UPDATE movies SET director='Unknown' WHERE director IS NULL;
-- director가 NULL인 데이터를 'Unknown'으로 변경

 

---

 

query 명령 

SELECT: 테이블을 결과물로 제공 

SELECT title, rating FROM movies;

SELECT UPPER(title) AS title_upp FROM movies;

SELECT * FROM movies WHERE director='Netflix';

 

SELECT * FROM movies WHERE director='Guy Ritchie';

SELECT * FROM movies WHERE original_language <> 'en' ;

SELECT * FROM movies WHERE original_language = 'ko' ;

SELECT * FROM movies WHERE status = 'Planned' AND budget <> 0 ;

SELECT * FROM movies WHERE rating > 9 OR (rating IS NULL AND genres = 'Documentary') ;

SELECT * FROM movies WHERE original_language IN ('en', 'es', 'de');

SELECT * FROM movies WHERE title LIKE 'The%' ; 
-- title이 'The'로 시작하는 영화 

SELECT * FROM movies WHERE title LIKE '%Love%' ;
-- title이 'Love'를 포함하는 영화

SELECT * FROM movies WHERE title LIKE 'The __' ;
-- title이 'The ㅇㅇ'인 영화 

SELECT * FROM movies WHERE title LIKE 'The ___ %' ;
-- title이 'The ㅇㅇㅇ'로 시작하는 영화

SELECT title, CASE WHEN rating >= 8 THEN '👍' 
  WHEN 'rating' <= 6 THEN '👎' 
  ELSE '👀' END AS good_or_not
 FROM movies;

 SELECT * FROM movies WHERE title LIKE 'The%' ORDER BY release_date DESC, revenue DESC; 
-- title이 'The'로 시작하는 영화를 release_date가 큰 순으로 나열 -> revenue가 큰 순으로 나열

SELECT * FROM movies LIMIT 5 OFFSET 5;
-- 5개를 반환하되, 앞의 5개를 제외한 다음순서의 데이터 5개를 반환
-- 실행순서: FROM -> WHERE -> SELECT -> ORDER BY -> OFFSET -> LIMIT

SELECT director, SUM(revenue) AS total_revenue FROM movies 
  WHERE director IS NOT NULL AND revenue IS NOT NULL GROUP BY director ORDER BY total_revenue DESC;
-- director로 그룹화 -> director별 revenue 합계 -> total_revenue 순으로 정렬 
-- 실행순서: FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY


SELECT release_date, SUM(revenue) as total_revenue FROM movies 
  GROUP BY release_date ORDER BY total_revenue DESC;
-- relese_date별 revenue의 합계 -> total_revenue 순으로 정렬

SELECT release_date, AVG(rating) AS FROM movies GROUP BY release_date HAVING avg_rating > 6 
  ORDER BY avg_rating DESC;
-- HAVING: GROUP BY로 그룹화한 데이터들 중에서 필터링

SELECT director, printf('%,d', ROUND(AVG(revenue))) AS avg_revenue FROM movies
  GROUP BY director
  ORDER BY AVG(revenue) DESC;
-- 감독별 평균 수익으로 정렬

SELECT director, AVG(rating) AS avg_rating FROM movies 
  WHERE rating IS NOT NULL AND director IS NOT NULL
  GROUP BY director
  HAVING COUNT(*) >= 5 
  ORDER BY avg_rating DESC;
-- 작품이 5개 이상인 감독들을 평균 rating 순으로 정렬 


SELECT genres, COUNT(*) AS number_of_movies FROM movies
  GROUP BY genres
  ORDER BY number_of_movies DESC;
-- 각 장르에 몇 편의 영화가 있는지 


SELECT COUNT(*) AS higher_than_six FROM movies
  WHERE rating IS NOT NULL AND rating >= 6;
-- 평점이 6보다 높은 영화는 몇 편 있는지 


SELECT rating, COUNT(*) AS rating_count FROM movies
  GROUP BY rating
  ORDER BY rating_count DESC;
-- 가장 많은 rating 점수는?

SELECT release_date, COUNT(*) AS release_count FROM movies
  GROUP BY release_date;
-- 연도별 개봉영화 수 


SELECT release_date, AVG(runtime) AS avg_runtime FROM movies
  WHERE runtime IS NOT NULL
  GROUP BY release_date
  ORDER BY avg_runtime DESC LIMIT 10;
-- 평균 상영시간이 가장 긴 년도 상위 10개 


SELECT AVG(rating) FROM movies
  WHERE release_date >=2000 ;
-- 21세기에 개봉한 영화의 평균 평점 


SELECT director, AVG(runtime) as avg_runtime FROM movies
  GROUP BY director
  ORDER BY avg_runtime DESC;
-- 평균 영화 상영 시간이 가장 긴 감독 


SELECT director, COUNT(*) AS movie_count FROM movies
  WHERE director IS NOT NULL
  GROUP BY director
  ORDER BY movie_count DESC LIMIT 5;
-- 가장 많은 영화를 작업한 감독 상위 5명 


SELECT director, MIN(rating) AS min_rating, MAX(rating) AS max_rating, COUNT(rating) AS rating_count FROM movies
  WHERE rating IS NOT NULL AND director IS NOT NULL 
  GROUP BY director
  HAVING rating_count > 5;
-- rating이 6개 이상인 각 감독의 최고평점/최저평점 


SELECT director, SUM(revenue) - SUM(budget) AS real_revenue FROM movies
  GROUP BY director 
  ORDER BY real_revenue DESC;
-- 가장 높은 수익을 벌어들인 감독 


SELECT AVG(rating) FROM movies
  WHERE runtime >= 120; 
-- 상영시간이 2시간 이상인 영화들의 평균 평점 


SELECT release_date, COUNT(*) AS movie_count FROM movies
  GROUP BY release_date
  ORDER BY movie_count DESC;
-- 가장 많은 영화가 상영된 연도 


SELECT (release_date / 10) * 10 AS decade, COUNT(*) AS total_movies FROM movies
  GROUP BY decade
  ORDER BY total_movies DESC; 
-- 각 10년 동안의 평균 영화 상영 시간 


SELECT (release_date / 10) * 10 AS decade, SUM(runtime) / COUNT(*) AS avg_runtime FROM movies
  WHERE runtime IS NOT NULL AND release_date IS NOT NULL
  GROUP BY decade;
  --ORDER BY total_movies DESC; 
-- 각 10년 동안의 평균 영화 상영 시간

 

 

VIEW

쿼리를 저장해놓고 간편하게 불러와서 재사용할 수 있다 

CREATE VIEW v_guy_ritchie_movie AS SELECT * FROM movies
  WHERE director='Guy Ritchie';
-- VIEW 만들기 

SELECT title FROM v_guy_ritchie_movie;
-- VIEW 호출하여 사용