JOIN : SQL JOIN QUERIES INTERVIEW QUESTIONS AND ANSWERS PDF SET-6

MS SQL SERVER JOINS RELATED INTERVIEW QUERIES
Here we come with latest/new sql server joins queries, as you know joins related queries/questions are most frequently asked in any database related interview to puzzle developers. So this set of interview question contains basic joins related interview question which can be asked in any company interview, And Company like TCS/HCL/Infosys/Nagarro mostly asked following type of queries. So before attend interview be prepare for it. This set of question is for experienced developers(2-3 years).
Upcoming set will contain more complex joins related interview questions which you will face time.
I promise you will be surprise to see next set of join queries, because you will face it first time.
At last you will be able to download all query in pdf format. So start from basic.

Related Tables:




SQL JOINS RELATED INTERVIEW QUERIES

51. Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for those employee which have assigned project already.
Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A INNER JOIN [ProjectDetail] ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName

52. Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee even they have not assigned project.
Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName

53(35.1) Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee if project is not assigned then display "-No Project Assigned".
Ans: SELECT FirstName, ISNULL(ProjectName,'-No Project Assigned') FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName













54. Get all project name even they have not matching any employeeid, in left table, order by firstname from "EmployeeDetail" and "ProjectDetail".
Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName


55. Get complete record(employeename, project name) from both tables([EmployeeDetail],[ProjectDetail]), if no match found in any table then show NULL.
Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A FULL OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName


56. Write a query to find out the employeename who has not assigned any project, and display "-No Project Assigned"( tables :- [EmployeeDetail],[ProjectDetail]).
Ans: SELECT FirstName, ISNULL(ProjectName,'-No Project Assigned') AS [ProjectName] FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID
WHERE ProjectName IS NULL


57. Write a query to find out the project name which is not assigned to any employee( tables :- [EmployeeDetail],[ProjectDetail]).
Ans: SELECT ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID
WHERE FirstName IS NULL


58. Write down the query to fetch EmployeeName & Project who has assign more than one project.
Ans: Select EmployeeID, FirstName, ProjectName from [EmployeeDetail] E INNER JOIN [ProjectDetail] P
ON E.EmployeeID = P.EmployeeDetailID
WHERE EmployeeID IN (SELECT EmployeeDetailID FROM [ProjectDetail] GROUP BY EmployeeDetailID HAVING COUNT(*) >1 )


59. Write down the query to fetch ProjectName on which more than one employee are working along with EmployeeName.
Ans: Select P.ProjectName, E.FName from ProjectDetails P INNER JOIN EmployeeDetails E
on p.EmployeId = E.Id where P.ProjectName in(select ProjectName from ProjectDetails group by ProjectName having COUNT(1)>1)


Click here for next set(MORE THAN 100 QUERIES)

Comments

  1. The query is wrong, the corrected one is:

    select P.ProjectName, E.FName
    from ProjectDetails P
    inner join EmployeeDetails E
    on p.EmployeId = E.Id
    where P.ProjectName in(select ProjectName from ProjectDetails group by ProjectName having COUNT(1)>1)

    ReplyDelete
  2. how it is wrong? U have done it on the bases of project name,
    I have done it on the bases of employed, main thing is count so here where condition can differ.

    ReplyDelete
  3. Naresh was mentioning your last query. Its wrong. Compare it with your first query, both are same. Please correct.

    ReplyDelete
  4. Yes. The last query is absolutely wrong.

    ReplyDelete
  5. @All Above Thanks for correction...
    I have update query...

    ReplyDelete
    Replies
    1. WITH CTE AS(
      SELECT FIRSTNAME,PROJECTNAME,COUNT(FIRSTNAME) OVER(PARTITION BY PROJECTNAME ) CNT FROM EMPLOYEEDETAILS
      INNER JOIN PROJECTDETAIL ON EMPLOYEEDETAILSID=EMPLOYEEID
      )
      SELECT FIRSTNAME,PROJECTNAME FROM CTE WHERE CNT>1

      Delete
    2. Which query is wrong? Where is corrected one?

      Delete
  6. create table a
    (
    id int
    )
    create table b
    (
    id int
    )

    insert into a values(1)

    insert into a values(1)

    insert into b values(1)
    insert into b values(1)
    insert into b values(1)


    select a.id,b.id
    From a
    full outer join b on b.id=a.id


    select id From a
    union all
    select id From b

    Create table table_a
    (
    id int,
    Name varchar(20)
    )
    Create table table_b
    (
    id int,
    Name varchar(20)
    )



    Insert into table_a values(1,'Vikas Alwat')
    Insert into table_a values(2,'Sachin Aggrawal')
    Insert into table_a values(3,'Manoj Kumar')

    Insert into table_b values(1,'Vikas Alwat')
    Insert into table_b values(4,'Sanjay Kumar')
    Insert into table_b values(5,'Sachin Aggrawal')
    Insert into table_b values(3,'Sandeep Kumar')

    select * from table_a
    select * from table_b

    select a.id,a.Name,b.id,b.Name
    From table_a a
    inner join table_b b on b.id=a.id


    select a.id,a.Name,b.id,b.Name
    From table_a a
    inner join table_b b on b.id=a.id and b.Name=a.Name

    select a.id,a.Name,b.id,b.Name
    From table_a a
    inner join table_b b on b.id=a.id or b.Name=a.Name

    select a.id,a.Name,b.id,b.Name
    From table_a a
    inner join table_b b on b.id!=a.id

    select a.id,a.Name,b.id,b.Name
    From table_a a
    inner join table_b b on not(b.id=a.id)

    select a.id,a.Name,b.id,b.Name
    From table_a a
    inner join table_b b on a.id in (1)

    --write down the query to get record with is not avaliable in table_b of table_a

    ReplyDelete
    Replies
    1. thanx...easy to understand

      Delete
  7. Select b.id, b.Name,a.id,a.Name
    From table_a b left outer join table_b a on b.id=a.id
    Where a.id is null

    select $ --- 0.00
    SELECT COUNT(*) --1
    SELECT COUNT('7') --1
    Select 'Vikas' +1 -- Throws error "Conversion failed when converting the varchar value 'Vikas' to data type int"

    Select 'Vikas' +'1' --1

    select (Select 'Vikas') --Vikas
    select Select 'Vikas' --Throws error "Incorrent syntax near the keyword 'Select'"

    select * from 'Table_a' -- Thows Error "Incorrect Syntax near Table_a"

    select * from table_a,table_b --prodcut of 2 tables.

    select count(*) +count(*) --2

    select 'Vikas' from table_a -- Display as many as rows in table_a

    select sum(1+2*3) --7
    select max(1+2*3) --7
    select MAx(1,2,3) --Throws error "The max functions requires 1 arguments"

    select Max('Vikas') --Vikas

    select Count(Select id from table_a) --Throw error "Incorrect syntax near the keyword 'Select'"

    Create table EmployeeDetails
    (
    EmployeeID int identity(1,1) primary key,
    FirstName varchar(25),
    LastName varchar(25),
    Salary money,
    JoiningDate Datetime,
    Department varchar(25),
    Gender varchar(10)
    )

    Create table ProjectDetail
    (
    ProjectDetailId int identity(1,1),
    EmployeeDetailsId int,
    ProjectName varchar(50)
    )

    alter table ProjectDetail
    add constraint EmployeeDetailsID_ref foreign key (EmployeeDetailsId) references EmployeeDetails(EmployeeID)

    ReplyDelete
  8. insert into EmployeeDetails (FirstName, LastName, Salary, JoiningDate, Department, Gender) Values('Vikas','Ahlawat',600000,'02-15-2013','IT','Male')
    insert into EmployeeDetails (FirstName, LastName, Salary, JoiningDate, Department, Gender) Values('nikita','Jain',530000,'01-09-2014','HR','Female')
    insert into EmployeeDetails (FirstName, LastName, Salary, JoiningDate, Department, Gender) Values('Ashish','Kumar',1000000,'01-09-2014','IT','Male')
    insert into EmployeeDetails (FirstName, LastName, Salary, JoiningDate, Department, Gender) Values('Nikhil','Sharma',480000,'01-09-2014','HR','Male')
    insert into EmployeeDetails (FirstName, LastName, Salary, JoiningDate, Department, Gender) Values('asish','Kadian',500000,'01-09-2014','Payroll','Male')

    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(1,'Task Track')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(1,'CLP')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(1,'Survey Management')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(2,'HR Management')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(3,'Task Track')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(3,'GRS')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(3,'DDS')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(4,'HR Management')
    insert into ProjectDetail (EmployeeDetailsId,ProjectName) values(6,'GL Management')


    select * from EmployeeDetails
    select * from ProjectDetail

    --51. Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for those employee which have assigned project already.
    select a.EmployeeID,FirstName,LastName,ProjectName
    From EmployeeDetails a
    inner join ProjectDetail b on b.EmployeeDetailsId=a.EmployeeID

    --52. Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee even they have not assigned project.
    select a.EmployeeID,FirstName,LastName,ProjectName
    From EmployeeDetails a
    left outer join ProjectDetail b on b.EmployeeDetailsId=a.EmployeeID

    --53(35.1) Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee if project is not assigned then display "-No Project Assigned".
    select a.EmployeeID,FirstName,LastName,isnull(ProjectName,'"-No Project Assigned"')
    From EmployeeDetails a
    left outer join ProjectDetail b on b.EmployeeDetailsId=a.EmployeeID

    --54. Get all project name even they have not matching any employeeid, in left table, order by firstname from "EmployeeDetail" and "ProjectDetail".
    select a.EmployeeID,FirstName,LastName,ProjectName
    From EmployeeDetails a
    right outer join ProjectDetail b on b.EmployeeDetailsId=a.EmployeeID

    --55. Get complete record(employeename, project name) from both tables([EmployeeDetail],[ProjectDetail]), if no match found in any table then show NULL.
    select a.EmployeeID,FirstName,LastName,ProjectName
    From EmployeeDetails a
    full outer join ProjectDetail b on b.EmployeeDetailsId=a.EmployeeID

    ReplyDelete
  9. Very Good Collection. Thanks a lot!!!

    ReplyDelete
  10. nice collection!!!

    ReplyDelete
  11. this is realy helpful in interviews

    ReplyDelete
  12. ryan9dec15@gmail.com

    PDF Please

    ReplyDelete
  13. Hi,

    Nice Collection...

    Could you Please mail the pdf to itspaladuguraju@gmail.com

    ReplyDelete
  14. lokesh9004@gmail.com

    PDF pls

    ReplyDelete
  15. HI I NEED MORE EXAMPLES TO PRACTICE
    CAN YOU MAIL TO MY ID :sadana.5b2@gmail.com

    ReplyDelete
  16. Kinly send me pdf file to below id
    njadhav03@gmail.com

    ReplyDelete
  17. Great article about sql joins with example, Nice work, Thank you for publishing this article.
    Visit Dot Net Tricks to learn difference between different type of sql joins.

    ReplyDelete
  18. FOR 59.

    WITH CTE AS(
    SELECT FIRSTNAME,PROJECTNAME,COUNT(FIRSTNAME) OVER(PARTITION BY PROJECTNAME ) CNT FROM EMPLOYEEDETAILS
    INNER JOIN PROJECTDETAIL ON EMPLOYEEDETAILSID=EMPLOYEEID
    )
    SELECT FIRSTNAME,PROJECTNAME FROM CTE WHERE CNT>1

    ReplyDelete
  19. dear sir/mam
    plz write a query to get a salary colom from emptable to project detail table...plz do needful

    ReplyDelete
    Replies
    1. As per my understanding you want to join both table to show project and salary together...
      Select ProjectName,Salary FROM EmployeeDetil E INNER JOIN ProjectDetail ON E.employeedetailid = p.employeeid

      Delete
  20. could you please share me pdf @shabashi2482@gmail.com

    ReplyDelete

Contact Form

Send