1

Bazy Danych

Prowadzący:
Andrzej Z. Sikorski

Podręcznik:
Chris J. Date Wprowadzenie do Systemów Baz Danych
Rozdziały 8, 10 (dodatkowo 11)

Oprogramowanie:
MS SQL Server

2

S# P# J # QTY
S1

P1

J 1

200

S1

P1

J 4

700

S2

P3

J 1

400

S2

P3

J 2

200

S2

P3

J 3

200

S2

P3

J 4

500

S2

P3

J 5

600

S2

P3

J 6

400

S2

P3

J 7

800

S2

P5

J 2

100

S3

P3

J 1

200

S3

P4

J 2

500

S4

P6

J 3

300

S4

P6

J 7

300

S5

P2

J 2

200

S5

P2

J 4

100

S5

P5

J 5

500

S5

P5

J 7

100

S5

P6

J 2

200

S5

P1

J 4

100

S5

P3

J 4

200

S5

P4

J 4

800

S5

P5

J 4

400

S5

P6

J 4

500

S#

SNAME

STATUS CITY

S1

Smith

20 London

S2

J ones

10 Paris

S3

Blake

30 Paris

S4

Clark

20 London

S5

Adams

30 Athens

P#

PNAME

COLOR

WEIGHT CITY

P1

Nut

Red

12.00

London

P2

Bold

Green

17.00

Paris

P3

Screw

Blue

17.00

Rome

P4

Screw

Red

14.00

London

P5

Cam

Blue

12.00

Paris

P6

Cog

Red

19.00

London

J #

J NAME

CITY

J 1

Sorter

Paris

J 2

Dispay

Rome

J 3

OCR

Athens

J 4

Console

Athens

J 5

RAID

London

J 6

EDS

Oslo

J 7

Tape

London

Chris Date: Dostawcy-Części-Projekty

3

Terminologia

S

(SUPPLIER) Dostawca

P

(PART) Część

J

(JOB) Projekt, Zadanie, Przedsięwzięcie

SPJ SUPPLIER- PART- JOB

QTY (Quantity) ilość

4

CREATE TABLE [J] (

[J#] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[JNAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[CITY] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(

[J#]

) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE J( J# varchar(10) NOT NULL PRIMARY KEY,

JNAME varchar(100),

CITY varchar(100))

Tabelka projektów

5

CREATE TABLE P (P# varchar(10) NOT NULL PRIMARY KEY,
PNAME

varchar(100),

COLOR

varchar(100),

WEIGHT numeric(5,2),
CITY

varchar(100))

CREATE TABLE S (S# varchar(10) NOT NULL PRIMARY KEY,

SNAME varchar(100),

STATUS

int,

CITY varchar(100))

Tabelki części i dostawców

6

Dostawy

CREATE TABLE SPJ (

S# varchar(10) references S( S#),

P# varchar(10) references P( P# ),

J# varchar(10) references J( J# ),

QTY int

)

7

Ogólna postać polecenia CREATE TABLE

CREATE TABLE
    [ database_name.[ owner ] . | owner. ] table_name
    ( { < column_definition >
        | column_name AS computed_column_expression
        | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
            | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
    )

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

8

Model O-Z bazy Dostawcy-części-

projekty

J

J #
J NAME
CITY

varchar(10)
varchar(100)
varchar(100)

<pk>

P

P#
PNAME
COLOR
WEIGHT
CITY

varchar(10)
varchar(100)
varchar(100)
numeric(5,2)
varchar(100)

<pk>

S

S#
SNAME
STATUS
CITY

varchar(10)
varchar(100)
int
varchar(100)

<pk>

SPJ

S#
P#
J #
QTY

varchar(10)
varchar(10)
varchar(10)
int

<fk3>
<fk2>
<fk1>

9

Najprostsze zapytanie

select * from S

rezultat

S#

SNAME

STATUS CITY

S1

Smith

20 London

S2

J ones

10 Paris

S3

Blake

30 Paris

S4

Clark

20 London

S5

Adams

30 Athens

select * from (select * from S) sx

10

Proste zapytania 1

select * from S
where status >10

rezultat

select * from (select * from S where status >10) sx

select * from (select * from S) sx
where status >10

S#

SNAME

STATUS CITY

S1

Smith

20 London

S3

Blake

30 Paris

S4

Clark

20 London

S5

Adams

30 Athens

11

Proste zapytania 2

select S# from S

rezultat

select S# from (select * from S) sx

select * from (select S# from S) sx

S#
S1
S2
S3
S4
S5

12

Proste zapytania 3

select S# from S
where status >10

rezultat

select S# from (select * from S where status >10) sx

S#
S1
S3
S4
S5

13

select P.COLOR,P.CITY
from P
where
not P.CITY='Paris' and
P.Weight>10

COLOR

CITY

Red

London

Blue

Rome

Red

London

Red

London

Podaj miasto i kolor części nie pochodzących z Paryża o wadze
większej niż 10.

Proste zapytania 4

Zadanie: za pomocą zapytań w klauzuli from podać postacie
równoważne! Ile jest takich postaci?

14

select P.COLOR,P.CITY from P //tylko COLOR i
CITY

select * from P //wszystkie kolumny

//znowu wszystkie – wymienione explicite
select P.P#,P.PNAME,P.COLOR,P.WEIGHT,P.CITY from P

select COLOR,CITY from P //tylko COLOR i CITY
//nazwy jednoznaczne można opuścić tabelkę

Projekcja – wybór podzbioru kolumn

15

select * from P //wszystkie wiersze

//waga większa niż 15 uncji (przykład amerykański)
select * from P where weight>15

Selekcja – wybór podzbioru wierszy

//waga >15 i miasto pochodzenia Paryż
select * from P where weight>15 and City=‘Paris’

16

select count(*) from P
rezultat 6
//Grupę stanowi cała tabelka!

Grupowanie 1

1.

2.

select avg(WEIGHT) from P
rezultat 15.166666

3.select count(*) from P

where City=‘London’
rezultat 3

4.select count(*),City from P

5.

select count(*),max(City) from P

17

select count(*) from P
group by City
//Grupowanie wg. miasta

Grupowanie 2

1.

3
2
1

select count(*),City from P
group by City
//można użyć agregatów i pól
//grupujących

2.

CITY

3 London
2 Paris
1 Rome

select count(*) as Count,City from P
group by City
//nazwane pole agregatu

3.

18

select count(*) as count,City
from P
where weight>12
group by City
//warunek selekcji

Grupowanie 3

1.

select count(*) as count,City
from P
where weight>12
group by City
having count(*)>1

2.

count

CITY

2 London
1 Paris
1 Rome

count

CITY

2 London

19

Grupowanie 4

select * from
(select count(*) as c,City
from P
where weight>12
group by City) px
where c>1
//where zamiast having –dzięki użyciu zapytania we from

1.

2.

c

CITY

2 London

//wyznaczyć największą średnią wagę wg. miast
select max(a) from
(select avg(weight) a from P
group by City) px
//Częsty błąd max(avg(x))

20

Przykład

Znaleźć miasto dla którego średnia waga części jest największa!

select City from p
group by City
having avg(weight)=
(select max(a) from
(select avg(weight) a from P
group by City) px)

select City from p
group by City
having avg(weight)=
„największa wartość”

1.

2.

21

Zagnieżdżanie (wstęp)

select City from p
group by City
having avg(weight)=
(select max(a) from
(select avg(weight) a from
P
group by City) px)

1) Przykład z p.19 tzw. FROM LISTEN SUBQUERY
2) Zapytanie skalarne - wynik pojedyncza wartość

jeden wiersz jedna kolumna używana zamiast
stałej w selekcji

(jedna kolumna w
select i grupowanie
bez group by)

22

Złączenie 1(join)

1.

select count(*) from SPJ  24
select count(*) from P  6
select count(*) from P,SPJ 

6*24=144

Iloczyn kartezjański (każdy z każdym)

2.

Podzbiór iloczynu kartezjańskiego bo z selekcją. Dwie notacje

select * from P,SPJ where P.P#=SPJ.P#

select * from P join SPJ on P.P#=SPJ.P#

23

Złączenie 2

3.

select count(*) from P,SPJ,S 

6*24*5=720

Iloczyn kartezjański trzech tabelek

4.

Notacja where

select * from P,SPJ,S where
P.P#=SPJ.P# and
S.S#=SPJ.S#

select * from P join SPJ on
P.P#=SPJ.P# join
S on S.S#=SPJ.S#

5.

Ta „druga” notacja Samodzielnie opracować przykład dla
czterech tabelek!

24

Złączenie 2a

select count(*) from P,SPJ,S 

6*24*5=720

select count(*) from P  6
select count(*) from SPJ

 24

select count(*) from S

 5

Zadanie: ile będzie wierszy w wyniku?

select * from P,SPJ,S
where
P.P#=SPJ.P#

Iloczyn kartezjański trzech tabelek

25

Złączenie 2b

ile teraz będzie wierszy w wyniku?

select * from P,SPJ,S
where
S.S#=SPJ.S#

Już prościzna.

26

Złączenie 3

6.

select * from P cross join SPJ

Iloczyn kartezjański dla notacji z użyciem join

Po co?

select * from P cross join SPJ join S
on SPJ.S#=S.S#

Jest odpowiednikiem zapytania:

select * from P,SPJ,S where
S.S#=SPJ.S#

Lub równoważnie:

select * from P,SPJ join S on
SPJ.S#=S.S#

27

Zadanie 1. zapisać wykorzystując notację cross join

select * from P,SPJ,S
where
P.P#=SPJ.P#

Złączenie 3a

Zadanie 2. zapisać powyższe zapytanie na wszystkie
możliwe sposoby.
Wykorzystać notację where oraz join. Można też skorzystać
(premiowane) From Listen Subquery.

28

select

*

from

SPJ

,

P

,

S

where

P

.

P#

=

SPJ

.

P#

select

*

from

P

,

SPJ

cross

join

S

where

P

.

P#

=

SPJ

.

P#

select

*

from

P

join

SPJ

on

P

.

P#

=

SPJ

.

P#

cross

join

S

select

*

from

P

join

SPJ

on

P

.

P#

=

SPJ

.

P#

,

S

Złączenie 3b

Zadanie 2. Rozwiązanie. Muszą być 4 możliwości
(dlaczego?)

From listen subquery

select

*

from

S

,

(

select

P

.

P#

from

SPJ

join

P

on

P

.

P#

=

SPJ

.

P#

)

t

Dlaczego nie może być
tutaj *?

Wskazówka.

29

Złączenie 4 (przykłady)

select CITY,QTY from SPJ join J on
J.J#=SPJ.J#

Zadanie wymagające użycia złączenia, wypisać wielkości
dostawy wraz z miastami projektów, których dotyczyły.

 Złączenie + projekcja

Pary: miasto składowania – miasto projektu dla dostaw.

select distinct P.CITY,J.CITY from
P,J,SPJ
where SPJ.J#=J.J# and SPJ.P#=P.P#

30

Złączenie 5 (przykłady c.d.)

Baza Northwind: pary pracowników z tego samego
miasta:

select e1.LastName,e2.LastName,e1.City
from Employees e1,Employees e2
where
e1.City=e2.City and
e1.LastName<e2.LastName

Podobne, Baza SPJ: pary części składowanych w tych
samych miastach.

select p1.P#,p2.P# from P p1 join P p2 on p1.City=p2.City

31

Złączenie 6 (przykłady c.d.)

P1

P1

P1

P4

P1

P6

P2

P2

P2

P5

P3

P3

P4

P1

P4

P4

P4

P6

P5

P2

P5

P5

P6

P1

P6

P4

P6

P6

Element w parze z „sobą”

powtórki

Dlatego należy dodać:
...
and p1.P#<p2.P#

32

Złączenie 7 MS SQL

< joined_table > ::=
    < table_source > < join_type > < table_source >
ON
<
search_condition >
    | < table_source > CROSS JOIN < table_source >
    | [ ( ] < joined_table > [ ) ]
< join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL }
[ OUTER] } ]
    [ < join_hint > ]
    JOIN

Przykładowe złączenie (wykorzystano wszystkie możliwości składniowe):

select count(*) from S inner loop join SPJ on S.S#<>SPJ.S#
Wynik 24*4=96 (dlaczego?)

33

Wszystko razem

Wypisać nazwy części z Londynu, których sumaryczne dostawy przekroczyły 1000:

select P.P#,max(PNAME) from P,SPJ
where
SPJ.P#=P.P# and
CITY='London'
group by P.P#
having sum(QTY)>1000

Selekcja, Projekcja,Grupowanie, Złączenie

34

Chris J. Date 8.3.5

select distinct S.CITY as SCITY, P.CITY as PCITY
from S join SPJ on S.S#=SPJ.S# join P on P.P#=SPJ.P#

MS SQL

select distinct S.CITY as SCITY, P.CITY as PCITY
from S join SPJ using S# join P using P#

Date str.268, podaj pary nazw miast, że dostawca z pierwszego miasta
Dostarcza część składowaną w drugim (SCITY,PCITY)

35

Chris J. Date 8.3.6

Date str.268, Numery par dostawców pochodzących z tego samego miasta

select First.S# as SA, Second.S# as SB from
S First,S Second
where
First.City=Second.City
and
First.S#<Second.S#

W tym przypadku można uruchomić pod MS SQL bez zmian!
Co jeśli pominąć drugi warunek klauzuli where?

36

Chris J. Date 8.3.7

Date str.268, Podaj liczbę dostawców!

select count(*) as N from S

Date str.268, Podaj liczbę miast w których rezydują dostawcy!

select count(CITY) as N from S

select count(distinct CITY) as N from S

37

Chris J. Date c.d.

8.3.8 Podać maksymalną i minimalna liczbę części P2 na zamówieniu!

select max(QTY) as MAXQ,min(QTY) as MINQ from SPJ
where P#=‘P2’

8.3.9 Podać numery części wraz z odpowiednimi wielkościami dostaw!

select P#,sum(QTY) as TOTQTY from SPJ
group by P#

Numery części wraz z liczbami zamówień?
- ze średnią wielkością zamówienia?

38

Chris J. Date c.d.

8.3.9 Alternatywne rozwiązanie.

select P.P#,
(select sum(QTY) from SPJ where SPJ.P#=P.P#) as TOTQTY
from P

8.3.10 Podaj numery części dostarczanych przez więcej niż jednego dostawcę!

select P# from SPJ
group by P#
having count(distinct S#)>1

Termin: Select Listen Subquery

39

Chris J. Date (klauzula in)

8.3.11 Nazwy dostawców dostarczających część P2!

select distinct SNAME from S
where
S# in (select S# from SPJ where SPJ.P#='P2')

1) Wprowadzono operator in w warunku.
2) Jaka jest „złożoność obliczeniowa” tego zapytania?

Przeszukiwanie wyniku select S# from SPJ where
SPJ.P#='P2' nie może już korzystać z klucza!

3) Poprawne rozwiązanie na następnej stronie!
4) Zagnieżdżanie (użycie nawiasów i podzapytań) jest tak

ważne, że będzie oddzielny wykład.

40

Chris J. Date (klauzula exists)

8.3.11 Nazwy dostawców dostarczających część P2! Alternatywne rozwiązania.

select SNAME from S
where
exists( select * from SPJ where S.S#=SPJ.S# and SPJ.P#='P2')

select max(SNAME) from SPJ,S
where
P#='P2' and
SPJ.S#=S.S#
group by S.S#

Nowy typ warunku: exists.
Prawdopodobnie najbardziej efektywne rozwiązanie:

Logicznie, max można by opuścić ale...

41

Chris J. Date 8.3.11,13

8.3.11 Ostatni wariant: distinct zamiast max oraz grupowania.

select distinct SNAME from SPJ,S
where
P#='P2' and
SPJ.S#=S.S#

8.3.13 Podaj dostawców mających status mniejszy od aktualnie największego!

select S# from S
where
STATUS<(select max(STATUS) from S)

Dla zabawy (ale się przyda) bez max. Tj. istnieje o wyższym statusie)

42

8.3.12 – in kontra join

select distinct SNAME from S
where S# in
(select SPJ.S# from SPJ
where SPJ.P# in (select P# from P where Color='Red'))

A efektywniej byłoby:

select distinct SNAME from SPJ,P,S
where
SPJ.P#=P.P# and
SPJ.S#=S.S# and
Color='Red'

8.3.12 Podać nazwy dostawców co najmniej jednej czerwonej części!

43

8.3.15 – exists w nowej postaci

Wersja z operatorem in samodzielnie.

8.3.12 Podać nazwy dostawców nie dostarczających P2. Wariant z exists.

select SNAME from S
where
not exists( select * from SPJ where S.S#=SPJ.S# and SPJ.P#='P2')

44

select

S#

from

S

where

not

exists

(

select

*

from

SPJ

where

P#

=

'P2'

and

SPJ

.

S#

=

S

.

S#

)

select

S#

from

S

where

not

exists

(

select

*

from

SPJ

where

P#

=

'P3'

and

SPJ

.

S#

=

S

.

S#

)

Dostawcy nie sprzedający określonej części (1)

Powinno działać zawsze

45

Dostawcy nie sprzedający określonej części (1a)

Tu będzie kiedyś slajd 1a o zanegowaniu złączenia.
Nie można po prostu zanegować warunku ci co
kupują różne od p2 to nie są ci sami co nie kupują p2

46

Dostawcy nie sprzedający określonej części (1a)

select

distinct

CompanyName

from

Customers c

,

Orders o

,

[Order Details] od

,

Products p

,

Categories c1

where

c

.

CustomerID

=

o

.

CustomerID

and

o

.

OrderID

=

od

.

OrderID

and

od

.

ProductID

=

p

.

ProductID

and

p

.

CategoryID

=

c1

.

CategoryID

and

c1

.

CategoryName

=

'Beverages'

Zadanie lab3-2g
Optymalny zapis – wyszukiwanie po kluczach zawsze jest
szybsze niż podzapytania.
Samodzielnie: zapisać z użyciem in albo exists

47

Dostawcy nie sprzedający określonej części (2)

Trochę o popełnianych błędach. Część P2 sprzedaje tylko S5

select

S#

from

S

where

S#

<>(

select

distinct

S#

from

SPJ

where

P#

=

'P3'

)

Co się stanie dla części sprzedawanych przez
większą liczbę dostawców?

Niby drobiazg

select

S#

from

S

where

S#

<>(

select

distinct

S#

from

SPJ

where

P#

=

'P2'

)

48

Dostawcy nie sprzedający określonej części (3)

Ten sam błąd (założenie że tylko jeden sprzedaje) ale inny skutek.

select

S

.

S#

from

S

,

(

select

distinct

S#

from

SPJ

where

P#

=

'P2'

)

spj1

where

spj1

.

S#

<>

S

.

S#

Załóżmy, że jest więcej sprzedających (P3) – są to S2,S3,S5
S X {S2,S3,S5} (takie, że S.S#<>SPJ1.S#)

select

S

.

S#

from

S

,

(

select

distinct

S#

from

SPJ

where

P#

=

'P3'

)

spj1

where

spj1

.

S#

<>

S

.

S#

49

Dostawcy nie sprzedający określonej części (4)

S

spj1

S1

S2

S2

S2

S3

S2

S4

S2

S5

S2

S1

S3

S2

S3

S3

S3

S4

S3

S5

S3

select

S

.

S#

from

S

,

(

select

distinct

S#

from

SPJ

where

P#

=

'P3'

)

spj1

where

spj1

.

S#

<>

S

.

S#

Zgodnie z oczekiwaniami
usunięte

Niestety!

Błąd dużo gorszy, bo
tamten
powodował komunikat

Już nawet nie trzeba zaglądać do par z S5!

50

Dostawcy nie sprzedający określonej części (4a)

select

S

.

S#

from

S

,

(

select

distinct

S#

from

SPJ

where

P#

=

'P2'

)

spj1

where

spj1

.

S#

<>

S

.

S#

S

spj1

S1

S5

S2

S5

S3

S5

S4

S5

S5

S5

Teraz OK ale tylko dlatego, że spj1 ma jeden
element!
Liczona ma być różnica zbiorów.

51

Dostawcy nie sprzedający określonej części (5)

O pewnej „wymuszonej” na prowadzącym ocenie 5.0.

select

S#

from

S

where

(

select

COUNT

(*)

from

SPJ

where

S

.

S#

=

SPJ

.

S#

and

P#

=

'P3'

)=

0

Niby miało być bez exists oraz in ale exists to po prostu:

select

S#

from

S

where

(

select

COUNT

(*)

from

SPJ

where

S

.

S#

=

SPJ

.

S#

and

P#

=

'P3'

)>

0

select

S#

from

S

where

exists(

select

*

from

SPJ

where

S

.

S#

=

SPJ

.

S#

and

P#

=

'P3'

)

52

Dostawcy nie sprzedający określonej części (6)

select

S

.

S#

,

P#

from

S

left

outer

join

SPJ

on

SPJ

.

P#

=

'P3'

and

SPJ

.

S#

=

S

.

S#

S1

NULL

S2

P3

S2

P3

S2

P3

S2

P3

S2

P3

S2

P3

S2

P3

S3

P3

S4

NULL

S5

P3

W kolumnie dostawców
znajdą
się też wszyscy również ci
co nie dostarczają –
wartością NULL

Szukany zbiór dostawców
wystarczy dodać
warunek.

53

Dostawcy nie sprzedający określonej części (7)

Pojęcie „naukowe” (czyt. nałkowe): left anti join

select

S

.

S#

from

S

left

outer

join

SPJ

on

SPJ

.

P#

=

'P3'

and

SPJ

.

S#

=

S

.

S#

where

SPJ

.

P#

is

null

1) Złączenie i antyzłączenie. Lewe i prawe bo

nieprzemienne.

2) Zadanie można też sformułować jako różnicę

zbiorów:

S-(ci co dostarczają P3)
3) Nowe słowo kluczowe is nie ma czegoś takiego

NULL=NULL czy dwie „próżnie” są sobie
równe? Dwie lokacje są puste czy zawierają to
samo jeśli nic nie zawierają? (filozofia)

54

Dostawcy nie sprzedający określonej części (8)

Operator różnicy except (i na razie koniec z tym
zadaniem) wróci jeszcze z bazą Northwind i
napojami bezalkoholowymi (i.e. beverages)

select

S#

from

S

except

select

S#

from

SPJ

where

P#

=

'P3'

1) Tylko pewna sztuczka notacyjna tzw. lukier

(syntactic sugar)

2) W praktyce sprowadza się do operatora not in

podobnie jak count()=0 i not exists

3) Technika z outer join warta zapamiętania

ponieważ serwery SQL są zoptymalizowane pod
kątem złączeń

4) Except też warte – bo będzie w dzieleniu

relacyjnym,

55

Podwójna agregacja (1)

select

MAX

(

Qty

)

from

(

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group

by

OrderID

)

t

select

Min

(

Qty

)

from

(

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group

by

OrderID

)

t

Wyznaczyć ekstremalne wartości sumaryczne zamówień
(sumy order details zgrupowane dla OrderID)

56

Podwójna agregacja (2)

select

MAX

(

Qty

)

from

(

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group

by

OrderID

)

t

• Wykorzystanie FROM LISTEN SUBQUERY.

• Kiedyś tylko w Oracle, w innych SQL konieczne

zdefiniowane „pomocniczej tabelki”
przechowującej sumy.

• Wykonanie następnej agregacji (max/min) na

pomocniczym zbiorze wynikowym.

• Wyższa szkoła jazdy (master class) – zrobić to

bez zbioru pomocniczego (zaraz będzie).

Zagnieżdżenie (zapisać na „boku”
nazwać t) i wykonać select
max(Qty) from t

57

Podwójna agregacja (3)

with

t

as

(

select

SUM

(

Quantity

*

UnitPrice

)

Qty

from

[Order Details]

group

by

OrderID

)

select

MAX

(

qty

)

from

t

Fajne tylko chyba pojawiło się jeszcze później niż FROM LISTEN.

create

view

t

as

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group

by

OrderID

Fajne tylko zostaje t czasem nie wiadomo po co.

58

Podwójna agregacja (3a)

• Tymczasowy obiekt pomocniczy.

• Były już zmienne i polecenie declare (dekompozycja

złożonego zapytania na zapytania prostsze)

• Zmienna może również być typu tablicowego i.e.

przechowywać tymczasową tablicę

declare

@t

table

(

qty

float

);

insert

@t

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group

by

OrderID

select

MAX

(

qty

)

from

@t

• Jest jeszcze jedna możliwość tworzenia obiektów pom.

Ale o tym kiedy indziej

59

Podwójna agregacja (4)

• Możliwe rozwiązanie bez obiektów pomocniczych

oraz bez from listen.

• Przedefiniowanie pytania – technika nie jest

uniwersalna.

• Wartość największa  nie istnieje taka sumaryczna

wartość która jest większa

• Dla wartości najmniejszej analogicznie

60

Podwójna agregacja (4a)

select

distinct

OrderID

from

[Order Details] od1

where

not

exists

(

select

*

from

[Order Details] od2

where

(

select

SUM

(

Quantity

*

UnitPrice

)

from

[Order Details] od3

where

od3

.

OrderID

=

od2

.

OrderID

)>

(

select

SUM

(

Quantity

*

UnitPrice

)

from

[Order Details] od3

where

od3

.

OrderID

=

od1

.

OrderID

)

)

61

Zadania lab 3-4c,d (1)

4. Baza Northwind. Agregacja i złączenia

(wstęp) Zwróćmy uwagę, że warunki z
zadań 3B-3E mogłyby dotyczyć
sumarycznych dostaw (suma QTY ze
wszystkich odpowiednich wierszy tabeli
SPJ). W takim wypadku należy się posłużyć
złączeniem i agregacją.

…

C. Klienci (CompanyName) wraz z wartością

zamówień

D. Klienci (CompanyName) o wartości

zamówień większej od średniej

…

62

Zadania lab 3-4c,d (2)

Drobna modyfikacja: zamiast klientów kategorie
(logika ta sama – struktura zapytania też –
dlaczego?)

selects

sum

(

Quantity

*

Products

.

UnitPrice

),

CategoryID

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

group

by

CategoryID

63

Zadania lab 3-4c,d (3)

select

sum

(

Quantity

*

Products

.

UnitPrice

),

(

select

CategoryName

from

Categories

where

Categories

.

CategoryID

=

Products

.

CategoryID

)

CategoryName

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

group

by

CategoryID

Nazwa zamiast CategoryID (3 sposoby):

select

sum

(

Quantity

*

Products

.

UnitPrice

),

MAX

(

CategoryName

)

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

join

Categories

on

Categories

.

CategoryID

=

Products

.

CategoryID

group

by

Products

.

CategoryID

64

Zadania lab 3-4c,d (4)

select

sum

(

Quantity

*

Products

.

UnitPrice

),

CategoryName

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

join

Categories

on

Categories

.

CategoryID

=

Products

.

CategoryID

group

by

Products

.

CategoryID,CategoryName

„Sztuczne” grupowanie,
zamiast „sztucznej”
agregacji

65

Zadania lab 3-4c,d (5)

select

sum

(

Quantity

*

Products

.

UnitPrice

),

MAX

(

CategoryName

)

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

join

Categories

on

Categories

.

CategoryID

=

Products

.

CategoryID

group

by

Products

.

CategoryID

having

sum

(

Quantity

*

Products

.

UnitPrice

)>”średnia wartość obrotów”

Zadanie 4d (zmodyfikowane, bo dla kategorii):

select

avg

(

total

)

from

(

select

sum

(

Quantity

*

Products

.

UnitPrice

)

total

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

group

by

CategoryID

)

t

66

Zadania lab 3-4c,d (6)

Połączenie dwóch zapytań – rozwinięcie lub zmienna pomocnicza.
Złożone (complex) ale nie trudne – poskładane z klocków

 

select

max

(

CategoryName

)

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

join

Categories

on

Categories

.

CategoryID

=

Products

.

CategoryID

group

by

Products

.

CategoryID

having

sum

(

Quantity

*

Products

.

UnitPrice

)>

(

select

avg

(

total

)

from

(

select

sum

(

Quantity

*

Products

.

UnitPrice

)

total

from

[Order Details]

join

Products

on

Products

.

ProductID

=

[Order Details]

.

ProductID

group

by

CategoryID

)

t

)

67

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

Wynik: 6

68

Typ zapytań „dla każdego” 2

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)

69

Typ zapytań „dla każdego” 3

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!

70

Typ zapytań „dla każdego” 4

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#))

Skoro dostarcza wszystko to nie ma takiej części której by nie dostarczał.

Teraz to samo w zwolnionym tempie

71

Typ zapytań „dla każdego” 5

select S# from S
where
not exists (
„część której S# nie dostarcza ”)

Zapytanie ma znaleźć dostawców więc tabelka S.

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#”)

72

Typ zapytań „dla każdego” 6

select P# from P
where
not exists (select * from SPJ
where SPJ.P#=P.P# and
SPJ.S#='S2')

Znaleźć części których S2 nie dostarcza!

Podsumowując:

)

(

)

(

x

x

x

x















select * from S
where forall(x,



(x))



select * from S
where not exists(x, not



(x))

73

select * from Customers c
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 )
))

Teaser/Challenge

74

Rozbudowany przykład

select P.P#,
'Waga w gramach' as text1,
P.weight*454 as GMWT,
P.COLOR,
'Maksymalna ilość' as text2,
max(SPJ.QTY) as MQTY
from P,SPJ
where P.P#=SPJ.P# and
P.COLOR in ('Red','Blue') and
SPJ.QTY>200
group by P.P#,P.WEIGHT,P.COLOR
having sum(SPJ.QTY)>1000

75

Złączenie zewnętrzne

select P.P#,QTY
from p,spj
where
spj.p#=*p.p# and spj.qty>500

select P.P#,QTY from
p left outer join (select P#,QTY from SPJ
where QTY>500) P1
on P1.P#=P.P#

76

Proste aplikacje klienckie

Odwieczny problem: klienci, klienty, agenci, agenty, jest
piloci –piloty

Te same
parametry

77

Połączenie, wykonanie prostego zapytania, rozłączyć

78

com.microsoft.sqlserver.jdbc.SQLServerDataSource ds=

new

com.microsoft.sqlserver.jdbc.SQLServerDataSource();

ds.setServerName(

"127.0.0.1"

);

ds.setDatabaseName(

"CJDate"

);

ds.setUser(

"abc"

);ds.setPassword(

"abc"

);

java.sql.Statement stmt=ds.getConnection().createStatement();
stmt.execute(

"select * from SPJ"

);

while

(stmt.getResultSet().next())

{

for

(

int

i=1;i<=stmt.getResultSet().getMetaData().getColumnCount();i++)

{ System.

out

.print(stmt.getResultSet().getString(i));

System.

out

.print(

'\t'

);

}
System.

out

.println();

}

Połączenie, wypisanie zbioru wynikowego, prawie
ManagementStudio

79

Zadania
1)Posłużyć się integrated security. Wymagana
dodatkowa konfiguracja
2)Uruchomić przykłady conntectDS i
connectURL
3)Nawiązać połączenie korzystając z
connectURL i integrated security
4)Uruchomić przykłady z wykładu w Eclipse