Tagi: distinct

Zadanie 18.

Znajdź produkty, które nie były zamówione ani razu. Zadanie rozwiąż więcej niż jednym sposobem.

Schemat: OE, Tabele: Order_Items, Product_Information, Kolumny: product_id, order_id, Wynik: 103

s18

 

 

 

 

 

 

Rozwiązanie

1. Z użyciem MINUS:

SELECT product_id FROM product_information
MINUS
SELECT product_id FROM order_items ORDER BY product_id;

2. Z wykorzystaniem złączenia OUTER JOIN:
SELECT product_id
FROM product_information
FULL OUTER JOIN order_items oi USING(product_id)
WHERE oi.order_id IS NULL
ORDER BY product_id;

3. Z podzapytaniem:
SELECT product_id
FROM product_information
WHERE product_id NOT IN
( SELECT DISTINCT product_id FROM order_items
)
ORDER BY product_id;

[collapse]

Zadanie 10.

Korzystając z widoku Order_Summary (utworzonego w Zadaniu 6.), napisz kwerendę, która zwróci zamówienia o wartości podobnej (plus minus 10%) do wskazanej (użyj w tym celu zmiennej &suma). Wynik posortuj rosnąco.

Screen przedstawia wynik dla &suma = 50 000.

Schemat: OE, Tabela: Order_Summary (widok), Kolumny: order_id, order_mode, order_date, order_total, Wynik: 7 rows

s10

 

 

 

 

Rozwiązanie

SELECT DISTINCT order_id, order_mode, order_date, order_total
FROM order_summary
WHERE order_total BETWEEN &suma *0.9 AND &suma *1.1
ORDER BY order_total;

[collapse]

Zadanie 8.

Napisz kwerendę, która zwróci listę nazwisk, które noszą zarówno klienci, jak i pracownicy.

Schemat: OE, HR, Tabele: Employees, Customers, Kolumny: cust_last_name, last_name, Wynik: 6 rows

s8

 

 

 

 

Rozwiązanie

1. Z operatorem INTERSECT:

SELECT DISTINCT last_name FROM HR.employees
INTERSECT
SELECT cust_last_name FROM customers;

2. Z użyciem EXISTS:

SELECT DISTINCT last_name FROM HR.employees
WHERE EXISTS (
SELECT cust_last_name
FROM customers
WHERE HR.employees.last_name = customers.cust_last_name );

[collapse]

Zadanie 7.

Stwórz kwerendę, która zliczy niepowtarzające się nazwiska klientów. W wyniku chcemy uzyskać konkretną liczbę. Zadanie rozwiąż na co najmniej dwa sposoby.

Schemat: OE, Tabela: Customers, Kolumny: cust_last_name, Wynik: 176 rows

s7

 

Rozwiązanie

1. Z klauzulą WITH:

WITH Lname AS
(
SELECT DISTINCT cust_last_name
FROM customers
)

SELECT COUNT(*) as LAST_NAME_COUNT FROM Lname;

2. Z podzapytaniem w klauzuli WHERE:

SELECT COUNT(*) as LAST_NAME_COUNT
FROM (
SELECT DISTINCT cust_last_name
FROM customers
);

3. Z grupowaniem:

SELECT COUNT(DISTINCT cust_last_name) AS last_name_count, 1
FROM customers
GROUP BY 1;

[collapse]

Zadanie 5.

Którzy dostawcy dostarczają produkty zamówień złożonych przez klientów o ID 107 i 149? Zadanie rozwiąż na dwa sposoby.

Schemat: OE, Tabele: Product_Information, Order_Items, Orders, Kolumny: supplier_id, order_id, product_id, Wynik: 32 rows

s5

 

 

 

 

 

Rozwiązanie

1. Łączenie tabel:

SELECT DISTINCT pi.supplier_id
FROM order_items oi
INNER JOIN product_information pi USING(product_id)
INNER JOIN orders ord USING(order_id)
WHERE ord.customer_id IN(107, 149)
ORDER BY supplier_id;

2. Podzapytania:

SELECT DISTINCT supplier_id
FROM product_information
WHERE product_id IN
(SELECT product_id
FROM order_items
WHERE order_id IN
( SELECT order_id FROM orders WHERE customer_id IN(107, 149)
)
)

ORDER BY supplier_id;

[collapse]

Zadanie 4.

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.

W tym celu będziemy potrzebować: ID klienta, łącznej wartości zamówień klienta, udziału procentowego tych zamówień w stosunku do wszystkich zamówień, oraz podliczenia punktów procentowych.

Schamat: OE, Tabela: Customers, Kolumny: customer_id, order_total, Wynik: 47 rows

s4

 

 

 

 

Rozwiązanie

WITH Subquery AS
( SELECT DISTINCT customer_id,
SUM(order_total) OVER(partition BY customer_id) AS Customer_Total,
SUM(order_total) OVER() AS Total,
CAST(100* SUM(order_total) OVER(partition BY customer_id) / SUM(order_total) OVER() AS NUMBER(5,2)) AS Customer_Percent
FROM orders
)
SELECT customer_id, customer_total, customer_percent,
SUM(customer_percent) OVER(order by customer_percent DESC) AS Sum_Pct
FROM Subquery
ORDER BY customer_total DESC;

[collapse]

Zadanie 23.

Klienci z jakich krajów złożyli zamówienia w 2006 roku? Nazwy krajów uporządkuj alfabetycznie i zadbaj, by się nie powtarzały.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, order_date, nls_territory, Wynik: 1 row

Rozwiązanie

SELECT DISTINCT c.nls_territory
FROM orders o INNER JOIN customers c
USING(customer_id)
WHERE EXTRACT(YEAR from o.order_date) = '2006';

[collapse]

Zadanie 16.

Korzystając z tabeli Employees w schemacie HR:

a) wyświetl listę numerów departamentów w taki sposób, by wszystkie były trzycyfrowe (uzupełnij je zerami z lewej) oraz nie powtarzały się.

b) wyświetl id pracownika oraz jego datę urodzenia i pensję wg określonego formatu:

p16

 

 

 

 

c) wygeneruj każdemu pracownikowi nowy adres mailowy składający się z nazwiska, kropki, pierwszej litery imienia, numeru departamentu, z domeną @abc.com (przykład: kowalska.a90@abc.com):

p16c

 

 

 

 

Schemat: HR, Tabela: Employees, Kolumny: department_id, employee_id, first_name, last_name, hire_date, salary, Wynik: a) 12 rows, b) oraz c) 107 rows

Rozwiązanie

a) SELECT DISTINCT LPAD(department_id, 3, '0') as Department
FROM employees
ORDER BY Department;

b) SELECT employee_id, to_char(salary, '$99G999D99') as Salary, to_char(hire_date, 'DD MON YYYY') as "Hire Date"
FROM employees
ORDER BY employee_id;

c) SELECT employee_id, lower(last_name ||'.'||SUBSTR(first_name, 1, 1)||department_id||'@abc.com') as "New Email"
FROM employees
ORDER BY employee_id;

[collapse]

Zadanie 26.

Wybierz wszystkich klientów, którzy zamówili produkty o id 1 i 55. Zadanie rozwiąż na dwa sposoby.

Baza: Northwind, Tabele: dbo.Order Details, dbo.Orders, Kolumny: CustomerID, OrderID, ProductID, Wynik: 16 rows

s26

Rozwiązanie

1. Z użyciem podzapytań i Exists:

SELECT DISTINCT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 1
AND EXISTS (
SELECT *
FROM dbo.[Order Details] OD2 JOIN dbo.Orders O2
ON OD2.OrderID = O2.OrderID
WHERE O.CustomerID = O2.CustomerID
AND OD2.ProductID = 55
)
ORDER BY O.CustomerID;

2. Z użyciem operatorów zbiorowych:

SELECT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 1

INTERSECT

SELECT O.CustomerID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE OD.ProductID = 55

ORDER BY O.CustomerID;

[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]
« Starsze wpsiy