SQL 刷题
2019-06-02 本文已影响0人
haidaozheng
https://www.nowcoder.com/ta/sql
https://leetcode-cn.com/problemset/database/
题目描述
查找最晚入职员工的所有信息
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
解答：重点是最后入职日期可能包含了多个员工
select *
from employees
where hire_date = (select max(hire_date) from employees)
题目描述
查找入职员工时间排名倒数第三的员工所有信息
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
解答：重点，倒数第三的日期要注意 去重 再排序
select *
from employees
where hire_date = (select distinct hire_date from employees order by hire_date desc limit 2,1)
题目描述
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
解答：使用rank(相同值相同排名）
select dept_no,emp_no,salary
from (
select a.dept_no,a.emp_no,b.salary,rank() over(partition by a.dept_no order by b.salary) as num
from dept_emp a
inner join salaries b on a.emp_no = b.emp_no
where a.to_date = '9999-01-01' and b.to_date = '9999-01-01')
where num = 1
题目描述
获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
解题思路：薪水第二多
select b.emp_no,b.salary
from salaries b
where to_date = '9999-01-01' and b.salary = (
select distinct a.salary
from salaries a
where a.to_date = '9999-01-01'
order by salary desc
limit 1,1)
题目描述
将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
解题思路：不要把问题想复杂：
UPDATE salaries
SET salary = salary * 1.1
WHERE emp_no IN
(SELECT s.emp_no
FROM salaries AS s
INNER JOIN emp_bonus AS eb ON s.emp_no = eb.emp_no)
题目描述
查找字符串'10,A,B' 中逗号','出现的次数cnt。
select char_length('A,10,B')- char_length(replace('10,A,B',',',''));
题目描述
获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
问题
编写一个 SQL 查询，找出每个部门工资前三高的员工。
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
Department 表包含公司所有部门的信息。
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '85000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Janet', '69000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('6', 'Randy', '85000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('7', 'Will', '70000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')
解题：
/* Write your T-SQL query statement below */
select Department,Employee,Salary
from (
select b.Name as Department,a.Name as Employee,a.Salary
,dense_rank() over(partition by b.Name order by a.Salary desc) as rk
from Employee a
inner join Department b on a.DepartmentId = b.ID
) as c
where rk<=3
题目
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
解题：
select a.request_at as 'Day'
,convert(decimal(3,2),1.0*sum(case
when a.status in ('cancelled_by_client','cancelled_by_driver') then 1 else 0 end)/count(a.id)) as 'Cancellation Rate'
from Trips a
where a.request_at between '2013-10-01' and '2013-10-03'
group by a.request_at
Sql 取众数、中位数、标准差、平均数
create table examines
(
[e_id] [int] IDENTITY(1,1) NOT NULL,
[dept_name] [nvarchar](200) NULL,
[ph_score] [int] NULL
)
SELECT dept_name,AVG(sp) as '中位数'
FROM
(
SELECT dept_name,
cast(ph_score as decimal(5,2)) sp,
ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY ph_score ASC, e_id ASC) AS RowAsc,
ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY ph_score DESC, e_id DESC) AS RowDesc
FROM examines SOH
) x
WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY dept_name
ORDER BY dept_name;
select dept_name,STDEV(ph_score) as '标准差' from examines group by dept_name
select dept_name,avg(ph_score) as '平均数' from examines group by dept_name
