Tagi: left join

Zadanie 21.

Dla klientów o ID 101, 111, 121, 141, 171 i 201 pokaż numer oraz datę ostatniego złożonego zamówienia. Jeśli klient nie ma na swoim koncie zamówień, powinna pojawić się stosowna informacja.

Schemat: OE, Tabele: Orders, Customers, Kolumny: customer-id, order_id, order_date, Wynik: 6 rows

p21

 

 

Rozwiązanie

SELECT customer_id as Customer, MAX(o.order_id) as "Last order",
to_char(MAX(o.order_date), 'DD.MM.YYYY') as "Last ord. date",
CASE
WHEN MAX(o.order_id) IS NULL THEN 'Ten klient nie ma zamówień'
ELSE ' '
END as Opis
FROM customers c LEFT OUTER JOIN orders o
USING(customer_id)
WHERE customer_id IN(101, 111, 121, 141, 171, 201)
GROUP BY customer_id
ORDER BY customer_id;

[collapse]

Zadanie 20.

Pokaż klientów o parzystym ID, którzy złożyli więcej niż 3 zamówienia lub nie złożyli żadnego.

Schemat: OE, Tabele: Orders, Customers, Kolumny: customer_id, order_id, Wynik: 138 rows

p20

 

 

 

 

Rozwiązanie

SELECT customer_id as Customer, COUNT(order_id) as Orders
FROM customers c LEFT OUTER JOIN orders o
USING(customer_id)
WHERE MOD(customer_id, 2) = 0
GROUP BY customer_id
HAVING COUNT(order_id) >= 3 OR COUNT(order_id) = 0
ORDER BY customer_id;

[collapse]

Zadanie 18.

Znajdź klientów, którzy nie złożyli żadnego zamówienia.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, cust_first_name, cust_last_name, order_id, Wynik: 272 rows

p18

 

 

 

 

 

Rozwiązanie

SELECT customer_id, c.cust_first_name, c.cust_last_name
FROM customers c LEFT OUTER JOIN orders o USING(customer_id)
WHERE o.order_id IS NULL
ORDER BY customer_id;

[collapse]

Zadanie 11.

Stwórz listę pracowników – id, imię, nazwisko oraz podaj informację, czy dana osoba nadal pracuje (pracuje / nie pracuje).

Schemat: HR, Tabele: Employees, Job_History, Kolumny: employee_id, first_name, last_name, end_date, Wynik: 110 rows

p11

 

 

 

Rozwiązanie

SELECT employee_id, e.first_name, e.last_name,
CASE
WHEN jh.end_date IS NULL THEN 'pracuje'
ELSE 'nie pracuje'
END AS Pracownik
FROM employees e LEFT OUTER JOIN job_history jh USING(employee_id)
ORDER BY employee_id;

[collapse]

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

Policz, ile zamówień złożył każdy z klientów. Wynik ułóż od największej ilości zamówień.

Baza: Northwind, Tabele: dbo.Customers, dbo.Orders, Kolumny: CustomerID, CompanyName, OrderID, Wynik: 91 rows

8

Rozwiązanie

SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) as Zamowienia
FROM dbo.Customers as C
LEFT JOIN dbo.Orders as O on C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.CompanyName
ORDER BY Zamowienia DESC

— W funkcji COUNT() wstawiłam zliczanie po OrderID zamiast *, ponieważ * zlicza wszystkie wiersze, również z wartością NULL gdyby się jakieś pojawiły – a teoretycznie mogą, bo mamy złączenie lewostronne – więc wynik mógłby być przekłamany.

[collapse]