SQLCTE.sql

create database cte
use cte 



CREATE TABLE employee
(
    eid INT PRIMARY KEY,
    ename VARCHAR(50),
    esalary INT,
    department VARCHAR(30)
);

INSERT INTO employee VALUES
(1,'Anjali',25000,'HR'),
(2,'Suresh',18000,'IT'),
(3,'Riya',32000,'Finance'),
(4,'Amit',20000,'Marketing'),
(5,'Neha',27000,'IT');

select * from employee 

---CTE ( Common Table Expression)
--- Syntax 

--- create CTE to find employee with salary > 21000
---WITH CTEname AS 
--(
-- Select * from employee
--)
WITH empsalary AS 
(
 Select * from employee 
 where esalary > 21000
)
select * from empsalary

--- create CTE to find out total salary department wise 
WITH Deptsalary as 
(
Select department,Sum(esalary) AS totalsalary 
from employee 
group by department 
) 
select * from deptsalary 

WITH Deptsalary as 
(
Select department,Sum(esalary) AS totalsalary 
from employee 
group by department 
) 
select * from deptsalary where department = 'it'

--- to find emp with highest salary department wise 
WITH HighestEmpSalary AS 
(
Select EID,Ename,Department,Esalary,
Row_NUmber() Over ( partition by department order by esalary desc) as rownum
from employee
)
Select * from highestempsalary where rownum = 1

---to find employees having salary greater than the average 
WITH AvgSalary AS 
(
 Select department, avg(esalary) as deptavg
 from employee 
 group by department 
),
highsalarythenavg AS
(
Select E.EID,E.ENAME ,E.DEPARTMENT,E.ESALARY
FROM EMPLOYEE E 
JOIN AVGSALARY A 
ON A.DEPARTMENT = E.DEPARTMENT 
WHERE E.ESALARY > A.DEPTAVG 
)
select * from highsalarythenavg

Select * from AvgSalary 

---difference between cte and subqueries 
--- extremly less space 
--- use to improve readability , it allows recursive ,usability 
--- Always starts with 'WITH' Keyword 
--- Always ends with Select * from CTE 
--- ctes always works with alias 
--- CTES are known as Temporary tables 
--- It execute only with CTE function 


---FUNCTION ( OFFSET ) it always works with order by 
Select Top 1 * from employee 
order by esalary desc 

---if u want only second record , u will use offset 
--offset - to skip the number of rows 
--- fetch next - to retreive the number of rows 
-- syntax 
-- Select * from tablename 
-- order by tablename 
---offset 'numofrowstoskip' row 
---fetch next 'numofrowstoretrive' row only 
Select  * from employee 
order by esalary desc 
offset 1 row 
Fetch next 1 row only 

-- create cte to find the highest sellling product from each city 
--- create cte to find top 1 customer from each city 
----create cte to find employees with lower salary as compare to department average 
--- create cte to find 4,5 employees with highest salary