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 in detail. 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:
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);
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;
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;
21.Display 10 to 15 records from Employee table.
Answer:
Select * from (Select rownum as ‘No_of_Row’, E.* from Employee E)
Where No_of_Row between 10 and 15;
22.What is query to fetch last record of table?
Answer :
Select * from Customer where Rowid= select max(Rowid) from Customer;
23.How to Find Companies Start with ‘W’ letter?
Answer :
SELECT * FROM companies WHERE companies_name %STARTSWITH ‘W’;
Complex SQL Interview Questions :
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.