1, Sql Server - rextester

1

/*
create table Total_Sales(name varchar(20), sales int)

insert into Total_Sales values('John',	10)
insert into Total_Sales values('Jennifer',	15)
insert into Total_Sales values('Stella',	20)
insert into Total_Sales values('Sophia',40)
insert into Total_Sales values('Greg',	50)
insert into Total_Sales values('Jeff',	20)

select * from Total_Sales

;with cte as 
(
    select row_number() over(order by sales desc) as id, name, sales from Total_Sales
)
select t1.id,t1.name,t1.sales, sum(t2.sales) as running_total, avg(t2.sales) as running_avg
from cte t1 inner join cte t2
on t1.id>=t2.id
group by t1.id,t1.name, t1.sales
order by t1.sales desc

select sales, count(*)
from total_sales
group by sales
having count(*)>1

/*
--Finding missing number in sequence
create table Total_Sales(id int)
insert into Total_Sales values(22)
insert into Total_Sales values(23)
insert into Total_Sales values(25)
insert into Total_Sales values(26)
insert into Total_Sales values(27)
insert into Total_Sales values(28)

select * from Total_Sales

declare @maxv int, @minv int
select @maxv=max(id), @minv=min(id) from total_sales 
while @minv<=@maxv
begin
    if not exists (select id from total_sales where id=@minv)
        break
    else
        set @minv = @minv+1
end

select @minv

/*
-- positive and negative sums
create table Total_Sales(name varchar(20), sales int)

insert into Total_Sales values('John',	-10)
insert into Total_Sales values('Jennifer',	15)
insert into Total_Sales values('Stella',	20)
insert into Total_Sales values('Sophia',-40)
insert into Total_Sales values('Greg',	50)
insert into Total_Sales values('Jeff',	20)

select * from Total_Sales

select  sum(case when sales>0 then sales else 0 end) as positive, 
        sum(case when sales<0 then sales else 0 end) as negative
from total_sales
*/

/*
-- avg salary under a manager
create table employee_manager(id int, name varchar(20), salary int, managerid int)

insert into employee_manager values(10,'Anil',50000,18)
insert into employee_manager values(11,'Vikas',75000,16)
insert into employee_manager values(12,'Nisha',40000,18)
insert into employee_manager values(13,'Nidhi',60000,17)
insert into employee_manager values(14,'Priya',80000,18)
insert into employee_manager values(15,'Mohit',45000,18)
insert into employee_manager values(16,'Rajesh',90000,null)
insert into employee_manager values(17,'Raman',55000,16)
insert into employee_manager values(18,'Santosh',65000,17)

select * from employee_manager

select e1.id, e1.name, avg(e2.salary)
from employee_manager e1
inner join employee_manager e2 on e1.id = e2.managerid
group by e1.id, e1.name

/*
--Median

create table Total_Sales(name varchar(20), sales int)

select * from Total_Sales

select  max(sales)
from    (select sales, ntile(4) over (order by sales) as quartile
         from   Total_Sales) i
where quartile=2
group by quartile
*/


run \| edit \| history \| help	0