Common Oracle operation code analysis

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.