Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Please
log in
to post a comment.
mywall
Sql Question 1
megha
Microsoft SQL Server T-SQL in 10 mn ~ Lesson 13 Subqueries vers. #5
string concatenation as aggregate operator in group by
#Temp Table fill
Loan37
[SQLS]-Split and update a column
tr3
Resolved error
Please log in to post a comment.