Bazy danych  kolokwium SQL
1 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
Dzialy_Wypozyczalni
Dzial
Wypozyczalni a
Gatunek
IdDzialu Integer
IdWypozyczalni Integer
IdGatunku Integer
NazwaDzialu Characters (30)
NazwaWypozyczalni Characters (30)
NazwaGatunku Characters (30)
T elefon Characters (20)
Identifier_1
Identifier_1
KodPocztowy Characters (6)
Poczta Characters (30)
Ulica Characters (40)
NrLokalu Characters (10)
Gatunek_T ytul
Identifier_1
Nosnik
T utul
T ytuly_Dzialu
IdNosnika Integer
IdT ytulu Integer
NazwaNosnika Characters (20)
NazwaT ytulu Characters (30)
Identifi er_1
RokWydania Integer
Identifi er_1
0,n
Egzemplarz
Egzempl arze_T ytulu
Nosnik_Egzemplarz
Aktor
IdEgzemplarza Integer
IdAktora Integer
Identifier_1
Nazwisko Characters (30)
Imie Characters (30)
Narodowosc Characters (20)
0,1
Identifier_1
Wypozyczenie
Obsada
DataWypozyczenia Date
0,n
NazwaRol i Characters (30)
Egzemplarz_Hi storiaWyp
0,n
0,n
Klient
RodzajRoli
IdKlienta Integer
HistoriaWypozyczen
IdRodzajuRoli Integer
Nazwisko Characters (30)
DataWypozyczenia Date
NazwaRodzaj uRoli Characters (20)
Imie Characters (30)
DataZwrotu Date
T elefon Characters (20)
Klient_HistoriaWyp
Identifier_1
KodPocztowy Characters (6)
Poczta Characters (30)
Ulica Characters (40)
NrLokalu Characters (10)
Identifier_1
2 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
Dzial
IdWypozyczalni integer
Gatunek
Wypozyczalnia
IdDzialu integer
IdGatunku integer
IdWypozyczalni integer
NazwaDzialu char(30)
NazwaGatunku char(30)
NazwaWypozyczalni char(30)
Telefon char(20)
KodPocztowy char(6)
Gatunek_Tytul
Poczta char(30)
Ulica char(40)
IdGatunku integer
NrLokalu char(10)
IdTytulu integer
Tutul
Nosnik
IdTytulu integer
IdNosnika integer
IdWypozyczalni integer
NazwaNosnika char(20)
Egzemplarz
IdDzialu integer
IdNosnika integer NazwaTytulu char(30)
IdTytulu integer RokWydania integer
IdEgzemplarza integer
IdKlienta integer
DataWypozyczenia date
Aktor
IdAktora integer
Nazwisko char(30)
Imie char(30)
Narodowosc char(20)
Obsada
IdTytulu integer
IdAktora integer
IdRodzajuRoli integer
NazwaRoli char(30)
Klient
IdKlienta integer
Nazwisko char(30)
Imie char(30)
Telefon char(20)
RodzajRoli
HistoriaWypozyczen
KodPocztowy char(6)
IdRodzajuRoli integer
IdNosnika integer
Poczta char(30)
NazwaRodzajuRoli char(20)
IdTytulu integer
Ulica char(40)
IdEgzemplarza integer
NrLokalu char(10)
IdKlienta integer
DataWypozyczenia date
DataZwrotu date
3 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT W.IdWypozyczalni, SELECT T.NazwaTytulu Nazwa, SELECT K.Nazwisko, K.Imie,
W.NazwaWypozyczalni, Tab.L_Tytulow, H.DataWypozyczenia DataWyp, MAX(tab1.Liczba) + MIN(tab1.Liczba)
Tab.L_Egzemplarzy H.DataZwrotu DataZwr LiczbaWyp
FROM ( FROM HistoriaWypozyczen H, Egzemplarz FROM
select W.IdWypozyczalni, E, Tutul T (
count(DISTINCT E.idTytulu) L_Tytulow, WHERE H.IdKlienta =2 AND SELECT K.idKlienta,
count(E.idTytulu) L_Egzemplarzy H.IdEgzemplarza = E.IdEgzemplarza AND COUNT(E.idEgzemplarza) Liczba
FROM Egzemplarz E, Tutul T, T.IdTytulu = E.IdTytulu FROM Klient K LEFT JOIN Egzemplarz E
Wypozyczalnia W UNION ON
WHERE T.IdWypozyczalni = SELECT T.NazwaTytulu, K.idKlienta = E.IdKlienta
W.IdWypozyczalni AND T.IdTytulu = E.DataWypozyczenia, NULL GROUP BY K.idKlienta
E.IdTytulu FROM Egzemplarz E, Tutul T UNION
GROUP BY W.IdWypozyczalni WHERE E.IdKlienta = 2 AND E.IdTytulu SELECT K.idKlienta,
) Tab, Wypozyczalnia W = T.IdTytulu COUNT(H.idEgzemplarza) Liczba
WHERE Tab.idWypozyczalni = ORDER BY DataWyp DESC, DataZwr ASC FROM Klient K LEFT JOIN
W.IdWypozyczalni HistoriaWypozyczen H ON
ORDER BY Tab.L_Tytulow DESC, K.IdKlienta = H.IdKlienta
Tab.L_Egzemplarzy DESC GROUP BY K.IdKlienta
) tab1, Klient K
Lub WHERE K.IdKlienta = tab1.idKlienta
GROUP BY K.Nazwisko, K.Imie
select W.IdWypozyczalni, ORDER BY LiczbaWyp DESC
W.NazwaWypozyczalni,
count(E.IdTytulu) L_egzemplarzy,
count(DISTINCT E.IdTytulu
) L_tytulow
FROM Wypozyczalnia W, Egzemplarz E,
Tutul T
WHERE W.IdWypozyczalni =
T.IdWypozyczalni AND T.IdTytulu =
E.IdTytulu
GROUP BY W.IdWypozyczalni,
W.NazwaWypozyczalni
ORDER BY L_tytulow DESC,
L_egzemplarzy DESC
4 | S t r o n a
Bazy danych  kolokwium SQL
ZADANIA z SQL a. Model danych  Kino.
Sala
kino_sala
Kino
IdSali I
IdKina I
NazwaSali A30
NazwaKina A30
Klimatyzacja I
KodPocztowy A6
Miejscowosc A30
Sala_miejsce
Ulica A30
Sala_Seans
Nr A15
telefon A20
Seans Miejsce
IdSeansu I Rzad I
SeansData DT Miejsce I
Film_Seans
Status A1 Status A1
0,n
Film
0,n
0,n 0,n
IdFilmu I
NazwaFilmuPL A50
NazwaFilmuOrg A50
CzasTrwania T
HistoriaBilet
Bilet
Cena N15,2
0,n
Cena N15,2
Rezerwacja A1
Rezerwacja A1
Rola
NazwaRoli A30
Dane o biletach
(Bilet) po każdym
dniu są przenoszone
do HistoriiBiletu
0,n
0,n
0,n
0,n
Osoba
TypBiletu
IdOsoby I TypRoli
Nazwisko A30
IdTypuBiletu I
IdTypuRoli I
Imie A30
NazwaTypuBiletu A30
NazwaTypuRoli A30
DataUr D
1. Przekształcić model konceptualny danych do modelu fizycznej bazy danych.
5 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania SQL:
2. Lista pierwszych 10 kin (id_kina, NazwaKina) z liczbą sal i łączną liczbą miejsc kinowych, tylko tych kin, które posiadają ogólną liczbę miejsc
większą od 500; listę uporządkować wg liczby miejsc malejąco.
3. Lista filmów(id_filmu,NazwaFilmuPL) z liczbą sprzedanych biletów na dany film; listę uporządkować wg liczby sprzedanych biletów.
4. Wykaz sal (idSali,Nazwalali,NazwaKina) i średnie wypełnienie danej sali w poprzednim roku; uporządkować malejąco wg średniego
wypełnienia Sali.
5. Wykaz biletów sprzedanych na film o nazwie  Shrek w kinach w Warszawie.
6. Wykaz typów biletu (idTypuBiletu, NazwaTypuBiletu) z procentowym udziałem we wszystkich sprzedanych biletach.
6 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
kino
sala
id_kina Integer
NazwaKina Characters (20)
Id_sali Integer
Kod_pocztowy Characters (6)
NazwaSali Characters (30)
Miejscowość Characters (30)
Klimatyzacja Integer
Ulica Characters (30)
Identifier_1
kino_sala
nr Characters (16)
telefon Characters (20)
Identifier_1
Sala_seans Sala_miejsce
seans Miejsce
Film
Rząd Integer
Id_Filmu Integer id_seansu Integer
seans_data Date Miejsce Integer
NazwaFilmuPl Characters (50)
Film_Seans
Status Characters (1) Status Characters (1)
NazwaFimuOrg Characters (50)
Czas_Trwania Time
Identifier_1 Identifier_1
0,n 0,n
Identifier_1
0,n
0,n
0,n
HistoriaBilet
Cena Number (15,2)
Bilet
Rezerwacja Characters (1)
Cena Number (15,2)
Rezerwacja Characters (1)
Rola
NazwaRoli Characters (30)
0,n
0,n
TypBiletu
0,n
0,n IdTypuBiletu Integer
NazwaTypuBiletu Characters (30)
TypRoli
osoba
Identifier_1
idTypuRoli Integer
IdOsoby Integer NazwaTypuRoli Characters (30)
Nazwisko Characters (30)
Identifier_1
Imie Characters (30)
Data_ur Date
Identifier_1
7 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
kino
sala
id_kina integer
id_kina integer
NazwaKina char(20)
Id_sali integer
Kod_pocztowy char(6)
NazwaSali char(30)
Miejscowość char(30)
Klimatyzacja integer
Ulica char(30)
nr char(16)
telefon char(20)
seans
id_seansu integer
Miejsce
Id_Filmu integer
Film
id_kina integer
id_kina integer
Id_sali integer
Id_Filmu integer
Id_sali integer
Rząd integer
NazwaFilmuPl char(50)
seans_data date
Miejsce integer
NazwaFimuOrg char(50)
Status char(1)
Status char(1)
Czas_Trwania time
Bilet
HistoriaBilet
id_seansu integer
id_seansu integer
id_kina integer
id_kina integer
Id_sali integer
Id_sali integer
Rola
Rząd integer
Rząd integer
Miejsce integer
Id_Filmu integer
Miejsce integer
IdTypuBiletu integer
idTypuRoli integer
IdTypuBiletu integer
Cena numeric(15,2)
IdOsoby integer
Cena numeric(15,2)
Rezerwacja char(1)
NazwaRoli char(30)
Rezerwacja char(1)
TypBiletu
IdTypuBiletu integer
NazwaTypuBiletu char(30)
TypRoli
osoba
idTypuRoli integer
NazwaTypuRoli char(30)
IdOsoby integer
Nazwisko char(30)
Imie char(30)
Data_ur date
8 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT TOP 10 K.id_kina, K.NazwaKina, SELECT Fi.Id_Filmu, Fi.NazwaFilmuPl,
COUNT(DISTINCT S.Id_sali) LiczbaSal, SUM(tab1.LiczbaBiletow1 +
SELECT k.id_kina,k.nazwakina,
( tab2.Liczbabiletow2)
s.id_sali,
SELECT COUNT(*) LiczbaSprzedanych
(count(*)*10/count(distinct
FROM kino Ki, Miejsce M FROM
hb.id_seansu))*0.1 as srednia
WHERE Ki.id_kina = M.id_kina AND (
FROM kino k, sala s, historiabilet
Ki.id_kina = K.id_kina SELECT F.Id_Filmu, F.NazwaFilmuPl,
hb, seans se
) LiczbaMiejsc COUNT(B.id_seansu) LiczbaBiletow1
WHERE k.id_kina = s.id_kina and
FROM kino K, sala S FROM Film F, seans S, Bilet B
hb.id_kina = s.id_kina and
WHERE K.id_kina = S.id_kina WHERE F.Id_Filmu = S.Id_Filmu AND
hb.id_sali = s.id_sali and
GROUP BY K.id_kina, K.NazwaKina S.id_seansu = B.id_seansu
se.id_seansu = hb.id_seansu and
HAVING LiczbaMiejsc>0 GROUP BY F.Id_Filmu, F.NazwaFilmuPl)
datediff(year, se.seans_data,
ORDER BY LiczbaMiejsc DESC tab1,
today()) = 1
(
GROUP BY k.id_kina, s.id_sali,
SELECT F.Id_Filmu, F.NazwaFilmuPl,
k.nazwakina
COUNT(H.id_seansu) Liczbabiletow2
ORDER BY srednia desc
FROM Film F, seans S, HistoriaBilet H
WHERE F.Id_Filmu = S.Id_Filmu AND
S.id_seansu = H.id_seansu
GROUP BY F.Id_Filmu, F.NazwaFilmuPl
) tab2, Film Fi
WHERE tab1.Id_Filmu = tab2.Id_Filmu
UWAGA:
AND tab1.Id_Filmu = Fi.Id_Filmu AND
datediff(year, dataWczesniejsza,
tab2.Id_Filmu = Fi.Id_Filmu
DataPozniejsza) zwraca nam różnicę
GROUP BY Fi.Id_Filmu, Fi.NazwaFilmuPl
zgodnie z podanym parametrem, w
ORDER BY LiczbaSprzedanych DESC
naszym przypadku jest to year.
9 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT B.id_seansu, B.id_kina,
B.Id_sali, B.Rzą_d, B.Miejsce, SELECT tab1.IdTypuBiletu,
B.IdTypuBiletu, B.Rezerwacja tab1.NazwaTypuBiletu,
FROM Bilet B, Film F, seans S, kino (SUM(liczba)*10/(
K, sala SA (SELECT COUNT(bilet.idtypubiletu)
WHERE TRIM(F.NazwaFilmuPl) = 'Shrek' FROM Bilet) +
AND F.Id_Filmu = S.Id_Filmu AND (SELECT
TRIM(K.Miejscowoś_ć_) = 'Warszawa' COUNT(HistoriaBilet.IdTypuBiletu)
AND B.id_seansu = S.id_seansu FROM HistoriaBilet)))*0.01 Procentowo
AND B.id_kina = K.id_kina AND FROM
SA.Id_sali = B.Id_sali (
UNION SELECT T.IdTypuBiletu,
SELECT HB.id_seansu, HB.id_kina, T.NazwaTypuBiletu,
HB.Id_sali, HB.Rzą_d, HB.Miejsce, COUNT(B.IdTypuBiletu) liczba
HB.IdTypuBiletu, HB.Rezerwacja FROM Bilet B, TypBiletu T
FROM HistoriaBilet HB, Film F, seans WHERE B.IdTypuBiletu = T.IdTypuBiletu
S, kino K, sala SA GROUP BY T.IdTypuBiletu,
WHERE TRIM(F.NazwaFilmuPl) = 'Shrek' T.NazwaTypuBiletu
AND F.Id_Filmu = S.Id_Filmu AND UNION
TRIM(K.Miejscowoś_ć_) = 'Warszawa' SELECT T.IdTypuBiletu,
AND HB.id_seansu = S.id_seansu T.NazwaTypuBiletu,
AND HB.id_kina = K.id_kina AND COUNT(HB.IdTypuBiletu) liczba
SA.Id_sali = HB.Id_sali FROM TypBiletu T, HistoriaBilet HB
WHERE HB.IdTypuBiletu =
T.IdTypuBiletu
GROUP BY T.IdTypuBiletu,
T.NazwaTypuBiletu) tab1
GROUP BY tab1.IdTypuBiletu,
tab1.NazwaTypuBiletu
10 | S t r o n a
Bazy danych  kolokwium SQL
11 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
Usługi
Bank
id_uslugi Integer
typ Characters (1) jest
nazwa_uslugi Variable characters (50)
kapital Money (50,2)
Identifier_1
Kantor
Typ_usługi
id_typu_uslugi Integer
nazwa_typu Variable characters (50)
posiada
Placowka_Finansowa
czy_kapitalowa Boolean
id Integer
Inheritance_2
Identifier_1
nazwa Variable characters (40)
adres Variable characters (40)
posiada_kurs
miasto Variable characters (40)
realizuje
Identifier_1
Kurs_waluty
Waluta_kurs_sredni_NBP
oferuj
kurs_kupna Money (50,2)
wartosci_notowania Money (50,2)
kurs_sprzedazy Money (50,2)
ilosc Integer
ilosc Integer
Operacje_finansowe
id_operacji Integer
nazwa_operacji Variable characters (50)
Inheritance_1 opis Variable characters (200)
Identifier_1
Waluta
notowania_walut
Notowania_waluty
id_waluty Integer
data_notowania_walut Date
kraj_waluty Variable characters (3)
Identifier_1
Identifier_1
12 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
Bank
id integer
Usługi
typ char(1)
id_uslugi integer
kapital numeric(50,2)
nazwa_uslugi varchar(50)
nazwa varchar(40)
Kantor
adres varchar(40)
id integer miasto varchar(40)
Typ_usługi
nazwa varchar(40)
id_typu_uslugi integer
adres varchar(40)
id_uslugi integer
miasto varchar(40)
posiada nazwa_typu varchar(50)
posiada
czy_kapitalowa smallint
id integer
id integer
id_uslugi integer
id_uslugi integer
Kurs_waluty
oferuj
id_waluty integer
Waluta_kurs_sredni_NBP
id integer data_notowania_walut date
id_waluty integer
id_waluty integer id integer
data_notowania_walut date
Pla_id integer Operacje_finansowe
wartosci_notowania numeric(50,2)
kurs_kupna numeric(50,2)
id_operacji integer
ilosc integer
kurs_sprzedazy numeric(50,2)
id integer
ilosc integer
Pla_id integer
nazwa_operacji varchar(50)
opis varchar(200)
Waluta
id_waluty integer
kraj_waluty varchar(3)
13 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT TOP 2 K.id, COUNT(O.id_waluty) SELECT TOP 10 B.id, COUNT(P.id) SELECT K.id Kantor,
LiczbaWalut Liczba_kap YEAR(K.data_notowania_walut) Rok,
FROM Kantor K, oferuj O FROM Bank B, posiada2 P, Typ_usłY
ugi MONTH(K.data_notowania_walut)
WHERE K.id = O.id T miesiac, AVG(K.kurs_kupna)
GROUP BY K.id WHERE B.id = P.id AND P.id_uslugi = Sredni_kurs_kup,
ORDER BY LiczbaWalut DESC T.id_uslugi AVG(K.kurs_sprzedazy)
AND T.czy_kapitalowa = 1 Sredni_kurs_sprz, MAX(K.kurs_kupna)
GROUP BY B.id max_kup, MIN(K.kurs_sprzedazy)
ORDER BY Liczba_kap DESC min_sprz, tab1.sprzedaz sprzedaz1
FROM Kurs_waluty K,
SELECT B.id BANK,
(SELECT TOP 2 K.id Kantor,
YEAR(K.data_notowania_walut) rok,
YEAR(K.data_notowania_walut) Rok,
MONTH(K.data_notowania_walut)
MONTH(K.data_notowania_walut)
miesiac, K.id_waluty,
miesiac, K.kurs_sprzedazy sprzedaz,
-((AVG(K.kurs_kupna) -
COUNT(K.kurs_sprzedazy) czestosc
AVG(K.kurs_sprzedazy))) spread, -
FROM Kurs_waluty K
((MIN(K.kurs_kupna) -
WHERE K.id = 1
MAX(K.kurs_sprzedazy))) max_roznica
GROUP BY Kantor, Rok, miesiac,
FROM Kurs_waluty K, Bank B
K.kurs_sprzedazy
WHERE B.id = K.id
ORDER BY czestosc DESC) tab1
GROUP BY B.id, rok, miesiac,
WHERE K.id = 1 AND tab1.Kantor =1 AND
k.id_waluty
tab1.Rok = Rok AND tab1.miesiac =
miesiac
GROUP BY K.id, Rok, miesiac,
sprzedaz1
(odrobinę naciągane)
14 | S t r o n a
Bazy danych  kolokwium SQL
Wypozyczajacy
Osoba
Limit Integer
IdOsoby Integer
Nazwisko Characters (30)
Autor
Imie Characters (30)
DataUr Date
0,n
PI_Osoba
Tytul
Wypozyczenie
IdTytulu Integer
DataWypozyczenia Date
Tytul Characters (50)
DataZwrotu Date
DataWydania Date
...
PI_Tytul
Gatunek
IdGatunku Integer
NazwaGatunku Characters (50)
Identifier_1
0,n
Wydawnictwo
PozycjaBiblioteczna
IdWydawnictwa Integer
IdPozycjiBibliotecznej Integer
NazwaWydawnictwa Characters (50)
PI_PozycjaBiblioteczna
PI_Wydawnictwo
Model danych  Biblioteka.
1. Pokaż gatunki (IdGatunku, NazwaGatunku) oraz liczbę tytułów danego gatunku.
2. Pokaż nazwiska, imiona oraz liczbę wypożyczonych pozycji przez wypożyczającego w roku 2003, którzy wypożyczyli powyżej 20 pozycji (uporządko`wad malejąco wg liczby
wypożyczeo).
3. Pokaż średnią liczbę wypożyczeń na wypożyczającego w 2002 roku.
4. Pokaż nazwiska, imiona wypożyczających oraz liczbę nie zwróconych przez nich pozycji, przetrzymywanych powyżej 6 miesięcy (uporządkowad malejąco wg liczby nie zwróconych
pozycji).
5. Pokaż autorów (nazwisko, imię) oraz liczbę tytułów, których są autorami (uporządkowad malejąco wg liczby tytułów, których są autorami).
15 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
Osoba
IdOsoby Integer
Nazwisko Characters (30)
Imie Characters (30)
DataUr Date
Inheritance_2
PI_IdOsoby
Wypozyczajacy Autor
Limit Integer
Relationship_4
0,n
Wypozyczenie
DataWyp Date
Tytul
DataZwr Date
IdTytulu Integer
Tytul Characters (30)
Relationship_3
DataWydania Date
PI_IdTytulu
0,n
Relationship_2
Relationship_1
PozycjaBiblioteczna
IdPB Integer
Gatunek
Wydawnictwo
PI_IdPB
IdGatunku Integer
IdWydawnictwa Integer
NazwaGatunku Characters (30)
NazwaWydawnictwa Characters (30)
PI_IdGatunku
PI_IdWydawnictwa
16 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
Wypozyczajacy
IdOsoby integer
Limit integer
Relationship_4
Nazwisko char(30)
FK_WYPOZYCZ_WYPOZYCZE_WYPOZYCZ IdTytulu integer
Imie char(30)
IdOsoby integer
DataUr date
Autor
Wypozyczenie
IdOsoby integer
IdOsoby integer
Nazwisko char(30)
IdTytulu integer
Tytul
Imie char(30)
IdPB integer
FK_RELATION_RELATIONS_AUTOR
DataUr date
IdTytulu integer
DataWyp date
IdGatunku integer
FK_RELATION_RELATIONS_TYTUL
DataZwr date
IdWydawnictwa integer
Tytul char(30)
DataWydania date
FK_TYTUL_RELATIONS_GATUNEK
FK_WYPOZYCZ_WYPOZYCZE_POZYCJAB
FK_TYTUL_RELATIONS_WYDAWNIC
PozycjaBiblioteczna
FK_POZYCJAB_RELATIONS_TYTUL
IdTytulu integer
Gatunek
Wydawnictwo
IdPB integer
IdGatunku integer
IdWydawnictwa integer
NazwaGatunku char(30)
NazwaWydawnictwa char(30)
17 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT G.IdGatunku, SELECT W.IdOsoby, MAX(W.Nazwisko) SELECT tab1.Nazwisko, tab1.Imie,
MAX(G.NazwaGatunku) NazwaGatunku, Nazwisko, MIN(W.Imie) Imie, tab1.LiczbaTytulow
COUNT(T.IdTytulu) LiczbaTytulow (COUNT(D.IdPB)*10000/365)*0.0001 FROM(SELECT A.IdOsoby,
FROM Gatunek G LEFT JOIN Tytul T sredniaLW MAX(A.Nazwisko) Nazwisko, MIN(A.Imie)
ON(G.IdGatunku = T.IdGatunku) FROM Wypozyczajacy W LEFT JOIN Imie, COUNT(R.IdTytulu) LiczbaTytulow
GROUP BY G.IdGatunku Wypozyczenie D ON (W.IdOsoby = FROM Autor A LEFT JOIN Relationship_4
D.IdOsoby) R ON(A.IdOsoby= R.IdOsoby)
WHERE YEAR(D.DataWyp) = 2003 GROUP BY A.IdOsoby
GROUP BY W.IdOsoby ORDER BY LiczbaTytulow DESC) tab1
SELECT W.IdOsoby, MAX(W.Nazwisko) SELECT W.IdOsoby, MAX(W.Nazwisko)
Nazwisko, MAX(W.Imie) Imie, Nazwisko, MAX(W.Imie) Imie,
COUNT(D.IdPB) LiczbaWypozyczen COUNT(D.IdPB) NieZwrocone
FROM Wypozyczajacy W INNER JOIN FROM Wypozyczajacy W INNER JOIN
Wypozyczenie D ON(W.IdOsoby = Wypozyczenie D ON(W.IdOsoby =
D.IdOsoby) INNER JOIN Tytul T D.IdOsoby)
ON(D.IdTytulu = T.IdTytulu) WHERE DATE(D.DataWyp)<=DATE(NOW(*)-
WHERE YEAR(D.DataWyp) = 2003 183) AND D.DataZwr IS NULL
GROUP BY W.IdOsoby GROUP BY W.IdOsoby
HAVING LiczbaWypozyczen>=1 ORDER BY NieZwrocone DESC
ORDER BY LiczbaWypozyczen DESC
18 | S t r o n a
Bazy danych  kolokwium SQL
19 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
Marka
TypPojazdu
Adres
IdMarki Integer IdTP Integer
NazwaMarki Characters (30) KodPocztowy Characters (6)
NazwaTP Characters (30)
Poczta Characters (50)
Identifier_1
Identifier_1
Miejsc_Adres
Ulica Characters (50)
NrDomu Characters (15)
Model_Marki
HistUmowaU
DataRozp Date
Model
DataZakon Date
KwotaUbezp Money (15,2)
IdModelu Integer
Typ_Modelu
KwotaSkladkiU Money (15,2)
NazwaModelu Characters (30)
Miejscowosc
Identifier_1
KodM Characters (3)
Miejscowosc Characters (50)
0,n
Inheritance_1
Identifier_1
Klient
Model_Pojazdu
CID Integer
P_Miejsc
Nazwisko Characters (30)
Imie Characters (30)
DataUr Date
Pojazd
MiejsceUr Text
Powiat
VIN Characters (17)
Identifier_1
KodP Characters (2)
Typ Characters (30) 0,n
Powiat Characters (50)
RokProdukcji Integer
0,n Identifier_1
Cena Money (15,2)
Status Characters (10)
Identifier_1
UmowaU
Woj_Pow
DataRozp Date
0,n
DataZakon Date
KwotaUbezp Money (15,2)
Szkody KwotaSkladkiU Money (15,2)
Wojewodztwo
IdS Integer
0,n
DataSzkody Date KodW Characters (2)
Attribute_14 Text 0,n Wojewodztwo Characters (50)
Attribute_15 Money (15,2)
Identifier_1
Identifier_1
0,n 0,n
TowarzystwoU
IdTU Integer
NazwaTU Characters (50)
Identifier_1
20 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
T ypPojazdu
Hi stUmowaU
IdT P integer
Marka
CID integer
NazwaT P char(30)
VIN char(17)
IdMarki integer
IdS integer
NazwaMarki char(30)
IdT U integer
DataRozp date
DataZakon date
KwotaUbezp numeric(15,2)
KwotaSkladkiU numeric(15,2)
Model
IdMarki i nteger
IdModel u i nteger Miejscowosc
IdT P i nteger
KodW char(2)
NazwaModel u char(30)
KodP char(2)
Klient
KodM char(3)
CID integer
Miejscowosc char(50)
KodW char(2)
KodP char(2)
KodM char(3)
KodPocztowy char(6)
Poczta char(50)
Ulica char(50)
Poj azd
Powiat
NrDomu char(15)
VIN char(17)
KodW char(2)
Nazwi sko char(30)
IdMarki integer
KodP char(2)
Imi e char(30)
IdModelu integer
Powiat char(50)
DataUr date
T yp char(30)
Mi ejsceUr long varchar
RokProdukcji integer
Cena numeric(15,2)
Status char(10)
UmowaU
IdT U i nteger
IdS i nteger
Wojewodztwo
VIN char(17)
CID i nteger KodW char(2)
Szkody
DataRozp date Wojewodztwo char(50)
IdS integer DataZakon date
DataSzkody date KwotaUbezp numeric(15,2)
Attribute_14 long varchar KwotaSkl adkiU numeric(15,2)
Attribute_15 numeric(15,2)
T owarzystwoU
IdT U integer
KodW char(2)
KodP char(2)
KodM char(3)
KodPocztowy char(6)
Poczta char(50)
Ul ica char(50)
NrDomu char(15)
NazwaT U char(50)
21 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT tab1.idMarki Marka, SELECT M.IdModelu, M.NazwaModelu, SELECT T.IdTU, T.NazwaTU, COUNT(DISTINCT
tab1.NazwaMarki NazwaMarki, tab1.IdModelu tab2.Umowy LiczbaUmow U.CID) LiczbaKlientow, COUNT(DISTINCT
Model, tab1.NazwaModelu NazwaModelu, FROM (SELECT TOP 5 tab1.VIN, U.VIN) LiczbaPojazdow,
SUM(tab1.LUmow) LiczbaUmow, SUM(tab1.Lszkod) LSzkod, SUM(tab1.Umowy) SUM(U.KwotaSkladkiU) LacznaKwSkladek
SUM(tab1.LacznaKwUb) LacznaKwotaUbezp, Umowy FROM TowarzystwoU T LEFT JOIN UmowaU U ON
SUM(tab1.LacznaKwSkl) LacznaKwotaSkladki FROM (SELECT P.VIN VIN, COUNT(U.IdS) (T.IdTU = U.IdTU)
FROM (SELECT M.IdMarki,M.NazwaMarki, LSzkod, COUNT(U.VIN) Umowy GROUP BY T.IdTU, T.NazwaTU
Mo.IdModelu, Mo.NazwaModelu, COUNT(U.VIN) FROM Pojazd P, UmowaU U, Szkody S ORDER BY LiczbaKlientow DESC,
LUmow, SUM(U.KwotaUbezp) LacznaKwUb, WHERE P.VIN = U.VIN AND S.IdS = U.IdS AND LacznaKwSkladek ASC
SUM(U.KwotaSkladkiU) LacznaKwSkl YEAR(now(*))-3<=YEAR(S.DataSzkody)
FROM Marka M LEFT JOIN Model Mo ON GROUP BY P.VIN
(M.IdMarki = Mo.IdMarki) LEFT JOIN Pojazd UNION ALL
P ON (Mo.IdModelu = P.IdModelu) SELECT P.VIN, COUNT(H.IdS), COUNT(H.VIN)
LEFT JOIN UmowaU U ON (P.VIN = U.VIN) Umowy
where YEAR(Now(*))-3 < YEAR(U.DataRozp) FROM Pojazd P, HistUmowaU H, Szkody S
GROUP BY M.IdMarki, M.NazwaMarki, WHERE P.VIN = H.VIN AND S.IdS = H.IdS AND
Mo.IdModelu, Mo.NazwaModelu YEAR(now(*))-3<=YEAR(S.DataSzkody)
UNION ALL GROUP BY P.VIN) tab1
SELECT M.IdMarki,M.NazwaMarki, GROUP BY tab1.VIN
Mo.IdModelu, Mo.NazwaModelu, COUNT(H.VIN) ORDER BY Lszkod DESC) tab2, Model M,
LUmow, SUM(H.KwotaUbezp) LacznaKwUb, Pojazd P
SUM(H.KwotaSkladkiU) LacznaKwSkl WHERE M.IdModelu = P.IdModelu AND P.VIN =
FROM Marka M LEFT JOIN Model Mo ON tab2.VIN
(M.IdMarki = Mo.IdMarki) LEFT JOIN Pojazd
P ON (Mo.IdModelu = P.IdModelu)
SELECT M.IdModelu, M.NazwaModelu, tab2.Umowy
LEFT JOIN HistUmowaU H ON (P.VIN = H.VIN)
LiczbaUmow
where YEAR(Now(*))-3 < YEAR(H.DataRozp)
FROM (SELECT TOP 5 tab1.VIN, SUM(tab1.Lszkod)
GROUP BY M.IdMarki, M.NazwaMarki,
LSzkod, SUM(tab1.Umowy) Umowy
Mo.IdModelu, Mo.NazwaModelu) tab1 FROM (SELECT P.VIN VIN, COUNT(U.IdS) LSzkod,
COUNT(U.VIN) Umowy
GROUP BY Marka, NazwaMarki, Model,
FROM Pojazd P LEFT JOIN UmowaU U ON (P.VIN =
NazwaModelu
U.VIN) LEFT JOIN Szkody S ON (S.IdS = U.IdS)
HAVING LiczbaUmow>=2
WHERE YEAR(now(*))-3<=YEAR(S.DataSzkody)
GROUP BY P.VIN
UNION ALL
SELECT P.VIN, COUNT(H.IdS), COUNT(H.VIN) Umowy
FROM Pojazd P LEFT JOIN HistUmowaU H ON P.VIN
= H.VIN LEFT JOIN Szkody S ON (S.IdS = H.IdS)
WHERE YEAR(now(*))-3<=YEAR(S.DataSzkody)
GROUP BY P.VIN) tab1
GROUP BY tab1.VIN
ORDER BY Lszkod DESC) tab2, Model M, Pojazd P
WHERE M.IdModelu = P.IdModelu AND P.VIN =
tab2.VIN
22 | S t r o n a
Bazy danych  kolokwium SQL
23 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
MarkaKoncernu
Koncern Adres
Osoba
IdK Integer KodPocztowy Characters (6)
Pesel Characters (11)
NazwaK Characters (30) Poczta Characters (30)
Nazwi sko Characters (30)
Mi ej scowosc Characters (30)
PI_IdK
Imie Characters (30)
Uli ca Characters (30)
DataUr Date
NrLokal u Characters (10)
PI_pesel
Wojewodztwo Characters (30)
T ypmodel u
Marka
T ypPoj azdu
IdMarki Integer
IdT P Integer
NazwaMarki Characters (30)
NazwaT P Characters (30)
PI_IdMarki
PI_IdT P
Dzi edziczenie
SiecKomi sow
Dziedzi czenie2
ModelMarki
IdSK Integer
NazwaSK Characters (30)
Model
Kli ent
PI_IdSK
IdModelu Integer
Pracowni k
NazwaModelu Characters (30)
0,n
si ecKomi sow
Stanowi sko Characters (30)
0,n
PI_IDModel u
Komis
Model Poj azdu
zatrudni enei
IdKomisu Integer
NazwaKomisu Characters (30)
0,n 0,n
Pojazd
PI_IdKomisu
VIN Characters (7)
T yp Characters (10)
RokProdukcji Integer
Cena Money (15,2)
Status Characters (1)
PI_VAN
HOperacja
Operacja
0,n
0,n 0,n
DataOperacji Date
Wyposazeni e
DataOperacj i Date
Cena Money (15,2)
Cena Money (15,2)
Liczba Money (15,2)
Info Characters (200)
0,n
0,n 0,n
T ypWyposazenia
IdT W Integer
T ypOperacji
NazwaT W Characters (30)
IdT O Integer
PI_IdT W
NazwaT O Characters (30)
PI_IdT O
24 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
Koncern
IdK integer
NazwaK char(30)
FK_MARKA_MARKAKONC_KONCERN
Marka
TypPojazdu
IdMarki integer
IdTP integer
IdK integer
NazwaTP char(30)
NazwaMarki char(30)
Klient
SiecKomisow
FK_MODEL_MODELMARK_MARKA
Pesel char(11)
IdSK integer
FK_MODEL_TYPMODELU_TYPPOJAZ
KodPocztowy char(6)
Model
NazwaSK char(30)
Pracownik Poczta char(30)
IdMarki integer
Miejscowosc char(30)
Pesel char(11)
FK_KOMIS_SIECKOMIS_SIECKOMI
IdModelu integer
Ulica char(30)
Komis
IdKomisu integer
FK_OPERACJA_OPERACJA8_KLIENT
IdTP integer
NrLokalu char(10)
Nazwisko char(30)
IdKomisu integer
FK_OPERACJA_OPERACJA7_KLIENT
NazwaModelu char(30)
Wojewodztwo char(30)
Imie char(30)
IdSK integer
Nazwisko char(30)
FK_PRACOWNI_ZATRUDNIE_KOMIS
DataUr date
KodPocztowy char(6)
FK_POJAZD_MODELPOJA_MODEL
Imie char(30)
Stanowisko char(30)
Poczta char(30)
DataUr date
Miejscowosc char(30)
Pojazd
Ulica char(30)
IdMarki integer
NrLokalu char(10)
IdModelu integer
Wojewodztwo char(30)
FK_OPERACJA_OPERACJA5_PRACOWNI
VIN char(7)
NazwaKomisu char(30)
Typ char(10)
RokProdukcji integer
HOperacja
Operacja
Cena numeric(15,2)
FK_OPERACJA_OPERACJA2_POJAZD
IdMarki integer
Status char(1) IdMarki integer
FK_WYPOSAZE_WYPOSAZEN_POJAZD
IdModelu integer
Wyposazenie
IdModelu integer
VIN char(7)
FK_OPERACJA_OPERACJA6_PRACOWNI
VIN char(7)
IdMarki integer
IdTO integer
IdTO integer
IdModelu integer
FK_OPERACJA_OPERACJA4_TYPOPERA
Pesel char(11)
Pesel char(11)
VIN char(7)
Kli_Pesel char(11)
Kli_Pesel char(11)
IdTW FK_OPERACJA_OPERACJA_POJAZD
integer
DataOperacji date
DataOperacji date
Liczba numeric(15,2)
Cena numeric(15,2)
Cena numeric(15,2)
Info char(200)
FK_WYPOSAZE_WYPOSAZEN_TYPWYPOS
FK_OPERACJA_OPERACJA3_TYPOPERA
TypWyposazenia
IdTW integer
TypOperacji
NazwaTW char(30)
IdTO integer
NazwaTO char(30)
25 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
26 | S t r o n a
Bazy danych  kolokwium SQL
27 | S t r o n a
Bazy danych  kolokwium SQL
28 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
dostepneUsl ugi VoIP
przypi saneNumeryVoIP
Rel ati onshi p_4i dUsl ugi VoIP Integer
numerVoIP Integer
nazwaUsl ugi VoIP Characters (30)
Identi fi er_1
Identi fi er_1
dostepneDaneT el eadresowe
usl ugi Internet
i dT el eadresowe Integer
ul i ca Characters (30)
dataWpi su Date & T i me
usl ugi VoIP
nrBudynku Characters (30)
nrMi eszkani a Characters (8)
daneKorespondencyj ne 0,n
kodPocztowy Characters (6)
mi asto Characters (30)
dostepneProfi l ePredkosci Internetu
Identi fi er_1
i dProfi l uInternet
0,n
nazwaProfi l u Characters (50)
downl oad Integer
pracowni k Kl i ent
upl oad Integer
opi ekun
kpx Characters (10) 0,n
Identi fi er_1
Identi fi er_1
l i ni aAbonencka
0,n
i dLi ni i Integer
0,n
Identi fi er_1
usl ugi PST N
0,n
0,n
Inheri tance_1 dataWpi su Date & T i me
l i ni aAbonenckaDSLAM
Osoba
dostepneDSLAM
id Integer 0,n
i mi e Vari abl e characters (40) i dDSLAM Integer
nazwi sko Vari abl e characters (40) nazwa Characters (30)
przypi saneNumeryPST N
l i czbaPortow Short i nteger
Identi fi er_1
numerPST N Integer
l okal i zacj a Vari abl e characters (30)
Identi fi er_1
Identi fi er_1
i dentyfi katoryUrzadzeni a
posi adaneUrzadzeni a
seri al IT P Vari abl e characters (10) Rel ati onshi p_6
MAC Vari abl e characters (10)
Identi fi er_1
dostepneUsl ugi PST N
i dUsl ugi PST N Integer
Rel ati onshi p_2 nazwaUsl ugi PST N Vari abl e characters (50)
Identi fi er_1
dostepneUrzadzeni a
usl ugi T V
i dDostepneUrzadzeni a Integer
dataWpi su Date & T i me
nazwaUrzadzeni a Vari abl e characters (30)
Identi fi er_1
0,n
dostepneUsl ugi T V
i dUsl ugi T V Integer
nazwaUsl ugi T V Vari abl e characters (50)
Identi fi er_1
29 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
dostepneUslugiVoIP
idUslugiVoIP integer
FK_PRZYPISA_RELATIONS_DOSTEPNE
nazwaUslugiVoIP char(30)
FK_USLUGIVO_USLUGIVOI_PRZYPISA
przypisaneNumeryVoIP uslugiInternet
numerVoIP integer idProfiluInternet
FK_USLUGIIN_USLUGIINT_KLIENT
FK_USLUGIIN_USLUGIINT_LINIAABO
idUslugiVoIP integer idLinii integer
id integer
dataWpisu timestamp
dostepneDaneTeleadresowe
idTeleadresowe integer
FK_USLUGIIN_USLUGIINT_DOSTEPNE
FK_USLUGIVO_USLUGIVOI_KLIENT
ulica char(30)
nrBudynku char(30)
dostepneProfilePredkosciInternetu
nrMieszkania char(8)
uslugiVoIP
idProfiluInternet
kodPocztowy char(6)
id integer
nazwaProfilu char(50)
miasto char(30)
numerVoIP integer
download integer
upload integer
liniaAbonencka
FK_USLUGIPS_USLUGIPST_LINIAABO idLinii integer
idDSLAM integer
FK_DANEKORE_DANEKORES_KLIENT uslugiPSTN
Klient
pracownik
FK_KLIENT_OPIEKUN_PRACOWNI
FK_DANEKORE_DANEKORES_DOSTEPNE
id integer
id integer
id integer
idLini i
pra_id integer
FK_USLUGIPS_USLUGIPSTi_KLIENTnteger
kpx char(10)
numerPSTN integer FK_LINIAABO_LINIAABON_DOSTEPNE
imie varchar(40)
imie varchar(40)
dataWpisu timestamp
nazwisko varchar(40)
dostepneDSLAM
nazwisko varchar(40)
idDSLAM integer
FK_USLUGIPS_USLUGIPST_PRZYPISA
nazwa char(30)
daneKorespondencyjne
liczbaPortow smallint
idTeleadresowe integer lokalizacja varchar(30)
FK_POSIADAN_POSIADANE_KLIENTprzypisaneNumeryPSTN
id integer
numerPSTN integer
idUslugiPSTN integer
dostepneUslugiPSTN
FK_USLUGITV_USLUGITV_KLIENT
FK_PRZYPISA_RELATIONS_DOSTEPNE
idUslugiPSTN integer
identyfikatoryUrzadzenia
posiadaneUrzadzenia nazwaUslugiPSTN varchar(50)
FK_POSI
serialITP varchar(10)ADAN_POSIADANE_IDENTYFI

serialITP varchar(10)
uslugiTV
idDostepneUrzadzenia integer
id integer
MAC varchar(10)
id integer
idLinii integer FK_USLUGITV_USLUGITV2_LINIAABO
FK_IDENTYFI_RELATIONS_DOSTEPNE
idUslugiTV integer
dostepneUrzadzenia dostepneUslugiTV
dataWpisu timestamp
idDostepneUrzadzenia integer idUslugiTV integer
nazwaUrzadzenia varchar(30) nazwaUslugiTV varchar(50)
FK_USLUGITV_USLUGITV3_DOSTEPNE
30 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT TOP 10 P.id Pracownik, K.id SELECT TOP 10 L.idLinii
Zadanie nie jest dokładnie sprecyzowane, co
Klient, MAX(K.nazwisko) nazwisko, LiniaAbonencka
MAX(K.imie) imie, FROM dostepneDaneTeleadresowe DDT traktujemy jako usługę?(mamy przecież 4 usługi,
COUNT(DISTINCT U.numerPSTN) INNER JOIN daneKorespondencyjne DK
po co wypisywad ilośd usług klientów skoro
LiczbaNumerow ON(DDT.miasto = 'Warszawa' AND
warunek >3 usługi podane, wtedy po prostu
FROM pracownik P INNER JOIN Klient K DDT.idTeleadresowe =
ON(P.id = K.pra_id) INNER JOIN DK.idTeleadresowe) selekt 4 wszędzie). Czy może rodzaj usługi dla
uslugiPSTN U INNER JOIN Klient K ON(DK.id = K.id)
klienta jest już innego usługą, ale czemu bez usług
ON (K.id = U.id) INNER JOIN uslugiTV UTV ON(K.id =
VOLP wtedy? Można to różnie interpretowad, jest
GROUP BY P.id, K.id UTV.id) INNER JOIN dostepneUslugiTV
ORDER BY LiczbaNumerow DESC DUTV kilka prawidłowych rozwiązao według mnie.
ON(UTV.idUslugiTV = DUTV.idUslugiTV)
INNER JOIN uslugiInternet UI ON (K.id
= UI.id) INNER JOIN
dostepneProfilePredkosciInternetu
DPPI
ON(UI.idProfiluInternet =
DPPI.idProfiluInternet) INNER JOIN
liniaAbonencka L ON(UI.idLinii =
L.idLinii)
WHERE DPPI.nazwaProfilu = 'neostrada'
AND DPPI.download BETWEEN 6000 AND
20000 AND DUTV.nazwaUslugiTV =
'20kanalow' AND
MONTH(NOW(*))-3 <=
MONTH(UTV.dataWpisu)
GROUP BY LiniaAbonencka
ORDER BY COUNT(UTV.idUslugiTV)
31 | S t r o n a
Bazy danych  kolokwium SQL
32 | S t r o n a
Bazy danych  kolokwium SQL
33 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
Rozgrywka
TypRozgrywki
RodzRozgr
IdR Integer
IdT Integer
NazwaRozgrywki Characters (256)
Sezon
NazwaTypu Characters (20)
Identifier_1
IdS Integer
Identifier_1
0,n 0,n
SezonRozgrywki
NazwaSezonu Characters (256)
0,n
DataOd Date & Time
DataDo Date & Time
TypZdarzenia
Klub
Identifier_1
IdTZ Integer
IdK Integer
NazwatTZ Characters (50)
NazwaKlubu Characters (256)
0,n
RokZalozenia Integer
Identifier_1
Identifier_1
ZdarzenieTYpu
Gosc Gospodarz
RodzajPozycji
Zdarzenie
IdRP Integer
Mecz IdZ Integer
NazwaRP Characters (256)
zdarzpow
CzasZ Timestamp
IdM Integer
Identifier_1
0,n
Relationship_6
Identifier_1
DataM Date & Time Relationship_7
MiejsceM Integer
0,n FunkcjaSedziowska
Identifier_1 0,n
IdFS Integer
NazwaFS Characters (256)
0,n
Identifier_1
SkladNaMecz SedziaMecz
0,n
Dziedziczenie
0,n
Sedzia
Zawodnik KlasaS Characters (256)
Osoba
IdO Characters (11)
Nazwisko Characters (30)
Imie Characters (20)
DataUr Date
MiejsceUr Characters (30)
Kraj Characters (30)
Identifier_1
34 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
Rozgrywka
FK_ROZGRYWK_RODZROZGR_TYPROZGRypRozgrywki
T
IdT integer
IdT integer
IdR integer
NazwaTypu char(20)
Sezon
SezonRozgrywki NazwaRozgrywki char(256)
IdS integer
IdT integer
FK_SEZONROZ_SEZONROZG_KLUB
NazwaSezonu char(256)
FK_SEZONROZ_SEZONROZG_ROZGRYWK
IdR integer
FK_SEZONROZ_SEZONROZG_SEZON
DataOd timestamp
IdK integer
TypZdarzenia DataDo timestamp
IdS integer
Klub
IdTZ integer
IdK integer
NazwatTZ char(50)
NazwaKlubu char(256)
RokZalozenia integer
RodzajPozycji
Zdarzenie
FK_SKLADNAM_SKLADNAME_KLUB IdRP integer FK_ZDARZENI_ZDARZENIE_TYPZDARZ
FK_MECZ_GOSC_KLUB
NazwaRP char(256)
IdTZ integer
IdZ integer
Mecz
FK_ZDARZENI_RELATIONS_MECZ IdM integer
FK_ZDARZENI_ZDARZPOW_ZDARZENI
IdM integer
Zda_IdTZ integer
FK_ZDARZENI_RELATIONS_ZAWODNIK
IdK integer
FK_SEDZIAME_SEDZIAMEC_MECZ Zda_IdZ integer
Klu_IdK integer
FK_MECZ_GOSPODARZ_KLUB
IdO char(11)
DataM timestamp
FK_SEDZIAME_SEDZIAMEC_FUNKCJAS
CzasZ timestamp
FunkcjaSedziowska
FK_SKLADNAM_SKLADNAME_MECZ
MiejsceM integer
IdFS integer
NazwaFS char(256)
SkladNaMecz
SedziaMecz
IdM integer
IdFS integer
IdO char(11)
IdO char(11)
Sedzia
FK_SKLADNAM_SKLADNAME_RODZAJPO
IdK integer
IdM integer
IdRP integer IdO char(11)
KlasaS char(256)
Zawodnik
Nazwisko char(30)
IdO char(11)
FK_SKLADNAM_SKLADNAME_ZAWODNIK
Imie char(20)
Nazwisko char(30)
DataUr date
FK_SEDZIAME_SEDZIAMEC_SEDZIA
Imie char(20)
MiejsceUr char(30)
DataUr date
Kraj char(30)
MiejsceUr char(30)
Kraj char(30)
35 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL(w zadaniu przyjęte, że zdarzeniem pow. może byd bramka)
SELECT Za.IdO, MAX(Za.Nazwisko),
SELECT TOP 10 K.IdK,
MAX(Za.Imie),
MAX(K.NazwaKlubu) Nazwa,
COUNT(TZ.IdTZ)+COUNT(ZT.idTZ)
COUNT(TZ.IdTZ)+COUNT(ZT.IdTZ)
LiczbaBramek
LiczbaBramekGosp
FROM Sezon s INNER JOIN
FROM Sezon S INNER JOIN
SezonRozgrywki SR ON(S.IdS = SR.IdS)
SezonRozgrywki SR ON (S.IdS =SR.IdS)
INNER JOIN Klub K ON (K.IdK = SR.IdK)
INNER JOIN Klub K ON (SR.IdK = K.IdK)
INNER JOIN Mecz M ON(M.IdK = K.IdK)
INNER JOIN Mecz M ON (M.IdK = K.IdK)
INNER JOIN Zdarzenie Z ON (M.IdM =
INNER JOIN Zdarzenie Z ON (M.IdM =
Z.IdM)
Z.IdM)
INNER JOIN TypZdarzenia TZ ON(TZ.IdTZ
INNER JOIN TypZdarzenia TZ ON
= Z.IdTZ) LEFT JOIN TypZdarzenia ZT
(TZ.IdTZ = Z.IdTZ) LEFT JOIN
ON(ZT.IdTZ = Z.Zda_IdTZ)
TypZdarzenia ZT ON(ZT.IdTZ =
INNER JOIN Zawodnik Za ON(Z.IdO =
Z.Zda_IdTZ)
Za.IdO)
WHERE TZ.NazwatTZ = 'gol gosp' AND
WHERE YEAR(now(*))-1 = YEAR(S.DataOd)
YEAR(now(*))-5<=YEAR(S.DataOd)
AND TZ.NazwatTZ IN('gol gosp','gol
GROUP BY K.IdK
gosc')
HAVING LiczbaBramekGosp>=1
GROUP BY Za.IdO
ORDER BY LiczbaBramekGosp DESC
HAVING LiczbaBramek =
(SELECT tab1.LiczbaBramek FROM
SELECT TOP 10 K.IdK,
(SELECT TOP 1 Za.IdO, COUNT(TZ.IdTZ)+
MAX(K.NazwaKlubu) Nazwa,
COUNT(ZT.IdTZ) LiczbaBramek
COUNT(TZ.IdTZ)+COUNT(ZT.IdTZ)
FROM Sezon s INNER JOIN
LiczbaBramekGosp
SezonRozgrywki SR ON(S.IdS = SR.IdS)
FROM (SELECT TOP 5 * FROM Sezon ORDER
INNER JOIN Klub K ON (K.IdK = SR.IdK)
BY Sezon.DataOd) S INNER JOIN
INNER JOIN Mecz M ON(M.IdK = K.IdK)
SezonRozgrywki SR ON (S.IdS =SR.IdS)
INNER JOIN Zdarzenie Z ON (M.IdM =
INNER JOIN Klub K ON (SR.IdK = K.IdK)
Z.IdM)
INNER JOIN Mecz M ON (M.IdK = K.IdK)
INNER JOIN TypZdarzenia TZ ON(TZ.IdTZ
INNER JOIN Zdarzenie Z ON (M.IdM =
= Z.IdTZ) LEFT JOIN TypZdarzenia ZT
Z.IdM)
ON(ZT.IdTZ = Z.Zda_IdTZ)
INNER JOIN TypZdarzenia TZ ON
INNER JOIN Zawodnik Za ON(Z.IdO =
(TZ.IdTZ = Z.IdTZ) LEFT JOIN
Za.IdO)
TypZdarzenia ZT ON(ZT.IdTZ =
WHERE YEAR(now(*))-1 = YEAR(S.DataOd)
Z.Zda_IdTZ)
AND TZ.NazwatTZ IN('gol gosp','gol
WHERE TZ.NazwatTZ = 'gol gosp'
gosc')
GROUP BY K.IdK
GROUP BY Za.IdO
HAVING LiczbaBramekGosp>=1
ORDER BY LiczbaBramek DESC) tab1)
ORDER BY LiczbaBramekGosp DESC
36 | S t r o n a
Bazy danych  kolokwium SQL
SELECT R.IdR, MAX(R.NazwaRozgrywki), MAX((Bramki.LiczbaBramek*100/Mecze.LiczbaMeczy)*0.01) SredniaBramekM,
MAX((Zdarzenia.LiczbaZdarzen*100/Mecze.LiczbaMeczy)*0.01) SredniaZdarzenM
FROM
(SELECT R.IdR, COUNT(M.IdM) LiczbaMeczy
FROM Rozgrywka R INNER JOIN SezonRozgrywki SR ON(R.IdR = SR.IdR) INNER JOIN Klub K
ON(K.IdK = SR.IdK) INNER JOIN Mecz M ON(K.IdK = M.IdK)
GROUP BY R.IdR) Mecze,
(SELECT R.IdR, (COUNT(Z.IdZ) + COUNT(Z.Zda_IdZ)) LiczbaZdarzen
FROM Rozgrywka R INNER JOIN SezonRozgrywki SR ON(R.IdR = SR.IdR) INNER JOIN Klub K
ON(K.IdK = SR.IdK) INNER JOIN Mecz M ON(K.IdK = M.IdK) INNER JOIN Zdarzenie Z ON(Z.IdM = M.IdM)
GROUP BY R.IdR) Zdarzenia,
(SELECT R.IdR, COUNT(TZ.IdTZ) + COUNT(ZT.IdTZ) LiczbaBramek
FROM Rozgrywka R INNER JOIN SezonRozgrywki SR ON(R.IdR = SR.IdR) INNER JOIN Klub K
ON(K.IdK = SR.IdK) INNER JOIN Mecz M ON(K.IdK = M.IdK) INNER JOIN Zdarzenie Z ON(Z.IdM = M.IdM)
INNER JOIN TypZdarzenia TZ ON(TZ.IdTZ = Z.IdTZ) LEFT JOIN TypZdarzenia ZT ON(ZT.IdTZ = Z.Zda_IdTZ)
WHERE TZ.NazwatTZ IN('gol gosp', 'gol gosc')
GROUP BY R.IdR) Bramki, Rozgrywka R
WHERE Mecze.IdR = Zdarzenia.IdR AND Zdarzenia.IdR = Bramki.IdR AND Bramki.IdR = R.IdR
GROUP BY R.IdR
37 | S t r o n a
Bazy danych  kolokwium SQL
38 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
GieldaPW
TypPW
Relationship_1
IdGPW Integer
IdTPW Integer
NazwaGPW Characters (50)
NazwaT PW Characters (50)
Identifier_1
Identifier_1
Sesja
PW
IdS Integer
IdPW Integer
DataSesji Date
Relationship_2
0,n
NazwaPW Characters (50)
Identifier_1
0,n
NotowaniaPW
DataEmisji Date
Czas Time Zlecenie
Identifier_1
0,n
0,n
Cena Integer
IdZ Integer
DataWystawienia Date
0,n
0,n 0,n
DataWaznosci Date
Status Integer
PozycjaZlecenia
Identifier_1
LIczba Integer
CenaMin Money (15,2)
CenaMax Money (15,2)
Relationship_3
Status Integer
Datarealizacji Date
TypZlecenia
... ...
PortfelKlienta
IdTZ Integer
LIczba Integer
NazwaTZ Characters (50)
0,n
Identifier_1
Klient
EmisjaPW
0,n
IdK Integer
OsobaFizyczna
LIczba Integer
DataRejestracji Date
PESEL Integer
CenaNominalna Money 0,n
(15,2)
Identifier_1
Nazwisko Characters (50)
Imie Characters (50)
OsobaPrawna
DataUr Date
NIP Integer
Nazwa Characters (50)
DataZalozenia Date
Inheritance_1
39 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
GieldaPW
IdGPW integer
TypPW
NazwaGPW char(50)
Sesja
FK_SESJA_RELATIONS_GIELDAPW
IdTPW integer
IdS integer
NazwaTPW char(50)
FK_PW_RELATIONS_TYPPW
IdGPW integer
NotowaniaPW
DataSesji date
IdS integer
IdPW integer
Zlecenie
PW
FK_NOTOWANI_NOTOWANIA_SESJA
Czas time
FK_NOTOWANI_NOTOWANIA_PW
IdTZ integer
IdPW integer
Cena integer
FK_POZYCJAZ_POZYCJAZL_ZLECENIEIdZ
integer
IdTPW integer
FK_POZYCJAZ_POZYCJAZL_PW
DataWystawienia date
NazwaPW char(50)
PozycjaZlecenia DataWaznosci date
DataEmisji date
Status integer
IdS integer
FK_POZYCJAZ_POZYCJAZL_SESJA
IdPW integer
IdTZ integer
FK_ZLECENIE_RELATIONS_TYPZLECE
IdZ integer
IdK integer
FK_EMISJAPW_EMISJAPW_PW
Kli_IdK integer
TypZlecenia
EmisjaPW
LIczba integer
IdTZ integer
FK_PORTFELK_PORTFELKL_PW
CenaMin numeric(15,2)
IdPW integer
NazwaTZ char(50)
CenaMax numeric(15,2)
IdK integer FK_POZYCJAZ_POZYCJAZL_OSOBAPRA

Status integer
LIczba integer
Datarealizacji date OsobaFizyczna
CenaNominalna numeric(15,2)
FK_PORTFELK_PORTFELKL_OSOBAPRA
CenaRealizacji numeric(15,2)
IdK integer
FK_EMISJAPW_EMISJAPW2_OSOBAPRA
PESEL integer
FK_POZYCJAZ_POZYCJAZL_OSOBAFIZ
FK_PORTFELK_PORTFELKL_OSOBAFIZNazwisko char(50)
OsobaPrawna
Imie char(50)
PortfelKlienta
PortfelKlienta
IdK integer
DataUr date
IdK integer NIP integer
IdK integer
DataRejestracji date
IdPW integer Nazwa char(50)
FK_PORTFELK_PORTFELKL_PW IdPW integer
LIczba integer DataZalozenia date
LIczba integer
DataRejestracji date
40 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
SELECT TOP 5 tab3.Osoba,
tab3."Nazwisko/Nazwa", tab3.Imie, tab3.Srodki
FROM (SELECT O.IdK Osoba, MAX(O.Nazwisko)
"Nazwisko/Nazwa", MIN(O.Imie) Imie,
SUM(PK.LIczba*tab.Cena) Srodki
FROM OsobaFizyczna O INNER JOIN PortfelKlienta
PK ON(O.IdK = PK.IdK) INNER JOIN
PW ON(PW.IdPW = PK.IdPW) INNER JOIN (
SELECT DISTINCT N.IdPW, N.IdS, N.Cena,
s.DataSesji, N.Czas
FROM NotowaniaPW N INNER JOIN Sesja S ON(N.IdS
= S.IdS)
ORDER BY S.DataSesji DESC) tab ON PW.IdPW =
tab.IdPW
GROUP BY O.IdK
UNION
SELECT O1.IdK Osoba, MAX(O1.Nazwa)
"Nazwisko/Nazwa", NULL,
SUM(PK2.LIczba*tab2.Cena) Srodki
FROM OsobaPrawna O1 INNER JOIN PortfelKlienta2
PK2 ON(O1.IdK = PK2.IdK) INNER JOIN
PW ON(PW.IdPW = PK2.IdPW) INNER JOIN (
SELECT DISTINCT N.IdPW, N.IdS, N.Cena,
S.DataSesji,N.Czas
FROM NotowaniaPW N INNER JOIN Sesja S ON(N.IdS
= S.IdS)
ORDER BY S.DataSesji DESC) tab2 ON PW.IdPW =
tab2.IdPW
GROUP BY O1.IdK) tab3
ORDER BY tab3.Srodki DESC
41 | S t r o n a
Bazy danych  kolokwium SQL
42 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
SiecGastronomii
Dostawca
ZakladZZ
# idSG Integer
# idD Integer
# idZ Integer
JOS
o KodPocztowy Characters (5)
o KodPocztowy Characters (5)
o KodPocztowy Characters (5)
# idJOS Characters (7)
JOS_Adres
o Poczta Characters (30) JOS_Adres
JOS_Adres
o Poczta Characters (30)
o Poczta Characters (30)
o NazwaJOS Characters (50)
SiecZZZ
o Miejscowosc Characters (30)
o Miejscowosc Characters (30)
o Miejscowosc Characters (30)
o Ulica Characters (40)
o Ulica ZamowieniaZZZ
Characters (40)
o Ulica Characters (40)
Typ_ZZZ
o NrLokalu Characters (15)
o NrLokalu Characters (15)
o NrLokalu Characters (15)
o Telefon Characters (15)
o Telefon Characters (15)
o Telefon Characters (15)
o Email Characters (50)
o Email Characters (50)
o Email Characters (50)
o NazwaSG Characters (30)
o NazwaD Characters (50)
o NazwaZ Characters (50)
Zamowienia
TypZakladuZZ
Pozycje
# idTZZ Integer
o Liczba Number (15)
Faktura
o NazwaTZZ Characters (50)
o CenaJednostkowa Number (8,2)
o VAT Integer # NrF Integer
Pozycje
o DataWystawienia Date
RodzajProduktu
Produkt
# idRP Integer
TypFaktury
Pozycje
# IdP Integer
o NazwaRP Characters (50)
# idTF Integer
o NazwaP Characters (50)
RodzajDok
o NazwaTF Characters (30)
RodzajeProduktow
43 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
ZakladZZ
SiecGastronomii
Dostawca
idTZZ integer
idSG integer
idD integer
idZ integer
JOS
idJOS char(7)
idJOS char(7)
idSG integer
KodPocztowy char(5)
idJOS char(7)
KodPocztowy char(5)
idJOS char(7)
Poczta char(30)
NazwaJOS char(50)
Poczta char(30)
KodPocztowy char(5)
Miejscowosc char(30)
Miejscowosc char(30)
Poczta char(30)
Ulica char(40)
Ulica char(40)
Miejscowosc char(30)
NrLokalu char(15)
NrLokalu char(15)
Ulica char(40)
Telefon char(15)
Telefon char(15)
NrLokalu char(15)
Email char(50)
Email char(50)
Telefon char(15)
NazwaSG char(30)
NazwaD char(50)
Email char(50)
NazwaZ char(50)
Faktura
Pozycje
idD integer
idRP integer
NrF integer
IdP integer
TypZakladuZZ
idTF integer
idD integer
idTZZ integer
idTZZ integer
NrF integer
idZ integer
NazwaTZZ char(50)
Liczba numeric(15)
DataWystawienia date
CenaJednostkowa numeric(8,2)
VAT integer
RodzajProduktu
idRP integer
TypFaktury
Produkt
NazwaRP char(50)
idTF integer
idRP integer
NazwaTF char(30)
IdP integer
NazwaP char(50)
44 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
45 | S t r o n a
Bazy danych  kolokwium SQL
46 | S t r o n a
Bazy danych  kolokwium SQL
Model konceptualny.
EtapTrasy Trasa
Etap
0,n
Lp Integer IDTrasy Integer
IdEtapu Integer
0,n
NazwaTrasy Characters (50)
Odleglosc Number (15,3)
Opis Text
Czas_Przejazdu Time
Identifier_1
Identifier_1
Przystanek_Koncowy
Przystanek_Poczatkowy
Przystanek Rozklad
0,n
IDPrzystanku Przyjazd Time
0,n
Ulica Variable characters (60) Odjazd Time
Pojazd
OpisPrzystanku Text DzienTygodnia Integer
Nrpojazdu Integer
0,n
Uwagi Text
Identifier_1
0,n
Identifier_1
Linia
Pojazd_TypPojazdu
Kierowca
Kierownik
NrLinii Integer
Uwagi Text Osoba
TypPojazdu
IdOsoby Integer
Identifier_1
IDTypuPojazdu Integer
Nazwisko Characters (30)
NazwaTP Characters (30)
Imie Characters (30)
TypLinii_Linia
Pojemnosc Integer
Adres Characters (30)
Identifier_1
Identifier_1
TypLinii
IdTypuLinii Integer
NazwaTypuLinii Variable characters (100)
Identifier_1
47 | S t r o n a
Bazy danych  kolokwium SQL
Model fizyczny.
EtapTrasy
IDTrasy integer
Etap
IdEtapu integer
Trasa
IdEtapu integer
Lp integer
IDTrasy integer
IDPrzystanku
NazwaTrasy char(50)
Prz_IDPrzystanku
Rozklad
Opis long varchar
Odleglosc numeric(15,3)
Czas_Przejazdu time
IdTypuLinii integer
NrLinii integer
IDTypuPojazdu integer
Przystanek
Nrpojazdu integer
IDPrzystanku
IDTrasy integer
Pojazd
Ulica varchar(60)
IDPrzystanku
IDTypuPojazdu integer
OpisPrzystanku long varchar
Przyjazd time
Nrpojazdu integer
Odjazd time
DzienTygodnia integer
Uwagi long varchar
Linia
IdTypuLinii integer
Kierowca
NrLinii integer
Uwagi long varchar
IdOsoby integer TypPojazdu
IDTypuPojazdu integer
IDTypuPojazdu integer
Nrpojazdu integer
Osoba
NazwaTP char(30)
Pojemnosc integer
IdOsoby integer
Oso_IdOsoby integer
Nazwisko char(30)
TypLinii
Imie char(30)
IdTypuLinii integer
Adres char(30)
NazwaTypuLinii varchar(100)
48 | S t r o n a
Bazy danych  kolokwium SQL
Zapytania w SQL
49 | S t r o n a