1) Zapytania SQL nie dzielą się na łatwe i trudne tylko na krótkie i długie.
Opanowanie SQL polega na przyswojeniu sobie zestawu prostych mechanicznych sztuczek. Małpa jest w stanie tego dokonać. Trzeba jednak wyćwiczyć biegłość w posługiwaniu się sztuczkami.
2) Wiele postaci składniowych i wiele zapytań dających ten sam rezultat.
3) Optymalna postać zapytania (niezależnie od optymalizacji wykonania) zależy od przeznaczenia zbioru wynikowego: zapytania ad-hoc (sporadyczne) oraz zapytania w ramach aplikacji programowych.
4) Zadanie: w bazie Northwind znaleźć najlepsze miesiące pod względem obrotów dla poszczególnych lat.
1
Podstawowy komponent rozwiązania
30310,30
1996
7
26609,40
1996
8
27636,00
1996
9
41203,60
1996
10
49704,00
1996
11
50953,40
1996
12
66692,80
1997
1
select Sum(UnitPrice*Quantity),
41207,20
1997
2
Year(OrderDate),
39979,90
1997
3
55699,39
1997
4
Month(OrderDate) from
56823,70
1997
5
[Order Details] od join Orders o on
39088,00
1997
6
55464,93
1997
7
od.OrderID=o.OrderID
49981,69
1997
8
group by Year(OrderDate),
59733,02
1997
9
Month(OrderDate)
70328,50
1997
10
45913,36
1997
11
order by 2,3
77476,26
1997
12
100854,72
1998
1
104561,95
1998
2
109825,45
1998
3
134630,56
1998
4
19898,66
1998
5
Szukane wiersze zbioru wynikowego
Wyniki coraz lepsze!
2
Kolumny bez nazw
Rozwiązanie 1 – konstrukcja top n
--solved straightforward way however we resort to "top n",
--which is not recommended by the lecturer
select Year(OrderDate) [Year],
(select top 1 Month(OrderDate)
from [Order Details] join Orders o2 on
o2.OrderID=[Order Details].OrderID
where Year(o2.OrderDate)=Year(o1.OrderDate)
group by Month(OrderDate)
order by Sum(Quantity*UnitPrice) desc) [Best Month]
from Orders o1 group by Year(OrderDate)
3
--in unlikely case there are two or more equally good months
--evidently with ties does not help
select Year(OrderDate) [Year],Month(OrderDate) [Best Month]
from Orders o1 join [Order Details] on
o1.OrderID=[Order Details].OrderID
group by Year(OrderDate),Month(OrderDate)
having
Sum(Quantity*UnitPrice)=
(
select Max(total) from
(select Sum(Quantity*UnitPrice) total
from Orders o2 join [Order Details] on
o2.OrderID=[Order Details].OrderID
where Year(o1.OrderDate)=Year(o2.OrderDate)
group by Year(OrderDate),Month(OrderDate)) t
)
4
--now we shorten the query notation
using CTE
--aggregation can be factored out as a common table
--which (possibly) makes it more readable
with Totals as
( select Year(OrderDate) y,Month(OrderDate)m,
Sum(Quantity*UnitPrice) total
from Orders o1 join [Order Details] on
o1.OrderID=[Order Details].OrderID
group by Year(OrderDate),Month(OrderDate)
)
select t1.y [Year],t1.m [Best Month] from Totals t1
where t1.total=(
select Max(t2.total) from Totals t2
where t2.y=t1.y
)
5
--application of ranking function
--certain syntactical constraints (ranking+selection)
--ranking can neither appear in having nor where clause
select * from
(
select RANK() over(partition by Year(OrderDate)
order by Sum(Quantity*UnitPrice) desc) r,
Year(OrderDate)[Year],Month(OrderDate)[Best Month]
from Orders o1 join [Order Details] on
o1.OrderID=[Order Details].OrderID
group by Year(OrderDate),Month(OrderDate)
) t
where
t.r=1
6
Best
r
Year
Month
1
1996
12
2
1996
11
3
1996
10
4
1996
7
select RANK() over(partition by Year(OrderDate)
5
1996
9
6
1996
8
order by Sum(Quantity*UnitPrice) desc) r,
1
1997
12
Year(OrderDate)[Year],Month(OrderDate)[Best Month]
2
1997
10
3
1997
1
from Orders o1 join [Order Details] on
4
1997
9
o1.OrderID=[Order Details].OrderID
5
1997
5
6
1997
4
group by Year(OrderDate),Month(OrderDate)
7
1997
7
8
1997
8
9
1997
11
10
1997
2
11
1997
3
12
1997
6
1
1998
4
2
1998
3
3
1998
2
4
1998
1
5
1998
5
7
--use of CTE
--ranking +CTE work together
with ranks as
(select RANK() over(partition by Year(OrderDate)
order by Sum(Quantity*UnitPrice) desc) r,
Year(OrderDate)[Year],Month(OrderDate)[Best Month]
from Orders o1 join [Order Details] on
o1.OrderID=[Order Details].OrderID
group by Year(OrderDate),Month(OrderDate)
)
select [Year],[Best Month] from ranks where r=1
8
Odwrotność iloczynu kartezjańskiego
Zawsze wykonalne.
Uogólnienie iloczynu arytmetycznego na liczby niepodzielne.
a÷b = (a- a mod b)/b
9
Typ zapytań „dla każdego” 1 ( forall) 8.3.16 Dostawcy sprzedający wszystkie części.
Wersja łatwiejsza. Porównanie liczb części dla dostawców.
select S# from S where
(select count(distinct P#) from SPJ
where SPJ.S#=S.S#)=
(select count(*) from P)
W zwolnionym tempie! –slow motion replay
1) Liczba produktów w ogóle sprzedawanych.
select count(*) from P
10
Wynik: 6
8.3.16 Dostawcy sprzedający wszystkie części slow motion. c.d.
select S#,count(distinct P#) from SPJ
group by S#
Wynik:
S1
1
S2
2
S3
2
S4
1
S5
6
Łopatologicznie więc dostajemy:
select S# from SPJ
group by S#
having count(distinct P#)=(select count(*) from P) 11
8.3.16 Dostawcy sprzedający wszystkie części slow motion. c.d.
Pamiętamy, że grupowanie można zastąpić przez zagnieżdżone zapytanie.
select S#,
(select count(distinct P#) from SPJ where SPJ.S#=S.S#) as ILE
from S
Wynik ten sam – teraz kolumnę ile można porównać w klauzuli where.
C.J.Date na temat rozwiązań opartych na liczeniu wierszy: 1)
Oparte na założeniu, że jest spełnione połączenie oparte na kluczu. Tj. nie ma takich wierszy SPJ, że wartości w kol. P# nie ma w tabelce P.
2)
Nie wszystkie produkty umożliwiają użycie zagnieżdżonych zap. (już nie jest to prawda)
3)
W wielu zadaniach typu „dla każdego” technika ta nie wystarcza!
12
Skoro dostarcza wszystko to nie ma takiej części której by nie dostarczał.
select S# from S
where
not exists (
select * from P
where
not exists (select * from SPJ where SPJ.S#=S.S# and
SPJ.P#=P.P#))
Teraz to samo w zwolnionym tempie→
13
Zapytanie ma znaleźć dostawców więc tabelka S.
select S# from S
where
not exists (
„część której S# nie dostarcza ”)
Dla danego S# szukamy części których on nie dostarcza!
select P# from P
where
not exists
(„pozycja z SPJ dla częsci P# i danego S#”)
14
Znaleźć części których S2 nie dostarcza!
select P# from P
where
not exists (select * from SPJ
where SPJ.P#=P.P# and
SPJ.S#='S2')
Podsumowując: ∀ϕ( x) ≡ ¬∃¬ϕ( x)
x
x
select * from S
where forall(x, ϕ (x))
→
select * from S
where not exists(x, not ϕ (x))
15
Dostarczający wszystkie czerwone części, podwójna negacja select * from S
where not exists
(
select * from P
where
P.Color='Red' and
not exists
(select * from SPJ
where SPJ.S#=S.S# and SPJ.P#=P.P#
)
)
16
Dostarczający wszystkie czerwone części, zliczanie zbioru wynikowego select * from S
where
(
select count (*) from P
where P.Color='Red'
)
=
(
select count(distinct P.P#) from
SPJ join P on P.P#=SPJ.P#
where P.Color='Red' and S.S#=SPJ.S#
)
17
Pary odbiorca-dostawca, takie że projekt korzysta z wszystkich części w ofercie dostawcy (ale niekoniecznie bierze od tego dostawcy)
select
J#,S# from S,J
Iloczyn kartezjański
where
not exists
(select * from SPJ SPJ
where SPJ.S#=S.S# and
not exists
(select * from SPJ SPJ1
where
SPJ1.J#=J.J# and
SPJ1.P#=SPJ.P#
)
)
18
Pary odbiorca-dostawca, takie że projekt korzysta z wszystkich części w ofercie dostawcy Inaczej niż w przyk. 3 wymaga się aby dostawa była od dostawcy S#
select
J#,S# from S,J
where
not exists
(select * from SPJ SPJ
where SPJ.S#=S.S# and
not exists
(select * from SPJ SPJ1
where
SPJ1.J#=J.J# and
SPJ1.P#=SPJ.P# and
SPJ1.S#=S.S#
)
)
19
Przykład 3 rozwiązanie ze zliczaniem zbioru wynikowego
select
J#,S# from J,S
where
(select count (distinct P#) from SPJ
where SPJ.S#=S.S#)
=
(
select count(distinct P#) from SPJ
where
SPJ.J#=J.J#
and
SPJ.P# in
( select P# from SPJ where SPJ.S#=S.S#
)
)
20
Przykład 4 rozwiązanie ze zliczaniem zbioru wynikowego, uproszczenie w stosunku do 5 (wymóg by dostawa była od S#)
select
J#,S# from J,S
where
(select count (distinct P#) from SPJ
where SPJ.S#=S.S#)
=
(
select count(distinct P#) from SPJ
where SPJ.S#=S.S# and
SPJ.J#=J.J#
)
21
Przykład 5 eliminacja konstrukcji in (sprawdzenie przynależności do zbioru) select
J#,S# from J,S
where
(select count (distinct P#) from SPJ
where SPJ.S#=S.S#)
=
(
select count(distinct SPJ.P#) from SPJ , SPJ SPJ1
where
SPJ.J#=J.J# and
SPJ.P# =SPJ1.P# and
SPJ1.S#=S.S#
)
22
12) Łagodne wprowadzenie do tematu następnego
wykładu: Znaleźć pracowników którzy sprzedają
wszystkie produkty (tj. dla każdego produktu istnieje
pozycja Orders/[Order Details] tego pracownika)
23
select * from Employees e
where
Nie istnieje dla danego pracownika
not exists
pozycja w tabelce products taka, że
(
pracownik jej nie sprzedawał.
select * from Products p
where
not exists
Rezultat: pusty zbiór wynikowy.
(
select * from Orders o,[Order Details] od
Operacja: dzielenie relacyjne
where
(EmployeeID,ProductID) z Orders-
o.OrderID=od.OrderID and
[Order Details] przez ProductID z P
o.EmployeeID=e.EmployeeID and
od.ProductID=p.ProductID
)
)
24
Rozwiązanie ze zliczaniem:
select * from Employees e
where
(select COUNT(distinct ProductID) from Orders o,[Order Details] od where
o.OrderID=od.OrderID and
o.EmployeeID=e.EmployeeID
) =( select COUNT(*) from Products)
25
12) Łagodne wprowadzenie do tematu następnego
wykładu: Znaleźć pracowników którzy sprzedają
wszystkie produkty (tj. dla każdego produktu istnieje
pozycja Orders/[Order Details] tego pracownika)
Poluzowane: pracownicy sprzedający wszystko ale tylko
z zadanej kategorii
26
Modyfikacja dzielnika
select LastName,FirstName from Employees e
where
not exists
(
select * from Products p where CategoryID=4
and
not exists
(
LastName FirstName
Davolio
Nancy
select * from Orders o,[Order Details] od
Leverling Janet
where
King
Robert
Callahan Laura
o.OrderID=od.OrderID and
o.EmployeeID=e.EmployeeID and
od.ProductID=p.ProductID
)
)
27
Metoda: policzyć ile pozycji z kategorii 4 dot. pracownika i sprawdzić czy to są wszystkie.
select LastName,FirstName from Employees e
where
(select COUNT(distinct p.ProductID) from Orders o,[Order Details] od, Products p where
od.ProductID=p.ProductID and
CategoryID=4 and
o.OrderID=od.OrderID and
o.EmployeeID=e.EmployeeID
) =
Złączenie z tabelką
( select COUNT(*) from Products where CategoryID=4)
28
Pracownicy sprzedający wszystko z jakiejkolwiek (co najmniej jednej kategorii) select LastName,FirstName from Employees e
where Inaczej :jest taka kategoria, że sprzedaje wszystko.
exists ( select * from Categories c
where
(
not exists
(
select * from Products p where CategoryID=c.CategoryID
and
not exists
(
select * from Orders o,[Order Details] od
where
o.OrderID=od.OrderID and
o.EmployeeID=e.EmployeeID and
od.ProductID=p.ProductID))))
29
Samodzielnie:
• Wersja ze zliczaniem (proste). Zapytanie
znajdujące pracowników którzy sprzedają
wszystko z jakiejkolwiek kategorii.
• Wyeliminować dodatkowy exists – zastąpić ze
względów wydajnościowych złączeniem
30
Zadanie typu forall dla bazy Northwind 1. Szukamy klientów, którzy jeżeli kupują cokolwiek od
pracownika to kupują również wszystko, co on sprzedaje.
2. Możliwe dwa warianty: wystarczy żeby lista produktów była ta sama lub kupowane u tego samego pracownika
3. Inaczej: mają być znalezieni tacy klienci, że jeśli kupują u pracownika to nie ma takiego produktu, że ten pracownik go sprzedał a dany klient go u tego pracownika nie zakupił.
4. Dodatkowa trudność: inaczej niż w SPJ powiązanie kupującego i sprzedającego przez tabelkę Orders – widok lub CTE
31
where
not exists
(
select * from Employees e
where
exists
(select * from Orders o
where o.EmployeeID=e.EmployeeID and
o.CustomerID=c.CustomerID)
and
exists (select * from Products where
exists (select * from Orders o, [Order Details] od where o.OrderID=od.OrderID and
o.EmployeeID=e.EmployeeID and
od.ProductID=Products.ProductID)
and
not exists(select * from Orders o, [Order Details] od where o.OrderID=od.OrderID and
o.EmployeeID=e.EmployeeID and
c.CustomerID=o.CustomerID and
od.ProductID=Products.ProductID )
))
32
Zadanie typu forall dla bazy Northwind Szukamy klientów, którzy jeżeli kupują cokolwiek od pracownika to kupują również wszystko, co on sprzedaje.
Inaczej: mają być znalezieni tacy klienci, że jeśli kupują u pracownika to nie ma takiego produktu, że ten pracownik go sprzedał a dany klient go u tego pracownika nie zakupił
Pomocniczy widok (tabelka wirtualna – nie przechowuje danych ale udostępnia!) create view CEP as
select distinct EmployeeID,CustomerID,ProductID
from
Orders,[Order Details]
where
Orders.OrderID=[Order Details].OrderID
(wirtualny odpowiednik SPJ)
33
select distinct CustomerID from CEP t klienci t
where
not exists
Nie ma takiego pracownika wśród tych, u
(select * from CEP t1
których klient t.CustomerID się zaopatruje
where
t1.CustomerID=t.CustomerID and
że jest taki produkt t2.ProductID
exists(select * from CEP t2 where t2.EmployeeID=t1.EmployeeID and
not exists( select * from CEP t3 where t.CustomerID nie kupił go u pracownika
t3.ProductID=t2.ProductID and
t1.EmployeeID
t3.EmployeeID=t1.EmployeeID and
t3.CustomerID=t.CustomerID)))
Ewidentne CTE – współdzielone wyrażenie
tablicowe
34
Wersja poluzowana. Kupują tę pozycję
not exists(
ale niekoniecznie u konkretnego
select t2.ProductID from CEP t1 , CEP t2 where
pracownika.
t1.CustomerID=Customers.CustomerID and
t1.EmployeeID=t2.EmployeeID and
not exists (
select * from CEP t3 where
t3.CustomerID=Customers.CustomerID and
t3.ProductID=t2.ProductID
)
)
select * from Customers where
Wersja oryginalna. Jedyna różnica
not exists(
zaznaczone strzałką.
select t2.ProductID from CEP t1 , CEP t2 where
t1.CustomerID=Customers.CustomerID and
t1.EmployeeID=t2.EmployeeID and
not exists (
select * from CEP t3 where
t3.CustomerID=Customers.CustomerID and
t3.ProductID=t2.ProductID and
W obydwu przypadkach ten sam
t3.EmployeeID=t1.EmployeeID
trywialny zbiór wynikowy.
)
)
35
Użycie w exists listy select jako wskazanie dla korelacji (poprawa czytelności)
Dzielenie relacyjne, programowanie defensywne
• Użycie w exists listy select jako wskazanie dla korelacji (poprawa czytelności)
• Zagadnienie programowania defensywnego. Użycie gwiazdki bez wpływu na wynik, bo exists/not exists uwzględnia tylko wiersze.
Projekcja w zagnieżdżonym zapytaniu:
• Wskazówka dot. możliwej korelacji.
• Bez znaczenia dla wyniku.
select * from Customers where
• Niepotrzebne jeśli nie ma już
not exists(
dalszych zagnieżdżeń
select t2.ProductID from CEP t1 , CEP t2 where
t1.CustomerID=Customers.CustomerID and
t1.EmployeeID=t2.EmployeeID and
Tutaj bez znaczenia. Również
not exists (
defensywnego☺
select * from CEP t3 where
t3.CustomerID=Customers.CustomerID and
t3.ProductID=t2.ProductID and
t3.EmployeeID=t1.EmployeeID
)
)
36
Postać równoważna postaci zapytania bez widoku CEP w zapytaniu głównym
Konieczne odwołanie się do
tabelki Customers (dlaczego?)
select CustomerID from Customers
where
Identyfikatory jakich klientów
nie znajdą się w zbiorze
not exists
wynikowym jeżeli użyć
(select * from CEP t1
widoku CEP w miejsce
tabelki Customers?
where
t1.CustomerID=Customers.CustomerID and
Na poprzedniej planszy o
exists(select * from CEP t2 where jeden exists mniej, zamiana
exista przez złączenie.
t2.EmployeeID=t1.EmployeeID and
not exists( select * from CEP t3 where t3.ProductID=t2.ProductID and
t3.EmployeeID=t1.EmployeeID and
t3.CustomerID=Customers.CustomerID)))
37
1) Rozważyć użycie tabelek Employees, Customers i Products razem z widokiem CEP. Przeanalizować wpływ na postać zbioru wynikowego.
2) Podać zapytanie równoważne korzystające z konstrukcji CTE
3) Podać przykłady zapytań w bazie Northwind będące odpowiednikami przykładów podanych w książce "Wprowadzenie do systemów baz danych ".
Zapytania SQL należy podać w postaci korzystającej z widoków i CTE, jedno wybrane zapytanie niekorzystające z tych konstrukcji – będzie przypominać zapytanie z p. 22.
38
(select distinct P# from SPJ) P1,
Ogólna postać dzielenia
(select distinct S# from SPJ) S1
relacyjnego
where
Operator divide by
not exists(
Odwrotność iloczynu
select distinct S#,P# from
kartezjańskiego
(select distinct P# from SPJ) P,
(select distinct S# from SPJ) S,
(select distinct J# from SPJ) J
SPJ divide by J
where
not exists(
Możliwe też:
select * from SPJ
SPJ/J, SPJ/P, SPJ/S,
SPJ/SP itd.
where
SPJ.P#=P.P# and
W konkretnym przypadku lepiej
SPJ.J#=J.J# and
przeanalizować zadanie i znaleźć
SPJ.S#=S.S#
zapytanie w miarę czytelne i
) and
wydajne.
S.S#=S1.S# and
P.P#=P1.P#
)
39
Ogólne zasady dla dzielenia relacyjnego 1
Definicja, dla relacji A B wynikiem dzielenia jest taka relacja C że: A/B= C ⇔ C= max {Z |Z ⊗ B ⊂A}
Tj. r∈ Z ⊗ C ⇒ r ∈ A
Reszta z dzielenia:
R=A-(A/B) ⊗ B ,
co jeśli R=∅ ?(zależność wielowartościowa)
Podwójna negacja exists
∀ ∃ y = ( r, x) ⇔ ¬ ∃ ¬ ∃ y = ( r, x) r B
∈ , x C
∈ y∈ A
r B
∈ , x C
∈
y∈ A
40
Ogólne zasady dla dzielenia relacyjnego 2
Zliczanie jako metoda sprawdzenia czy zbiory są równe.
Równa liczność jest warunkiem koniecznym ale niewystarczającym Warunkiem wystarczającym jest ten sam lub silniejszy predykat A =B jeżeli
A={x| ϕ(x)},B={x| φ(x)} |A|=|B| oraz ϕ⇒ φ.
Liczba wierszy spełniających predykat jest równa liczbie oczekiwanej.
Jeżeli podzbiór ma liczbę elementów równą nadzbiorowi to znaczy że jest mu równy.
41
Ogólne zasady dla dzielenia relacyjnego 3
Równość zbiorów
Przykład
Zadanie znaleźć pracowników którzy dostarczających te same produkty Znaleźć pracowników dostarczających te same kategorie produktów.
Sprawdzanie równości zbiorów wynikowych:
1. A=B ⇔ (A∪B)- (A∩B) =0 (zapisać ten fakt przy użyciu SQL-owych działań na zbiorach formułując odpowiednie zapytania zagnieżdżone) 2. Drugi sposób polega na tym żeby dwa razy skorzystać z podwójnej negacji w celu sprawdzenia czy A sprzedaje wszystko co B (tj. czy A ⊂ B)oraz czy B sprzedaje wszystko co A (B ⊂ A)
3. Wariant p2. korzystający ze zliczania zamiast z podwójnej negacji.
42
Ogólne zasady dla dzielenia relacyjnego 4
Uogólnienia dzielenia relacyjnego:
1) Definicja podstawowa E.F. Codd "A relational Model of large Shared Data Banks"
2) Uogólniona definicja Stephen Todd A{X,Y} i B{Y,Z} A/B zawiera takie wiersze X,Z że
{X,Y} występuje w A dla wszystkich {Y,Z} – prościej rozłożyć relację Y,Z na sumę względem kolumny Z, wykonać dzielenie i z powrotem zsumować wynik. Gotowe rozwiązanie zadań typu (kupują u niego wszystko co on sprzedaje) 3) Relacja uogólniona A divide B per AB , definicja (p. Date ) A.X where forall B exists AB {A.X=AB.X and B.Y=AB.Y}
4) Co to znaczy "uogólniona definicja"? ☺
4a) Pokazać że podstawowa definicja dzielenia relacyjnego jest szczególnym przypadkiem definicji podanych w punktach 2 i 3.
4b) Zadanie: Przyjąć A=Customers, B=Employees a tabelka AB = select CustomerID,EmployeeID from Orders, jak zinterpretować wtedy A divide B per AB? Zapisać ze zliczaniem i podwójną negacją.
4c) Ułożyć przykład dla definicji z p.2 i bazy Northwind.
43