sql练习

尚硅谷数据库练习题

案例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