SQL Query Interview Questions | SQL Interview Questions in Query form

In my previous article i have given different complex sql queries as well as sql queries for interviews.I have also given article on basic sql queries as well.In this article i would like to add another article on SQL Query Interview Questions with answers.The article will give you some most important queries which are really important for interviews. This will give SQL Query Interview Questions which is mixture of all SQL Questions.These queries are important for interviews and helpful to all people who is related to SQL.I would like to give pure SQL queries instead of giving theoretical interview questions.

Question 1 Most Asked SQL Query Interview Questions

How to Calculate Second Highest Salary in SQL?

Answer :

I have explained different ways to calculate second highest salary of employee in SQL.For this question i would like to explain 2-3 different ways to calculate Second highest salary of employees.

Query 1 :

SELECT max(e1.sal), e1.deptno FROM s_emp e1 WHERE sal < (SELECT max(sal) FROM s_emp e2 WHERE e2.deptno = e1.deptno) GROUP BY e1.deptno;

Query 2 :

SELECT * FROM (SELECT S.*,DENSE_RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) DR FROM SOURCE ) S WHERE S.DR=2;

Query 3 :

Select Name From Employees Where Salary =

(Select Distinct Top(1) Salary from Employees where Salary Not In

(Select Dustinct Top(1) Salary from Employees Order By Salary Descending) Order by Salary Descending);

Question 2

90% asked SQL Query Interview Questions

How to create duplicate table with data and without data?

Answer :

There are so many times where user needs to create table with data for testing purpose as well as table without data as well for creating the structure of the table.

Query 1 : Create table with data

Create table Student_Replica as Select * from Student;

Query 2: Create table without data

Create table Student_Replica as Select * from Student where 1=2;

Question 3

Query to find out the data between range.

Answer :

In day to day activities user needs to find out the data between some range. To achieve this user needs to use Between..and operator or Greater than and less than operator.

Query 1 : Using Between..and operator

Select * from Employee where salary between 25000 and 50000;

Query 2 : Using operators (Greater than and less than)

Select * from Employee where salary >= 25000 and salary <= 50000;

Question 4

How to calculate Even records from the table?

Answer:

This is also most asked sql query interview questions .User needs to use Mod function to calculate this.

Select * from(Select rownum as rno,E.* from Student) where Mod(rno,2)=0;

Question 5

How to calculate odd records from the table?

Answer:

This is also most asked sql query interview questions .User needs to use Mod function to calculate this.

Select * from(Select rownum as rno,E.* from Student) where Mod(rno,2)=1;

Question 6

How to remove duplicate rows from table?

Answer:

User needs to select the duplicate rows from the table without using distinct keyword.Following query will give you the duplicate rows from the table.

Select rollno FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

Query to delete duplicate rows from the table:

Delete FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

Question 7

What is query to delete all the user tables from specific database?

Answer :

This is most common query used to delete all the user defined tables from the oracle database.

Query :

Begin

For I In

(Select * from Tabs)    —Tabs is system table in which user get the different user defined table names.

Loop

Execute immediate (‘Drop Table  ‘||i.table_name||’cascade constraints’);

End loop;

End;

Question 8

What is query to fetch last day of next month in oracle?

Answer:

Select LAST_DAY (ADD_MONTHS (SYSDATE,1)) from dual;

Question 9

What is query to find out repetitive letters from specific column?

Answer:

This is most important question.This query is useful in checking the validations in the PL SQL Block.User needs to use the regular expression function to count the repetitive letters.

Select regexp_count (‘AmitA’,’A’) as Repeated_character from dual;

Question 10

What is query to Find out last record from the table.

Answer :

This is also most important SQL Query interview questions asked in the interview.  We can achieve this using rowid and max function together,

Select * from Students where rowid = select max(rowid) from Students;

Question 11

How to calculate number of rows in table without using count function?

Answer:

There are so many system tables which are very important .Using the system table user can count the number of rows in the table.following query is helpful in that case,

Select table_name, num_rows from user_tables where table_name=’Employee’;

Question 12

How to calculate first half and last half records in the table.

Answer:

There are some situations where user needs to find out exactly first half and second half of the table.First half means first 50% of data and last half means last 50% of records.

Query : To find out first half of the query

select rownum, e.* from emp e where rownum<=(select count(*)/2 from Students);

Query : To find last half of the query

Select rownum,S.* from Students S

minus

Select rownum,S.* from Students S where rownum<=(Select count(*)/2) from Students);

Question 13 :

How to add Email validation using single SQL Statement

This is one of the most important SQL Query Interview Questions .User can see the Email validation in SQL using multiple ways.

Answer :

Query :

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

Question 14

How to show maximum and minimum values from table.

Answer :

This is also most important SQL Query Interview Questions which needs to be used in most of real life scenarios.

Select max (marks) from Students

Union

Select min (marks) from Students;

Question 15

Finding the Constraint information from the table.

Answer:

There are so many times where user needs to find out the specific constraint information of the table. following queries are useful,

SELECT * From User_Constraints;

SELECT * FROM User_Cons_Columns;

Question 16

How to check the procedure code using the system tables?

Answer :

 SELECT * FROM User_Source

WHERE Type=’PROCEDURE’

AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

Question 17

Give me query of Self Join.

Answer :

The most commonly asked SQL Query Interview Questions asked in interview.The query is to find out the manager name using self join.

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

Question 18

How to display 1 to 100 numbers without using sequence?

Answer :

User needs to use hierarchical query to display 1 to 100 numbers in SQL,

Select level from dual connect by level <=100;

These are most important SQL Query Interview Questions useful to users. If you like this article or if you have some issues with the same kindly comment in comments section.