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

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)

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.

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

Yes. The last query is absolutely wrong.

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

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

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)

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

Very Good Collection. Thanks a lot!!!

nice collection!!!

this is realy helpful in interviews

ryan9dec15@gmail.com

PDF Please

nice collection of questions

Hi,

Nice Collection...

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

Viveksarathi.ss@gmail.com

lokesh9004@gmail.com

PDF pls

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

pdf pls.

sujata.ssn@gmail.com

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

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.

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

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

Great information thanks

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