Oracle Database interview Exercises

1. List all departments that have at least one employee.
Analysis: How many employees are in each department--grouped by department number
Select Deptno,count (*) from EMP GROUP BY DEPTNO has count (*) >= 1;

2. List all employees who pay more than "SMITH".
Analysis: First query out SMITH salary: Select Sal from emp where Ename= ' Smith ';
SELECT * from emp where sal > (select sal from emp where ename= ' SMITH ');

3. Lists the names of all employees and their immediate superiors.
Analysis: Table self-mapping, for the table alias, the Association T1 table Simulation employee table T2 Table Save Direct parent Information
Select T1.ename Employee name, T2.ename direct superior from EMP t1,emp T2 where T1. MGR = T2.empno;

4. List all employees whose employment date is earlier than their immediate superiors.
Analysis: The principle and the above problem are similar
Select T1.*,t2.hiredate from emp t1,emp T2 where T1. MGR = T2.empno and T1.hiredate < t2.hiredate;

5. Lists the department names and employee information for those departments, and lists the departments that have no employees.
Analysis: Departments do not have staff to show-outside connections. No matter how the Department information must be displayed, through the department to associate staff
SELECT * FROM dept LEFT OUTER join emp on dept.deptno = Emp.deptno;

6. Lists the names of all "clerk" (clerks) and their department names.
Analysis: Find job for clerk employee name and department name
Employee Name EMP Table
Department Name Dept Table
Select Emp.ename,dept.dname,emp.job from emp,dept where Emp.deptno = Dept.deptno and emp.job= ' clerk ';

7. Lists various jobs with a minimum salary of more than 1500.
Analysis: Minimum wage for work – grouped by work, minimum salary
Select min (sal) from the EMP group by job;
Greater than 1500 is a grouping condition-having
Select Job,min (SAL) from the EMP group by Job have min (sal) > 1500;

8. Lists the names of employees who work in the department "sales", assuming they do not know the department number of the sales department.
Analysis: Employee name is located in EMP Department name Dept
Select Emp.ename from emp,dept where Emp.deptno = Dept.deptno and dept.dname = ' SALES ';

9. List all employees who pay more than the company's average salary.
Analysis: The company's average salary first select AVG (SAL) from EMP;
SELECT * from emp where sal > (select AVG (SAL) from EMP);

10. List all employees who work in the same way as "SCOTT".
Analysis: First query Scott:select job from emp where ename = ' SCOTT ';
SELECT * from emp where ename <> ' scott ' and job = (select Job from emp where ename = ' Scott ');

11. Lists the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30.
Analysis: View the Payroll list for all employees in department 30 select Sal from emp where deptno = 30;
SELECT * from emp where Sal in (select Sal from emp where deptno = 30);

12. Lists the employee's name and salary for salaries higher than the salary of all employees who work in department 30.
Analysis:
SELECT * from emp where sal > All (select Sal from emp where deptno = 30);
SELECT * from emp where sal > (select Max (SAL) from emp where deptno = 30);

13. List the number of employees working in each department and the average wage.
Analysis: Grouping by department
Select Deptno, COUNT (*), AVG (SAL) from the EMP group by DEPTNO;

14. Lists the names, department names, and wages of all employees.
Analysis:
Select Emp.ename,dept.dname,emp.sal from emp,dept where emp.deptno = Dept.deptno;

15. Lists detailed information and number of departments for all departments.
Analysis:
Select Dept.*,count (emp.*) from emp,dept where Emp.deptno = Dept.deptno GROUP by Deptno; Error!
For each department number and number of select Deptno,count (*) from the EMP group by DEPTNO;
and then the Dept table is associated with select Dept.*,temp. Number of Departments from Dept, (select Deptno,count (*) Department number from EMP Group by Deptno) temp where DEPT.DEPTN o = Temp.deptno;

16. List the minimum wage for each job.
Analysis: Each work group, minimum wage min
Select Job,min (SAL) from the EMP group by job;

17. Lists the manager (manager) minimum salary for each department.
Analysis: Where job= ' manager ' filters all not manager data
Select Deptno,min (SAL) from the emp where job = ' MANAGER ' GROUP by DEPTNO;

18. Lists the annual salary of all employees, sorted from lowest to highest annual salary.
Analysis: Select ename, sal*12 from emp order by sal*12 ASC;

19. Find out the employee number, name, and salary of all employees whose salary is above 3000 (including 3000) in the EMP table.
Analysis: SELECT * from emp where Sal >= 3000;

20. Find out all personnel information for all salaries above ' ALLEN '.
Analysis: SELECT * from emp where sal > (select sal from emp where ename = ' ALLEN ');

21. Check out all employees whose department number is 20 in the EMP table and whose salary is above 2000 (excluding 2000), showing their employee number, name and salary, as shown in the following column name: Employee Number Employee Name salary
Analysis: Select empno employee number, ename employee name, Sal salary from emp where Deptno = 2000 and Sal >;

22. Check out all types of work in the EMP table (No Duplicates)
Analysis: SELECT distinct job from EMP;

23. Find out all information for people who are not empty for all bonus (Comm) fields.
Parse: Not empty is not NULL
SELECT * FROM EMP where comm are NOT null;

24. Check out the information for all employees with a salary between 800 and 2500 (closed interval). (Note: Use two ways to implement and and between and)
Analysis: SELECT * from emp where Sal >= and Sal <= 2500;
SELECT * from EMP where Sal between and 2500;

25. Check out the information for all employees with employee number 7521,7900,7782. (Note: Implemented in two ways, or and in)
Analysis: SELECT * from EMP where empno in (7521,7900,7782);
SELECT * from emp where empno=7521 or empno = 7900 or empno = 7782;

26. Find all employee information with a "a" character in the name and a salary of more than 1000 (excluding 1000).
Analysis: Fuzzy query
SELECT * from emp where ename like '%a% ' and sal > 1000;

27. Check out the name of the third letter is "M" of all employee information.
Analysis: A third letter __m%
SELECT * from emp where ename like ' __m% ';

28. Sort all employees by salary in ascending order, with the same salary as the entry time.
Analysis: SELECT * from emp order by Sal Asc,hiredate Desc;

29. Sort all employees in ascending alphabetical order, with the same initials as the salary descending.
Analysis: SUBSTRING (' string ', number of characters, length); --Initial letter substring (ename,1,1)
SELECT * from emp ORDER by substring (ename,1,1) asc,sal desc;

30. Find out the name, entry time and salary of the person who first worked.
Analysis: Minimum-hiredate for the earliest working person
Select Ename,hiredate,sal from emp where hiredate = (select min (hiredate) from EMP);
Select Ename,hiredate,sal from emp where hiredate <= all (select HireDate from EMP);

> any = = = > min
> all = = = > Max
< any = = = < Max
< all = = = < min

31. Show all employee's name, salary, bonus, if no bonus, show 100 temporarily.
Analysis: Select Ename,sal,comm from EMP; --no bonus show 100 function ifnull
Select Ename,sal,ifnull (comm,100) from EMP;

32. Show the position of the highest-paid person.
Analysis: The select job from the emp where Sal = (select Max (SAL) from EMP);
Select Job from emp where Sal >= all (select Sal from EMP);

33. Find out the maximum salary and minimum salary for all departments in the EMP table, and the department Number 10 is not displayed.
Analysis: GROUP BY Department select Deptno,max (Sal), Min (sal) from EMP where deptno<>10 Group by DEPTNO;

34. Delete the employee with the highest salary in unit 10th.
Analysis: Delete from emp where deptno=10 and Sal >= all (select Sal from EMP where deptno=10); MYSQL does not support
Mysql specification, modify or delete records in a table, do not allow querying of the same table in subqueries
ERROR 1093 (HY000): You can ' t specify target table ' EMP ' for update in FROM clause

Solution: Temp Table
Delete from emp where deptno=10 and Sal >= all (select T.sal from (select Sal from emp where deptno=10) t);

35. Lower the salary of the highest-paid employee by 30%.
Analysis: Update emp Set sal = sal*0.7 where Sal = (select Max (SAL) from EMP); MYSQL does not support
Introducing temporary tables
Update emp Set sal = sal*0.7 where Sal = (select T.maxsal from (select Max (SAL) maxsal from EMP) t);

36. Query the employee's name, salary and wage level (salary >=3000 is 3, salary >2000 is 2, salary <=2000 is 1 level)
Analysis:
Select Ename,sal, Case if sal>=3000 then ' 3 level ' when sal>2000 and then ' 2 ' else ' 1 ' end level from EMP;

Syntax: Case ... when ... then ... when ... then ... else ..... end

Row and column Interchange
Name Course Score

Zhang San language 74

Zhang San Mathematics 83

Zhang San Physics 93

John Doe Language 74

John Doe Mathematics 84

John Doe Physics 94

Want to become (get the following result):

Name Chinese mathematics Physics

—- —- —- —-

Lee 474 84 94

Sheet 374 83 93

——————-

Select Name,max (case when cource = ' language ' then score else 0 end) from scores group by name;

Select Name,max (case when cource = ' language ' then score else 0 end) language, Max (case when cource = ' math ' then score else 0 end) Math,
Max (case when cource = ' English ' then score else 0 end) English from scores group by name;

Oracle Database interview Exercises