データベース系覚書き

Select文覚書きとまとめ

【条件】

3つの表からなるデータベースがある

movie表-----------------------------------

id ：　 映画ID

title ： 映画名

yr ：　 公開年

director: 監督

budget ：予算

gross ：興行収入

actor表-----------------------------------

id： 俳優女優ID

name ：俳優女優氏名

casting表-----------------------------------

movieid： movie表のidへの外部キー

actorid： actor表のidへの外部キー

ord ： 俳優の出演表中の順位（主演1、助演2など）

-----------------------------------

１）指定された年に公開された映画のIDとタイトルを指定された件数のみ表示

select id, title from movie
   where yr = 2009    //年を指定
   limit 100;        //表示上限指定
   

２）titleを指定して公開年を表示

select yr from movie
   where title = "Citizen Kane";    //タイトル指定

３）指定の文言を含む映画のタイトルと公開年を表示

select title, yr from movie
   where title like '%Dog%';    //タイトル指定

４）指定の文字列をタイトルに含む映画の、タイトルと公開年を表示

select title, yr from movie
   where title like '%Star Wars%';

５）指定タイトルの映画についてidを表示

select id from movie
   where title like 'American Graffiti';

６）指定の俳優のidを調べる

select id from actor
   where name like 'Michael Douglas';

７）指定タイトルの映画のidを調べる

select id from movie
   where title like 'Casablanca';

８）指定されたidの映画に出演している俳優・女優の名前を表示/表の結合

SELECT a.name FROM movie m
   join casting c on m.id = c.movieid
   join actor a on c.actorid = a.id
  and m.id = 17785
;

９）指定されたタイトルの映画に出演している俳優の名前を表示

select a.name from movie m
   join casting c on m.id = c.movieid
   join actor a on c.actorid = a.id
   and title like 'Alien'

10）指定された俳優が出演している映画タイトル全て表示

select title from movie m
   join casting c 
   on m.id = c.movieid
   join actor a
   on c.actorid = a.id
   and name = 'Harrison Ford'
;

11）指定された年に公表された全ての映画タイトルと，主演俳優（女優）名を表示

select title, a.name from movie m
   join casting c 
   on m.id = c.movieid
   join actor a
   on c.actorid = a.id
   and c.ord = 1
   where m.yr = '1962'
;

12）指定された俳優が映画に出演した年と、それぞれの年に何回出演したかを調べる

select yr, count(title) from movie m
   join casting c     on m.id = c.movieid
   join actor a    on c.actorid = a.id
   and name = 'Harrison Ford'
   group by yr
;

13）指定された年に公開の映画タイトルと出演者数を出演者数が多い順に並べて表示

select m.title, count(c.ord) as ord_num from movie m
   join casting c     on m.id = c.movieid
   join actor a    on c.actorid = a.id
   and m.yr = '2000'
   group by m.title
   order by ord_num DESC
;

14）10回以上主演している俳優女優の、名前と出演回数を調べる

select actor.name, count(casting.ord = 1) from actor
   join casting on actor.id = casting.actorid
   
   where casting.ord=1
   group by actor.name
   having count(*)>=10
   order by casting.actorid
;

15）指定された俳優と共演したことのある俳優の名前を表示する。複数回共演した場合は重複無し、また指定された俳優自身は除外

SELECT DISTINCT name FROM actor, movie, casting
   WHERE name <> 'Sandra Bullock'
   AND movie.id = casting.movieid
   AND casting.actorid = actor.id
   AND movieid IN ( SELECT movieid FROM casting, actor
       WHERE actor.id = actorid AND name = 'Sandra Bullock' )
;