SELECT id, title FROM movie WHERE yr=1962
select yr from movie where title like 'Citizen Kane';
3.Star Trek movies
List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.
select id, title, yr from movie where title like 'Star Trek%';
select id from actor where name like 'Glenn Close';
select id from movie where title like 'Casablanca';
SELECT actor.name FROM actor JOIN casting ON casting.actorid = actor.id join movie on casting.movieid = movie.id WHERE movie.title = 'casablanca';
select actor.name from actor join casting on casting.actorid = actor.id where casting.movieid = (select id from movie where title = 'Alien');
select a.title from movie a join casting b on a.id = b.movieid join actor c on b.actorid = c.id where c.name = 'Harrison Ford';
select a.title from movie a join casting b on a.id = b.movieid join actor c on b.actorid = c.id where c.name = 'Harrison Ford' and b.ord != 1;
select a.title, c.name from movie a join casting b on a.id = b.movieid join actor c on b.actorid = c.id where a.yr = 1962 and b.ord = 1;
select yr,COUNT(title) from movie join casting on movie.id=movieid join actor on actorid=actor.id where name='Rock Hudson' group by yr having count(title) > 1;
SELECT movie.title, actor.name FROM movie JOIN casting ON movie.id = casting.movieid JOIN actor ON actor.id = casting.actorid WHERE movie.id IN (SELECT casting.movieId FROM casting JOIN actor ON casting.actorid = actor.id where actor.name = 'Julie Andrews') AND casting.ord = 1 /*제일 어려웠던 문제 서브쿼리를 이용하여 JA가 출연한 영화의 리스트를 먼저 뽑으면 의외로 쉽게 풀렸다. 이걸로 이틀 고민한건 안비밀 */
select actor.name from casting join actor on actor.id = casting.actorid where ord = 1 group by actor.name having count(ord) >= 15
select movie.title, count(casting.ord) from movie join casting on casting.movieid = movie.id where movie.yr = 1978 group by movie.title order by count(casting.ord) desc, movie.title;
select distinct actor.name from casting join actor on casting.actorid = actor.id where casting.movieid in (select casting.movieid from casting join actor on casting.actorid = actor.id where actor.name = 'Art Garfunkel') and actor.name != 'Art Garfunkel' order by actor.name; // 'Art Garfunkel' 이름은 써주기
sql은 python처럼 정해진 형식이 없어서
문제를 풀 때 마다 내가 조금씩 다른 형식으로 코드를 짠다는 걸 느꼈다.
코드를 일관성있게 통일하고, 대문자/소문자를 구분하는 연습도 해야겠다.