day25 sql查询练习

6. AddressId 是上表主键 编写⼀个 SQL 查询，满⾜条件：⽆论 person 是否有地址信息，都需要基于上述两 表提供 person 的以下信息： FirstName, LastName, City, State

SELECT FirstName,LastName,city,state FROM Address a ,person2 b WHERE a.`PersonId`=b.`PersonId`

7. 给定⼀个 salary 表，如下所示，有 m = 男性 和 f = ⼥性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使⽤⼀个更新 （Update）语句，并且没有中间的临时表。 注意，您必只能写⼀个 Update 语句，请不要编写任何 Select 语句。

UPDATE salary SET sex = 
	CASE sex 
	WHEN 'f' THEN 'm'
	ELSE 'f' END;

8. 给定表 customer ，⾥⾯保存了所有客户信息和他们的推荐⼈。写⼀个查询语句，返回⼀个编号列表，列表中编号的推荐⼈的编号都 不是 2。 对于上⾯的示例数据，结果为

SELECT * FROM customer WHERE referee_id IS NULL OR referee_id != 2;

9. timestamp 是这张表的主键. 写⼀条SQL查询语句获取合作过⾄少三次的演员和导演的 id 对 (actor_id, director_id)

SELECT director_id,actor_id FROM ActorDirector GROUP BY director_id,actor_id HAVING COUNT(*) >= 3

10. 选出所有 bonus < 1000 的员⼯的 name 及其 bonus。 Employee 表单

SELECT NAME,Bonus FROM Employee LEFT JOIN Bonus ON Employee.`empId`=bonus.`empId` WHERE Bonus IS NULL OR bonus < 1000;

11. Employee 表包含所有员⼯，他们的经理也属于员⼯。每个员⼯都有⼀个 Id，此外 还有⼀列对应员⼯的经理的 Id ,给定 Employee 表，编写⼀个 SQL 查询，该查询可以获取收⼊超过他们经理的员 ⼯的姓名。在上⾯的表格中，Joe 是唯⼀⼀个收⼊超过他的经理的员⼯。

SELECT a.`name` FROM employee2 a,employee2 b WHERE a.managerId = b.empId AND a.`salary`>b.`salary`

12. 某⽹站包含两个表，Customers 表和 Orders 表。编写⼀个 SQL 查询，找出所 有从不订购任何东⻄的客户。 例如给定上述表格，你的查询应返回：

SELECT * FROM Customers2 WHERE id NOT IN (SELECT DISTINCT customerId FROM order2)

13. ⼀个⼩学⽣ Tim 的作业是判断三条线段是否能形成⼀个三⻆形。 然⽽，这个作业⾮常繁重，因为有⼏百组线段需要判断。 假设表 table 保存了所有三条线段的三元组 x, y, z ，你能帮 Tim 写⼀个查 询语句，来判断每个三元组是否可以组成⼀个三⻆形吗？

SELECT *, IF(X+Y>z AND X+z>Y AND Y+z>Y,'true','false') AS flag FROM table1;

14. 描述 给定 3 个表： salesperson， company， orders。 输出所有表 salesperson 中，没有向公司 ‘RED’ 销售任何东⻄的销售员。 根据表 orders 中的订单 ‘3’ 和 ‘4’ ，容易看出只有 ‘John’ 和 ‘Pam’ 两 个销售员曾经向公司 ‘RED’ 销售过。 所以我们需要输出表 salesperson 中所有其他⼈的

SELECT NAME FROM salesPerson WHERE sales_id NOT IN (SELECT sales_id FROM company,orders3 WHERE company.`com_id` = orders3.`com_id` AND company.`name` = 'RED');

15. 编写⼀个 SQL 查询，来删除 Person 表中所有重复的电⼦邮箱，重复的邮箱⾥只 保留 Id 最⼩ 的那个。

DELETE a FROM person3 a,person3 b WHERE a.email = b.email AND a.id > b.id;

16. 给定⼀个 Weather 表，编写⼀个 SQL 查询，来查找与之前（昨天的）⽇期相⽐ 温度更⾼的所有⽇期的 Id。

SELECT w1.id FROM weather w1 ,weather w2 WHERE w1.recorddate - w2.recorddate = 1 AND w1.temperature > w2.temperature

17. 表 my_numbers 的 num 字段包含很多数字，其中包括很多重复的数字。 你能写⼀个 SQL 查询语句，找到只出现过⼀次的数字中，最⼤的⼀个数字吗？

SELECT IFNULL((SELECT num FROM my_numbers GROUP BY num HAVING COUNT(num) =1 ORDER BY num DESC LIMIT 1),NULL) num; 

18. 有⼀个courses 表 ，有: student (学⽣) 和 class (课程)。 请列出所有超过或等于5名学⽣的课。

SELECT courses.`class`,COUNT(courses.`class`) c FROM courses GROUP BY courses.`class` HAVING c > 5

19. 在 Facebook 或者 Twitter 这样的社交应⽤中，⼈们经常会发好友申请也会收 到其他⼈的好友申请。现在给如下两个表： 写⼀个查询语句，求出好友申请的通过率，⽤ 2 位⼩数表示。通过率由接受好友申 请的数⽬除以申请总数。

SELECT ROUND(
	(SELECT COUNT(*) c FROM (SELECT * FROM request_accepted GROUP BY requester_id,accepter_id) temp)
	/(SELECT COUNT(*) f FROM (SELECT * FROM friend_request GROUP BY sender_id,send_to_id) temp),2)
	AS result

20. ⼏个朋友来到电影院的售票处，准备预约连续空余座位。 你能利⽤表 cinema ，帮他们写⼀个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

SELECT 
	DISTINCT c1.seat_id
	-- *
FROM 
	cinema2 c1
LEFT JOIN 
	cinema2 c2
ON 
	ABS(c1.seat_id - c2.seat_id) = 1
WHERE 
	c1.free = 1 AND c2.free = 1
ORDER BY c1.seat_id;