|
Radosław Wolicki Kierunek: ETI Rok: III Grupa: II |
Lab. Bazy Danych |
Prowadzący : dr inż. A. Sikorski |
|
Data Ćwiczenia: 07-01-2013 |
Ćwiczenie nr.13 |
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
with ep as
(select EmployeeID,ProductID from Orders,[Order Details] where Orders.OrderID=[Order Details].OrderID)
select e1.EmployeeID ,e2.EmployeeID from Employees e1, Employees e2
where
not exists (select * from ep where ep.EmployeeID=e1.EmployeeID and
not exists (select * from ep ep1 where ep1.EmployeeID=e2.EmployeeID and ep1.ProductID=ep.ProductID))
and
not exists (select * from ep where ep.EmployeeID=e2.EmployeeID and
not exists (select * from ep ep1 where ep1.EmployeeID=e1.EmployeeID and ep1.ProductID=ep.ProductID))
and
e1.EmployeeID<e2.EmployeeID
with ec as
(select EmployeeID,CategoryID from Orders,[Order Details],Products where Orders.OrderID=[Order Details].OrderID
and [Order Details].ProductID=Products.ProductID)
select e1.EmployeeID ,e2.EmployeeID from Employees e1, Employees e2
where
not exists (select * from ec where ec.EmployeeID=e1.EmployeeID and
not exists (select * from ec ec1 where ec1.EmployeeID=e2.EmployeeID and ec1.CategoryID=ec.CategoryID))
and
not exists (select * from ec where ec.EmployeeID=e2.EmployeeID and
not exists (select * from ec ec1 where ec1.EmployeeID=e1.EmployeeID and ec1.CategoryID=ec.CategoryID))
and
e1.EmployeeID<e2.EmployeeID
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 czyli 7.01.2013
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) . Skorzystać z rozwiązań zadań 3 oraz 4 dot. bazy CJDate. (UWAGA) instrukcja w zasadzie zawiera rozwiązanie – dlatego prowadzący zweryfikuje wyniki dzisiejszego laboratorium.
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
Ułożyć podobny przykład dla bazy Northwind.
a) Pary klientów kupujących to samo
b) Pary sprzedawców, to samo
c) Oba warianty dla kategorii
d) Rozważyć różne zapisy z zadania dodatkowego dla bazy CJDate
Wskazówka dot. zadania znaleźć takie pary dostawców , że SA dostarcza to wszystko co SB (por. zadanie 3 tam było S-J tutaj S-S)
select sa.S#,sb.S# from S sa, S sb
where
not exists
(select * from SPJ spj1 where spj1.S#=sb.S# and
not exists (select * from SPJ spj2 where spj2.P#=spj1.P# and
spj2.S#=sa.S#))
and sa.S#<>sb.S#
e) Dlaczego ostatni warunek (chodzi o and sa.S#<>sb.S#) ? Czy nie powinno być np.
sa.S#>sb.S# (nie powinno ale odpowiedź uzasadnić).
select sa.S#,sb.S# from S sa, S sb
where
not exists
(select * from SPJ spj1 where spj1.S#=sb.S# and
not exists (select * from SPJ spj2 where spj2.P#=spj1.P# and
spj2.S#=sa.S#))
and sa.S#>sb.S#
ODP: Wynik jest taki sam mimo zmiany ostatniego warunku.
f) Rozważyć zapis ze wskazówki (po punkcie d) z wykorzystaniem notacji z wykładu „amerykańskiego” (Uniwersytet Wisconsin , Lester McCann)
select sa.S#,sb.S# from S sa, S sb where sa.S#<>sb.S#
except
select s1,s2 from
(
select S.S# s1,SPJ.S# s2,SPJ.P# from S,SPJ
except
select SPJ.S#,S.S#,SPJ.P# from S,SPJ
) t
objaśnić powyższe zapytanie oraz wykorzystać w rozwiązaniu
g) Wykorzystać pozostałe 2 notacje z wykładu „amerykańskiego” (i.e. zawieranie się zbiorów oraz zliczanie)
h) (Dodatkowo) zapisać rozwiązanie bezpośrednio korzystając z notacji z podwójnym except (c.f. )rozwiązanie zadania 6.48 z książki. W książce opisano wyprowadzenie rozwiązania z rachunku relacyjnego (podobnie jak McCann) . Wskazówka (schemat do uzupełnienia)
select sa.S#,sb.S# from S sa, S sb
where
not exists
(select * from SPJ spj1 where spj1.S#=sb.S# and
not exists (select * from SPJ spj2 where spj2.P#=spj1.P# and
spj2.S#=sa.S#))
and
not exists
(select * from SPJ spj1 where spj1.S#=sa.S# and
not exists (select * from SPJ spj2 where spj2.P#=spj1.P# and
spj2.S#=sb.S#))
and
sb.S#<>sa.S#
Uzasadnić powyższy schemat. Pokazać, że w istocie jest to pierwsza wskazówka (AB)- (AB) .
i) Rozważyć rozwiązanie w c# (np. na serwerze aplikacji z wykorzystaniem prostszych SQL) – powiedzmy „chwilowy zanik pomysłowości” –zamiast SQL pętle. Do zrobienia w lab.
j) Zadanie 8.11.48 z książki – pozostawione czytelnikowi (e.g. studentom)
k) Zadanie na przyszły tydzień będzie polegało na uzupełnieniu bazy danych w taki sposób by zbiór wynikowy był niepusty.
Wskazówki:
1) Rozważyć przykład dot. eksploracji danych
select CategoryName,
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders
o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1996 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "1996",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders
o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1997 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "1997",
( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders
o,Products p where
o.OrderID=od.OrderId and year(o.OrderDate)=1998 and
p.ProductID=od.ProductID and
p.CategoryID=c.categoryID ) as "1998 "
from
Categories c
Uruchomić w Management Studio.
2) Deklaracja tabelki powinna zawierać 3 kolumny o typie stałoprzecinkowym numeric(10,2)
create table YearCategoryStat
(
"1996" numeric(10,2),
"1997" numeric(10,2),
"1998" numeric(10,2)
)
3) W Visual studio utworzyć aplikację konsolową i uruchomić poniższy przykład
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication3
{
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("Data Source=.;Initial
Catalog=Northwind;Integrated Security=SSPI");
conn.Open();
SqlCommand cmd = new SqlCommand(
"select CategoryName, ( select Sum(Quantity*od.UnitPrice) from [Order
Details] od,Orders o,Products p where " +
"o.OrderID=od.OrderId and year(o.OrderDate)=1996 and
p.ProductID=od.ProductID and " +
"p.CategoryID=c.categoryID ) as [1996], " +
"( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders
o,Products p where " +
"o.OrderID=od.OrderId and year(o.OrderDate)=1997 and
p.ProductID=od.ProductID and " +
"p.CategoryID=c.categoryID ) as [1997], " +
"( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders
o,Products p where " +
"o.OrderID=od.OrderId and year(o.OrderDate)=1998 and
p.ProductID=od.ProductID and " +
"p.CategoryID=c.categoryID ) as [1998] from Categories c", conn
);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
Console.Write(rdr.GetValue(i));
Console.Write("\t");
}
Console.WriteLine();
}
conn.Close();
}
}
}
4) Przeprowadzić eksperymenty z zapytaniami ze sprawozdań (lub z wykładów jeśli ktoś nie oddaje sprawozdań)
5) Zastąpić wypisywanie na konsolę poleceniami insert , posłużyć się funkcją ExecuteNonQuery
6) (Dodatkowo) spróbować wygenerować programowo polecenie create table na podstawie analizy zbioru wynikowego. Rozważyć poniższy fragment kodu:
SqlDataReader rdr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < rdr.FieldCount; i++)
{
sb.AppendFormat("{0} {1},",
new object[] {
rdr.GetName(i),rdr.GetDataTypeName(i)}
);
sb.AppendLine();
}
Polecenia aktualizujące stan bazy danych c.d.
16) Ustalić adres IP maszyny wskazanej przez prowadzącego i nawiązać połączenie sieciowe z serwerem bd. (użytkownik/xyz xyz) Wyjaśnić dlaczego w przypadku sieci korzystającej z DHCP adres ten nie jest znany z góry. Następne polecenia wykonywać tylko wskazanym na serwerze.
17) Polecenie podnoszące cenę 3 najlepiej sprzedających się produktów o 10%. Rozważyć różne (przynajmniej 2 postaci tego polecenia)
18) Podobnie jak 17, polecenie podnoszące cenę wszystkich produktów z kategorii najlepiej sprzedających się produktów.
19) Dodać nową kategorię wraz z zestawem produktów. Dodać do bazy zamówienia po 1 sztuce dla wszystkich klientów z Niemiec.
20) Dodać w tabelce Employees pole TotalSales i wypełnić odpowiednimi danymi.
21) GroupOn Powiedzmy, że Northwind weszło w kontakt z GroupOn. Dodać odpowiednie tabelki , wypełnić pola danymi. Założyć, że groupon zostanie wprowadzony do bazy przed złożeniem zamówienia. Klient może mieć wiele groupon’ów. Rozważyć wybrane) sytuacje:
a. groupon dotyczyć może pojedynczego zamówienia,
b. dotyczy pojedynczego zamówienia i tylko pewnej grupy produktów
c. ustalony na grouponie jest pewien limit kwotowy, może być rozliczony i wykorzystany przy składaniu wielu zamówień
d. jak w podpunkcie c ale z ograniczeniami z podp. B
e. każdy produkt może mieć podany upust lub alternatywnie upusty są zawarte w grouponie.
22) Uruchomić przykłady aplikacji bazodanowych korzystających z okienek. Przeanalizować kod – dobra znajomość użytych mechanizmów będzie potrzebna przy realizacji dalszych laboratoriów.