Oracle SQL Practice Questions – Article2

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: Get the number of the employees recruited year wise?

Ans:

SELECT
    TO_CHAR(HireDate,'yyyy') HireDate,
    count(*) EmpCount
FROM EMP
GROUP BY to_char(HireDate,'yyyy');

Q2: Get the number of the employees recruited year wise along with the total number of employees recruited?

Ans:

SELECT
    to_char(HireDate,'yyyy') HireDate,
    count(*) EmpCount
FROM EMP
GROUP BY ROLLUP(to_char(HireDate,'yyyy'));

Q3: Write a query to get the employee details along with below additional information?

    • Dept wise
    • Sum(Sal)
    • Job wise Avg(Sal)
    • 2nd highest salary of each Dept
    • Lowest salary in each job
    • Manager name

Ans:

SELECT e.*, (SELECT sum(sal) FROM emp e1 WHERE e1.deptno = e.deptno) SumSal_DeptWise,
round((SELECT avg(sal) FROM emp WHERE job = e.job), 2) AvgSal_JobWise,
(SELECT max(sal) FROM emp WHERE WHERE = e.deptno AND sal NOT IN (SELECT max(sal) FROM emp WHERE WHERE = e.deptno)) HighSal_2nd_DeptWise,
(SELECT min(sal) FROM emp WHERE job = e.job) LowSal_JobWise,
(SELECT m.ename FROM emp m WHERE e.mgr = m.empno) Manager
FROM emp e;

Q4: Get the details of year wise, dept wise investments including the name of the department only when the number of employees recruited year wise, dept wise is crossing by two?

Ans:

SELECT
    to_char(HireDate, 'YYYY') Investment_year, Deptno, 
    sum(Sal) Total_investment
FROM Emp
GROUP BY to_char(HireDate, 'YYYY'), Deptno
HAVING count(*) > 2;

Q5: Display the employees with their Managers?

Ans:

Q6: Get the details of the employees who have managers?

Ans:

Q7: Get the details of the employees who are controlling other employees? OR this question can be framed like- Get the details of the Managers?

Ans:

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments