99 query statements commonly used in Oracle database _oracle

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;
Note: Because the employee number is not duplicated, it is not possible to eliminate duplicate columns by proving that all the columns are not duplicated at this time.

7. Query the employee's number, name, work, but display the format: 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 annual salary of each employee
Select Ename, sal * Income from EMP;

9. Find all employee information with a salary greater than 1500
SELECT * from emp where sal > 1500;

10. Find out the employee information that can receive the bonus every month
SELECT * FROM EMP where Comm was not null;

11. Search for employee information without bonus
SELECT * FROM EMP where comm is null;

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

13. Find out the employee's information that the base salary is greater than 1500 or can receive the bonus
SELECT * from emp where sal > 1500 or comm are NOT null;

14. Inquire about the employee's information that the base salary is not greater than 1500 or not receive the bonus
SELECT * from emp where not (Sal > 1500 and comm are not NULL);

15. Query basic salary greater than 1500, but less than 3000 of all employee information
SELECT * from emp where sal > 1500 and Sal < 3000;

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

17. Find out all employee information employed in 1981 (employee employed 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 information about employee whose name is Smith
SELECT * from emp where ename = ' SMITH ';

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

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

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

22. Query the employee information for the second letter of all employee's name containing "M"
SELECT * from emp where ename like ' _m% ';

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

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

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

26. Query employee information for employee ID not 7369
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. Demand to be sorted according to salary from high to low
SELECT * from emp order BY Sal Desc;

29. Request to inquire out all the employee information of 20 departments, the information of inquiry is sorted according to the salary from high to low, if the salary is equal, then according to the date of employment from early to late.
SELECT * from emp where deptno = m by sal Desc, hiredate ASC;

30. Turn lowercase letters into uppercase letters
Select Upper (' Hello ') from dual;

31. Change capital letters to lowercase letters
Select lower (' HELLO world ') from dual;

32. Ask for information about employee whose name is Smith
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 employee's name 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. Character handler functions that manipulate strings
Select substr (' Hello ', 1, 3) intercepts strings, length (' Hello ') string lengths, replace (' Hello ', ' l ', ' X ') string replaces from dual;
Select substr (' Hello ', 0, 3) intercepts strings, length (' Hello ') string lengths, replace (' Hello ', ' l ', ' X ') string replaces from dual;

37. Display 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. Require 789.536 values to be retained two decimal places
Select Round (789.536, 2) from dual;

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

41. Using the trunc () function does not preserve any decimal numbers, 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 for a decimal point by trunc ()
Select Trunc (789.536, 2) from dual;

43. Number of negative effects
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 days of 10 employees entering the company (current date-Employment date = day/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 given the date

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

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

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

Select Last_day (sysdate) from dual;

51. Conversion function
To_char (): Converting to strings
To_number (): Convert to Digital
To_date (): Convert to date

52. Check all employee's employee number, name, date of hire
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. Convert 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 (Request plus bonus)
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. Query the employee's number, name, date of employment and work, and require that the employee's work be replaced with the following information:
Select Empno Employee number,
ENAME Employee Name,
HireDate Hire Date,
Decode (Job,
' Clerk ', ' salesman ',
' Salesman ', ' Sales people ',
' manager ', ' managers ',
' ANALYST ', ' analyst ',
' PRESIDENT ', ' President '
) position
from EMP;

59. Cartesian product (connected)
SELECT * from EMP, dept;
SELECT * FROM EMP Cross JOIN dept;

60. Internal 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. 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 employee, work, employee's direct superior leader
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 employee's direct superior leader name 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 paid.
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 Rank ', 3, ' third grade ', 4, ' Second Rank ', 5, ' first rank '),
M.ename,
M.sal,
Decode (Ms.grade, 1, ' Fifth grade ', 2, ' fourth Grade ', 3, ' third grade ', 4, ' Second Grade ', 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;

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 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 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 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 Join 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 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;

Select COUNT (ename) from EMP;

A. Select min (sal) from EMP;

Select Max (SAL) from EMP;

Select sum (SAL) from EMP;

Select Avg. (SAL) from EMP;

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 wage in each department
Select Deptno, Avg (SAL) from EMP Group by DEPTNO;

82. Group by sector 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. Requirements show departmental numbers and average wages of more than 2000 average wage
Select Deptno, Avg (SAL) from EMP GROUP BY DEPTNO has avg (SAL) > 2000;

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

86. Request for information on all employees with a higher salary than the employee number 7654
SELECT * from emp where sal > (select sal from emp where empno = 7654);

87. Ask for information on all employees with a salary ratio of 7654 high and at the same time with 7788 doing the same job
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 name, work and salary of the employee with the lowest 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 wage of the department, and the name of the lowest income employee
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. Information for employees who find the minimum wage in each department
SELECT * from emp where Sal in (select min (sal) to EMP Group by DEPTNO);
SELECT * from emp where Sal =any (select min (sal) to 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) salary in the subquery condition
SELECT * from emp where Sal >any (select min (sal) to 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) salary in the subquery condition
SELECT * from emp where Sal <any (select min (sal) to EMP Group by DEPTNO);
SELECT * from emp where Sal < (select max (min (sal)) from the EMP Group by DEPTNO);

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

94. In example 90, employee information that is less than the lowest (small) salary in the subquery condition
SELECT * from emp where Sal <all (select min (sal) to EMP Group by DEPTNO);
SELECT * from emp where Sal < (select Min. (SAL) from EMP Group by DEPTNO);

95. Find out 20 employee information without bonuses in the department
SELECT * from the EMP where (Sal, NVL (comm,-1)) in (select Sal, NVL (comm,-1) from emp where deptno = 20);
SELECT * from emp where deptno = ' and comm is null;

The Union operator returns all the distinct rows selected by the two query
Select Deptno from the EMP Union select Deptno from dept;

The two query selects all rows, including duplicate rows.
Select Deptno from EMP UNION ALL select Deptno from dept;

The Intersect operator only returns rows of two queries.
Select Deptno from emp intersect select Deptno from dept;

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