99 query statements commonly used in Oracle database (reproduced)

1. Select * from EMP;

2. Select Empno, ename, job from EMP;

3. Select empno number, ename name, job work from EMP;

4. Select Job from EMP;

5. Select distinct job from EMP;

6. Select distinct empno, job from EMP;
Description: Because the employee number is not duplicated, it proves that all columns are not duplicated at this time, so duplicate columns cannot be eliminated.

7. Check out the employee's number, name, and work, but the format shown: number is: 7369 employee, name is: Smith, work is: clear
Select ' number is: ' | | Empno | | ' Employee, name is: ' | | ename | | ', Work is: ' | | Job from EMP;

8. Find out the name and salary of each employee
Select Ename, sal * Income from EMP;

9. Find out all employee information for wages greater than 1500
SELECT * from emp where sal > 1500;

10. Inquire about employee information that can receive bonuses each month
SELECT * FROM EMP where comm are NOT null;

11. Query employee information without a bonus
SELECT * FROM EMP where comm is null;

12. Check the employee information that the base salary is greater than 1500 and can receive the bonus
SELECT * from emp where Sal > Comm and are NOT null;

13. Find employee information that has a base salary greater than 1500 or can receive a bonus
SELECT * from emp where sal > comm are NOT null;

14. Find employee information with a base salary of not more than 1500 or not eligible to receive the bonus
SELECT * from emp where not (Sal > Comm and-is not NULL);

15. Query for all employee information with a base salary greater than 1500 but less than 3000
SELECT * from emp where Sal > and Sal < 3000;

16. Query all employee information with a base salary greater than or equal to 1500, but less than or equal to 3000
SELECT * from emp where Sal >= and Sal <= 3000;
SELECT * from EMP where Sal between and 3000;

17. Find out all employee information employed in 1981 (employees hired between January 1, 1981 and December 31, 1981)
SELECT * from emp where hiredate between ' January-January -81 ' and ' 3 January-December-81 ';

18. Ask for a name that is Smith's employee information
SELECT * from emp where ename = ' SMITH ';

19. Request for information about employees who are employees of 7369, 7499, 7521
SELECT * from emp where empno = 7369 or empno = 7499 or empno = 7521;
SELECT * from EMP where empno in (7369, 7499, 7521);

20. Ask for specific information about employees who are not 7369, 7499, 7521
SELECT * from EMP where empno not in (7369, 7499, 7521);

21. Request the name is Smith, Allen, King's employee information
SELECT * from EMP where ename in (' SMITH ', ' ALLEN ', ' KING ');

22. Find employee information with "M" in the second letter of all employees ' names
SELECT * from emp where ename like ' _m% ';

23. Query employee information that contains the letter M in the employee's name
SELECT * from emp where ename like '%M% ';

24. Request for information on employees hired in 1981
SELECT * from emp where hiredate like '%81% ';

25. Inquire about 5 of employee information in payroll
SELECT * from emp where Sal like '%5% ';

26. Inquire about employee information that is not 7369 of the employee number
SELECT * FROM emp where empno! = 7369;
SELECT * from emp where empno <> 7369;

27. Order from low to high according to salary
SELECT * frm emp order by Sal;
SELECT * from emp ORDER by SAL ASC;

28. Order from high to low salary
SELECT * from emp order BY Sal Desc;

29. All employee information is requested for 20 departments, the information is sorted according to the salary from high to low, if the wages are equal, according to the employment date from early to late.
SELECT * from emp where deptno = the ORDER by sal Desc, hiredate ASC;

30. Change lowercase letters to uppercase
Select Upper (' Hello ') from dual;

31. Turn uppercase letters into lowercase letters
Select lower (' HELLO world ') from dual;

32. Ask for a name that is Smith's employee information
SELECT * from emp where ename = Upper (' Smith ');

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

34. Change the name of the employee in the employee table to the beginning letter capital
Select Initcap (ename) from EMP;

35. Concatenation of the string "Hello" and "World"
Select concat (' Hello ', ' world ') from dual;

36. Characters commonly used character handling function for manipulating strings
Select substr (' Hello ', 1, 3) intercepts strings, length (' Hello ') strings, replace (' Hello ', ' l ', ' X ') strings replaced from dual;
Select substr (' Hello ', 0, 3) intercepts strings, length (' Hello ') strings, replace (' Hello ', ' l ', ' X ') strings replaced from dual;

37. Displays the last three characters of all employees ' names and names
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. Required to retain 789.536 value two decimal places
Select Round (789.536, 2) from dual;

40. Requires rounding of the 10 bits of an integer in the 789.536 numeric value rounding carry
Select Round (789.536,-2) from dual;

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

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

43. Negative function indicates the number of digits
Select Trunc (789.536,-2) from dual;

44. Use the MoD () function to take the remainder of the operation
Select mod (3) from dual;

45. Show the number of weeks that 10 employees enter the company (current date-Employment date = days/7 = number of weeks)
Select Empno, ename, round ((sysdate-hiredate)/7) from emp where deptno = 10;

46. Date function
Months_between (): Find the number of months for a given date range
Add_months (): Adds the specified number of months to the specified date, after which the date is calculated
Next_day (): The next date for the specified date
Last_day (): Find the date of the last day of the month for a given date

47.
Select Empno, ename, Months_between (Sysdate, HireDate) from EMP;
Select Empno, ename, round (Months_between (Sysdate, HireDate)) from EMP;

Sysdate Select, Add_months (Sysdate, 4) from dual;

A. Select Next_day (sysdate, ' Monday ') from dual;

. Select Last_day (sysdate) from dual;

51. Conversion functions
To_char (): Convert to String
To_number (): Convert to Digital
To_date (): Convert to date

52. Check the employee number, name and 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. Check 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;

Select To_number (' 123 ') + to_number (' 123 ') from dual;

55. Converting a string to a date type
Select To_date (' 2009-01-01 ', ' YYYY-MM-DD ') from dual;

56. Find out the annual salary of each employee (plus bonus required)
Select Empno, ename, Sal, Comm, (sal + comm) * from EMP;
Select Empno, ename, Sal, Comm, NVL (comm, 0), (sal + NVL (comm, 0)) * Income from EMP;

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. Find out the employee's number, name, date of employment and job, and ask to replace the employee's work with the following information:
Select Empno Employee number,
ENAME Employee Name,
HireDate Employment Date,
Decode (Job,
' Clerk ', ' salesman ',
' Salesman ', ' Sales people ',
' manager ', ' managers ',
' Analyst ', ' analyst ',
' President ', ' President '
) position
from EMP;

59. Cartesian product (intersection connection)
SELECT * from EMP, dept;
SELECT * from the EMP Cross join dept;

60. Internal connection
SELECT * FROM EMP E, dept d where e.deptno = D.deptno;
SELECT * FROM EMP e inner joins dept D on e.deptno = D.deptno;
SELECT * FROM emp e joins 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. Ask for the employee's 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. Ask for the name of the employee, the job, the name of the direct superior leader of the employee
Select E.ename, E.job, m.ename from emp E, emp m where e.mgr = M.empno;

64. Ask for the name of the employee, the job, the name of the employee's direct supervisor, and the 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. Ask for the name of each employee, salary, department name, salary at the company level (Salgrade), and the name of the leader and the rank of the company where the salary is
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 rank ', 2, ' fourth Rank ', 3, ' third rank ', 4, ' Second Rank ', 5, ' first rank '),
M.ename,
M.sal,
Decode (Ms.grade, 1, ' Fifth rank ', 2, ' fourth Rank ', 3, ' third rank ', 4, ' Second Rank ', 5, ' first rank ')
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;

Empno Select, 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 joins dept D on e.deptno = D.deptno;

67. Left Outer connection
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 joins dept D on e.deptno = D.deptno;
Select Empno, ename, D.deptno, Dname, loc from EMP e left joins dept D on e.deptno = D.deptno (+);

68. Right Outer connection
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 joins dept D on e.deptno = D.deptno;

Select E.empno, E.ename, M.empno, m.ename from emp E, emp m where e.mgr = M.empno;

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 joins dept D on e.deptno = d.deptno where d.deptno = 30;
SELECT * FROM emp e joins 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 joins 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;

A. Select count (ename) from EMP;

The. Select min (sal) from EMP;

The. Select Max (SAL) from EMP;

Select sum (SAL) from EMP;

A. Select AVG (SAL) from EMP;

The. Select sum (SAL) from emp where deptno = 20;

Select AVG (SAL) from emp where deptno = 20;

80. Find out the number of employees in each department
Select Deptno, COUNT (deptno) from the EMP group by DEPTNO;
Select Deptno, COUNT (empno) from the EMP group by DEPTNO;

81. Find out the average salary for each department
Select Deptno, Avg (SAL) from the EMP group by DEPTNO;

82. Group BY department and show 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. Require that the department number and average salary of the average salary greater than 2000 be displayed
Select Deptno, Avg (SAL) from EMP Group BY DEPTNO have avg (SAL) > 2000;

84. Displays the total number of non-salesperson job names and monthly wages for the same job employee, and the total monthly salary of the employee who is engaged in the same job is greater than 5000, and the output is sorted in ascending order by the total monthly wage.
Select Job, sum (SAL) Su from emp where Job <> ' salesman ' GROUP by job has sum (SAL) > The 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. Finding the highest wage in the department
Select MAX (avg (SAL)) from the EMP group by DEPTNO;

86. Ask for all employee information that is higher than employee number 7654 salary
SELECT * from emp where sal > (select sal from emp where empno = 7654);

87. Ask for a higher salary than 7654, while working with 7788 of all employee information
SELECT * FROM emp
Where Sal > (select sal from emp where empno = 7654)
and job = (select Job from emp where empno = 7788);

88. Ask for the lowest wage employee's name, job, salary
Select ename, Job, Sal from emp where sal = (select min (sal) from EMP);

89. Ask for the name of the department, the number of employees in the department, the average salary of the department, the minimum income of the Department employees
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 out the employee's information for each department's minimum wage
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 larger than the lowest (small) wage in a subquery condition
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, employee information that 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 < (the Select Max (SAL)) from the EMP Group by DEPTNO);

93. In example 90, employee information that is larger than the highest (large) wage in the subquery condition
SELECT * from emp where Sal >all (select min (sal) from EMP Group by DEPTNO);
SELECT * from emp where sal > (the Select max (SAL)) from the EMP Group by DEPTNO);

94. Example 90, employee information that is smaller than the lowest (small) wage in a subquery condition
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 employee information with no 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 =-Comm is null;

The. Union operator returns all the non-repeating rows selected for two queries
Select Deptno from EMP Union select Deptno from dept;

The. Union all operation conforms to all rows selected by two queries, including duplicate rows
Select Deptno from EMP UNION ALL select Deptno from dept;

98. Intersect operator only returns rows for all two queries
Select Deptno from the EMP intersect select Deptno from dept;

The minus operator only returns rows that were selected by the first query but not selected by the second query, that is, the rows that appear in the second query result are excluded from the first query result
Select Deptno from dept minus select Deptno from EMP;

99 query statements commonly used in Oracle database (reproduced)