MySQL你掌握了多少?这些SQL题你能作对几道?

bcaed392699f406ca59236e6dfa9b745.gif

MySQL是工作中常用数据库,必须掌握,但小伙伴们又掌握了多少呢,今天一起来测试一下吧~


力扣SQL

⭐组合两个表

⭐第二高的薪水

⭐超过经理收入的员工

⭐查找重复的电子邮箱

⭐从不订购的客户

⭐大的国家

⭐删除重复的电子邮箱

⭐有趣的电影


9359109aaae644a58505456ba2dfc295.gif


⭐组合两个表

表1: Person

+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId 是Person表主键

表2: Address

+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId 是Address表主键 PersonId 是表 Person 的外键

题目:

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于表1和表2两表提供 person 的以下信息:

FirstName, LastName, City, State


有思路了嘛,先别着急往下看,先自己想想思路解决它~

这里我们使用的是左外连接

代码实现

select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId;

恭喜第一题通过我们接着做第二道题!


⭐第二高的薪水

Employee

Employee 表 +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

题目

  • 📃编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary),如果不存在第二高的薪水,那么查询应返回 null
  • 例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+

是不是也难不倒你呢~ 加油做出来他!

代码实现

方法1

select ifnull(( select max(salary) from Employee where salary < (select max(salary) from Employee)),null) as SecondHighestSalary;

方法2

select ifnull( (select distinct Salary from Employee order by Salary desc limit 1,1), null) as SecondHighestSalary;


⭐超过经理收入的员工

Employee

+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+

题目

Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。查询出的结果如下所示。

+----------+ | Employee | +----------+ | Joe | +----------+

是不是也难不倒你呢~ 加油做出来他!

代码实现

SELECT staff.Name AS 'Employee' FROM Employee AS staff, Employee AS manager WHERE staff.ManagerId = manager.Id AND staff.Salary > manager.Salary

⭐查找重复的电子邮箱

Person表

+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+

题目:

编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。

根据以上输入,你的查询应返回以下结果:

+---------+ | Email | +---------+ | a@b.com | +---------+

代码实现

方法一

select Email from (select Email, count(Email) as num from Person group by Email) as temporary where num > 1;

方法二

select Email from Person group by Email having count(Email) > 1; 

⭐从不订购的客户

Customers 表:

+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+

Orders 表:

+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+

题目:

某网站包含两个表CustomersOrders。编写一个 SQL 查询,找出所有从不订购任何东西的客户

例如给定上述表格,你的查询应返回:

+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+

代码实现

方法一

select customers.name as 'Customers' from customers where customers.id not in (select customerid from orders);

方法二

select c.name as Customers from Customers c left join Orders o on c.id = o.CustomerId where o.id is null;

⭐大的国家

World 表:

+-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | int | +-------------+---------+
World 表: +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+-----------+---------+------------+--------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000000 | | Albania | Europe | 28748 | 2831741 | 12960000000 | | Algeria | Africa | 2381741 | 37100000 | 188681000000 | | Andorra | Europe | 468 | 78115 | 3712000000 | | Angola | Africa | 1246700 | 20609294 | 100990000000 | +-------------+-----------+---------+------------+--------------+

name 是这张表的主键
这张表的每一行提供:国家名称、所属大陆、面积、人口和 GDP 值。


如果一个国家满足下述两个条件之一,则认为该国是 大国 :

面积至少为 300 平方公里(即,3000000 km2),或者
人口至少为 2500 万(即 25000000)

编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。按 任意顺序 返回结果表。

查询结果格式如下例所示。

输出: +-------------+------------+---------+ | name | population | area | +-------------+------------+---------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +-------------+------------+---------+

代码实现

方法一

SELECT name, population, area FROM world WHERE area >= 3000000 OR population >= 25000000;

方法二

SELECT name, population, area FROM world WHERE area > 3000000 UNION SELECT name, population, area FROM world WHERE population > 25000000;

⭐删除重复的电子邮箱

Person

+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+

Id 是这个表的主键。

题目:

编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。

代码实现

DELETE pson1 FROM Person pson1,Person pson2 WHERE pson1.Email = pson2.Email AND pson1.Id > pson2.Id 

⭐有趣的电影

cinema:

+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | +---------+-----------+--------------+-----------+

题目:

某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。

作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 降序排列。

代码实现

select * from cinema where mod(id, 2) = 1 and description != 'boring' order by rating DESC;

本网页由快兔兔AI采集器生成,目的为演示采集效果,若侵权请及时联系删除。

原文链接:https://blog.csdn.net/weixin_42306958/article/details/123193369

更多内容