-- create
CREATE TABLE EmployeeDemographics (
  EmployeeID int,
  FirstName varchar(15),
  LastName varchar(15),
  Age int,
  Gender varchar(10)
);

CREATE TABLE EmployeeSalary (
  EmployeeID int,
  JobTitle varchar(20),
  Salary int
);
-- 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'),
(1011, 'Ryan', 'Howard', 26, 'Male'),
(null, 'Holly', 'Flax', null, null),
(1013, 'Darryl', 'Philbin', null, 'Male')

INSERT INTO EmployeeSalary VALUES
(1001, 'Salesman', 45000),
(1002, 'Receptionist', 36000),
(1003, 'Salesman', 63000),
(1004, 'Accountant', 47000),
(1005, 'HR', 50000),
(1006, 'Regional Manager', 65000),
(1007, 'Supplier Relations', 41000),
(1008, 'Salesman', 48000),
(1009, 'Accountant', 42000)
-- update
update EmployeeDemographics
set EmployeeID = 1012, Age = 31, Gender = 'Female'
where FirstName = 'Holly' and LastName = 'Flax';
-- delete

-- fetch
/* select FirstName, LastName, Gender, Salary,
count(Gender) over (partition by Gender) as TotalGender
from EmployeeDemographics as dem
join EmployeeSalary as sal
on dem.EmployeeID = sal.EmployeeID */

/* with CTE_Employee as 
(select FirstName, LastName, Gender, JobTitle, 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 */

--temporary table
/*
create table #Temp_Employee (
  EmployeeID int,
  JobTitle varchar(20),
  Salary int)

insert into #Temp_Employee values 
('1001', 'HR', '45000')

insert into #Temp_Employee
select *
from EmployeeSalary

drop table if exists #Temp_Employee2
create table #Temp_Employee2 (
  JobTitle varchar(20),
  EmployeesPerJob int,
  AvgAge int,
  AvgSalary int)
  
insert into #Temp_Employee2
select JobTitle, count(JobTitle), avg(Age), avg(Salary)
from EmployeeDemographics as emp 
join EmployeeSalary as sal 
  on emp.EmployeeID = sal.EmployeeID
group by JobTitle

select *
from #Temp_Employee2
*/

--string functions (sf)
/*
create table EmployeeErrors (
  EmployeeID varchar(50),
  FirstName varchar(50),
  LastName varchar(50))

insert into EmployeeErrors values 
('1001 ', 'Jimbo', 'Halbert'),
(' 1002', 'Pamela', 'Beasely'),
('1005', 'TOby', 'Flenderson - Fired')

select *
from EmployeeErrors
*/
--sf using trim, ltrim, rtrim 
/*
select EmployeeID, trim(EmployeeID) as IDTrim
from EmployeeErrors

select EmployeeID, ltrim(EmployeeID) as IDTrim
from EmployeeErrors

select EmployeeID, rtrim(EmployeeID) as IDTrim
from EmployeeErrors
*/

--sf using replace
/*
select LastName, replace(LastName, '- Fired','') as LastNameFixed
from EmployeeErrors
*/

--sf using substring
--fuzzy match: firstname, lastname, gender, age, birthdate 
/*
select err.FirstName, substring(err.FirstName,1,3), dem.FirstName, substring(dem.FirstName,1,3)
from EmployeeErrors as err 
join EmployeeDemographics as dem
     on substring(err.FirstName,1,3) = substring(dem.FirstName,1,3)
*/

--sf using upper and lower
/*
select FirstName, lower(FirstName)
from EmployeeErrors

select FirstName, upper(FirstName)
from EmployeeErrors
*/

--stored procedures
/*
go
create procedure Temp_Employee 
as 
begin
  create table #temp_employee (
  JobTitle varchar(20),
  EmployeesPerJob int,
  AvgAge int,
  AvgSalary int)
  
  insert into #temp_employee
  select JobTitle, count(JobTitle), avg(Age), avg(Salary)
  from EmployeeDemographics as emp 
  join EmployeeSalary as sal 
       on emp.EmployeeID = sal.EmployeeID
  group by JobTitle
  
  select *
  from #temp_employee
end
go
alter procedure Temp_Employee
@JobTitle nvarchar(100)
as
begin
  create table #temp_employee (
  JobTitle varchar(20),
  EmployeesPerJob int,
  AvgAge int,
  AvgSalary int)
  
  insert into #temp_employee
  select JobTitle, count(JobTitle), avg(Age), avg(Salary)
  from EmployeeDemographics as emp 
  join EmployeeSalary as sal 
       on emp.EmployeeID = sal.EmployeeID
  where JobTitle = @JobTitle
  group by JobTitle
  
  select *
  from #temp_employee
end
go 
execute Temp_Employee @JobTitle = 'Salesman'
*/

--subqueries: query within a query in select, from, where, insert, update, delete st8ments
select *
from EmployeeSalary