99 commonly used query statements in Oracle database

1. select * from emp;

2. select empno, ename, job from emp;

##3. select empno number, ename name, job job from emp;

4. select job from emp;

5. select distinct job from emp;

6. select distinct empno, job from emp;

Explanation: Because the employee numbers are not repeated , so it is proved that all columns are not duplicated, so duplicate columns cannot be eliminated.

7. Query the employee's number, name, and job, but the displayed format is: employee number: 7369, name Is: smith, job is: clear

select 'employee number: ' || empno || ', name is: ' || ename || ', job is: ' || from job emp;

8. Find the name and annual salary of each employee

select ename, sal * 12 income from emp;

9. Find the information of all employees whose salary is greater than 1500

select * from emp where sal > 1500;

10. Query the information of employees who can get bonus every month

select * from emp where comm is not null;

11. Query the employees without bonus Information

select * from emp where comm is null;

12. Query the information of employees whose basic salary is greater than 1500 and can receive bonuses

select * from emp where sal > 1500 and comm is not null;

13. Query the information of employees whose basic salary is greater than 1500 or who can receive bonuses

select * from emp where sal > 1500 or comm is not null;

14. Query Get the information of employees whose basic salary is not greater than 1500 or who cannot receive bonuses

select * from emp where not(sal > 1500 and comm is not null);

15. Query the basic salary is greater than 1500, but All employee information less than 3000

select * from emp where sal > 1500 and sal < 3000;

16. Query all employee information whose basic salary is greater than or equal to 1500, but less than or equal to 3000

select * from emp where sal >= 1500 and sal <= 3000;
select * from emp where sal between 1500 and 3000;

17. Query all employee information hired in 1981 ( Employees hired between January 1, 1981 and December 31, 1981)

select * from emp where hiredate between '1-January-81' and '31-December-81';

18. Request to query the employee information whose name is smith

select * from emp where ename = 'SMITH';

19. Request to query the employees whose name is 7369, 7499, 7521 Specific information

select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
select * from emp where empno in(7369, 7499, 7521);

20. Request query Get the specific information of employees whose employees are not 7369, 7499, 7521

select * from emp where empno not in(7369, 7499, 7521);

21. Request to find out whether the names are smith, allen, king Employee information

select * from emp where ename in('SMITH', 'ALLEN', 'KING');

22. Query all employee names whose second letter contains "M" Employee Information

SELECT * from Emp WHERE Ename Like '_m%';
## 23. Query the employee information containing the letter M in the employee name

select * from emp where ename like '%m%m%m%m%m% ';

24. Request to query the information of employees hired in 1981

select * from emp where hiredate like '%81%';

25. Query the salary containing 5 Employee information

select * from emp where sal like '%5%';

26. Query employee information whose employee number is not 7369

select * from emp where empno != 7369;

select * from emp where empno <> 7369;

27. Request to sort by salary from low to high

select * frm emp order by sal;

select * from emp order by sal asc ;

28. Request to sort by salary from high to low

select * from emp order by sal desc;

29. Request to query all employee information of 20 departments, the query information Sort by salary from high to low. If the salaries are equal, sort by employment date from early to late.

select * from emp where deptno = 20 order by sal desc, hiredate asc;

30. Change lowercase letters to uppercase letters

select upper('hello') from dual;

##31. Change uppercase letters to lowercase letters
select lower('HELLO WORLD') from dual;

32. Request to query employee information whose name is smith
select * from emp where ename = upper('smith');

33. Use the initcap() function to change the first character of the word Capitalize letters
select initcap('hello world') from dual;

34. Change the employee names in the employee table to capitalize the first letter
select initcap(ename) from emp;

35. Concatenate the strings "hello" and "world"
select concat('hello ', 'world') from dual;

36. Commonly used operations on strings Character processing function
select substr('hello', 1, 3) intercepts the string, length('hello') is the length of the string, replace('hello', 'l', 'x') replaces the string from dual;

select substr('hello', 0, 3) intercept the string, length('hello') the length of the string, replace('hello', 'l', 'x') string replacement from dual ;

37. Display the names and last three characters of all employees
select ename, substr(ename, length(ename) -2) from emp;
select ename, substr(ename, -3, 3) from emp;

38. Use numeric functions to perform rounding operations
select round(789.536) from dual;

39. Require that the value 789.536 be kept to two decimal places
select round(789.536, 2) from dual;

40. It is required to round the tenth digit of the integer in the 789.536 value
select round(789.536, -2) from dual;

41. Using the trunc() function will not retain any decimals, and the decimal point will not perform rounding operations
select trunc(789.536) from dual;

42. The decimal point can also be specified through trunc() Reserved number of digits
select trunc(789.536, 2) from dual;

43. Use negative numbers to represent digits
select trunc(789.536, -2) from dual;

44 . Use the mod() function to perform the remainder operation
select mod(10, 3) from dual;

45. Display the number of weeks in which employees in the 10 department entered the company (current date - employment date = number of days / 7 = week number)
select empno, ename, round((sysdate - hiredate) / 7) from emp where deptno = 10;

46. Date function
months_between(): find The number of months in a given date range
add_months(): Add the specified number of months to the specified date to find the subsequent date
next_day(): The next date of the specified date
last_day() : Find the last day of the month on a given date

47.
select empno, ename, months_between(sysdate, hiredate) from emp;
select empno, ename, round(months_between(sysdate, hiredate)) from emp;

48. select sysdate, add_months(sysdate, 4) from dual;

49. select next_day(sysdate, 'Monday') from dual;

50. select last_day(sysdate) from dual;

51. Conversion function
to_char(): Convert to string
to_number(): Convert to number
to_date() : Convert to date

52. Query the employee number, name, employment date of all employees
select empno,
ename,
to_char(hiredate, 'yyyy') year,
to_char(hiredate, 'mm') months,
to_char(hiredate, 'dd') day
from emp;

select empno, ename, to_char(hiredate, 'yyyy-mm-dd' ) from emp;

select empno, ename, to_char(hiredate, 'fmyyyy-mm-dd') from emp;

53. Query the number, name and salary of all employees
select empno, ename, sal from emp;
select empno, ename, to_char(sal, '99,999') from emp;
select empno, ename, to_char(sal, 'L99,999') from emp;
select empno, ename, to_char(sal, '$99,999') from emp;

54. select to_number('123') + to_number('123') from dual;

55 . Convert a string into a date type
select to_date('2009-01-01', 'yyyy-mm-dd') from dual;

56. Find the annual salary of each employee ( Requirements plus bonus)
select empno, ename, sal, comm, (sal + comm) * 12 from emp;
select empno, ename, sal, comm, nvl(comm, 0), (sal + nvl (comm, 0)) * 12 income from emp;

57. decode() function is similar to if...elsif...else statement
select decode(1, 1, 'The content is 1', 2, 'The content is 2', 3, 'The content is 3') from dual;

58. Query the employee's number, name, date of employment and work, and request to replace the employee's job with The following information:
select empno employee number,
ename employee name,
hiredate employment date,
decode(job,
'CLERK', 'salesman',
'SALESMAN' , 'Salesperson',
'MANAGER', 'Manager',
'ANALYST', 'Analyst',
'PRESIDENT', 'President'
) Position
from emp;

59. Cartesian product (cross join)
select * from emp, dept;
select * from emp cross join dept;

60. Inner join
select * from emp e, dept d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e .deptno = d.deptno;

61. Natural join
select * from emp natural join dept;
select * from emp e join dept d using(deptno);

62. Request to query employee number, name, department number, name, address
select e.empno, e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;

63. Request to query the employee’s name, job, and the name of the employee’s immediate superior
select e.ename, e.job, m.ename from emp e , emp m where e.mgr = m.empno;

64. Request to query the employee’s name, job, the name of the employee’s direct superior and the name of the department
select e.ename, e.job, m.ename, d.dname from emp e, emp m, dept d where e.mgr = m.empno and e.deptno = d.deptno;

65. Request to query each employee’s name, salary, department name, salary level in the company (salgrade), and the name of the leader and salary level in the company
select e.ename, e.sal , d.dname, s.grade, m.ename, m.sal, ms.grade
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e.mgr = m.empno
and m.sal between ms.losal and ms.hisal;

select e. ename,
e.sal,
d.dname,
decode(s.grade, 1, 'Fifth grade', 2, 'Fourth grade', 3, 'Third grade', 4 , 'Second grade', 5, 'First grade'),
m.ename,
m.sal,
decode(ms.grade, 1, 'Fifth grade', 2, ' Fourth grade', 3, 'Third grade', 4, 'Second grade', 5, 'First grade')
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empno
and m.sal between ms.losal and ms.hisal;

66. select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e join inner dept d on e.deptno = d.deptno;

67. Left outer join
select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno (+);
select empno, ename, d.deptno, dname, loc from emp e left outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e left join dept d on e.deptno = d.deptno(+);

68. Right outer join
select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno(+) = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right join dept d on e.deptno = d.deptno;

69. select e.empno, e.ename, m.empno, m. ename from emp e, emp m where e.mgr = m.empno;

70. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno(+);

71.
select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30;
select * from emp e inner join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e natural join dept d where deptno = 30;
select * from emp e join dept d using(deptno) where deptno = 30;

72.
select e.ename, d. deptno, d.dname, d.loc from emp e right outer join dept d on e.deptno = d.deptno;
select e.ename, d.deptno, d.dname, d.loc from emp e right join dept d on e.deptno = d.deptno;
select e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno(+) = d.deptno;

73. select count(ename) from emp;

74. select min(sal) from emp;

75. select max(sal) from emp;

76. select sum(sal) from emp;

77. select avg(sal) from emp;

78. select sum(sal) from emp where deptno = 20;

79. select avg(sal) from emp where deptno = 20;

80. Find the number of employees in each department
select deptno, count(deptno) from emp group by deptno;
select deptno, count(empno) from emp group by deptno;

##81. Find the average salary of each department

select deptno, avg(sal) from emp group by deptno;

82. Group by department and display the name of the department and the number of employees in each department

select d.dname, count(e.empno) from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

select d.deptno, d.dname, temp.c

from (select deptno, count(e.empno) c from emp e group by e.deptno) temp, dept d
where temp.deptno = d.deptno;

83. Request to display the department number and average salary where the average salary is greater than 2000

select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;

84. Display the job name of non-sales personnel and the sum of the monthly wages of employees engaged in the same job, and must meet the monthly wages of employees engaged in the same job If the total is greater than 5000, the output results are sorted in ascending order by the total monthly salary.

select job, sum(sal) su from emp where job <> 'SALESMAN' group by job having sum(sal) > 5000 order by su ;

select temp.job, sum(temp.sal) s

from (select job, sal from emp e where job <> 'SALESMAN') temp
group by temp.job
having sum(temp.sal) > 5000
order by s;

85. Find the salary of the department with the highest average salary

select max(avg(sal)) from emp group by deptno;

86. Request to query all employee information with higher salary than employee number 7654

select * from emp where sal >(select sal from emp where empno = 7654);

87. Request to query the information of all employees whose salary is higher than 7654 and who are engaged in the same job as 7788
select * from emp
where sal >(select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);

88. Request to query the name, job, salary of the employee with the lowest salary
select ename, job, sal from emp where sal = (select min(sal) from emp);

89. Request to query: department name, number of employees in the department, average salary of the department, name of the lowest paid employee in the department
select d.dname, temp. c, temp.a, e.ename
from dept d,
(select deptno, count(empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp,
emp e
where d.deptno = temp.deptno and e.sal = temp.m;

select d.deptno, temp.dname, temp.c, temp.a, e.ename, e .sal
from
(select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal) m
from emp e, dept d
where e.deptno = d.deptno
group by d.dname) temp,
emp e,
dept d
where temp.m = e.sal
and temp.dname = d.dname ;

90. Find the information of the minimum wage employees in each department
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any(select min(sal) from emp group by deptno);
select * from
(select min(sal) m from emp group by deptno) temp,
emp e
where e.sal = temp.m;

91. In Example 90, employee information that is greater than the lowest (small) salary in the subquery condition
select * from emp where sal >any(select min(sal) from emp group by deptno);
select * from emp where sal > (select min(min(sal)) from emp group by deptno);

##92. In Example 90, Employee information smaller than the highest (larger) salary in the subquery condition

select * from emp where sal select * from emp where sal < (select max(min(sal)) from emp group by deptno);

93. In Example 90, employee information that is greater than the highest (larger) salary in the subquery condition

select * from emp where sal >all(select min(sal) from emp group by deptno);
select * from emp where sal > (select max(min(sal)) from emp group by deptno);

94. In Example 90, the employee information is smaller than the lowest (smaller) salary in the subquery condition

select * from emp where sal select * from emp where sal < (select min(min(sal)) from emp group by deptno);

95. Find information about employees without bonuses in 20 departments

select * from emp where (sal, nvl(comm, -1)) in (select sal, nvl(comm, -1) from emp where deptno = 20);
select * from emp where deptno = 20 and comm is null ;

96. The union operator returns all unique rows selected by the two queries

select deptno from emp union select deptno from dept;

97. The union all operator is combined All rows selected by two queries, including duplicate rows

select deptno from emp union all select deptno from dept;

98. The intersect operator only returns rows that are common to both queries

select deptno from emp intersect select deptno from dept;

99. The minus operator only returns rows selected by the first query but not selected by the second query, that is, in the first query result Exclude the rows that appear in the second query result

select deptno from dept minus select deptno from emp;

For more articles related to the 99 query statements commonly used in Oracle databases, please pay attention to the PHP Chinese website!