SQL语句常见面试题,MySQL常见面试语句整理 |
您所在的位置:网站首页 › sql语法面试题 › SQL语句常见面试题,MySQL常见面试语句整理 |
1. 第二高的薪水 难度简单844收藏分享切换为英文接收动态反馈 SQL架构 编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。 +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。 +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+答案:select (select distinct salary from Employee order by salary desc limit 1,1) as SecondHighestSalary 2. 超过经理收入的员工 难度简单396收藏分享切换为英文接收动态反馈 SQL架构 Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。 +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。 +----------+ | Employee | +----------+ | Joe | +----------+答案:select Name as 'Employee' from Employee e where Salary > ( select Salary from Employee where id = e.ManagerId ) 3. 查找重复的电子邮箱 难度简单292收藏分享切换为英文接收动态反馈 SQL架构 编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。 示例: +----+---------+ | Id | Email | +----+---------+ | 1 | [email protected] | | 2 | [email protected] | | 3 | [email protected] | +----+---------+根据以上输入,你的查询应返回以下结果: +---------+ | Email | +---------+ | [email protected] | +---------+说明:所有电子邮箱都是小写字母。 答案:select email from person group by email having count(email)>1 4. 组合两个表 难度简单895收藏分享切换为英文接收动态反馈 SQL架构 表1: Person +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId 是上表主键表2: Address +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId 是上表主键编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息: FirstName, LastName, City, State答案:select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId 5. 两个表 班级表class (cid,sid) 分数表source(sid,source) 1,查询出每个班级的学生人数: select count(sid) from class group by cid; 2,查询出1001班级下,成绩大于80的人数 select count(s.sid) from class c,source s where c.sid=s.sid and c.cid = 1001 and s.source>80; 6. 部门工资最高的员工 难度中等402收藏分享切换为英文接收动态反馈 SQL架构 Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+Department 表包含公司所有部门的信息。 +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。 +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+解释: Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。 答案: 方法:使用 JOIN 和 IN 语句 算法 因为 Employee 表包含 Salary 和 DepartmentId 字段,我们可以以此在部门内查询最高工资。 SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId; 注意:有可能有多个员工同时拥有最高工资,所以最好在这个查询中不包含雇员名字的信息。 | DepartmentId | MAX(Salary) | |--------------|-------------| | 1 | 90000 | | 2 | 80000 | 然后,我们可以把表 Employee 和 Department 连接,再在这张临时表里用 IN 语句查询部门名字和工资的关系。 MySQL SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee JOIN Department ON Employee.DepartmentId = Department.Id WHERE (Employee.DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId ) ; |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |