Oracle Learning Article Three: SQL query

SELECT * from EMP;

--1. Identify 30 of employees in the department
SELECT * from emp where deptno = 30;

--2. List the names, changes, and department numbers of all clerks (clerk)
SELECT * from emp where job = Upper (' clerk ');

--3. Identify employees with higher commissions than salaries.
SELECT * from emp where comm > sal;

--4. Find employees with commissions above 60% of salary.
SELECT *
From EMP
WHERE comm > Sal * 0.6

--5. Find out the details of all the manager (manager) and department 20 Clerks (Clerk) in department 10
SELECT *
From EMP
WHERE (Deptno = ten and job = Upper (' manager '))
or (Deptno = job = Upper (' clerk '));

--6. Find out the details of all the managers in department 10 (manager) and all Clerks (clerk) in department 20, neither the manager nor the clerk, but the salary is greater than or equal to 2000 of all employee details
SELECT *
From EMP
WHERE (Deptno = ten and job = Upper (' manager '))
or (Deptno = job = Upper (' clerk '))
or (Job not in (' MANAGER ', ' Clerk ') and Sal >= 2000);

--7. Find out the different jobs of employees who receive commissions
SELECT DISTINCT job from EMP where comm are NOT null;

--8. Find employees who do not charge commissions or receive commissions less than 100.
SELECT *
From EMP
where comm is null
or comm < 100;

--9. Find employees who are employed on the 3rd day of each month
Select Last_day (HireDate)
From EMP
SELECT * from emp where Last_day (hiredate)-2 = hiredate;

--10. Identify employees who were employed before 12.
SELECT * from emp where Months_between (sysdate, HireDate)/> 12;

--11. Display all employee names in uppercase letters
Select Initcap (ename) from EMP;

--12. Displays the employee name that is exactly 5 characters.
SELECT * from emp where length (ename) = 5;

--13. Displays the name of the employee without "R".
SELECT * from emp where ename '%r% ';

--14. Displays the first three characters of all employees with a name.
Select substr (ename, 0, 3) from EMP;

--15. Show all employees, replace "a" with "a"
Select Replace (ename, ' a ', ' a ') from EMP;

--16. Name and date of employment of employees over 10 years
Select Ename, HireDate
From EMP
where Months_between (Sysdate, HireDate)/> 10;

--17. Show employee details, sort by name
SELECT * from emp order by ename;

--18. Show employee name and date of employment, according to their service life, the oldest employee card at the front
Select Ename, HireDate from emp order by HireDate;

--19. Show the name/work and salary of all employees, sorted in descending order of work, sorted by salary if co-work is the same
Select ename, Job, Sal from emp order by job Desc, Sal;

--20. Displays the names of all employees/the year and month in which the company was joined, arranges the monthly order of the day of employment, and, if the month is the same, arranges the first year's employees.
Select Ename, To_char (hiredate, ' yyyy ') y, To_char (hiredate, ' mm ') m
From EMP
Order by M, y;

--21. Shown in one months for 30 days, all employees are on a daily salary, ignoring the remainder.
Select Ename, Round (SAL/30) from EMP;

--22. Identify employees employed in February (in any year).
SELECT * from emp where TO_CHAR (hiredate, ' mm ') = ' 02 ';

--23. The number of days each employee is shown to join the company.
Select Ename, Trunc (sysdate-hiredate) from EMP;

--24. Displays the names of all employees with "A" anywhere in the Name field.
Select ename from emp where ename like '%a% ';

--25. Show the service life of all employees by month and day (approximate)
Select Ename,
HireDate,
Trunc (Months_between (sysdate, HireDate)/y,
Trunc (mod (Months_between (sysdate, HireDate),) m,
Trunc (mod (sysdate-hiredate,)) d
from EMP;

Oracle Learning Article Three: SQL query