Essential Oracle 50 Selected Practice Questions with Answers

Attention: All the questions are based on the tables of the scott account to write SQL.

1. Query the name and department number of the employee whose employee number is 7499
   

   select ename, deptno from emp where empno = 7499;
   

2. Select the names and salaries of employees whose salary is not between 5000 and 12000
   

   select ename, sal from emp where sal < 5000
   

   or
   

    sal > 12000; or  
   select ename, sal from emp where sal not between 5000 and 12000;
   

3. Select the names, job_ids and hire dates of employees whose hire date is between 1998-02-01 and 1998-05-01
   

   select ename, job, hiredate from emp where hiredate > to_date('1998-02-01', 'yyyy-mm-dd')  
   and hiredate < to_date('1998-05-01', 'yyyy-mm-dd');
   

4. Select the names and department numbers of employees who work in department 20 or 50
   

   select ename, deptno from emp where deptno in(20, 50); 
   

   or
   

   select ename, deptno from emp where deptno = 20 or deptno = 50
   

5. Select the names and hire dates of employees who were hired in 1992
   

   select ename, hiredate from emp  
   where extract(year from hiredate) = '1992';
   

6. Select the names and jobs of employees who have no manager in the company
   

   select ename, job from emp where mgr is null;
   

7. Select the names of employees whose third letter of the name is 'a'
   

   select ename from emp where instr(ename, 'A') = 3; 
   

   or
   

   select ename from emp where ename like '__A%';
   

8. Select the names of employees whose names contain both the letters 'a' and 'e'
   

   select ename as Name from emp where instr(ename, 'A') > 0 and instr(ename, 'E') > 0; or  
   select ename from emp  
   where ename like '%A%' and ename like '%E%';
   

9. Query the employee number, name, salary, and the result after increasing the salary by 20%
   

   select empno, ename, sal, (sal + sal * 0.2) from emp;*
   

10. Sort the employees' names by the first letter and write the length of the name (length)
    

    select ename, length(ename) from emp  
    order by ascii(substr(ename, 1, 1));
    

11. Query the names of each employee and display the number of months each employee has worked in the company
    

    select ename, trunc(months_between(sysdate, hiredate)) from emp;
    

12. Query the names of employees and the number of months they have worked (worked_month), and sort them in descending order of the number of months
    

    select ename, trunc(months_between(sysdate, hiredate)) worked_month from emp order by worked_month desc;
    

13. Query the maximum, minimum, average and total salary of the employees in the company
    

    select max(sal), min(sal), avg(sal), sum(sal) from emp;
    

14. Query the maximum, minimum, average and total salary of employees of each job
    

    select max(sal), min(sal), avg(sal), sum(sal) from emp group by job;
    

15. Select the number of employees of each job
    

    select job, count(1) from emp group by job
    

16. Query the difference between the maximum and minimum salary of employees
    

    select max(sal) - min(sal) difference from emp
    

17. Query the minimum salary of each manager's subordinates, where the minimum salary cannot be lower than 6000, and employees without managers are not counted
    

    select mgr, min(sal) from emp where mgr is not null  
    group by mgr having min(sal) > 4000;
    

18. Query the names of all departments, work locations, number of employees and average salary.
    

    select d.dname, d.loc, count(1), avg(sal) from dept d inner join emp e on e.deptno = d.deptno group by d.dname, d.loc;
    

19. Query the names and hire dates of employees who are in the same department as Scott
    

    select ename, hiredate, deptno from emp  
    where deptno = (select deptno from emp where ename = 'SCOTT');
    

20. Query the employee number, name and salary of employees whose salary is higher than the average salary of the company
    

    select empno, ename, sal from emp where sal > (select avg(sal) from emp);
    

21. Query the employee number, name and salary of employees whose salary is higher than the average salary of their department
    

    select empno, ename, sal from emp e  
    where sal > (select avg(sal) from emp where deptno = e.deptno);
    

22. Query the employee numbers and names of employees who are in the same department as the employees whose names contain the letter 'u'
    

    select empno, ename  
    from emp where deptno in (select deptno from emp  
    where instr(ename, 'U') > 0); 
    

    or
    

    select e1.empno, e1.ename from emp e1, emp e2  
    where instr(e2.ename, 'U', 1, 1) > 0 and e1.deptno = e2.deptno;
    

23. Query the names and salaries of employees whose manager is King
    

    select ename, sal from emp  
    where mgr = (select empno from emp where ename = 'KING');
    

24. Use PL/SQL to implement the 9*9 multiplication table
    

    declare  
    v_result number;  
    v_i number;  
    v_j number;  
    begin  
    for v_i in 1..9  
    loop  
    for v_j in 1..v_i  
    loop  
    v_result := v_i * v_j;  
    dbms_output.put(v_i || '' || v_j || '=' || v_result ||' ');  
    end loop;  
    dbms_output.put_line('');  
    end loop;  
    end;
    

OK,If you find this article helpful, feel free to share it with more people.

If you want to find a SQL tool to practice, you can try our sqlynx, which has a simple interface and is easy to use. https://www.sqlynx.com/download/ Free download