初识MySQL作业

1. 找出下面语句中的错误

select employee_id , last_name , salary* 12 as “ANNUAL SALARY”from employees;

2. 显示出表employees中的全部job_id（不能重复）

SELECT

​ DISTINCT job_id

FROM

​ employees;

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

SELECT

​ CONCAT(last_name,first_name) AS 姓名,

​ employee_id AS id

FROM

​ employees

WHERE

​ manager_id IS NULL;

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

SELECT

​ CONCAT(last_name,first_name) AS 姓名,

​ salary ,

​ commission_pct

FROM

​ employees

WHERE

​ commission_pct IS NULL;

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

SELECT

​ CONCAT(last_name,first_name) AS 姓名

FROM

​ employees

WHERE

​ last_name LIKE “__a%”;

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

SELECT

​ *

FROM

​ employees

WHERE

​ last_name LIKE “%e”;

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

SELECT

​ CONCAT(last_name,first_name) AS 姓名,

​ job_id AS 职位

FROM

​ employees

WHERE

​ manager_id IN (100,101,110);

8. 面试题：

Select * from employees;和

​ Selcet * from employees where commission_pct like ‘%%’and last_name like ‘%%’;结果是否一样？并说明原因；

答：不一样，若commission_pct为NULL结果就不一样，当与运算有一个为假整个表达式就为假；若改为OR在此表中结果就会一样。