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

Source: Internet
Author: User

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.