SQL_Intermediate

Join, union, having clause, update, delete, aliasing and partition

Initial Tables

Venn representation of join

Inner join

select FirstName,Job,Salary from EmployeeDemographics as demo
inner join EmployeeSalary as sal
on demo.EmployeeID=sal.EmployeeID

Full outer join

select FirstName,Job,Salary from EmployeeDemographics as demo
full outer join EmployeeSalary as sal
on demo.EmployeeID=sal.EmployeeID

Right outer join

select FirstName,Job,Salary from EmployeeDemographics as demo
right outer join EmployeeSalary as sal
on demo.EmployeeID=sal.EmployeeID

Left outer join

select FirstName,Job,Salary from EmployeeDemographics as demo
left outer join EmployeeSalary as sal
on demo.EmployeeID=sal.EmployeeID

Join

Join is inner join by default.

select FirstName,Job,Salary from EmployeeDemographics as demo
join EmployeeSalary as sal
on demo.EmployeeID=sal.EmployeeID

Union

New Table

-- Creating new Table to show Union.

create table EmployeeDemographics2(
EmployeeID int,
FirstName varchar(20),
LastName varchar(20),
Age int,
Gender varchar(10)
)
insert into EmployeeDemographics2 values
(1012,'Mukta','Chakraborty',21,'Female'),
(1013,'Poonam','Saha',20,'Female')

Opertion

select FirstName,Age from EmployeeDemographics
union
select FirstName,Age from EmployeeDemographics2
order by Age asc

Case statement

Case Statement is like conditional statement. It can be used to create additional columns based on logic.

select FirstName,Age,Salary,
case
when salary<50000 then salary+(salary*0.1)
else salary
end as Salary_Update
from EmployeeDemographics dem
join EmployeeSalary sal 
on dem.EmployeeID=sal.EmployeeID

Having clause

Where clause throws error while working with aggregate like min, max & count so we need to use the having clause

Use group by with having clause.

select Job,count(Job) as Vaccancies 
from EmployeeSalary
group by Job
having avg(salary)>50000
order by count(job) desc

Update

update EmployeeDemographics
set age=22 
where EmployeeID=1001

select * from EmployeeDemographics

Delete

Delete from EmployeeDemographics 
where EmployeeID=1001

select * from EmployeeDemographics

Partion

Group satements cuts down the number of rows by rolling them up but with the help of partition we can get all the rows and aggregate things corrosponding to them.

select FirstName,Gender,
count(EmployeeID) over (partition by Gender) as Total_Gender
from EmployeeDemographics

Aliasing

select FirstName+' '+LastName as Full_Name, Age 
from EmployeeDemographics

Alia singing in real life

Aliasing in Database

Bye bye.,...