Essential Oracle 50 Selected Practice Questions with Answers

Review all blog information to know more about databases and our products.

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 

    1
    select ename, deptno from emp where empno = 7499;

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

    1
    select ename, sal from emp where sal < 5000

    or 

    1
    2
     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 

    1
    2
    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 

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

    or 

    1
    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 

    1
    2
    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 

    1
    select ename, job from emp where mgr is null;

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

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

    or 

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

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

    1
    2
    3
    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% 

    1
    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)

    1
    2
    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

    1
    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

    1
    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

    1
    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

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

  15. Select the number of employees of each job

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

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

    1
    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

    1
    2
    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.

    1
    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

    1
    2
    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

    1
    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

    1
    2
    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’

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

    or
    1
    2
    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

    1
    2
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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

Share

Copyright © 2024 SQLynx Co., Ltd.