Oracle Application (interview question example): Important, oracle example

Oracle Application (interview question example): Important, oracle example

(1) ROW_NUMBER () OVER (partition by col1 order by col2) indicates grouping by col1 and sorting by col2 within the group, the value calculated by this function indicates the sequential numbers after sorting in each group (the group is continuous and unique ).

1. The employees in the department have the highest salaries.
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)


2. Average Department salary level
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)


3. Average salary level of the Department
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


4. Managers among employees
Select ename from emp where empno in (select distinct mgr from emp)


5. You are not allowed to use group functions to calculate the highest salary value.
Select distinct sal from emp
Where sal not in
(Select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal ))


6. Find the Department Number of the Department with the highest average salary
Select deptno, avg_sal from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
Where avg_sal =
(Select max (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno ))


Method 2: nesting of Aggregate functions


Select deptno, avg_sal from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
Where avg_sal =
(Select max (avg (sal) from emp group by deptno)


7. Name of the Department with the highest average salary
Select dname from dept where deptno =
(Select deptno from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
Where avg_sal =
(Select max (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno)
)
)
8. Name of the Department with the lowest average salary rating
Select dname, t1.deptno, grade, avg_sal from
(
Select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
Join salgrade s on (t. avg_sal between s. losal and hisal)
) T1
Join dept on (t1.deptno = dept. deptno)
Where t1.grade =
(
Select min (grade) from
(
Select deptno, grade, avg_sal 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)
)
)


You can create a view to replace the simplified sub-query statement with repeated SQL statements.


9. Ask the Department name with the lowest average salary among department managers




10. Ask the name of a manager who is higher than the highest salary of an ordinary employee
Select ename from emp
Where empno in (select distinct mgr from emp where mgr is not null)
And
Sal>
(
Select max (sal) from emp where empno not in
(Select distinct mgr from emp where mgr is not null)
)
11. Top 5 employees with the highest salaries


12. 6th to 10th employees with the highest salary


Comparison Efficiency
Select * from emp where deptno = 10 and ename like '% A % ';
Select * from emp where ename like '% A %' and deptno = 10;
The first execution is more efficient. Exact match is performed first. If the department number is not equal to 10, it will not be viewed directly.
However, when it is really necessary to put it into oracle for execution, oracle may have optimized this statement internally, and it is difficult to distinguish the statement for higher efficiency.




1. Find out the names of all students who have never chosen teacher Dawn.
S (sno, sname) Student ID name
C (cno, cname, cteacher) course No. Instructor
SC (sno, cno, sccgrade) Student No. Score


Select sname from s join SC on (s. sno = SC. sno) join c (c. cno = SC. cno) where c. cteacher <> 'liming ';


2. List the names and average scores of two or more failed students


Select sname where sno in (select sno from SC where scgrade <60 group by sno having count (*)> = 2 );


3. Names of all students who have learned course 1 and course 2


Select sname from s where sno in (select sno from SC where cno = 1 and cno in (select distinct sno from SC where cno = 2 ));

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.