Oracle foundation – simple query exercise (2)

Time:2020-5-21

Practice based on the employees table of HR user in Oracle Database

  1. HR needs to be paid more than $12000 a month (last_ Name) and salary report, please complete the query statement
select last_name, salary from employees where salary >12000;
  1. HR needs to identify high-income and low-income employee groups. Please show the name and salary of employees whose monthly salary is not in the range of $5000 to $12000
select last_name, salary from employees where salary not between 5000 and 12000;
  1. Please check the employee’s name and date of employment in 1994
select last_name, hire_date from employees where extract(year from hire_date)=1994;
  1. Please check the name and position of the employee without leader
select last_name,job_id from employees where manager_id is null;
  1. Please check the name of employees with bonus, monthly salary and bonus, and rank them in descending order by bonus coefficient
select last_name,salary,salary*commission_pct form employees where nvl(commission_pct,0)>0 order by commission_pct desc;
  1. Please query the employee name and monthly salary whose monthly salary is greater than 1000
select last_name,salary from employees where salary>1000;
  1. Please check the employee name with the third letter a
select last_name from employees where last_name like '__a%';
  1. Please query the employee name including a or e
select last_name from employees where last_name like '%a%' or lase_name like '%e%';
  1. Please check the position is “Sa”_ Rep “or” St_ “And monthly salary is not $2500, $3500, $7000
select * from employees where job_id in ('SA_REP','ST_CLERK') and salary not in (2500,3500,7000);
  1. Please check the employee name, salary and bonus coefficient whose bonus coefficient is 20%
select last_name,salary,commission_pct from employees where commission_pct = 0.2;