|
Radosław Wolicki Kierunek: ETI Rok: III Grupa: II |
Lab. Bazy Danych |
Prowadzący : dr inż. A. Sikorski |
|
Data Ćwiczenia: 17-12-2012 |
Ćwiczenie nr.12 |
Zestaw zadań laboratoryjnych
Nr 6
Dzielenie relacyjne, SQL, kwantyfikator ogólny Baza CJDate
1) Uruchomić przykłady podane na wykładzie.
Przykład 1
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#
)
)
Przykład 2
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#
)
Przykład 3
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#
)
)
Przykład 4
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#
)
)
Przykład 5
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#
)
)
Przykład 6
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#
)
Przykład 7
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#
)
2) Zapytania SQL: znaleźć projekty (J) które mają dostawy wszystkich części z Londynu
select J# from J
except
select J# from
(select J#,P# from P,J where P.CITY='London'
except
select J#,P# from SPJ) t
3) Znaleźć pary dostawca projekt (S-J) takie, że dostawca dostarcza wszystkie części wykorzystywane przez projekt
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#
)
)
W wersji z ''except'':
select J#,S# from S,J
except
select J#,S# from
(
select S.S#,P#,J# from SPJ,S
except
select SPJ1.S#,SPJ2.P#,SPJ2.J# from SPJ SPJ1,SPJ SPJ2
where
SPJ1.J#=SPJ2.J#
) t
4) Pary dostawca projekt takie, że projekt wykorzystuje wszystkie części dostarczane przez danego dostawcę (por. Chris Date uwagi bibliograficzne 6.4 w polskim tłumaczeniu lub 7.48th Edition w angielskojęzycznej)
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#
)
)
5) Ułożyć zapytania z p. 3,4 w obydwu wersjach tj. podwójna negacja Exists oraz sprawdzanie liczności zbioru
Zapytanie dla Zadania 3:
select J# from J where
not exists(select P# from P where CITY='London' and
not exists(select * from SPJ where SPJ.J#=J.J# and SPJ.P#=P.P#))
6) W zadaniach 7 -11 można posłużyć się tylko jedną techniką – obydwa rozwiązania dla chętnych
???
7) Podaj numery (J#) projektów zaopatrywanych całkowicie przez dostawcę S1 (Date 6.41)
select * from SPJ where S# ='S1'
select distinct J# from SPJ where S#='S1'
8) Podaj numery części (P#) dostarczanych do wszystkich odbiorców w Londynie (Date 6.42)
select P# from P where
not exists(select J# from J where CITY='London' and
not exists(select * from SPJ where SPJ.P# =P.P# and SPJ.J#=J.J#))
9) Podaj numery dostawców dostarczających tę samą część do wszystkich projektów (6.43)
select * from J
select S#,P#,COUNT(distinct J#) from SPJ
group by S#,P#
select * from S where
exists(select SPJ.P# from SPJ where SPJ.S#=S.S# and
not exists (select * from J where
not exists (select * from SPJ spj1 where spj1.J#=J.J# and spj1.P#=SPJ.P# and spj1.S#=S.S#)))
select * from S where
not exists(select * from SPJ spj1,SPJ spj2 where spj1.S#=S.S# and spj2.S#=S.S# and
not exists(select * from SPJ spj3 where SPJ3.S#=S# and spj3.J#=spj2.J# and SPJ3.P#=spj1.P#))
10) Podaj numery dostawców dostarczających wszystkie te same części do wszystkich projektów
select * from S where
not exists(select * from SPJ spj1,SPJ spj2 where spj1.S#=S.S# and spj2.S#=S.S# and
not exists(select * from SPJ spj3 where SPJ3.S#=S# and spj3.J#=spj2.J# and SPJ3.P#=spj1.P#))
11) Podaj numery projektów zaopatrywanych przynajmniej w jedną część dostępną u dostawcy S1 (6.44)
Pierwszy Sposób:
select distinct J# from SPJ where
P# in (select P# from SPJ where S#='s1')
Drugi Sposób:
select distinct spj1.J# from SPJ spj1, SPJ spj2 where
spj2.S#='s1' and spj1.P#=spj2.P#
Baza Northwind
1) Znaleźć CustomerID takich klientów co kupują wszystko
select * from Products p
where
not exists (select * from Customers c where
not exists(
select * from Orders o join [Order Details] od on o.OrderID=od.OrderID
where CustomerID=C.CustomerID and ProductID=p.ProductID
))
2) Znaleźć ProductID produktów dostarczanych do wszystkich klientów
select * from Categories c1
where
not exists (select * from Customers c where
not exists(
select * from Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
where CustomerID=C.CustomerID and c1.CategoryID=p.CategoryID
)
)
Sprawdzenie że działa:
select * from Customers c
where
not exists (select * from Categories c1 where
not exists(
select * from Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
where CustomerID=C.CustomerID and c1.CategoryID=p.CategoryID
)
)
3) Zadania analogiczne do p.3 i 4 dla bazy CJDate znaleźć pary pracownik (Employee) klient (Customer) takie, że pracownik obsługuje sprzedaż wszystkich produktów kupowanych przez klienta
4) Takie pary pracownik klient że klient kupuje wszystko co dany pracownik sprzedaje
5) To samo dla kategorii
6) Rozważyć wykorzystanie CTE w celu uproszczenia, zastanowić się, jaka definicja CTE będzie najbardziej sensowna
7) Wybrane zadanie dot. bazy northwind rozwiązać za pomocą obydwu technik (negacji i
zliczania)
Zadanie dodatkowe
Rozważyć następujący problem: (Date 6.48 tłumaczenie polskie) znaleźć pary dostawców dostarczających te same części. Można zapoznać się z rozwiązaniem w książce. Wskazówka prowadzącego (znacznie prostsze rozwiązanie) .
a) Sposób pierwszy skorzystać w faktu że
A=B (AB)- (AB) =0
I zapisać ten fakt przy użyciu SQL-owych działań na zbiorach )
b) 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 , skorzystać z
rozwiązania zadań 3 lub 4.
c) Można też rozważyć punkt b z wykorzystaniem zliczania
d) To samo dla kategorii
Ułożyć podobny przykład dla bazy Northwind