Oracle Basics – simple query exercise (2)

Time:2020-9-26

Practice based on the employees table under HR user in Oracle database

  1. HR needs to get the name of the employee whose salary is 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 groups of high-income and low-income employees. Please show the name and salary of the employee 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 find the employee name and date of employment of the employee in 1994
select last_name, hire_date from employees where extract(year from hire_date)=1994;
  1. Please check the names and positions of employees without leaders
select last_name,job_id from employees where manager_id is null;
  1. Please query the employee name, monthly salary and bonus with bonus in descending order according to bonus coefficient
select last_name,salary,salary*commission_pct form employees where nvl(commission_pct,0)>0 order by commission_pct desc;
  1. Please check the name and salary of employees 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 containing 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 the monthly salary is not $2500, 3500 or $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;