条件查询练习题

一、根据条件表达式查询

# 案例1：查询月薪>5000的员工信息

select * from employees where salary > 5000

# 案例2：查询月薪=12000的员工信息

select * from employees where  salary = 12000

# 案例3：查询月薪不等于12000的员工信息

select * from employees where salary <> 12000

# 案例4：查询没有奖金的员工名

select first_name,commission_pct from employees where commission_pct <=>  NULL

# 案例5：查询有奖金的员工名

select first_name,commission_pct from employees where commission_pct > 0

二、按逻辑表达式进行查询

# 案例1：查询月薪在5000到12000的员工工资和姓名

# 方式一

select salary,last_name,first_name from employees where salary >= 5000 and salary <= 12000

# 方式二

select salary,last_name,first_name from employees where salary between 5000 and 12000

# 案例2：查询月薪不在5000到12000的员工工资和姓名

select salary,last_name,first_name from employees where not (salary between 5000 and 12000)

# 案例3：查询部门编号=90 或 月薪>10000并且月薪<15000的员工信息

# 错误。

select * from employees where department_id = 90 or (salary between 10000 and 15000)

# 原因：between 10000 and 15000 表示>=10000且<=15000

# 正确如下：

select * from employees where department_id = 90 or (salary > 10000 and salary <15000)

# 生成30以内的随机数

select ceil(RAND()*30)