Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
SQL_Joins_RankingFunctions
--Sql Server 2014 Express Edition --Batches are separated by 'go' select @@version as 'sql server version' create table Department ( Id int primary key, [Name] varchar(8)) insert into Department(Id,[Name]) values (1, 'IT') ,(2, 'Finance') ,(3, 'HR') ,(4, 'Admin') ,(5, 'Network') select * from Department create table Employee(ID int identity(1,1) primary key, FirstName varchar(200), LastName varchar(200), Gender varchar(8), Salary int, DeptId int foreign key references Department(Id)) insert into Employee(FirstName, LastName, Gender, Salary)values ('Rakesh', 'sharma', 'Male', 12000, 1), ('Rakesh', 'Goel', 'Male', 24000,3), ('Vinod', 'sharma', 'Male', 35000,2), ('Rajni', 'sharma', 'Male', 52000,1), ('Renuka', 'Goel', 'Male', 24000,2), ('Vishakha', 'sharma', 'Male', 35000,2) select * from Employee -- Employees and their departments select e.FirstName+ ' ' +e.LastName as Employee, d.Name as Department from Employee e Inner Join Department d on e.deptId = d.id --Employees per department select d.Name as Department, Count(e.Id) as Employees from Employee e right Join Department d on e.deptId = d.id group by d.Name, e.deptId --Departments that dont have any employees select distinct d.Name as NoEmployeeHere from Department d left outer join Employee e on e.deptId = d.id where e.deptId is NULL; -- Employees and their departments select e.FirstName+ ' ' +e.LastName as Employee, d.Name as Department from Employee e Inner Join Department d on e.deptId = d.id --Employees per department select d.Name as Department, Count(e.Name) as Employees from Employee e right Join Department d on e.deptId = d.id group by d.Name, e.deptId --Departments that dont have any employees select distinct d.Name as NoEmployeeHere from Department d left outer join Employee e on e.deptId = d.id where e.deptId is NULL ;
run
|
edit
|
history
|
help
0
check my joins and ranking functions and your feedback will be appreciated
by gauravchugh_2006@yahoo.com, 1 years ago
Please
log in
to post a comment.
Transpose group per sequence
Trabalho.Agrecação
SinghDestiny
omnichannel_example
Cursor (procedural while)
Running Total
gdfgfd
While Loop in SQL Server for Multiple Entries
Tablestudent
Libros
stackse - search stackoverflow differently
check my joins and ranking functions and your feedback will be appreciated
by gauravchugh_2006@yahoo.com, 1 years agoPlease log in to post a comment.