JOIN QUERY ON SQL

EMP TABLE

DEPT TABLE

1. Display Id and FN in EMP table also display the DNAME in DEPT table

Query:-

SELECT [dbo].[EMP].id,

[dbo].[EMP].FN,

[dbo].[DEPT].DNAME

FROM [dbo].[EMP]

LEFT JOIN [dbo].[DEPT]

ON [dbo].[EMP].id= [dbo].[DEPT].ID

Output:-

RESULT

2. Display sum of salary but salary is greater than 75.

Query:-

SELECT SUM ([SAL])AS TOTAL_SAL

FROM [dbo].[EMP]

WHERE [SAL] > 75

Output:-

RESULT

3. Display the below table:-

RESULT

Query:-

SELECT E.[id],

E.[FN],

E.[LN],

E.[MGR_ID],

E1.[FN]

FROM [dbo].[EMP] E

left join [dbo].[EMP] E1

ON E.[MGR_ID]= E1.[id]

4. Display DNAME according to Employee

Query:-

SELECT [dbo].[EMP].FN,

[dbo].[EMP].LN,

[dbo].[DEPT].DNAME

FROM [dbo].[EMP]

inner join [dbo].[DEPT]

Get Vaishali’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

on [dbo].[EMP].id= [dbo].[DEPT].ID

Output:-

RESULT

5. Display count according to DNAME

Query:-

select count([dbo].[DEPT].ID) as count,

[dbo].[EMP].id,

[dbo].[EMP].FN,

[dbo].[DEPT].ID,

[dbo].[DEPT].[DNAME]

from [dbo].[EMP]

left join [dbo].[DEPT]

on [dbo].[EMP].id = [dbo].[DEPT].ID

group by [dbo].[DEPT].ID,[dbo].[EMP].FN, [dbo].[EMP].id, [dbo].[DEPT].[DNAME]

Output:-

RESULT

6. Display FN and LN department wise

Query:-

select [dbo].[EMP].FN,

[dbo].[EMP].LN,

[dbo].[DEPT].DNAME

from [dbo].[EMP]

inner join [dbo].[DEPT]

on [dbo].[EMP].id = [dbo].[DEPT].ID

group by [dbo].[DEPT].DNAME,[dbo].[EMP].FN,[dbo].[EMP].LN,[dbo].[DEPT].ID

order by [dbo].[DEPT].[ID]

Output:-

RESULT

7. Display distinct record from MGR_ID and as per MGR_ID display total of salary.

Query:-

SELECT distinct([MGR_ID]),

SUM([SAL]) AS TOTAL

FROM [dbo].[EMP]

GROUP BY [MGR_ID],[SAL]

Output:-

RESULT

If you are new to database learning — SQL Server recommended is the following must-watch the video: -