自学SQL网题解
自学SQL网(6-12课题解)
本文章尽量用简洁的代码去完成题目要求,话不多说,上代码。
SQL Lesson 6: 用JOINs进行多表联合查询
1.找到所有电影的国内Domestic_sales和国际销售额
SELECT * FROM movies
JOIN boxoffice
ON movies.id=boxoffice.movie_id;
2.找到所有国际销售额比国内销售大的电影
SELECT * FROM movies
join boxoffice
on movies.id=boxoffice.movie_id
where Domestic_sales <International_sales;
3.找出所有电影按市场占有率rating倒序排列
SELECT * FROM movies
join boxoffice
on movies.id=boxoffice.movie_id;
4.每部电影按国际销售额比较,排名最靠前的导演是谁,线上销量多少
SELECT Director,International_sales FROM movies
join boxoffice
on movies.id=boxoffice.movie_id
order by International_sales desc
limit 1;
SQL Lesson 7: 外连接(OUTER JOINs)
1.找到所有有雇员的办公室(buildings)名字
SELECT distinct Building_name FROM employees
left join Buildings
on employees.Building=Buildings.Building_name
where Building;
2.找到所有办公室和他们的最大容量
SELECT distinct Building_name,Capacity FROM Buildings;
3.找到所有办公室里的所有角色(包含没有雇员的),并做唯一输出(DISTINCT)
SELECT distinct role,Building_name FROM Buildings
left join Employees
on Buildings.Building_name = Employees. Building;
4.找到所有有雇员的办公室(buildings)和对应的容量
SELECT distinct Building_name,Capacity FROM Buildings
left join Employees
on Buildings.Building_name = Employees. Building
where B