Tagi: CTE

Zadanie 5.

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).

2. Wypełnij tabelę ProductDiscount danymi z tabeli Products (ProductID, UnitPrice).

3. 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.

4. 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.

5. 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

z5

 

 

 

 

 

 

 

 

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)

);

2. INSERT INTO dbo.ProductDiscount (ProductID, UnitPrice)
SELECT ProductID, UnitPrice
FROM dbo.Products;

3. 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);

4. 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;

5. 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]

Zadanie 4.

1. Skopiuj tabelę dbo.Orders za pomocą SELECT INTO (tworząc tabelę dbo.Ord2).

2. Tabeli dbo.Ord2 przypisz klucz główny na kolumnie OrderID

3. Dodaj kolumnę ShipDelay, w której określimy, czy towar został dostarczony na czas.

4. Wypełnij kolumnę ShipDelay danymi (RequiredDate – ShippedDate)

5. Zmodyfikuj tabelę wstawiając 0 (zero) tam, gdzie nie było opóźnień w dostawie.

6. Sprawdź, czy są klienci którzy dostali towar z opóźnieniem, oraz czy złożyli jeszcze jakieś zamówienia (później).

Baza: Northwind, Tabela: dbo.Orders / Ord2, Kolumny: CustomerID, OrderID, RequiredDate, ShippedDate, ShipDelay, Wynik: klienci, którzy złożyli kolejne zamówienia po otrzymaniu dostawy z opóźnieniem: 33, Klienci, którzy po opóźnionej dostawie nie złożyli już zamówień: 4, Klienci, którzy po pierwszym opóźnieniu złożyli kolejne zamówienie, ale po drugim już nie: 1

Rozwiązanie

1.

USE Northwind;

SELECT *
INTO dbo.Ord2
FROM dbo.Orders;

2.

ALTER TABLE dbo.Ord2
ADD CONSTRAINT OrderID_pk PRIMARY KEY (OrderID);

3.

ALTER TABLE dbo.Ord2
ADD ShipDelay INT;

4.

WITH CTE AS (
SELECT OrderID, CustomerID, RequiredDate, ShippedDate, ShipDelay, DATEDIFF(day, RequiredDate, ShippedDate) as DaysDelay
FROM dbo.Ord2
)

UPDATE CTE
SET ShipDelay = DaysDelay;

5.

UPDATE dbo.Ord2
SET ShipDelay = 0
WHERE ShipDelay <= 0;

Albo z użyciem wyrażeń tablicowych:

WITH CTE2 AS (
SELECT ShipDelay,
CASE
WHEN ShipDelay <= 0 THEN 0
ELSE ShipDelay
END AS SD

FROM dbo.Ord2
)

UPDATE CTE2
SET ShipDelay = SD;

6. Klienci, którzy złożyli kolejne zamówienia po otrzymaniu dostawy z opóźnieniem:

SELECT CustomerID, OrderID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
);

 

Klienci, którzy po opóźnionej dostawie nie złożyli już zamówień:

SELECT CustomerID, OrderID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND NOT EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
);

 

Klienci, którzy po pierwszym opóźnieniu złożyli kolejne zamówienie, ale po drugim już nie:

SELECT CustomerID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
)

INTERSECT

SELECT CustomerID
FROM dbo.Ord2
WHERE ShipDelay > 0
AND NOT EXISTS (
SELECT *
FROM dbo.Ord2 as O
WHERE Ord2.CustomerID = O.CustomerID
AND O.OrderID > Ord2.OrderID
);

 

[collapse]

Zadanie 25.

Policz, ile w firmie pracuje kobiet, a ilu mężczyzn. Wynik podaj również w procentach.

Baza: AdventureWorks, Tabela: HumanResources.Employee, Kolumny: Gender, Wynik: 2 rows

s25

Rozwiązanie

WITH CTE AS (
SELECT Gender, COUNT(*) as num
FROM HumanResources.Employee
GROUP BY Gender
)

SELECT Gender, num, CAST(100. * num / SUM(num) OVER() as DECIMAL(5,2)) as Pct
FROM CTE;

[collapse]

Zadanie 24.

Wcielmy w życie zasadę Pareto (zasada 80/20). Mówi ona, że tylko ok. 20% klientów firmy odpowiada za ok. 80% jej przychodów. Sprawdźmy, czy podobna relacja zachodzi i tu.

1. Utwórz zapytanie, które zwróci nazwę firmy, łączną wartość zamówień firmy, łączną ogólną wartość zamówień.

2. Zmodyfikuj zapytanie tak, by zwracało również procentowy udział wartości zamówień danej firmy.

3. Powyższe zapytanie zmodyfikuj jeszcze raz, tak, aby pokazywało również zsumowane punkty procentowe (poczynając od najwyższych wartości).

Baza: Northwind, Tabela: Sales Totals by Amount (widok), Kolumny: CompanyName, SaleAmount, Wynik: 31 rows

s24

 

 

 

 

Rozwiązanie

1. SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total
FROM dbo.[Sales Totals by Amount];

 

2. SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total,
CAST(100. * SUM(SaleAmount) OVER(PARTITION BY CompanyName) / SUM(SaleAmount) OVER() as DECIMAL(5,2)) as Pct
FROM dbo.[Sales Totals by Amount];

 

3. WITH CTE AS (
SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total,
CAST(100. * SUM(SaleAmount) OVER(PARTITION BY CompanyName) / SUM(SaleAmount) OVER() as DECIMAL(5,2)) as Pct
FROM dbo.[Sales Totals by Amount]
)

SELECT CompanyName, TotalByCust, Total, Pct, SUM(Pct) OVER(ORDER BY Pct DESC) as PctSum
FROM CTE
ORDER BY Pct DESC;

[collapse]

Zadanie 21.

Utwórz tabelę, gdzie w jednej kolumnie będą liczby od 1 do 10, a w drugiej od 10 do 1. Nie używaj pętli.

Baza: -, Tabela: -, Kolumny: -, Wynik: 10 rows

s21

 

 

 

 

 

 

 

 

Rozwiązanie

WITH CTE AS
(
SELECT 1 as x
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
)

SELECT x, ROW_NUMBER() OVER(ORDER BY x DESC) as x2
FROM CTE
ORDER BY x;

[collapse]

Zadanie 20.

1. Utwórz wspólne wyrażenie tablicowe (CTE), które zwróci nazwy miast i państw, w których są zarówno klienci jak i dostawcy.

2. W ostatecznym wyniku zapytania chcemy zobaczyć klientów i dostawców (CompanyName, SupplierID / CustomerID) z miast określonych w wyrażeniu tablicowym.

Baza: Northwind, Tabele: dbo.Suppliers, dbo.Customers, Kolumny: City, Country, CompanyName, SupplierID, CustomerID, Wynik: 14 rows

s20

 

 

 

 

 

Rozwiązanie

WITH CTE AS (

SELECT Country, City
FROM dbo.Suppliers

INTERSECT

SELECT Country, City
FROM dbo.Customers

)

SELECT s.SupplierID, s.CompanyName, s.City, c.CustomerID, c.CompanyName
FROM dbo.Suppliers as s JOIN dbo.Customers as c on s.City = c.City
WHERE s.City in (SELECT City FROM CTE);

[collapse]

Zadanie 17.

1. Utwórz wspólne wyrażenie tablicowe (CTE), nazwane SalesSummary, które wyświetli nam szereg informacji o zamówieniach (OrderID, SaleAmount, CompanyName, ProductID, ProductName, UnitPrice, Quantity, Discount, ExtendedPrice, ShippedDate). Użyj w tym celu widoków Sales Totals by Amount oraz Order Details Extended.

2. W kwerendzie zewnętrznej wybierz wszystkie kolumny tabeli SalesSummary, oraz dołóż kolumnę zawierającą ID klienta (CustomerID, z tabeli Customers).

Baza: Northwind, Tabele: dbo.Sales Totals by Amount (widok), dbo.Order Details Extended (widok), dbo.Customers, Kolumny: OrderID, SaleAmount, CompanyName, ProductID, ProductName, UnitPrice, Quantity, Discount, ExtendedPrice, ShippedDate, CustomerID, Wynik: 233 rows

s17

 

 

Rozwiązanie

1. WITH SalesSummary AS
(
SELECT s.OrderID, s.SaleAmount, s.CompanyName, o.ProductID, o.ProductName, o.UnitPrice, o.Quantity, o.Discount, o.ExtendedPrice,  s.ShippedDate
FROM dbo.[Sales Totals by Amount] as s
JOIN dbo.[Order Details Extended] as o on s.OrderID = o.OrderID
)

2. SELECT *, (SELECT c.CustomerID FROM dbo.Customers as c WHERE SalesSummary.CompanyName = c.CompanyName) as CustomerID
FROM SalesSummary
ORDER BY OrderID;

[collapse]

Zadanie 16.

Wykorzystamy tabelę wynikową uzyskaną w zadaniu 15.

1. Powyższą tabelę przekształć do postaci wspólnego wyrażenia tablicowego (CTE), nazwanego Tab.

2. W kwerendzie zewnętrznej wybierz „20% najcenniejszych klientów”: nazwę firmy, łączną wartość zamówień złożonych przez daną firmę oraz łączną wartość wszystkich zamówień złożonych przez wszystkich klientów. Interesuje nas tylko 20% firm z najwyższymi łącznymi wartościami zamówień.

Baza: Northwind, Tabele: CTE Tab, dbo.order Subtotals, Kolumny: CompanyName, Subtotal, Wynik: 18 rows

s16

 

 

 

 

Rozwiązanie

WITH Tab AS (
SELECT o.OrderID, c.CompanyName, os.Subtotal
FROM dbo.Orders as o JOIN dbo.Customers as c on o.CustomerID = c.CustomerID
JOIN dbo.[Order Subtotals] as os on o.OrderID = os.OrderID
)

SELECT TOP (20) PERCENT CompanyName, SUM(Subtotal) as 'CustSum', ( SELECT SUM(Subtotal) FROM dbo.[Order Subtotals] ) as TotalSum
FROM Tab
GROUP BY CompanyName
ORDER BY CustSum DESC;

[collapse]

Zadanie 14.

1. Utwórz wspólne wyrażenie tablicowe (CTE) nazwane ShortOrd, które zwróci ID zamówienia, ID klienta, ID pracownika, datę zamówienia oraz ID przewoźnika (z tabeli Orders).

2. Utwórz kwerendę zewnętrzną, opartą na powyższym wyrażeniu, która zwróci ID zamówienia, ID klienta, wartość zamówienia oraz nazwę przewoźnika, dotyczące zamówień złożonych w 1996 roku. (Uwaga: potrzebne będzie złączenie wyrażenia tablicowego z tabelą Shippers oraz widokiem Order Subtotals).

Baza: Northwind, Tabele: dbo.Orders, dbo.Shippers, dbo.Order Subtotals (widok), Kolumny: OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, Subtotal, CompanyName, Wynik: 152 rows

s14

 

 

 

Rozwiązanie

WITH ShortOrd AS
(
SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia
FROM dbo.Orders
)

SELECT so.OrderID, so.CustomerID, os.Subtotal as Sum, s.CompanyName as Shipper
FROM ShortOrd as so JOIN dbo.Shippers as s on so.ShipVia = s.ShipperID
JOIN dbo.[Order Subtotals] as os on so.OrderID = os.OrderID
WHERE YEAR(so.OrderDate) = '1996'
ORDER BY so.CustomerID;

[collapse]

Zadanie 10.

Zadanie składa się z kilku kroków:

1. Utwórz wspólne wyrażenie tablicowe (CTE), które będzie zwracać id przewoźnika, nazwę firmy, miasto i kraj. Dodaj filtr, który zwróci tylko przewoźników z Londynu. Nazwijmy to wyrażenie SuppliersByCity.

Wybierz wszystkie rekordy z SuppliersByCity.

2. Zadeklaruj zmienną tekstową ‚miasto’, która będzie przechowywać nazwę miasta. Zmodyfikuj CTE, wstawiając zmienną. (DECLARE @miasto as nvarchar(20) = ‚London’;).

3. Sprawdź poprawność działania kwerendy, wykonując ją z różnymi parametrami zmiennej @miasto (np Tokyo, Osaka, Berlin, Ravenna, Paris, Boston, Sydney).

4. Zmodyfikuj kwerendę tak, by działała, gdy zmienna @miasto zawiera jedynie część nazwy.

Baza: Northwind, Tabela: dbo.Suppliers, Kolumny: SupplierID, CompanyName, City, Country

Rozwiązanie

1. WITH SuppliersByCity AS
(
SELECT SupplierID, CompanyName, City, Country
FROM dbo.Suppliers
WHERE city = 'London'
)
SELECT * FROM SuppliersByCity;

 

2. DECLARE @miasto as nvarchar(20) = 'London';
WITH SuppliersByCity AS
(
SELECT SupplierID, CompanyName, City, Country
FROM dbo.Suppliers
WHERE city = @miasto
)
SELECT * FROM SuppliersByCity;

 

4. DECLARE @miasto as nvarchar(20) = 'sa';
WITH SuppliersByCity AS
(
SELECT SupplierID, CompanyName, City, Country
FROM dbo.Suppliers
WHERE city LIKE '%'+@miasto+'%'
)
SELECT * FROM SuppliersByCity;

[collapse]