SQL-practicing

[el-abed]

Transitioning from the physics world to the data one, learning SQL requires practicing the language. Thus, here I am storing all code or documents related to SQL in this repository based on different youtubers

[el-abed]

Table 1 Query:
Create Table EmployeeDemographics 
(EmployeeID int, 
FirstName varchar(50), 
LastName varchar(50), 
Age int, 
Gender varchar(50)
)

Table 1 Insert:
Insert into EmployeeDemographics VALUES
(1001, 'Jim', 'Halpert', 30, 'Male'),
(1002, 'Pam', 'Beasley', 30, 'Female'),
(1003, 'Dwight', 'Schrute', 29, 'Male'),
(1004, 'Angela', 'Martin', 31, 'Female'),
(1005, 'Toby', 'Flenderson', 32, 'Male'),
(1006, 'Michael', 'Scott', 35, 'Male'),
(1007, 'Meredith', 'Palmer', 32, 'Female'),
(1008, 'Stanley', 'Hudson', 38, 'Male'),
(1009, 'Kevin', 'Malone', 31, 'Male')

[el-abed]

/*

Today's Topic: Subqueries (in the Select, From, and Where Statement)

*/

Select EmployeeID, JobTitle, Salary
From EmployeeSalary

-- Subquery in Select

Select EmployeeID, Salary, (Select AVG(Salary) From EmployeeSalary) as AllAvgSalary
From EmployeeSalary

-- How to do it with Partition By
Select EmployeeID, Salary, AVG(Salary) over () as AllAvgSalary
From EmployeeSalary

-- Why Group By doesn't work
Select EmployeeID, Salary, AVG(Salary) as AllAvgSalary
From EmployeeSalary
Group By EmployeeID, Salary
order by EmployeeID

-- Subquery in From

Select a.EmployeeID, AllAvgSalary
From 
    (Select EmployeeID, Salary, AVG(Salary) over () as AllAvgSalary
     From EmployeeSalary) a
Order by a.EmployeeID

-- Subquery in Where

Select EmployeeID, JobTitle, Salary
From EmployeeSalary
where EmployeeID in (
    Select EmployeeID 
    From EmployeeDemographics
    where Age > 30)