使用HR数据库进行练习之过滤和排序数据练习
练习用到的HR数据库，之前的文章有说到，大家参考 https://blog4jimmy.com/2017/12/268.html 这一篇blog就好了
Practice 1
编写一条SQL查询语句，显示所有salary不在$10000到$15000区间的employee的名字（first_name，last_name）以及salary
可以使用逻辑运算的过滤语句和NOT BETWEEN语句
下面是使用NOT BETWEEN语句进行过滤
|
1 2 |
SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000; |
下面是使用逻辑运算进行过滤
|
1 2 |
SELECT first_name, salary FROM employees WHERE (salary > 15000 OR salary < 10000); |
Practice 2
编写一条SQL查询语句，显示所有departments为30或者100的employee的名字（first_name，last_name）和department ID
|
1 2 |
SELECT first_name, last_name, department_id FROM employees WHERE department_id = 30 OR department_id = 100; |
另一种使用IN的方法：
|
1 2 |
SELECT first_name, last_name, department_id FROM employees WHERE department_id IN (30, 100); |
Practice 3
编写一条SQL查询语句，查询所有salary不在$10000和$15000范围内部内，部门为30或者100的employee的名字（first_name，last_name）和salary
|
1 2 |
SELECT first_name, last_name, salary, department_id FROM employees WHERE (salary NOT BETWEEN 10000 AND 15000) AND (department_id IN (30, 100)); |
Practice 4
编写一条SQL查询语句，查询所有在1987年雇用的employee
|
1 2 |
SELECT first_name, last_name, hire_date FROM employees WHERE YEAR(hire_date) = 1987; |
Practice 5
编写一条SQL查询语句，显示所有first_name中同时带有字母’b’和’c’的employee
|
1 2 |
SELECT first_name FROM employees WHERE first_name LIKE '%b%' AND first_name LIKE '%c%'; |
Practice 6
编写一条SQL查询语句，显示所有salary不等于4500、10000、15000，job为programmer或者shipping clerk的employee的last name、job和salary
|
1 2 |
SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('IT_PROG', 'SH_CLERK') AND salary NOT IN (4500, 10000, 15000); |
Practice 7
编写一条SQL查询语句，显示所有last_name有六个字符的employee的last_name
|
1 |
SELECT last_name FROM employees WHERE LENGTH(last_name) = 6; |
Practice 8
编写一条SQL查询语句，显示所有employee last_name中第三个字符为’e’的last_name列
|
1 |
SELECT last_name FROM employees WHERE last_name LIKE '__c%'; |
Practice 9
编写一条SQL查询语句，显示所有employees表中可用的jobs/designations
|
1 |
SELECT DISTINCT job_id FROM employees; |
Practice 10
编写一条SQL查询语句，显示last_name为‘KING’、‘SCOTT’、‘BLAKE’或‘FORD’的所有记录
|
1 |
SELECT * FROM employees WHERE last_name IN ('BLAKE', 'SCOTT', 'KING', 'FORD'); |
