Inleiding Databanken: oefeningen Sven Casteleyn Lokaal: 6G319 Email: [email protected] HomePage: te bereiken via
http://wise.vub.ac.be/
Inleiding Databanken: leerstof
SQL– DDL & DML: syntax en toepassingen
Relationeel model en relationele algebra Domein & tuple calculus Entity Relationship modeling FA’s & Normalisatie
Relational model:overzicht
Relational model
Herhaling terminologie
Relatie (tabel)
Een relatie is een tabel met kolommen en rijen.
Attribuut
Een attribuut is een kolom van een relatie
Tupel
Een tupel is een rij van een relatie.
Domein
Een domein is een verzameling van toegelaten waarden voor 1 of
meerdere attributen. Voorbeelden (informeel) van domeinen voor
attributen zijn “integers”, “strings”, “datum”, …
Relationele databank
Een relationele databank is een collectie van relaties (tabellen)
Relational model:overzicht
Relational model:overzichtRelatie
Relational model:overzichtRelatie
Attributen
Relational model:overzichtRelatie Tupel
Attributen
Superkey
Een attribuut of een verzameling van attributen die elk tuple uniek
identificeert binnen een relatie.
Candidate key
Een superkey {K} zodanig dat geen enkele echte deelverzameling van K een superkey is.
Elk tuple van R wordt door de waarde van K uniek geidentificeerd (uniciteit).
Geen enkele echte deelverzameling van K heeft de uniciteit eigenschap.
Primary key
De candidate key die gekozen werd om tuples uniek te identificeren in
een relatie
Foreign key
Een attribuut of een verzameling van attributen binnen een relatie die
overeenkomt met een candidate key van een andere relatie.
Relational model: keys
SuperkeySuperkey
Relational model: keys
Candidate key
SuperkeySuperkey
Candidate key
Relational model: keysCandidate key Candidate key
SuperkeySuperkey
Primary key
Primary key Primary key
Relational model: keysCandidate key Candidate key
SuperkeySuperkey
Primary key
Primary key Primary key
Foreign keyForeign key
Structured Query Language (SQL)
SQL: doel Een database taal moet de gebruiker 3 zaken toelaten:
- de relationele databank creeren- de relationele databank populeren- (simpele en minder simpele) queries uitvoeren
2 delen: - DDL voor het definieren van de databank struktuur - DML voor het opvragen en updaten van tupels
SQL: kenmerken
SQL bevat geen control flow commando’s.
SQL is een “non-procedural language”, d.w.z. dat je specificeert wat je wil, in plaats van hoe je het wil bekomen.
Algemene SQL Statement
SELECT [DISTINCT | ALL] {* | attribuut (-expressie) [AS new_name]
[,...] }
FROM relatie [alias_name] [, ...]
[WHERE conditie]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]
Algemene SQL Statement
SELECT [DISTINCT | ALL] {* | attribuut (-expressie) [AS new_name]
[,...] }
FROM relatie [alias_name] [, ...]
[WHERE conditie]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]
Algemene SQL Statement
SELECT [DISTINCT | ALL] {* | attribuut (-expressie) [AS new_name]
[,...] }
FROM relatie [alias_name] [, ...]
[WHERE conditie]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]
Algemene SQL Statement
SELECT [DISTINCT | ALL] {* | attribuut (-expressie) [AS new_name]
[,...] }
FROM relatie [alias_name] [, ...]
[WHERE conditie]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]
De basis SELECT statement
SELECT: specificeert welke attributen (kolommen) in het resultaat moeten verschijnen
FROM: specificeert de te gebruiken relatie(s)
WHERE: filtert de tupels in het resultaat door test van een bepaalde conditie
De basis SELECT statement: simpel voorbeeld
Vind naam en voornaam van alle studenten die in het derde jaar zitten
De basis SELECT statement: simpel voorbeeld SELECT StudentSecondname,
StudentFirstname FROM Student WHERE StudentGrade = 3
Vind naam en voornaam van alle studenten die in het derde jaar zitten
De basis SELECT statement: simpel voorbeeld SELECT StudentSecondname,
StudentFirstname FROM Student WHERE StudentGrade = 3
Relatie
De basis SELECT statement: simpel voorbeeld SELECT StudentSecondname,
StudentFirstname FROM Student WHERE StudentGrade = 3
Attribuut
Relatie
Attribuut
De basis SELECT statement: simpel voorbeeld SELECT StudentSecondname,
StudentFirstname FROM Student WHERE StudentGrade = 3
Conditie
Attribuut
Relatie
Attribuut
SQLData Manupilation Language:
Specificatie
SQL: alle attributen en tupels teruggeven SELECT StudentFirstname,
StudentSecondname, StudentID, StudentDateOfBirth,StudentGradeFROM Student
SQL: alle attributen en tupels teruggeven SELECT StudentFirstname,
StudentSecondname, StudentID, StudentDateOfBirth,StudentGradeFROM Student
Equivalent:
SELECT *FROM Student
Specifiek attribuut, alle tupels teruggeven Geef alle voornamen van studenten terug SELECT StudentFirstname
FROM Student Geef alle voor- en familienamen van
studenten terug SELECT StudentFirstname,
StudentSecondnameFROM Student
DISTINCT
Elimineert dubbels
DISTINCT
Elimineert dubbels
Geef alle verschillende voornamen van studenten
SELECT DISTINCT StudentFirstname FROM Student
Hernoemen van kolommen: AS
Gebruik AS om kolommen te hernoemen
SELECT DISTINCT StudentFirstname AS VoornamenZonderDubbelsFROM Student
Tupel selectie
Gebruik WHERE (conditie) Geef naam en voornaam van alle studenten
die in het 3de jaar zitten SELECT StudentSecondname,
StudentFirstnameFROM StudentWHERE StudentGrade = 3;
Toegelaten in de WHERE clause
= , < , > , <= , >= , < >
Logische operatoren: OR, AND, NOT
Volgorde van evaluatie:– van links naar rechts– subexpressies tussen haakjes eerst geevalueerd– NOT wordt vóór AND en OR geevalueerd– AND wordt geevalueerd voor OR
Range conditie: BETWEEN/ NOT BETWEEN Waarden binnen een bepaald interval Geef voor- en familienaam terug van alle
studenten die geboren zijn in 1979 SELECT StudentFirstname,
StudentDateOfBirthFROM StudentWHERE StudentDateOfBirth BETWEEN #01-01-1979# AND #01-01-1980#;
Pattern matching: LIKE
% doet dienst al wildcard (nul of meer characters)
_ (underscore) stelt exact 1 character voor
SELECT StudentFirstnameFROM StudentWHERE StudentFirstname LIKE '%an%';
Testen op NULL: IS NULL / IS NOT NULL NULL stelt een onbekende waarde voor in een
tabel Geef voornaam en studiejaar terug van alle
studenten waarvan de geboortedatum gekend is SELECT StudentFirstname, StudentGrade
FROM StudentWHERE StudentDateOfBirth IS NOT NULL
Resultaten sorteren: ORDER BY
ORDER BY wordt gebruikt om het resultaat van een query te sorteren
ORDER BY kan stijgend (ASC) of dalend (DESC)
Resultaten sorteren (ORDER BY) ORDER BY wordt gebruikt om het resultaat van
een query te sorteren ORDER BY kan stijgend (ASC) of dalend
(DESC) Geef de voornamen terug van alle studenten
gerangschikt per jaar (laagste jaar eerst) SELECT StudentFirstname, StudentGrade
FROM StudentORDER BY StudentGrade ASC
Ordenen naar meerdere kolommen Geef alle voornamen van studenten
gerangschikt per jaar en alfabetisch gerangschikt (per jaar)
Ordenen naar meerdere kolommen Geef alle voornamen van studenten
gerangschikt per jaar en alfabetisch gerangschikt (per jaar)
SELECT StudentFirstname, StudentGradeFROM StudentORDER BY StudentGrade, StudentFirstname
Aggregate functions (1/4)
COUNT: geeft het aantal waarden in een kolom terug SUM: geeft de som van de waarden in een kolom
terug AVG: geeft het gemiddelde van de waarden in een
kolom terug MIN: geeft de kleinste waarde van een kolom terug MAX: geeft de grootste waarde van een kolom terug
Aggregate functions (2/4)
Alle aggregate functions gaan over 1 kolom van een relatie, en geven 1 waarde terug
COUNT, MIN en MAX kunnen niet numerieke domeinen aan, SUM en AVG kunnen enkel gebruikt worden met numerieke waarden
Al deze functies elimineren eerst NULL waarden ( uitgezonderd COUNT (*) )
Aggregate functions (3/4)
Aggregate functies kunnen ENKEL gebruikt worden in de SELECT clause en de HAVING clause (zie verder)
SELECT StudentFirstNameFROM StudentWHERE MIN(StudentDateOfBirth)
Aggregate functions (4/4)
Als een SELECT clause een aggregate function bevat en er wordt geen GROUP BY clause gebruikt, dan kan de SELECT clause geen kolom referenties bevatten tenzij die kolom in een aggregate wordt gebruikt
SELECT StudentID, COUNT(StudentGrade)FROM Student
Count: voorbeeld
Hoeveel studenten zijn ingeschreven in het 2de jaar?
SELECT COUNT(StudentFirstname)FROM StudentWHERE StudentGrade = 2
COUNT en DISTINCT: voorbeeld
Hoeveel verschillende voornamen komen voor onder de studenten?
SELECT COUNT(DISTINCT StudentFirstname) AS DifferentNamesFROM Student
COUNT en AVG: voorbeeld
Vind het totaal aantal studenten en hun gemiddelde studiejaar die niet geboren zijn in 1980
SELECT COUNT(StudentFirstname) AS AantalStudenten, AVG(StudentGrade) AS GemiddeldeStudiejaarFROM StudentWHERE StudentDateOfBirth NOT BETWEEN #01-01-1980# AND #31-12-1980#
MIN MAX AVG: voorbeeld
Geef de leeftijd van de oudste en jongste student terug
SELECT MIN(StudentDateOfBirth) AS Oudste, MAX(StudentDateOfBirth) AS JongsteFROM Student
Subqueries
Subqueries kunnen gebruikt worden in de WHERE en HAVING (zie verder) clauses
Drie soorten:– Scalaire subquery: geeft een enkele waarde terug
– Rij subquery: geeft meerdere attributen (kolommen) terug, maar slechts 1 enkele tupel (rij)
– Table subquery: geeft een relatie (tabel) terug
Subqueries: voorbeeld
Geef de voornamen van alle studenten terugdie geboren zijn op dezelfde dag als de student met StudentID 1.
SELECT StudentFirstnameFROM StudentWHERE StudentDateOfBirth =
(SELECT StudentDateOfBirth FROM Student WHERE StudentID = 1)
Subquery met aggregate function (1/2)
Geef de namen terug van alle studenten die in een hoger jaar zitten dan de gemiddelde student
SELECT StudentFirstnameFROM StudentWHERE StudentGrade >
(SELECT AVG(StudentGrade) FROM Student)
Subquery met aggregate function (2/2)
Merk op dat we hier niet kunnen schrijven:SELECT StudentFirstnameFROM StudentWHERE StudentGrade >
AVG(StudentGrade) Aggregate functions mogen immers enkel
gebruikt worden in de SELECT en HAVING clause, niet in de WHERE clause.
Subquery met aggregate function:rules (1/2) ORDER BY mag niet gebruikt worden in een
subquery De SELECT lijst in de subquery moet bestaan
uit 1 enkele attribuutnaam of expressie, behalve voor subqueries die het keyword EXISTS gebruiken (zie verder)
Attribuutnamen in de subquery verwijzen by default naar de tabel in de FROM clause van de subquery.
Subquery met aggregate function:rules (2/2) Wanneer de subquery voorkomt als operand in
een vergelijking, dan moet de subquery de rechtse operand zijn
SELECT StudentFirstnameFROM StudentWHERE (SELECT AVG(StudentGrade)
FROM Student) < StudentGrade
Nested subqueries; gebruik van IN: voorbeeld Geef de voornamen terug van alle studenten
die geboren zijn tussen het geboortejaar van de student met studentenummer 7 en voor 1980
Nested subqueries; gebruik van IN: voorbeeld
SELECT StudentFirstnameFROM StudentWHERE StudentDateOfBirth IN (SELECT StudentDateOfBirth FROM Student WHERE StudentDateOfBirth BETWEEN
(SELECT StudentDateOfBirth FROM Student WHERE StudentID = 7)
AND #01-01-1980#);
Nested subqueries; gebruik van IN: opmerkingen De query wordt uitgevoerd van binnen naar
buiten Merk op dat we hier niet de gelijkheids
conditie kunnen gebruiken, gezien er meerdere geboortedata tussen de opgegeven tijdstippen kunnen vallen, in plaats wordt IN gebruikt
ANY/SOME en ALL (1/2)
ANY/SOME en ALL kunnen gebruikt worden in subqueries die een kolom met meerdere waarden teruggeven
Als de subquery voorafgegaan wordt door ALL dan zal de conditie waar zijn enkel en alleen indien ze geldt voor ALLE waarden geproduceerd door de subquery
ANY/SOME en ALL (2/2)
Als de subquery voorafgegaan wordt door SOME dan zal de conditie waar zijn enkel en alleen indien ze geldt voor een (een of meerdere) waarde geproduceerd door de subquery
ANY en SOME zijn synoniemen Als de subquery leeg is, dan geeft ALL true
terug, ANY geeft false terug
ANY / SOME: voorbeeld
Geef de voornamen van alle studenten die jonger zijn (=later geboren) zijn dan ten minste 1 student uit het 2de jaar
SELECT StudentFirstnameFROM StudentWHERE StudentDateOfBirth > SOME
(SELECT StudentDateOfBirth FROM Student WHERE StudentGrade = 2)
Oefeningen Databank
Server / host: wendy.vub.ac.be Poort: 5432 Initiele databank: <familienaam><naam>,
bijv. ‘CasteleynSven’ Login: <naam><voornaam>, bijv.
‘CasteleynSven’ Paswoord: database_01
Top Related