数据库实训练习题1

1.过滤数据

1. 查询工资大于 12000 的员工姓名和工资

2. 查询员工号为 176 的员工的姓名和部门号和年薪

3. 选择工资不在 5000 到 12000 的员工的姓名和工资

4. 选择在 20 或 50 号部门工作的员工姓名和部门号

5. 选择公司中没有管理者的员工姓名及 job_id

6. 选择公司中有奖金的员工姓名，工资和奖金级别

7. 选择员工姓名的第三个字母是 a 的员工姓名

8. 选择姓名中有字母 a 和 e 的员工姓名

9. 显示出表 employees 表中 first_name 以 'e' 结尾的员工信息

10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位

11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位

SELECT `last_name`,`salary` FROM `employees` WHERE `salary`>12000;
SELECT `last_name`,`department_id`,`salary`*12*(1+IFNULL(`commission_pct`,0)) FROM `employees` WHERE`employee_id`=176;
SELECT `last_name`,`salary` FROM `employees` WHERE `salary`  NOT BETWEEN 5000 AND 12000;
SELECT `last_name`,`department_id` FROM `employees` WHERE `department_id`=20 OR `department_id`=50;
SELECT `last_name`,`job_id` FROM `employees` WHERE `manager_id` IS NULL;
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE `commission_pct` IS NOT NULL;
SELECT DISTINCT `last_name` FROM `employees` WHERE `last_name` LIKE '__a%';
SELECT `last_name` FROM `employees` WHERE `last_name` LIKE '%a%e%' OR `last_name` LIKE '%e%a%';
SELECT * FROM `employees` WHERE `first_name` LIKE '%e';
SELECT `last_name`,`job_id` FROM `employees` WHERE `department_id` BETWEEN 80 AND 100;
SELECT `last_name`,`job_id`  FROM `employees` WHERE `manager_id` IN (100,101,110);

2.排序数据

1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT `last_name`,`department_id`,`salary`*12 annual_sal FROM `employees` ORDER BY annual_sal DESC,`last_name` ASC;
SELECT `last_name`,`salary` FROM `employees` WHERE `salary` NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
SELECT * FROM `employees` WHERE `email` LIKE '%e%' ORDER BY LENGTH (email) DESC,`department_id` ASC;