Oracle Database exercise example and answer sharing, oracle exercise questions

Oracle Database exercise example and answer sharing, oracle exercise questions

I. Create a table

1. create table empgj (empno number () primary key, ename varchar2 (10), 2.job varchar2 (9), mgr number (), hiredate date, sal number ), comm 3. number (7, 2), deptno number (2, 0); 4. -- 1 5. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 6. (7369, 'Smith ', 'cler', 7902, to_date ('2017-12-17', 'yyyy/mm/dd'), 7.800.00, '', 20); 8. commit; 9. -- 2 10. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 11. (7499, 'allen ', 'salesman', 7698, to_date ('1970-2-20 ', 'yyyy/mm/dd'), 12.1600.00, '1970. 00', 20); 13. commit; 14. -- 3 15. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 16. (7521, 'ward ', 'salesman', 7698, to_date ('2017-2-22 ', 'yyyy/mm/dd'), 17.1250.00, '2017. 00', 30); 18. commit; 19. -- 4 20. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 21. (7566, 'Jones ', 'manager', 7839, to_date ('1970-4-2', 'yyyy/mm/dd'), 22.2975.00, '', 20); 23. commit; 24. -- 5 25. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 26. (7654, 'martin ', 'salesman', 7698, to_date ('2017-9-28 ', 'yyyy/mm/dd'), 1981, '2017. 00', 30); 28. commit; 29. -- 6 30. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 31. (7698, 'bucke', 'manager', 7839, to_date ('1970-5-1 ', 'yyyy/mm/dd'), 1981, '', 30); 33. commit; 34. -- 7 35. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 36. (7782, 'clark', 'manager', 7839, to_date ('2017-6-9 ', 'yyyy/mm/dd'), 1981, '', 10); 38. commit; 39. -- 8 40. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 41. (7788, 'Scott ', 'analyst', 7566, to_date ('2017-4-19', 'yyyy/mm/dd'), 1987, '', 20); 43. commit; 44. -- 9 45. insert into empgj (empno, ename, job, hiredate, sal, comm, deptno) values 46. (7839, 'King', 'President ', to_date ('2017-11-17', 'yyyy/mm/dd'), 1981, '', 10); 48. commit; 49. -- 10 50. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 51. (7844, 'turner ', 'salesman', 7698, to_date ('2017-9-8 ', 'yyyy/mm/dd'), 1981, '0. 00', 30); 53. commit; 54. -- 11 55. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 56. (7876, 'adams', 'cler', 7788, to_date ('2017-5-23 ', 'yyyy/mm/dd'), 1987, '', 20); 58. commit; 59. -- 12 60. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 61. (7900, 'James ', 'cler', 7698, to_date ('2017-12-3', 'yyyy/mm/dd'), 1981, '', 30); 63. commit; 64. -- 13 65. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 66. (7902, 'Ford ', 'analyst', 7566, to_date ('2017-12-3', 'yyyy/mm/dd'), 1981, '', 20); 68. commit; 69. -- 14 70. insert into empgj (empno, ename, job, mgr, hiredate, sal, comm, deptno) values 71. (7934, 'miller ', 'cler', 7782, to_date ('2017-1-23', 'yyyy/mm/dd'), 1982, '', 10); 73. commit; 74. select empno as "employee ID", ename as "employee name", job as "position", mgr as "Employee manager ID", 75. hiredate as "Start Date", sal as "salary", comm as "performance", deptno as "ID of the employee's Department" 76. from empgj; 77. 78. alter session set nls_date_format = 'yyyy/mm/dd'; 79. 80. create table deptgj (deptno number (2, 0), dname varchar2 (14), loc varchar2 (13); 81. -- 1 82. insert into deptgj (deptno, dname, loc) values (10, 'accounting', 'New york '); 83. commit; 84. -- 2 85. insert into deptgj (deptno, dname, loc) values (20, 'Research ', 'Dallas'); 86. commit; 87. -- 3 88. insert into deptgj (deptno, dname, loc) values (30, 'sales', 'Chicago '); 89. commit; 90. -- 4 91. insert into deptgj (deptno, dname, loc) values (30, 'operations', 'boston '); 92. commit;

Ii. Table display

Iii. Exercise Questions and answers

1. Obtain the name of the person with the highest salary for each department

select e.ename,t.maxsal, t.deptno from empgj e join (select max(sal) as maxsal, deptno from empgj group by deptno) t  on t.deptno = e.deptno and e.sal= t.maxsal;

-->

Select e. ename, t. maxsal, d. dname from empgj e join (select max (sal) as maxsal, deptno from empgj group by deptno) t on t. deptno = e. deptno and e. sal = t. maxsal, deptgj dwhere e. deptno = d. deptno; (add the Department name)

<+> Name of the person with the highest salary in the sales department

 Select ename    from empgj   where sal = (select max(e.sal)                     from empgj e                     join deptgj d                       on e.deptno = d.deptno                     where d.dname = 'sales');

2. Who pays above the average salary of the department?

select e.ename, e.sal, t.avgsal  from empgj e  join (select avg(sal) as avgsal, deptno from empgj group by deptno) t   on t.deptno = e.deptno and e.sal > t.avgsal;

3. Do not use group functions (Max) to get the highest salary

select sal from (select sal from empgj order by sal desc) where rownum = 1;

4. Obtain the Department ID of the Department with the highest average salary

select deptno  from (select avg(sal), deptno          from empgj         group by deptno         order by avg(sal) desc) where rownum = 1;

(Ps: This method can only query the maximum average salary of only one department. If the average salary of both Departments is the same and the average salary is the highest, this query will be missed, hope you can solve this problem)

5. Obtain the name of the Department with the highest average salary

(1)select dname  from deptgj where deptno = (select deptno                   from (select avg(sal), deptno                           from empgj                          group by deptno                          order by avg(sal) desc)                  where rownum = 1);

(2)select t2.avgsal, t2.deptno, d.dname  from (select t.avgsal, t.deptno          from (select avg(sal) as avgsal, deptno                  from empgj                 group by deptno                 order by avgsal desc) t         where rownum = 1) t2  join deptgj d on t2.deptno = d.deptno;

6. Get the name of a manager with a higher salary than a common employee (employee code does not appear on the mgr field)

select ename  from empgj where sal <       (select max(sal)          from empgj         where empno not in               (select distinct mgr from empgj where mgr is not null))   and job = 'manager';

Ps: Pay attention to the usage of not in here. It cannot be followed by null data. Pay attention to the removal. not exists is generally used.

7. Top five employees with the highest salaries

select ename, sal  from (select ename, sal from empgj order by sal desc) where rownum < 6;

8. The sixth to tenth employees with the highest salaries

select ename, sal  from (select ename, sal, rownum rn          from (select ename, sal from empgj order by sal desc)) where rn >= 6   and rn <= 10;

Ps: some people may think, if I directly query rownum greater than 5 and less than 11, isn't the query statement simpler? For example: select ename, sal from (select ename, sal from empgj order by sal desc) where rownum> = 6 and rownum <= 10; however, no results are returned for the query, this is because rownum cannot be> (a value greater than 1),> = (a value greater than or equal to 1), OR = (a value greater than or equal to 1 ), otherwise, there is no result because: (1) rownum is a pseudo column and a rownum value is generated for each returned record after a returned result is required. (2) the rownum of the returned Result Records is sorted from 1, so the first record is always 1. In this way, when the first record is queried, The rownum of the record is 1, however, the condition requires rownum> 1, so it does not match. Continue to query the next record. Because there is no matching record before, the rownum of the next record is still 1, so the loop Then, no results are generated. According to the query statement I wrote, the rownum pseudo column is added to the new table to become an integer column, and a query within a certain range can be performed. For more information about rownum usage, see this article.

9. Obtain the final five employees

select ename  from (select ename, hiredate from empgj order by hiredate desc) where rownum <= 5;

10. List the names of all employees and direct superiors

(1)select a.ename, b.ename from empgj a left join empgj b on a.mgr = b.empno;

(2)select t.ename, e.ename  from (select ename, mgr from empgj) t  left join empgj e on e.empno = t.mgr;

The writing of Database SQL statements is flexible. Sometimes a condition may contain query statements written in multiple ways. In addition to restoring the disk in a timely manner, you should be trained frequently to be skilled in using SQL statements.