Oracle SQL Practice Questions – Article1

Knowledge of SQL is must if you are interested a career in databases. Demand for SQL-expertise is always high and is valued in the market. Oracle is a very popular secured database that is widely used across multinational companies. So, in this article, you get Oracle SQL questions that cover the most frequently asked interview questions and help you brush up your knowledge before the interview.

If you are a fresher or an experienced, these are the basic questions for you to test your SQL query writing skills.

Click download here to download all tables used in the questions. You must create all these table in your Oracle database to run through all SQL questions described in this article.

 

Most Popular SQL Interview Questions and Answers

Q1. Display the current date?

Ans:

SELECT sysdate FROM Dual;

Q2. Display the details of all employees?

Ans:

SELECT * FROM Emp;

Q3. Display the department information from Dept table?

Ans:

SELECT * FROM Dept;

Q4. Display the name, job and department number for all employees?

Ans:

SELECT Ename, Job, Deptno
FROM Emp;

Q5. Display the name and salary for all employees?

Ans:

SELECT Ename, Sal
FROM Emp;

Q6. Display the number of employees and total salary of all the employees in Emp table?

Ans:

SELECT Empno, Sal+NVL(Comm, 0) TotalSal
FROM Emp;

Q7. Display the employee name and annual salary of all employees?

Ans:

SELECT Ename, Sal*12 AnnualSal
FROM Emp;

Q8. Display the name of all employees who are working in department 10?

Ans:

SELECT Ename, Deptno
FROM Emp
WHERE Deptno = 10;

Q9. Display name, designation, salary, commission and department number of all managers working in department 20?

Ans:

SELECT Ename, Job, Sal, Comm, Deptno
FROM Emp
WHERE Deptno = 20 AND Job = 'MANAGER';

You can also combine multiple filter conditions as below.

SELECT Ename, Job, Sal, Comm, Deptno
FROM Emp
WHERE (Deptno, Job) = (20, 'MANAGER');

Q10. Display the names of employees who are working as clerks and earning 3000 or more?

Ans:

SELECT Ename
FROM Emp
WHERE Job = 'CLERK' AND Sal >= 3000;

Q11. Display the employee number & name who are earning commission?

Ans:

SELECT Empno, Ename, Comm
FROM Emp
WHERE Comm IS NOT NULL AND Comm <>0;

"WHERE Comm IS NOT NULL AND Comm <> 0" can also be achieved simply by saying "Comm <> 0". This is because NULL can’t be compared using any operators other than IS NULL and IS NOT NULL. So, below query is also correct to get teh employees

SELECT Empno, Ename
FROM Emp
WHERE Comm <> 0;

Q12. Display the employee number & name who do not earn commission?

Ans:

SELECT Empno, Ename
FROM Emp
WHERE Comm IS NULL OR Comm = 0;

Q13. Display the names of employees who are working as clerks or salesman or analyst and earning more than 3000?

Ans:

SELECT Ename
FROM Emp
WHERE Job IN ('CLERK', 'SALESMAN', 'ANALYST') AND Sal > 3000;

Q14. Display the names of employees who joined the company before 30th June 1981 or after 31st December 1983?

Ans:

SELECT Ename, HireDate
FROM Emp
WHERE HireDate < '30-JUN-1981' OR HireDate > '31-DEC-1983';

Q15. Display the names of employees who joined the company on or before 30th June 1981 or on or after 31st December 1983?

Ans:

SELECT Ename, HireDate
FROM Emp
WHERE HireDate <= '30-JUN-1981' OR HireDate >= '31-DEC-1983';

Q16. Display the names of employees who joined the company between the year 1980 and 1985?

Ans:

SELECT Ename, HireDate
FROM Emp
WHERE HireDate >= '01-JAN-1980' AND HireDate <= '31-DEC-1985';

This type of situation can also be achieved by BETWEEN AND operator. So, you can write the query as below as well.

SELECT Ename, HireDate
FROM Emp
WHERE HireDate BETWEEN '01-JAN-1980' AND '31-DEC-1985';

If you are good with Oracle functions, you can also write the query as below.

SELECT Ename, HireDate
FROM Emp
WHERE to_char(HireDate,'yyyy') BETWEEN '1980' AND '1985';

Q17. Display the names of employees working in department 10 or 20 or 40 or employees working as clerks, salesman or analyst?

Ans:

SELECT Ename
FROM Emp
WHERE Deptno IN (10, 20, 40) OR Job IN ('CLERK', 'SALESMAN', 'ANALYST');

Q18. Display the names of employees whose name starts with "S"?

Ans:

SELECT Ename
FROM Emp
WHERE Ename LIKE 'S%';

Q19. Display the employee number and names whose name ends with "S"?

Ans:

SELECT Empno, Ename
FROM Emp
WHERE Ename LIKE '%S';

Q20. Display the employees whose names have 2nd character as "L"?

Ans:

SELECT Ename
FROM Emp
WHERE Ename LIKE '_L%';

Q21. Display the employees whose names have 2nd character as "A" and 4th character as "E"?

Ans:

SELECT Ename
FROM Emp
WHERE Ename LIKE '_A_E%';

Q22. Display the employees name and job whose name 3rd last character is "I"?

Ans:

SELECT Ename, Job
FROM Emp
WHERE Ename LIKE '%E__';

Q23. Display the employees whose name is exactly five character in length?

Ans:

An Underscore "_" in a pattern can match exactly one character in the value. So, to get names of five characters, put five underscores in the pattern without any space.

SELECT Ename
FROM Emp
WHERE Ename LIKE '_____';

OR

SELECT Ename
FROM Emp
WHERE length(Ename) = 5;

Q24. Display the name and job of employees who are not managers?

Ans:

SELECT Ename, Job
FROM Emp
WHERE Job <> 'MANAGER';

Q25. Display the name and job of employees who are not working as salesman or clerk or analyst?

Ans:

SELECT Ename, Job
FROM Emp
WHERE Job NOT IN ('SALESMAN', 'CLERK', 'ANALYST');

Q26. Display the name and job of employees of department 20 who are not salesman or clerk?

Ans:

SELECT Ename, Job
FROM Emp
WHERE Deptno = 20 AND Job NOT IN ('SALESMAN', 'CLERK');

Q27. Display the total number of employees working in the company?

Ans:

SELECT count(*) Total_emps
FROM Emp;

Q28. Display the total salary being paid to all employees?

Ans:

SELECT SUM(Sal) Total_Sal
FROM Emp;

Q29. Display the maximum salary from EMP table?

Ans:

SELECT max(Sal) Max_sal
FROM Emp;

Q30. Display the minimum salary and average salary from EMP table?

Ans:

SELECT min(Sal) Min_sal, avg(Sal) Avg_sal
FROM Emp;

Q31. Display the maximum and minimum paid clerk?

Ans:

SELECT max(Sal) Max_paid_clerk, min(Sal) Min_paid_clerk
FROM Emp
WHERE Job = 'CLERK';

Q32. Display the maximum salary being paid to a salesman in any department?

Ans:

SELECT max(Sal) Max_paid_salesman
FROM Emp
WHERE Job = 'SALESMAN';

Q33. Display the average salary drawn by all managers?

Ans:

SELECT AVG(Sal) Avg_sal_mgr
FROM Emp
WHERE Job = 'MANAGER';

Q34. Display the maximum, minimum and average of all managers in department 20?

Ans:

SELECT max(Sal) Max_paid_mgr, min(Sal) Min_paid_mgr, avg(Sal) avg_mgr_sal
FROM Emp
WHERE Deptno = 20 AND Job = 'MANAGER';

Q35: Display the total salary drawn by Analyst working in department number 30?

Ans:

SELECT SUM(Sal) TotalSal
FROM Emp
WHERE Job = 'ANALYST' AND Deptno =40;

Q36: Display name, job, salary and join date of the employees in ascending order of their salary?

Ans:

SELECT Ename, Job, Sal, HireDate
FROM Emp
ORDER BY Sal ASC;

Q37: Display employee number, name, department number and salary. Sort the output based on department number and within each department sort by descending order of salary and within salary by name?

Ans:

SELECT Empno, Ename, Deptno, Sal
FROM Emp
ORDER BY Deptno, Sal Desc, Ename;

Q38: Display the name of the employee along with their annual salary. The name of the employee earning highest annual salary should appear first?

Ans:

SELECT Ename, Sal*12 AnnualSal
FROM Emp
ORDER BY Sal DESC;

Q39: Display the name, designation, department number and annual salary in "$" format for all employees in descending order of their annual salary?

Ans:

SELECT Ename, Job, Deptno, to_char(Sal*12, '99,999.99 $') AnnualSal
FROM Emp
ORDER BY Sal DESC;
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments