SQLZOO练习题（8）

今天药忘吃喽~ 2023-10-09 15:29 21阅读 0赞

## SQLZOO练习题 （8） ##

![在这里插入图片描述][fddd25c393c146ce8ae9f3afa9d77dfd.png_pic_center]  
1.列出时间为1962年的电影编号，名称

SELECT id, title
     FROM movie
     WHERE yr=1962

![在这里插入图片描述][2ae53dc7b77a405d989835533c3ca612.png_pic_center]  
2.给出《公民凯恩》的年分。

select yr from movie where title = 'Citizen Kane'

![在这里插入图片描述][ec77fd2e6e10451780c17593b4224c41.png_pic_center]

3.列出所有的《星际迷航》电影，包括id，标题和yr(所有这些电影的标题中都包含《星际迷航》这个词)。按年排序结果。

select id,title,yr from movie where title like '%Star_Trek%'

![在这里插入图片描述][6f1b296adbde42d398f78cf3528152da.png_pic_center]  
4.演员格伦·克洛斯的id是什么?

select id from actor where name='Glenn Close'

![在这里插入图片描述][6953307468454dbca49facf1c7f348f8.png_pic_center]  
5.电影《卡萨布兰卡》的id是什么?

select id from movie where title = 'Casablanca'

![在这里插入图片描述][345984cfb93244edb4f5f18e0559d9ae.png_pic_center]  
6.获得《卡萨布兰卡》的演员名单

select name 
    from actor inner join casting 
    on actor.id = casting.actorid 
    where movieid=(select id from movie where title = 'Casablanca')

![在这里插入图片描述][7e87ee645da8496695c66d5661fcfeda.png_pic_center]  
7.获得电影《异形》的演员名单

select name 
    from actor inner join casting 
    on actor.id = casting.actorid 
    where movieid=(select id from movie where title = 'Alien')

![在这里插入图片描述][338bd6f685d7491eac0dc2f8d7b037c3.png_pic_center]  
8.列出“哈里森·福特”出演过的电影

select title 
    from movie inner join casting 
    on movie.id = casting.movieid 
    where actorid = (select id from actor where name = 'Harrison Ford')

![在这里插入图片描述][22c19a339c9d43fb8d753ab4fa629f2d.png_pic_center]  
9.列出“哈里森·福特”出现过但不是主演的电影。\[注意:casting的ord字段给出了演员的位置。如果ord=1，那么这个演员是主演角色\]

select title 
    from movie inner join casting 
    on movie.id = casting.movieid 
    where actorid = (select id from actor where name = 'Harrison Ford') and ord != 1

![在这里插入图片描述][9fd1b6cc1e954dabbf389e83d853384f.png_pic_center]  
10.列出1962年所有电影的主演。

select title,name 
    from movie inner join casting 
    on movie.id = casting.movieid 
    inner join actor  
    on casting.actorid = actor.id 
    where yr = 1962 and ord =1

![在这里插入图片描述][c26341028ea64dbfbfbf886726b02ab3.png_pic_center]  
11.“洛克·哈德森”最繁忙的年份，显示年份和他每年制作的电影数量其中任何一年他制作了超过2部电影。

SELECT yr,COUNT(title) 
    FROM movie JOIN casting 
    ON movie.id=movieid 
    JOIN actor 
    ON actorid=actor.id 
    WHERE name='Doris Day' GROUP BY yr HAVING COUNT(title) > 1

![在这里插入图片描述][3f6b02da4dfe43efb1eb4176956c89d0.png_pic_center]

12.列出朱莉·安德鲁斯出演的所有电影的片名和主角。

select title,name 
    from actor inner join casting 
    on actor.id = casting.actorid  
    inner join movie 
    on casting.movieid = movie.id 
    where movieid in 
      (
       select movieid 
       from casting inner join actor 
       on casting.actorid = actor.id 
       where name = 'Julie Andrews'
       ) 
    and ord = 1

![在这里插入图片描述][f4fe9b0d3a374392969dc0b24f3379bc.png_pic_center]  
13.按字母顺序列出至少有15个主演角色的演员名单

select name 
    from actor inner join casting 
    on actor.id = casting.actorid 
    where ord = 1 group by name having count(name)>=15 order by name

![在这里插入图片描述][df2a2d7d4fd2431baf8f564de35b199c.png_pic_center]  
14.列出1978年上映的电影，先按演员人数排序，再按片名排序。

select title,count(title)  
    from movie inner join casting 
    on movie.id = casting.movieid 
    inner join actor 
    on actor.id = casting.actorid 
    where yr = 1978 group by title order by count(title) desc,title

![在这里插入图片描述][5b9ab857c6264894a09cdb21451e6056.png_pic_center]  
15.列出所有与阿特·加芬克尔合作过的人。

select name 
    from actor inner join casting 
    on actor.id = casting.actorid 
    where movieid in 
       (
        select movieid 
        from casting inner join actor 
        on actor.id = casting.actorid 
        where name = 'Art Garfunkel'
        )
      and name !='Art Garfunkel' order by name

![在这里插入图片描述][4f368231583a4242906832fa5ed1abad.png_pic_center]

[fddd25c393c146ce8ae9f3afa9d77dfd.png_pic_center]: https://img-blog.csdnimg.cn/fddd25c393c146ce8ae9f3afa9d77dfd.png#pic_center
[2ae53dc7b77a405d989835533c3ca612.png_pic_center]: https://img-blog.csdnimg.cn/2ae53dc7b77a405d989835533c3ca612.png#pic_center
[ec77fd2e6e10451780c17593b4224c41.png_pic_center]: https://img-blog.csdnimg.cn/ec77fd2e6e10451780c17593b4224c41.png#pic_center
[6f1b296adbde42d398f78cf3528152da.png_pic_center]: https://img-blog.csdnimg.cn/6f1b296adbde42d398f78cf3528152da.png#pic_center
[6953307468454dbca49facf1c7f348f8.png_pic_center]: https://img-blog.csdnimg.cn/6953307468454dbca49facf1c7f348f8.png#pic_center
[345984cfb93244edb4f5f18e0559d9ae.png_pic_center]: https://img-blog.csdnimg.cn/345984cfb93244edb4f5f18e0559d9ae.png#pic_center
[7e87ee645da8496695c66d5661fcfeda.png_pic_center]: https://img-blog.csdnimg.cn/7e87ee645da8496695c66d5661fcfeda.png#pic_center
[338bd6f685d7491eac0dc2f8d7b037c3.png_pic_center]: https://img-blog.csdnimg.cn/338bd6f685d7491eac0dc2f8d7b037c3.png#pic_center
[22c19a339c9d43fb8d753ab4fa629f2d.png_pic_center]: https://img-blog.csdnimg.cn/22c19a339c9d43fb8d753ab4fa629f2d.png#pic_center
[9fd1b6cc1e954dabbf389e83d853384f.png_pic_center]: https://img-blog.csdnimg.cn/9fd1b6cc1e954dabbf389e83d853384f.png#pic_center
[c26341028ea64dbfbfbf886726b02ab3.png_pic_center]: https://img-blog.csdnimg.cn/c26341028ea64dbfbfbf886726b02ab3.png#pic_center
[3f6b02da4dfe43efb1eb4176956c89d0.png_pic_center]: https://img-blog.csdnimg.cn/3f6b02da4dfe43efb1eb4176956c89d0.png#pic_center
[f4fe9b0d3a374392969dc0b24f3379bc.png_pic_center]: https://img-blog.csdnimg.cn/f4fe9b0d3a374392969dc0b24f3379bc.png#pic_center
[df2a2d7d4fd2431baf8f564de35b199c.png_pic_center]: https://img-blog.csdnimg.cn/df2a2d7d4fd2431baf8f564de35b199c.png#pic_center
[5b9ab857c6264894a09cdb21451e6056.png_pic_center]: https://img-blog.csdnimg.cn/5b9ab857c6264894a09cdb21451e6056.png#pic_center
[4f368231583a4242906832fa5ed1abad.png_pic_center]: https://img-blog.csdnimg.cn/4f368231583a4242906832fa5ed1abad.png#pic_center