一、简单
175.组合两个表
![632810a06b7a6e346d58fe628505bfcf.png](https://i-blog.csdnimg.cn/blog_migrate/94a929855579115b3c5fdc2bafef535f.png)
![1fb0eba0015400f6fe69e420304e6d36.png](https://i-blog.csdnimg.cn/blog_migrate/c77580c58ffaaa7aefddcd0a976c6313.png)
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
![7fc995393736f9c912c7b1a137db5eb0.png](https://i-blog.csdnimg.cn/blog_migrate/7f61037d228ac853ca49c2c6d2c44051.png)
select FirstName, LastName, City, State
from Person p
left join Address a on p.PersonId = a.PersonId;
176.第二高薪水
编写一个 SQL 查询,获取Employee
表中第二高的薪水(Salary) 。
![c06b86fba9f67bc6e325fc302ef8b97b.png](https://i-blog.csdnimg.cn/blog_migrate/9d5f5839add78fa337b719d2d52f189d.png)
例如上述 Employee
表,SQL查询应该返回 200
作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回null
。
![7539ff7ee667504b890d4149931b747e.png](https://i-blog.csdnimg.cn/blog_migrate/c154beb3a460a9ae8b291a36e2613bde.png)
select max(Salary) as SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee);
181.超过经理收入的员工
Employee
表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
![2378dd53c995f28c2cf80d5f697fe299.png](https://i-blog.csdnimg.cn/blog_migrate/762c304c1f4711100ae7f8bb4206e67b.png)
给定 Employee
表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
![a683663a3d66b531ee19264602a1171d.png](https://i-blog.csdnimg.cn/blog_migrate/4455de2ec1801846ac3d3507154187da.png)
select a.Name as Employee
from Employee a, Employee b
where a.ManagerId = b.Id and a.Salary > b.Salary;
182.查找重复的电子邮箱
编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱。
![9e358c676f58c8334157295064cddfdc.png](https://i-blog.csdnimg.cn/blog_migrate/a0d243d1554887b0b7bbfec277632ac4.png)
根据以上输入,你的查询应返回以下结果:
![0f5e3cdba0ecf54165c870f80f535f72.png](https://i-blog.csdnimg.cn/blog_migrate/64e9f4ddb04713cc38802b4c92b62694.png)
select c.Email as Email
from
(select Email, count(Email) as co_email from Person group by Email) c
where c.co_email > 1;
183.从不订购的客户
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
![abd5c8bc3b1ac7c01777e2526f4098b1.png](https://i-blog.csdnimg.cn/blog_migrate/abcc9073e23a956a297d6f1b654e0d05.png)
Orders
表:
![a05e891ddf1eb4bd9112d3a52717f1cb.png](https://i-blog.csdnimg.cn/blog_migrate/95caed6beccb2f7c6214673e0a05ade5.png)
例如给定上述表格,你的查询应返回:
![f76e0543497494ed392dd84eb9096463.png](https://i-blog.csdnimg.cn/blog_migrate/4eae120d2c01cc9eb83132bbfd9f4c3c.png)
select name as Customers
from(
select c.Name as name, o.Id as OrderId
from Customers c
left join Orders o on c.Id = o.CustomerId
) co
where OrderId is null;
196.删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person
表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
![f7f458d910c466b5327f133c114ec0df.png](https://i-blog.csdnimg.cn/blog_migrate/951636bc0e350daf2087f259dd9cc2b9.png)
例如,在运行你的查询语句之后,上面的Person
表应返回以下几行:
![275bf4e5a950ef2f0f6a6f1d78e877a4.png](https://i-blog.csdnimg.cn/blog_migrate/f5c8f07474fc51cffc8289391a43aad9.png)
DELETE p1
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id;
197.上升的温度
给定一个Weather
表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
![faecef334551117e37e6c28ad7c1bdbd.png](https://i-blog.csdnimg.cn/blog_migrate/809cdde59957ae5ee63da804ef5fe6ed.png)
例如,根据上述给定的Weather
表格,返回如下 Id:
![337e893602c2762acf0c450ff1ad447a.png](https://i-blog.csdnimg.cn/blog_migrate/c234e6f7a12a58aa0b21c7dd4f17c64b.png)
(1)TO_DAYS函数,用来将日期换算成天数
select w1.Id
from Weather w1, Weather w2
where w1.Temperature > w2.Temperature and TO_DAYS(w1.RecordDate) = TO_DAYS(w2.RecordDate) + 1;
(2)DataDiff函数计算两个日期的差值
select w1.Id
from Weather w1, Weather w2
where w1.Temperature > w2.Temperature and datediff(w1.RecordDate,w2.RecordDate) = 1;
(3)SUBDATE函数,实现日期减一
select w1.Id
from Weather w1, Weather w2
where w1.Temperature > w2.Temperature and subdate(w1.RecordDate,1) = w2.RecordDate;
596.超过5名学生的课
有一个courses
表 ,有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
![475e809c43423cac1471c338bb122485.png](https://i-blog.csdnimg.cn/blog_migrate/a01e8e1fc6990446ed2ec5b23248f5f0.png)
应该输出:
![d53b3739288757e2abadc68ad0615579.png](https://i-blog.csdnimg.cn/blog_migrate/1ebbfa66a4850c889165bdefe304d062.png)
Note:
学生在每个课中不应被重复计算。
select class
from(
select class, count(distinct student) as num
from courses
group by class
) as c
where num >= 5;
620.有趣的电影
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。
例如,下表 cinema:
![0b9501c0927335882cf306382d212242.png](https://i-blog.csdnimg.cn/blog_migrate/8a1961a15bc107c92c15c9e184e06d2d.png)
对于上面的例子,则正确的输出是为:
![f8853329477a00aca937a5cd910bb293.png](https://i-blog.csdnimg.cn/blog_migrate/25a23ee894320dd6f0d7205a25cbac08.png)
select id, movie, description, rating
from cinema
where id%2 = 1 and description != 'boring'
order by rating desc;
627.交换工资
给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。
例如:
![03290f920a5b578a327ea9a374374d64.png](https://i-blog.csdnimg.cn/blog_migrate/ad26a53ab37d33bffe70de83474cab10.png)
运行你所编写的更新语句之后,将会得到以下表:
![42532b3f2f8349bb07ea248767a241f3.png](https://i-blog.csdnimg.cn/blog_migrate/60c90bbf37d6e339f9f5b92f44c22a7d.png)
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
1179.重新格式化部门表
部门表 Department
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
查询结果格式如下面的示例所示:
Department 表:
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
查询得到的结果表:
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
SELECT id,
SUM(CASE month WHEN 'Jan' THEN revenue ELSE null END) AS 'Jan_Revenue',
SUM(CASE month WHEN 'Feb' THEN revenue ELSE null END) AS 'Feb_Revenue',
SUM(CASE month WHEN 'Mar' THEN revenue ELSE null END) AS 'Mar_Revenue',
SUM(CASE month WHEN 'Apr' THEN revenue ELSE null END) AS 'Apr_Revenue',
SUM(CASE month WHEN 'May' THEN revenue ELSE null END) AS 'May_Revenue',
SUM(CASE month WHEN 'Jun' THEN revenue ELSE null END) AS 'Jun_Revenue',
SUM(CASE month WHEN 'Jul' THEN revenue ELSE null END) AS 'Jul_Revenue',
SUM(CASE month WHEN 'Aug' THEN revenue ELSE null END) AS 'Aug_Revenue',
SUM(CASE month WHEN 'Sep' THEN revenue ELSE null END) AS 'Sep_Revenue',
SUM(CASE month WHEN 'Oct' THEN revenue ELSE null END) AS 'Oct_Revenue',
SUM(CASE month WHEN 'Nov' THEN revenue ELSE null END) AS 'Nov_Revenue',
SUM(CASE month WHEN 'Dec' THEN revenue ELSE null END) AS 'Dec_Revenue'
FROM Department
GROUP BY id;