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

S# P# J # QTY
S1

J 1

200

J 4

700

J 1

400

J 2

200

J 3

200

J 4

500

J 5

600

J 6

400

J 7

800

J 2

100

J 1

200

J 2

500

J 3

300

J 7

300

J 2

200

J 4

100

J 5

500

J 7

100

J 2

200

J 4

100

J 4

200

J 4

800

J 4

400

J 4

500

SNAME

STATUS CITY

Smith

20 London

J ones

10 Paris

Blake

30 Paris

Clark

20 London

Adams

30 Athens

PNAME

COLOR

WEIGHT CITY

Nut

Red

12.00

London

Bold

Green

17.00

Paris

Screw

Blue

17.00

Rome

Screw

Red

14.00

London

Cam

Blue

12.00

Paris

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

Terminologia

(SUPPLIER) Dostawca

(PART) Część

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

SPJ SUPPLIER- PART- JOB

QTY (Quantity) ilość

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]

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

JNAME varchar(100),

CITY varchar(100))

Tabelka projektów

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

Dostawy

CREATE TABLE SPJ (

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

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

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

QTY int

)

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 } ]

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

projekty

J #
J NAME
CITY

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

<pk>

P#
PNAME
COLOR
WEIGHT
CITY

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

<pk>

S#
SNAME
STATUS
CITY

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

<pk>

SPJ

S#
P#
J #
QTY

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

Najprostsze zapytanie

select * from S

rezultat

SNAME

STATUS CITY

Smith

20 London

J ones

10 Paris

Blake

30 Paris

Clark

20 London

Adams

30 Athens

select * from (select * from S) sx

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

SNAME

STATUS CITY

Smith

20 London

Blake

30 Paris

Clark

20 London

Adams

30 Athens

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

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

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?

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

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’

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

Grupowanie 1

select avg(WEIGHT) from P
rezultat 15.166666

3.select count(*) from P

where City=‘London’
rezultat 3

4.select count(*),City from P

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

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

Grupowanie 2

3
2
1

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

CITY

3 London
2 Paris
1 Rome

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

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

Grupowanie 3

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

count

CITY

2 London
1 Paris
1 Rome

count

CITY

2 London

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

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

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ść”

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)

Złączenie 1(join)

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)

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#

Złączenie 2

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

6*24*5=720

Iloczyn kartezjański trzech tabelek

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#

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

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

Złączenie 2b

ile teraz będzie wierszy w wyniku?

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

Już prościzna.

Złączenie 3

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#

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.

select

from

SPJ

where

SPJ

select

from

SPJ

cross

join

where

SPJ

select

from

join

SPJ

cross

join

select

from

join

SPJ

Złączenie 3b

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

From listen subquery

select

from

(

select

from

SPJ

join

SPJ

)

Dlaczego nie może być
tutaj *?

Wskazówka.

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#

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

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

Element w parze z „sobą”

powtórki

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

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

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

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)

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?

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

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?

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

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.

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

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)

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!

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

select

from

where

not

exists

(

select

from

SPJ

where

'P2'

and

SPJ

)

select

from

where

not

exists

(

select

from

SPJ

where

'P3'

and

SPJ

)

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

Powinno działać zawsze

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

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

CustomerID

and

OrderID

and

ProductID

and

CategoryID

and

CategoryName

'Beverages'

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

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

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

select

from

where

<>(

select

distinct

from

SPJ

where

'P3'

)

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

Niby drobiazg

select

from

where

<>(

select

distinct

from

SPJ

where

'P2'

)

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

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

select

from

(

select

distinct

from

SPJ

where

'P2'

)

spj1

where

spj1

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

from

(

select

distinct

from

SPJ

where

'P3'

)

spj1

where

spj1

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

spj1

select

from

(

select

distinct

from

SPJ

where

'P3'

)

spj1

where

spj1

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!

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

select

from

(

select

distinct

from

SPJ

where

'P2'

)

spj1

where

spj1

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

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

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

select

from

where

(

select

COUNT

(*)

from

SPJ

where

SPJ

and

'P3'

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

select

from

where

(

select

COUNT

(*)

from

SPJ

where

SPJ

and

'P3'

select

from

where

exists(

select

from

SPJ

where

SPJ

and

'P3'

)

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

select

from

left

outer

join

SPJ

'P3'

and

SPJ

NULL

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.

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

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

select

from

left

outer

join

SPJ

'P3'

and

SPJ

where

SPJ

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

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

from

except

select

from

SPJ

where

'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,

Podwójna agregacja (1)

select

MAX

(

Qty

)

from

(

select

SUM

(

Quantity

UnitPrice

)

Qty

from

[Order Details]

group

OrderID

)

select

Min

(

Qty

)

from

(

select

SUM

(

Quantity

UnitPrice

)

Qty

from

[Order Details]

group

OrderID

)

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

Podwójna agregacja (2)

select

MAX

(

Qty

)

from

(

select

SUM

(

Quantity

UnitPrice

)

Qty

from

[Order Details]

group

OrderID

)

• 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

Podwójna agregacja (3)

with

(

select

SUM

(

Quantity

UnitPrice

)

Qty

from

[Order Details]

group

OrderID

)

select

MAX

(

qty

)

from

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

create

view

select

SUM

(

Quantity

UnitPrice

)

Qty

from

[Order Details]

group

OrderID

Fajne tylko zostaje t czasem nie wiadomo po co.

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

table

(

qty

float

);

insert

select

SUM

(

Quantity

UnitPrice

)

Qty

from

[Order Details]

group

OrderID

select

MAX

(

qty

)

from

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

Ale o tym kiedy indziej

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

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

)

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

…

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

ProductID

[Order Details]

ProductID

group

CategoryID

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

ProductID

[Order Details]

ProductID

group

CategoryID

Nazwa zamiast CategoryID (3 sposoby):

select

sum

(

Quantity

Products

UnitPrice

MAX

(

CategoryName

)

from

[Order Details]

join

Products

ProductID

[Order Details]

ProductID

join

Categories

CategoryID

Products

CategoryID

group

Products

CategoryID

Zadania lab 3-4c,d (4)

select

sum

(

Quantity

Products

UnitPrice

CategoryName

from

[Order Details]

join

Products

ProductID

[Order Details]

ProductID

join

Categories

CategoryID

Products

CategoryID

group

Products

CategoryID,CategoryName

„Sztuczne” grupowanie,
zamiast „sztucznej”
agregacji

Zadania lab 3-4c,d (5)

select

sum

(

Quantity

Products

UnitPrice

MAX

(

CategoryName

)

from

[Order Details]

join

Products

ProductID

[Order Details]

ProductID

join

Categories

CategoryID

Products

CategoryID

group

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

ProductID

[Order Details]

ProductID

group

CategoryID

)

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

ProductID

[Order Details]

ProductID

join

Categories

CategoryID

Products

CategoryID

group

Products

CategoryID

having

sum

(

Quantity

Products

UnitPrice

(

select

avg

(

total

)

from

(

select

sum

(

Quantity

Products

UnitPrice

)

total

from

[Order Details]

join

Products

ProductID

[Order Details]

ProductID

group

CategoryID

)

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

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:

Łopatologicznie więc dostajemy:

select S# from SPJ
group by S#
having count(distinct P#)=(select count(*) from P)

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!

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

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

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:

)

(

)

(













select * from S
where forall(x,



(x))



select * from S
where not exists(x, not



(x))

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

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

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#

Proste aplikacje klienckie

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

Te same
parametry

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

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

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

Document Outline

Slide 1
Slide 2
Slide 3
Slide 4
Slide 5
Slide 6
Slide 7
Slide 8
Slide 9
Slide 10
Slide 11
Slide 12
Slide 13
Slide 14
Slide 15
Slide 16
Slide 17
Slide 18
Slide 19
Slide 20
Slide 21
Slide 22
Slide 23
Slide 24
Slide 25
Slide 26
Slide 27
Slide 28
Slide 29
Slide 30
Slide 31
Slide 32
Slide 33
Slide 34
Slide 35
Slide 36
Slide 37
Slide 38
Slide 39
Slide 40
Slide 41
Slide 42
Slide 43
Slide 44
Slide 45
Slide 46
Slide 47
Slide 48
Slide 49
Slide 50
Slide 51
Slide 52
Slide 53
Slide 54
Slide 55
Slide 56
Slide 57
Slide 58
Slide 59
Slide 60
Slide 61
Slide 62
Slide 63
Slide 64
Slide 65
Slide 66
Slide 67
Slide 68
Slide 69
Slide 70
Slide 71
Slide 72
Slide 73
Slide 74
Slide 75
Slide 76
Slide 77
Slide 78
Slide 79