Employee test database statement

Time:2021-10-7

1. Find all contents of the entire employee table.
select *
from employees
2. View the employee name (last_name).
select last_name
from employees
3. View employee number, name, and type of work.
select last_name,job_id,employee_id
from employees
4. Displays the names and wages of all employees and displays department_ The ID is displayed as (department_id).
select last_name,salary,DEPARTMENT_ID as Department_Id
from employees
5. Find employees working in department 60.
select last_name+first_name name,department_id
from employees
where departmet_id=60
6. Position to be found is sh_ Clerk and SA_ Man’s employee name (last_name).
select last_name job_id
from employees
where job_id in (‘sh_clerk’,’sa_man’)
7. Find position is not sh_ Clerk and SA_ Type of work and name of man’s employees. Display the name as (first_name + last_name named “name”).
select first_name+last_name Name, job_id
from employees
where job_id not in (‘sh_clerk’,’sa_man’)
8. Find out which employees have salaries between 2000 and 3000
select *
from employees
where salary between 2000 and 3000
9. Find out which employees are not paid between 3000 and 5000
select *
from employees
where salary not between 3000 and 5000
10. Find first_ Name starts with D, followed by only three letters of employee information.
select *
from employees
where first_name like ‘D___’ and first_name not like ‘d__ ‘
11. Find last_ Name employee information starting with K.
select last_name,first_name,department_id
from employees
where last_name like ‘k%’
12. Find the first_name, type of work and department number of the employee whose name starts with the letter M and ends with L, and whose third letter is C
select first_name,job_id,department_id
from employees
where first_name like ‘m_c%l’
13. Find out which employees do not start with SA.
select job_id
from employees
where job_id not like ‘sa%’
14. Find employee information without bonus.
select *
from employees
where commission_pct is null
15. Find information about employees with bonuses.
select *
from employees
where commission_pct is not null
16. Find information about employees in department 30 who are not clerk.
select *
from employees
where department_id=30 and job_id not like ‘%clerk%’
17. Find information about employees who work in department 30 or are not clerk.
select *
from employees
where department_id=30
or job_id not like ‘%clerk%’
Find the information of employees in department 60 whose salary is greater than 5000
select *
from employees
where department_id=60
and salary>5000
18. Displays the employee’s last_name in alphabetical order.
select last_name
from employees
order by last_name
19. Displayed in descending order of department number.
select * from employees order by department_id desc
20. Find information about employees whose salary is higher than $2000, sorted by department number and employee name.
select * from employees where salary>2000 order by department_id,employee_id
21. Select employee information with bonus higher than 5%
SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCT
FROM dbo.EMPLOYEES
WHERE (COMMISSION_PCT > .05)
22. Query the information of employees whose annual salary is higher than 50000
select from employees where 12salary>50000
23 query the names of employees with bonuses higher than 5000

1. Find out the name of the employee with department area number 1700
select first_name,last_name,city,department.location_id
from locations,employees,department
where locations.location_id=department.location_id
and locations.location_id=1700
2. Query the name and salary information of employees whose working area is Beijing
select first_name,last_name,salary,commission_pct,city
from locations,employees,departments
where departments.location_id=locations.location_id
and departments.department_id = employees.department_id
and departments.location_id=1700
3. Query the employee name, salary and salary category name whose salary standard is class B
select last_name,first_name,salary,commission_pct,gra
from departments d,employees e,job_grades j
where e.salary between j.lowest and j.highest
and j.gra=’b’
and d.department_id=e.department_id
4. Find out the employee and salary information managed by the supervisor raphaely
select a.last_name+a.first_name as name, a.salary,a.commission_pct,b.last_name
from employees a,employees b
where a.department_id=b.department_id
and a.last_name like ‘%raphaely%’
5. Find out the Department where the employee belongs and show the records of the Department without employees.
select e.last_name+e.first_name as name,d.department_id
from departments d
left outer join employees e
on (e.department_id=d.department_id)
6. Query employee information without assigned Department
select e.last_name+e.first_name as name,e.department_id
from departments d
left outer join employees e
on (e.department_id=d.department_id)
where d.department_id is null
7. Calculate the average salary and total salary of each department
select department_id,sum (salary) sum,avg (salary) avg
from employees
group by department_id
8. Query the number of employees of each type of work in each department
select count(*)num,department_id,job_id
from employees
group by department_id,job_id
9. Please calculate the total number of employees in the employee table
select count(*)
from employee
10. Please calculate the average salary of all employees in the employee table
select avg(salary)
from employee
11. Please find out the minimum wage in the employee table
select min(salary)
from employee
12. Please query the maximum salary in the employee table
select max(salary)
from employee
13. Please calculate the average wage, maximum wage and minimum wage of each department
select max(salary) max,min(salary) min,avg(salary) avg,department_id
from employee
group by department_id
14. Query the Department name, salary and salary with total salary greater than 4200 grouped by department name
select department_name,sum(salary)
from employees e,departments d
where e.department_id=d.department_id
group by department_name
having sum(salary)>4200
test001

1. Please query the minimum wage employees in the employee table
select last_name
from employee
where salary=(select min(salary) from employee)
2. Please find the employee with the highest salary in the employee table
select last_name
from employee
where salary=(select max(salary) from employee)
3. Query last of employees whose salary is higher than No. 105_ Name, and employees with the same type of work as him.
select last_name,job_id,salary
from employees
where salary>(select salary from employees where employee_id=’105′)
and job_id=(select job_id from employees where employee_id=’105′)
4. Query employees whose salary is higher than or equal to the maximum salary of department No. 30.
select last_name,salary
from employees
where salary>=(select max(salary) from employees where department_id=30)
5. Query the information of all personnel in the Department of employees with wages between 1000 and 5000.
select *
from employees
where department_id in
(select department_id from employees where salary between 1000 and 5000)
6. Find the personnel information of all employees in the Department whose salary is higher than No. 60. Display their employee number, last_ Name and salary.
select last_name,employee_id,salary
from employees
where salary>
(select max(salary) from employees where department_id=60)
7. Change the type of work and department number of employee 114 to the type of work and department number of employee 102.
8. Change the department number of all employees with the same type of work as employee 106 to the Department of employee 106.
9. Query type of work is not sh_ Clerk, and the salary is less than any of them_ Employee information of clerk.