Common SQL query statements

1. 1. Find employees with a Commission higher than 60% of their salaries.
2. Select * From e where comm> Sal * 0.6;
4. 2. Find out the details of all clerks in department 10, all managers and department 20.
5. Select * from EMP
6. Where deptno = 10 and job = 'manager' or deptno = 20 and job = 'cler ';
8. 3. Find out the details of all managers in department 10, all clerks in department 20, and all employees who are neither a manager nor clerk but whose salaries are greater than or equal to 2000.
9. Select * from EMP
10. Where deptno = 10 and job = 'manager' or deptno = 20 and job = 'wheel' or job not in ('manager', 'wheel') and Sal> = 2000;
11. Select * from EMP
12. Where deptno = 10 and job = 'manager' or deptno = 20 and job = 'wheel' or (job <> 'manager' and Sal> = 2000 );
14. 4. Find out the different jobs of the employees who charge the commission.
15. Select distinct job from EMP where comm is not null;
17. 5. Find out the employees who do not receive a commission or receive a Commission less than 300.
18. Select * from EMP where comm is null or comm <300;
20. 6. Find all employees employed on the last day of each month.
21. Select * from EMP where hiredate = last_day (hiredate );
22. -- Find all employees who are last hired each month
23. Select * from EMP where hiredate in
24. (Select maxh from
25. (Select max (hiredate) as maxh, extract (month from hiredate)
26. From EMP
27. Group by extract (month from hiredate )));
29. 7. Find the employees employed later than 26 years ago.
30. Select * from EMP where months_between (sysdate, hiredate) <= 26*12;
32. 8. The names of all employees whose first letters are capitalized are displayed.
33. Select * from EMP where ename = initcap (ename );
35. 9. The employee name is exactly five characters long.
36. Select * from EMP where length (ename) = 5;
38. 10. The employee name without "R" is displayed.
39. Select * from EMP where instr (ename, 'R') = 0;
40. Select * from EMP where ename not like '% R % ';
42. 11 display the first three characters of the names of all employees.
43. Select substr (ename, 1, 3) as en3 from EMP;
45. 12. display the names of all employees. Replace "a" with "".
46. Select Replace (ename, 'A', 'A') from EMP;
48. 13 display the names of all employees and the dates of the service life of 10 years.
49. Select ename, hiredate, add_months (hiredate, 120) as h_10y from EMP;
51. 14. Detailed information about employees is displayed, sorted by name.
52. Select * from EMP order by ename;
54. 15. display the employee name. The oldest employee is placed at the top of the list based on the service life.
55. Select ename, hiredate from EMP order by hiredate;
57. 16 displays the names, jobs, and salaries of all employees. Jobs are sorted in descending order of work, and jobs are sorted by salary.
58. Select ename, job, Sal
59. From EMP
60. Order by job DESC, Sal;
62. 17. The names of all employees and the year and month of joining the company are displayed, sorted by the month where the employee's employment day is located, and the items in the earliest year are listed at the top.
63. Select ename, to_char (hiredate, 'yyyy-mm') as y_m
64. From EMP
65. Order by to_char (hiredate, 'mm'), to_char (hiredate, 'yyyy ');
67. 18 shows the daily salary of all employees in the case of 30 days in a month, rounded up.
68. Select round (SAL/30) as Sal from EMP;
70. 19. Find all employees who were employed in April February (in any year.
71. Select * from EMP where to_char (hiredate, 'mm') = '02 ';
73. 20. For each employee, the number of days of joining the company is displayed.
74. Select Ceil (SYSDATE-HIREDATE) as Dy from EMP;
76. 21 shows any location of the Name field, including the names of all employees of ".
77. Select ename from EMP where ename like '% A % ';
78. Select ename from EMP where instr (ename, 'A')> 0;
80. 22. The service life of all employees is displayed on a yearly, monthly, and daily basis.
81. Select hiredate,
82. Floor (months_between (sysdate, hiredate)/12) as y,
83. MoD (floor (months_between (sysdate, hiredate), 12) as m,
84. MoD (floor (SYSDATE-HIREDATE), 30) as d
85. From EMP;
87. 23 list all departments with at least one employee.
88. Select * from Dept where deptno in (select distinct deptno from EMP );
90. 24 list all employees with higher salaries than Smith.
91. Select * from EMP where SAL> (select Sal from EMP where ename = 'Smith ');
93. 25. List the names of all employees and their superiors.
94. Select ygb. ename as empnm, sjb. ename as mgrnm
95. From EMP ygb, EMP sjb
96. Where ygb. Mgr = sjb. empno;
98. 26 List all employees whose employment date is earlier than their direct superiors.
99. Select * from EMP ygb where hiredate <(select hiredate from EMP sjb where sjb. empno = ygb. Mgr );
101. 27 List departments and employees in these departments, and list departments without employees.
102. /*
103. Select * from Dept where deptno in (select distinct deptno from EMP)
104. Union
105. Select * from Dept where deptno not in (select distinct deptno from EMP)
106. */
107. Select dname, ename
108. From dept a left join EMP B
109. On a. deptno = B. deptno;
111. 28. List the names of all "Clerk" (Clerks) and their department names.
112. Select ename, dname
113. From dept a, EMP B
114. Where a. deptno = B. deptno and job = 'cler ';
116. 29. List the minimum salaries of various types and make the minimum salary more than 1500.
117. Select job, min (SAL)
118. From EMP
119. Group by job
120. Having min (SAL)> 1500;
122. 30. List the names of employees engaged in sales. Assume that they do not know the Department Number of the Sales Department.
123. Select ename from EMP where deptno = (select deptno from Dept where dname = 'sales ');
125. 31 List all employees whose salaries are higher than the company average.
126. Select * from EMP where SAL> (select AVG (SAL) from EMP );
128. 32. list all employees engaged in the same job as Scott.
129. Select * from EMP where job = (select job from EMP where ename = 'Scott ') and ename <> 'Scott ';
131. 33. List the names and salaries of all employees whose salaries are equal to those of Department 30.
132. Select * from EMP where Sal in (select Sal from EMP where deptno = 30 );
134. 34 list the names and salaries of all employees whose salaries are higher than those of employees who work in department 30.
135. Select * from EMP where SAL> All (select Sal from EMP where deptno = 30 );
137. 35 list the managers and other information of employees working in each department.
138. Select a. *, B .*
139. From dept a, EMP B
140. Where a. deptno = B. deptno and job = 'manager ';
141. /*
142. Select a. *, B .*
143. From (select * from EMP where job <> 'manager') A, (select * from EMP where job = 'manager') B
144. Where a. deptno = B. deptno and B. Job = 'manager'
145. Order by A. deptno;
146. */
148. 36 List the names, department names, and salaries of all employees.
149. Select ename, dname, Sal
150. From dept a, EMP B
151. Where a. deptno = B. deptno;
153. 37 list different groups of employees engaged in the same job but belonging to different departments.
154. Select * from EMP order by job, deptno;
156. 38 list the details of all departments allocated with the number of employees, even if the number of employees is 0.
157. Select * from Dept where deptno in (select distinct deptno from EMP)
158. Union
159. Select * from Dept where deptno not in (select distinct deptno from EMP)
161. 39 list the minimum wage for various types of jobs.
162. Select job, min (SAL)
163. From EMP
164. Group by job;
166. 40 list the minimum salaries of managers in each department.
167. Select min (SAL) from EMP where job = 'manager ';
169. 41 list the annual salary of all employees sorted by annual salary.
170. Select Sal * 12 as y_sal from EMP order by Sal * 12;
172. 42 list the employees whose salaries are at the fourth place.
173. Select * from
174. (Select a. *, row_number () over (order by Sal DESC) as rn from emp)
175. Where Rn = 4;
177. 43. Search for the first five records in the EMP table
178. Select * from EMP where rownum <= 5;
180. 44 search for more than 10 records in the EMP table
181. Select * from (select a. *, rownum as rn from EMP a) Where rn> 10;
183. 45. Search for employees with a salary of 5th in the EMP table
184. Select * from
185. (Select a. *, row_number () over (order by Sal DESC) as rn from emp)
186. Where Rn = 5;
188. 46. Find employees with a salary of 3rd in the EMP table Department 30.
189. Select * from
190. (Select a. *, row_number () over (partition by deptno order by Sal DESC) as rn from emp)
191. Where Rn = 5 and deptno = 30;
193. 47 search for employees with a salary of 3rd per department in the EMP table
194. Select * from
195. (Select a. *, row_number () over (partition by deptno order by Sal DESC) as rn from emp)
196. Where Rn = 3;
198. 48. Calculate the total salaries of each department.
199. Select deptno, sum (SAL) as sumsal
200. From EMP
201. Group by deptno;
203. -- Details of employees and managers of each department: number, name, salary, employment date, Department number
204. Select a. empno as Eno, A. ename as ENM, A. Job as ejob, A. Sal as esal, A. hiredate as edate, A. deptno as edept,
205. B. empno as MnO, B. ename as mnm, B. Job as mjob, B. Sal as msal, B. hiredate as mdate, B. deptno as mdept
206. From (select * from EMP where job <> 'manager' and job <> 'President ') A, (select * from EMP where job = 'manager') B
207. Where a. deptno = B. deptno;