Skip to main content

MOST IMPORTANT QUERIES (90% ASKED IN INTERVIEWS)

MOST IMPORTANT QUERIES (90% ASKED IN INTERVIEWS)
  • 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;
Complex SQL Queries for Interview
Complex SQL Queries
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’);
Search For Jobs OnMonster Logo
in
 


20.Select all records from Employee table where name not in ‘Amit’ and ‘Pradnya’
select * from Employee where name Not  in (‘Amit’,’Pradnya’);
21.how to write sql query for the below scenario
I/p:ORACLE
O/p:
O
R
A
C
L
E
i.e, splitting into multiple columns a string using sql.
Answer:
Select Substr(‘ORACLE’,Level,1) From Dual
Connect By Level<= Length(‘ORACLE’);

Comments

Post a Comment

Popular posts from this blog

Mockito interview Questions

1.       Question 1. What Is Mockito? Answer : Mockito allows creation of mock object for the purpose of Test Driven Development and Behavior Driven development. Unlike creating actual object, Mockito allows creation of fake object (external dependencies) which allows it to give consistent results to a given invocation. 2.       Question 2. Why Do We Need Mockito? What Are The Advantages? Answer : Mockito differentiates itself from the other testing framework by removing the expectation beforehand. So, by doing this, it reduces the coupling. Most of the testing framework works on the "expect-run-verify". Mockito allows it to make it "run-verify" framework. Mockito also provides annotation which allows to reduce the boilerplate code. 3.       Question 3. Can You Explain A Mockito Framework? Answer : In Mockito, you always check a particular class. The dependency in that class is injected using m...
2 comments
Read more

REST Methods

GET ============================================= HTTP GET method is used to **read** (or retrieve) a representation of a resource. According to the design of the HTTP specification, GET requests are used only to read data and not change it. Therefore, when used this way, they are considered safe. That is, they can be called without risk of data modification or corruption. Means calling it once has the same effect as calling it 10 times. Additionally, GET is idempotent which means that making multiple identical requests ends up having the same result as a single request. Donot expose unsafe operations via GET. It should never modify any resources on the server. Example: ------------    GET http://www.example.com/customers/12345/orders POST =================================== -> The POST verb is most-often utilized to **create** new resources. In particular, it's used to create subordinate resources. -> That is, subordinate to some other (e.g. parent) reso...
Post a Comment
Read more