SQL Interview Questions Part - 2

1. Write a SQL query to find the names of employees that begin with ‘A’?

To display name of the employees that begin with ‘A’, type in the below command:

SELECT * FROM Table_name WHERE EmpName like 'A%'

2. Write a SQL query to get the third highest salary of an employee from employee_table?

SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;

3. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;

Example of IN:

SELECT * FROM students where ROLL_NO IN (8,15,25);

4. How can you fetch common records from two tables?

You can fetch common records from two tables using INTERSECT. For example:

Select studentID from student INTERSECT   Select StudentID from Exam

5. How can you fetch alternate records from a table?

You can fetch alternate records i.e both odd and even row numbers. For example- To display even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

6. How can you select unique records from a table?

You can select unique records from a table by using the DISTINCT keyword.

Select DISTINCT studentID from Student

7. How can you fetch first 5 characters of the string?

There are a lot of ways to fetch characters from a string. For example:

Select SUBSTRING(StudentName,1,5) as studentname from student

8. How to fetch monthly Salary of Employee if annual salary is given?

Select Employee_name,Salary/12 as ‘Monthly Salary’ from employee;

9. 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;

or

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

10. 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);

11. What is the Query to fetch first record from Employee table?

Select * from Employee where Rownum =1;

12. What is the Query to fetch last record from the table?

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

13. What is Query to display first 5 Records from Employee table?

Select * from Employee where Rownum <= 5;

14. 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;

15. 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;

16. 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;

17. How to Display Odd rows in Employee table?

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

18. How to Display Even rows in Employee table?

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

19. 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;

20. How Can i create table with same structure with data of Employee table?

Create table Employee1 as select * from Employee;

21. How Can i create table with same structure of Employee table?

Create table Employee_1 as Select * from Employee where 1=2;

22. Display first 50% records from Employee table?

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

23. 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);

24. How do i fetch only common records between 2 tables?

Select * from Employee;

Intersect

Select * from Employee1;

25. 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);

26. 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);

27. Select all records from Employee table whose name is ‘Amit’ and ‘Pradnya’?

Select * from Employee where Name in(‘Amit’,’Pradnya’);

28. Select all records from Employee table where name not in ‘Amit’ and ‘Pradnya’?

select * from Employee where name Not  in (‘Amit’,’Pradnya’);

29. 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.

Select Substr(‘ORACLE’,Level,1) From Dual
Connect By Level<= Length(‘ORACLE’);

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

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

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

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

32. 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;

33. 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;

34. How to display following using query?

*

**

***

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;

35. 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');

36. How to display 1 to 100 Numbers with query?

Select level from dual connect by level <=100;

37. How to remove duplicate rows from table?

Delete FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);

38. 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;