INTERMEDIATE SQL QUERIES

JOIN- INNER, OUTER, RIGHT, LEFT

The EmployeeDemographics TABLE referred to in the thread contains columns EmployeeID, FirstName, LastName, Age, Gender, and the EmployeeSalary TABLE contains columns EmployeeID, JobTitle, Salary

Get Ishita Tandon’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

DIFFERENCE BETWEEN INNER, OUTER, LEFT AND RIGHT JOIN

Credits: https://commons.wikimedia.org/wiki/File:SQL_Joins.svg

INNER JOIN

SELECT *
FROM EmployeeDemographics
Inner Join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID

FULL OUTER JOIN

SELECT *
FROM EmployeeDemographics
FULL OUTER Join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID

LEFT JOIN

SELECT *
FROM EmployeeDemographics
LEFT Join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID

RIGHT JOIN

SELECT *
FROM EmployeeDemographics
RIGHT Join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID

SELECTING COLUMNS IN JOIN

SELECT EmployeeDemographics.EmployeeID, FirstName, LastName, JobTitle, Salary
FROM EmployeeDemographics
FULL OUTER Join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID

UNIONS

A union is different from joining as a union joins all the data from both tables and we do not need a common column. If two columns have the same columns it unites the data in separate columns into one

SELECT *
FROM EmployeeDemoghraphics
UNION
SELECT *
FROM WareHouseEmployeeDemographics

CASE STATEMENT

SELECT FisrtName, LastName, Age
CASE WHEN Age>30 THEN 'OLD'
     WHEN Age BETWEEN 27 AND 30 THEN 'Adult'
     ELSE 'Young'
END AS Group
FROM EmployeeDemoghraphics
WHERE Age is NOT NULL
ORDER BY Age

HAVING STATEMENT

Having is used when we want to filter data on an aggregate function

SELECT JobTitle, COUNT(JobTitle)
FROM EmployeeDemographics
JOIN EmployeeSalary
ON EmployeeDemographics.EmplyeeID=EmployeeSalary.EmplyeeID
GROUP BY JobTitle
HAVING COUNT(JobTitle)>1

UPDATING AND DELETING DATA

UPDATE EmployeeDemographics
SET EmployeeID=1012, Age=31
WHERE FirstName='Holly' AND LastName='Flax'
DELETE FROM EmployeeDemographics
WHERE EmployeeID=1005

ALIASING

This is used to enhance the readability of the script.

--To change the name of a column
SELECT FirstName AS FName
FROM EmployeeDemographics
--to name a new column
SELECT FirstName+" "+LastName AS FullName
FROM EmployeeDemographics

PARTITION BY

A partition divides by result calculated by window function

SELECT FirstName, LastName, Gender, Salary, 
COUNT(Gender) OVER (PARTITION BY Gender) as TotalGender
FROM EmployeeDemographics dem
JOIN EmployeeSalary sal
ON dem.EmployeeID=sal.EmployeeID

Hi, My name is Ishita. I am an electrical engineering undergrad at the National Institute of Technology. I took the initiative to write a blog because when I started learning programming I faced obstacles finding the right sources. Now I want to share my knowledge with everyone stepping into learning programming for Data Science to make it easier for them. Honestly, I am no expert but feel free to join me along my journey as we both learn together. You can connect with me on LinkedIn.

https://www.linkedin.com/in/ishita-tandon-247a521ab/