Database SQL

2010
ROC ter AA T&T Team ICT
Cees Loomans
[DATABASE SQL]
Een aanzet tot de standaard vraagtaal SQL.
Database SQL
1 mei 2010
Inhoud
Inleiding SQL .................................................................................................................................. 5
Het opzetten van de database (DDL) ............................................................................................. 6
De CREATE TABLE opdracht ....................................................................................................... 6
De ALTER TABLE opdracht ......................................................................................................... 7
De DROP TABLE opdracht .......................................................................................................... 8
Integriteitbewaking met de UNIQUE INDEX .............................................................................. 9
Indexeren ................................................................................................................................... 9
VRAGEN EN OPDRACHTEN .................................................................................................. 11
VRAGEN ............................................................................................................................... 11
OPDRACHTEN ...................................................................................................................... 11
Het werken met Gegevens (DML) ............................................................................................... 12
Het vullen van de tabel met behulp van INSERT ..................................................................... 12
VRAGEN EN OPDRACHTEN .................................................................................................. 14
VRAGEN ............................................................................................................................... 14
OPDRACHTEN ...................................................................................................................... 14
Fundamentele opdrachten met SELECT ...................................................................................... 15
VRAGEN ............................................................................................................................... 20
OPDRACHTEN ...................................................................................................................... 20
DISTINCT .................................................................................................................................. 21
OPDRACHTEN ...................................................................................................................... 22
Rekenen met kolominhouden ................................................................................................. 23
OPDRACHTEN ...................................................................................................................... 24
Operatoren in het WHERE gedeelte. ....................................................................................... 25
OPDRACHTEN ...................................................................................................................... 26
Het combineren van operatoren met AND en OR................................................................... 27
In SQL kunnen we dan gebruik maken van AND en OR....................................................... 27
OPDRACHTEN ...................................................................................................................... 28
VRAGEN EN OPDRACHTEN .................................................................................................. 30
VRAGEN ............................................................................................................................... 30
OPDRACHTEN ...................................................................................................................... 30
BETWEEN ................................................................................................................................. 31
OPDRACHTEN ...................................................................................................................... 31
IN.............................................................................................................................................. 32
OPDRACHTEN ...................................................................................................................... 32
Hoofdstuk: Inleiding SQL
Het gebruik van NOT................................................................................................................ 29
2
Database SQL
1 mei 2010
LIKE .......................................................................................................................................... 33
OPDRACHTEN ...................................................................................................................... 33
NULL......................................................................................................................................... 34
OPDRACHTEN ...................................................................................................................... 34
ORDER BY ................................................................................................................................. 35
OPDRACHTEN ...................................................................................................................... 36
GROUP BY, HAVING, COUNT, AVG, SUM, MAX en MIN .......................................................... 37
Count ................................................................................................................................... 38
AVG ...................................................................................................................................... 38
SUM ..................................................................................................................................... 38
MAX en MIN......................................................................................................................... 39
HAVING ................................................................................................................................ 40
OPDRACHTEN ...................................................................................................................... 41
HET RAADPLEGEN VAN MEERDERE TABELLEN ............................................................................ 42
De verschillende mogelijkheden ............................................................................................. 42
De JOIN .................................................................................................................................... 44
OPDRACHTEN ...................................................................................................................... 46
De OUTERJOIN ......................................................................................................................... 47
De AUTOJOIN ........................................................................................................................... 47
OPDRACHTEN ...................................................................................................................... 49
De NON-EQUIJOIN ................................................................................................................... 50
De SUBQUERY .......................................................................................................................... 50
OPDRACHTEN ...................................................................................................................... 52
ANY en ALL ............................................................................................................................. 53
OPDRACHTEN ...................................................................................................................... 53
De gesynchroniseerde SUBQUERY .......................................................................................... 55
De EXISTS operator .............................................................................................................. 56
OPDRACHTEN ...................................................................................................................... 56
OPDRACHTEN ...................................................................................................................... 58
VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT ............................................... 59
De denkwijze............................................................................................................................ 59
Nog nooit ... ............................................................................................................................. 59
OPDRACHTEN ...................................................................................................................... 60
Alleen maar ... .......................................................................................................................... 60
OPDRACHTEN ...................................................................................................................... 61
Hoofdstuk: Inleiding SQL
UNION, MINUS en INTERSECT ................................................................................................. 57
3
Database SQL
1 mei 2010
De (dubbele) ontkenning ......................................................................................................... 62
OPDRACHTEN ...................................................................................................................... 63
Alle ... ....................................................................................................................................... 64
OPDRACHTEN ...................................................................................................................... 66
DE DATA DICTIONARY .................................................................................................................. 67
Het principe van de data dictionary ........................................................................................ 67
De data dictionary als bewaker van de database. ................................................................... 67
VIEWS........................................................................................................................................... 70
Het principe van de VIEW ........................................................................................................ 70
Het maken van een VIEW ........................................................................................................ 70
OPDRACHTEN ...................................................................................................................... 72
Het verwijderen van een VIEW ................................................................................................ 72
GEGEVENS WIJZIGEN ................................................................................................................... 73
De verschillende mogelijkheden ............................................................................................. 73
INSERT .................................................................................................................................. 73
OPDRACHTEN ...................................................................................................................... 73
UPDATE ................................................................................................................................ 74
OPDRACHTEN ...................................................................................................................... 74
DELETE ................................................................................................................................. 75
OPDRACHTEN ...................................................................................................................... 75
GEGEVENS BEVEILIGEN................................................................................................................ 76
Vormen van gegevensbeveiliging ............................................................................................ 76
Bescherming tegen verlies................................................................................................... 76
Autorisatie van gebruik........................................................................................................ 76
VRAGEN ............................................................................................................................... 79
Hoofdstuk: Inleiding SQL
OPDRACHTEN ...................................................................................................................... 79
4
Database SQL
1 mei 2010
Inleiding SQL
De bedoeling van de cursus is om te leren SQL vragen te stellen aan een relationele database.
Door het zelfstandig doornemen van de cursus en daarbij de vragen en opdrachten te maken
leert de student hoe hij of zij een database kan bevragen. We maken daarbij gebruik van de
database MYSQL en een voorbeeld database.
Vraag aan je docent hoe je de database benaderd.
SQL (in Amerikaanse termen uit te spreken als 'sequel') is sedert de eerste publicaties van
Codd in 1971 en Boyce en Chamberlin in 1973 in sneltempo uitgegroeid tot de standaardtaal
voor vierde generatie relationele databases.
Juist omdat SQL zo nauw aansluit bij de reeds bekende relationele algebra en calculus en vrij
eenvoudige instructies kent voor alle aspecten van het opzetten en beheren van een database
is de populariteit van SQL snel toegenomen en nog steeds groeiende.
Een waarschuwing is hierbij meteen van belang: SQL is inderdaad vrij snel aan te leren en lijkt
daardoor de oplossing voor vele problemen.
Niets is minder waar. In het nu volgende zal ik proberen om aan de hand van een voorbeeld
database duidelijk te maken hoe eenvoudig SQL aan te leren is. Aan de andere kant hoop ik
ook duidelijk te maken dat deze eenvoud meteen het grootste gevaar is omdat men al snel
denkt de juiste oplossing gevonden te hebben, terwijl achteraf soms blijkt dat dit nu juist niet
het geval was!
Hoofdstuk: Inleiding SQL
SQL wordt veel gebruikt in combinatie met dynamisch gegenereerde webpagina’s op het
internet. Hierbij worden allerlei gegevens, artikelen en dergelijke in de database opgeslagen en
indien nodig opgeroepen en getoond. Om later zelf dynamische pagina’s te kunnen maken is
kennis van SQL noodzakelijk.
5
Database SQL
1 mei 2010
Het opzetten van de database (DDL)
De CREATE TABLE opdracht
Voor het opzetten van tabellen kent SQL een vrij eenvoudig commando namelijk het CREATE
TABLE commando dat er als volgt uit ziet:
CREATE TABLE <tabelnaam>
(
<kolomnaam> <kolomtype> {(<lengte>)} {NOT NULL},
<kolomnaam2> <kolomtype> {(<lengte>)}{NOT NULL},…
)
De notatiewijze van de commando's in dit boek moeten we als volgt lezen:
- HOOFDLETTERS betekenen een letterlijk in te tikken woord
- kleine letters tussen < > wil zeggen een zelf in te vullen woord
- tekst tussen { } is optioneel
- een serie ..... geeft aan dat het gehele voorgaande stuk meerdere malen herhaald mag
worden
- alle andere leestekens zoals, en ( ) moeten op de aangegeven plaatsen gebruikt worden.
Gemakshalve gaan we hieruit van een nogal "kale" CREATE TABLE opdracht. In een latere
cursus zal nog worden stilgestaan bij de uitbreidingen op deze opdracht en de rol van de data
dictionaire.
Bij de bepaling van het kolomtype kunnen we kiezen uit verschillende mogelijkheden. De
belangrijkste typen zijn:
voor kolommen met decimale getallen
voor alfanumerieke kolommen
voor datumkolommen
voor alfanumerieke kolommen met zeer veel karakters
Bij DECIMAL kunnen we de lengte en eventueel het aantal decimalen aangeven.
Bij CHAR moeten we de lengte aangeven.
Naast deze standaardtypen komt (afhankelijk, van het RDBMS waar mee gewerkt wordt) nog
een groot aantal andere typen (VARCHAR, LONG VARCHAR, DECIMAL, FLOAT, INTEGER,
SMALLINT en dergelijke) voor, die echter niet meer dan variaties op deze vier basistypen zijn.
We zouden dus de volgende opdracht kunnen maken:
CREATE TABLE WERKNEMERS(
CODE CHAR(3) NOT NULL,
NAAM CHAR(15) NOT NULL,
VOORLETTERS CHAR(8),
VOORVOEGSEL CHAR(8),
ADRES CHAR(20),
POSTCODE CHAR(7),
WOONPLAATS CHAR(20),
TELEFOON CHAR(12),
DATUM_IN_D DATE,
SAL DECIMAL(8,2),
CHEF CHAR(3)
)
Hoofdstuk: Het opzetten van de database (DDL)
DECIMAL
CHAR
DATE
LONG
6
Database SQL
1 mei 2010
Hierdoor ontstaat er een tabel met een elftal kolommen waarmee we onze werknemers
kunnen beschrijven. Een aantal opvallende zaken zullen we eerst bespreken.
Bij een tweetal kolommen geven we de kwalificatie NOT NULL, dat wil zeggen dat deze kolom
ingevuld moet worden. Het is namelijk niet toegestaan dat een werknemer opgenomen wordt
die nog geen CODE heeft (denk aan de integriteit!) en bovendien stellen we vast dat van iedere
werknemer in ieder geval zijn of haar NAAM bekend moet zijn.
De volledige naam van iedere werknemer hebben we opgesplitst in een kolom NAAM, een
kolom VOORLETTERS en een kolom VOORVOEGSEL voornamelijk omdat dit bij het maken van
gesorteerde overzichten erg handig kan zijn. We zorgen er dan voor dat bij het alfabetiseren,
werknemer 'De Jonge' na 'Jansma' wordt geplaatst in plaats van voor 'Dekker'.
Ondanks het feit dat een telefoonnummer uit cijfers bestaat hebben we hier toch gezegd dat
een telefoonnummer van het type CHAR is. Dit heeft een tweetal redenen; in de eerste plaats
zorgen we er zo voor dat de nullen die vooraan staan bij een telefoonnummer niet weggehaald
worden (dus geen 20 in plaats van 020) en bovendien voorkomen we zo dat 020-1234567 de
waarde -1234547 toebedeeld krijgt.
Bij de kolom datum van indiensttreding (DATUM_IN_D) hebben we alleen het type van de
kolom (DATE) en niet de lengte opgegeven omdat de lengte door het RDBMS zelf bepaald is en
dus niet apart opgegeven hoeft te worden.
De kolom salaris (SAL) is van het type DECIMAL en heeft een lengteaanduiding 8,2. Dat wil
zeggen dat deze kolom in totaal 8 posities breed zal worden waarvan twee posities door de
decimalen in beslag zullen worden genomen.
De kolom CHEF bestaat uit een drietal karakters. Dit is uiteraard niet toevallig evenveel als de
kolom CODE omdat in deze kolom eenvoudig weg de CODE van de CHEF van de
desbetreffende werknemer wordt ingevuld. In de gegevensanalyse spreekt men dan wel van
een CODE in de rol van CHEF, met andere woorden deze CODE speelt, als het ware een beetje
toneel en heeft zich vermomd als CHEF.
Eigenlijk zou het CREATE commando voldoende moeten zijn voor het opzetten van een
database. Helaas is het zo dat we ten tijde van het opzetten van de database niet echt in de
toekomst kunnen kijken. Zo kan het voorkomen dat we ons bij de analyse van de situatie
vergist hebben in de mogelijke groei van een bepaalde waarde: (In Amerika zorgde de eerste
cheque boven de 9.999,99 dollar die de Social Security Agency moest boeken voor een
onkostenpost van tientallen Miljoenen dollars).
Om in staat te zijn een wijziging in een tabel aan te brengen zonder omslachtige ex- en import
operaties uit te hoeven voeren hebben we de beschikking over het ALTER TABLE commando.
Met behulp van de ALTER TABLE opdracht kunnen we verschillende soorten wijzigingen
aanbrengen. In deze paragraaf zullen we overigens niet ingaan op de beperkingen en andere
regels die door middel van ALTER TABLE in de data dictionaire kunnen worden vastgelegd. Dit
wordt nog in later hoofdstuk aan de orde gesteld.
Hoofdstuk: Het opzetten van de database (DDL)
De ALTER TABLE opdracht
7
Database SQL
1 mei 2010
In de eerste plaats kan het voorkomen dat de lengte of het type van een kolom gewijzigd moet
worden. In een aantal RDBMS-en kunnen we hier een optie van de ALTER TABLE opdracht voor
gebruiken:
ALTER TABLE <tabelnaam> MODIFY (<kolomnaam> <kolomtype>
[(<lengte>)] [ NULL| NOT NULL ], ...)
We zouden dus onze tabel aan kunnen passen door op te geven
ALTER TABLE WERKNEMERS MODIFY (SAL DECIMAL(9,2))
Na het geven van deze opdracht is in de tabel WERKNEMERS de kolom SAL een positie breder
geworden.
De toevoeging NOT NULL kan alleen gegeven worden als de desbetreffende kolom in de gehele
tabel in iedere rij een waarde heeft. Bovendien mag een bestaande kolom alleen kleiner
gemaakt worden of een ander kolomtype gegeven worden als in alle rijen van de tabel deze
kolom nog oningevuld (oftewel NULL) is.
Een tweede soort wijziging zou kunnen zijn dat er een kolom toegevoegd moet gaan worden.
Daarvoor gebruiken we de volgende opdracht:
ALTER TABLE <tabelnaam> ADD (<kolomnaam> <kolomtype>
[(<lengte>)] [ NULL | NOT NULL ], ...)
We zouden bijvoorbeeld op kunnen geven
ALTER TABLE WERKNEMERS ADD (SOFI_NUM CHAR(10))
Dit heeft tot gevolg dat aan de tabel WERKNEMERS een kolom SOFI NUM voor het sociaalfiscaal nummer van de werknemers wordt toegevoegd.
De DROP TABLE opdracht
Voor het weer verwijderen van tabellen is er in SQL een zeer eenvoudige en daardoor ook zeer
gevaarlijke opdracht
DROP TABLE <tabelnaam>
Als we dus op zouden geven
DROP TABLE WERKNEMERS
zou dit betekenen dat we in één regel een opdracht hebben ingetikt die een gehele tabel
zonder meer verwijdert. Voorzichtigheid is hier dus geboden!
Hoofdstuk: Het opzetten van de database (DDL)
Een kolom toevoegen die als NOT NULL gedefinieerd wordt kan alleen als de tabel nog geen
enkele rij bevat. De standaard waarde voor de MODIFY optie is NULL, met andere woorden
deze kolom mag oningevuld blijven.
8
Database SQL
1 mei 2010
Integriteitbewaking met de UNIQUE INDEX
Bij het definiëren van onze tabel zijn we er van uitgegaan dat in elk geval de CODE en de
NAAM van een werknemer bekend moeten zijn. Dat is een eerste stap in de richting van een
integere database.
Een probleem blijft echter dat we er bij sommige RDBMS-en niet voor kunnen zorgen dat in de
kolom CODE bij het toevoegen van een rij steeds een unieke code wordt gebruikt. Om er in die
gevallen voor te zorgen dat een sleutelkolom alleen unieke waarden kan bevatten, kunnen we
een UNIQUE INDEX aanmaken. Dat doen we door op te geven
CREATE [UNIQUE] INDEX <indexnaam> ON <tabelnaam>(.<kolomnaam>,
...)
UNIQUE is dus een optie van de CREATE INDEX opdracht omdat in SQL gebruik gemaakt wordt
van indices om het opzoeken van gegevens te versnellen. We zullen het principe van de index
verder behandelen in een van de volgende paragrafen.
Wat betreft het toekennen van een naam aan een index zijn we geheel vrij. Toch is het
verstandig om ook hier enige systematiek in acht te nemen. In de praktijk is. Bijvoorbeeld een
verkorte weergave van de tabelnaam en de kolom(men) waarop de index gedefinieerd is
uitermate handig.
We zouden dus kunnen opgeven
CREATE UNIQUE INDEX WERKNM_CODE ON WERKNEMERS(CODE)
Hierdoor wordt bij iedere nieuwe rij die toegevoegd wordt aan de
tabel WERKNEMERS eerst gecontroleerd of de waarde in de kolom CODE niet al bestaat in de
tabel WERKNEMERS.
CREATE UNIQUE INDEX WRKNM_NM_TEL ON WERKNEMERS(NAAM, TELEFOON)
Zodat bij iedere nieuwe rij eerst gecontroleerd wordt of de combinatie van NAAM en
TELEFOON niet al bestaat in de tabel.
In steeds meer implementaties is het op deze wijze bewaken van de integriteit overgenomen
door het opnemen van regels en beperkingen in de data dictionaire. Dit wordt in een alter
hoofdstuk aangegeven.
Indexeren
In de vorige paragraaf hebben we het gebruik van een UNIQUE INDEX aan de orde gesteld.
Daarmee konden we er voor zorgen dat er in een bepaalde kolom (of een bepaalde combinatie
van kolommen) alleen unieke waarden ingevuld konden worden. Dit is echter niet het enige
gevolg van het aanmaken van een (UNIQUE) INDEX.
Hoofdstuk: Het opzetten van de database (DDL)
Uit het feit dat we bij het opgeven van de kolomnaam ook meerdere kolomnamen op mogen
geven kunnen we al afleiden dat het dus ook mogelijk is om samengestelde sleutels op deze
manier uniek te houden. Zo zouden we op kunnen geven
9
Database SQL
1 mei 2010
Naast het bewaken van de uniciteit zorgt een index ook voor het versnellen van de
zoekoperaties.
We hebben al opgemerkt dat de UNIQUE INDEX een optie is van het algemene lNDEX
commando. Als we een index aanmaken betekent dit dat we de mogelijkheid creëren om de
desbetreffende tabel te benaderen via de index in plaats van het van begin tot einde
doorzoeken van de tabel. Er wordt als het ware een index zoals bij een boek aangemaakt
waardoor het RDBMS door gebruik te maken van deze index de gezochte rijen veel sneller
terug kan vinden.
Naast dit voordeel van de tijdwinst bij het opzoeken van rijen kan het aanmaken van indices
ook een nadeel hebben. Net als bij de index van een boek zal een wijziging in de tabel tot
gevolg hebben dat de index ook bijgewerkt moet worden. Bij het aanbrengen van wijzigingen
zal een index dus eerder tot tijdverlies dan tot tijdwinst leiden. Vandaar dat het van belang is
om slechts indices aan te maken op kolommen waar vaak op gezocht zal worden en niet op
kolommen die sterk aan wijzigingen onderhevig zijn.
Bovendien moeten we ons realiseren dat bij vrijwel alle RDBMS-en die met SQL benaderd
kunnen worden het onmogelijk is om het RDBMS te dwingen gebruik te maken van de index.
Er is een zogenaamde optimizer aanwezig die zelf zal bepalen of het gebruik van de index
nuttig is of niet.
Als we in de tabel WERKNEMERS dus versneld willen zoeken op grond van bijvoorbeeld de
CODE van de werknemers zouden we een index aan kunnen maken met
CREATE INDEX WERKN_CODE ON WERKNEMERS(CODE)
Het weer verwijderen van een INDEX kunnen we uitvoeren door gebruik te maken van de
opdracht
Hoofdstuk: Het opzetten van de database (DDL)
DROP INDEX <indexnaam>
1
0
Database SQL
1 mei 2010
VRAGEN EN OPDRACHTEN
Deze opdrachten en vragen kunnen alleen worden uitgeprobeerd op de database indien U
deze zelf heeft geïnstalleerd.
VRAGEN
1. Waarom zouden we bij het toevoegen van een kolom aan een bestaande tabel niet de
NOT NULL kwalificatie mee kunnen geven?
2. Waar zorgt de toevoeging UNIQUE voor bij de volgende index:
CREATE UNIQUE INDEX KL_NM_PC ON KLANTEN(NAAM, POSTCODE)
3. Waarom zou het onmogelijk zijn om een bestaande kolom smaller te maken?
OPDRACHTEN
1. Geef de opdracht in SQL voor het aanmaken van de tabel waar de gegevens van een
student kunnen worden opgeslagen. Zorg ervoor dat in ieder geval de naam, adres,
postcode, telefoonnummer, geboortedatum en datum van start van de studie worden
opgenomen. (Bepaal zelf het type en de lengte!)
2. Geef de opdracht in SQL om aan de tabel uit opgave 1 een kolom genaamd
EXPORTCODE van 4 karakters toe te voegen.
3. Geef de opdracht in SQL om deze EXPORTCODE vervolgens 1 karakter breder te
maken.
4. Geef de opdracht in SQL om een tabel KLANTEN aan te maken met daarin opgenomen
een alfanumerieke KLANTCODE kolom die altijd ingevuld moet zijn, een alfanumerieke
NAAM kolom, een numerieke CRED_LIMIET kolom en een datumkolom genaamd
KLANT_SINDS. Bepaal zelf de lengtes.
5. Geef de opdracht In SQL om bij de KLANTEN tabel een index aan te maken op de
kolom KLANTCODE.
Hoofdstuk:
6. Geef de opdracht in SQL om er met behulp van indexeren voor te zorgen dat de
combinatie van de kolommen KLANTCODE en NAAM in iedere rij uniek zal blijven.
1
1
Database SQL
1 mei 2010
Het werken met Gegevens (DML)
Het vullen van de tabel met behulp van INSERT
Voordat we ook maar iets kunnen doen met een tabel zullen er uiteraard eerst gegevens in
moeten staan. We zuilen dat hier doen met de INSERT opdracht. In de praktijk zal het INSERT
commando een ondergeschikte rol spelen omdat het zeer vaak veel eenvoudiger is om met
behulp van een applicatiegenerator een invoerscherm te maken zodat. de vrij omslachtige
wijze van werken bij de INSERT opdracht vermeden kan worden.
Met de INSERT opdracht kunnen we één rij tegelijk toevoegen aan een bepaalde tabel
INSERT INTO <tabelnaam> [(kolomnaam,...)] VALUES (waarde, ...)
Zo zouden we in de tabel WERKNEMERS zoals eerder aangemaakt een aantal rijen kunnen
inbrengen door gebruik te maken van
INSERT INTO WERKNEMERS VALUES ('10', 'BUCK', 'A.P.', 'DE',
'LINDELAAN 2', '1055 PP', 'AMSTERDAM', '020-6768087', '01-JAN80', 4567, '99')
INSERT INTO WERKNEMERS VALUES ('12', 'BRASSER', 'K.J.', NULL,
'LINDELAAN 18', '1055 PP', 'AMSTERDAM', '020-9874386',
'12-OCT-88', 2149, '20')
INSERT INTO WERKNEMERS VALUES ('18', 'VLIET', 'P.', 'VAN','KADE
34', '1056 KM', 'AMSTERDAM', '020-9321072',
'28-SEP-85',2638, '20')
INSERT INTO WERKNEMERS VALUES ('19', 'UMBGROVE', 'M.M.',NULL,
'TALMALAAN 3', '4351 JG', 'VLISSINGEN', '0118-439816','13-NOV87', 2988, '30')
INSERT INTO WERKNEMERS VALUES ('20', 'MAAS', 'L.C.',
NULL','BOSJESLAAN 3', '3085 HG', 'ROTTERDAM', '010-9859526',
'01-FEB-84', 3332, '10')
INSERT INTO WERKNEMERS VALUES ('25', 'NEVE', 'G.', 'DE',
'PRINSENGRACHT 20', '1048 GP', 'AMSTERDAM', '020-9865743', '15MAY-91', 3148, '40')
INSERT INTO WERKNEMERS VALUES ('26', 'GEEL', 'J.M.G.',
'VAN','PLEIN 43', '3568 JL', 'UTRECHT', '030-7385195', '01-DEC88',1921, '30')
INSERT INTO WERKNEMERS VALUES ('28', 'ZEEUW', 'A.J.', 'DE',
'JULlANASTRAAT 6', '5610 JP', 'EINDHOVEN', '040-5932182', '12AUG-86', 2981, '35')
Hoofdstuk: Het werken met Gegevens (DML)
INSERT INTO WERKNEMERS VALUES ('16', 'LIEVENSE', 'I.H.',
NULL,'HOOFDSTRAAT 3', '8948 KK', 'LEEUWARDEN', '0588916534','14-0CT-95', 3148, '30')
1
2
Database SQL
1 mei 2010
INSERT INTO WERKNEMERS VALUES ('30', 'MERK', 'J.', NULL,
'EIKENLAAN 12', '3086 KK', 'ROTTERDAM', '010-9734733', '01-MAR86',4749, '40')
INSERT INTO WERKNEMERS VALUES ('31', 'PAREE', 'G.M.', NULL,'KADE
44:, '1056 KM', 'AMSTERDAM', '020-9753264', '25-MAR-90', 2745,
'35')
INSERT INTO WERKNEMERS VALUES ('35', 'JONGE', 'L.M.',
'DE','GRACHT 11', '6222 AG', 'MAASTRICHT', '043-3324785', '30JUN-95', 2118, '40')
INSERT INTO WERKNEMERS VALUES ('40', 'WILLEGEN', 'W.M.I,'VAN',
'BEUKENLAAN 12', '3088 LM', 'ROTTERDAM', '010-9865843', '01-MAR78', 4988, '99')
INSERT INTO WERKNEMERS VALUES ('45', 'JOBSE', 'G.C.',
NULL,'SINGEL 128', '3088 JP', ~ ‘ROTTERDAM', '010-9434785', '15JUL-86',4716,'40')
INSERT INTO WERKNEMERS VALUES ('47', 'JANSMA', 'L.M.',
NULL,'SINGEL 48', '1038 JG', 'AMSTERDAM', '020-4343210', '14APR-84', 2144, '35')
INSERT INTO WERKNEMERS VALUES ('48', 'BOS', 'P.', NULL,
'BEUKENLAAN 18', '3088 LM', 'ROTTERDAM', '010-9324321, '28-NOV87', 2357, '60')
INSERT INTO WERKNEMERS VALUES ('52', 'MAAS', 'M.C.', 'VAN DER',
'HOGEWEG 12', '3078 KL', 'ROTTERDAM', '010-3544567','12-DEC-86',
2849, '60')
INSERT INTO WERKNEMERS VALUES ('60', 'VLIET', 'P.C.', 'VAN
DER','ORANJEPLEIN 3',
'5655 BG', 'EINDHOVEN', '040-9143421', '01-APR-79',3848, '10')
INSERT INTO WERKNEMERS VALUES ('99', 'KONING', 'C.', 'DE',
'SINGEL 2', '3088 JK', 'ROTTERDAM', '010-6493214', '01-JAN80',5624, NULL)
Uit het voorafgaande wordt meteen duidelijk dat dit een nogal omslachtige wijze van het
invoeren van rijen is. Ook wordt duidelijk dat we bij de INSERT opdracht een aantal regels in
acht moeten nemen.
Hoofdstuk: Het werken met Gegevens (DML)
INSERT INTO WERKNEMERS VALUES ('50', 'LOOF', 'J.G.', 'DE',
'PLEIN 12', '3568 JL', 'UTRECHT', '030-9678765', '01-AUG-86',
4328, '10')
1
3
Database SQL
1 mei 2010
Waarden die ingevuld moeten worden in een alfanumerieke (CHAR) kolom moeten met enkele
aanhalingstekens worden omgeven.
Waarden in DATE kolommen moeten in het formaat DD-MON-YY en tussen aanhalingstekens
ingevoerd worden.
Numerieke (DECIMAL) kolommen moeten zonder aanhalingstekens ingevoerd worden.
Indien een bepaalde kolom niet ingevuld hoeft te worden gebruiken we NULL.
De kolomnamen hoeven we alleen op te geven als we een volgorde hanteren die afwijkt van
de volgorde van de kolommen zoals we die gebruikt hebben bij de CREATE TABLE opdracht of
als we bepaalde kolommen niet willen invullen zonder steeds NULL te gebruiken. We zouden
bijvoorbeeld alleen de CODE en de NAAM van een werknemer in kunnen voeren door op te
geven
INSERT INTO WERKNEMERS (CODE,NAAM)VALUES ('80','JANSEN')
VRAGEN EN OPDRACHTEN
Deze vragen en opdrachten kunnen alleen worden uitgeprobeerd indien U zelf de database
heeft geïnstalleerd.
VRAGEN
1. Wat zijn de belangrijkste aandachtspunten bij het gebruik van respectievelijk CHAR,
NUMBER, DATE en NULL waarden in een INSERT opdracht?
OPDRACHTEN
1. Geef de INSERT opdracht voor het invoeren van de volgende rijen in de WERKNEMERS
tabel
54 P.KATS BEUKENLAAN 1243057 HG ROTTERDAM geen telefoon, in dienst per 1 april
1989 met een nog onbekend salaris, de chef wordt 30.
2. Geef een zo kort mogelijke lNSERT opdracht om de volgende rij aan de tabel toe te
voegen
71 K.SCHREURS, telefoon 0118-493218, nadere gegevens nog
niet bekend.
SPECIAL
Om de opdrachten van de volgende hoofdstukken te maken is het nodig dat je de database
netjes aanmaakt en invult. Dit kan met de opdrachten die we hierboven geleerd hebben.
Echter, het is waarschijnlijk dat er dan veel type fouten gemaakt zullen worden waardoor het
een en ander niet bepaald soepel zal verlopen. Er is een bestand gemaakt SQLCURSUS.TXT dat
alle opdrachten die nodig zijn netjes bevat. Je kunt dit importeren in je databasemanagement
programma. Ga in de documentatie van het RDBM na hoe dat moet en importeer het dan.
Hoofdstuk: Het werken met Gegevens (DML)
63 K.5MULDERS P.C..HOOFTSTRAAT 56 1033 KL AMSTERDAM telefoon 020-6843762,
in dienst met ingang van heden, salaris wordt 3567, de chef wordt 40.
1
4
Database SQL
1 mei 2010
Fundamentele opdrachten met SELECT
Met behulp van de SELECT opdracht zijn we in staat om een aantal fundamentele handelingen
te verrichten. We kunnen namelijk bepaalde kolommen uit de tabel opvragen (PROJECTIE),
bepaalde rijen uit de tabel selecteren (SELECTIE) en we kunnen gegevens uit meerdere
tabellen gecombineerd opvragen (JOIN). We zullen deze aspecten één voor één behandelen.
Bij de PROJECTIE gaat het er om dat we slechts bepaalde kolommen uit de tabel willen zien. In
de WERKNEMERS tabel zou dit bijvoor- beeld de NAAM, de WOONPLAATS en het
TELEFOONNUMMER van de werknemers kunnen zijn. In een figuur weergegeven ziet
PROJECTIE er als volgt uit
In SQL kunnen we een projectie uitvoeren door in een SELECT instructie slechts bepaalde
kolommen te noemen
SELECT NAAM,WOONPLAATS, TELEFOON FROM WERKNEMERS
Deze SELECT opdracht geeft meteen aan wat er minimaal voor moet komen in een dergelijke
opdracht. In de eerste plaats geven we aan welke kolommen we willen zien (de PROJECTIE
dus) en in de tweede plaats geven we na FROM aan uit welke tabel de kolommen moeten
komen.
Bij de keuze van de kolommen hoeven we geen rekening te houden met de volgorde waarin
deze voorkomen in de tabel, iedere volgorde is toegestaan zolang we de namen van de
kolommen maar juist opgeven. Als we alle kolommen van een tabel willen zien dan gebruiken
we een * .
Hoofdstuk: Het werken met Gegevens (DML)
PROJECTIE
1
5
Database SQL
1 mei 2010
Dus:
SELECT*
FROM WERKNEMERS
geeft een overzicht van alle kolommen (en alle rijen) uit de WERKNEMERS tabel.
Als we de informatie uit de tabel willen beperken tot bepaalde rijen (een SELECTIE uitvoeren)
dan doen we dit door achter het FROM gedeelte een voorwaarde gedeelte aan te geven met
behulp van WHERE. We zouden bijvoorbeeld informatie op kunnen vragen uit onze
WERKNEMERS tabel door alleen de werknemers uit UTRECHT te selecteren
SELECT *
FROM WERKNEMERS
WHERE WOONPLAATS = 'UTRECHT'
PROJECTIE
Bij het gebruik van een alfanumerieke constante in het WHERE gedeelte moeten we gebruik
maken van enkele aanhalingstekens om aan te geven dat het hier om een alfanumerieke
constante gaat. Dit geldt ook voor een datum constante. Numerieke constanten kunnen
zonder de enkele aanhalingstekens gebruikt worden. Dus
WHERE <kolomnaam> = 'CHAR constante'
WHERE <kolomnaam> = 'DATE constante'
WHERE <kolomnaam> = NUMBER constante
Uiteraard zal het heel vaak zo zijn dat we een SELECTIE en een PROJECTIE willen combineren.
Bijvoorbeeld als we van alle werknemers uit UTRECHT alleen de NAAM, de WOONPLAATS en
het TELEFOONNUMMER willen zien
Hoofdstuk: Het werken met Gegevens (DML)
Hierdoor worden alle rijen uit de tabel waarvoor geldt dat in de kolom WOONPLAATS de
waarde UTRECHT is ingevuld op ons scherm afgedrukt. Er is dus een SELECTIE uitgevoerd.
1
6
Database SQL
1 mei 2010
SELECT NAAM, WOONPLAATS, TELEFOON
FROM WERKNEMERS
WHERE WOONPLAATS = 'UTRECHT'
Hierdoor worden de projectie en de selectie gecombineerd.
SELECT+PROJECTIE
In veel gevallen zal het bovendien zo zijn dat we gegevens uit meerdere tabellen willen
combineren. Het proces van normaliseren leidt tenslotte al vrij snel tot een ware explosie van
het aantal tabellen.
Dat maakt de kans op redundantie vrij klein maar maakt het opvragen van gegevens er in veel
gevallen niet gemakkelijker op.
Het is vooral van belang om te weten in welke volgorde een relationele database (conceptueel
gezien uiteraard) de verschillende onderdelen van een SELECT opdracht verwerkt.
Als we een eenvoudige SELECT opdracht zoals in het voorafgaande formuleren, zal de
verwerking als volgt plaatsvinden: eerst wordt de tabel (of worden de tabellen) in het
werkgeheugen geplaatst, met andere woorden: eerst wordt het FROM gedeelte uitgevoerd.
Vervolgens zal er een selectie van rijen die aan de voorwaarde voldoen plaatsvinden (het
WHERE gedeelte dus) en tenslotte zal gekeken worden welke kolommen uiteindelijk op het
beeldscherm moeten worden gezet. Dat betekent dus dat bij een combinatie van gegevens uit
meerdere tabellen deze tabellen allereerst in het werkgeheugen worden geplaatst zonder dat
er een vorm van selectie heeft plaatsgevonden.
Als we gegevens uit meerdere tabellen willen combineren geven we dit aan door in het FROM
gedeelte de tabellen, gescheiden door komma's, te noemen. Als we weer terugkeren naar de
tabellen uit het eerste hoofdstuk zouden we ons voor kunnen stellen dat we een overzicht
Hoofdstuk: Het werken met Gegevens (DML)
Bij het combineren van gegevens uit meerdere tabellen (de JOIN) zullen we ons een aantal
zaken moeten realiseren.
1
7
Database SQL
1 mei 2010
willen hebben van alle uren die verschillende werknemers hebben besteed aan verschillende
projecten.
We zouden dus kunnen formuleren
SELECT *
FROM WERKNEMERS, WERK
Als we deze opdracht daadwerkelijk zo in zouden geven, zouden we het volgende resultaat
verkrijgen:
WERKNEMERSNAAM
WERKNEMERSPROJECTUREN
CODE
CODE
CODE
Jan
Jan
Jan
Jan
Jan
Irene
Irene
Irene
Irene
Irene
Inge
Inge
Inge
Inge
Inge
Karel
Karel
Kare1
Karel
Karel
12
34
12
56
34
12
34
12
56
34
12
34
12
56
34
12
34
12
56
34
110
110
120
130
120
110
110
120
130
120
110
110
120
130
120
110
110
120
130
120
28
14
12
23
16
28
14
12
23
16
28
14
12
23
16
28
14
12
23
16
We hebben dan het product van twee verzamelingen op ons scherm oftewel een cartesiaans
product. Dit wil zeggen dat elke rij uit de (eerste) tabel WERKNEMERS is gekoppeld met elke rij
van de (tweede) tabel WERK. Dit kan uiteraard niet de bedoeling zijn. We willen immers
gegevens uit twee tabellen met elkaar combineren die ook op dezelfde entiteiten betrekking
hebben. Dit kunnen we bereiken door deze rijen uit dit cartesiaans product te selecteren. Als
we aan de voorgaande opdracht toevoegen dat we alleen geïnteresseerd zijn in gegevens die
betrekking hebben op één en dezelfde werknemer, kunnen we dat als volgt formuleren
SELECT *
FROM WERKNEMERS, WERK
WHERE WERKNEMERS.WERKNEMERSCODE = WERK.WERKNEMERSCODE
Als we de opdracht zo formuleren wordt er nog steeds een cartesiaans product gemaakt maar
dit wordt alleen in het werkgeheugen gebruikt door het RDBMS. Als dit cartesiaans product
dan in het werkgeheugen is geplaatst, worden hier vervolgens alle rijen uit geselecteerd die
voldoen aan de gestelde voorwaarde in het WHERE gedeelte. Hierbij valt op dat we de
werknemerscode vooraf laten gaan door de naam van de tabel, dus
WERKNEMERS.WERKNEMERSCODE=WERK.WERKNEMERSCODE
Hoofdstuk: Het werken met Gegevens (DML)
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
67
67
67
67
67
1
8
Database SQL
1 mei 2010
en niet
WERKNEMERSCODE=WERKNEMERSCODE
omdat de kolom met als kolomkop WERKNEMERSCODE nu in feite tweemaal voorkomt in het
werkgeheugen en we dus aan moeten geven welke van de twee we bedoelen.
Het toevoegen van deze voorwaarde heeft tot gevolg dat we nu op ons scherm het volgende
te zien krijgen
WERKNEMERS
CODE
NAAM
WERKNEMERS PROJECT
CODE
CODE
UREN
12
12
34
34
56
Jan
Jan
Irene
Irene
Inge
12
12
34
34
56
28
12
14
16
23
110
120
110
120
130
En dit lijkt meer op wat we eigenlijk wilden weten. Als we dan bovendien nog een projectie
toepassen, kunnen we een aardig resultaat verkrijgen
SELECT
WERKNEMERS.WERKNEMERSCODE, NAAM, PROJECTCODE, UREN
FROM WERKNEMERS, WERK
WHERE WERKNEMERS.WERKNEMERSCODE=WERK.WERKNEMERSCODE
WERKNEMERSCODE
NAAM
PROJECTCODE
UREN
12
12
34
34
56
Jan
Jan
Irene
Irene
Inge
110
120
110
120
130
28
12
14
16
23
Nu we de fundamentele bewerkingen besproken hebben zullen we in de volgende paragrafen
nog eens wat gedetailleerder ingaan op allerlei aspecten van de SELECT instructie.
Hoofdstuk: Het werken met Gegevens (DML)
Geeft dan
1
9
Database SQL
1 mei 2010
VRAGEN
1. Welk woord gebruiken we altijd om voorwaarden te stellen aan de te selecteren rijen?
2. Hoe kunnen we een opgevraagd overzicht van rijen beperken tot bepaalde kolommen?
3. Hoeveel rijen zouden we op ons scherm krijgen als we van drie tabellen van ieder 80
rijen op zouden vragen
SELECT * FROM TABEL1, TABEL2, TABEL3
4. Wanneer moeten we gebruik maken van de combinatie tabelnaam.kolomnaam?
5. Noem de 3 fundamentele opdrachten die we met een SELECT kunnen uitvoeren. Geef
een korte uitleg over de werking ervan.
6. Geef aan wat er achtereenvolgens wordt uitgevoerd bij de volgende opdracht
SELECT A.a,A.b,B.a
FROM A, B
WHERE A.a=B.a
OPDRACHTEN
1. Geef de opdracht in SQL om alle gegevens uit de tabel KLANTEN op te vragen.
Hoofdstuk: Het werken met Gegevens (DML)
2. Geef de opdracht in SQL om alle gegevens op te vragen uit de tabellen KLANTEN en
BESTELLINGEN. (Zowel in de tabel KLANTEN als in de tabel BESTELLINGEN is er een
kolom KLANTCODE.)
2
0
Database SQL
1 mei 2010
DISTINCT
Weer uitgaande van de tabel WERKNEMERS zoals we die in een eerdere paragraaf hebben
gevuld zouden we een overzicht op kunnen vragen van alle woonplaatsen waar onze
werknemers wonen, Met het voorgaande nog in gedachten is deze vraag vrij snel in SQL te
formuleren
SELECT WOONPLAATS
FROM WERKNEMERS
Als we dit zo invoeren krijgen we het volgende resultaat
WOONPLAATS
Ondanks het feit, dat dit het enige juiste antwoord op de gestelde vraag is, zal het in veel
gevallen eerder de bedoeling zijn om een overzicht te produceren van alle verschillende
woonplaatsen uit de tabel WERKNEMERS. Ook dit is in SQL vrij eenvoudig te formuleren
SELECT DISTINCT WOONPLAATS
FROM WERKNEMERS
Dit zal het volgende resultaat opleveren
WOONPLAATS
AMSTERDAM
LEEUWARDEN
VLISSINGEN
ROTTERDAM
EINDHOVEN
MAASTRICHT
Door het toevoegen van DISTINCT zorgen we er voor dat er geen dubbele waarden afgedrukt
worden. Let wel: DISTINCT geldt per rij, dus als we zouden invoeren
Hoofdstuk: Het werken met Gegevens (DML)
AMSTERDAM
AMSTERDAM
LEEUWARDEN
AMSTERDAM
VLISSINGEN
ROTTERDAM
AMSTERDAM
UTRECHT
EINDHOVEN
ROTTERDAM
AMSTERDAM
MAASTRICHT
ROTTERDAM
ROTTERDAM
AMSTERDAM
ROTTERDAM
UTRECHT
ROTTERDAM
EINDHOVEN
ROTTERDAM
2
1
Database SQL
1 mei 2010
SELECT DISTINCT NAAM,WOONPLAATS
FROM WERKNEMERS
levert dit vrijwel alle namen en alle woonplaatsen uit de WERKNEMERS tabel op omdat deze
steeds als combinatie wel eenmalig voorkomen. (Welke combinatie van naam en woonplaats
komt nu nog steeds te vervallen?)
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
1. Alle namen van alle werknemers.
2. Alle namen slechts eenmaal. genoemd. 11
3. Alle combinaties van WOONPLAATS en POSTCODE eenmaal genoemd.
4. Alle salarissen.
5. Alle salarissen slechts eenmaal genoemd.
Hoofdstuk: Het werken met Gegevens (DML)
6. Alle waarden uit de DATUM_IN_D kolom.
2
2
Database SQL
1 mei 2010
Rekenen met kolominhouden
Het is ook mogelijk om berekeningen uit te voeren met te selecteren kolommen alvorens ze op
het scherm af te drukken. We gebruiken daarvoor {involgorde van prioriteit) de operatoren
* en / vermenigvuldigen en delen
+ en -optellen en aftrekken
De prioriteit is uiteraard te beïnvloeden door gebruik te maken van haakjes ().
We mogen deze operatoren gebruiken om berekeningen uit te voeren met een kolom en een
constante, twee kolommen en {hoewel dat niet zo erg nuttig is) twee constanten.
Als we naast het maandsalaris ook het jaarsalaris van de WERKNEMERS willen zien kunnen we
dat opvragen door
SELECT NAAM,SAL,SAL * 12
FROM WERKNEMERS
BUCK
MAAS
MERK
WILLEGEN
KONING
LOOF
VLIET
NEVE
JONGE
JOBSE
VLIET
BRASSER
BOS
MAAS
JANSMA
PAREE
ZEEUW
LIEVENSE
UMBGROVE
GEEL
4567
3332
4749
4988
5624
4328
3848
3148
2118
4716
2638
2149
2357
2849
2144
2745
2981
3148
2988
1921
54804
39984
56988
59856
67488
51936
46176
37776
25416
56592
31656
25788
28284
34188
25728
32940
35772
37776
35856
23052
Als we nog een tweede numerieke kolom in onze tabel zouden hebben (COMMISSIE
bijvoorbeeld) zouden we een overzicht kunnen krijgen door
SELECT NAAM,SAL,COMMISSIE,SAL+COMMISSIE
FROM WERKNEMERS
We moeten hierbij wel waarschuwen voor het feit dat een optelling waar een NULL waarde bij
betrokken is ook de waarde NULL oplevert. Immers: als we iets onbekends ergens bij optellen,
kunnen we alleen maar aangeven dat dit als uitkomst iets op zal leveren dat ook onbekend is.
Met andere woorden: alle WERKNEMERS zonder COMMISSIE krijgen de waarde NULL als
Hoofdstuk: Het werken met Gegevens (DML)
Dat geeft ons het volgende overzicht .
NAAM
SAL SAL * 12
2
3
Database SQL
1 mei 2010
uitkomst in de laatste kolom van bovenstaand overzicht (Met de functie NVL(<kolomnaam>,O)
die iedere NULL waarde omzet in het getal 0, kunnen we dit in sommige RDBMS-en oplossen.)
Vaak leiden rekenkundige bewerkingen tot lelijke en lange kolomkoppen in onze overzichten.
Dit kunnen we oplossen door in het SELECT gedeelte na de rekenkundige formulering een
spatie, gevolgd door een alternatieve kolomkop op te geven.
SELECT NAAM,SAL PER_MAAND,SAL *12 PERJAAR
FROM WERKNEMERS
Dit ziet er meteen al wat fraaier uit dan de formulering zonder alternatieve kolomkoppen.
NAAM
PER_MAAND PER_JAAR
BUCK
MAAS
MERK
WILLEGEN
KONING
LOOF
VLIET
NEVE
JONGE
JOBSE
VLIET
BRASSER
BOS
MAAS
JANSMA
PAREE
ZEEUW
LIEVENSE
UMBGROVE
GEEL
4567
3332
4749
4988
5624
4328
3848
3148
2118
4716
2638
2149
2357
2849
2144
2745
2981
3148
2988
1921
54804
39984
56988
59856
67488
51936
46176
37776
25416
56592
31656
25788
28284
34188
25728
32940
35772
37776
35856
23052
1. De naam en het weeksalaris (salaris maal 3 gedeeld door 13) onder de kolomkop
WEEKSAL van iedere werknemer.
2. De naam en het salaris inclusief een op handen zijnde salarisverhoging van 2 % onder
de kolomkop NWSAL.
3. De naam en het totaal aan salaris inclusief de in vorige paragraaf genoemde kolom
COMMISSIE per jaar van alle werknemers.
4. De naam en de bonus (onder de kolomkop BONUS) voor iedere werknemer. Oe bonus
wordt berekend door het aantal jaren in dienst (af te leiden uit de numerieke kolom
JAAR_IN_O) te vermenigvuldigen met 1 % van het salaris.
Hoofdstuk: Het werken met Gegevens (DML)
OPDRACHTEN
Geef een opdracht in SOL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
2
4
Database SQL
1 mei 2010
Operatoren in het WHERE gedeelte.
Tot nu toe hebben we alleen nog maar gebruik gemaakt van het = teken om voorwaarden te
stellen aan de te selecteren rijen. Uiteraard hebben we ook andere operatoren tot onze
beschikking. We kunnen gebruik maken van het < of het > teken om andersoortige
voorwaarden te stellen. Bijvoorbeeld alle namen van werknemers met een salaris van meer
dan 3000 Euro:
SELECT NAAM
FROMWERKNEMERS
WHERE SAL > 3000
Het < of > teken kunnen we ook gebruiken om twee kolommen met elkaar te vergelijken in
plaats van het vergelijken van een kolom met een constante zoals in het voorgaande
voorbeeld. Hoewel we niet kunnen zeggen dat dit nu zo'n voor de hand liggende vraag is,
zouden we kunnen vragen om de namen van alle werknemers met een code die groter is dan
de code van hun chef
Ondanks het feit dat deze opdracht in dit geval het juiste antwoord oplevert moeten we toch
waarschuwen voor het gebruik van het < en het > teken bij alfanumerieke ((HAR) kolommen.
Omdat de ASCII tabel (gelukkig) onderscheid maakt tussen hoofdletters en kleine letters, kan
dit nog wel eens tot problemen leiden.
Bovendien wordt er nu lexicografisch vergeleken; dat wil zeggen dat eerst gekeken zal worden
naar het eerste teken, als dit gelijk is wordt gekeken naar het volgende teken enzovoort. Dat is
een prima uitgangspunt voor het indelen van bijvoorbeeld het telefoonboek maar het strookt
niet met ons numerieke positiestelsel omdat daarbij de waarde 80 kleiner zal zijn dan 100
terwijl bij een lexicografische vergelijking we bij het eerste teken al constateren dat de 8 in de
ASCII tabel een hogere waarde heeft dan de 1 en dat daarmee de waarde 80 dus groter is dan
de waarde 100! Bij numerieke (NUMBER) kolommen leveren deze operatoren vanzelfsprekend
geen enkel probleem op.
Als we het < of > teken willen combineren met het = teken, doen we dit eenvoudigweg door ze
achter elkaar te plaatsen
SELECT NAAM
FROM WERKNEMERS
WHERE SAL >= 3000
De volgorde van deze twee tekens staat vast, met andere woorden het is niet toegestaan om
=> te gebruiken in plaats van >= .
De 'ongelijk aan' operator wordt_in de verschillende RDBMS-en op verschillende wijzen
voorgesteld. De meest voorkomende vormen zijn <>, != en ^= . In dit boek zullen we het !=
teken gebruiken.
Zo kunnen we dus alle namen van werknemers die niet in ROTTERDAM wonen opvragen door
Hoofdstuk: Het werken met Gegevens (DML)
SELECT NAAM
FROM WERKNEMERS
WHERE CODE > CHEF
2
5
Database SQL
1 mei 2010
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS != 'ROTTERDAM'
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
1. Alle namen en telefoonnummers van werknemers uit AMSTERDAM .
2. Alle namen, adressen en woonplaatsen van werknemers die minder verdienen dan
2600 Euro.
3. Alle namen van werknemers die een naam hebben die in het telefoonboek na de naam
JANSEN komt.
4. De woonplaatsen van alle werknemers die niet voor chef 40 werken.
5. De salarissen van alle werknemers die uit MAASTRICHT komen.
6. De namen en adressen van alle werknemers die na 1 januari 1988 in dienst zijn
gekomen.
7. De namen en 10% van het salaris (onder de kolomkop SAL_DEEL) van alle werknemers
die niet in ROTTERDAM wonen.
8. Alle woonplaatsen (zonder dubbelen) van werknemers die voor 31 december 1987 in
dienst zijn gekomen.
9. Alle namen en telefoonnummers van werknemers met een postcode boven 3088.
Hoofdstuk: Het werken met Gegevens (DML)
10. Alle namen van werknemers die niet meer dan 3500 Euro verdienen .
2
6
Database SQL
1 mei 2010
Het combineren van operatoren met AND en OR
Vaak zullen we niet kunnen volstaan met het stellen van één voorwaarde maar zullen we
meerdere voorwaarden willen combineren.
In SQL kunnen we dan gebruik maken van AND en OR.
Zo zouden we ons af kunnen vragen welke werknemers uit ROTTERDAM aangenomen zijn voor
1 januari 1987.
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM' AND
DATUM_IN_D < '01-JAN-87'
Dat is een geheel andere vraag dan de vraag welke werknemers uit ROTTERDAM komen OF
voor 1 januari 1987 zijn aangenomen
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM' OR
DATUM_IN_D < '01-JAN-87'
Bij het gecombineerd gebruiken van de AND en OR operatoren moeten we enige
voorzichtigheid in acht nemen.
Stel dat we een overzicht willen van alle werknemers die voor 1 januari 1987 in dienst zijn
gekomen en uit ROTTERDAM of uit UTRECHT komen, dan levert de opdracht
niet het juiste antwoord op. Zo geformuleerd vragen we immers om de namen van alle
werknemers die voor 1 januari 1987 in dienst zijn gekomen en in ROTTERDAM wonen,
aangevuld met de namen van werknemers die in UTRECHT wonen.
We kunnen dit probleem op twee manieren oplossen
SELECT NAAM
FROM WERKNEMERS
WHERE DATUM_IN_D < '0l-JAN-87' AND
WOONPLAATS = 'ROTTERDAM! OR
DATUM_IN_D < '01-JAN-87'AND
WOONPLAATS = 'UTRECHT'
of
SELECT NAAM
FROM WERKNEMERS
WHERE DATUM_IN_D < '01-JAN-87! AND
(WOONPLAATS = 'ROTTERDAM' OR
WOONPLAATS = !UTRECHT')
In veel gevallen zullen we, vanwege de kortere formulering, voor de tweede oplossing kiezen:
het gebruik van haakjes.
Hoofdstuk: Het werken met Gegevens (DML)
SELECT NAAM
FROM WERKNEMERS
WHERE DATUM_IN_D < '01-JAN-87' AND
WOONPLAATS = 'ROTTERDAM' OR
WOONPLAATS = 'UTRECHT'
2
7
Database SQL
1 mei 2010
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
1. De namen van alle werknemers die in UTRECHT wonen en een salaris hebben van
meer dan 3000 Euro.
2. De namen van alle werknemers met een postcode tussen (inclusief de grens waarden)
3050 en 3100.
3. De namen van alle werknemers met een salaris beneden de 2500 Euro of een salaris
boven de 4000 Euro.
4. De namen van alle werknemers met een salaris tussen de 2500 en 4000 Euro.
5. De namen en woonplaatsen van alle werknemers met als woonplaats ROTTERDAM en
als postcode 3088 JP of 3085 HG.
6. De namen van alle werknemers met als chef chef nummer 99 of chef nummer 20 en
als salaris een salaris van meer dan 4000 Euro of minder dan 2500 Euro.
7. De code en de naam van alle werknemers die niet voor chef 40 werken of een salaris
tussen 3000 en 4000 Euro hebben.
8. De naam, woonplaats en datum van indiensttreding van alle werknemers die tussen 1
januari 1987 en 31 december 1988 in dienst zijn gekomen of in AMSTERDAM wonen.
9. De namen van alle werknemers die in UTRECHT wonen of een salaris hebben tussen
2100 en 3000 Euro.
Hoofdstuk: Het werken met Gegevens (DML)
10. De namen van alle werknemers die niet in ROTTERDAM of in UTRECHT wonen, niet
voor chef 99 werken en een salaris tussen 2000 en 3000 Euro hebben.
2
8
Database SQL
1 mei 2010
Het gebruik van NOT
Naast de != operator kunnen we ook gebruik maken van NOT om een ontkenning te
formuleren. Dit wil echter niet zeggen dat de NOT operator een zelfde werking heeft als de !=
operator!!
We zouden ons bijvoorbeeld af kunnen vragen welke werknemers niet in ROTTERDAM of
AMSTERDAM wonen.
Als we dat proberen te formuleren als
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS != 'ROTTERDAM' OR
WOONPLAATS != 'AMSTERDAM'
hebben we weliswaar een ontkennende operator in een met OR verbonden stel voorwaarden
geplaatst maar deze opdracht zal alle werknemers uit de WERKNEMERS tabel op ons scherm
zetten omdat voor alle werknemers geldt dat hun woonplaats of ongelijk aan AMSTERDAM of
ongelijk aan ROTTERDAM is; dat geldt zelfs voor ROTTERDAM en AMSTERDAM zelf.
De vraag is wel te beantwoorden met
SELECT NAAM
FROM WERKNEMERS
WHERE NOT (WOONPLAATS = 'ROTTERDAM' OR
WOONPLAATS = 'AMSTERDAM')
De NOT operator heeft een hogere prioriteit dan de AND operator, die op zijn beurt weer
boven de OR operator gaat.
A
B
NOT A
A AND B
A OR B
WAAR
ONWAAR
ONWAAR
WAAR
WAAR
WAAR
ONWAAR
ONWAAR
ONWAAR
WAAR
WAAR
ONBEKEND
ONWAAR
ONBEKEND
WAAR
ONWAAR
WAAR
WAAR
ONWAAR
WAAR
ONWAAR
ONWAAR
WAAR
ONWAAR
ONWAAR
ONWAAR
ONBEKEND
WAAR
ONBEKEND
ONBEKEND
ONBEKEND
WAAR
ONBEKEND
ONBEKEND
WAAR
ONBEKEND
ONWAAR
ONBEKEND
ONWAAR
ONBEKEND
ONBEKEND
ONBEKEND
ONBEKEND
ONBEKEND
ONBEKEND
Hoofdstuk: Het werken met Gegevens (DML)
In de volgende evaluatietabel kunnen we zien hoe de NOT, AND en OR operatoren gebruikt
moeten worden. Met ONBEKEND wordt bedoeld dat de waarde niet is ingevuld oftewel NULL
is.
2
9
Database SQL
1 mei 2010
VRAGEN EN OPDRACHTEN
VRAGEN
Wat is het verschil tussen de volgende twee opdrachten
SELECT NAAM
FROM WERKNEMERS
WHERE CHEF != '99' OR CHEF = '40'
SELECT NAAM
FROM WERKNEMERS
WHERE NOT (CHEF = '99' OR (CHEF = '40')
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel.
1. Alle namen van werknemers die niet voor 1 april 1988 in dienst zijn gekomen.
2. Alle namen van werknemers die een salaris hebben dat niet tussen de 2500 en 3500
Euro ligt.
3. Alle namen van de werknemers die niet in MAASTRICHT of UTRECHT wonen en niet
voor chef 40 of chef 99 werken.
4. Alle namen van werknemers die niet als chef ‘chef 99’ of ‘chef 20’ hebben en een
salaris van meer dan 4000 of minder dan 2000 Euro hebben.
Hoofdstuk: Het werken met Gegevens (DML)
5. Alle namen van werknemers die niet in 1988 in dienst zijn gekomen.
3
0
Database SQL
1 mei 2010
BETWEEN
Als we op zoek zijn naar rijen uit de tabel waarbij de waarde van een kolom tussen twee
waarden ligt, zouden we gebruik kunnen maken van >= en <= gekoppeld met AND. Zo zouden
we de namen van alle werknemers met een salaris tussen de 2500 en de 3000 Euro op kunnen
vragen met
SELECT NAAM
FROM WERKNEMERS
WHERE SAL >= 2500 AND
SAL <=3000
Voor dit soort vragen kent SQL de BETWEEN operator:
SELECT NAAM
FROM WERKNEMERS
WHERE SAL BETWEEN 2500 AND 3000
Deze formulering is overzichtelijker dan de voorgaande met de >= en <= tekens. Wel moeten
we ons realiseren dat BETWEEN ...AND ...niet letterlijk TUSSEN ...EN ...betekent omdat ook de
grenswaarden meegenomen worden. Uiteraard kunnen we dit oplossen. Door te formuleren
BETWEEN 2501 AND 2999 of iéts dergelijks. Met behulp van de BETWEEN operator in
combinatie met NOT kunnen we uiteraard ook vragen stellen als: Welke werknemers hebben
een salaris dat niet tussen de 2500 en 3000 Euro ligt.
SELECT NAAM
FROM WERKNEMERS
WHERE SAL NOT BETWEEN 2500 AND3000
Geformuleerd zonder gebruik te maken van de BETWEEN operator zouden we moeten stellen:
Merk op dat in dit geval gebruik gemaakt wordt van OR om de twee voorwaarden aan elkaar
te koppelen.
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
1. Alle namen van werknemers die in dienst gekomen zijn tussen 1 januari 1985 en 31
december 1986.
2. Alle namen van werknemers die een salaris tussen 3500 en 4500 Euro hebben en in
ROTTERDAM wonen.
3. Alle namen van werknemers die een salaris hebben dat niet tussen 4000 en 5000 Euro
ligt of in UTRECHT wonen.
Hoofdstuk: Het werken met Gegevens (DML)
SELECT NAAM
FROM WERKNEMERS
WHERE SAL < 2500 OR
SAL > 3000
3
1
Database SQL
1 mei 2010
IN
Vaak zullen we op zoek zijn naar rijen waarbij een bepaalde kolom gelijk moet zijn aan een
aantal mogelijke waarden. We zouden bijvoorbeeld op zoek kunnen zijn naar alle werknemers
die in ROTTERDAM, AMSTERDAM of UTRECHT wonen. We kunnen dit vrij eenvoudig
formuleren door te vragen
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM' OR
WOONPLAATS = 'AMSTERDAM' OR
WOONPLAATS =}UTRECHT'
Het zal duidelijk zijn dat dit, zeker bij herhaald gebruik van dit soort samengestelde
voorwaarden, niet erg prettig werkt. Gelukkig hebben we de beschikking over de IN operator
waarmee we dergelijke vragen vrij eenvoudig op kunnen lossen
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS IN ('ROTTERDAM', 'AMSTERDAM','UTRECHT')
Met behulp van de IN operator kunnen we dus aan een kolom de voorwaarde stellen dat de
daar ingevulde waarde voor moet komen in een reeks van waarden die we, gescheiden door
komma's, tussen haakjes plaatsen na de IN operator.
Het spreekt voor zich dat we ook hier weer gebruik kunnen maken
van NOT om de voorwaarde ontkennend te maken.
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
2. De namen van werknemers met als code 10, 12 of 60 en een salaris tussen de 3500 en
5000 Euro.
3. De namen van werknemers die in UTRECHT of MAASTRICHT wonen en niet voor chef
99 of chef 40 werken
4. De namen van werknemers die meer dan 4000 Euro verdienen en in ROTTERDAM,
UTRECHT of MAASTRICHT wonen.
5. De namen van werknemers die niet in AMSTERDAM of UTRECHT wonen en in 1988 in
dienst zijn gekomen.
Hoofdstuk: Het werken met Gegevens (DML)
1. De namen van werknemers die voor chef 99, 40 of 30 werken.
3
2
Database SQL
1 mei 2010
LIKE
In sommige gevallen krijgen we te maken met voorwaarden die niet zo gemakkelijk te vangen
zijn in vraagstellingen zoals we die tot nu toe behandeld hebben. Met name geldt dit voor
vragen als: welke werknemers hebben een naam die begint met een 'V' of hebben een naam
met als derde letter een' A ' ? Voor dit soort vragen maken we gebruik van de LIKE operator
tezamen met de % en _ tekens.
Het procentteken (%) staat daarbij voor een willekeurige rij lettertekens terwijl het
onderstrepingsteken (_) staat voor een willekeurig letterteken. Bovenstaande vragen zijn dus
als volgt te formuleren:
SELECT NAAM
FROM WERKNEMERS
WHERE NAAM LIKE 'V%'
en
SELECT NAAM
FROM WERKNEMERS
WHERE NAAM LIKE '__A%'
Zo kunnen we dus ook de voorwaarde stellen dat de naam niet mag bestaan uit 5 lettertekens
door gebruik te maken van:
SELECT NAAM
FROM WERKNEMERS
WHERE NAAM NOT LIKE '_____'
1. Alle namen van werknemers met een naam beginnend met een B.
2. Alle namen van werknemers met een naam met als vijfde letter een E.
3. Alle namen van werknemers met een naam waar op de laatste plaats niet de letter E
staat.
4. Alle namen van werknemers met een naam van vier letters.
5. Alle woonplaatsen die niet eindigen op 'DAM'.
6. Alle namen van werknemers die in een 'LAAN' wonen (dat wil zeggen: niet in een
straat of plein of iets dergelijks).
7. Alle namen van werknemers met een postcode die begint met 30.
Hoofdstuk: Het werken met Gegevens (DML)
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
3
3
Database SQL
1 mei 2010
NULL
Als we op zoek zijn naar rijen in een tabel waarbij we als voorwaarde stellen dat een bepaalde
ko1om oningevuld moet zijn, met andere woorden dat deze kolom de waarde onbekend
oftewel NULL moet hebben, maken we gebruik van de ISNULL voorwaarde.
We zouden bijvoorbeeld kunnen kijken welke werknemers geen CHEF hebben door in te
voeren:
SELECT NAAM
FROM WERKNEMERS II
WHERE CHEF IS NULL
We dienen ons (zoals reeds opgemerkt) goed te realiseren dat de NULL waarde betekent
onbekend wat dus niet hetzelfde is als de waarde 0. Zo zal de opdracht
SELECT NAAM
FROMWERKNEMERS
WHERE CHEF <= '99'
niet alle rijen uit de tabel WERKNEMERS selecteren omdat de niet ingevulde waarde bij
werknemer DE KONING niet kleiner is dan de waarde '99' maar eenvoudigweg ONBEKEND is
zodat deze vraagstelling slechts 19 van de 20 rijen oplevert.
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
1. De namen van werknemers die geen voorvoegsels bij hun naam hebben.
3. De namen van werknemers waarvan het telefoonnummer onbekend is.
4. De namen van werknemers waarvan het salaris of de chef (nog) riet bekend is.
5. De namen van werknemers waarvan het salaris (nog) niet bekend is maar die wel een
chef hebben.
Hoofdstuk: Het werken met Gegevens (DML)
2. De namen van werknemers die wel een chef hebben.
3
4
Database SQL
1 mei 2010
ORDER BY
Als we het resultaat van een bepaalde vraagstelling ook nog willen
sorteren, kunnen we gebruik maken van de opdracht ORDER BY.
Deze optie wordt altijd als laatste onderdeel van een SELECT opdracht opgegeven. Eigenlijk is
het niets anders dan een laatste bewerking van de geselecteerde rijen. Als we de namen van
alle werknemers uit ROTTERDAM, alfabetisch geordend, willen zien gebruiken we
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS ='ROTTERDAM'
ORDER BY NAAM
Het is ook mogelijk om op meer dan één waarde te ordenen:
SELECT NAAM
FROM WERKNEMERS
ORDER BY NAAM,SAL
Dit zal de namen van de werknemers geordend op naam (en bij gelijke namen op salaris)
weergeven. Daarmee meteen aangevend dat het ordenen dus zowel op alfanumerieke als
numerieké kolommen kan gebeuren. Dat geldt overigens ook voor datumkolommen.
Uiteraard geldt ook hier weer het gevaar van een lexicografische ordening van alfanumerieke
waarden die uit cijfers bestaan.
Een apart probleem vormen de NULL waarden bij het gebruik van ORDER BY. Bij verschillende
RDBMS-en wordt hier verschillend mee omgegaan. In ORACLE bijvoorbeeld komen de NULL
waarden altijd eerst, of er nu oplopend of aflopend wordt gesorteerd. Dat betekent dus dat bij
de opdracht
SELECT NAAM
FROM WERKNEMERS
ORDER BY CHEF
werknemer DE KONJNG als eerste op het scherm zal komen, ook als we opgeven
SELECT NAAM
FROM WERKNEMERS
ORDER BY CHEF DESC
Hoofdstuk: Het werken met Gegevens (DML)
We kunnen deze volgorde ook omdraaien (van Z naar A laten sorteren) door achter ORDER BY
NAAM DESC (van DESCENDING) op te geven. Om de sorteervolgorde van A naar Z te laten
lopen zouden we ASC (van ASCENDING) op kunnen geven maar aangezien dat de default (door
het systeem gekozen) volgorde is doen we dit meestal niet.
3
5
Database SQL
1 mei 2010
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
1. De namen van alle werknemers oplopend geordend naar hun code.
2. Alle woonplaatsen slechts eenmaal genoemd, alfabetisch aflopend geordend.
3. Alle namen van alle werknemers, aflopend geordend naar salaris.
4. Alle namen van alle werknemers, geordend naar datum van indiensttreding.
Hoofdstuk: Het werken met Gegevens (DML)
5. Alle namen van alle werknemers met een salaris tussen 2000 en 4000 Euro, geordend
naar woonplaats en per woonplaats naar naam.
3
6
Database SQL
1 mei 2010
GROUP BY, HAVING, COUNT, AVG, SUM, MAX en MIN
De GROUP BY instructie zorgt er voor dat bepaalde rijen gegroepeerd worden zodat we
berekeningen en selecties kunnen uitvoeren met groepen rijen in plaats van individuele rijen.
We zullen ons eerst eens af gaan vragen hoe de GROUP BV instructie nu eigenlijk werkt.
Omdat de GROUP BV instructie onmiddellijk na de WHERE component wordt uitgevoerd
kunnen we ons het volgende voorstellen.
We zouden van de rijen in de tabel WERKNEMERS een aantal gegroepeerde rijen kunnen
maken, bijvoorbeeld per WOONPLAATS. Om dit te bereiken zouden we dus na de WHERE
component toe moeten voegen
GROUP BY WOONPLAATS
Het gevolg hiervan is dat er als het ware per woonplaats een aantal rijen tot één rij
gecombineerd worden. Dit betekent automatisch dat we met een opdracht als
SELECT NAAM,WOONPLAATS FROM WERKNEMERS
GROUP BY WOONPLAATS
NAAM
WOONPLAATS
BUCK,BRASSER, VLIET,NEVE,PAREE,JANSMA
UMBGROVE
LIEVENSE
MMS,MERK, WILLEGEN,JOBSE,BOS, MMS,KONING
GEEL,LOOF
ZEEUW, VLIET
JONGE
AMSTERDAM
VLISSINGEN
LEEUWARDEN
ROTTERDAM
UTRECHT
EINDHOVEN
MAASTRICHT
Het op het scherm zetten van de woonplaatsen is niet zo'n probleem. Maar wat moet er nu
afgedrukt worden bij NAAM ?? Dit kan dus niet
We zullen er dus rekening mee moeten houden dat we bij de GROUP BY optie alleen vragen
om die kolommen waar op gegroepeerd wordt of gebruik maken van de zogenaamde GROUP
FUNCTIES. De GROUP FUNCTIES die we in iedere SQL implementatie aan kunnen treffen zijn:
COUNT
AVG
SUM
MAX
MIN
Hoofdstuk: Het werken met Gegevens (DML)
niets kunnen beginnen. Immers er wordt gegroepeerd, dus eigenlijk ontstaat er iets als het
volgende
3
7
Database SQL
1 mei 2010
Count
We zouden ons bijvoorbeeld af kunnen vragen hoeveel werknemers er in iedere plaats wonen
SELECT WOONPLAATS,COUNT(*)
FROM WERKNEMERS
GROUP BV WOONPLAATS
Dit heeft tot gevolg dat er bij het tussen resultaat gekeken zal worden hoeveel rijen er in
iedere groep zitten (we geven door het* teken aan dat het aantal rijen geteld moet worden)
en dat de uitkomst op het scherm afgedrukt zal worden. Dat geeft dan het volgende resultaat
WOONPLAATS
AMSTERDAM
VLISSINGEN
LEEUWARDEN
ROTTERDAM
UTRECHT
EINDHOVEN
MAASTRICHT
COUNT(*)
6
1
1
7
2
2
1
We kunnen COUNT ook gebruiken om alleen het aantal ingevulde waarden per kolom te
tellen. We gebruiken dan de kolomnaam in plaats van het * teken.
SELECT WOONPLAATS,COUNT(CHEF)
FROM WERKNEMERS
GROUP BY WOONPLAATS
Geeft ons per woonplaats het aantal werknemers met een CHEF. Met andere woorden:
ROTTERDAM zal dan door het getal 6 in plaats van 7 worden vergezeld omdat de in
ROTTERDAM woonachtige DE KONING geen CHEF heeft en in dit geval dus niet meetelt.
SELECT WOONPLAATS,AVG(SAL)
FROM WERKNEMERS
GROUP BY WOONPLAATS I
AVG berekent het gemiddelde en houdt daarbij rekening met het feit dat niet ingevulde
kolommen (NULL waarden) genegeerd moeten worden. Ook is het in sommige RDBMS-en
mogelijk om DISTINCT te gebruiken zodat het gemiddelde van alle verschillende waarden
berekend wordt. (AVG(DISTINCT(GETAL)) bij de waarden 1,1,1 en 3 levert dan 2 op in plaats
van 1,5.)
SUM
De SUM functie totaliseert een numerieke kolom
SELECT WOONPLAATS,SUM(SAL)
FROM WERKNEMERS
GROUP BY WOONPLAATS
levert per WOONPLAATS het totaal aan salarissen op.
Hoofdstuk: Het werken met Gegevens (DML)
AVG
De functie AVG wordt gebruikt om het gemiddelde (AVERAGE) van een bepaalde groepte
berekenen. Zo zouden we het gemiddelde salaris per woonplaats op kunnen vragen door
3
8
Database SQL
1 mei 2010
MAX en MIN
De MAX en de MIN functie berekenen respectievelijk de hoogste en de laagste waarde binnen
een groep.
SELECT WOONPLAATS,MAX(SAL)
FROM WERKNEMERS
GROUP BY WOONPLAATS
geeft ons per woonplaats het hoogste salaris, terwijl
SELECT WOONPLAATS,MIN(SAL)
FROM WERKNEMERS
GROUP BY WOONPLAATS
ons het laagste salaris per woonplaats oplevert.
Het is ook toegestaan om te groeperen op meerdere kolommen. Als we bijvoorbeeld een
overzicht willen hebben van het aantal werknemers per woonplaats en binnen woonplaats per
postcode gebied kunnen we opvragen
SELECT WOONPLAATS,POSTCODE,COUNT(*)
FROM WERKNEMERS
GROUP BY WOONPLAATS,POSTCODE
De GROUP FUNCTIES kunnen ook gebruikt worden zonder het GROUP BY gedeelte. Dat komt
met name van pas als we zo’n functie willen gebruiken voor een hele tabel. We beschouwen
dan in feite de gehele tabel als een groep.
De opdracht
geeft ons een overzicht van achtereenvolgens het aantal rijen, het gemiddelde salaris, het
totaalbedrag van alle salarissen, het hoogste salaris en het laagste salaris van alle werknemers
in de WERKNEMERS tabel.
Aan gegroepeerde rijen kunnen we ook typische groepsvoorwaarden' stellen. Zo zouden we
bijvoorbeeld het aantal werknemers per woonplaats op kunnen vragen maar daar tegelijkertijd
aan toe kunnen voegen dat we in het overzicht alleen die woonplaatsen willen zien die meer
dan 5 werknemers hebben.
Hoofdstuk: Het werken met Gegevens (DML)
SELECT COUNT(*), AVG(SAL), SUM(SAL), MAX(SAL), MIN(SAL)
FROM WERKNEMERS
3
9
Database SQL
1 mei 2010
HAVING
Dan moeten we gebruik maken van HAVING.
SELECT WOONPLAATS,COUNT(*)
FROM WERKNEMERS
GROUP BY WOONPLAATS
HAVING COUNT(*) > 5
Dat geeft dan het volgende resultaat
WOONPLAATS
AMSTERDAM
ROTTERDAM
COUNT(*)
6
7
Het RDBMS zal nog steeds de eerste stappen uit de eerder genoemde opdracht zonder
HAVING uitvoeren, maar zal voordat het resultaat op het scherm wordt afgedrukt als het ware
nog die groepen uitfilteren die niet aan de, in het HAVING gedeelte gestelde, voorwaarde
voldoen.
Uiteraard is het toegestaan om in een SELECT opdracht met een GROUP BY voorwaarden te
stellen met behulp van een WHERE gedeelte. We moeten ons dan goed realiseren dat het
WHERE gedeelte eerder wordt uitgevoerd dan het GROUP BY gedeelte, zodat er groepen
gemaakt worden van alleen die rijen die aan de, in het WHERE gedeelte gestelde, voorwaarde
voldoen.
SELECT WOONPLAATS,AVG(SAL)
FROM WERKNEMERS
WHERE SAL>2500
GROUP BY WOONPLAATS
Apart aandacht verdient nog de waarde NULL oftewel de niet ingevulde waarde. Bedenk dat er
dus ook een groep kan ontstaan van records waarbij in de kolom die we in het GROUP BY
gedeelte gebruiken, niets is ingevuld.
Hoofdstuk: Het werken met Gegevens (DML)
Geeft ons per woonplaats het gemiddelde salaris van iedereen die meer dan 2500 Euro
verdient.
4
0
Database SQL
1 mei 2010
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de WERKNEMERS
tabel
Hoofdstuk: Het werken met Gegevens (DML)
1. Per woonplaats het gemiddelde salaris.
2. Het hoogste salaris dat per chef door een ondergeschikte verdiend wordt.
3. Idem als vraag 2 maar nu tellen alleen de werknemers die minder dan 4000 Euro
verdienen mee.
4. Het aantal werknemers dat onder iedere chef valt, chefs met minder dan twee
ondergeschikten moeten niet vermeld worden
5. Die woonplaatsen waar het gemiddelde salaris hoger is dan 2600 Euro.
4
1
Database SQL
1 mei 2010
HET RAADPLEGEN VAN MEERDERE TABELLEN
De verschillende mogelijkheden
Voor het raadplegen van gegevens uit meerdere tabellen hebben we de beschikking over een
aantal mogelijkheden. In de eerste plaats kunnen we natuurlijk gebruik maken van de eerder
besproken JOIN.
Naast de JOIN kennen we de SUBQUERY, die ook gebruikt kan worden voor het raadplegen van
gegevens uit één tabel maar eigenlijk het best tot zijn recht komt bij het gebruik van meerdere
tabellen. Bovendien zullen we zien dat we in veel gevallen zowel een JOIN als een SUBQUERY
kunnen gebruiken om een antwoord te vinden op een bepaalde vraag.
Tenslotte hebben we dan ook de UNION, MINUS EN INTERSECT mogelijkheden. Met deze
opdrachten doen we eigenlijk niets anders dan het bewerken van de resultaten van twee
SELECT opdrachten. We zullen in paragraaf 4.4 stilstaan bij deze opdrachten.
Voordat we deze verschillende mogelijkheden gaan bespreken zullen we nog een drietal
tabellen aan onze database gaan toevoegen. We zullen volstaan met het geven van de CREATE
opdrachten waarmee deze tabellen gemaakt zijn en een overzicht geven van de ingevulde
waarden.
Om te beginnen maken we een tabel PROJECTEN aan. Deze tabel is, in een enigszins verkorte
vorm, bij het normaliseren al eens aan de orde geweest.
Indien de data dictionaire hiertoe geen mogelijkheden biedt, kunnen we de sleutel kolom
beschermen door
CREATE UNIQUE INDEX PROJ_PROJ_CODE ON PROJECTEN(PROJ_CODE)
De volgende waarden zijn ingevuld:
PROJ_CODE
110
120
130
140
150
PROJ_NAAM
ITCZ
MTS
CAIA
GABD
ZVH
PROJ_PLAATS
ROTTERDAM
ROTTERDAM
AMSTERDAM
UTRECHT
MAASTRICHT
PROJ_LEIDER
40
10
30
60
35
START_DATUM
01-JAN-95
15-AUG-95
01-OCT-96
01-JAN-96
15-FEB-96
BUDGET
100.000
145.000
80.000
98.000
112.000
Vervolgens maken we een tabel WERK aan die de gegevens over de werkzaamheden van de
verschillende werknemers aan de verschillende projecten vastlegt. Deze tabel brengt dus een
verband aan tussen de tabel WERKNEMERS en de tabel PROJECTEN.
CREATE TABLE WERK (
PROJ_CODE CHAR(3) NOT NULL,
CODE CHAR(3) NOT NULL,
UREN NUMBER(4)
)
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
CREATE TABLE PROJECTEN (
PROJ_CODE CHAR(3) NOT NULL,
PROJ_NAAM CHAR(15) NOT NULL,
PROJ_PLAATS CHAR(20),
PROJ_LEIDER CHAR(3),
STARTDATUM DATE,
BUDGET NUMBER(6)
)
4
2
Database SQL
1 mei 2010
Ook hier kunnen we de (samengestelde) sleutel beschermen met
CREATE UNIQUE INDEX WERK_PRJODE ON WERK(PROLCODE,CODE)
Ingevuld zijn de volgende waarden
PROJ_CODE
110
110
110
110
110
120
120
120
120
120
120
130
130
140
140
140
150
150
150
150
CODE
40
25
45
26
10
10
50
20
12
26
40
30
26
60
48
26
35
47
28
26
UREN
120
48
90
28
12
80
76
18
63
18
38
26
70
50
81
26
90
22
10
18
CREATE TABLE SCHALEN (
SCHAAL CHAR(2) NOT NULL,
LAAGSTE NUMBER(8,2),
HOOGSTE NUMBER(8,2)
)
Vervolgens eventueel
CREATE UNIQUE INDEX SCHALEN_SCH ON SCHALEN(SCHAAL)
Ingevuld geeft dat
SCHAAL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
LAAGSTE
1600,00
2000,00
2300,00
2600,00
2900,00
3200,00
3500,00
3800,00
4100,00
4500,00
5000,00
5500,00
6000,00
6500,00
HOOGSTE
1999,99
2299,99
2599,99
2899,99
3199,99
3499,99
3799,99
4099,99
4499,99
4999,99
5499,99
5999,99
6499,99
9999,99
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Tenslotte maken we nog een tabel aan waarin we vastleggen welke SALARISSCHALEN er in
onze organisatie zijn. We leggen uiteraard het nummer van de schaal en de hoogste en laagste
salarissen binnen iedere schaal vast.
4
3
Database SQL
1 mei 2010
De JOIN
Zoals we in paragraaf 3.2 al hebben kunnen zien is het mogelijk om gegevens uit meerdere
tabellen te raadplegen door gebruik te maken van de JOIN. Dat wil zeggen dat we achter het
FROM gedeelte meerdere tabellen noemen en vervolgens (om het cartesiaans produkt te
beperken) in het WHERE gedeelte nadere voorwaarden stellen. De meest voor de hand
liggende en daardoor ook meest gebruikte vorm is die waarbij we stellen dat alle kolommen in
alle rijen van het cartesiaans produkt betrekking hebben op dezelfde entiteit. Dit kunnen we
doen door te stellen dat de inhoud van twee kolommen die in beide tabellen voorkomen, gelijk
moeten zijn aan elkaar. We noemen dit ook wel de EQUIJOIN vanwege het = teken dat gebruikt wordt.
Als we dit willen doen, moeten we er uiteraard voor zorgen dat er in beide tabellen een
overeenkomstige kolom voorkomt. Als er een goede gegevensanalyse en een goede
normalisatie (zie DEEL 1) heeft plaatsgevonden is dat ook het geval.
Als we nu bijvoorbeeld gegevens op willen vragen uit de tabellen WERKNEMERS en WERK,
laten we zeggen de CODE van de werknemers, hun NAAM en de PROJJODE met het aantal
UREN dat de werknemer aan dat project heeft gewerkt gebruiken we
SELECT WERKNEMERS.CODE,NAAM,PROJ_CODE,UREN
FROM WERKNEMERS,WERK
WHERE WERKNEMERS.CODE = WERK.CODE
We moeten weer rekening houden met het feit dat de kolom CODE zowel in WERKNEMERS als
in WERK voorkomt dus moeten we aangeven welke CODE kolom we bedoelen. In het
bovenstaande voorbeeld is het uiteraard net zo goed mogelijk om te vragen WERK.CODE af te
drukken in plaats van WERKNEMERS.CODE. Het zal ook duidelijk zijn dat een werknemer die
aan meerdere projecten werkt nu ook meerdere malen in ons overzicht voor zal komen. Zijn of
haar CODE is immers een aantal malen gelijk aan een CODE in de tabel WERK.
We hadden natuurlijk ook op kunnen geven
SELECT WERKNEMERS.CODE,WERKNEMERS.NAAM,
WERK. PROJ_ CODE, WERK. UREN
FROM WERKNEMERS,WERK
WHERE WERKNEMERS.CODE = WERK.CODE
maar omdat de kolommen NAAM,PROJ_CODE en UREN niet in beide tabellen voorkomen en er
dus geen verwarring mogelijk is, laten we de tabelaanduiding meestal achterwege.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
In onze database is er een verband aan te brengen tussen de tabel WERKNEMERS en de tabel
WERK doordat in beide tabellen de kolom CODE voorkomt. Bovendien kunnen we een verband
aanbrengen tussen de tabellen PROJECTEN en WERK door middel van de kolom PROJ_CODE.
Uiteraard is het dan ook mogelijk om een verband aan te brengen tussen WERKNEMERS en
PROJECTEN via de tabel WERK. Schematisch hebben we dus de volgende situatie
4
4
Database SQL
1 mei 2010
Het is uiteraard ook mogelijk om een JOIN te maken van meer dan twee tabellen. We zouden
de voorgaande vraag kunnen uitbreiden door ook de PROJ_NAAM in onze vraagstelling op te
nemen. We krijgen dan:
SELECT WERKNEMERS.CODE, NAAM,
PROJECTEN. PROJ_CODE, PROJ_NAAM,UREN
FROM WERKNEMERS,WERK,PROJECTEN
WHERE WERKNEMERS.CODE = WERK.CODE AND
WERK.PROJ_CODE = PROJECTEN.PROJ_CODE
Om dit geheel wat in te korten (en omdat we deze mogelijkheid in het vervolg nog nodig
hebben) kunnen we gebruik maken van zogenaamde ALIAS namen voor de tabellen. Dat geven
we aan door na de naam van de tabel in het FROM gedeelte een spatie en vervolgens de
alternatieve naam op te geven. Zo zouden we de vorige opdracht ook hebben kunnen
formuleren als
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
SELECT A.CODE, NAAM, C.PROJ_CODE,
PROJ_NAAM, UREN
FROM WERKNEMERS A,WERK B,PROJECTEN C
WHERE A.CODE = B.CODE AND B.PROJ_CODE = C.PROJ_CODE
4
5
Database SQL
1 mei 2010
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1.De codes, namen (van de werknemers) en de projectcodes van de projecten waar de
werknemers aan werken.
2.De projectcodes, projectnamen en medewerkerscodes van alle projecten.
3.De codes, namen, projectcodes, projectnamen en de bestede uren per werknemer.
4. De namen en de bestede uren van alle werknemers.
5. De namen en de bestede uren van alle werknemers uit UTRECHT.
6.De projectnamen van alle projecten waar werknemers die in ROTTERDAM wonen aan
meewerken.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
7.De namen van alle werknemers die aan een project in AMSTERDAM werken.
4
6
Database SQL
1 mei 2010
De OUTERJOIN
In sommige gevallen komt het voor dat we een overzicht willen hebben van gegevens uit
meerdere kolommen en daarbij ook gegevens willen zien van entiteiten die niet in beide
tabellen voorkomen.
Als we naar het voorbeeld uit de vorige paragraaf kijken zien we dat de werknemers die niet
aan een project werken en wiens CODE dus niet voorkomt in de tabel WERK niet afgebeeld
worden in ons overzicht. Hun CODE is immers in geen enkel geval gelijk aan een CODE uit de
WERK tabel! Als we deze werknemers toch in ons overzicht willen betrekken, is daar in de
standaard SQL opdrachten geen mogelijkheid voor. Sommige RDBMS’en zoals ORACLE geven
ons die mogelijkheid wel door de zogenaamde OUTERJOIN.
De OUTERJOIN is een vorm van de EQUIJOIN waarbij we als het ware zeggen: 'Als het zo is dat
een kolomwaarde die in één van de twee tabellen voorkomt (en gebruikt wordt in het WHERE
gedeelte) niet voorkomt in de andere tabel, neem deze rij dan toch éénmalig op in het
uiteindelijke overzicht'
We geven dit aan door in het WHERE gedeelte, na de naam van de kolom waar de waarde
eventueel niet in voorkomt, een tussen haakjes geplaatst + teken te zetten.
De vraag uit de vorige paragraaf zou er dan als volgt uit komen te zien
SELECT WERKNEMERS.CODE,NAAM,
PROJECTEN.PROJ_CODE, PROJ_NAAM,UREN
FROM WERKNEMERS,WERK,PROJECTEN
WHERE WERKNEMERS.CODE = WERK.CODE(+) AND WERK.PROJ_CODE =
PROJECTEN.PROJ_CODE(+)
De AUTOJOIN
Er is een aantal vragen dat ook met de tot nu toe behandelde mogelijkheden niet op te lossen
is. Dat geldt met name voor vragen waarbij we eigenlijk voorwaarden willen stellen die
betrekking hebben op dezelfde tabel als waar we gegevens uit raadplegen.
Dat is bijvoorbeeld het geval bij een vraag als: Welke werknemers hebben een hoger salaris
dan hun chef? We willen dan eigenlijk de werknemers koppelen aan hun chef om te kunnen
testen of hun salaris hoger is dan het salaris van die chef. Dat zou geen problemen opleveren
als we een aparte CHEFS tabel tot onze beschikking zouden hebben. We zouden dan immers de
volgende situatie hebben:
WERKNEMERS
CODE NAAM
SAL
10
JAN
100
20
30
40
50
IRENE
KAREL
INGE
PETER
80
90
110
60
CHEF
CHEFS
CODE
SAL
20
20
80
30
90
30
30
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Hierdoor worden dus ook de werknemers die niet aan een project werken in ons overzicht
opgenomen. Bij de kolommen PROJ_CODE, PROJ_NAAM en UREN wordt dan de waarde NULL
in ons overzicht geplaatst.
4
7
Database SQL
1 mei 2010
Als we dan willen weten welke werknemer meer verdient dan zijn of haar chef vragen we
eenvoudigweg
SELECT NAAM
FROM WERKNEMERS,CHEFS
WHERE WERKNEMERS.CHEF=CHEFS.CODE AND WERKNEMERS.SAL>C HEFS.SAL
De JOIN van de tabellen WERKNEMERS en CHEFS levert het volgende tussenresultaat op (we
koppelen alle rijen uit de twee tabellen waarvoor geldt dat de waarde ingevuld bij de kolom
CHEF in de WERKNEMERS tabel gelijk is aan de waarde in de kolom CODE in de CHEFS tabel, en
hebben dus de werknemers en hun chefs aan elkaar gekoppeld)
CODE
10
40
50
NAAM
JAN
INGE
PETER
SAL
100
110
60
CHEF
20
30
30
CODE
20
30
30
SAL
80
90
90
CODE
20
30
SAL
80
90
Hier wordt dan vervolgens de voorwaarde
WERKNEMERS. SAL>CHEFS.SAL
op toegepast. Dat levert het volgende tussenresultaat op
CODE
10
40
NAAM
JAN
INGE
SAL
100
110
CHEF
20
30
Tenslotte volgt er dan nog de, inmiddels welbekende, PROJECTIE en dat geeft als eindresultaat
JAN
INGE
Maar helaas heeft het proces van normalisatie er toe geleid dat er geen aparte tabel CHEFS is.
Dat zorgt er voor dat er zo weinig mogelijk gegevens dubbel (dus redundant) worden
opgeslagen maar maakt de vraagstelling er niet gemakkelijker op.
We kunnen dit probleem oplossen door gebruik te maken van de mogelijkheid om een tabel
twee ALIAS namen te geven en die tabellen vervolgens gewoon als twee aparte tabellen te behandelen.
Vervolgens doen we precies hetzelfde als in het voorgaande geval; we koppelen de
werknemers aan hun chefs, controleren of hun salaris hoger is dan dat van die chef en laten
van alle werknemers waar dit voor geldt hun naam afdrukken
SELECT WERKERS.NAAM
FROM WERKNEMERS WERKERS,
WERKNEMERS CHEFS
WHERE WERKERS.CHEF = CHEFS.CODE AND
WERKERS.SAL > CHEFS.SAL
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
NAAM
4
8
Database SQL
1 mei 2010
Omdat de kolom NAAM in beide tabellen voorkomt moeten we weer aangeven welke kolom
we bedoelen. In dit geval is het natuurlijk wel van belang welke van de twee we kiezen!
Dit levert ons dan het volgende overzicht op
NAAM
JANSMA
PAREE
ZEEUW
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van alle werknemers,die meer verdienen dan iemand anders die in dezelfde
woonplaats woont als zij zelf.
2. De namen van alle projecten die een budget hebben dat hoger is dan het budget van een
project dat in dezelfde projectplaats wordt uitgevoerd.
3. De namen van alle werknemers die meer verdienen dan werknemer PAREE.
4. De namen van alle werknemers die meer verdienen dan een werknemer die voor dezelfde
chef werkt.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
5. De namen van alle werknemers die eerder in dienst gekomen zijn dan hun chef.
4
9
Database SQL
1 mei 2010
De NON-EQUIJOIN
In de vorige paragraaf hebben we eigenlijk al kunnen zien dat de voorwaarden in het WHERE
gedeelte bij de JOIN niet beperkt hoeven te blijven tot gelijkstellende voorwaarden (de
EQUIJOIN) maar dat we ook < , > en andere operatoren mogen gebruiken. Zo’n JOIN noemen
we dan ook een NON-EQUIJOIN.
Het is zelfs toegestaan om bijvoorbeeld de BETWEEN operator te gebruiken. Dit zouden we in
onze database toe kunnen passen door te vragen naar de namen van de werknemers met
daarbij afgedrukt hun salaris en hun salarisschaal. We moeten dan een JOIN formuleren van de
WERKNEMERS tabel met de SCHALEN tabel. De SCHAAL die we dan willen koppelen aan de
rijen uit de WERKNEMERS tabel betreft dan steeds die schaal die een boven- en een
ondergrens kent waartussen het salaris van de desbetreffende werknemer zich bevindt. We
kunnen dat als volgt bereiken:
SELECT NAAM,SAL,SCHAAL
FROM WERKNEMERS,SCHALEN
WHERE SAL BETWEEN LAAGSTE AND HOOGSTE
De SUBQUERY
Naast het gebruik van de JOIN om gegevens uit meerdere tabellen te raadplegen kennen we de
SUBQUERY oftewel de sub-vraag om bepaalde problemen op te lossen. Er is vrijwel geen enkel
probleem te bedenken dat we niet ook met de JOIN op zouden kunnen lossen maar in veel
gevallen is de SUBQUERY een stuk helderder en bovendien eenvoudiger te formuleren.
SELECT WOONPLAATS
FROM WERKNEMERS
WHERE NAAM = 'BOS'
Met als resultaat
WOONPLAATS
ROTTERDAM
en vervolgens
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM'
Uiteraard levert dit het antwoord op onze vraag op, maar het zou zoveel eenvoudiger zijn als
we dat in één stap zouden kunnen doen. Laten we het eerst eens op gaan lossen met een JOIN
SELECT WERKERS1.NAAM
FROM WERKNEMERS WERKERS1,WERKNEMERS WERKERS2
WHERE WERKERS2.NAAM = 'BOS' AND
WERKERS1.WOONPLAATS = WERKERS2WOONPLAATS
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Stel dat we op zoek zijn naar de namen van alle werknemers die in dezelfde plaats wonen als
werknemer BOS. Natuurlijk kunnen we het antwoord hierop in twee stappen verkrijgen:
5
0
Database SQL
1 mei 2010
Daarmee koppelen we iedere rij uit de WERKNEMERS tabel (die we WERKERS1 noemen) met
iedere rij uit nogmaals de WERKNEMERS tabel (nu WERKERS2 genoemd) als geldt dat in het
tweede gedeelte bij NAAM (WERKERS2.NAAM dus) 'BOS' is ingevuld en als bovendien de
WOONPLAATS in beide gedeelten (WERKERS1 en WERKERS2) hetzelfde is.
Alleen al het feit dat hier enige uitleg nodig is bij de oplossing van een vrij eenvoudige vraag,
geeft aan dat de wijze van oplossen niet erg simpel is. Met een SUBQUERY is het eigenlijk veel
eenvoudiger op te lossen
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS = (
SELECT WOONPLAATS
FROM WERKNEMERS
WHERE NAAM = 'BOS'
)
Als we deze oplossing zien, zien we al vrij snel wat de bedoeling is, namelijk 'Geef de namen
van alle werknemers met als woonplaats (de woonplaats van BOS)'. Dit is niet alleen wat
sneller te doorzien maar sluit ook meer aan bij de wijze waarop we de vraag geformuleerd
hadden.
Door gebruik te maken van een sub-vraag kunnen we eigenlijk op vrij eenvoudige wijze
vraagstellingen zoals in het voorgaande oplossen.
zal dit tot een foutmelding leiden omdat de SUBQUERY hier meerdere woonplaatsen oplevert
en geen enkele waarde uit een rij kan gelijk zijn aan de waarden uit meerdere andere rijen. We
gebruiken dan de (al eerder behandelde) IN operator.
Dus
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS IN
(
SELECT WOONPLAATS
FROM WERKNEMERS
WHERE CHEF = '40'
)
Het is ook mogelijk om meerdere SUBQUERIES aan elkaar te koppelen.
De voorgaande vraag zouden we ook kunnen formuleren als:
Welke werknemers wonen in dezelfde plaats als de werknemers die als chef MERK hebben?
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS =(
SELECT WOONPLAATS
FROM WERKNEMERS
WHERE CHEF = '40'
)
5
1
Database SQL
1 mei 2010
Dat lossen we op door :
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van werknemers die dezelfde chef hebben als werknemer 40.
2. De namen van werknemers die aan hetzelfde project werken als werknemer 35.
3. De namen van projecten die uitgevoerd worden In dezelfde plaats als de woonplaats
van werknemer BOS.
4. De namen van werknemers die in een andere plaats wonen dan werknemer 99.
5. De namen van werknemers die een hoger salaris hebben dan werknemer 12.
6. De namen van projecten waar werknemer MERK aan werkt.
7. De woonplaatsen van werknemers die aan hetzelfde project werken als werknemer
MERK.
8. De namen van alle werknemers die in dezelfde salarisschaal vallen als werknemer BOS.
9. De projectcodes van alle projecten waar een werknemer meer dan 30 uur aan gewerkt
heeft.
10. De namen van werknemers die dezelfde chef hebben als werknemer MAAS en in
dezelfde plaats wonen als werknemer PAREE.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
SELECT NAAM
FROM WERKNEMERS
WHERE WOONPLAATS IN
(
SELECT WOONPLAATS
FROM WERKNEMERS
WHERE CHEF IN
(
SELECT CODE
FROM WERKNEMERS
WHERE NAAM = 'MERK'
)
)
5
2
Database SQL
1 mei 2010
ANY en ALL
Speciaal voor gebruik met SUBQUERIES zijn de operatoren ANY en ALL beschikbaar. Hoewel ze
in veel gevallen overbodig zijn en vaak vervangen kunnen worden door de (nog te behandelen)
EXISTS operator, kunnen in sommige gevallen de ANY en ALL operatoren goede diensten
bewijzen.
Als we ons bijvoorbeeld afvragen welke werknemers er meer verdienen dan alle werknemers
uit ROTTERDAM kunnen we daarvoor de ALL operator in combinatie met de > operator
gebruiken
SELECT NAAM
FROM WERKNEMERS
WHERE SAL > ALL (
SELECT SAL
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM'
)
De ANY operator geeft dus aan, dat het voldoen aan één enkele waarde in de SUBQUERY
genoeg is.
Uiteraard zijn ANY en ALL ook te gebruiken in combinatie met de operatoren >= < en <=
Het gebruik van de ANY en ALL operatoren in combinatie met = en ! = is wel toegestaan maar
niet aan te raden omdat het vaak leidt tot ondoorzichtig geformuleerde vraagstellingen.
Bovendien kan dit altijd vermeden worden door gebruik te maken van IN en NOT IN.
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van werknemers die later in dienst zijn gekomen dan alle werknemers uit
ROTTERDAM.
2. De namen van werknemers die eerder in dienst zijn gekomen dan een werknemer uit
AMSTERDAM.
3. De namen van projecten die een "groter budget hebben dan alle projecten waar
werknemer 4Q aan werkt.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Het gebruik van de ALL operator houdt dus in dat het salaris in alle gevallen groter moet zijn
dan de salarissen van werknemers uit ROTTERDAM.
Vinden we het daarentegen voldoende als de werknemer een salaris heeft dat hoger is dan dat
van een willekeurige werknemer uit ROTTERDAM, dan kunnen we ANY gebruiken
SELECT NAAM
FROM WERKNEMERS
WHERE SAL > ANY (
SELECT SAL
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM'
)
5
3
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Database SQL
1 mei 2010
5
4
Database SQL
1 mei 2010
De gesynchroniseerde SUBQUERY
Tot nu toe hebben we het uitsluitend gehad over SUBQUERIES die onafhankelijk van de
hoofdquery werden uitgevoerd. Met andere woorden, er werd eerst een soort deelverzameling
van rijen geselecteerd door middel van een SELECT instructie tussen haakjes om vervolgens de
te selecteren rijen te toetsen aan deze verzameling. De samengestelde deelverzameling was
dus een statisch geheel waaraan de te selecteren rijen getoetst werden.
Het is echter ook mogelijk om de SUBQUERY te koppelen aan de HOOFDQUERY. We kunnen
dus voor iedere rij die beoordeeld wordt, opnieuw de deelverzameling waaraan getoetst wordt
samen laten stellen. Hierdoor krijgt de subquery een veel dynamischer karakter en loopt dus
.eigenlijk synchroon met de hoofdquery, vandaar ook de benaming gesynchroniseerde
subquery (soms ook wel gecorreleerde subquery genoemd vanwege het verband (de
correlatie) met de hoofdquery).
In zijn kernvorm ziet de gesynchroniseerde SUBQUERY er als volgt uit
We hebben een dergelijke constructie bijvoorbeeld nodig als we op zoek zijn naar alle
werknemers met een hoger salaris dan het gemiddelde salaris van de werknemers uit hun
WOONPLAATS. We hebben dan immers behoefte aan een steeds veranderend gemiddelde
salaris namelijk het gemiddelde salaris van de werknemers uit de WOONPLAATS van de
werknemer die op dat moment voor selectie in aanmerking komt. Een steeds weer opnieuw te
berekenen, dynamisch oftewel gesynchroniseerd gemiddelde! We lossen dit probleem als volgt
op
SELECT NAAM
FROM WERKNEMERS W WHERE SAL >
(
SELECT AVG(SAL)
FROM WERKNEMERS
WHERE WOONPLAATS = W.WOONPLAATS
)
Met behulp van een dergelijke constructie zouden we dus ook de vraag kunnen beantwoorden
welke werknemer PER WOONPLAATS het hoogste salaris heeft
SELECT NAAM
FROM WERKNEMERS W
WHERE SAL = (
SELECT MAX(SAL)
FROM WERKNEMERS
WHERE WOONPLAATS = W.WOONPLAATS
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
SELECT <kolom naam>
FROM <tabel naam 1 > <aliasnaam>
WHERE <voorwaarde>
(
SELECT <kolomnaam>
FROM <tabelnaam2>
WHERE <uitdrukking> <operator> <aliasnaam
.kolomnaam>
)
5
5
Database SQL
1 mei 2010
)
De EXISTS operator
De EXISTS operator wordt gebruikt om te testen of een rij wel of niet voorkomt in een bepaalde
tabel. Hoewel de EXISTS operator slechts in enkele gevallen noodzakelijk is, zullen we hem hier
toch behandelen. In sommige RDBMS-en is het niet toegestaan om een GROUP FUNCTIE aan
beide zijden van een operator te gebruiken. In deze gevallen moeten we de EXISTS operator
gebruiken.
We zouden ons bijvoorbeeld af kunnen vragen welke werknemers tenminste één of meer
werknemers 'onder zich hebben' in de hiërarchie van de organisatie. We zouden dat als volgt
op kunnen lossen :
SELECT NAAM
FROM WERKNEMERS W
WHERE EXISTS (
SELECT *
FROM WERKNEMERS
WHERE CHEF = W.CODE
)
Hierbij vragen we ons dus steeds af: 'Bestaat er een rij in de WERKNEMERS tabel waarvoor
geldt dat er in de kolom CHEF een waarde is ingevuld die gelijk is aan de waarde die ingevuld is
in de kolom CODE van de rij die op het punt staat geselecteerd te worden? Is dat het geval druk
dan de waarde uit de kolom NAAM van die rij af.'
De oplettende en fantasierijke lezer had natuurlijk al opgemerkt dat dit probleem ook op te
lossen is door middel van een JOIN van de tabel WERKNEMERS met zichzelf (de AUTOJOIN dus)
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van alle werknemers die tenminste aan alle projecten werken waar ook
werknemer 40 aan werkt. (Gebruik de EXISTS operator!)
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
SELECT DISTINCT CHEFS.NAAM
FROM WERKNEMERS WERKERS,WERKNEMERS CHEFS
WHERE WERKERS.CHEF=CHEFS.CODE
5
6
Database SQL
1 mei 2010
UNION, MINUS en INTERSECT
De UNION, MINUS en INTERSECT operatoren voeren bewerkingen uit op tabellen die het
resultaat zijn van twee SELECT opdrachten. Deze resultaat tabellen kunnen we beschouwen als
verzamelingen van elementen. Als we uitgaan van twee verzamelingen A en B, zal de UNION de
optelsom van alle elementen van verzameling A en B opleveren. De MINUS geeft bij A - B alle
elementen van A behalve die elementen die ook onderdeel uitmaken van B en B - A levert alle
elementen van verzameling B op behalve die elementen die ook lid zijn van A. De INTERSECT
tenslotte zal alle elementen die voorkomen bij A en ook onderdeel uitmaken van B
presenteren. We kunnen dit grafisch duidelijk weergeven:
SELECT NAAM,TELEFOON
FROM WERKNEMERS
WHERE WOONPLAATS = 'AMSTERDAM'
UNION
SELECT NAAM,TELEFOON
FROM WERKNEMERS
WHERE WOONPLAATS = 'UTRECHT'
Deze vraag is natuurlijk gemakkelijker te beantwoorden door gebruik te maken van OR
SELECT NAAM,TELEFOON
FROM WERKNEMERS
WHERE WOONPLAATS = 'AMSTERDAM' OR WOONPLAATS = 'UTRECHT'
Of met de IN operator
SELECT NAAM,TELEFOON FROM WERKNEMERS
WHERE WOONPLAATS IN ('AMSTERDAM':UTRECHT')
We kunnen echter niet in alle gevallen gebruik maken van IN of OR.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Omdat we hierbij de resultaat tabellen als verzamelingen beschouwen, zullen ook de eventueel
dubbel voorkomende rijen buiten beschouwing worden gelaten. Het gebruik van DISTINCT bij
deze operatoren is dus overbodig.
De UNION operator doet dus eigenlijk niets anders dan twee of meer resultaattabellen bij
elkaar tellen. Het spreekt voor zich dat, dat betekent dat het aantal kolommen en het type van
de kolommen uit de resultaattabellen met elkaar moeten overeenkomen.
We zouden bijvoorbeeld de namen en telefoonnummers van alle werknemers uit AMSTERDAM
of UTRECHT in een tabel kunnen zetten met
5
7
Database SQL
1 mei 2010
Stel dat we, naast de WERKNEMERS tabel, ook de beschikking hebben over een tabel met
gepensioneerde werknemers en we willen een overzicht in een resultaattabel van alle
werknemers en gepensioneerden uit AMSTERDAM of UTRECHT.
Bij dit soort vragen kan de UNION operator goede diensten bewijzen
SELECT NAAM,TELEFOON
FROM WERKNEMERS
WHERE WOONPLAATS IN ('AMSTERDAM':UTRECHT')
UNION
SELECT NAAM,TELEFOON
FROM GEPENSIONEERDEN
WHERE WOONPLAATS IN ('AMSTERDAM':UTRECHT')
We kunnen MINUS bijvoorbeeld gebruiken om de vraag te beantwoorden: in welke plaatsen
wonen wel werknemers maar worden geen projecten uitgevoerd:
SELECT WOONPLAATS
FROM WERKNEMERS
MINUS
SELECT PROJ_PLAATS
FROM PROJECTEN
SELECT WOONPLAATS
FROM WERKNEMERS
WHERE WOONPLAATS NOT IN
(
SELECT PROJ_PLAATS
FROM PROJECTEN
)
En zo kunnen we natuurlijk ook alle plaatsen achterhalen waar zowel werknemers wonen als
projecten worden uitgevoerd door:
SELECT WOONPLAATS
FROM WERKNEMERS
INTERSECT
SELECT PROJ_PLAATS
FROM PROJECTEN
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van alle projecten en de namen van alle werknemers in één tabel
gepresenteerd.
2. De codes van alle werknemers en de codes van alle projecten in één tabel
gepresenteerd.
Hoofdstuk: HET RAADPLEGEN VAN MEERDERE TABELLEN
Uiteraard hadden we die vraag ook kunnen beantwoorden met:
5
8
Database SQL
1 mei 2010
VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
De denkwijze
In het voorgaande zijn we er eigenlijk steeds van uitgegaan dat SOL op vrij eenvoudige wijze
antwoord kan geven op allerlei soorten vragen.
Toch blijkt in de praktijk dat veel mensen na korte tijd tegen dezelfde soort problemen
aanlopen en pas na zeer veel moeite, of soms zelfs helemaal niet, tot een oplossing komen.
Vaak blijkt dan dat men zich de denkwijze die men binnen SOL moet volgen niet goed eigen
gemaakt heeft. In dit hoofdstuk zullen we een poging wagen om, aan de hand van een aantal
vaak terugkerende problemen, de lezer hier alvast op voor te bereiden.
Nog nooit ...
Een probleem vinden veel mensen, die nog weinig ervaring met SOL hebben, vaak de
vraagstellingen die we aan zouden kunnen geven met de zinsnede 'nog nooit ... .'
Als we echter de mogelijkheden van SOL goed op een rijtje zetten, komen we al vrij snel tot de
conclusie dat we een dergelijke vraag eigenlijk zouden moeten benaderen alsof er staat: 'Wie
komt er niet voor in de groep die .... al eens gedaan/gehad hebben 7'
Dat geldt dus ook voor de voorgaande vraag. Eigenlijk zouden we moeten stellen
'Welke werknemers komen niet voor in de groep werknemers die wel eens aan een project in
UTRECHT hebben gewerkt 7'
En deze vraag is in SOL tamelijk eenvoudig te formuleren
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE NOT IN
(
SELECT CODE
FROM WERK
WHERE PROJ_CODE IN
(
)
Nou ja … tamelijk eenvoudig?!
)
SELECT PROJ_CODE
FROM PROJECTEN
WHERE PROJ_PLAATS = 'UTRECHT'
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
Vragen als: 'Welke werknemers hebben nog nooit aan een project in UTRECHT gewerkt 7'
worden dan gezien als onoverkomelijke problemen.
5
9
Database SQL
1 mei 2010
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van werknemers die nog nooit aan een project hebben gewerkt, dat geleid
wordt door werknemer 10.
2. De namen van projecten waar werknemer 60 nog nooit aan gewerkt heeft.
3. De namen van projecten waar nog nooit iemand uit AMSTERDAM aan gewerkt heeft.
4. De namen van alle werknemers die nog nooit aan een project met PROJJODE 120
hebben gewerkt.
5. De namen van werknemers die nog nooit langer dan 30 uur aan een project hebben
gewerkt.
Een ander, steeds terugkerend, probleem vormen vragen die de zinsnede 'alleen maar' met
zich meebrengen.
We zouden bijvoorbeeld de vraag kunnen stellen welke werknemers alleen maar gewerkt
hebben aan projecten in UTRECHT.
Ook hier moeten we ons realiseren dat er voor 'alleen maar' geen operator in SQL bestaat. Wel
kunnen we stellen dat de desbetreffende werknemer blijkbaar aan een project in UTRECHT
moet werken of gewerkt moet hebben en tegelijkertijd niet aan een project uit een andere
plaats mag werken of gewerkt mag hebben. Zo geformuleerd kunnen we de vraag wel in SQL
beantwoorden
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE IN
(
SELECT CODE
FROM WERK
WHERE PROJ_CODE IN
(
SELECT PROJ_CODE
FROM PROJECTEN
WHERE PROJ_PLAATS = 'UTRECHT'
)
)
AND
CODE NOT IN
(
SELECT CODE
FROM WERK
WHERE PROJ_CODE NOT IN
(
SELECT PROJ_CODE
FROM PROJECTEN
WHERE PROJ_PLAATS = 'UTRECHT'
)
)
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
Alleen maar ...
6
0
Database SQL
1 mei 2010
Afgezien van de op het eerste gezicht vrij omslachtig aandoende formulering, zal het duidelijk
zijn dat een goede omschrijving van de eigenlijke vraag en, daarmee samenhangend, een juiste
plaatsing van de ontkenning(en) van groot belang is.
We kunnen deze vraag overigens ook oplossen door middel van
Daarmee stellen we in het eerste gedeelte dat de werknemer niet aan projecten in een andere
plaats dan UTRECHT gewerkt mag hebben. In het tweede gedeelte voegen we toe dat de
desbetreffende werknemer wel voor moet komen in de tabel WERK omdat anders ook de
werknemers die aan geen enkel project werken (en dus ook niet aan een project in een andere
plaats dan UTRECHT) in het resultaat worden opgenomen.
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van werknemers die alleen maar aan projecten geleid door werknemer 10
hebben meegewerkt.
2. De namen van projecten waar alleen maar door werknemers uit ROTTERDAM aan is
gewerkt.
3. De namen van werknemers die alleen maar aan projecten hebben meegewerkt waar
ook door werknemer JOBSE aan is gewerkt.
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
SELECT NAAM
FROM WERKNEMRERS
WHERE CODE NOT IN
(
SELECT CODE
FROM WERK
WHERE PROLCODE NOT IN
(
SELECT PROJ_CODE
FROM PROJECTEN
WHERE PROJ_PLAATS='UTRECHT'
)
)
AND
CODE IN
(
SELECT CODE
FROM WERK
)
6
1
Database SQL
1 mei 2010
De (dubbele) ontkenning
Ontkennend geformuleerde vraagstellingen willen nog wel eens problemen opleveren. Met
name het verschil tussen het gebruik van NOT en de != operator leidt nogal eens tot
problemen.
Stel dat we willen weten welke werknemers niet aan het project met PROJ_CODE 110 werken.
Sommige mensen formuleren dit in SQL als
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE IN
(
SELECT CODE FROM WERK
WHERE PROJ CODE != '110'
)
Eigenlijk zouden we de vraagstelling eerst in SQL termen moeten vertalen. We zouden dan
kunnen zeggen dat we op zoek zijn naar alle werknemers die niet behoren tot de groep
werknemers die aan project 110 werken. Zo gesteld is de vraag vrij eenvoudig in SQL te
formuleren.
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE NOT IN
(
SELECT CODE
FROM WERK
WHERE PROJ_CODE = '110'
)
De ontkenning moet dus op een andere plaats staan omdat het mogelijk is dat een werknemer
aan meerdere projecten werkt.
Ook de dubbele ontkenning (niet behorend bij de groep waarvoor niet geldt ... ) vinden veel
mensen op het eerste gezicht lastig te formuleren in SQL. Toch is ook hiervoor na enig
denkwerk op vrij eenvoudige wijze een oplossing te formuleren.
Stel dat we een overzicht willen hebben van alle werknemers die nog nooit aan een project
hebben gewerkt dat niet de code 110 had. We zien dan al vrij snel dat het bij een dergelijke
vraagstelling eigenlijk gaat om een 'alleen maar' vraag die we in een vorige paragraaf al
besproken hebben en dus ook op een vergelijkbare manier op kunnen lossen :
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
Dat levert bij onze database bijvoorbeeld ook werknemer 26 (VAN GEEL) op, terwijl die wel
degelijk aan project 110 werkt! Als we goed kijken zien we dan ook dat met de bovenstaande
SELECT opdracht niet gevraagd wordt welke werknemers niet aan project 110 werken, maar
welke werknemers aan een project werken dat NIET de PROJ_CODE 110 heeft. Dat betekent
dus dat alle werknemers die aan een project werken met een andere PROJ_CODE dan 110
geselecteerd worden, dus ook de werknemers die naast een ander project ook aan project 110
werken.
6
2
Database SQL
1 mei 2010
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE NOT IN
(
SELECT CODE
FROM WERK
WHERE PROJ_CODE != '110'
)
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE NOT IN
(
SELECT CODE
FROM WERK
WHERE PROJ CODE!= '110'
)
AND
CODE IN
(
SELECT CODE
FROM WERK
)
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. De namen van werknemers die niet aan een project werken dat in UTRECHT wordt
uitgevoerd.
2. De namen van werknemers die aan een project werken dat in een andere plaats dan
UTRECHT wordt uitgevoerd.
3. De namen van werknemers die nog nooit aan een project geleid door werknemer 35
hebben meegewerkt.
4. De namen van werknemers die nog nooit aan een project hebben meegewerkt
waarvoor een budget lager dan 100.000 gulden beschikbaar was.
5. De namen van werknemers die nog nooit aan een project hebben gewerkt dat niet
door werknemer 35 werd geleid.
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
Wellicht het enige probleem dat hier nog aan vastzit is dat op grond van deze SELECT opdracht
(zoals in de eerdere paragraaf al aan de orde kwam) ook de werknemers die aan geen enkel
project meewerken geselecteerd worden omdat zij nu eenmaal behoren tot de groep
werknemers wiens CODE niet voorkomt in de groep van werknemers die niet aan project 110
werken; zij werken immers aan geen enkel project. Dit kunnen we vrij eenvoudig oplossen door
de voorgaande SELECT opdracht nog wat uit te breiden
6
3
Database SQL
1 mei 2010
Alle ...
Een lastig probleem vormen tenslotte vraagstellingen die het woord 'alle' in zich hebben.
'Welke klanten hebben alle artikelen besteld 7'
Of
'Welke werknemers werken mee aan alle projecten 7'
Dit probleem kunnen we op twee manieren benaderen.
In de eerste plaats kennen we geen speciale operator om het gebruik van 'alle' mee weer te
geven. We zullen de vraag dus op een 'SQL-achtige' manier moeten omschrijven.
We kiezen dan vrijwel altijd voor de volgende redenering:
Als een werknemer aan alle projecten mee moet werken is er dus geen enkel project waar de
werknemer niet aan meegewerkt heeft.
SELECT CODE,NAAM
FROM WERKNEMERS W
WHERE NOT EXISTS
(
SELECT PROJ_CODE
FROM PROJECTEN
WHERE PROJ_CODE NOT IN
(
SELECT PROJ CODE
FROM WERK
WHERE CODE = WCODE
)
)
Dit behoeft wellicht enige toelichting. Als we bovenstaande opdracht van achter naar voren
lezen, zien we dat in de laatste subquery alle PROJ_CODEs van de werknemer die op het punt
staat geselecteerd te worden uit de tabel WERK gehaald worden (Het is immers een
dynamische (gesynchroniseerde) subquery vanwege CODE = WCODE)
Als we dat bijvoorbeeld doen voor werknemer 40 dan zien we dat in de tabel WERK alleen de
PROJ_CODEs 110 en 120 bij de CODE 40 voorkomen. (Gebruik de tabellen om mee te denken!!)
Als we vervolgens de PROJ_CODEs uit de tabel PROJECTEN selecteren waarvoor geldt dat hun
PROJ_CODE niet voorkomt in de zojuist verzamelde groep PROJ_CODEs dan zien we dat alleen
PROJ_CODE 110 en 120 afvallen zodat we de PROLCODEs 130,140 en 150 overhouden.
Als we dan in de hoofdquery, door gebruik te maken van NOT EXISTS, aangeven dat de
subquery niets op mag leveren, zal duidelijk zijn dat werknemer 40 niet geselecteerd kan
worden omdat de subquery een drietal waarden (130,140 en 150) oplevert en dat is beduidend
meer dan niets!
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
Zo geformuleerd kunnen we deze vraag wel in SQL stellen
6
4
Database SQL
1 mei 2010
Nog een voorbeeld, nu met werknemer 26 die wel aan alle projecten meewerkt en dus wel
geselecteerd zou moeten worden. We beginnen weer met de laatste subquery, deze levert
voor werknemer 26 de waarden 110,120,130,140 en 150 op.
Vervolgens selecteren we uit de PROJECTEN tabel die PROJ_CODEs die niet in de zojuist
verzamelde waarden voor komen. Omdat we alleen de projecten 110,120,130, 140 en 150
kennen, kunnen we geen enkele waarde uit de PROJECTEN tabel selecteren die niet in de
verzamelde groep waarden voorkomt. Als de subquery niets oplevert, moeten we vanwege het
NOT EXISTS gedeelte in de hoofdquery de CODE van die werknemer selecteren. In dit geval
moet dat dus ook gebeuren. En dat is dan terecht want werknemer 26 werkt aan alle
projecten!
Een andere benadering is niet bij alle SQL implementaties mogelijk. We hebben al eerder
kunnen zien dat het gebruik van group functies aan twee zijden van een operator niet in alle
implementaties is toegestaan. Omdat het in sommige RDBMS-en wel toegestaan is en omdat
het een vereenvoudiging betekent van de vraagstelling, zullen we deze tweede oplossing hier
toch behandelen.
We zouden de vraag dan kunnen herformuleren als:
Deze vraag is vrij eenvoudig te formuleren als we gebruik kunnen maken van de COUNT functie
aan beide zijden van het = teken
SELECT CODE,NAAM
FROM WERKNEMERS
WHERE CODE IN
(
SELECT CODE
FROM WERK
GROUP BY CODE
HAVING COUNT(PROJ_CODE) =
(
SELECT COUNT(PROJ_CODE)
FROM PROJECTEN
)
)
Als we dit weer voor de twee werknemers die we hiervoor hebben behandeld nagaan kunnen
we zien dat voor de werknemer met CODE 40 het aantal verschillende projecten precies 2
bedraagt. Dat komt dus niet overeen met het aantal PROJ_CODEs in de PROJECTEN tabel (5).
Als we dan werknemer 26 bekijken zien we dat bij de CODE 26 precies 5 verschillende
PROJ_CODEs voorkomen in de PROJECTEN tabel. Dat is wel gelijk aan het aantal PROJ_CODEs in
de PROJECTEN tabel en werknemer 26 zal dus wel geselecteerd worden.
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
'Geef de werknemers die aan precies evenveel verschillende projecten werken als er projecten
zijn.'
6
5
Database SQL
1 mei 2010
Merk op dat we bij beide COUNT opdrachten geen DISTINCT hoeven te gebruiken omdat de
groepering per CODE er voor zal zorgen dat de bij iedere CODE behorende PROJ_CODEs
verschillend zullen zijn en omdat de PROJ_CODE de sleutel is van de PROJECTEN tabel en dus
uniek zal zijn in iedere rij. (Dit geldt uiteraard alleen als onze database ook daadwerkelijk
integer is !)
OPDRACHTEN
Geef een opdracht in SQL voor het opvragen van de volgende overzichten uit de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
Hoofdstuk: VEEL VOORKOMENDE PROBLEMEN MET DE SELECT OPDRACHT
1. De namen van alle medewerkers die meegewerkt hebben aan alle projecten die in
ROTTERDAM worden uitgevoerd.
2. De namen van projecten die geleid worden door alle werknemers uit MAASTRICHT.
3. De codes van alle projecten waaraan door werknemers uit alle in de tabel
WERKNEMERS bekende woonplaatsen wordt gewerkt.
6
6
Database SQL
1 mei 2010
DE DATA DICTIONARY
Het principe van de data dictionary
Bij het gebruik van een RDBMS is het vaak handig om gebruik te maken van de ingebouwde
data dictionary. Eigenlijk is de term data dictionary hier wat verwarrend omdat we ook van een
data dictionary spreken als we het in de gegevensanalyse hebben over alle informatie over
gegevens die in een bepaalde organisatie gebruikt worden. De lezer dient zich dus te realiseren
dat we het in dit hoofdstuk hebben over de door het RDBMS zelf aangemaakte en bijgewerkte
data dictionary.
In deze ingebouwde data dictionary wordt bijgehouden welke gebruikers toegang hebben tot
het systeem, welke tabellen er voor iedere gebruiker beschikbaar zijn, uit welke kolommen
iedere tabel bestaat, van welk type iedere kolom is, waar de gegevens zijn opgeslagen
enzovoort. Telkens als er een gebruiker toegang vraagt tot het systeem, als er om een tabel
gevraagd wordt of als er een rij aan een tabel wordt toegevoegd (om slechts een paar
voorbeelden te noemen) zal het RDBMS de data dictionary gebruiken om te controleren of de
gevraagde opdracht mogelijk en/of toegestaan is. Omdat we in de data dictionary te maken
hebben met gegevens over de gegevens in de database, wordt hierbij ook wel gesproken van
metagegevens.
Bovendien zien we dat de data dictionary een steeds belangrijkere rol gaat spelen bij het
opleggen van regels aan de gegevens die in de database worden opgeslagen.
In dit hoofdstuk zullen we vooral stilstaan bij de mogelijkheden van de data dictionary voor het
bewaken van de integriteit van de database. Omdat andere aspecten van de data dictionary
(fysieke opslag, bron van informatie voor de beheerder) in veel gevallen erg verschillend zijn
voor iedere SQL implementatie zal daar verder niet bij worden stilgestaan.
Zoals in hoofdstuk 1 al is aangegeven kan de data dictionary gebruikt worden om de gegevens
die in de database worden opgeslagen aan bepaalde regels te onderwerpen. We hebben het
hierbij over beperkingen die we op willen leggen aan de gegevens die worden opgeslagen, in
het Engels gebruiken we hiervoor de term constraint (beperking).
Al deze beperkingen worden bij het definiëren van de tabel (CREATE TABLE) of door middel van
het wijzigen van de tabel (ALTER TABLE) vastgelegd. De in deze paragraaf te bespreken
beperkingen vormen een uitbreiding op de al behandelde NOT NULL beperking.
Voor de op te leggen beperkingen kunnen we gebruik maken van de volgende mogelijkheden:
PRIMARY KEY, UNIQUE, FOREIGN KEY en CHECK.
Hoofdstuk: DE DATA DICTIONARY
De data dictionary als bewaker van de database.
6
7
Database SQL
1 mei 2010
Door de toevoeging van PRIMARY KEY dwingen we af dat alleen unieke waarden die bovendien
niet onbekend mogen zijn, in deze kolom worden opgenomen:
CREATE TABLE WERKNEMERS
(CODE CHAR(3) PRIMARY KEY, NAAM CHAR(15) NOT NULL)
De toevoeging UNIQUE zorgt er voor dat alleen unieke waarden worden opgenomen in de
desbetreffende kolom. In tegenstelling tot PRIMARY KEY moeten we bij UNIQUE wel de
toevoeging NOT NULL gebruiken als we deze kolom altijd ingevuld willen hebben:
CREATE TABLE PROJECTEN
(PROJ_CODE CHAR(3) PRIMARY KEY, PROJ_NAAM CHAR(15) NOT NULL
UNIQUE )
Door gebruik te maken van
FOREIGN KEY <kolomnaam, ... >
REFERENCES <tabelnaam> {<kolomnaam, ... >}
kunnen we de eerder besproken referentiële integriteit afdwingen.
CREATE TABLE PROJECTEN
(PROJ_CODE CHAR(3) PRIMARY KEY, PROJ_NAAM CHAR(15) NOT NULL
UNIQUE, PROJ_LEIDER CHAR(3),
FOREIGN KEY(PROJ_LEIDER) REFERENCES WERKNEMERS(CODE))
zorgt er voor dat bij iedere toevoeging en/of wijziging gecontroleerd wordt of de in te vullen
projectleider ook een bestaande werknemer is.
Met behulp van CHECK kunnen we de gegevens die ingevoerd worden controleren voordat ze
worden geaccepteerd.
CREATE TABLE WERKNEMERS (CODE CHAR(3) PRIMARY KEY, NAAM CHAR(15)
NOT NULL, CHECK (MOD(CODE,10) = 0), CHECK NAAM = UPPER(NAAM))
Omdat het in sommige gevallen onmogelijk is om bij het definiëren van de tabel al te verwijzen
naar een andere tabel, zullen we soms gebruik moeten maken van het ALTER TABLE
commando. Dit is bijvoorbeeld het geval als we in de WERKNEMERS tabel op willen nemen tot
welke afdeling een medewerker behoort en in een AFDELINGEN tabel willen opnemen welke
werknemer het hoofd van die afdeling is. Op het moment dat we de WERKNEMERS tabel
definiëren (er van uit gaande dat we dat als eerste doen) kunnen we nog niet opnemen
FOREIGN KEY AFDELING CHAR(3) REFERENCES AFDELINGEN(CODE) omdat de
AFDELINGEN tabel op dat moment nog niet bestaat. De volgorde omdraaien heeft geen zin
omdat dan bij het definiëren van de AFDELINGEN tabel de WERKNEMERS tabel nog niet bestaat
en we dus niet op kunnen nemen HOOFD CHAR(3), FOREIGN KEY (HOOFD)
REFERENCES WERKNEMERS(CODE). We lossen dit op door - nadat we de WERKNEMERS en
de AFDELINGEN tabellen hebben gedefinieerd - een ALTER TABLE opdracht uit te voeren:
Hoofdstuk: DE DATA DICTIONARY
zorgt er voor dat alleen codes voor werknemers gebruikt worden die een meervoud van 1 0 zijn
(rest na deling door 10 (MODulus) moet 0 zijn) en bewaakt dat alle namen met hoofdletters
worden ingevoerd.
6
8
Database SQL
1 mei 2010
ALTER TABLE WERKNEMERS
ADD (FOREIGN KEY (AFDELING) REFERENCES AFDELINGEN(CODE))
Hoofdstuk: DE DATA DICTIONARY
In ORACLE bijvoorbeeld kan vanaf versie 7 dit probleem opgelost worden door gebruik te
maken van de opdracht CREATE SCHEMA. Daarmee worden alle CREATE TABLE opdrachten als
één opdracht gezien en bestaat het hiervoor geschetste probleem niet meer.
6
9
Database SQL
1 mei 2010
VIEWS
Het principe van de VIEW
De VIEW is eigenlijk een fenomeen dat zich 'boven' de tabellen bevindt. We zouden kunnen
zeggen dat een VIEW een bepaalde kijk op één of meerdere tabellen weergeeft.
We zouden ons voor kunnen stellen dat er binnen de organisatie waar we onze WERKNEMERS
tabel gebruiken, verschillende personen en/of afdelingen zijn die gebruik willen maken van
gegevens uit onze WERKNEMERS tabel. De personeelsvereniging wil graag alle namen en
adressen, de afdeling personeelszaken wil de namen, adressen en telefoonnummers, de
boekhouding wil de codes en de salarissen, enzovoort.
Nu zouden we voor al deze personen/afdelingen aparte tabellen kunnen gaan maken die zij
dan zouden kunnen raadplegen. Dit heeft echter verschillende nadelen. In de eerste plaats
betekent dit dat de overtolligheid aan gegevens (redundantie) waar we door middel van
normaliseren zo tegen gestreden hebben, weer volledig teniet gedaan wordt omdat we allerlei
gegevens nu weer dubbel of zelfs driedubbel en meer op gaan slaan. Bovendien geeft dit nogal
wat onderhoudsproblemen. Als werknemer 10 verhuist, betekent dit dat we eerst na moeten
gaan in welke tabellen haar adres voorkomt om vervolgens deze wijziging in al die tabellen
door te gaan voeren.
We zouden er dan voor kunnen kiezen om de WERKNEMERS tabel dan maar voor iedereen
beschikbaar te stellen. Ook dit is niet zo'n gelukkige oplossing omdat we dan moeten gaan
bepalen wie welke wijzigingen door mag gaan voeren en bovendien moeten we er voor zorgen
dat een bepaalde wijziging slechts één maal wordt uitgevoerd (tweemaal een loonsverhoging
van 10% voor dezelfde werknemer zal voor die werknemer geen bezwaar zijn maar op die
manier is onze organisatie natuurlijk geen lang leven beschoren). Dat is een stuk lastiger als er
meerdere mensen de beschikking hebben over dezelfde gegevens. We komen hier nog op
terug in als we het hebben over Gegevensbeveiliging.
Om dit soort problemen het hoofd te kunnen bieden, kunnen we gebruik maken van VIEW’s.
Een VIEW is eigenlijk niets anders dan een vastlegging van welke gegevens uit welke tabel (of
tabellen!) gecombineerd moeten worden tot een VIEW. De VIEW krijgt een naam en gedraagt
zich verder volledig alsof het een tabel is. Bij een aantal RDBMS-en kunnen we zelfs via een
VIEW (die dan wel aan enkele voorwaarden moet voldoen) gegevens aan een tabel toevoegen
of gegevens uit een tabel wissen of wijzigen.
Het maken van een VIEW
Bij het maken van een VIEW hoeven we ons eigenlijk alleen maar af te vragen welke naam we
aan de VIEW zullen geven en met welke SELECT opdracht we de VIEW kunnen samenstellen.
Hoofdstuk: VIEWS
Bovendien vindt waarschijnlijk niet iedereen het zo'n prettig idee dat bijvoorbeeld zijn of haar
salaris niet alleen bij de financiële afdeling maar ook bij de personeelsvereniging en alle andere
geledingen inde organisatie bekend is.
7
0
Database SQL
1 mei 2010
Stel dat de eerder genoemde personeelsvereniging de beschikking wil hebben over de namen,
adressen, postcodes en woonplaatsen om de uitnodigingen voor de feestavonden en het
personeelsblad te kunnen versturen. We geven dan de opdracht
CREATE VIEW PERSONEEL(NAAM,ADRES,POSTCODE,WOONPLAATS) AS
SELECT NAAM,ADRES,POSTCODE,WOONPLAATS
FROM WERKNEMERS
Vervolgens geven we het bestuur van de personeelsvereniging toestemming om deze VIEW te
gebruiken (hoe dat werkt wordt later behandeld). Zij kunnen vanaf dat moment de VIEW PERSONEEL gewoon benaderen alsof het een tabel is. Het verschil tussen een VIEW en een tabel
hoeft voor de eindgebruiker dus helemaal niet aan de orde gesteld te worden. De
personeelsvereniging kan nu dus alle namen en adressen opvragen door op te geven:
SELECT *
FROM PERSONEEL
Het belangrijkste verschil met een tabel is eigenlijk dat van een VIEW alleen de definitie (de
CREATE VIEW opdracht dus) bewaard wordt en iedere keer als een gebruiker de VIEW wil
gebruiken zal deze opnieuw worden samengesteld.
Dat heeft niet alleen het voordeel dat de VIEW vrijwel geen ruimte in beslag neemt maar dit
zorgt er ook voor dat de VIEW steeds de meest recente gegevens bevat. Zolang de wijzigingen
maar verwerkt zijn in de WERKNEMERS tabel zullen die ook doorwerken in de VIEW
PERSONEEL.
Niet alleen bij het beperken van het aantal kolommen dat iemand mag zien maar ook om
sommige vragen wat eenvoudiger te kunnen formuleren, kunnen we gebruik maken van de
VIEW.
Stel dat er iemand in onze organisatie is die zeer vaak behoefte heeft aan een overzicht van de
CODE, de NAAM, het SALARIS en het aantal gewerkte UREN van de werknemers. Als hij of zij
gebruik maakt van onze database moet er om deze overzichten te kunnen produceren steeds
een JOIN gemaakt worden van de tabel WERKNEMERS en de tabel WERK. We zouden dit wat
eenvoudiger kunnen maken door de JOIN op te nemen in een VIEW.
CREATE VIEW PROJ_MED (MED_NR,NAAM,SALARIS,PROJECT,UREN) AS
SELECT WERKN EM ERS. CODE, NAAM,SAL, PROJ_ CODE, U REN
FROM WERKNEMERS,WERK
WHERE WERKNEMERS.CODE = WERK.CODE
SELECT *
FROM PROJ_MED
opgegeven worden om het gewenste overzicht te verkrijgen.
Ook voor het maken van rekenkundig bewerkte overzichten kunnen we de VIEW gebruiken. Als
we bijvoorbeeld vaak de behoefte hebben aan overzichten van WERKNEMERS met hun
jaarsalaris kunnen we opgeven
Hoofdstuk: VIEWS
Vanaf dat moment kan gewoon
7
1
Database SQL
1 mei 2010
CREATE VIEW SALARISSEN (MEDEW,PER_MAAND,PER_JAAR) AS
SELECT NAAM,SAL,SAL*12
FROM WERKNEMERS
Ook nu is het gevraagde overzicht eenvoudig te produceren door
SELECT *
FROM SALARISSEN
OPDRACHTEN
1. Geef de opdracht voor het maken van een VIEW genaamd ITCZ met daarin de namen
en telefoonnummers van alle medewerkers die aan het ITCZ project meewerken.
2. Geef de opdracht voor het maken van een VIEW genaamd HOOG_SAL waarin
opgenomen zijn de namen en volledige adressen van alle werknemers die meer
verdienen dan hun chef.
Het verwijderen van een VIEW
Het verwijderen van een VIEW is een vrij eenvoudige opdracht. Evenals bij het verwijderen van
een tabel en een index maken we ook hier weer gebruik van een DROP .. , opdracht.
DROP VIEW <naam van de view>
Hoofdstuk: VIEWS
Bij het verwijderen van een VIEW moeten we er rekening mee houden dat eventuele andere
VIEWs die als één van hun bronnen gebruik maken van de nu te verwijderen VIEW niet meer
geldig zijn en dus ook verwijderd moeten worden. Het is afhankelijk van de SQL implementatie
of deze verwijdering automatisch uitgevoerd wordt of dat dit handmatig gedaan moet worden.
7
2
Database SQL
1 mei 2010
GEGEVENS WIJZIGEN
De verschillende mogelijkheden
Naast het benaderen van een RDBMS om overzichten te produceren, willen we natuurlijk ook
nieuwe gegevens in kunnen voeren, gegevens kunnen wijzigen en gegevens kunnen
verwijderen. Dit kan in SQL met de opdrachten INSERT, UPDATE en DELETE. We zullen deze
mogelijkheden in dit hoofdstuk bespreken.
INSERT
Zoals we al hebben kunnen zien, kunnen we een tabel vullen met gegevens door gebruik te
maken van de INSERT opdracht. We hebben daar ook opgemerkt dat dit een vrij omslachtige
manier van werken is die bij de meeste RDBMS-en niet gebruikt zal worden maar vervangen zal
worden door een zogenaamde applicatiegenerator of 'scherm-bouw' mogelijkheid.
De INSERT opdracht is echter ook op een wat minder omslachtige manier te gebruiken. Met
name door voor het vullen van de tabel gebruik te maken van gegevens uit een andere tabel en
door middel van een SELECT opdracht de nieuwe tabel te vullen.
We zouden bijvoorbeeld (hoewel dat niet aan te raden is) een aparte tabel kunnen maken van
alle werknemers uit ROTTERDAM. We moeten dan uiteraard eerst de tabelstructuur definiëren
met een CREATE opdracht :
CREATE TABLE ROTTERDAM(NAAM CHAR(15) NOT NULL, ADRES CHAR(20),
POSTCODE CHAR(7), WOONPLAATS CHAR(20), TELEFOON CHAR(12))
Vervolgens kunnen we de WERKNEMERS tabel gebruiken als 'leverancier' van rijen.
INSERT INTO ROTTERDAM(NAAM, ADRES, POSTCODE, WOONPLAATS,
TELEFOON)
SELECT NAAM,ADRES,POSTCODE,WOONPLAATS, TELEFOON
FROM WERKNEMERS
WHERE WOONPLAATS = 'ROTTERDAM'
OPDRACHTEN
1. Geef de opdracht voor het aanmaken van een tabel genaamd PROJ_RDAM met daarin
opgenomen de kolommen naam, adres, postcode en woonplaats.
2. Geef de opdracht om deze tabel te vullen met de gegevens van alle werknemers die
aan een project in ROTTERDAM werken.
Hoofdstuk: GEGEVENS WIJZIGEN
Door gebruik te maken van een SELECT opdracht in het tweede gedeelte van de INSERT
opdracht vullen we de tabel ROTTERDAM met alle gegevens van alle werknemers die aan de
gestelde voorwaarde voldoen.
7
3
Database SQL
1 mei 2010
UPDATE
Een andere vorm van gegevensbewerking is de UPDATE opdracht waarmee we waarden in
kolommen kunnen wijzigen. Dit is uiteraard met name handig bij het doorvoeren van
wijzigingen als verhuizingen, salarisverhogingen en dergelijke.
Stel dat werknemer 26 van UTRECHT naar GELEEN gaat verhuizen, het nieuwe adres wordt:
BOTERMARKT 14 5989 HJ en het telefoonnummer wordt 046-4186530. Deze wijziging kunnen
we als volgt Invoeren:
UPDATE WERKNEMERS
SET
ADRES='BOTERMARKT 14',
POSTCODE='5989 Hr',
WOONPLAATS = 'GELEEN',
TELEFOON = '046-4186530'
WHERE
CODE = '26'
We kunnen de UPDATE opdracht ook gebruiken om een wijziging door middel van een
rekenkundige bewerking uit te voeren
UPDATE WERKNEMERS
SET SAL = SAL * 1.10
WHERE CHEF = '99'
OPDRACHTEN
Geef de opdrachten voor het aanbrengen van de volgende wijzigingen in de WERKNEMERS,
WERK, PROJECTEN en SCHALEN tabellen
1. Geef alle werknemers die meewerken aan het CAIA project een salarisverhoging van
15%.
2. Halveer het budget van alle projecten die geleid worden door werknemer 35.
3. Verander de postcode van werknemer MAAS in 3085 PD.
4. Geef alle medewerkers die aan een project werken waar ook medewerker 50 aan
meewerkt een salarisverhoging van 250 gulden.
5. Geef alle medewerkers die werken aan het project dat geleid wordt door werknemer
MERK een zelfde salaris als MERK.
Hoofdstuk: GEGEVENS WIJZIGEN
Zo wordt bij alle werknemers met als chef 99 een salarisverhoging van 10% doorgevoerd. Het
spreekt voor zich dat we in dit soort gevallen uiterst voorzichtig moeten zijn met het
formuleren van onze voorwaarden.
7
4
Database SQL
1 mei 2010
DELETE
Tenslotte moeten we ook de mogelijkheid hebben om rijen uit tabellen te verwijderen. Dat
kunnen we vrij eenvoudig doen door gebruik te maken van de DELETE opdracht.
We zouden bijvoorbeeld werknemer 40 kunnen wissen door in te geven:
DELETE FROM WERKNEMERS WHERE CODE = '40'
Twee dingen zijn hierbij van belang. In de eerste plaats moeten we uiteraard uitermate
zorgvuldig zijn met het formuleren van de voorwaarde. Relationele databases zijn wat dat
betreft vergelijkbaar met mensen: iedereen voldoet aan geen enkele voorwaarde en vrijwel iedereen voldoet aan onduidelijk omschreven voorwaarden! De opdracht
DELETE FROM WERKNEMERS
is dan ook een zeer snelle methode om alle rijen uit de WERKNEMERS tabel te verwijderen.
In de tweede plaats komt hier het begrip referentiële integriteit weer aan de orde. Zouden we
de gegevens van werknemer 40 uit de WERKNEMERS tabel wissen zonder daarbij eerst te
controleren of er in de tabel WERK geen rijen meer voorkomen met als CODE de waarde 40,
dan kan de situatie ontstaan dat we een aantal uren, besteed aan een project, hebben
geregistreerd waarvan we niet weten door wie ze zijn uitgevoerd. Daar zal de opdrachtgever
van het project niet zo blij mee zijn.
Hoofdstuk: GEGEVENS WIJZIGEN
OPDRACHTEN
Geef een opdracht in SQL voor het uitvoeren van de volgende opdrachten met de
WERKNEMERS, WERK, PROJECTEN en SCHALEN tabellen
1. Verwijder alle rijen uit de WERK tabel waar werknemer 26 bij betrokken is.
2. Verwijder werknemer 26 uit de WERKNEMERS tabel.
3. Verwijder alle werknemers die niet aan een project werken, behalve werknemer 99.
4. Verwijder alle gegevens over werknemers die nog nooit aan een project in UTRECHT
hebben gewerkt uit de WERK tabel.
5. Verwijder alle gegevens uit de WERK tabel van werknemers die aan een project werken
waar werknemer 26 nog nooit aan meegewerkt heeft.
7
5
Database SQL
1 mei 2010
GEGEVENS BEVEILIGEN
Vormen van gegevensbeveiliging
Uiteraard is het belangrijk om er zorg voor te dragen dat de gegevens in de database beveiligd
zijn tegen verlies of onbevoegd gebruik. We hebben het dan eigenlijk over twee soorten
gegevensbeveiliging: er mogen geen gegevens verloren raken als gevolg van één of andere
calamiteit: de bescherming tegen verlies dus. Bovendien mogen gegevens niet geraadpleegd
worden door personen die daar geen bevoegdheid voor hebben: de bescherming tegen
ongeoorloofd gebruik. Op beide vormen van beveiliging zal in dit hoofdstuk nader worden
ingegaan.
Bescherming tegen verlies
Vrijwel alle RDBMS-en kennen de mogelijkheid om gegevens te exporteren en dus op diskette
of band vast te leggen, zodat de gegevens op een veilige plaats opgeborgen kunnen worden (de
backup zoals dat in fraai Nederlands heet). Het spreekt voor zich dat dit van groot belang is; we
weten zeker dat iedere computer op een dag stuk gaat, we weten alleen niet wanneer!
Naast deze, in vrijwel ieder programma aan te treffen mogelijkheden, kennen we in vrijwel alle
RDBMS-en die met SQL werken de ROLLBACK en COMMIT mogelijkheden.
De wijzigingen die we aanbrengen als we een database met SQL benaderen, worden in het
werkgeheugen (in de buffers) uitgevoerd en nog niet daadwerkelijk weggeschreven naar het
externe geheugen (de disk).
Als er nu iets echt misgaat (we hebben een tabel met 89765 rijen in één opdracht volledig
gewist) kunnen we in veel gevallen met de opdracht
ROLLBACK
de wijzigingen weer ongedaan maken.
Bovendien houdt het RDBMS vaak een soort administratie bij (een logfile) waarin geregistreerd
wordt welke wijzigingen er allemaal plaatsvinden. Dit kan na een stroomstoring gebruikt
worden om de nog niet definitief doorgevoerde wijzigingen nogmaals uit te voeren.
COMMIT
geven. Dan worden alle wijzigingen ook daadwerkelijk weggeschreven en kunnen we ook geen
ROLLBACK meer uitvoeren.
Autorisatie van gebruik
Om gegevens te beschermen tegen gebruik door onbevoegden kennen we uiteraard de al
besproken VIEW. Toch is alleen het maken van VIEWs niet voldoende. Vandaar dat in RDBMSen die met SQL werken een geheel aan bevoegdheden en bevoegdheidsniveaus is opgenomen
waardoor het mogelijk wordt om gegevens afdoende te beschermen tegen ongeoorloofd
gebruik.
Hoofdstuk: GEGEVENS BEVEILIGEN
Als we er op een bepaald moment echt zeker van willen zijn dat alle wijzigingen ook op disk
worden weggeschreven kunnen we de opdracht
7
6
Database SQL
1 mei 2010
Om te beginnen zal iedere gebruiker bij het RDBMS bekend moeten zijn voordat hij of zij ook
maar 'verbinding' kan krijgen met het RDBMS. Dat betekent dat iedere gebruiker zich bekend
moet maken aan het RDBMS door middel van een gebruikers naam en een wachtwoord. Een
gebruiker die daartoe de bevoegdheid heeft, kan nieuwe gebruikers introduceren door gebruik
te maken van
GRANT CONNECT TO <gebruikersnaam> IDENTIFIED BY <wachtwoord>
Zo zouden we dus een nieuwe gebruiker KARIN met als wachtwoord SNOOPY als volgt kunnen
introduceren
GRANT CONNECT TO KARIN IDENTIFIED BY SNOOPY
Vanaf dat moment kan KARIN gebruik maken van het RDBMS en tabellen en dergelijke
raadplegen waarvoor haar ook weer apart toestemming is verleend. Als dat niet gebeurd is,
kan zij dus nog niets!
We kunnen andere gebruikers het recht geven om bepaalde bewerkingen uit te voeren op
tabellen waarvan wij eigenaar zijn (dat wil zeggen dat we de CREATE opdracht hebben
gegeven).
Een andere gebruiker zou dus KARIN de bevoegdheid kunnen geven om een tabel te gaan
gebruiken. Dit verlenen van bevoegdheden omvat een aantal mogelijkheden. We kunnen de
volgende bevoegdheden verlenen
SELECT
INSERT
UPDATE
DELETE
ALTER
INDEX
Iemand mag de tabel raadplegen
Iemand mag rijen toevoegen
Iemand mag wijzigingen aanbrengen
Iemand mag rijen verwijderen
Iemand mag de structuur van de tabel wijzigen
Iemand mag indices aanmaken bij de tabel
Zo zou iemand KARIN dus toestemming kunnen geven om de tabe WERKNEMERS te raadplegen
door in te geven
Uiteraard hoeven we hier geen wachtwoord toe te voegen.
De UPDATE bevoegdheid kan zelfs per kolom gegeven worden. Dus we zouden KARIN
toestemming kunnen geven om in de kolom SAL uit de tabel WERKNEMERS wijzigingen aan te
brengen
GRANT UPDATE ON WERKNEMERS(SAL) TO KARIN
Als we KARIN alle bevoegdheden voor de tabel WERKNEMERS willen geven, hoeven we
gelukkig niet alle mogelijkheden op te sommen maar kunnen we volstaan met
GRANT ALL ON WERKNEMERS TO KARIN
We kunnen deze bevoegdheden aan alle andere gebruikers verlener met de opdracht
GRANT ALL ON WERKNEMERS TO PUBLIC
Hoofdstuk: GEGEVENS BEVEILIGEN
GRANT SELECT ON WERKNEMERS TO KARIN
7
7
Database SQL
1 mei 2010
Ook kunnen we aangeven dat KARIN de aan haar verleende bevoegdheden weer aan andere
gebruikers mag doorgeven
GRANT ALL ON WERKNEMERS TO KARIN WITH GRANT OPTION
In de situatie die nu is ontstaan kan KARIN nog steeds niets anders doen dan de tabel
WERKNEMERS gebruiken. Om zelf ook tabellen aan te kunnen maken moet zij een hogere
bevoegdheid krijgen. Dat doen we door de volgende opdracht op te geven
GRANT RESOURCE TO KARIN
We kunnen KARIN ook de hoogst mogelijke bevoegdheid geven met de opdracht
GRANT DBA TO KARIN
Vanaf dat moment heeft KARIN de Database Administrator bevoegdheid en kan nu ook nieuwe
gebruikers introduceren, tabellen van anderen raadplegen en bewerken, wachtwoorden van
andere gebruikers opvragen enzovoort.
Wel moeten we er rekening mee houden dat een tabel die we niet zelf aangemaakt hebben,
voorafgegaan moet worden door de naam van de eigenaar van die tabel. Stel dat gebruiker
PETER aan KARIN de bevoegdheid heeft verleend om de tabel BESTELLINGEN (die hij
aangemaakt heeft) te raadplegen door
GRANT SELECT ON BESTELLINGEN TO KARIN
dan zal KARIN, als zij opgeeft
SELECT * FROM BESTELLINGEN
de mededeling krijgen dat deze tabel niet bestaat! Zij moet opgeven
SELECT * FROM PETER. BESTELLINGEN
Dit probleem kan KARIN oplossen door gebruik te maken van een synoniem voor die tabel.
Bijvoorbeeld
CREATE SYNONYM BESTELLINGEN FOR PETER.BESTELLINGEN
SELECT * FROM BESTELLINGEN
Alle hiervoor genoemde bevoegdheden kunnen iemand weer ontnomen worden door gebruik
te maken van
REVOKE <bevoegdheid> FROM <gebruikersnaam>
Zo zouden we KARIN de toegang tot de database weer kunnen ontzeggen door
REVOKE CONNECT FROM KARIN
of (iets minder grof)
REVOKE SELECT ON BESTELLINGEN FROM KARIN
Hoofdstuk: GEGEVENS BEVEILIGEN
Vanaf dat moment kan zij gewoon opgeven
7
8
Database SQL
1 mei 2010
VRAGEN
1. Waarom zouden we geen wachtwoord op moeten geven bij het verlenen van
toestemming aan een andere gebruiker om onze tabel(len) te raadplegen?
Hoofdstuk: GEGEVENS BEVEILIGEN
OPDRACHTEN
1. Introduceer de nieuwe gebruiker SCOTT met als wachtwoord TIGER.
2. Geef gebruiker SCOTT toestemming om de gehele tabel WERK te raadplegen.
3. Geef gebruiker SCOTT toestemming om de telefoonnummers van werknemers in de
WERKNEMERS tabel te wijzigen.
4. Geef gebruiker SCOTT toestemming om zelf tabellen aan te maken
7
9