sql important queries for interview purpose

Q1.find out manager of each employee.

empid   empname           managerid

1              mukesh                3

2              rakesh                  0

3              yogi                        2

4              sanjeev                1

5              hari                       1

Ans.

        select e.empid ,e.empname,M.empname as manager   from

empM e  left outer join empM M on

 m.empid=e.managerid

empid   empname           manager

1              mukesh                yogi

2              rakesh                NULL

3              yogi                      rakesh

4              sanjeev                mukesh

5              hari                     mukesh

Q2. Find out output of this query

        select e.empid ,e.empname,M.empname as manager   from

empM e  left outer join empM M on

 e.empid=m.managerid

ans.

empid empname     manager

1     mukesh      sanjeev

1     mukesh      hari

2     rakesh      yogi

3     yogi        mukesh

4     sanjeev     NULL

5     hari        NULL

Q3.find out that manager  who has more than 1 employee under him.

empid   empname           managerid

1              mukesh                3

2              rakesh                  0

3              yogi                        2

4              sanjeev                1

5              hari                       1

Ans.

 

 declare @manager as varchar(23),@count as int

 declare @name table(empid int ,empname varchar(23),managerid  varchar(34))

 insert into @name(empid,empname,managerid) select e.empid ,e.empname,M.empname as manager   from

empM e  left outer join empM M on

 m.empid=e.managerid

 

 select @manager=managerid,@count=COUNT(managerid)from @name group by managerid having COUNT(managerid)>1

 select @manager as manager

Q4. HOW TO USE OUPUT PARAMETER IN PROCEDURE

ANS.

 create proc sp_out

 (

   @a int,

   @b int,

   @c int output

   )

   as

   begin

   set @c=@a+@b

   end

  

   declare @p as int

   exec sp_out 1,2,@p output

   select @p

   3

Q5 WHAT WOULD BE OUTPUT OF LEFT OUTER JOIN IF WE HAVE TWO TABLE LIKE THIS

1.TABLE PK

ID        NAME

1        MUKESH

2        YOGI

NULL     YOGI

 

2. TABLE JK

 

REFID       ADDRESS

NULL        NULL 

1           KANPUR

NULL        JHANSI

 

ANS.

  SELECT PK.ID,PK.NAME,JK.[ADDRESS] FROM PK LEFT OUTER JOIN JK ON

         PK.ID=JK.REFID

ID      NAME      ADDRESS

1     MUKESH     KANPUR

2     YOGI       NULL

NULL  YOGI       NULL

NULL  YOGI       JHANSI

Q6. GIVE YOUR VIEW WHETHER SP_TB  PROCEDURE

1 WILL RUN OR NOT BCZ  TEMP TABLE  IS NOT CREATED IN PROCEDURE SP_PO

 

       CREATE PROC SP_TB

         AS

         BEGIN

         CREATE TABLE #TEMP(NAME VARCHAR(23))

          EXEC SP_PO

         END

        

        

          CREATE PROC SP_PO

         AS

         BEGIN

         INSERT INTO #TEMP VALUES('MUKESH')        

         END

        

ANS THIS WILL RUN SMOOTHLY

Q7. JOINS

 

TABLE 1.  EMPM

empid   empname           managerid

1              mukesh                3

2              rakesh                  0

3              yogi                        2

4              sanjeev                1

5              hari                       1

   TABLE 2.   EMPM2

REFID    ADDRESS

1              KANPUR

TABLE 3 . EMPM3

 FID        SEX

2              MALE

 

NOW SEE THE RESULT OF QUERY

 

                                                                 LEFT OUTER JOIN        

         SELECT PM.empid,PM.empname,PM2.[ADDRESS] FROM EMPM PM LEFT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID

WILL PRODUCE THE RESULT

empid empname     ADDRESS

1     mukesh      KANPUR

2     rakesh      NULL

3     yogi        NULL

4     sanjeev     NULL

5     hari        NULL

 

NOW APPLY AGAIN INNER JOIN THEN RESULT

 

         SELECT PM.empid,PM.empname,PM2.[ADDRESS] FROM EMPM PM LEFT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID INNER JOIN

         EMPM3 PM3 ON PM.empid=PM3.FID

 

empid   empname           ADDRESS

2              rakesh                  NULL

IF  APPLY AGAIN LEFT OUTER THEN RESULT

 

         SELECT PM.empid,PM.empname,PM2.[ADDRESS],PM3.SEX FROM EMPM PM LEFT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID LEFT OUTER JOIN

         EMPM3 PM3 ON PM.empid=PM3.FID

 

empid empname     ADDRESS     SEX

1     mukesh      KANPUR      NULL

2     rakesh      NULL        MALE

3     yogi        NULL        NULL

4     sanjeev     NULL        NULL

5     hari        NULL        NULL

 

NOW WHEN WE APPLY RIGHT JOIN IN PREVIOUS TWO TABLE THEN RESULT

SELECT PM.empid,PM.empname,PM2.[ADDRESS] FROM EMPM PM RIGHT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID

empid empname    ADDRESS

1     mukesh     KANPUR

 

NOW WHEN WE APPLY LEFT JOIN IN PREVIOUS QUERY THEN RESULT

SELECT PM.empid,PM.empname,PM2.[ADDRESS],PM3.SEX FROM EMPM PM RIGHT OUTER JOIN EMPM2 PM2 ON PM.empid=PM2.REFID LEFT OUTER JOIN

         EMPM3 PM3 ON PM.empid=PM3.FID

empid empname     ADDRESS     SEX

1     mukesh      KANPUR      NULL

 

Q8. USING JOIN IN  UPDATE

UPDATE  PM1 SET PM1.EMPNAME='yogi' FROM EMPM PM1

          INNER JOIN EMPM2 PM2 ON PM1.empid=PM2.REFID

 Q9. how to use cursor.

sql query Copying table data to another table that should not be repeating terms using Cursor

sql query Copying table data to another table that should not be repeating terms using Cursor


DECLARE @ACCOUNTID BIGINT
DECLARE @NAME VARCHAR(30)
DECLARE @TEB TABLE(NAME VARCHAR(34),NUM BIGINT)
DECLARE @GETACCOUNTID CURSOR
SET @GETACCOUNTID = CURSOR FOR
SELECT NAME, MBNO
FROM EMP1
OPEN @GETACCOUNTID
FETCH NEXT
FROM @GETACCOUNTID INTO @NAME,@ACCOUNTID
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM @TEB WHERE NAME=@NAME AND NUM=@ACCOUNTID)
zzBEGIN
DECLARE @P AS BIGINT
INSERT INTO @TEB VALUES(@NAME,@ACCOUNTID)
END


FETCH NEXT
FROM @GETACCOUNTID INTO @NAME,@ACCOUNTID
END
CLOSE @GETACCOUNTID
DEALLOCATE @GETACCOUNTID
SELECT* FROM @TEB

Q10. how to use case statement in sql
ans.
          
select  name,case(age) when '23' then '101'
when '27'then '102'
else
'mukesh'
end              

from emp

Q11.HERE WE HAVE A TABLE LIKE THIS

TABLE EMP12

name      gender
mukesh     m
yogi       f
rakesh     m

UPDATE THE TABLE OF COLUMN GENDER WHERE M THEN F AND WHERE F THERE M

ANS 


UPDATE emp12

SET
gender = CASE
WHEN gender = 'm'
THEN 'f'
ELSE 'm'
END