数据库是进行数据分析的“源泉”,对冗杂的数据进行整理和编排是数据分析的首要任务。同时将来职业生活中,对数据库系统进行DB操作也会是重要任务之一,因此掌握数据库语言刷题刷起来,至少对语言运用和数据库管理有所了解是题中之义。
本篇为牛客题霸的SQL训练题目练习记录(四)。
研究内容 | 语言 | 日期 |
---|---|---|
数据库 | sql | 2021年1月29日 |
1. 更新表
题目描述:
请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
create table emp_bonus(
emp_no int 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`));
#如:
INSERT INTO emp_bonus VALUES (10001,1);
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
个人解答:
update salaries
set salary=salary*1.1
where salaries.emp_no in (select eb.emp_no from emp_bonus as eb)
and salaries.to_date='9999-01-01'
注意事项:empno要是在获奖里的才行,是隐藏条件
2. 连接函数
题目描述:
将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat,请用||实现,mysql支持concat)
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 concat(last_name,"'",first_name) as name
from employees
中间的引号’ 需要用双引号“” 套起来
3. 修改表名
题目描述:
将titles_test表名修改为titles_2017。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
个人解答:
alter table titles_test RENAME TO titles_2017
4. 修改表
题目描述:
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错。
CREATE TABLE titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
个人解答:
UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;
牛客给出第二种题解:根据原本记录直接replace一条
REPLACE INTO titles_test
VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
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 employees.emp_no = dept_emp.emp_no);
牛客解答2:
#不用exists的方法:
select * from employees e
left join dept_emp d on d.emp_no = e.emp_no
where d.emp_no is null;
6.表连接与case
题目描述:
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’。
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,
received 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`));
牛客解答:
select e.emp_no
,e.first_name
,e.last_name
,b.btype
,s.salary
,case when b.btype=1 then s.salary*0.1
when b.btype=2 then s.salary*0.2
else s.salary*0.3
end as bonus
from employees e
join salaries s on e.emp_no=s.emp_no
join emp_bonus b on e.emp_no=b.emp_no
where s.to_date='9999-01-01';
本题有难度在于一个嵌套的case