使用HR数据库进行练习之字符串练习
Practice 1
编写一条SQL查询语句，获取job_id和对用的employee的id，输出如下图类似：
|
1 2 |
SELECT job_id, GROUP_CONCAT(employee_id, ' ') 'Employees ID' FROM employees GROUP BY job_id; |
Practice 2
编写一条SQL查询语句，更新employees表中的电话号码，将电话号码中包含‘124’的子串替换为‘999’
这里只显示更新后的值，就不适用UPDATE来修改表的数据了
|
1 2 |
SELECT phone_number, REPLACE(phone_number, '124', '999') replace_phone_number FROM employees WHERE phone_number LIKE '%124%'; |
Practice 3
编写一条SQL查询语句，显示first name的长度大于等于8个字符的employee的详细信息
|
1 2 |
SELECT * FROM employees WHERE LENGTH(first_name) >= 8; |
Practice 4
编写一条SQL查询语句，添加@example.com到email字段后面
|
1 |
SELECT email, CONCAT(email, '@example.com') append_email FROM employees; |
Practice 5
编写一条SQL查询语句，获取employee id，first name和hire month
|
1 |
SELECT employee_id, first_name, MONTH(hire_date) 'hire month' FROM employees; |
Practice 6
编写一条SQL查询语句，查找所有first name是大写的employee
|
1 |
SELECT * FROM employees WHERE first_name = BINARY UPPER(first_name); |
Practice 7
编写一条SQL查询语句，显示电话号码的后四个字节
|
1 |
SELECT substring(phone_number, LENGTH(phone_number)-3, 4) FROM employees; |
或者
|
1 |
SELECT RIGHT(phone_number, 4) 'Ph.no' FROM employees; |
Practice 8
编写一条SQL查询语句，显示街道地址的最后一个单词
|
1 2 |
SELECT location_id, street_address, SUBSTRING_INDEX(street_address, ' ', -1) last_word FROM locations; |
Practice 9
编写一条SQL查询语句，显示名字最短的street的信息
|
1 2 3 |
SELECT * FROM locations WHERE LENGTH(street_address) = (SELECT MIN(LENGTH(street_address)) FROM locations); |
Practice 10
编写一条SQL查询语句，显示包含多个单词的job title的第一个单词
|
1 2 |
SELECT job_title, SUBSTRING_INDEX(job_title, ' ', 1) FROM jobs WHERE POSITION(' ' IN job_title) != 0; |
Practice 11
编写一条SQL查询语句，显示在last name的第二个位置后包含字符串‘c’的first name的长度
|
1 2 |
SELECT first_name, LENGTH(first_name), last_name FROM employees WHERE last_name LIKE '_%c%'; |
Practice 12
编写一条SQL查询语句，显示first name以字母‘A’，‘J’或‘M’的employee的first name和first name的长度，并给每一列一个适当的标签，按照employee的first name排列
|
1 2 |
SELECT first_name fname, LENGTH(first_name) name_length FROM employees WHERE first_name LIKE 'J%' OR first_name LIKE 'A%' OR first_name LIKE 'M%'; |
Practice 13
编写一条SQL查询语句，显示所有employee的first name和salary。将salary格式化为10字节长，用$符填充，标签列为SALARY
|
1 2 |
SELECT first_name, LPAD(salary, 10, '$') SALARY FROM employees; |
Practice 14
编写一条SQL查询语句，显示雇员的名字的前八个字符，并用“$”符号表示他们的薪水金额。 每个’$’标志表示一千美元。 按照工资的降序对数据进行排序。
|
1 2 |
SELECT first_name, LEFT(first_name, 8), REPEAT('$', FLOOR(salary/1000)) 'SALARY($)' FROM employees ORDER BY salary DESC; |
Practice 15
编写一条SQL查询语句，显示雇员的代码，first name，last name和雇用日期谁雇用任何一个月的第七天或任何一年的第七个月。
|
1 2 |
SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE DAY(hire_date) = 7 OR MONTH(hire_date) = 7; |
