尚硅谷数据库练习题
案例1:查询工资>10000的员工信息
案例2:查询 job_id不是’IT_PROG’的员工姓名、job_id、salary
案例3:查询工资>10000并且小于20000的员工姓名、工资、年薪
案例4:查询部门编号不在90到120之间的并且 工资>15000的员工信息
select * from employees where salary>10000;
select first_name,last_name,job_id,salary from employees where job_id not like 'IT_PROG';
select first_name,salary,salary*12 as 年薪 from employees where salary between 10000 and 20000 ;
select employee_id from employees where department_id not between 90 and 120 and salary >10000;
#案例1:查询员工信息表中姓名第一个字符为e的员工信息
#案例2:查询员工信息表中姓名第二个字符为e,第五个字符为a的员工信息
#案例3:查询姓名中前面字符为ernst的员工信息
#案例4:查询姓名中第二个字符为_的员工信息
select * from employees where first_name like 'e%';
select * from employees where first_name like '_e__a';
select * from employees where first_name like 'ernst%';
select * from employees where last_name like '_$_%' escape '$';
#案例1:查询工种编号是IT_PROT或是ST_CLERK或是AD_VP的员工信息
select * from employees where job_id in('IT_PROG','ST_CLERK','AD_VP');
#案例1:查询哪个员工没有奖金
#案例2:查询哪个员工有奖金
select first_name from employees where commission_pct is null ;
select first_name from employees where commission_pct is not null;
#案例1:按工资从低到高排序
select distinct * from employees order by salary desc;
#部门编号>50的按工资从高到低排序,如果一样,再按frist_name升序
select * from employees where department_id>50 order by salary desc,first_name;
#将姓名中包含e字符的年薪和姓名显示出来,并且按年薪进行降序
select salary*12*(1+ifnull(commission_pct,0)) 年薪,first_name from employees where first_name like '%e%' order by 年薪 desc;
#显示所有有奖金的员工姓名、奖金、姓名的长度,按姓名的长度从低到高排序
select last_name