HI WELCOME TO Sirees

20 SQL Queries for interview | Complex SQL Queries for Interview

Leave a Comment

In this article I will explain 20 SQL Queries for interview purpose.These are really important queries which will ask in most of the interview questions.


  • Answer:-
Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;
  • Answer :-
Select * from Employee a where row_id != select max(row_id) for Employee b where a.Employee_num=b.Employee_num;
  • Answer:-
   Select Employee_name,Salary/12 as ‘Monthly Salary’ from employee;
4.What is the Query to fetch first record from Employee table?
  • Answer:-
 Select * from Employee where Rownum =1;
5.What is the Query to fetch last record from the table?
  • Answer:-
Select * from Employee where Rowid= select max(Rowid) from Employee; 
6.What is Query to display first 5 Records from Employee table?
  • Answer:
Select * from Employee where Rownum <= 5;
6.What is Query to display last 5 Records from Employee table?
  • Answer:
Select * from Employee e where rownum <=5
union
select * from (Select * from Employee e order by rowid desc) where rownum <=5;
7.What is Query to display Nth Record from Employee table?
Select * from Employee  where rownum = &n;
8.How to get 3 Highest salaries records from Employee table?
select distinct salary from employee a where 3 >= (select count(distinct salary) from emp loyee b where a.salary <= b.salary) order by a.salary desc;
9.How to Display Odd rows in Employee table?
Select * from(Select rownum as rno,E.* from Employee E) where Mod(rno,2)=1;
10.How to Display Odd rows in Employee table?
Select * from(Select rownum as rno,E.* from Employee) where Mod(rno,2)=0;
11.How to fetch 3rd highest salary using Rank Function?
select * from (Select Dense_Rank() over ( order by  salary desc) as Rnk,E.* from Employee E) where Rnk=3;
12.How Can i create table with same structure of Employee table?
Create table Employee_1 as Select * from Employee where 1=2;
13.Display first 50% records from Employee table?
Select rownum,E.* from Employee E where rownum<=(Select count(*/2) from Employee);
14.Display first 50% records from Employee table?
Select rownum,E.* from Employee E
minus
Select rownum,E.* from Employee E where rownum<=(Select count(*/2) from Employee);
15.How Can i create table with same structure with data of Employee table?
Create table Employee1 as select * from Employee;
16.How do i fetch only common records between 2 tables.
Select * from Employee;
Intersect
Select * from Employee1;
17.Find Query to get information of Employee where Employee is not assigned to the department.
Select * from Employee where Dept_no Not in(Select Department_no from Employee);
18.How to get distinct records from the table without using distinct keyword.
select * from Employee a where  rowid = (select max(rowid) from Employee b where  a.Employee_no=b.Employee_no);
19.Select all records from Employee table whose name is ‘Amit’ and ‘Pradnya’
Select * from Employee where Name in(‘Amit’,’Pradnya’);
20.Select all records from Employee table where name not in ‘Amit’ and ‘Pradnya’
select * from Employee where name Not  in (‘Amit’,’Pradnya’);

all right reserved to http://www.complexsql.com/ 

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.