Poziom zaawansowany

Zadanie 3

  1. Utwórz tabelę ProductDiscount (ProductID, UnitPrice, Discount). Klucz główny ustaw na ProductID, a pole Discount domyślnie ma wartość 0.

Utwórz również tabelę ArchiwumPD (ProductID, OldDiscount, NewDiscount).

  1. Wypełnij tabelę ProductDiscount danymi z tabeli Products (ProductID, UnitPrice).
  2. Ustal rabat 9% dla 10 najdroższych produktów, oraz 6% dla kolejnych pięciu. Zarówno nowe jak i stare wartości rabatu wraz z id produktu przenieś do tabeli ArchiwumPD.
  3. Dodaj kolumnę NewPrice, która przechowywać będzie cenę produktu po rabacie. Jeśli produkt nie ma przyznanego rabatu, w polu NewPrice widnieć powinno zero.
  4. Przywróć wcześniejsze wartości rabatów dla produktów 7-12 (wzgl. ceny).

Baza: Northwind, Tabele: dbo.ProductDiscount, dbo.ArchiwumPD, Kolumny: ProductID, UnitPrice, Discount, NewPrice, OldDiscount, NewDiscount

 

 

 

 

 

 

 

 

Rozwiązanie
  1. CREATE TABLE dbo.ProductDiscount (

ProductID INT PRIMARY KEY,

UnitPrice INT NOT NULL,

Discount NUMERIC(4,3) DEFAULT 0

);

CREATE TABLE dbo.ArchiwumPD (

ProductID INT PRIMARY KEY,

OldDiscount NUMERIC(4,3),

NewDiscount NUMERIC(4,3)

);

  1. INSERT INTO dbo.ProductDiscount (ProductID, UnitPrice)

SELECT ProductID, UnitPrice

FROM dbo.Products;

  1. Dla pierwszych 10:

WITH CTE AS (

SELECT ProductID, UnitPrice, Discount

FROM dbo.ProductDiscount

ORDER BY UnitPrice DESC

OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

)

UPDATE CTE

SET Discount = 0.09

OUTPUT

deleted.ProductID,

deleted.Discount,

inserted.Discount

INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount);

I kolejne 5:

WITH CTE AS (

SELECT ProductID, UnitPrice, Discount

FROM dbo.ProductDiscount

ORDER BY UnitPrice DESC

OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

)

UPDATE CTE

SET Discount = 0.06

OUTPUT

deleted.ProductID,

deleted.Discount,

inserted.Discount

INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount);

Lub z wykorzystaniem tabeli pochodnej (analogicznie będzie przy 10 najdroższych produktach):

UPDATE dbo.ProductDiscount

SET Discount = 0.06

OUTPUT

deleted.ProductID,

deleted.Discount,

inserted.Discount

INTO dbo.ArchiwumPD(ProductID, OldDiscount, NewDiscount)

WHERE ProductID IN (SELECT ProductID

FROM dbo.ProductDiscount

ORDER BY UnitPrice DESC

OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY);

 

  1. ALTER TABLE dbo.ProductDiscount

ADD NewPrice NUMERIC(5,2);

UPDATE dbo.ProductDiscount

SET NewPrice = UnitPrice*(1 – Discount);

UPDATE dbo.ProductDiscount

SET NewPrice = 0

WHERE Discount = 0;

  1. WITH CTE AS (

SELECT PD.Discount as Disc, APD.OldDiscount as OldDisc

FROM dbo.ProductDiscount as PD LEFT JOIN dbo.ArchiwumPD as APD

on PD.ProductID = APD.ProductID

ORDER BY UnitPrice DESC

OFFSET 6 ROWS FETCH NEXT 6 ROWS ONLY

)

UPDATE CTE

SET Disc = OldDisc;

I aktualizujemy ceny w kolumnie NewPrice:

UPDATE dbo.ProductDiscount

SET NewPrice = UnitPrice*(1 – Discount);

UPDATE dbo.ProductDiscount

SET NewPrice = 0

WHERE Discount = 0;

[collapse]

Alexandretta

Kobieta gracz. Po godzinach pracy, z zapałem i piekielnymi ognikami w oczach biega po lochach i tłucze bogu ducha winne zombie czy inne szkielety. Miłośniczka wszelkiej maści cRPG. Notoryczne problemy z wyborem klasy i rasy, bo wszystko fajne... W chwilach zwątpienia zatraca się przy dźwiękach mieczy, roztrzaskiwanych tarcz i okrzyków bojowych, słuchając wiking metalu z zimnej, niegościnnej, odległej Skandynawii. Czasem zdarzy jej się pograć w jakąś strategię bądź nawet w FPP, ale tylko na easy, żeby nie psuć sobie niepotrzebnie nerwów.

Related Articles

Back to top button