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 salesman (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 (clerk) in department 10 and all managers (manager) and department 20 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 salesmen in department 20 (clerk), who are neither managers nor salesmen but whose salary is greater than or equal to 2000 of all employees. 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;--Show all employee names in uppercase 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 ' replaces all ' a ' select replace (ename, ' a ', ' a ') from emp;--displays the employee name and date of employment for the full 30 years of service SELECT * from EMP where (sysdate-hiredate)/365 > 30;--display 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 their service life, ranked the oldest employee at the front select ename, hired Ate from emp order by HireDate asc;--shows all employees ' names, jobs and salaries, sorted in descending order of work, sort by salary ascending if work is same select ename, job, Sal from emp order by Job de  SC, Sal Asc;select ename, Job, Sal from emp order by 2 desc, 3;--Displays the names of all employees, joins the company's year and month, sorts by the month of the employment date, and if the month is the same, the earliest year is ranked first select ename, To_number (To_cHar (hiredate, ' yyyy ')) year, To_number (To_char (hiredate, ' mm ')) is from EMP ORDER by 3 desc, 2 asc;--displays one months for 30 days of the employee's daily salary, ignoring the remainder Number of select round (SAL/30) daily from emp;--find all employees employed in (any year) February select * from emp where To_number (To_char (hiredate, ' mm ')) = 2;--for each Employees, showing the number of days they joined the company select Ename, Round (sysdate-hiredate) Day from emp;--all employee names in the name that contain "A" SELECT * from EMP where UPPER ( ename) like '%a% ';--Displays the service life of all employees by month and day select Ename, HireDate, Trunc (Months_between (sysdate, HireDate)/12) year, Trunc (m OD (Months_between (sysdate, HireDate),) months, trunc (Sysdate-add_months (Hiredate,months_between (sysdate), hired ATE)) Dayfrom EMP;

Simple SQL Part Hardening exercises