XUESQL中文在线SQL练习及其答案

1.多表练习

2.John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

'''

SELECT Title,(b.Domestic_sales+b.International_sales)/m.Length_minutes AS Per_sales
    FROM Movies m LEFT JOIN Boxoffice b on m.Id=b.Movie_id 
    WHERE Director='John Lasseter'
    ORDER BY Per_sales DESC LIMIT 3

'''
'''

select N.title, (B.Domestic_sales + B.International_sales)/N.length_minutes as A
    from (select * from Movies as M where M.director like "John Lasseter")"N"
    join Boxoffice as "B"
    on N.id = B.movie_id
    order by A desc
    limit 3

'''

'''

select M.title, (B.Domestic_sales + B.International_sales)/M.length_minutes as A
     from Movies as M
     join Boxoffice as B
     on M.id =B.movie_id
     where M.director like "John Lasseter"
     order by A desc
     limit 3

'''

3.找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

'''

select W.title,(-w."总销量"+T."总销量") as 销售额差额
from(select *, sum(B.Domestic_sales+B.International_sales) as 总销量
	from movies as M
	join Boxoffice  as B
	on M.id = B.movie_id
	group by M.title 
	order by "总销量" desc
	limit 1)"T", (select *, sum(B.Domestic_sales+B.International_sales) as 总销量
	from movies as M
	join Boxoffice  as B
	on M.id = B.movie_id
	group by M.title 
	order by "总销量" desc
	)"W"
    group by W.title

'''

'''

SELECT Title, (SELECT MAX(Domestic_sales+International_sales)FROM Boxoffice) - SUM(Domestic_sales+International_sales) AS Diff 
    FROM Movies 
    INNER JOIN Boxoffice 
    ON Movies.ID = Boxoffice.Movie_id 
    GROUP BY Title 

'''