TOP 20 SQL Query Examples

In this post I am sharing frequently asked SQL Query Interview Questions for freshers and experienced Developers.When conducting campus interview process in written test they may ask the following SQL queries and at the same time in technical process also.

1) Write a Query to display employee number and total salary for each employee?

Ans: Select empno,sal+comm from emp;

2) Write a Query to display employee name and annual salary for all Employees?

Ans: select ename,12*sal+nvl(comm,0)annualsal from emp;

3) Write a Query to display names of all employees who are working in department number 10?

Ans: select ename from emp where deptno=10;

4) How to display the name of the current user?

Ans: show user;

5) What is the experience gap between the first employee and latest?

Ans: Select max(doj)-min(doj)from employee;

6) How to find out least 5 earners of the company?

Ans: Select rownum,ename,sal from(select ename,sal from emp order by sal desc)where rownum<=5;

7) What is difference between Selection statement and Projection in SQL?

Ans: Selection:  You can use Select statement in SQL to choose rows in a table that you have returned by query.

Projection: You can use Projection statement in SQL to choose column in a table that you have returned by query.

8) How to display employee number and name who do not earn any commission?

Ans: Select empno,ename from emp where comm is null and comm=0;

9) What is difference between SQL and SQL*PLUS?

Ans: 

SQL is command language to communicate with any Server like Oracle server. When you entered a SQL statement,it is stored in part of memory called the SQL buffer and remains there until you entered a new statement.

SQL *PLUS is an Oracle tool that recognizes and submit SQL statements to Oracle server or for execution it contains its own command language.

10) How to add a new Column to an existing table?

Ans: By using ALTER command we can add new column for existing a table.
       ALTER table emp add(city);
        Table is altered.(new column name city is added to employee table)

11)Can we use select statement in views?

Ans: Select(query)Statements can be used on views in the same way as tables.

SQL> CREATE VIEW emp  AS
select * from emp where emp_id IS NOT NULL;

View Created
Then we can use Select statement to display employee by using emp_id
Select * from emp where emp_id=101;

12) How to display list of users in your database?

Ans: Select * from dba_users;

13) How to display names of all tables from current user?

Ans: Select * from tab;

14) How to display employee who are not working as managers?

Ans: Select * from emp e where empno not in(select mgr from emp where e.empno=mgr);

(OR)
Select * from emp where empno not in(select mgr from emp where mgr is not null);

15) How to display Department numbers and total number of employees working in each department?

Ans: select deptno,count(*)from emp groupby deptno;

16) How to display department numbers and total salary for each Department?

Ans: Select deptno,sum(sal)from emp groupby deptno;

17)How to display the department number with more than 3 employees in each department?

Ans: Select detptno,count(*)from emp groupby deptno having count(*)>3;

18)How to display the names of employees who earn Highest salary in their respective job groups?

Ans: Select ename,job from emp where sal in(select max(sal)from emp group by job);

19) How to display the information from the employee table where ever job manager is found it should be displayed as Boss?

Ans: Select ename,replace(job,'MANAGER','BOSS')from emp;

20) How to display your age in days?

Ans: select sysdate-to-date('30-may-1976')from dual;

See also:

Top 20 SQL Query interview Questions with results
SQL GroupBy and Having Clause with examples
Oracle Data Types with examples
SQL Functions with examples