ADVANCE SQL QUERIES

CTEs, Temp Tables, String Functions and Subqueries

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

CTEs: Common Table Expression

WITH CTE_Employee as 
(SELECT FirstName, LastName, Genbder, Salary, 
COUNT(Gender) OVER (PARTITION BY Gender) as TotalGender,
AVG(Salary) OVER (PARTITION BY Gender) AS AvgSalary
FROM EmployeeDemographIcs as emp 
JOIN EmployeeSalary as sal
ON emp.EmployeeID=sal.EmployeeID
WHERE Salary>'45000' 
)
SELECT *
FROM CTE_Employee

TEMP TABLES

DROP TABLE IF EXISTS #Temp_Employee
CREATE TABLE #temp_Employee (
EmployeeID int,
JobTitle varchar(100),
Salary int)
INSERT INTO #temp_Employee VALUES (
'1001', 'HR' ,' 45000')
SELECT * 
FROM #temp_Employee

STRING FUNCTIONS

CREATE TABLE  EmployeeErrors(
EmployeeID varchar(50),
FirstName varchar(50),
LastName varchar(50)
)
INSERT INTO EmplyeeErrors VALUES
('1001', 'Jimbo','Halbert')
,(' 1002','Pamela','Beasely')
,('1005','TOby','Flenderson-Fired')
--we can see they're are errors in the data so we will try using string functions to clean data
--to get rid of blank spaces
SELECT EmployeeID, TRIM(EmployeeID) as IDTRIM
FROM EmployeeErrors
--USING REPLACE
SELECT LastName, REPLACE(LastName,'-Fired','') as LastNameFixed
FROM EmployeeErrors
--USING SUBSTRING
SELECT LastName, SUBSTRING(FirstName,1,3) as LastNameFixed
FROM EmployeeErrors
--USING UPPER, LOWER
SELECT FirstName, LOWER(FirstName)
FROM EmployeeErrors
SELECT FirstName, UPPER(FirstName)
FROM EmployeeErrors

STORED PROCEDURES

Get Ishita Tandon’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

A stored procedure is a group of SQL statements that is created and then stored in the database.

CREATE PROCEDURE TEST
AS
SELECT *
FROM EmployeeDemographics 
--TO EXECUTE THE STORED PROCEDURE
EXEC TEST
--TO ALTER A PROCEDURE and ADD AN INPUT PARAMETER
ALTER PROCEDURE TEST 
@GENDER

SUBQUERIES

SELECT EmployeeID, Salary, (SELECT AVG(Salary) From EmployeeSalary) AS AllAvgSalary
FROM EmployeeSalary

We can do the same using a partition by statement

SELECT EmployeeID, Salary, AVG(Salary)OVER () AS AllAvgSalary
FROM EmployeeSalary

But if we try doing the same thing using group by, we won’t be able to do that

SUBQUERY IN FROM

SELECT a.EmployeeID, AllAvgSalary
FROM (SELECT EmployeeID, Salary, AVG(Salary)OVER () AS AllAvgSalary
FROM EmployeeSalary) AS a

SUBQUERY IN WHERE

SELECT EmployeeID, JobTitle, Salary
FROM EmployeeSalary
WHERE EmployeeID in (
      SELECT EmployeeID
      FROM EmployeeDemographics
      WHERE Age>30)

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/