queries.sql

-- T-SQL query practice against the BikeStores sample database

--1
DECLARE @unit varchar(1) = '-';

;with C as
(
    select id, parent, 0 as lvl,CAST(REPLICATE(@unit, 2 + 4 * 0) + txt as varchar(400)) as formatted
    from forum
    where parent is null

    union all

    select f.id, f.parent,c.lvl + 1,CAST(REPLICATE(@unit, 2 + 4 * (c.lvl + 1)) + f.txt as varchar(400))
    from forum f join C on f.parent = C.id
)
select formatted
from C
order by id;


--2
select p.product_name , count(p.product_name) sumOfProducts
from production.products p
group by p.product_name
having count(p.product_name)>1


--3
;with deleteProducts as
(
select *, ROW_NUMBER() over(partition by p.product_name order by p.product_name) as rowNumber
from production.products p
)
select *
from deleteProducts
where rowNumber>1


--4
select distinct p.product_name
from sales.order_items s join production.products p
on s.product_id=p.product_id



--5
select p.product_id , s.order_id
from sales.order_items s right join production.products p
on s.product_id=p.product_id
where s.product_id is null



--6
select p.product_name , b.brand_name
from production.brands b join production.products p
on b.brand_id=p.brand_id
where b.brand_name in ('Electra','Surly','Trek')
order by b.brand_name



--7
select first_name , last_name ,email, 'c'
from sales.customers
union all
select first_name , last_name ,email, 's'
from sales.staffs s


--8
select first_name , last_name ,email
from sales.customers
where email like '%gmail%'



--9
select *
from sales.orders
where DATEDIFF(DAY,order_date,
				case
				when shipped_date is not null
				then shipped_date
				else
				 GETDATE()
				end
				) > 2;


--10
select *
from sales.orders
where order_date=EOMONTH(order_date)


--11
Declare @str varchar(50)='1,2,3,4,5,6,7'

;with lastOrder as(
select ROW_NUMBER() over(partition by c.customer_id order by order_date desc ) as rn,c.first_name,c.last_name,c.email,o.customer_id
,o.order_id,o.order_date,sas.store_name,soi.quantity,p.product_name, soi.quantity*soi.list_price*(1-soi.discount) as calPrice
from sales.orders o
join string_split(@str,',') ss on o.customer_id=ss.value
join sales.customers c on  o.customer_id=c.customer_id
join sales.stores sas on sas.store_id=o.store_id
join sales.order_items soi on soi.order_id=o.order_id
join production.products p on p.product_id=soi.product_id
)
select *
from lastOrder
where rn =1


--12
declare @cols varchar(100)='first_name,last_name,email,phone'
declare @sortedCols varchar(100)='phone,last_name'

declare @sql nvarchar(max) = 'select '+@cols+' from sales.customers order by '+@sortedCols
print @sql
exec sp_executesql @sql