image.png
找到所有电影的国内Domestic_sales和国际销售额
SELECT * FROM movies inner join Boxoffice on movies.id = Boxoffice.movie_id;
找到所有国际销售额比国内销售大的电影
SELECT * FROM movies inner join Boxoffice on movies.id = Boxoffice.movie_id where Boxoffice.International_sales > Boxoffice.Domestic_sales;
找出所有电影按市场占有率rating倒序排列
SELECT * FROM movies inner join Boxoffice on movies.id = Boxoffice.movie_id order by rating desc;
每部电影按国际销售额比较,排名最靠前的导演是谁,线上销量多少
SELECT Director,International_sales FROM movies inner join Boxoffice on movies.id = Boxoffice.movie_id order by International_sales desc limit 1;
找到所有有雇员的办公室(buildings)名字
SELECT distinct Building FROM employees where Building not null;
SELECT DISTINCT bui.Building_name FROM Employees emp left join Buildings bui on bui.Building_name = emp.Building;
找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)
select distinct building_name,role from Buildings b left join employees e on b.building_name =e.building;
找到所有有雇员的办公室(buildings)和对应的容量
select distinct Building,Capacity from Buildings b left join employees e on b.building_name =e.building where e.building not null;
找到还没有雇员的办公室
SELECT distinct b.Building_name FROM Buildings b left join employees e on e.building = b.Building_name where e.name is null;
image.png
列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
SELECT id,title,(Domestic_sales+International_sales) / 1000000 as sale FROM movies m left join Boxoffice b on m.id=b.movie_id;
列出所有偶数年份的电影，需要电影ID,名字和年份
SELECT id,title,(Rating)*10 as rating FROM movies m left join Boxoffice b on m.id=b.movie_id;
列出所有偶数年份的电影，需要电影ID,名字和年份
SELECT id,title,year as rating FROM movies m left join Boxoffice b on m.id=b.movie_id where (year % 2) = 0;
John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
SELECT title,(Domestic_sales+International_sales)/Length_minutes as value FROM movies m left join Boxoffice b on m.id=b.movie_id where Director = 'John Lasseter' order by value desc limit 3;
电影名最长的3部电影和他们的总销量是多少
SELECT title,length(title),(Domestic_sales+International_sales) as value FROM movies m left join Boxoffice b on m.id=b.movie_id order by length(title) desc limit 3;
