SQL Basic Query statement

SELECT * from EMP where deptno=30;
--1 selects all employees in department 30.

Select Ename,job,empno,deptno from emp where job= ' clerk ';
--2 lists the name, employee number, and department number of all clerks (clerk).

Select Ename, Sal,comm from EMP where comm>sal;
--3 find employees with bonuses above their salaries.

Select Ename, Sal,comm from EMP where comm>sal*0.6;
--4 find employees with bonuses above 60% of their salary.
Select Ename, Job, deptno from EMP where deptno=10 and job= ' MANAGER ';
--5 Find all Managers (manager) in Department 10

select* from EMP where (deptno=10 and job= ' manager ') or (job!= ' manager ' and job!= ' clerk ' and sal>=2000);
--6 Find out the details of all the managers in department 10, and all the employees in all departments who are not managers but are not clerks but have a salary greater than or equal to 2000.

SELECT DISTINCT job from EMP where comm>0 and comm are NOT null;
--7 find out the different jobs of employees with bonuses.

SELECT * FROM EMP where comm is null or comm<100;
--8 find employees who have no bonuses or bonuses below 100.

SELECT * from emp where Last_day (hiredate) -2=hiredate;
--9 Find all employees employed on the 3rd day of the month.

SELECT * from emp where hiredate<=add_months (SYSDATE,-30*12);
--10 to find employees who were employed before 30.

Select Initcap (ename) from EMP;
--11 displays the names of all employees in an uppercase letter.

SELECT * from emp where length (ename) = 5;
--12 displays the name of an employee who is exactly 5 characters.

SELECT * from emp where ename '%r% ';
--13 displays the names of employees without ' R '.

Select substr (ename,0,3) from EMP;
--14 Displays the first three characters of all employee names.

Select Replace (ename, ' A ', ' L ') from EMP;
--15 displays the names of all employees, substituting ' L ' for all ' A '

Select Ename,hiredate from emp where (To_char (sysdate, ' yyyy ')-to_char (hiredate, ' yyyy ')) >=30;--select ename, HireDate from EMP where hiredate= (); --16 Displays the name and date of employment of employees over 30 years of service.
SELECT * from emp order by ename;
--17 displays details of the employee, sorted by name.

Select Ename,hiredate from emp order by hiredate ASC;
--18 shows the employee's name and date of employment, according to their service life, the oldest employees in the front.

Select Ename,job,sal from emp order by job desc,sal;
--19 displays the names, jobs, and salaries of all employees, sorted by work in descending order, and by salary if the work is the same.

Select Ename,hiredate from emp order by hiredate,hiredate ASC;
--20 displays the names of all employees, the year and month in which the company was joined, sorted by the month of the hire date, and, if the month is the same, the employee of the earliest year is ranked first.

Select Ename,trunc (sal/30,0) from EMP;
--21 shows the daily salary of all employees in one months for 30 days, ignoring the remainder.

SELECT * from emp where TO_CHAR (hiredate, ' fmmm ') = ' 2 ';
--22 Find all employees employed in February (in any year).

Select Ename,trunc (sysdate-hiredate) from EMP;
--23 for each employee, show the number of days that they joined the company.

Select ename from emp where ename like '%a% ';
--24 displays the names of all employees that contain ' A ' at any location in the Name field.

SQL Basic Query statement