SQL练习题(7)-牛客网

牛客网在线编程网址：https://www.nowcoder.com/activity/oj

(默认使用SQLite)

 

题目1：

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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`));

用group_concat函数

select dept_no, group_concat(emp_no, ',') employees 
from dept_emp
group by dept_no
order by dept_no;

 

题目2：

查找排除当前最大、最小salary之后的员工的平均工资avg_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 avg(salary) as avg_salary from salaries
where to_date = '9999-01-01'
AND salary <> (select max(salary) from salaries)
AND salary <> (select min(salary) from salaries);

实际子查询应该也要考虑最大最小salary是否是当前的，不过这样就通不过了:

select avg(salary) as avg_salary from salaries
where to_date = '9999-01-01'
AND salary <> (select max(salary) from salaries where to_date = '9999-01-01')
AND salary <> (select min(salary) from salaries where to_date = '9999-01-01');

 

题目3：

分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5,5;
/*select * from employees limit 5 offset 5;*/

 

题目4：

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示

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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

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`));

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`));

create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

用left join，没有分配到bonus的员工就不会显示后两个字段的信息了:

select dept_emp.emp_no, dept_emp.dept_no, EB.btype, EB.recevied
FROM dept_emp LEFT JOIN emp_bonus EB
ON dept_emp.emp_no = EB.emp_no;

 

题目5：

使用含有关键字exists查找未分配具体部门的员工的所有信息。

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`));

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`));

如下

select * from employees
where not exists (select emp_no from dept_emp where emp_no = employees.emp_no);

https://www.techonthenet.com/sqlite/exists.php

Note

• SQL statements that use the EXISTS Condition in SQLite are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition.

之所以是这个结果，是因为每次外表都是读入一行去执行子查询是否满足的条件。但这种方法也因此比较没有效率，可以通过其他查询来实现目标功能。

 

题目6：

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;

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`));

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

select * from employees 
where emp_no in (select emp_no from emp_v);

select employees.* from employees, emp_v
where employees.emp_no = emp_v.emp_no;
/*或者再添加AND employees.birth_date = emp_v.birth_date等*/

 

题目7：

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`));

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 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`));

获取有奖金的员工相关信息。

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'。

select emp.emp_no, emp.first_name, emp.last_name, emp_bonus.btype, sal.salary, 
(case emp_bonus.btype
when 1 then sal.salary*0.1
when 2 then sal.salary*0.2
else sal.salary*0.3
end) as bonus
from employees emp, emp_bonus, salaries sal
where emp.emp_no = emp_bonus.emp_no
and emp.emp_no = sal.emp_no
and sal.to_date = '9999-01-01';

 

题目8：

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

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`));

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545

select s1.emp_no, s1.salary, sum(s2.salary) running_total
from salaries s1 INNER JOIN salaries s2 
ON s1.emp_no >= s2.emp_no
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
order by s1.emp_no;

或

select s1.emp_no, s1.salary, 
(select sum(s2.salary) from salaries s2 where s2.emp_no <= s1.emp_no and s2.to_date = '9999-01-01') as running_total
from salaries s1
where s1.to_date = '9999-01-01'
order by s1.emp_no;

 

题目9：

对于employees表中，给出奇数行的first_name

 (隐含的意思应该是按first_name排序后的奇数行，但输出结果不用做排序)

select first_name from
(
    select first_name, (select count(*) from employees e2 where e2.first_name <= e1.first_name) as rank 
    from employees e1
) as temp
where temp.rank%2 <> 0;  /*rank整除2取余不为零 奇数*/

更简练的:

select first_name 
from employees s1
where (select count(*) from employees s2 where s2.first_name <= s1.first_name)%2 <> 0;