Tagi: offset fetch

Zadanie 25.

Korzystając z widoku z zadania 23, stwórzmy podsumowanie sprzedażowe z punktu widzenia sprzedawców i klientów:

a) Który klient złożył najwięcej zamówień, a który miał największą wartość zamówień?

s25a

 

 

 

b) podsumowanie sprzedaży według klienta i sprzedawcy (rollup)

s25b

 

 

 

 

 

 

 

c) wyznacz sprzedawców, którzy odpowiadają (łącznie) za ok. 70% sprzedaży.

s25c

 

 

 

d) wyznacz średnią wartość sprzedaży dla każdego sprzedawcy w danym roku oraz ilość obsłużonych klientów

 

s25d

 

 

 

Schemat: OE, Tabela: my_order_view (widok z zadania 23) , Kolumny: customer_id, order_id, order_total, sales_rep_id, ord_year, Wynik: a) 2 rows b) 112 rows c) 4 rows d) 20 rows

Rozwiązanie

a) WITH cte1 AS
(SELECT customer_id,
COUNT(order_id) AS orders
FROM my_order_view
GROUP BY customer_id
ORDER BY COUNT(order_id) DESC
FETCH FIRST row only
),
cte2 AS
(SELECT customer_id,
SUM(order_total) AS total
FROM my_order_view
GROUP BY customer_id
ORDER BY COUNT(order_total) DESC
FETCH FIRST row only
)
SELECT 'Max orders:' AS description, customer_id, orders AS total FROM cte1
UNION
SELECT 'Max value:', customer_id, total FROM cte2;

 

b) SELECT NVL(TO_CHAR(customer_id), 'total') AS customer_id,
NVL(TO_CHAR(sales_rep_id), 'total')     AS sales_rep_id,
SUM(order_total)  AS order_total
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
GROUP BY cube(customer_id, sales_rep_id)
ORDER BY customer_id, sales_rep_id;

 

c) WITH cte AS
( SELECT DISTINCT sales_rep_id,
SUM(order_total) over(partition BY sales_rep_id) AS sales_rep_total,
SUM(order_total) over() AS total,
CAST(100* SUM(order_total) over(partition BY sales_rep_id) / SUM(order_total) over() AS NUMBER(5,2)) AS sales_rep_pct
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
),
cte2 AS
(SELECT sales_rep_id,
sales_rep_total,
sales_rep_pct,
SUM(sales_rep_pct) over(order by sales_rep_pct DESC) AS total_pct
FROM cte
)
SELECT * FROM cte2 WHERE total_pct <= 70 ORDER BY sales_rep_pct DESC;

 

d) SELECT ord_year, sales_rep_id, TO_CHAR(AVG(order_total), '99999D99') AS orders_total,
COUNT(customer_id) AS customers
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
GROUP BY sales_rep_id, ord_year
ORDER BY ord_year, sales_rep_id;

[collapse]

Zadanie 24.

Korzystając z widoku utworzonego w zadaniu 23, podsumujmy sprzedaż według produktów.

a) które produkty sprzedawały się najlepiej – ranking top 10

s24a

 

 

 

 

b) który produkt sprzedawał się najlepiej w konkretnych latach – ranking top 5 produktów na dany rok

s24b

 

 

 

 

 

c) który produkt sprzedawał się najlepiej w każdej kategorii?

s24c

 

 

 

 

 

d) który produkt sprzedawał się najlepiej w pierwszych trzech miesiącach lat 2006-2007? Ranking top 3 produktów.

s24d

 

 

 

 

Schemat: OE, Tabela: Product_Information oraz widok utworzony w zadaniu 23, Kolumny: product_id, quantity, ord_year, ord_month, category_id, Wynik: a) 10 rows b) 20 rows c) 17 rows d) 15 rows

Rozwiązanie

a) SELECT product_id, SUM(quantity) AS quantity, dense_rank() over(order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id
ORDER BY quantity DESC
FETCH FIRST 10 rows WITH ties;

b) WITH Subquery AS (
SELECT ord_year, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id, ord_year
)

SELECT * FROM Subquery
WHERE rank <= 5
ORDER BY ord_year, rank;

c) WITH Subquery AS (
SELECT pi.category_id, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by pi.category_id order by SUM(quantity) DESC) AS rank
FROM my_order_view inner join product_information pi using(product_id)
GROUP BY pi.category_id, product_id
)

SELECT category_id, product_id, quantity
FROM Subquery
WHERE rank = 1;

d) WITH Subquery AS (
SELECT ord_year, ord_month, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year, ord_month order by SUM(quantity) DESC) AS rank
FROM my_order_view
WHERE ord_month in(1,2,3) and ord_year in(2006, 2007)
GROUP BY ord_year, ord_month, product_id
)

SELECT * FROM Subquery
WHERE rank <= 3;

[collapse]

Zadanie 29.

Stwórz zapytanie, które pokaże nam najczęściej zamawiane produkty. Wybierz 15 najczęściej zamawianych produktów. (Uwaga: więcej niż jeden produkt może być zamówiony daną ilość razy)

Schemat: OE, Tabela: Order_Items, Kolumny: order_id, product_id, Wynik: 16 rows

p29

 

 

 

 

 

Rozwiązanie

SELECT product_id, COUNT(order_id) AS Orders
FROM order_items
GROUP BY product_id
ORDER BY COUNT(order_id) DESC
FETCH FIRST 15 rows WITH ties;

[collapse]

Zadanie 25.

Dla 10 ostatnich (najnowszych) zamówień określ, czy klient zamawiający ma credit_limit wystarczający na pokrycie kosztów zamówienia. Wynik podaj opisowo – order_id, customer_id, oraz „Limit OK” lub „Limit zbyt mały”.

Schemat: OE, Tabele: Customers, Orders, Kolumny: order_id, customer_id, credit_limit, order_total, Wynik: 10 rows

p25

 

 

 

 

 

 

Rozwiązanie

SELECT o.order_id, customer_id,
CASE
WHEN c.credit_limit >= o.order_total THEN 'Limit OK'
ELSE 'Limit zbyt maly'
END as Limity
FROM orders o INNER JOIN customers c
USING(customer_id)
ORDER BY o.order_id DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

[collapse]

Zadanie 6.

Z tabeli Orders (OE) wybierz zamówienia:

a) złożone w marcu 2007 roku

b) złożone w trybie innym niż direct i o statusie 4 lub 8

c) 10 zamówień o najwyższej wartości

Schemat: OE, Tabela: Orders, Kolumny: Order_Date, Order_Mode, Order-Status, Order_Total, Wynik: a) 7 rows b) 12 rows c) 10 rows

Rozwiązanie

a) SELECT *
FROM Orders
WHERE Order_Date >= To_Date('2007-03-01') AND Order_Date < To_Date('2007-04-01');

b) SELECT *
FROM Orders
WHERE Order_Mode <> 'direct' AND (Order_Status = 4 OR Order_Status = 8);

c) SELECT *
FROM Orders
ORDER BY Order_Total DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

[collapse]

Zadanie 2.

1. Utwórz tabelę (u mnie nazywa się Tab1) zawierającą trzy kolumny: id (będącą identyfikatorem, zaczynającym od wartości 1 z inkrementacją o 1), orderid (typ numeryczny), company (typ tekstowy). Żadna z kolumn nie przyjmuje wartości NULL.

2. Utworzoną tabelę wypełnij danymi, pochodzącymi z tabeli dbo.Orders; niech będzie to 10 najnowszych zamówień (OrderID) oraz odpowiadające im firmy (CustomerID).

3. Dodaj wiersz z numerem zamówienia 11078 i firmą LILAS.

4. Usuń pierwsze trzy wiersze (id od 1 do 3).

5. Zmień nazwę firmy z LILAS na ABCDE.

6. Zwróć przedostatni dodany rekord (bazując na identyfikatorze id).

7. Usuń tabelę stworzoną w punkcie 1.

Baza: Northwind, Tabela: dbo.Orders, Kolumny: OrderID, CustomerID, Wynik: 8 rows (po modyfikacjach)

z2

 

 

 

 

 

 

 

Rozwiązanie

1. CREATE TABLE dbo.Tab1 (
id INT NOT NULL IDENTITY(1,1),
orderid INT NOT NULL,
company VARCHAR(10) NOT NULL
);

 

2. INSERT INTO dbo.Tab1(orderid, company)
SELECT OrderID, CustomerID
FROM dbo.Orders
ORDER BY OrderID DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

 

3. INSERT INTO dbo.Tab1(orderid, company)
VALUES (11078, 'LILAS');

 

4. DELETE FROM dbo.Tab1
WHERE id in (1, 2, 3);

 

5. UPDATE dbo.Tab1
SET company = 'ABCDE'
WHERE company = 'LILAS';

 

6. SELECT *
FROM dbo.Tab1
WHERE id = IDENT_CURRENT('dbo.Tab1') - 1;

 

7. DROP TABLE dbo.Tab1;

[collapse]

Zadanie 1.

Wyznacz id klienta, który złożył ostatnie zamówienie (najnowsze). Zadanie rozwiąż na dwa różne sposoby.

Baza: Northwind, Tabela: dbo.Orders, Kolumny: CustomerID, OrderID, Wynik: 1 row

s1

Rozwiązanie

1) z użyciem funkcji OFFSET-FETCH, ewentualnie TOP:

SELECT CustomerID, OrderID
FROM dbo.Orders
ORDER BY OrderID DESC
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;

2) z użyciem podzapytania:

SELECT CustomerID, OrderID
FROM dbo.Orders
WHERE OrderID = ( SELECT Max(OrderID) FROM dbo.Orders );

[collapse]

Zadanie 19.

Wyznacz 5 klientów, którzy mają najwyższą wartość zamówień.

Baza: Northwind, Tabele: dbo.Customers, dbo.Orders, dbo.Order Details, Kolumny: CustomerID, CompanyName, Quantity, UnitPrice, Wynik: 5 rows

24

Rozwiązanie

SELECT C.CustomerID, C.CompanyName, SUM(OD.Quantity * OD.UnitPrice) as Suma FROM dbo.Customers as C

JOIN dbo.Orders as O on C.CustomerID = O.CustomerID

JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID

GROUP BY C.CustomerID, C.CompanyName

ORDER BY Suma DESC

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

[collapse]

Zadanie 13.

Stwórz kwerendę, która zwraca w wyniku listę numerów zamówień, uporządkowanych rosnąco. Nas jednak interesują tylko pozycje od 25 do 50 z tej listy.

Baza: Northwind, Tabela: Orders, Kolumny: OrderID, Wynik: 25 rows

18

Rowziązanie

SELECT OrderID
FROM dbo.Orders
ORDER BY OrderID
OFFSET 24 ROWS FETCH NEXT 25 ROWS ONLY;

[collapse]
« Starsze wpsiy