Oracle DatabaseMediumCodeIt is the foundation and the most basic for database operation. The Oracle Database Code involved in the following article is very valuable for beginners, and I hope it will be helpful to everyone.
Who has the highest salary in the department?
The following code snippet: select ename, sal from emp join (select max (sal) max_sal, deptno from emp group by deptno) t on (emp. sal = t. max_sal and emp. deptno = t. deptno );-
Calculate the average salary of a department
The following code snippet: select deptno, avg_sal, grade from (select deptno, avg (sal) avg_sal from emp group by deptno) t join salgrade s on (t. avg_sal between s. losal and s. hisal );
Average Department salary
The following code snippet: select deptno, avg (grade) from (select deptno, ename, grade from emp join salgrade s on (emp. sal between s. losal and s. hisal) t group by deptno;
Who are managers of employees?
The following code snippet: select ename from emp where empno in (select distinct mgr from emp );
Calculate the highest salary without using a group Function
The following code snippet: select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal ))
Use the group function to calculate the highest salary.
The following is a code snippet:
Select max (sal) from emp;
Department ID of the Department with the highest average salary
The following code snippet: select deptno, avg_sal from (select avg (sal) avg_sal, deptno from emp group by deptno) t where avg_sal = (select max (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno) t );
Writing Group function nesting
The following code snippet: select deptno, avg_sal from (select avg (sal) avg_sal, deptno from emp group by deptno) t where avg_sal = (select max (avg (sal )) from emp group by deptno );
Name of the Department with the highest average salary
The following code snippet: select dname from dept where deptno = (select deptno from (select avg (sal) avg_sal, deptno from emp group by deptno) t where avg_sal = (select max (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno) t ));
Department Name of the Department with the lowest average salary rating
The following code snippet: select avg (sal) avg_sal, deptno from emp group by deptno
Average Department salary
The following code snippet: select min (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno)
Minimum Average Wage
The following code snippet: select avg_sal, deptno from (select avg (sal) avg_sal, deptno from emp group by deptno) t where avg_sal = (select min (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno ))
Minimum average wage and department ID
The following code snippet: select t. avg_sal, t. deptno, s. grade from (select avg (sal) avg_sal, deptno from emp group by deptno) t join salgrade s on (t. avg_sal between s. losal and s. hisal) where avg_sal = (select min (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno ))
Minimum and department numbers and wage levels of average wages
The following code snippet: select d. dname, t. avg_sal, t. deptno, s. grade from (select avg (sal) avg_sal, deptno from emp group by deptno) t join salgrade s on (t. avg_sal between s. losal and s. hisal) join dept d on (t. deptno = d. deptno) where avg_sal = (select min (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno ))
Minimum and department ID of the average wage and the wage level and department name
Another
The following code snippet: select t1.deptno, t1.avg _ sal, grade, d. dname from (select deptno, avg_sal, grade from (select deptno, avg (sal) avg_sal from emp group by deptno) t join salgrade s on (t. avg_sal between s. losal and s. hisal) t1 join dept d on (t1.deptno = d. deptno) where grade = (select min (grade) from (select deptno, avg_sal, grade from (select deptno, avg (sal) avg_sal from emp group by deptno) t join salgrade s on (t. avg_sal between s. losal and s. hisal )));
Create a view or table.
The following code snippet: conn sys/sys as sysdba;
Connected.
The following code snippet: grant create table, create view to scott;
This article introduces you so much. This is only a small part of the article. There are many other articles not introduced here, and we will continue to introduce you more knowledge in the future, hope to help you.