ORACLE-HR Table Query Command Practice (Ultra-Complete Select command Encyclopedia) ____oracle

Switch to Oracle's HR users to practice under

1. Check the name and salary of the employee whose salary is more than 12000

Select Initcap (concat (last_name,first_name)) "name" salary from employees where salary>12000;

2. Check the employee's name and department number for employee number 176

Select Initcap (concat (last_name,first_name)) "name" department_id from employees where employee_id = 176;

3. Select the name and salary of the employee whose salary is not 5000 to 12000

Select Initcap (concat (last_name,first_name)) "name" salary from employees where salary<5000 or salary>12000;

4. Select the employee's name, job_id, and time of employment between 1908-02-01 and 1908-05-01

Writing a:

Select Initcap (concat (last_name,first_name)) "name" Job_id,hire_date from employees where hire_date between ' January-February-08 ' and ' January-May-08 ';

Writing two:

Select Initcap (concat (last_name,first_name)) "name" Job_id,hire_date from employees where hire_date between (' 1908-02-01 ', ' yyyy-mm-dd ') and to_date (' 1908-05-01 ', ' yyyy-mm-dd ');

5. Select the name and department number of the employee who works in department 20 or 50th

Writing a:

Select Initcap (concat (last_name,first_name)) "name" department_id from employees where department_id=20 or department_id = 50;

Writing two:

Select Initcap (concat (last_name,first_name)) "name" department_id from Employees where department_id in (20,50);

6. Select the name and time of employment of the employee employed in 1908

Writing a:

Select Initcap (concat (last_name,first_name)) "name" Hire_date from employees where hire_date like '%08 ';

Writing two:

Select Initcap (concat (last_name,first_name)) "name", hire_date from Employees where hire_date between (' 1908-1-1 '), ' Yyyy-mm-dd ') and to_date (' 1908-12-31 ', ' yyyy-mm-dd ');

7. Select the name and job_id of the employee without the manager in the company

Writing a:

Select Initcap (concat (last_name,first_name)) "name" job_id from employees where manager_id is null;

Writing two:

Select Initcap (concat (last_name,first_name)) "name" job_id from Employees where NVL (manager_id,0) = 0;

8. Select the name, salary and bonus level of the employee who has the bonus in the company

Writing a:

Select Initcap (concat (last_name,first_name)) "name" salary,commission_pct from employees where commission_pct was not null ;

Writing two:

Select Initcap (concat (last_name,first_name)) "name" salary,commission_pct from Employees where NVL2 (commission_pct, commission_pct,0) >0;

Writing three:

Select Initcap (concat (last_name,first_name)) "name" commission_pct from Employees where NVL (commission_pct,0) <>0 ;

9. Select the employee name of the third letter is the employee name of a

Select Initcap (concat (last_name,first_name)) "name" from Employees where Initcap (Concat (last_name,first_name)) like ' __a %';

10. Select the names of employees with letters A and E

Select Initcap (concat (last_name,first_name)) "name" from Employees where Initcap (Concat (last_name,first_name)) like '%a% ' And Initcap (Concat (last_name,first_name)) like '%e% ';

11. Display System Time

Writing a:

Select sysdate from dual;

Writing two:

Select Current_timestamp from dual;

12. Query employee number, name, salary, and salary increase of 20% after the result (new salary)

Select Employee_id,initcap (concat (last_name,first_name)) "name", salary*1.2 as "new salary" from employees;

13. The name of the employee is sorted by first letter and the length of the name is written.

Select Initcap (concat (last_name,first_name)) "name", Length (Initcap (concat)) as "name length" from Employees ORDER BY substr (Initcap (concat (Last_name,first_name)), 1, 1);

14. Check the names of each employee and show the number of months that employees work in the company

Select Initcap (concat (last_name,first_name)) "Name", Trunc (Months_between (sysdate,hire_date), 0) "Working time" from employees;

15. Query the name of the employee and the number of months worked in the company (Worked_month), sorted by the number of months in descending order

Select Initcap (concat (last_name,first_name)) "Name", Trunc (Months_between (sysdate,hire_date), 0) "Working time" from employees Order BY Trunc (Months_between (sysdate,hire_date), 0) desc;

16. Make a query that produces the following results

<last_name> earns <salary> monthly but wants <salary*3>

Dream Salary

King earns $24000 monthly but wants $72000

Select last_name| | ' Earns ' | | To_char (Salary, ' $99999 ') | | ' monthly but wants ' | | To_char (salary*3, ' $99999 ') as "Dream salary" from employees;

17. Use the Decode function, according to the following conditions:

Job Grade

Ad_pres A

St_man B

It_prog C

Sa_rep D

St_clerk E

Others F

Produce the following results

Last_Name	job_id	Grade
King	Ad_pres	A

Writing a:

Select Last_name,job_id,decode (job_id, ' ad_pres ', ' A ', ' St_man ', ' B ', ' It_prog ', ' C ', ' sa_rep ', ' D ', ' St_clerk ', ' E ', ' F ') Grade from Employees;

Writing two:

Select last_name,job_id,

Case job_id

When ' ad_pres ' then ' A '

When ' St_man ' then ' B '

When ' It_prog ' then ' C '

When ' sa_rep ' then ' D '

When ' St_clerk ' then ' E '

Else ' F ' End "grage" from employees;

18. Inquire the maximum value, minimum, average, sum of employees ' salary

Select Max (Salary) "Max", Min (Salary) "Min", avg (Salary) "average", sum (Salary) "sum" from employees;

19. Inquire the maximum value, minimum, average, sum of the employees ' salary of each job_id

Select Job_id,max (Salary) "Max", Min (Salary) "Min", avg (Salary) "average", sum (Salary) "sum" from Employees group by JOB_ID;

20. Select the number of employees with various job_id

Select Job_id,count (*) from Employees GROUP by JOB_ID;

21. Check the gap between the employee's maximum wage and the minimum wage (difference)

Select Max (Salary)-min (salary) as "difference" from employees;

22. Check the managers under the minimum wage of employees, where the minimum wage can not be less than 6000, no managers of employees do not count

Select Manager_id,min (Salary) from Employees GROUP by MANAGER_ID has min (salary) >=6000 and manager_id is not null;

23. Check the names of all departments, LOCATION_ID, the number of employees and the average wage

Select Department_name,location_id,count (e.job_id) as "total number of departments", AVG (e.salary) as "average wage" from departments d,employees E where d.department_id=e.department_id group by department_name,location_id;