Following are some very of import Complex SQL Queries Examples with answers. I have tried to explain each and every query in particular so that everyone will go idea of how it is executed step-past-footstep. In SQL and PL SQL interviews we crave to know the key and important complex sql queries in item level. These are important Complex SQL Queries :

Complex SQL Queries Examples(xc% ASKED IN Interviews)

SQL Interview Book DOWNLOAD FOR FREE

one.Query to detect Second Highest Salary of Employee?(click for explaination)

Answer:

Select singled-out Salary from Employee e1 where two= Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;

Alternative Solution : Suggested by Ankit Srivastava

select min(salary)from(select distinct salary from emp society by bacon desc)where rownum<=2;

two.Query to find duplicate rows in table?(click here for explaination )

Answer :

Select * from Employee a where rowid <>( select max(rowid) from Employee b where a.Employee_num=b.Employee_num);

three.How to fetch  monthly Salary of Employee if annual salary is given?(click here for Explaination)

Answer:

   Select Employee_name,Bacon/12 equally 'Monthly Salary' from employee;

Click here to get information on ROW_ID

4.What is the Query to fetch offset record from Employee table? (ninety% asked Complex SQL Queries Examples)

Answer:

 Select * from Employee where Rownum =1;

Select * from Employee where Rowid= select min(Rowid) from Employee;

Click here to get What is Rownum?

5.What is the Query to fetch final record from the tabular array?

Answer:

Select * from Employee where Rowid= select max(Rowid) from Employee;

Complex SQL Queries Examples
Circuitous SQL Queries

Click here to go 20 interview questions on Perforance Tuning..

6.What is Query to display get-go 5 Records from Employee table?(xc% asked Circuitous SQL Queries Examples)

Reply:

Select * from Employee where Rownum <= 5;

CLICK HERE TO Get Data ON NORMALIZATION

half dozen.What is Query to brandish last v Records from Employee tabular array?(90% asked Complex SQL Queries Examples)

Answer:

Select * from Employee e where rownum <=5

wedlock

select * from (Select * from Employee e order by rowid desc) where rownum <=5;

Click Hither to go What is Union?

7.What is Query to brandish Nth Record from Employee table?

Answer :

select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where rownum <= N_ROWS ) where rnum >= N_ROWS

8.How to go 3 Highest salaries records from Employee table?

Answer:

select singled-out bacon from employee a where 3 >= (select count(distinct salary) from employee b where a.salary <= b.salary) order by a.salary desc;

Alternative Solution: Suggested past Ankit Srivastava

select min(salary)from(select singled-out salary from emp gild by salary desc)where rownum<=3;

nine.How to Brandish Odd rows in Employee table?(90% asked Complex SQL Queries Examples)

Answer:

Select * from(Select rownum equally rno,E.* from Employee Due east) where Mod(rno,ii)=ane;

10.How to Display Even rows in Employee table?

Answer:

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

Learn SQL Server Course hither: SQL Server Training

eleven.How to fetch tertiary highest bacon using Rank Function?

Answer:

select * from (Select Dense_Rank() over ( order by  bacon desc) every bit Rnk,East.* from Employee Eastward) where Rnk=iii;

Click Here to Get Data on Rank and Dense_Rank

12.How Can i create table with same construction of Employee table?(ninety% asked Complex SQL Queries Examples)

Respond:

Create table Employee_1 equally Select * from Employee where 1=ii;

13.Display outset l% records from Employee table?

Reply:

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

14.Display final 50% records from Employee table?

Answer:

Select rownum,E.* from Employee Due east

minus

Select rownum,E.* from Employee E where rownum<=(Select count(*)/2) from Employee);

15.How Tin i create table with same structure with data of Employee tabular array?

Respond:

Create table Employee1 as select * from Employee;

xvi.How do i fetch only common records betwixt 2 tables.

Reply:

Select * from Employee;

Intersect

Select * from Employee1;

CLICK Hither TO Become INFORMATION ABOUT INTERSECT OPERATOR

17.Find Query to go information of Employee where Employee is non assigned to the department

Reply:

Select * from Employee where Dept_no Not in(Select Department_no from Section);

eighteen.How to go distinct records from the tabular array without using distinct keyword.

Answer:

select * from Employee a where  rowid = (select max(rowid) from Employee b where  a.Employee_no=b.Employee_no);

nineteen.Select all records from Employee table whose name is 'Amit' and 'Pradnya'

Answer:

Select * from Employee where Proper noun in('Amit','Pradnya');

twenty.Select all records from Employee table where name not in 'Amit' and 'Pradnya'

Respond:

select * from Employee where proper name Non  in ('Amit','Pradnya');

Click Hither to get  20 Interview Questions for Tech Mahindra….

21.how to write sql query for the below scenario
I/p:ORACLE

O/p:
O
R
A
C
L
Eastward
i.e, splitting into multiple columns a string using sql.

Answer:

Select Substr('ORACLE',Level,1) From Dual
Connect Past Level<= Length('ORACLE');

22.How to fetch all the records from Employee whose joining year is  2017?

Answer:

Oracle:

select * from Employee where To_char(Joining_date,'YYYY')='2017′;

MS SQL:

select * from Employee where substr(convert(varchar,Joining_date,103),7,iv)='2017′;

23.What is SQL Query to find maximum salary of each department?

Answer:

Select Dept_id,max(salary) from Employee group by Dept_id;

24.How Do you observe all Employees with its managers?(Consider at that place is manager id also in Employee table)

Answer:

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

25.Brandish the proper noun of employees who accept joined in 2016 and salary is greater than 10000?

Answer:

Select name from Employee where Hire_Date like '2016%' and salary>10000;

26.How to display following using query?

*

**

***

Answer:

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

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

27.How to add the e-mail validation using only one query?

Answer :

User needs to apply REGEXP_LIKE function for email validation.

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

28.How to brandish 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

29.How to remove duplicate rows from table?(100% asked in Complex SQL Queries for Interviews)

Reply:

First Step: Selecting Duplicate rows from tabular array

Tip: Employ concept of max (rowid) of table. Click here to go concept of rowid.

Select rollno FROM Student WHERE ROWID <>

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

Step ii:  Delete duplicate rows

Delete FROM Student WHERE ROWID <>

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

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

Respond:

Select rollno, count (rollno) from Student

Group past rollno

Having count (rollno)>1

Order by count (rollno) desc;

31.How to Detect the Joining date of Employee in YYYY-Twenty-four hour period-Date format.

Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEES;

Promise This commodity named Complex SQL queries examples is useful to all the programmers. This article gives you the thought near Circuitous SQL Queries examples and will be useful to all the programmers.

Question 32 : How to convert the System time in to seconds in oracle?

Answer :

To_char function is used to catechumen time to grapheme and ssss volition used to catechumen the time in to seconds.

Following query is useful.

                SQL> select   2    to_char(sysdate,'hh24:mi:ss') Every bit "SystemTime",   3    to_char(sysdate,'sssss') "Seconds"   4  from dual;  SystemTime     Seconds --------       ----- xi:34:50       41750
Video for Complex sql queries :

Register for gratis and Get Circuitous SQL Interview Questions PDF ：

FOR Issues  CONTACT : compexsql@gmail.com