Query to find Second Highest Salary of Employee?
Select distinct Salary from Employee e1 where 2=Select count (distinct Salary) from Employee e2 where
e1.salary<=e2.salary;
Alternative Solution:
Select min (salary) from (select distinct salary from emp order by salary desc) where rownum<=2;
Query to find duplicate rows in table?
Select * from Employee a where rowid <>( select max(rowid) from Employee b where
a.Employee_num=b.Employee_num);
How to fetch monthly Salary of Employee if annual salary I given?
Select Employee_name,Salary/12 as ‘Monthly Salary’ from employee;
What is the Query to fetch first record from Employee table?
Select * from Employee where Rownum =1;
Click here to get What is Row num?
What is the Query to fetch last record from the table?
Select * from Employee where Rowid= select max(Rowid) from Employee;
What is Query to display first 5 Records from Employee table?
Select * from Employee where Rownum <= 5;
What is Query to display last 5 Records from Employee table?
Select * from Employee e where rownum <=5 union
Select * from (Select * from Employee e order by rowid desc) where rownum <=5;
What is Query to display Nth Record from Employee table?
Select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where
rownum <= N_ROWS ) where rnum >= N_ROWS
How to get 3 highest salaries records from Employee table?
Select distinct salary from employee a where 3 >= (select count(distinct salary) from employee b where a.salary
<= b.salary) order by a.salary desc;
Alternative Solution:
Select min (salary) from (select distinct salary from emp order by salary desc)where rownum<=3;
How to Display Odd rows in Employee table?( Answer:
Select * from (Select rownum as rno,E.* from Employee E) where Mod(rno,2)=1;
How to Display Even rows in Employee table?
Select * from(Select rownum as rno,E.* from Employee) where Mod(rno,2)=0;
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;
How Can i create table with same structure of Employee table?
Create table Employee_1 as Select * from Employee where 1=2;
Display first 50% records from Employee table?
Select rownum, e.* from emp e where rownum<=(select count(*)/2 from emp);
Display last 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);
How can i create table with same structure with data of Employee table?
Create table Employee1 as select * from Employee;
How do i fetch only common records between 2 tables?
Select * from Employee; Intersect
Select * from Employee1;
Write a 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);
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);
Select all records from Employee table whose name is ‘Amit’ and ‘Pradnya’
Select * from Employee where Name in(‘Amit’,’Pradnya’);
Select all records from Employee table where name not in ‘Amit’ and ‘Pradnya’
Select * from Employee where name Not in (‘Amit’,’Pradnya’);
How to fetch all the records from Employee who’s joining year is 2017?
Answer: Oracle:
Select * from Employee where To_char(Joining_date,’YYYY’)=’2017′;
What is SQL Query to find maximum salary of each department?
Select Dept_id,max(salary) from Employee group by Dept_id;
How do you find all Employees with its managers? (Consider there is manager id also in Employee table)
Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;
Display the name of employees who have joined in 2016 and salary is greater than 10000?
Select name from Employee where Hire_Date like ‘2016%’ and salary>10000;
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;
How to add the email validation using only one query?
SELECT
Email FROM
Employee
Where NOT REGEXP_LIKE (Email, ‘[A-Z0-9._%+-]+@[A-Z0- 9.-]+\.[A-Z]{2,4}’, ‘i’);
How to display 1 to 100 Numbers with query?
Select level from dual connect by level <=100;
How to remove duplicate rows from table?
Step 1: Selecting Duplicate rows from table 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);
How to find count of duplicate rows?
Select rollno, count (rollno) from Student Group by rollno
Having count (rollno)>1 Order by count (rollno) desc;
