SQL Lesson 6: 用JOINs进行多表联合查询
从这部分开始就是联表查询了
1.找到所有电影的线下Domestic_sales和线上销售额
1
| SELECT * FROM movies m,Boxoffice b WHERE m.Id=b.Movie_id;
|
2.找到所有线上销售额比线下销售大的电影
1 2
| SELECT * FROM movies m,Boxoffice b WHERE m.Id=b.Movie_id AND b.Domestic_sales<b.International_sales;
|
3.找出所有电影按市场占有率rating倒序排列
1 2
| SELECT * FROM movies m,Boxoffice b WHERE m.Id=b.Movie_id ORDER BY b.Rating DESC;
|
4.每部电影按线上销售额比较,排名最靠前的导演是谁,线上销量多少
1 2 3
| SELECT m.Director,b.International_sales FROM movies m,Boxoffice b WHERE m.Id=b.Movie_id ORDER BY b.International_sales DESC LIMIT 1;
|
SQL SQL Lesson 7: 外连接（OUTER JOINs）
1.找到所有有雇员的办公室(buildings)名字
1 2 3 4
| SELECT DISTINCT b.Building_name as buildings FROM employees e LEFT JOIN Buildings b ON e.Building=b.Building_name ;
|
2.找到所有办公室和他们的最大容量
1
| SELECT * FROM Buildings ;
|
3.找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)
1 2 3
| SELECT DISTINCT b.Building_name,e.Role FROM Buildings b LEFT JOIN employees e ON e.Building=b.Building_name
|
4.找到所有有雇员的办公室(buildings)和对应的容量
1 2 3 4
| SELECT DISTINCT b.Building_name as buildings,b.Capacity FROM employees e LEFT JOIN Buildings b ON e.Building=b.Building_name WHERE e.Building IS NOT NULL;
|
SQL Lesson 8: 关于特殊关键字 NULLs
1.找到雇员里还没有分配办公室的(列出名字和角色就可以)
1
| SELECT Role,Name FROM employees WHERE Building IS NULL;
|
2.到还没有雇员的办公室
1 2 3 4
| SELECT b.Building_name as buildings FROM Buildings b LEFT JOIN employees e ON e.Building=b.Building_name WHERE e.Name IS NULL;
|
SQL Lesson 9: 在查询中使用表达式
1.列出所有的电影ID,名字和销售总额(以百万美元为单位计算)
1 2 3
| SELECT m.Id,m.Title, (b.Domestic_sales+b.International_sales)/1000000 as sales FROM movies m,Boxoffice b WHERE m.Id=b.Movie_Id;
|
2.列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)
1 2
| SELECT m.Id,m.Title,b.Rating*10 as num FROM movies m,Boxoffice b WHERE m.Id=b.Movie_Id;
|
3.列出所有偶数年份的电影，需要电影ID,名字和年份
1
| SELECT Id,Title,Year FROM movies WHERE Year%2=0;
|
4.John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
1 2 3 4 5
| SELECT m.Title, (b.Domestic_sales+b.International_sales)/m.Length_minutes AS num FROM movies m,Boxoffice b WHERE m.Id=b.Movie_Id AND Director="John Lasseter" ORDER BY Num DESC LIMIT 3;
|
5.电影名最长的3部电影和他们的总销量是多少
1 2 3 4 5 6
| SELECT length(Title) AS len, m.Title,(b.Domestic_sales+b.International_sales) AS sales FROM movies m,Boxoffice b WHERE m.Id=b.Movie_Id ORDER BY len DESC LIMIT 3;
|
SQL Lesson 10: 在查询中进行统计I (Pt. 1)
1.找出就职年份最高的雇员(列出雇员名字+年份）
1 2
| SELECT Name,Years_employed FROM employees order by Years_employed desc limit 1;
|
2.按角色(Role)统计一下每个角色的平均就职年份
1
| SELECT Role,avg(Years_employed) FROM employees group by Role;
|
3.按办公室名字总计一下就职年份总和
1
| SELECT Building,sum(Years_employed) FROM employees group by Building;
|
4.每栋办公室按人数排名,不要统计无办公室的雇员
1 2 3 4
| SELECT Building,count(Name) as num FROM employees where Building is not null group by Building order by num;
|
5.就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率”50%” 记为 50)
1 2 3 4 5
| SELECT Years_employed , count(Name)*100/(select count(*) from employees) as num FROM employees where Years_employed in (1,3,5,7) group by Years_employed;
|
SQL Lesson 11: 在查询中进行统计II (Pt. 2)
1.统计一下Artist角色的雇员数量
1 2
| SELECT count(Name) FROM employees where Role="Artist" group by Role;
|
2.按角色统计一下每个角色的雇员数量
1
| SELECT Role,count(Name) FROM employees group by Role;
|
3.算出Engineer角色的就职年份总计
1 2
| SELECT sum(Years_employed) FROM employees where Role="Engineer" group by Role;
|
4.按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）
1 2 3 4 5
| select role, count(name),building is not null as pepole from employees group by role,pepole
|
SQL Lesson 12: 查询执行顺序
1.统计出每一个导演的电影数量（列出导演名字和数量）
1
| SELECT Director,count(Title) FROM movies group by Director;
|
2.统计一下每个导演的销售总额(列出导演名字和销售总额)
1 2 3 4
| SELECT m.Director, sum(b.Domestic_sales+b.International_sales) FROM movies m,Boxoffice b where m.Id=b.Movie_id group by Director;
|
3.按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)
1 2 3 4 5 6 7 8
| SELECT m.Director, sum(b.Domestic_sales+b.International_sales) as sales, count(Title) as num, sum(b.Domestic_sales+b.International_sales)/count(Title) as sn FROM movies m,Boxoffice b where m.Id=b.Movie_id group by Director having num>1 order by Sn desc limit 1;
|
4.找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额
1 2 3 4 5 6
| SELECT Title,(SELECT Domestic_sales+International_sales as sales FROM movies m,Boxoffice b where m.Id=b.Movie_id order by Sales desc limit 1)-Domestic_sales-International_sales as sd FROM movies m,Boxoffice b where m.Id=b.Movie_id;
|