DB&SQL4- 1 Sets in een RDBS Een database bevat een set tabelschema’s (entiteitsklassen) Een...
-
Upload
raphael-mertens -
Category
Documents
-
view
222 -
download
2
Transcript of DB&SQL4- 1 Sets in een RDBS Een database bevat een set tabelschema’s (entiteitsklassen) Een...
1DB&SQL4-
Sets in een RDBSEen databasebevat een set tabelschema’s (entiteitsklassen)
Een tabelschema (entiteitsklasse)bevat een set attribuutnamen met hun type
Een tabel bevat een set records (entiteiten)
Een record bevat een set waarden gekoppeld aan attribuutnamen
Een resultaat van een query is een set records
NB hoewel settheorie de basis is van het relationeel model wordt in Access niet altijd in sets gewerkt en kun je toch duplicaatrecords krijgen, gebruik zo nodig DISTINCT(ROW).
2DB&SQL4-
Verzamelingen
Het relationeel model is gebaseerd op de verzamelingenleer.Een verzameling (set) bestaat uit 0 - N () elementen.Het aantal elementen: de kardinaliteit van de set.Een element kan zelf weer een set zijn.
{} lege set{1,8,3} een set met 3 elementen{{2,4},{1,8}} een set met twee sets als elementen
De elementen van een set zijn ongeordend{1,8,3} = {8,1,3}
De elementen van een set zijn uniek (vgl groep individuen){1,8,3,3,1} = {1,8,3}
3DB&SQL4-
Operaties op verzamelingen
Samenvoegen van twee sets: vereniging / union A B
{1,2,5,6} {2,6,7}= {1,2,5,6,7}
Gemeenschappelijke deel van twee sets: doorsnede / intersection A B
{1,2,5,6} {2,6,7} = {2,6}
verschil / difference van twee sets:A - B
{1,2,5,6} – {2,6,7} = {1,5}
12
5 67
12
56
7
1 25 6
7
4DB&SQL4-
Setoperaties op hele tabellen: UNION
(opdracht in Access SQL)Vereniging entiteiten uit twee tabellen samenvoegen
VB: samenvoegen van 2 ledenlijsten
Naam Adres Wpl Naam Adres Wpl AA Aweg 1 A AA Aweg 1 ABB Bweg 2 B DD Dweg 4 D
CC Cweg 3 C union EE Eweg 5 E
Naam Adres WplAA Aweg 1 ABB Bweg 2 BCC Cweg 3 CDD Dweg 4 DEE Eweg 5 E
VVV ADO
5DB&SQL4-
Setoperaties op hele tabellen: INTERSECTION
niet als opdracht in Access SQLDoorsnede gemeensch. elementen uit twee tabellen
VB: vergelijken van 2 ledenlijsten: wie is van beide lid?
Naam Adres Wpl Naam Adres Wpl AA Aweg 1 A AA Aweg 1 ABB Bweg 2 B DD Dweg 4 D
CC Cweg 3 C intersection EE Eweg 5 E
Naam Adres WplAA Aweg 1 A
VVV ADO
6DB&SQL4-
Setoperaties op hele tabellen: DIFFERENCEniet als opdracht in Access SQL
Difference/except verschil tussen twee tabellen
VB: wie is wel van VVV maar niet van ADO lid?VVV ADONaam Adres Wpl Naam Adres Wpl AA Aweg 1 A AA Aweg 1 ABB Bweg 2 B DD Dweg 4 D
CC Cweg 3 C VVV-ADO EE Eweg 5 E
Naam Adres WplBB Bweg 2 BCC Cweg 3 C
7DB&SQL4-
Setoperaties op groepen records
Vereniging keus tussen verschillende restricties OR
b.v. geef me planten met rode OF paarse kleur
Intersectie meerdere restricties tegelijk AND
b.v. Welke planten zijn van de soort bol EN bloeien in maart?
Verschil positive en negatieve restrictie (AND NOT)
b.v. Welke planten zijn van de soort bol maar bloeien NIET in maart?
8DB&SQL4-
Cartesisch product van tabellen
Product entiteiten uit twee tabellen combineren tot een nieuwe entiteit
VB alle mogelijke combinaties voor gemengd spel
VNaam VLft MNaam MLft V1 28 M1 28V2 24 M2 18V3 18 product
VNaam VLft Mnaam MLftV1 28 M1 28V2 24 M1 28V3 18 M1 28V1 28 M2 18V2 24 M2 18V3 18 M2 18
9DB&SQL4-
Joins-1Equi join product van tabellen met restrictie:
alleen de rijen met een gelijke waarde voor een gelijk attribuut(gewoonlijk sleutel/verwijzende sleutel)
VB: Geef paren met gelijke leeftijd:
VNaam VLft MNaam MLft V1 28 M1 28V2 24 M2 18V3 18 equi-join
VLft=MLft
VNaam VLft Mnaam LftV1 28 M1 28V3 18 M2 18
10DB&SQL4-
Joins-2Theta join: product van 2 tabellen met restrictie:
alleen rijen met ongelijke waarde voor een gelijk attribuut ( < , > , =<, => )
VB: Geef paren waarbij de man ouder is dan de vrouw
VNaam VLft MNaam MLft V1 28 M1 28V2 24 M2 18V3 18 theta
join MLft > VLft
VNaam VLft Mnaam MLftV2 24 M1 28V3 18 M1 28
11DB&SQL4-
Joins-3
Inner join of kortweg join (SQL join of via restrictie)product van 2 (of meer) tabellenrestrictie: rijen met gelijke waarde voor gelijke attributen
Natural join (als boven + selectie velden) : inner join, maar dubbele kolom(men) maar 1 keer in resultaat tabel
Left/right outer join (SQL left/right join of via restrictie)Van één tabel alle records, ook als er geen combinatie bestaat
Full outer join (alleen via restricties)
Self join (tabel dupliceren)Een tabel combineren met zichzelf
12DB&SQL4-
Twee gerelateerde tabellen
ISBN Title Price PubID0-321-32132-1 Balloon $34.00 31-1111-1111-1 C++ $29.95 10-99-999999-9 Emma $20.00 10-91-335678-7 Fairie Queene $15.00 10-91-045678-5 Hamlet $20.00 20-103-45678-9 Iliad $25.00 10-12-345678-6 Jane Eyre $49.00 30-99-777777-7 King Lear $49.00 2
BOOKS
PubID PubName PubPhone1 Big House 123-456-78902 Alpha Press 999-999-99993 Small House 714-000-0000
PUBLISHERS Publisher en Book zijn aparte entiteitsklassen/ tabellen, de relatie wordt gelegd via PubID, dit is de verwijzende sleutel in BOOKS naar een Publisher in PUBLISHERS
13DB&SQL4-
Jointype definieren in relatieschema of QBE
Wil je, als je de relatie boek-uitgever gebruikt in een query
1. alleen de combinaties van beiden zien?2. alle boeken, en alleen de bijbehorende uitgevers?3. alle uitgevers, en alleen de bijbehorende boeken?
Default in Access is 1, maar soms is een andere keus nuttig,dan kun je b.v. zoeken naar de uitgevers zonder boeken. Als je een relatie definieert kun je kiezen.
1 is de equi-join, 2 en 3 zijn outer joins
Opties voor een relatie -1
14DB&SQL4-
Twee eisen aan een veld?Wie verkoopt tofu en konbu? Eerste interpretatie: of
Select s.SupplierName FROM Suppliers s, Products pWHERE s.SupplierID = p.SupplierID AND(p.ProductName = ‘tofu’ OR p.Productname = ‘konbu’);
s pSupplierName SupplierID SupplierID ProductNameMayumi’s 6 6 konbuMayumi’s 6 6 tofuNewSupp 11 11 konbuEen WHERE clause geeft de restricties aan vooréén record (regel) van de (combinatie)tabel. Hoe regel je de interpretatie zowel .. als .. ?
15DB&SQL4-
Een tabel tweemaal raadplegen
Stileren:
Geef de naam van de Supplier s waarvoor geldt dat
s verkoopt ‘tofu’ en s verkoopt ‘konbu’oftewel:
er is een product p1 in Products met p1.ProductName = ‘tofu’
te koop bij s (p1.SupID = s.SupID) ener is een product p2 in Products met p2.ProductName = ‘konbu’
te koop bij s (p2.SupID = s.SupID)
16DB&SQL4-
2 eisen aan 1 record via duplicaattabel
(ook andere mogelijkheden)
SELECT s.CompanyName FROM Suppliers s, Products p1, Products p2WHERE s.SupplierID = p1.SupplierID AND p1.ProductName = ‘tofu’AND s.SupplierID = p2.SupplierIDAND p2.ProductName = ‘konbu’
s p1 p2SupName SupID SupID Product SupID ProductMayumi’s 6 6 tofu 6 konbu
17DB&SQL4-
Geef aantal boeken, som, gemiddelde, maximum en minimum prijsvan BOOKS:
SELECT COUNT(*) AS N, SUM(Price) AS Sum, AVG(Price) AS Avg, MAX(Price) AS Max, MIN(Price) AS MinFROM BOOKS
N Sum Avg Max Min15 F 659,90 F 43,99 F 250,00 F 12,00
Statistische functies
18DB&SQL4-
Statistische functies per groep -1Geef aantal boeken, totale prijs en gemiddelde prijs per PubID
SELECT COUNT(*) AS N, SUM(Price) AS Sum, AVG(Price) AS Avg, PubID FROM BOOKSGROUP BY PubID
N Sum Avg PubID6 F 139,95 F 23,33 13 F 103,00 F 34,33 25 F 166,95 F 33,39 31 F 250,00 F 250,00 4
19DB&SQL4-
Stat. functies per groep - 2Hoe duur is het duurste boek van elke uitgever?Geef naam en nummer van de uitgever en zijn hoogste prijs,sorteer op prijs
SELECT p.PubName, p.PubID, Max(b.Price) AS MaximumFROM BOOKS b, PUBLISHERS pWHERE b.PubID = p.PubIDGROUP BY p.PubName, p.PubIDORDER BY Max(b.Price);
In ORDER BY en GROUP BY geen alias voor veldnaam(dus niet Maximum gebruiken i.p.v. Max(b.Price)
In GROUP BY alle velden uit de SELECT-clausedie geen stat. functie bevatten.
20DB&SQL4-
Statistische functies: groepscriteriumGeef de gemiddelde prijs van de boeken van die uitgever(s) waarvan we meerdere boeken hebben
Stileren:Geef de PubIDs in BOOKS, met de gemiddelde prijs van hun BOOKS voor die PubID waarvoor geldt dathet aantal BOOKS > 1
Genereren:SELECT PubID, AVG(Price)FROM BOOKSGROUP BY PubIDHAVING COUNT(*) > 1;
Een criterium op recordniveauzet je in WHERE
HAVING geeft eenstatistisch criteriumvoor een groep
21DB&SQL4-
Syntax van SELECT..
SELECT (DISTINCT) attribuut|expression (AS kolomnaam), ..-> keuze van de velden in de resultaattabelFROM tabel (AS) alias, ..-> benodigde tabellen, eventueel met afkorting WHERE relatiecriteria AND zoekcriterium AND/OR .. -> met diverse vergelijkingsoperatoren (<, >, =)GROUP BY alle niet-statistische velden in resultaattabel-> bij gebruik van stat. functies in SELECT clauseHAVING statistisch criterium-> criteria voor subgroepenORDER BY attr-> sorteren van resultaattabel
22DB&SQL4-
Oefenen
Geef de uitgevernamen + aantal door hen uitgegeven boeken van de uitgevers die een gemiddelde prijs onder de 25 hebben
SELECT p.PubName, COUNT(b.ISBN) As AantalFROM PUBLISHERS p, BOOKS bWHERE p.PubID = b.PubIDGROUP BY p.PubID, p.PubNameHAVING AVG(b.Price) < 25;
Waarom zou je hier (eventueel) PubID in GROUP BY zetten?
23DB&SQL4-
Subquery 1
1. Wat is de hoogste boekenprijs in de collectie?
SELECT MAX(b.Price) FROM BOOKS b;
2. Wat is het duurste boek uit de collectie?
???
24DB&SQL4-
Subquery 1 - vervolg
Stileren: Title van BOOKS waarvoor-geldt-dat Price is de hoogste prijs in BOOKS
SQL genereren:SELECT Title FROM BOOKSWHERE Price =
(SELECT MAX(Price) FROM BOOKS);
In de subquery (subselect) wordt opnieuw naar detabel BOOKS gekeken om het maximum op te zoeken
NIET: WHERE Price = Max(Price) want wat zou dat betekenen?
25DB&SQL4-
Subquery 2
Geef me de boeken die duurder zijn dan gemiddeld
Stileren: BOOKS b waarvoor geldt datPrice groter is dan de gemiddelde Price van BOOKS
Genereren: SELECT ISBN, Title, PriceFROM BOOKSWHERE Price >
(SELECT AVG(Price) FROM BOOKS);
26DB&SQL4-
Subquery 3Wat is de langste auteursnaam in ons bestand en hoe lang is hij?
Stileren: a.AuName en lengte van a.AuName van AUTHORs awaarvoor-geldt-dat
de lengte van a.AuName is de maximumlengte van AuName in AUTHORS
of
de lengte van a.AuName is groter of gelijk aan alle waarden van AuName in AUTHORS
27DB&SQL4-
Subquery 3 - SQL
Genereren:SELECT AuName, Len(AuName)FROM AUTHORSWHERE Len(AuName) = (SELECT Max(Len(AuName)) FROM AUTHORS);
of:
SELECT AuName, Len(AuName)FROM AUTHORSWHERE Len(AuName) >= ALL (SELECT Len(AuName) FROM AUTHORS);
NB naast ALL bestaat ook ANY en SOME
28DB&SQL4-
IN met een subquery
IN kan wordt gebruikt als meer dan een waarde kan voldoen ; zulke waarden kunnen worden opgehaald met een subselect.
Geef de boeken van publishers met een naam op ‘House’
SELECT * FROM BOOKSWHERE PubID IN (SELECT p.PubID FROM PUBLISHERS pWHERE p.PubName LIKE "*House";)
N.B. Alternatief in dit geval natuurlijkSELECT .. (BOOKSvelden) FROM BOOKS b, PUBLISHERS pWHERE b.PubID = p.PubID AND p.PubName LIKE "*House";
29DB&SQL4-
2 eisen aan 1 record: Via Exists
EXISTS: geeft aan dat een subselect een resultaat heeft (de set niet leeg is)
SELECT s.CompanyName FROM Suppliers sWHERE EXISTS
(SELECT * FROM Products pWHERE s.SupplierID = p.SupplierID AND p.ProductName = 'tofu’)
AND EXISTS (SELECT * FROM Products p WHERE p.SupplierID = s.SupplierID AND p.ProductName = 'konbu');
Omdat tabel s niet in de FROM-clause staat,wordt de ‘centrale’ s.SupplierIDgebruikt!
30DB&SQL4-
NOT EXISTSIs er een uitgever zonder boeken?
Stileren:Geef de publisher p van PUBLISHERSwaarvoor geldt dater geen boek b van BOOKS bestaatdat p heeft b uitgegeven (dus PubID gelijk)
SELECT * FROM PUBLISHERS pWHERE NOT EXISTS(SELECT * FROM BOOKS bWHERE p.PubID = b.PubID);
(alternatief: outer join + NULL-criterium)
31DB&SQL4-
Statistische eisen aan een groep
Uitgevers met minder dan 3 boeken:
SELECT p.PubNameFROM PUBLISHERS p, BOOKS bWHERE p.PubID = b.PubIDGROUP BY p.PubNameHAVING COUNT(b.Title) < 3;
De uitgever met de minste boeken?
32DB&SQL4-
Subselect in HAVING
SELECT p.PubName, COUNT(b.ISBN)FROM Publishers AS p, Books AS bWHERE p.PubID=b.PubIDGROUP BY p.PubNameHAVING COUNT(b.ISBN) <= ALL
(SELECT COUNT(ISBN) FROM BOOKSGROUP BY PubID)
33DB&SQL4-
Meer dan één
Vraag:Geef me de auteurs die bij meer dan één uitgever zijn
Stileren:Geef de a.AuNames in AUTHORS waarvoor geldt datvoor hun BOOKS b (relatie via BOOK/AUTHOR)geldt dat het aantal verschillende b.PubID >1
34DB&SQL4-
Meer dan een?
SELECT a.AuNameFROM BOOKS b, [BOOK/AUTHOR] ba, AUTHORS a WHERE a.AuID = ba.AuID AND b.ISBN = ba.ISBNGROUP BY a.AuNameHAVING COUNT(b.PubID) > 1;
Wat gaat hier fout?
Probleem: COUNT(DISTINCT ..) wordt niet geaccepteerd,dus bij elk boek wordt PubID meegeteld, of hij hetzelfdeof anders is dan de vorige
35DB&SQL4-
Meer dan 1: dubbele tabel
JOIN- Structuur:BOOKS BOOK/AUTH AUTHORS BOOK/AUTH BOOKSb1 --- ba1 --- a --- ba2 --- b2
SELECT DISTINCT a.AuNameFROM BOOKS AS b1,[BOOK/AUTHOR] AS ba1, AUTHORS AS a, [BOOK/AUTHOR] AS ba2, BOOKS AS b2WHERE b1.ISBN = ba1.ISBN AND ba1.AuID = a.AuID AND b2.ISBN = ba2.ISBN AND ba2.AuID = a.AuID AND b1.PubID <> b2.PubID;
of via EXISTS
36DB&SQL4-
Meer dan x???
37DB&SQL4-
Meer dan x uitgevers: via 2 queries
Maak eerst een query die alle bestaande relaties AUTHOR-PUBLISHER toont, sla hem op als AUTH_PUB
SELECT DISTINCT a.AuName, p.PubIDFROM AUTHORS a, [BOOK/AUTHOR] ba, BOOKS b, PUBLISHER pWHERE a.AuID = ba.AuID AND ba.ISBN = b.ISBN AND b.PubID = p.PubID;
Dan een query die in AUTH_PUB de juiste records selecteert:
SELECT AuName FROM AUTH_PUBGROUP BY AuNameHAVING COUNT(*) > 1;