Jimmy Chen

A Programmer

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

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

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

Practice 1

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

可以使用逻辑运算的过滤语句和NOT BETWEEN语句

下面是使用NOT BETWEEN语句进行过滤

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;

另一种使用IN的方法:

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');

发表评论

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

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

%d 博主赞过: