Sql Querys and Sql Interview Questions Part-2

1) How to display 10 TO 15 rows from a table?
SELECT ename
FROM Employee
WHERE rowid IN
  (SELECT rowid FROM Employee WHERE rownum<=15
  MINUS
  SELECT rowid FROM Employee WHERE rownum<10
  );
2) Difference between sql and pl/sql

     Sql                                       Pl/sql

 ----------------------------             ------------------------------
  Single query or command execution   Full programming language
  Data source for reports, web pages   Application language to build, format and display report, web pages
  Declarative in nature                   Procedural in nature
  Used for Manipulating data           Creating applications

3) write a query to find employees with age greater than 25? 

    select * from Employee where DateDiff(year,dob, GetDate()) >25

4) write a query to find 3rd, 5th or 6th i.e. N'th highest Salary?

    SELECT * FROM EMPLOYEE E WHERE 4 = (SELECT COUNT(DISTINCT E1.SALARY) FROM EMPLOYEE E1 WHERE E1.SALARY>E.SALARY)

     Here, 4= 5-1 i.e. N-1 ;

5) write a query to find second highest salary?

      Select max(Sal) from Employee where Sal not in (Select max(Sal) from employee)

6) write a query to find duplicate records in Employee table?  

     SELECT EMPID,EMPNAME, SAL, COUNT(*) AS Count  FROM EMPLOYEE GROUP BY EMPID,EMPNAME, SAL HAVING COUNT(*)>1

7) write a qyery to get the list of employees with same salary? 

    Select distinct e.empid,e.empname,e.salary from Employee e, Employee e1 where e.salary =e1.salary and e.empid != e1.empid

8) write a query to get the maximum salary from each department ?

    select DeptId, max(sal) as Sal from Employee group by deptid

9) Write a query to get employees whose ID is even?

    select * from Employee where empid %2 =0

10) Write a query to get employees whose ID is an odd number?

     select * from Employee where empid %2 !=0

11) write a query to print prime numbers from 1 to 100?

     DECLARE @i INT,@a INT,@count INT,@result varchar(Max)

     SET @i = 1
     set @result=''

    WHILE (@i <= 100)
     BEGIN
          SET @count = 0
          SET @a = 1

  -- logic to check prime number
      WHILE (@a <= @i)
      BEGIN
         IF (@i % @a = 0)
            SET @count = @count + 1

         SET @a = @a + 1
      END

      IF (@count = 2)
         set @result = @result+cast(@i as varchar(10))+' , '

      SET @i = @i + 1
      END

      set @result = (select substring(@result, 1, (len(@result) - 1)))
      print(@result)


12)  Write a query to print numbers from 1 to 100 without using loops?

     ;with numcte
      AS
        (
        SELECT 1 [SEQUENCE]

         UNION ALL

        SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] <100
        )

      SELECT * FROM numcte

13) What a Query to fetch first record from Employee table?

      Select * from Employee where Rownum =1;

14) Write a query to fetch last record from the table?

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

15) Write a Query to display first 5 Records from Employee table?

    Select * from Employee where Rownum <= 5;

16) Write a 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;

17) Write a query to get 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);

18) Write a query to get the list of employees whose name contains the character 'v' ?

     select * from Employee where charindex('v',Ename) > 0

19) Write a query to get the list of of Employees whose job starts with characters MAN ?

     select * from Employee where Left(job,3)='MAN'

20) Write a query to get the list of Employees whose names third character is 'I' ?

   select * from Employee where Right(Left(Ename,3),1)='I'

21) Write a query to get the list of Employees whose names third character is 'I' using SubString function ?

    select * from Employee where substring(Ename,3,1)='I'

22) Write a query to findout the years of Experience each Employee has in the organization ?

     select * from Employee DateDiff(yy,HireDate,GetDate()) as Years from Employee

23) Write a query to get the list of Employees whose have an experience of 25 and more years ?

    select * from Employee DateDiff(yy,HireDate,GetDate()) >=

24) Write a query to get the list of employees who have subordinates under them ?

     using Sub query
    -----------------
        Select * from Employee Where EmpNo in (select Distinct mgr from Employee)

     Co-related sub query
    --------------------
       select * fron Employee E where Exists (Select * from Emp m where E.EmpNo=M.Mgr)

25) Write a query to the details of the department in which no employee is working ?

     select * from Dept D where not exists (select * from Emp E where E.Deptno=D.Deptno)

26) Write a query to get the details of departments under which Employee's are working ?

      Using Sub query
    -------------------

      select * from Dept where DeptNo in (select Distinct DeptNo from Employee)

     Co-related Sub query
    ---------------------

      select * from Dept D where exists (select * from Employee E where E.Deptno=D.Deptno)

27) Write a query to get the highest salaryed Employee details from the Employee table?

     select * from Employee E where 0=(select count(sal) from Employee where sal >E.sal)

28) Write a query to get the details of the Employees whose joined most recently in each dept ?

    select 8 from Employee where cast (Deptno as varchar)+' '+cast(hiredate as varchar) in (select cast(Deptno as varchar)+' '+cast(max(hiredate) as varchar) from     Employee group by Deptno

29) Write a query to get the details most senior employees in the organization ?

   select * from Employee Where hiredate=(select max(hiredate) from Employee)

30) Write a query to get the list salary Employee in each job ?

   select * from Employee where job+' '+cast(sal as varchar) in (select  job+' '+cast(min(sal) as varchar) from Employee group by job

31) Write a query to get the details of employees who are earning the highest salary in each department ?

  select * from Employee where sal in (select max(sal) from Employee group by Deptno)

32) Write a query to get the details of employee's who are earning less than the highest salary of DeptNo=10 ?

     select 8 from Employee where sal<(select max(sal) from Employee where DeptNo=10)

33) Write a query to get the list of employees who are earning less than the lowest salary of DeptNo=20 ?

     select 8 from Employee where sal<(select min(sal) from Employee where DeptNo=20)

34) Write a query to get the list of employees working in hyderabad ?

    select * from Employee DeptNo=(select DeptNo from Dept where Loc='hyderabad')

35) Write a query to get the details of employees whose earning more than highest salary of DeptNo 30 ?

    select * from Employee where sal > (select max(sal) from Employee where DeptNo=30)

           or

    select * from Employee where sal > all (select sal from Employee where DeptNo=30)

36)  Write a query to get the list of employees working in sales deparment ?

    select * from Employee DeptNo=(select DeptNo from Dept where DName='sales')

37) Write a query to get the details of second highest employee salary details ?

     select * from Employee where sal =(select max(sal) from Employee where sal <(select max(sal) from Employee))

38) Write a query to get the details of the Employees whose is earning the highest salary ?

     select * from Employee where sal =(select max(sal) from Employee)

39) Write a query to findout the highest salary in the organization ?

    select max(sal) from Employee

40) Write a query to get the second highest salary in the organization ?

   select max(sal) from Employee where sal < (select max(sal) from Employee)

About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

3 comments

  1. Good very good keep it up srinivas.........

    ReplyDelete
  2. Salaam,

    Gasping at your brilliance! Thanks a tonne for sharing all that content. Can’t stop reading. Honestly!

    Have a user who is db_owner of a database but they cannot access it. I thought to drop the user and recreate, however this is not possible because of the error shown in the subject.
    There is a login at the instance level but no user in the database, however 'dbo' has this user as it's login.
    But nice Article Mate! Great Information! Keep up the good work!

    Merci

    ReplyDelete