自学SQL习题答案整理（lesson4--）

前几天看到一个学SQL的网站，感觉挺好的，但是比较少人用。链接：自学SQL。在这里放上一部分题目的答案，自己在mysql里实现了一下，方便以后再做这个练习的时候自查，主要是学习一些查询语句的运用：

SELECT distinct director from movies; 

SELECT * FROM movies order by year limit 10,4;

SELECT * FROM movies order by title limit 5;

SELECT * FROM movies order by title limit 5,5;

SELECT title FROM movies where director="John Lasseter" order by length_minutes limit 2,1;

select * from movies

order by director asc,year desc

limit 10

---

SELECT * FROM north_american_cities where country="Canada";

SELECT * FROM north_american_cities where country="United States" order by Latitude;

SELECT * FROM north_american_cities where longitude<-87.629798 order by longitude desc;

SELECT * FROM north_american_cities where Country="Mexico"

order by population desc

limit 2

SELECT * FROM north_american_cities

where Country="United States"

order by Population desc

limit 2,2

SELECT * FROM north_american_cities

order by Country asc,Population desc

limit 10

---

 SQL Lesson 6: 用JOINs进行多表联合查询

1.找到所有电影的国内Domestic_sales和国际销售额 

SELECT * FROM Movies

INNER JOIN Boxoffice

WHERE Id=Movie_id

 2.找到所有国际销售额比国内销售大的电影 

SELECT * FROM Movies

INNER JOIN Boxoffice

WHERE Id=Movie_id AND Domestic_sales<International_sales

3.找出所有电影按市场占有率rating倒序排列

SELECT * FROM Movies

INNER JOIN Boxoffice

WHERE Id=Movie_id

ORDER BY Rating DESC

4. 每部电影按国际销售额比较,排名最靠前的导演是谁,线上销量多少

select m.Director,International_sales FROM movies as m
inner join Boxoffice as B
on m.Id=B.Movie_id
order by International_sales desc
limit 1

---

 SQL Lesson 7: 外连接（OUTER JOINs）

1.找到所有有雇员的办公室(buildings)名字 

SELECT DISTINCT Building FROM employees

WHERE Building IS NOT NULL;

2.到所有办公室和他们的最大容量

SELECT * FROM Buildings

3.找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

SELECT DISTINCT building_name, Role FROM Buildings
LEFT JOIN Employees
    ON Employees.Building  = Buildings.Building_name

4.找到所有有雇员的办公室(buildings)和对应的容量（利用子查询）

SELECT * FROM Buildings

WHERE Building_name IN (

SELECT DISTINCT Building FROM Employees

WHERE Building IS NOT NULL

)

---

SQL Lesson 8: 关于特殊关键字 NULLs

1.找到雇员里还没有分配办公室的(列出名字和角色就可以) 

SELECT name,Role FROM employees

WHERE Building IS NULL;

2.找到还没有雇员的办公室

SELECT Building_name FROM Buildings

WHERE Building_name NOT IN (

SELECT DISTINCT Building FROM Employees

WHERE Building IS NOT NULL);

---

 SQL Lesson 9: 在查询中使用表达式

1.列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

SELECT Id,Title,(Domestic_sales+International_sales)/1000000 as Total_sales  
FROM Movies

INNER JOIN Boxoffice

WHERE Id=Movie_id;

2.列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

SELECT Id,Title,Rating*10 AS market_index FROM Movies

INNER JOIN Boxoffice

WHERE Id=Movie_id;

3.列出所有偶数年份的电影，需要电影ID,名字和年份 

SELECT Id,Title,year From Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id AND year%2=0;

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

SELECT Title,per_value FROM

(
SELECT *,(Domestic_sales+International_sales) AS
Total_sales,(Domestic_sales+International_sales)/Length_minutes AS
per_value FROM Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id AND Director="John Lasseter"

）
ORDER BY per_value DESC
LIMIT 3;

5.电影名最长的3部电影和他们的总销量是多少

SELECT Title,Total_sales FROM 
(
SELECT *,(Domestic_sales+International_sales) AS
Total_sales,LENGTH(Title) AS
len_movie FROM Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id
ORDER BY len_movie DESC
)
LIMIT 3;

---

SQL Lesson 10: 在查询中进行统计I (Pt. 1)

1.找出就职年份最高的雇员(列出雇员名字+年份） 

SELECT Name,MAX(Years_employed) FROM employees;

2.按角色(Role)统计一下每个角色的平均就职年份 

SELECT Role,nums*1.0/num FROM

(SELECT Role,count(role) as num,sum(Years_employed) as nums from Employees

GROUP BY Role) AS Statistics

3.按办公室名字总计一下就职年份总和 

SELECT Building,SUM(Years_employed) FROM Employees

GROUP BY Building;

4.每栋办公室按人数排名,不要统计无办公室的雇员 ✓

SELECT Building,COUNT(Building) FROM Employees

WHERE Building IS NOT Null

GROUP BY Building;

5.就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)

SELECT Years_employed,num*100.0/15 FROM(

SELECT Years_employed,COUNT(Years_employed) AS num FROM Employees

WHERE Years_employed%2=1 AND Years_employed<8

GROUP BY Years_employed)

---

SQL Lesson 11: 在查询中进行统计II (Pt. 2) 

1.统计一下Artist角色的雇员数量

SELECT COUNT(*) FROM employees

WHERE Role="Artist";

2.按角色统计一下每个角色的雇员数量 

SELECT Role,COUNT(Role) FROM employees

GROUP BY Role;

3.算出Engineer角色的就职年份总计

SELECT SUM(Years_employed) FROM employees

WHERE Role="Engineer"

GROUP BY Role;

4.按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

SELECT Role,case when building is null then "无办公室"
else "有办公室" end as 有无办公室
,count(Name)
FROM employees
group by role,有无办公室 

5.按角色和就职年份统计人数,年份按0-3，3-6，6-9这种阶梯分组，最后按角色+阶梯分组排序 

select Role,case when Years_employed<3 then "0-3"
when Years_employed<6 then "3-6"
else "6-9" end as "就职年份"
,count(*)
from Employees
group by Role,"就职年份"