Jimmy Chen

A Programmer

使用HR数据库进行练习之字符串练习

使用HR数据库进行练习之字符串练习

Practice 1

  编写一条SQL查询语句,获取job_id和对用的employee的id,输出如下图类似:

《使用HR数据库进行练习之字符串练习》

SELECT job_id, GROUP_CONCAT(employee_id, ' ')  'Employees ID' 
    FROM employees GROUP BY job_id;

Practice 2

  编写一条SQL查询语句,更新employees表中的电话号码,将电话号码中包含‘124’的子串替换为‘999’

这里只显示更新后的值,就不适用UPDATE来修改表的数据了

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的详细信息

SELECT * FROM employees
    WHERE LENGTH(first_name) >= 8;

Practice 4

  编写一条SQL查询语句,添加@example.com到email字段后面

SELECT email, CONCAT(email, '@example.com') append_email FROM employees;

Practice 5

  编写一条SQL查询语句,获取employee id,first name和hire month

SELECT employee_id, first_name, MONTH(hire_date) 'hire month' FROM employees;

Practice 6

  编写一条SQL查询语句,查找所有first name是大写的employee

SELECT * FROM employees WHERE first_name = BINARY UPPER(first_name);

Practice 7

  编写一条SQL查询语句,显示电话号码的后四个字节

SELECT substring(phone_number, LENGTH(phone_number)-3, 4) FROM employees;

或者

SELECT RIGHT(phone_number, 4) 'Ph.no' FROM employees;

Practice 8

  编写一条SQL查询语句,显示街道地址的最后一个单词

SELECT location_id, street_address, SUBSTRING_INDEX(street_address, ' ', -1) last_word
    FROM locations;

Practice 9

  编写一条SQL查询语句,显示名字最短的street的信息

SELECT * FROM locations
    WHERE LENGTH(street_address) = (SELECT MIN(LENGTH(street_address))
        FROM locations);

Practice 10

  编写一条SQL查询语句,显示包含多个单词的job title的第一个单词

SELECT job_title, SUBSTRING_INDEX(job_title, ' ', 1) FROM jobs
    WHERE POSITION(' ' IN job_title) != 0;

Practice 11

  编写一条SQL查询语句,显示在last name的第二个位置后包含字符串‘c’的first name的长度

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排列

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

SELECT first_name, LPAD(salary, 10, '$') SALARY
    FROM employees;

Practice 14

  编写一条SQL查询语句,显示雇员的名字的前八个字符,并用“$”符号表示他们的薪水金额。 每个’$’标志表示一千美元。 按照工资的降序对数据进行排序。

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和雇用日期谁雇用任何一个月的第七天或任何一年的第七个月。

SELECT employee_id, first_name, last_name, hire_date FROM employees
    WHERE DAY(hire_date) = 7 OR MONTH(hire_date) = 7;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d 博主赞过: