秋招-SQL备战练习3

续接前两篇博客秋招-SQL备战练习1
秋招-SQL备战练习2

用到的数据库表如下：
employees_test

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
1
2
3
4
5
6
7

audit

CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);
1
2
3
4

titles_test

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);
1
2
3
4
5
6

员工奖金表emp_bonus

create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
1
2
3
4

薪水表salaries

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`));
1
2
3
4
5

1. 构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

create trigger audit_log after insert on employees_test
    begin 
    insert into audit(EMP_no,NAME) values(new.ID,new.NAME);
    end;
1
2
3
4

2. titles_test中，删除emp_no重复的记录，只保留最小的id对应的记录。

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');
1
2
3
4
5
6
7

delete from titles_test where id not in 
(select min(id) from titles_test group by emp_no);
1
2

3. titles_test 中将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

update titles_test set emp_no=replace(emp_no,10001,10005)
where id=5;
1
2

-- 方法1
-- select em.* from employees em,emp_v ev where em.emp_no=ev.emp_no;

-- 方法2
select * from employees intersect select * from emp_v;
1
2
3
4
5

5. 将所有获取奖金的员工当前的薪水增加10%。

update salaries
set salary=salary*1.1
where emp_no in (select emp_no from emp_bonus)
    and to_date='9999-01-01';
1
2
3
4

6. 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

select (length('10,A,B')-length(replace('10,A,B',',','')))/length(',') 
as cnt;
1
2

---