-- 1
select Bezeichnung from Dim.ProduktTyp
order by Bezeichnung asc;
-- 2
select Bezeichnung from dim.ProduktTyp
where Bezeichnung like '%Mountain%'
order by Bezeichnung asc;

select B.bezeichnung as Mountain
,COUNT(p.produktid) as [Anzahl Produkte]
from Dim.ProduktTyp as b
left join Stamm.Produkt as p
on b.ProduktTypID = p.ProduktTypID
where b.Bezeichnung like '%Mountain%'
group by b.Bezeichnung
order by [Anzahl Produkte] DESC;
-- 4
select B.bezeichnung as Mountain
,COUNT(p.produktid) as [Anzahl Produkte]
from Dim.ProduktTyp as b
left join Stamm.Produkt as p
on b.ProduktTypID = p.ProduktTypID
where b.Bezeichnung like '%Mountain%'
group by b.Bezeichnung
having count(p.produktid) = 0
order by [Anzahl Produkte] DESC;
--5
select DENSE_RANK() over (order by COUNT(p.produktid)DESC)AS RANG ,
B.bezeichnung as Mountain
,COUNT(p.produktid) as [Anzahl Produkte]
from Dim.ProduktTyp as b
left join Stamm.Produkt as p
on b.ProduktTypID = p.ProduktTypID
where b.bezeichnung like '%Mountain%'
group by b.Bezeichnung;

-- 2/1
select Bezeichnung from dim.ProduktKategorie
order by Bezeichnung asc;
-- 2/2
select
count(*) as [Anzahl Kategorien]
from dim.ProduktKategorie
-- 2/3
select bezeichnung from dim.ProduktKategorie
where OberKategorieID IS NULL
-- 2/4
select
count(*) as [Anzahl Oberkategorien] from dim.ProduktKategorie
where OberKategorieID IS NULL
group by OberKategorieID

-- 2/5
select K.bezeichnung as Oberkategorie,
B.bezeichnung as unterkategorie
from Dim.ProduktKategorie as k
join Dim.ProduktKategorie as b
on b.OberKategorieID = k.ProduktKategorieID
order by Oberkategorie, unterkategorie;
--2/6
select K.bezeichnung as oberkategorie,
B.bezeichnung as Unterkategorie
,COUNT(p.produktid) as [Anzahl Produkte]
from Dim.ProduktKategorie as k
join Dim.ProduktKategorie as b
on b.OberKategorieID = k.ProduktKategorieID
left join Stamm.Produkt as p
on p.ProduktKategorieID = b.ProduktKategorieID
group by k.Bezeichnung, b.Bezeichnung
order by oberkategorie, Unterkategorie
 -- 2/8
Select top 1 b.bezeichnung as unterkategorie
from dim.produktkategorie as a
join dim.produktkategorie as b
on b.oberkategorieid = A.produktkategorieid
left join stamm.produkt as p
on P.produktkategorieid = b.produktkategorieid
group by a.bezeichnung, b.bezeichnung
order by COUNT(p.produktid) desc;
-- 2/9
with unterabfrage1 as (
select b.bezeichnung AS OBERKATEGORIE
, u.bezeichnung as unterkategorie
, count(p.produktid) as [Anzahl Produkte]
from dim.produktkategorie as b
join dim.produktkategorie as u
on u.oberkategorieid = b.oberkategorieID
left join stamm.produkt as p
on p.produktkategorieid = u.produktkategorieId
group by b.bezeichnung, u.bezeichnung
),
Unterabfrage2 as ( Select top 1 unterkategorie
from unterabfrage1
order by [Anzahl Produkte] desc
)
SELECT p.farbe
, p.groesse as Größe
, p.bezeichnung as ProduktBezeichnung
, p.produktnummer
, p.verkaufspreis
from stamm.produkt as p
join dim.produktkategorie as k
on p.produktkategorieid = k.produktkategorieid
where k.bezeichnung = (Select unterkategorie from Unterabfrage2)
order by farbe,größe,Produktbezeichnung;

-- 2/10
select format(sum(d.bestellmenge*d.einzelpreis), 'N2') as [Umsatz]
from verkauf.bestelldetail as d
join stamm.produkt as p
on d.produktid = p.produktid
join dim.produktkategorie as k
on p.produktkategorieid = k.produktkategorieid
and k.bezeichnung = 'Mountain Bikes';
-- 2/11
select year(b.bestelldatum) as Geschaeftsjahr
,format(sum(d.bestellmenge*d.einzelpreis), 'N2') as [Umsatz]
from verkauf.bestellung as b
join verkauf.bestelldetail as d
on b.bestellungid = d.bestellungid
join stamm.produkt as p
on d.produktid = p.produktid
join dim.produktkategorie as k
on p.produktkategorieid = k.produktkategorieid
and k.bezeichnung = 'Mountain Bikes'
group by year(b.bestelldatum);


-- 2/12
-- 2/13
-- 2/14

-- 1/3
select Stadt,
COUNT(*) as [Anzahl Kunden]  from CR.Kunde
where Land = 'Canada'
group by Stadt, Land
having COUNT(*) > 4
order by [Anzahl Kunden] desc;

-- 2/3
select firma
from cr.kunde
where stadt = (select stadt
from cr.kunde
where firma = 'futuristic bikes')
except
select 'futuristic bikes';

-- 3/3

select  b.KundenNr,  k.firma, C.Bestellmenge as [Anzahl Bestellungen],
(sum (c.Einzelpreis * c.bestellmenge)) as Umsatz
from CR.Kunde as k
join Verkauf.Bestellung as b
on k.KundenNr = b.KundenNr
join Verkauf.BestellDetail as c
on b.bestellungid = c.bestellungid
group by b.KundenNr, k.firma,c.bestellmenge
order by Umsatz desc;
-- 4/3
select bestellungid
, cast(bestelldatum as date) as Bestelldatum
from verkauf.bestellung
where versanddatum is null and year(bestelldatum) = 2018
order by bestellungid;
-- 6/3
select bestellungid
,cast(Bestelldatum as date) as bestelldatum
,cast(Versanddatum as Date) as Versanddatum
,datediff(d, Bestelldatum, Versanddatum) AS [Tage bis Versand]
, case
when datediff(d,Bestelldatum,Versanddatum) < 3 then 'gut'
when datediff(d, bestelldatum,Versanddatum) <= 5 then 'ok'
else 'schlecht'
end as Bewertung
from verkauf.bestellung
where Versanddatum is not null
and year(bestelldatum) = 2018
order by BestellungID;
-- 1/4
select p.mitarbeiterid
, p.nachname
, p.vorname
,FORMAT(sum(d.einzelpreis*d.bestellmenge),'N2') as UmsatzQ4
from personal.mitarbeiter as p
left join verkauf.bestellung as b
on p.mitarbeiterid = b.mitarbeiterid
and b.bestelldatum >= '20181001'
and b.bestelldatum < '20190101'
join verkauf.bestelldetail as d
on b.bestellungid = d.bestellungid
group by p.mitarbeiterid, p.nachname, p.vorname
order by p.nachname, p.vorname

--2/4
with umsatzdaten as(select p.mitarbeiterid
, p.nachname
, p.vorname
,sum(d.einzelpreis*d.bestellmenge) as Umsatz
from personal.mitarbeiter as p
left join verkauf.bestellung as b
on p.mitarbeiterid = b.mitarbeiterid
and b.bestelldatum >= '20181001'
and b.bestelldatum < '20190101'
join verkauf.bestelldetail as d
on b.bestellungid = d.bestellungid
group by p.mitarbeiterid, p.nachname, p.vorname)
Select mitarbeiterid
,nachname
,vorname
,format(umsatz, 'N2') as umsatz
,case
when umsatz >= 150000 then 25
when umsatz >= 100000 then 20
when umsatz >= 50000 then 10
else 5
end as Provision
,format (case
when umsatz >= 150000 then umsatz*0.25
when umsatz >= 100000 then umsatz*0.2
when umsatz >= 50000 then umsatz*0.1
else umsatz*0.05 end,'N2') as Provision
from umsatzdaten
order by nachname, vorname;
--1/5