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 (AB)- (AB) =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 (AB)- (AB) .

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.