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

J1

200

S1

P1

J4

700

S2

P3

J1

400

S2

P3

J2

200

S2

P3

J3

200

S2

P3

J4

500

S2

P3

J5

600

S2

P3

J6

400

S2

P3

J7

800

S2

P5

J2

100

S3

P3

J1

200

S3

P4

J2

500

S4

P6

J3

300

S4

P6

J7

300

S5

P2

J2

200

S5

P2

J4

100

S5

P5

J5

500

S5

P5

J7

100

S5

P6

J2

200

S5

P1

J4

100

S5

P3

J4

200

S5

P4

J4

800

S5

P5

J4

400

S5

P6

J4

500

S#

SNAME

STATUS

CITY

S1

Smith

20 London

S2

Jones

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#

JNAME

CITY

J1

Sorter

Paris

J2

Dispay

Rome

J3

OCR

Athens

J4

Console

Athens

J5

RAID

London

J6

EDS

Oslo

J7

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

Jones

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

select distinct

CompanyName

from

Customers c

where

not

exists

(

select

*

from

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'

)

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

Zastąpienie join przez exists , potem już proste dodać

NOT

Jak tego uniknąć i zanegować złączenie?

48

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'

)

49

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#

50

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!

51

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.

52

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

Będzie dobrze jeśli:

select

S#

from

S

where

S#

not in

(

select

S#

from

SPJ

where

P#

=

'p3'

)

select

S#

from

S

except

select

S#

from

SPJ

where

P#

=

'p3'

lub

Ciągle 2 podzapytania, chcemy zrobić to jednym. (in, exist, except)

53

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'

)

54

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.

55

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

56

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,
5) Tak naprawdę wydajny SQL optymalizuje anti join

57

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)

58

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group by

OrderID

Podwójna agregacja (1a)

jest jakaś największa wartość

59

Podwójna agregacja (1b)

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group by

OrderID

order by

1

select

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group by

OrderID

order by

1

desc

select

top

1

SUM

(

Quantity

*

UnitPrice

)

as

Qty

from

[Order Details]

group by

OrderID

order by

1

desc

60

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

61

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.

62

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

63

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

64

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

)

)

65

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

…

66

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

67

Zadania lab 3-4c,d (3)

Nazwa zamiast CategoryID (3 sposoby):

Będą pokazane 3 techniki:
-zagnieżdżenie w klauzuli select
-sztuczna agregacja (tj. użycie funkcji agregującej tylko w celu
zapewnienia poprawności składniowej)
-dodatkowy argument w group by

68

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

69

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

70

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

71

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

)

72

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

73

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)

74

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!

75

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

→

76

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

77

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

78

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

79

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

80

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#

81

Proste aplikacje klienckie

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

Te same
parametry

82

try

{

com.microsoft.sqlserver.jdbc.SQLServerDataSource ds=

new

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

ds.setServerName(

"MIROSŁAW-PC\\SQL"

);

ds.setDatabaseName(

"Northwind"

)

//ds.setIntegratedSecurity(true);

ds.setPassword(

"abc"

); ds.setUser(

"abc"

);

Connection con = ds.getConnection();

Statement stm =con.createStatement();

stm.execute(

"select count(*) from [Order Details]"

);

stm.getResultSet().next();

System.

out

.println(stm.getResultSet().getInt(1));

con.close();

}

catch

(Exception e){}

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

83

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

84

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