SQL刷题第九天 牛客

题一：在audit表上创建外键约束，其emp_no对应employees_test表的主键id

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

(以下2个表已经创建了)

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

后台会判断是否创建外键约束，创建输出1，没创建输出0

创建外键语句结构：

ALTER TABLE <表名>

ADD CONSTRAINT FOREIGN KEY (<列名>)

REFERENCES <关联表>（关联列）

alter table audit
add constraint foreign key (emp_no)
references employees_test(id);

题二：将所有获取奖金的员工当前的薪水增加10%

现有员工获取到的奖金简表emp_bonus如下：

• emp_no指获取到奖金的员工编号；
• bytpe指获取到的奖金类型。

emp_no	btype
10001	1

有员工薪资简表salaries如下：

• emp_no指员工编号；
• salary指薪资；
• from_date指该薪资的开始日期；
• to_date指该薪资的结束日期。

emp_no	salary	from_date	to_date
10001 10001	85097.0 88958.0	2001-06-22 2002-06-22	2002-06-22 9999-01-01

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工，不考虑获取的奖金的类型)。

以上示例更新后的结果salaries为:

emp_no	salary	from_date	to_date
10001 10001	85097.0 97853.8	2001-06-22 2002-06-22	2002-06-22 9999-01-01

注：只需写出更新语句，后台会执行以下SQL语句，将更新后的结果与预期结果对比:

select * from salaries;

更新数据标准格式如下:

update 表名
set 
    字段1 = 值1,
    字段2 = 值2,
    ...
where 筛选条件

update salaries as s join emp_bonus as e 
    on s.emp_no=e.emp_no
    set salary=salary*1.1
    where to_date='9999-01-01'

题三： 将employees表中的所有员工的last_name和first_name通过(\')连接起来

将employees表中的所有员工的last_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`));
输出格式:

name
Facello'Georgi
Simmel'Bezalel
Bamford'Parto
Koblick'Chirstian
Maliniak'Kyoichi
Preusig'Anneke
Zielinski'Tzvetan
Kalloufi'Saniya
Peac'Sumant
Piveteau'Duangkaew
Sluis'Mary

 补充：

CONCAT（）拼接
RTrim():去除字符串右端空格
LTrim():去除字符串左端空格
Trim():去除字符串两端的空格

SELECT concat(RTrim(last_name),"'",LTrim(first_name)) as name
from employees

题四：查找字符串中逗号出现的次数

现有strings表如下：

• id指序列号；
• string列中存放的是字符串，且字符串中仅包含数字、字母和逗号类型的字符。

id	string
1 2 3	10,A,B,C,D A,B,C,D,E,F A,11,B,C,D,E,G

请你统计每个字符串中逗号出现的次数cnt。

以上例子的输出结果如下：

id	cnt
1 2 3	4 5 6

 方法：

1. 使用length()函数获取字符串长度；用replace()函数将","替换成" "(英文逗号 替换成 空格)；
2. 逆向思维，将原字符串长度 - 替换逗号后的字符串长度（空格不算字符串）=逗号的个数；
3. 记得列的别名

SELECT id, LENGTH(string) - LENGTH(REPLACE(string, ",", "")) AS cnt
FROM strings;

题五：获取employees中的first_name

现有employees表如下：

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12
10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15
10009	1952-04-19	Sumant	Peac	F	1985-02-18
10010	1963-06-01	Duangkaew	Piveteau	F	1989-08-24
10011	1953-11-07	Mary	Sluis	F	1990-01-22

请你将employees中的first_name，并按照first_name最后两个字母升序进行输出。

以上示例数据的输出如下：

first_name

Christian

Tzvetan

Bezalel

Duangkaew

Georgi

Kyoichi

Anneke

Sumant

Mary

Parto

Saniya

方法：

1. LEFT(s,n)返回字符串 s 的前 n 个字符
2. RIGHT(s,n)返回字符串 s 的后 n 个字符

select first_name
from employees
order by right(first_name,2)