Interview questions: Database written exam SQL operation not seen 5

2018/5/31 Oracle Database interview Written questions summary
Http://www.yjbys.com/qiuzhizhinan/show-308759.html 1/4
Oracle Database
1. Basic Testing
Select all information for employees in department 30
Select * from EMP where deptno=30;
The number of the employee who listed the position as (MANAGER), name
Select empno,ename from emp where job =? Manager?;
Find employees with bonuses above their salaries
Select * from EMP where comm>sal;
Find out the sum of bonuses and wages for each employee
Select sal+comm,ename from EMP;
Identify the manager in department 10 and General Staff in department 20 (clerk)
Select * from EMP where (deptno=10 and job=? MANAGER?) or (Deptno=20 and job=?)
Clerk?);
Find employees in department 10 who are neither managers nor ordinary employees, and have a salary greater than or equal to 2000. Select * FROM
EMP where deptno=10 and job not in (? MANAGER?,? Clerk)? and sal>=2000;
Identify the different jobs of employees with bonuses
Select distinct job from EMP where Comm are not null and comm>0
Find employees with no bonuses or bonuses below 500
Select * from EMP where comm<500 or comm is null;
Show the employee's name, according to their service life, the oldest employees in the front row
Select ename from emp order by HireDate;
2. Function testing
Find employees who are hired on the third day of the month (e.g. 2009-5-29)
SELECT * from emp where Last_day (hiredate) -2=hiredate;
Find out who hired 25 years ago.
2018/5/31 Oracle Database interview Written questions summary
Http://www.yjbys.com/qiuzhizhinan/show-308759.html 2/4
SELECT * from emp where hiredate<=add_months (sysdate,-25*12);< p= "" >
All employee names are preceded by Dear, and the first letter is capitalized
Select Dear | | Initcap (ename) from EMP;
Find an employee with a 5-letter name
SELECT * from emp where length (ename) = 5;
Find employees with names that don't have the letter R
SELECT * from EMP where ename is not a like%r%;
First word showing the names of all employees
Select substr (ename,0,1) from EMP;
Show all employees in descending order by name, if same, sort by wage ascending
Assuming one months for 30 days, find out the daily wages of all employees, regardless of decimals
Find employees employed in February
SELECT * from emp where to_hiredate,fmmm) = 2;

3. Grouping functions
The average wage of employees under each department, >500
Select avg (SAL) from EMP where sal>500 Group by DEPTNO;
Statistics of departments with average wage greater than 500 in each department
Select Deptno,avg (SAL) from the EMP GROUP by DEPTNO has avg (SAL) >500; Figure out the Department 30
Bonuses awarded to employees in the
Select Max (comm) from emp where deptno = 30;
Figure out the name of the employee who received the most bonus in department 30
Select ename from emp where comm = (select Max (comm) from EMP where deptno=30);
Figure out the number of employees and the minimum wage for each position
Select Job,min (SAL), COUNT (*) from the EMP group by job;
Lists the number of employees per department in the employee table, and the Department no
2018/5/31 Oracle Database interview Written questions summary
Http://www.yjbys.com/qiuzhizhinan/show-308759.html 3/4
Select Count (*), DEPTNO from EMP Group by DEPTNO;
Get employee information that is more than the average salary of their department
SELECT * FROM EMP E1, (select Deptno,avg (SAL) as avgsal from EMP Group by DEPTNO) E2
where E1.deptno=e2.deptno and e1.sal > e2.avgsal;
Group statistics under each department, the average bonus for each position (also counted as the person who has no bonus) and the total wage (including the bonus) select
Deptno,job,avg (NVL (comm,0)), Sum (SAL+NVL (comm,0)) from the EMP group by Deptno,job;
4. Multi-table Joint search
Lists the number of employees per department in the employee table, and the Department no
Select Deptno,count (*) from the EMP group by DEPTNO;
List the number of employees per department in the employee table (the number of employees must be greater than 3), and the department name
Select d.*, Ed.cou from Dept D, (select Deptno,count (*) cou from EMP Group by Deptno
Having Count (*) >3) Ed where D.deptno=ed.deptno;
Find employees with more pay than Jones
SELECT * from emp where sal>= (select Sal from EMP where
Lower (ename) =jones);
List all employees ' names and their superiors ' names
Select E1.ename as lower, e2.ename as upper from EMP e1,emp E2 where e1.mgr
= E2.empno;
Select E1.ename as lower, e2.ename as upper from EMP e1,emp e2 where E1.mgr =
E2.empno (+);
Find two jobs with the highest average wage, grouped by position
SELECT * FROM (select AVG (SAL) from emp order BY job desc) where rownum<3; 20= ""
select= "D.dname=" "from=" "emp=" "dept=" "d=" "where=" "and=" "E.sal=" "> (select
Max (SAL) from
EMP where deptno=20) and E.deptno=d.deptno
Job categories with average wage greater than 2000
2018/5/31 Oracle Database interview Written questions summary
Http://www.yjbys.com/qiuzhizhinan/show-308759.html 4/4
The select job from the EMP group by job has avg (SAL) > 2000;
The average wage for all employees with a salary greater than 2000 and the average wage is greater than 2500 select
Deptno,avg (SAL) from EMP where sal>2000 GROUP by DEPTNO have avg (SAL) >2500;
Get the department number, department name, department position of the department with the lowest total payroll per month SELECT * FROM dept
where
Deptno = (
Select E.deptno from
(select Deptno,sum (SAL) from EMP Group by Deptno ORDER by sum (SAL)) E
where Rownum=1
);

Interview questions: Database written exam SQL operation not seen 5