Oracle-hr Table query command exercises (a complete list of select commands)

Oracle-hr Table query command exercises (a complete list of select commands) switch to the oracle hr user to practice 1. query the names and salaries of employees whose salaries are greater than 12000. Select initcap (concat (last_name, first_name) "name", salary from employees where salary> 12000; 2. query the name and department number of an employee whose employee number is 176. select initcap (concat (last_name, first_name) "name", department_id from employees where employee_id = 176; 3. select the name and salary of an employee whose salary is not between 5000 and 12000. select initcap (concat (last_name, first_name) "name", salary from employees where salary <5000 or salary> 12000; 4. select the name of the employee whose employment time is between and. job_id and employment time are written as one: select initcap (concat (last_name, first_name) "name", job_id, hire_date from employees where hire_date between '01-February-08 'and '01-May-08'; Statement 2: select initcap (concat (last_name, first_name) "name ", job_id, hire_date from employees where hire_date between to_date ('2017-02-01 ', 'yyyy-MM-DD') and to_date ('2017-05-01 ', 'yyyy-MM-DD'); 5. select the employee name and department number that work in department 20 or 50. Statement 1: Select initcap (concat (last_name, first_name) "name ", statement from employees where department_id = 20 or department_id = 50; Statement 2: 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 hired on April 9, 1908. Statement 1: select initcap (concat (last_name, first_name) "name", hire_date from employees where hire_date like '% 08 '; statement 2: select initcap (concat (last_name, first_name) "name", hire_date from employees where hire_date between to_date ('2017-1-1 ', 'yyyy-MM-DD ') and to_date ('1970-12-31 ', 'yyyy-MM-DD'); 7. select the employee name without manager in the company and job_id Statement 1: Select initcap (concat (last_name, first_name) "name", job_id from employees where manager_id is null; Statement 2: select initcap (concat (last_name, first_name) "name", job_id from employees where nvl (manager_id, 0) = 0; 8. select the name of the employee who has the bonus in the company, and write the statement for salary and bonus level 1: Select initcap (concat (last_name, first_name) "name", salary, commission_pct from employees where commission_pct is not null; statement 2: Select initcap (concat (last_name, first_name) "name", salary, commission_pct from employees where nvl2 (commission_pct, commission_pct, 0)> 0; Statement 3: select initcap (concat (last_name, first_name) "name", commission_pct from employees where nvl (commission_pct, 0) <> 0; 9. select initcap (concat (last_name, first_name) "name" from employees where initcap (concat (last_name, first_name )) like '_ a %'; 10. 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 Statement 1: Select sysdate from dual; Statement 2: Select current_timestamp from dual; 12. result (new salary) Select employee_id, initcap (concat (last_name, first_name) "name ", salary * 1.2 as "new salary" from employees; 13. sort the employee's name by the first letter and write the name length (length) Select initcap (concat (last_name, first_name) "name", length (initcap (concat (last_name, first_name) as "Name Length" from employees order by substr (initcap (concat (last_name, first_name), 1, 1); 14. query the names of employees and display the number of months each employee works in the company. Select initcap (concat (last_name, first_name) "name", trunc (months_between (sysdate, hire_date ), 0) "active time" from employees; 15. query the name of an employee and the number of months (worked_month) of work in the company, and sort Select initcap (concat (last_name, first_name) "name" in descending order of the number of copies per month ", trunc (months_between (sysdate, hire_date), 0) "active time" from employees order by trunc (months_between (sysdate, hire_date), 0) desc; 16. make a query, generate the following result <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 generates the following result: Last_name Job_id Grade king AD_PRES A write method: select last_name, job_id, decode (job_id, 'ad _ pres', 'A', 'st _ MAN ',' B ', 'it _ prog', 'C ', 'sa _ REP ', 'D', 'st _ cler', 'E', 'F') Grade from employees; Statement 2: 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 _ cler' then' E 'else' f'end "Grage" from employees; 18. queries the maximum, minimum, and average values of the company's employees' salaries. The sum is select max (salary) "maximum", min (salary) "minimum", avg (salary) "average ", sum (salary) "sum" from employees; 19. queries the maximum, minimum, average, and total values of the employees of each job_id. select job_id, max (salary) "maximum", min (salary) "minimum", avg (salary) "average", sum (salary) "sum" from employees group by job_id; 20. select job_id, count (*) from employees group by job_id; 21. queries the gap between the highest wage and the minimum wage of an employee (DIFFERENCE) Select max (salary)-min (salary) as "DIFFERENCE" from employees; 22. query the minimum wage of employees under each manager. The minimum wage cannot be less than 6000. employees without managers are not counted in Select manager_id, min (salary) from employees group by manager_id having min (salary)> = 6000 and manager_id is not null; 23. query the names of all departments, location_id, number of employees, and average salary. Select department_name, location_id, count (e. job_id) as "Total number of departments", avg (e. salary) as "average salary" from departments d, employees e where d. department_id = e. department_id group by department_name, location_id;