A Programmer

# 使用HR数据库进行练习之过滤和排序数据练习

练习用到的HR数据库，之前的文章有说到，大家参考 http://blog4jimmy.com/2017/12/268.html 这一篇blog就好了

## Practice 1

编写一条SQL查询语句，显示所有salary不在\$10000到\$15000区间的employee的名字（first_name，last_name）以及salary

```SELECT first_name, last_name, salary FROM employees
WHERE salary NOT BETWEEN 10000 AND 15000;
```

```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

```SELECT first_name, last_name, department_id FROM employees
WHERE department_id = 30 OR department_id = 100;
```

```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

```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

```SELECT first_name, last_name, hire_date FROM employees
WHERE YEAR(hire_date) = 1987;
```

## Practice 5

编写一条SQL查询语句，显示所有first_name中同时带有字母'b'和'c'的employee

```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

```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

```SELECT last_name FROM employees WHERE LENGTH(last_name) = 6;
```

## Practice 8

编写一条SQL查询语句，显示所有employee last_name中第三个字符为'e'的last_name列

```SELECT last_name FROM employees WHERE last_name LIKE '__c%';
```

## Practice 9

编写一条SQL查询语句，显示所有employees表中可用的jobs/designations

```SELECT DISTINCT job_id FROM employees;
```

## Practice 10

编写一条SQL查询语句，显示last_name为‘KING’、‘SCOTT’、‘BLAKE’或‘FORD’的所有记录

```SELECT * FROM employees WHERE last_name IN ('BLAKE', 'SCOTT', 'KING', 'FORD');
```