Complete Oracle exercises

Full Oracle exercise PS: All Tables come from the tables of SCOTT users in Oracle, and should be known to beginners. I deleted the SMITH of the emp table without rollback, so the results may be slightly different.

1. SELECT * FROM emp WHERE deptno = 30; 2. SELECT all employees in department 30. list the names, numbers, and department numbers of all clerks (clers): SELECT ename, empno, deptno FROM emp where upper (job) = 'cler'; 3. list employees with Commission (comm) higher than salary (sal): SELECT * FROM emp WHERE comm> sal; 4. SELECT * FROM emp WHERE comm> (sal * 0.6) for employees whose commission (comm) is higher than the salary percentage of 60; 5. find the details of all managers in department 10 AND all clerks in department 20, SELECT * FROM emp WHERE (deptno = 10 AND job = 'manager ') OR (deptno = 20 AND job = 'cler'); 6. team 10 Information about all managers and all clerks in department 20, who are neither managers nor clerks but whose salaries are greater than or equal to 2000: SELECT * FROM emp WHERE (deptno = 10 AND job = 'manager') OR (deptno = 20 AND job = 'cler') OR (job not in ('manager ', 'cler') AND sal> = 2000); 7. find out the different jobs of employees who charge commission (comm): [DISTINCT-> eliminate duplicate keywords] select distinct job FROM emp WHERE comm is not null; 8. find out the employees who do not receive a commission OR the Commission IS less than 100: SELECT * FROM emp WHERE comm is null or comm <100; 9. find all employees employed on the last three days of each month: · use the LAST_DAY () function SELECT * F ROM emp WHERE (LAST_DAY (hiredate)-2) = hiredate; 10. find the employees employed earlier than 12 years ago: · use MONTHS_BETWEEN (today, employment date) SELECT * FROM emp WHERE MONTHS_BETWEEN (SYSDATE, hiredate)/12> 12; 11. select initcap (ename) FROM emp; 12. SELECT ename FROM emp where length (ename) = 5; 13. show employee names without "R": SELECT ename FROM emp WHERE ename not like '% R %'; 14. display the first three characters of all employee names: select substr (ename,) FROM emp; 15. show the names of all employees REPLACE all "A" with "x"; select replace (ename, 'A', 'x') FROM emp; 16. display the names and dates of employment of employees who have served for ten years: SELECT ename, hiredate FROM emp WHERE MONTHS_BETWEEN (sysdate, hiredate)/12> 10; 17. displays employee details, sorted BY name: SELECT * FROM emp order by ename; 18. display the employee's name and employment date, and sort the oldest employee in front of SELECT ename, hiredate FROM emp order by hiredate; 19 Based on the service life. displays the names, jobs, and salaries of all employees in descending ORDER of jobs. Jobs are sorted BY salaries if jobs are the same: SELECT ename, job, sal FROM emp order by job DESC, sal; 2 0. display the names of all employees, including the year and month of joining the company, sorted by the year where the employment date is located. If the year is the same, the employees of the earliest month are listed at the top: · use TO_CHAR () function SELECT ename, TO_CHAR (hiredate, 'yyyy') year, TO_CHAR (hiredate, 'mm') mon FROM emp order by year, mon; 21. displays the daily salary of all employees for 30 days in a month, and ignores the remainder: ROUND () Rounding SELECT ename, ROUND (sal/30) daily salary FROM emp; 22. find all employees who were hired in (any year) in February: SELECT * FROM emp WHERE TO_CHAR (hiredate, 'mm') = 2; 23. for each employee, the number of days for joining the company is displayed: select round (sysdate-hiredate) FROM emp; 24. display any bit of the Name field Set the names of all employees containing "A": SELECT ename FROM emp WHERE ename LIKE '% A %'; 25. display the service life of all employees in the form of year and month: Year: Find the total month/12-> Generate decimal places, and cannot be rounded to month: Take the remaining SELECT ename for 12, TRUNC (MONTHS_BETWEEN (sysdate, hiredate)/12) year, TRUNC (MOD (MONTHS_BETWEEN (sysdate, hiredate), 12) monFROM emp;/* ------------- complex query, subquery, multi-table join ------------ */26. lists information about all departments and departments with at least three employees. [!] SELECT d. *, ed. count FROM dept d, (SELECT deptno, COUNT (empno) count FROM emp group by deptno having count (empno)> 3) edWHERE d. deptno = ed. deptno; 27. list all employees with higher salaries than allen select sal FROM emp WHERE ename = 'allen '; // subquery SELECT * FROM emp WHERE sal> (SELECT sal FROM emp WHERE ename = 'allen '); 28. list the names of all employees and their superiors: SELECT e. ename employee, m. ename superior FROM emp e, emp m WHERE e. mgr = m. empno (+); because KING does not have a superior, adding a (+) number indicates a left join 29. column Number, name, and department name of all employees whose employment date is earlier than the direct superior's SELECT e. ename, e. empno, d. dname FROM emp e, emp m, dept d WHERE e. mgr = m. empno AND e. deptno = d. deptno AND e. hiredate <m. hiredate; 30. list department names and information about employees in these departments, and list departments without employees. · SELECT d should be displayed even if there are no employees. deptno, d. dname, e. empno, e. enameFROM dept d, emp eWHERE d. deptno = e. deptno (+); 31. list the name and department name of "CLERK". department count: · find the name and department Number of all clerks: SELECT e. ename, d. dname, ed. couFROM emp e, dept d, (SELECT deptno, COUNT (empno) cou FROM emp group by deptno) ed WHERE e. deptno = d. deptno AND job = 'cler' AND ed. deptno = d. deptno; 32. list jobs with a minimum salary of more than 1500 and the total number of employees engaged in the job. By group, the minimum wage is greater than 1500 SELECT job, MIN (sal) FROM empG Roup by job having min (sal)> 1500; · according to SELECT e. job, COUNT (empno) FROM emp eWHERE job IN (SELECT job FROM emp group by job having min (sal)> 1500) group by e. job; 33. name of the employee who works in the department SALES department. If you do not know the Department ID of the SALES department, query the Department ID (subquery) of the SALES department in the DEPT table SELECT deptnoFROM deptWHERE dname = 'sales '; · The preceding is the subquery SELECT ename FROM empWHERE deptno = (SELECT deptnoFROM deptWHERE dname = 'sales'); 34. list the salaries of all employees whose salaries are higher than the average salaries, their departments, superiors, and the company's salary levels. · Average Salary: select avg (sal) FROM emp; · SELECT * FROM emp WHERE sal> (select avg (sal) FROM emp) list all employees whose salaries are higher than the average salary ); · associate with the department table to query the information of the Department (note that KING has no upper-level attention and right join) SELECT e. *, d. dname, d. loc, m. ename FROM emp e, dept d, emp mWHERE e. mgr = m. empno (+) ANDe. deptno = d. deptno ANDe. sal> (select avg (sal) FROM emp); · calculate the employee's wage level SELECT e. *, d. dname, d. loc, m. ename, s. gradeFROM emp e, dept d, emp m, salgrade sWHERE e. mgr = m. empno (+) ANDe. deptno = d. Deptno ANDe. sal> (select avg (sal) FROM emp) AND e. sal BETWEEN s. losal AND s. hisal; 35. list all employees and department names of the same job as SCOTT: SELECT job FROM emp WHERE ename = 'Scott '; · subquery SELECT e. *, d. dname FROM emp e, dept dWHERE e. job = (SELECT job FROM emp WHERE ename = 'Scott ') AND e. deptno = d. deptno; · SCOTT exists in the above results. SELECT e should be removed. *, d. dname FROM emp e, dept dWHERE e. job = (SELECT job FROM emp WHERE ename = 'Scott ') AND e. deptno = d. d Eptno AND e. ename! = 'Scott '; 36. list the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30. SELECT sal FROM emp WHERE deptno = 30. subquery SELECT ename, sal FROM empWHERE sal IN (SELECT sal FROM emp WHERE deptno = 30) AND deptno! = 30; 37. name and salary of employees whose salaries are higher than the salaries of ALL employees who work in the Department 30. Change the Department name in the previous procedure> ALL, greater than the maximum SELECT ename, sal FROM empWHERE sal> ALL (SELECT sal FROM emp WHERE deptno = 30) AND deptno! = 30; · associate with dept and obtain the Department name SELECT e. ename, e. sal, d. dname FROM emp e, dept dWHERE e. sal> ALL (SELECT sal FROM emp WHERE deptno = 30) AND e. deptno! = 30AND e. deptno = d. deptno; 38. list the number of employees, average salary, and average service life of each department. Number of employees working in each department: SELECT d. dname, COUNT (e. empno) FROM emp e, dept dWHERE e. deptno = d. deptnoGROUP BY d. dname; · find the average salary and service life SELECT d. dname, COUNT (e. empno), AVG (sal), AVG (MONTHS_BETWEEN (sysdate, hiredate)/12) year FROM emp e, dept dWHERE e. deptno = d. deptnoGROUP BY d. dname; 39. list the names, departments, and salaries of all employees. SELECT e. ename, d. dname, e. sal FROM emp e, dept d WHERE e. deptno = d. deptno; 40. list the trusted information and number of people in all departments · List the number of people in all departments SELECT deptno dno, COUNT (empno) cou FROM empGROUP BY deptno; · use the table above as a temporary table: [because there are no employees in Department 40, 0 should be used for expression] SELECT d. *, NVL (ed. cou, 0) FROM dept d, (SELECT deptno dno, COUNT (empno) cou FROM empGROUP BY deptno) edWHERE d. deptno = ed. dno (+); 41. List the minimum wage for various jobs and the names of employees engaged in the job: select min (sal) m FROM emp group by job; · subquery SELECT e. ename FROM emp eWHERE e. sal IN (select min (sal) m FROM emp group by job); 42. List the minimum salaries of managers IN each department: · obtain the salaries of managers IN each department, SELECT deptno, MIN (sal) FROM emp WHERE job = 'manager' group by deptno; 43. List the annual salary of all employees in descending order of their annual salary: · pay attention to the bonus. The NVL function must be used to process the SELECT ename (sal + NVL (comm, 0) * 12 income FROM emp order by income44. The Superior Supervisor of an employee can be found, and require the salaries of these supervisors to exceed 3000 select distinct m. * FROM emp e, emp mWHERE e. mgr = m. empno AND m. sal> 3000; 45. Obtain the total salary of the Department employees with the 's' character in the department name. For the number of Department employees, query the Department name in the department table and use fuzzy search, to determine the Department number SELECT deptno FROM dept WHERE dname LIKE '% S %'; · Based on the subquery above, find the total salary and department count select sum (sal ), COUNT (empno) FROM emp eWHERE e. deptno IN (SELECT deptno FROM dept WHERE dname LIKE '% S %') group by deptno; 46. a salary increase of 10% is given to those who have been working for more than 10 years; UPDATE emp SET sal = sal * 1.1 WHERE MONTHS_BETWEEN (sysdate, hiredate)/12> 10;