99 common query statements for oracle databases

1. select * from emp;

2. select empno, ename, job from emp;

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

4. select job from emp;

5. select distinct job from emp;

6. select distinct empno, job from emp;
Note: Because employee numbers are not repeated, it is proved that all columns are not repeated at this time, so repeated Columns cannot be eliminated.

7. query the employee ID, name, and work, but the displayed format is: 7369 employee, name: smith, and work: clear
Select ':' | empno | 'employee, name:' | ename | ', job:' | job from emp;

8. Obtain the name and annual salary of each employee.
Select ename, sal * 12 income from emp;

9. Obtain information about all employees whose salaries exceed 1500.
Select * from e where sal> 1500;

10. Query Information about employees who can receive bonuses each month.
Select * from emp where comm is not null;

11. Query Information about employees without bonuses
Select * from emp where comm is null;

12. Query Information about employees whose basic salary is greater than 1500 and whose bonuses can be received
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 the bonus
Select * from emp where sal> 1500 or comm is not null;

14. query the information of employees whose basic salary is not greater than 1500 or who cannot receive the bonus
Select * from emp where not (sal> 1500 and comm is not null );

15. query information about all employees whose basic salary is greater than 1500, but less than 3000
Select * from emp where sal> 1500 and sal <3000;

16. query information about all employees 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 Information on all employees hired in 1981 (employees hired between January 1-19, 1981 and December 31)
Select * from emp where hiredate between '1-January-81 'and '31-December-81 ';

18. query the employee information whose name is smith
Select * from emp where ename = 'Smith ';

19. query specific information of employees 7369,749 and 7521
Select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
Select * from emp where empno in (7369,749 9, 7521 );

20. query the details of employees who are not 7369,749, 7521
Select * from emp where empno not in (7369,749 9, 7521 );

21. query the employee information whose name is smith, allen, and king.
Select * from emp where ename in ('Smith ', 'allen', 'King ');

22. The second letter in all employee names contains "M" employee information.
Select * from emp where ename like '_ M % ';

23. The employee's name contains employee information with the letter M.
Select * from emp where ename like '% M % ';

24. Query of information on employees hired in 1981
Select * from emp where hiredate like '% 100 ';

25. query employee information that includes 5 in salary
Select * from emp where sal like '% 100 ';

26. Query Information of employees whose employee number is not 7369
Select * from emp where empno! = 7369;
Select * from emp where empno <> 7369;

27. requirements are sorted in descending order of wages
Select * frm emp order by sal;
Select * from emp order by sal asc;

28. requirements are sorted by salary from high to low
Select * from emp order by sal desc;

29. All employees in department 20 should be queried. The queried information is sorted by salary from high to low. If the wages are equal, the information is sorted 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. query the employee information whose name is smith
Select * from emp where ename = upper ('Smith ');

33. Use the initcap () function to uppercase the first letter of a word
Select initcap ('Hello World') from dual;

34. Change employee names in the employee table to uppercase letters
Select initcap (ename) from emp;

35. concatenate the string "hello" and "world"
Select concat ('hello', 'World') from dual;

36. Common Character Processing functions for string operations
Select substr ('hello', 1, 3) captures the length of the string, replace ('hello', 'l', 'x ') string replacement from dual;
Select substr ('hello', 0, 3) captures the length of the string, replace ('hello', 'l', 'x ') string replacement from dual;

37. display the names and the 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 a numeric function to perform the rounding operation
Select round (789.536) from dual;

39. The 789.536 value must be kept in two decimal places.
Select round (789.536, 2) from dual;

40. The 10 digits of the integer in the 789.536 value must be rounded to carry.
Select round (789.536,-2) from dual;

41. Using the trunc () function will not retain any decimal places, and the decimal point will not be rounded down.
Select trunc (789.536) from dual;

42. You can also specify the number of reserved digits of the decimal point through trunc ().
Select trunc (789.536, 2) from dual;

43. A negative number indicates the number of 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 for employees in 10 departments to enter the company (current date-employment date = days/7 = days)
Select empno, ename, round (sysdate-hiredate)/7) from emp where deptno = 10;

46. Date Functions
Months_between (): returns the number of months in the specified date range.
Add_months (): add the specified number of months to the specified date to obtain the subsequent date.
Next_day (): Specifies the next date of the date
Last_day (): returns the date of the last day of the given month.

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 functions
To_char (): Convert to string
To_number (): convert to a number
To_date (): Convert to date

52. query employee numbers, names, and employment dates 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 IDs, names, and salaries of all employees
Select empno, ename, sal from emp;
Select empno, ename, to_char (sal, '201312') 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 ('000000') + to_number ('000000') from dual;

55. convert a string to a date type
Select to_date ('1970-01-01 ', 'yyyy-mm-dd') from dual;

56. Calculate the annual salary of each employee (requires a 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. The decode () function is similar to the if... elsif... else statement.
Select decode (1, 1, 'content is 1', 2, 'content is 2', 3, 'content is 3') from dual;

58. Check the employee ID, name, employment date, and work. Replace the employee's work with the following information:
Select empno employee ID,
Ename employee name,
Hiredate employment date,
Decode (job,
'Wheel', 'salesclerk ',
'Salesman', 'salesman ',
'Manager', 'manager ',
'Analyst', 'analysts ',
'President ', 'President'
) Position
From emp;

59. Cartesian Product (intersection join)
Select * from emp, dept;
Select * from emp cross join dept;

60. inner connection
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 connection
Select * from emp natural join dept;
Select * from emp e join dept d using (deptno );

62. The employee ID, name, department ID, name, and address must be queried.
Select e. empno, e. ename, d. deptno, d. dname, d. loc from emp e, dept d where e. deptno = d. deptno;

63. query the employee's name, work, and employee's direct superior's name
Select e. ename, e. job, m. ename from emp e, emp m where e. mgr = m. empno;

64. query the employee's name, work, employee's direct superior leadership name, and department name
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. query the name, salary, Department name, salary in the company's salgrade, and the name of the leader and the grade of the company where the salary is located.
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, 'level 5 ', 2, 'level 4', 3, 'level 3 ', 4, 'level 2', 5, 'level 1 '),
M. ename,
M. sal,
Decode (ms. grade, 1, 'level 5 ', 2, 'level 4', 3, 'level 3 ', 4, 'level 2', 5, 'level 1 ')
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 inner join 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. Calculate the average salary of each department
Select deptno, avg (sal) from emp group by deptno;

82. Group by department and display the Department name and 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. requires that the Department numbers and average salaries with an average salary greater than 2000 be displayed
Select deptno, avg (sal) from emp group by deptno having avg (sal)> 2000;

84. displays the non-Salesperson's work name and the total monthly salary of the same employee, and the total monthly salary of the employee engaged in the same job must be greater than 5000. The output result is the ascending order of 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) & gt; 5000
Order by s;

85. Find the Department salary with the highest average salary
Select max (avg (sal) from emp group by deptno;

86. All employees whose salaries are higher than the employee number 7654 are required to be queried
Select * from emp where sal> (select sal from emp where empno = 7654 );

87. Query of all employees whose salaries are higher than 7654 and who are engaged in the same job as 7788
Select * from emp
Where sal> (select sal from e-mapreduce where e-mapreduce = 7654)
And job = (select job from emp where empno = 7788 );

88. The name, work, and salary of the employee with the lowest wage should be queried.
Select ename, job, sal from emp where sal = (select min (sal) from emp );

89. Required query results: department name, number of employees of the Department, average salary of the Department, and name of the employee with the lowest income of 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. Obtain the minimum wage information for 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, employees whose salaries are higher than (smaller) in subquery Conditions
Select * from emp where sal> any (select min (sal) from emp group by deptno );
Select * from emp where sal> (select min (sal) from emp group by deptno );

92. In example 90, the employee information is smaller than the highest (large) wage in the subquery condition.
Select * from emp where sal <any (select min (sal) from emp group by deptno );
Select * from emp where sal <(select max (min (sal) from emp group by deptno );

93. In example 90, the employee information is higher than the highest (higher) 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. employee information in example 90, which is smaller than the minimum wage in subquery Conditions
Select * from emp where sal <all (select min (sal) from emp group by deptno );
Select * from emp where sal <(select min (sal) from emp group by deptno );

95. Find information about employees who do not have any bonuses in department 20.
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 two rows selected for the query that are not repeated.
Select deptno from emp union select deptno from dept;

97. The union all operator combines two queries for all selected rows, including duplicate rows.
Select deptno from emp union all select deptno from dept;

98. The intersect operator returns only the rows of both queries
Select deptno from emp intersect select deptno from dept;

99. The minus operator returns only the rows selected by the first query but not selected by the second query, that is, the rows excluded from the second query result in the first query result.
Select deptno from dept minus select deptno from emp;