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

JAVA Expert Interview Questions Answers 2017

Java Basics ::  Interview Questions and Answers Home  »  Interview Questions  »  Technical Interview  »  Java Basics  » Interview Questions 1.     What is the difference between a constructor and a method? A constructor is a member function of a class that is used to create objects of that class. It has the same name as the class itself, has no return type, and is invoked using the new operator. A method is an ordinary member function of a class. It has its own name, a return type (which may be void), and is invoked using the dot operator. 2.     What is the purpose of garbage collection in Java, and when is it used? The purpose of garbage collection is to identify and discard objects that are no longer needed by a program so that their resources can be reclaimed and reused. A Java object is subject to garbage collection when it becomes unreachable to the program in which it is used. 3.  ...
3 comments
Read more

Java Example Program to Convert List to Set

import java.util.ArrayList; import java.util.HashSet; import java.util.Set; public class ListToSet {  /**   * @author Amarjit Kumar   * @category interview questions   *   * Description: Convert List to set in java with example program   *   */  public static void main(String[] args) {   ArrayList<String> arrList= new ArrayList<>();   arrList.add("Java");   arrList.add("Java");   arrList.add("List to String");   arrList.add("Example Program");   Set<String> strSet = new HashSet<String>(arrList);   System.out.println(strSet);   System.out.println(arrList);  } } /*  * Java program to convert list to set. Convert ArrayList of string to HashSet  * in java example program How to convert List to Set in java Set<String> strSet  * = new HashSet<String>(arrList); HashSet having a constructor which will take  * list as an ar...
Post a Comment
Read more