Simple SQL Part Hardening exercises

Simple query for some SQL exercises

--Select all employees in department 30 select * from emp where deptno = 30;--List all clerk (Clerk) name, number, and department number select E.ename, E.empno, E.deptno from EMP e where e.job = ' clerk ';--find out bonuses above salary for employees select * from EMP where comm > sal;--Find bonuses above salary of 60% employees select * from emp where com M > Sal * 0.6;--Find out the details of all the salesmen (manager) and department 20 in department 10 (clerk) SELECT * from emp e where E.deptno = ten and e.job = ' MANAGE R ' or E.deptno = E.job = ' Clerk '; select * from emp e where (E.deptno = ten and e.job = ' MANAGER ') or (e.dept No = E.job = ' clerk '); select * from emp e where E.deptno = ten and e.job = ' MANAGER ' UNION SELECT * from EMP e where E.deptno = E.job = ' Clerk ';--Find out all the managers in department 10, all the clerks in department 20 (clerk), not the manager or the salesman but their salary is equal to 2000 of the full staff of the specific information select *  From emp e where E.deptno = ten and e.job = ' MANAGER ' Unionselect * from emp e where E.deptno = + e.job = ' Clerk ' union SELECT * from emp e where e.sal > + e.job not in (' MANAGER ', ' Clerk ');--Find out the different jobs of the employees who receive the bonus select distinct E.job fro M EMP e;--find no bonus or charge less than 10 bonus0 Employees SELECT * from emp e where e.comm is null or E.comm < 100;--find all employees employed on the 3rd day of each month SELECT * from emp e where e.hiredate be Tween Last_day (HireDate)-3 and Last_day (HireDate);--find employees employed before 30 SELECT * from EMP e where (sysdate-e.hiredate)/365 ; 30;--displays the names of all employees in the first letter select Initcap (ename) from emp;--displays the employee name exactly 5 characters select * from emp where length (ename) = 5;--display without Employee name for "R" SELECT * from emp where ename '%k% ';--Displays the first three characters of all employee names Select substr (ename, 0, 3) from emp;--displays the names of all employees and uses ' A ' Replace all ' a ' select replace (ename, ' a ', ' a ') from emp;--displays the employee name and employment date for 30 years of service SELECT * from EMP where (sysdate-hiredate)/365 > 30;--displays employee details, sorted by name from big to small select * from emp ORDER by ename desc;--displays the employee's name and date of employment. According to its service life, the oldest employee is ranked first select Ename, HireDate from emp order by HireDate asc;--Show all employees ' names, jobs and salaries, sorted in descending order of work, sort by salary ascending select ename, Job, Sal from emp order by job desc, Sal Asc;select ename, Job, Sal from emp order by 2 desc, 3;--Show all employee names, increase company year And month, sorted by the month on which they were hired. If the month is the same. The earliest year is ranked at the front select ename, To_number (To_cHar (hiredate, ' yyyy ')) year, To_number (To_char (hiredate, ' mm ')) is the daily salary of the employee from the EMP order by 3 DESC, 2 asc;--showing one months for a 30-day situation. Ignore remainder select round (SAL/30) daily from emp;--find all employees employed in (no matter what year) February select * from emp where To_number (To_char (hiredate, ' mm ')) = 2; --For each employee, show the number of days that it increased the company select Ename, Round (sysdate-hiredate) day from emp;--display names in any location including all employee names in "A" SELECT * from EMP where Upper (ename) like '%a% ';--show the service life of all employees by month and day select Ename, HireDate, Trunc (Months_between (sysdate, HireDate)/12) year, T  Runc (mod (Months_between (sysdate, HireDate),) months, trunc (Sysdate-add_months (Hiredate,months_between, hiredate)) Dayfrom EMP;

Simple SQL Part Hardening exercises