Complex SQL Interview Questions

Complex SQL Interview Questions :

In my previous articles, I have given the idea about different complex queries. In this article, I will try to explain the Complex SQL Interview Questions. which may ask in interview with examples. I will try to explain the query in steps so that user will get idea of its execution. There are following SQL queries for Interviews, which are very important. These SQL queries for Interviews are important for fresher’s as well as the experienced professionals, which have good experience. I have tried to collect the queries, which I have faced personally in interview:

Complex SQL Interview Questions

1.How to display 1 to 100 Numbers with query?

Answer:

Select level from dual connect by level <=100;

Tip: User needs to know the concept of Hierarchical queries. Click here to get concept of hierarchical queries

2.How to remove duplicate rows from table?(100% asked in Complex SQL Interview Questions )

Answer:

First Step: Selecting Duplicate rows from table

Tip: Use concept of max (rowid) of table. Click here to get concept of rowid.

Select rollno FROM Student WHERE ROWID <>

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

Step 2:  Delete duplicate rows

Delete FROM Student WHERE ROWID <>

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

3.How to find count of duplicate rows? (95% asked in SQL queries for Interviews )

Answer:

Select rollno, count (rollno) from Student

Group by rollno

Having count (rollno)>1

Order by count (rollno) desc;

4.How to find Third highest salary in Employee table using self-join?(90% asked Complex SQL Interview Questions )

Answer:

Select * from Employee a Where 3 = (Select Count (distinct Salary) from Employee where a.salary<=b.salary;

Click here for explanation.

5.How to Show the Max marks and min marks together from student table?

Answer:

Select max (marks) from Student

Union

Select min (marks) from Student;

Tip : Use the concept of union to show the max and min marks together. Click here to get information about union and union all.

6.How to display following using query?

*

**

***

Answer:

We cannot use dual table to display output given above. To display output use any table. I am using Student table.

 

SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4;

7.How to display Date in DD-MON-YYYY table?

Answer:

Select to_date (Hire_date,’DD-MON-YYYY’) Date_Format from Employee;

8.If marks column contain the comma separated values from Student table. How to calculate the count of that comma separated values?

Student Name Marks
Amit 30,130,20,4
Sukruta 100,20,30
Sonali 140,10

 

Want to display output like :

 

 

Student Name Marks Count
Amit 4
Sukruta 3
Sonali 2

 

Answer:

Select Student_name, regexp_count (marks,’,’) + As “Marks Count” from Student;

Tip: In real scenarios, lot of times developer needs to calculate the number of commas in the column then regexp_count function is used.

9.How to create the Student_1 table, which is exact replica of Student table?

Answer:

Create Table Student_1 as select * from Student;

10.What is Query to drop all user tables from Oracle?

Answer:

To Drop all tables user needs to write simple PLSQL block

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;

11.How to get number of Weekends of current month?

Answer:

SELECT count (*) AS Weekends FROM

(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1

)

Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

Let us Fragment the Query for Understanding,

Step 1:  Try running internal query

SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1;

The query will give the all the dates from first to last of current date.

Step 2: To count the weekends.

From all the month, we need to calculate the weekends. Weekends means the Saturdays and Sundays from the month. So here, we need to use To_char function and ‘dy’ attribute of that function to calculate days. Therefore, we have used Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’); condition.

Therefore, Final Query will be,

SELECT count(*) AS Weekends FROM

(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1

)

Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

12.What is query to fetch last day of previous month in oracle?

Answer:

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

13.How to display the String vertically in Oracle?

Answer:

SELECT SUBSTR (‘AMIET’, LEVEL, 1) FROM dual

Connect by level <= length (‘AMIET’);

Output :

A

M

I

E

T

14.Write query to find the repeated characters from your name?

Answer:

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

15.How to display departmentwise and monthwise maximum salary?

Answer:

Select Department_no, TO_CHAR (Hire_date,’Mon’) as Month from Employee group by Department_no, TO_CHAR (Hire_date,’mon’);

16.How to get DDL of table in Oracle?

Answer:

To get DDL user needs to use dbms_metadata package and its get_ddl procedure,

Select dbms_metadata.get_ddl (TABLE,’table_name’) from dual;

17.How to convert seconds in to time format?

Answer:

SELECT

TO_CHAR (TRUNC (2700/3600),’FM9900′) || ‘:’ ||

TO_CHAR (TRUNC (MOD (2700, 3600)/60),’FM00′) || ‘:’ ||

TO_CHAR (MOD (2700, 60),’FM00′)

FROM DUAL;

Where 2700 is seconds.

Output:

00:45:00

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

Answer:

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

Tip: User needs to use the system tables for the same. So using user_tables user will get the number of rows in the table.

19.How to fetch common records from two different tables which has not any joining condition.

Answer:

Select * from Table1

Intersect

Select * from Table2;

Tip: Use Intersect keyword for fetching common records.

20.Display 4 to 7 records from Employee table.

Answer:

Select * from (Select rownum as ‘No_of_Row’, E.* from Employee E)

Where No_of_Row between 4 and 7;

In above article i have given 20 most important Complex SQL Interview Questions.Hope you like this article on very exceptional way of writing complex queries. This article of Complex SQL Interview Questions is helpful from students to employees who is willing to work on SQL.