Kan je het niet vinden? Gebruik CTRL + F en zoek dat op wat je nodig bent.

Inhoud
Inleiding ………………………………………………………………………………………………………………………………… 5
Wat is SQL? ………………………………………………………………………………………………………………………… 5
Standaarden ………………………………………………………………………………………………………………………. 5
Oracle ……………………………………………………………………………………………………………………………….. 5
Soorten SQL commando’s ……………………………………………………………………………………………………. 6
Inloggegevens MSSQL …………………………………………………………………………………………………………. 7
Vragen en opdrachten …………………………………………………………………………………………………………. 7
RDBMS ………………………………………………………………………………………………………………………………….. 8
Samengevat ……………………………………………………………………………………………………………………….. 8
Eigenschappen van tabellen in een relationele database: ………………………………………………………. 11
Termen ……………………………………………………………………………………………………………………………. 12
Goede gewoontes …………………………………………………………………………………………………………….. 12
Sectie 1 ……………………………………………………………………………………………………………………………….. 13
SELECT …………………………………………………………………………………………………………………………….. 13
Aliassen ………………………………………………………………………………………………………………………… 14
Rekenkundige operatoren ………………………………………………………………………………………………. 16
NULL ……………………………………………………………………………………………………………………………….. 18
TOP …………………………………………………………………………………………………………………………………. 19
Vragen …………………………………………………………………………………………………………………………….. 20
Opdrachten sectie 1 ………………………………………………………………………………………………………….. 22
Sectie 2 ……………………………………………………………………………………………………………………………….. 27
Describe …………………………………………………………………………………………………………………………… 27
sp_columns …………………………………………………………………………………………………………………… 27
sp_help ………………………………………………………………………………………………………………………… 27
CONCAT …………………………………………………………………………………………………………………………… 28
DISTINCT ………………………………………………………………………………………………………………………….. 32
WHERE …………………………………………………………………………………………………………………………….. 34
BETWEEN … AND …………………………………………………………………………………………………………… 37
IN ………………………………………………………………………………………………………………………………… 39
Werkboek databases en SQL (ROC van Twente)

2
LIKE ……………………………………………………………………………………………………………………………… 39
IS NULL …………………………………………………………………………………………………………………………. 42
IS NOT NULL ………………………………………………………………………………………………………………….. 43
Vragen sectie 2 …………………………………………………………………………………………………………………. 45
Opdrachten sectie 2 ………………………………………………………………………………………………………….. 47
Sectie 3 ……………………………………………………………………………………………………………………………….. 51
Logische operatoren ………………………………………………………………………………………………………….. 51
AND ……………………………………………………………………………………………………………………………… 51
OR ……………………………………………………………………………………………………………………………….. 52
NOT ……………………………………………………………………………………………………………………………… 53
Volgorde ………………………………………………………………………………………………………………………. 53
ORDER BY ………………………………………………………………………………………………………………………… 56
DESCENDING ………………………………………………………………………………………………………………… 61
Vragen sectie 3 …………………………………………………………………………………………………………………. 64
Opdrachten sectie 3 ………………………………………………………………………………………………………….. 67
Sectie 4 ……………………………………………………………………………………………………………………………….. 72
Tekstfucties ……………………………………………………………………………………………………………………… 72
Lower …………………………………………………………………………………………………………………………… 72
Upper …………………………………………………………………………………………………………………………… 73
Concat ………………………………………………………………………………………………………………………….. 74
Replace ………………………………………………………………………………………………………………………… 75
Substring ………………………………………………………………………………………………………………………. 76
Left ………………………………………………………………………………………………………………………………. 77
Right…………………………………………………………………………………………………………………………….. 78
Rekenfuncties …………………………………………………………………………………………………………………… 79
Round ………………………………………………………………………………………………………………………….. 79
Ceiling ………………………………………………………………………………………………………………………….. 79
Floor …………………………………………………………………………………………………………………………….. 80
Datumfuncties ………………………………………………………………………………………………………………….. 80
Getdate ………………………………………………………………………………………………………………………… 80
DayName ……………………………………………………………………………………………………………………… 81
Month ………………………………………………………………………………………………………………………….. 81
Year ……………………………………………………………………………………………………………………………… 81
DateAdd ……………………………………………………………………………………………………………………….. 82
DateDiff ……………………………………………………………………………………………………………………….. 83
Format …………………………………………………………………………………………………………………………. 83
Werkboek databases en SQL (ROC van Twente)

3
Groepsfuncties …………………………………………………………………………………………………………………. 84
Min ……………………………………………………………………………………………………………………………… 85
Max ……………………………………………………………………………………………………………………………… 85
Sum ……………………………………………………………………………………………………………………………… 86
Avg ………………………………………………………………………………………………………………………………. 87
Count …………………………………………………………………………………………………………………………… 88
Vragen sectie 4 …………………………………………………………………………………………………………………. 89
Opdrachten sectie 4 ………………………………………………………………………………………………………….. 92
Sectie 5 ……………………………………………………………………………………………………………………………….. 97
Datatypes ………………………………………………………………………………………………………………………… 97
Numeriek ……………………………………………………………………………………………………………………… 97
Alfanumeriek ………………………………………………………………………………………………………………… 98
Datums ………………………………………………………………………………………………………………………… 98
Conversie …………………………………………………………………………………………………………………………. 99
Nesten …………………………………………………………………………………………………………………………… 101
Null functies ……………………………………………………………………………………………………………………. 101
ISNULL ………………………………………………………………………………………………………………………… 101
COALESCE …………………………………………………………………………………………………………………… 105
CASE ………………………………………………………………………………………………………………………………. 107
Vragen sectie 5 ……………………………………………………………………………………………………………….. 108
Opdrachten sectie 5 ………………………………………………………………………………………………………… 110
Database Design …………………………………………………………………………………………………………………. 116
Inleiding …………………………………………………………………………………………………………………………. 116
Database maken ……………………………………………………………………………………………………………… 116
Tabellen aanmaken …………………………………………………………………………………………………………. 118
Gegevens toevoegen aan tabellen …………………………………………………………………………………….. 123
Sectie 6 ……………………………………………………………………………………………………………………………… 127
Joins ………………………………………………………………………………………………………………………………. 127
CROSS JOIN …………………………………………………………………………………………………………………….. 129
INNER JOIN …………………………………………………………………………………………………………………….. 131
OUTER JOIN ……………………………………………………………………………………………………………………. 133
LEFT OUTER JOIN …………………………………………………………………………………………………………. 135
RIGHT OUTER JOIN ………………………………………………………………………………………………………. 136
FULL OUTER JOIN …………………………………………………………………………………………………………. 138
SELF JOIN ……………………………………………………………………………………………………………………….. 140
Vragen sectie 6 ……………………………………………………………………………………………………………….. 142
Werkboek databases en SQL (ROC van Twente)

4
Opdrachten sectie 6 ……………………………………………………………………………………………………………. 144
Sectie 7 ……………………………………………………………………………………………………………………………… 150
Subqueries ……………………………………………………………………………………………………………………… 150
Single-row subqueries ………………………………………………………………………………………………….. 152
Multiple-row ……………………………………………………………………………………………………………….. 154
Exists ………………………………………………………………………………………………………………………….. 155
Groepsfuncties ……………………………………………………………………………………………………………. 159
Vragen sectie 7 ……………………………………………………………………………………………………………….. 161
Opdrachten sectie 7 ………………………………………………………………………………………………………… 163
Sectie 8 ……………………………………………………………………………………………………………………………… 168
GROUP BY ………………………………………………………………………………………………………………………. 168
Null waarden ………………………………………………………………………………………………………………. 171
Where ………………………………………………………………………………………………………………………… 172
Order by ……………………………………………………………………………………………………………………… 173
Vragen sectie 8 ……………………………………………………………………………………………………………….. 177
Opdrachten sectie 8 ………………………………………………………………………………………………………… 180

Inleiding
Wat is SQL?
SQL is een standaardtaal om data in een database op te slaan, te bewerken en op te
halen. Met deze cursus leer je SQL te gebruiken in MySQL, SQL Server, MS Access, Oracle
en andere databasesystemen. SQL staat voor Structured Query Language. Een query is
een vraag in het Engels. Als je een query maakt, stel je dus eigenlijk een vraag aan de
database. Maar een query kan ook bedoeld zijn om iets te veranderen aan de database,
bijvoorbeeld om een wijziging in een record aan te brengen of om een record toe te
voegen. Dat wijzigen en toevoegen van records zit niet in deze cursus. Er wordt enkel
geleerd hoe je een bestaande database kunt bevragen met SQL.
Standaarden
We gaan werken volgens de ANSI-standaard. ANSI staat voor American National
Standards Institute. Dit instituut bestaat al sinds 1918 en het beheert en administreert
standaarden en afspraken. SQL is de standaard op het gebied van Relational Database
Management Systems. Deze taal werd oorspronkelijk ontworpen door IBM halverwege de
jaren 70 van de vorige eeuw. In de jaren 80 werd het al veel gebruikt en in 1986 werd het
een standaard volgens ANSI. Tot op heden zijn er drie ANSI-standaarden van SQL
geregistreerd. Ze worden genoemd naar het jaar waarin ze voor het eerst werden
voorgesteld en ze zijn bekend onder de namen ANSI-86, ANSI-92 en ANSI-99.
Oracle
We maken gebruiken van de employees tabellen van Oracle. Het is een database met
twintig werknemers die hier en daar een klein beetje aangepast is ten behoeve van deze
cursus. De gegevens zijn opgeslagen in acht tabellen:

employees
departments
jobs
locations
job_grades
regions
countries
job_history
De relaties tussen deze tabellen kunnen we in een database diagram weergeven:
Werkboek databases en SQL (ROC van Twente)
5
employees
employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id
locations
location_id
street_address
postal_code
departments
department_id
department_name
city
state_province
country_id
countries
manager_id
location_id
regions
region_id
region_name
country_id
country_name
region_id
job_history
employee_id
start_date
end_date
job_id
department_id
jobs
job_id
job_title
min_salary
max_salary
De volgorde en de inhoud van de onderwerpen is grotendeels gebaseerd op de
Engelstalige cursus Database Programming with SQL van Oracle die docenten en
leerlingen gratis kunnen doen bij de Oracle Academy. De onderdelen die enkel in een
Oracle omgeving werken zijn weggelaten. Alles wat hier geleerd wordt, is volgens de
ANSI-standaard.
Soorten SQL commando’s
Er zijn verschillende soorten SQL commando’s. De belangrijkste categorieën met
voorbeelden tussen haakjes:

DDL – Data Definition Language (CREATE TABLE – ALTER TABLE – DROP TABLE
etc.)
DML – Data Manipulation Language (INSERT – UPDATE – DELETE)
DQL – Data Query Language (SELECT)
DCL – Data Control Language (GRANT – REVOKE etc.)
Nogmaals: in deze cursus werken we grotendeels met Data Query Language, dus alleen
maar met het bevragen van een database.
Werkboek databases en SQL (ROC van Twente)
6
Inloggegevens MSSQL
Alle query’s in deze cursus kunnen geprobeerd worden via tabellen in een Microsoft SQL
omgeving. De bijbehorende database kun je op je eigen computer installeren met een
script dat via je docent beschikbaar is. Het is ook mogelijk de opdrachten uit te voeren
op een database in de cloud. Inloggegevens hiervoor krijg je van je docent.
Vragen en opdrachten
In het materiaal worden voortdurend voorbeelden gegeven van query’s. Het is de
bedoeling dat je deze query’s uitprobeert in de database, zodat je het juiste gevoel krijgt
met het maken van query’s. Het heeft uiteraard geen nut de query’s vanuit de cursus te
kopiëren naar de database; je moet ze echt zelf typen, fouten maken en foutmeldingen
krijgen!
Elke sectie wordt afgesloten met een aantal vragen. Je moet nadenken over de
bestudeerde stof. Jouw uitwerkingen lever je in volgens de procedure die door jouw
docent is aangegeven. Daarna volgen er opdrachten die uitgevoerd moeten worden
met behulp van query’s op de database. De antwoorden moeten ter controle worden
ingeleverd bij de docent. Bij de eerste secties zijn de opdrachten vrij eenvoudig, maar ze
worden steeds moeilijker en uitgebreider. De ervaring leert dat het op dat moment beter
is om de opdrachten in groepjes te maken en inleveren. Je kunt dan samen het probleem
oplossen.
Het moet in principe mogelijk geheel zelfstandig deze cursus door te nemen. De cursus
wordt afgesloten met een toets voor een cijfer.
Werkboek databases en SQL (ROC van Twente)
7
RDBMS
Op YouTube staat een instructief filmpje met uitleg over DBMS en SQL.
Zoals je in het filmpje hebt kunnen zien, is het relationele model het meest gebruikte
DBMS. Het centrale begrip in de relationele gegevensstructuur is de tabel of relatie
(vandaar de naam van het model). Een tabel wordt beschouwd als een verzameling rijen
of tupels. Alle gegevens van een relationele database liggen vast in de vorm van
kolomwaarden binnen een rij van een tabel. De enige manier om in een relationele
database gegevens met elkaar in verband te brengen bestaat uit de vergelijking van
kolomwaarden. Er bestaan op zich geen ingebakken verbanden tussen tabellen
onderling.
Samengevat
1.Een database is een verzameling tabellen.
2.Een tabel is een verzameling rijen.
3.Een rij is een verzameling kolomwaarden.
Als je gewend bent te werken met Excel zul je de structuur herkennen. Een Excel
werkblad kan verschillende tabbladen hebben met op elk tabblad een tabel. In elke tabel
zie je rijen en kolommen met op de bovenste rij de kolomnamen. Hieronder zie je de
tabel employees van Oracle in Excel. Je kunt de tabellen in Excel
formaat hier downloaden. Ze staan dan bij elkaar op één werkblad. Het is handig een
exemplaar op papier te hebben terwijl je met de cursus bezig bent.
Werkboek databases en SQL (ROC van Twente)
8
Laten we maar eens een vraag stellen aan de database. Neem bijvoorbeeld de volgende
query. We vragen alle informatie op van de tabel countries.
SELECT *
FROM countries;
Dat levert onderstaand resultaat op. In deze tabel is country_id een primary key. Dat is
een uniek veld en er moet een waarde zijn, elk land heeft een ander id van twee
letters. Het veld region_id is in deze tabel een foreign key. Een foreign key is een veld dat
verwijst naar een primary key in een andere tabel. Je herkent de primary keys en foreign
keys in het Excel werkblad aan de kleuren van de namen van de kolommen. Als de kleur
hetzelfde is, is er een verwijzing naar een andere tabel waar dezelfde kleur voorkomt.
Werkboek databases en SQL (ROC van Twente)
9
Met onderstaande query vragen we alle informatie op van de tabel regions.
SELECT *
FROM regions;
En dan zie je dat de region_id’s een region_name hebben.
Nog een voorbeeld met de tabel employees.
SELECT *
FROM employees;
Werkboek databases en SQL (ROC van Twente)
10
Er zijn vier kolommen
met _id, namelijk employee_id, job_id, manager_id en department_id. De primary key van
de tabel employees is employee_id en de andere id’s geven de relatie aan die er bestaat
met deze tabel of met een andere tabel. De kolom job_id verwijst naar de
tabel jobs, manager_id verwijst naar de tabel employees en de
kolom department_id verwijst naar de tabel departments.
Als er employees toegevoegd worden, kunnen de namen van werknemers toevalligerwijs
best hetzelfde zijn, er zijn immers mensen met exact dezelfde voor- en achternaam, maar
er zijn nooit twee dezelfde employee_id’s. Een primary key kan één kolom zijn of een
combinatie van kolommen. In deze cursus is de primary key van de tabellen altijd maar
één kolom en hij staat in deze database altijd in de eerste kolom. Vaak is een primary
key numeriek en wordt hij automatisch opgehoogd (auto increment), maar het hoeft dus
niet, want dat heb je bij de tabel countries gezien. Daar worden twee letters gebruikt.
Eigenschappen van tabellen in een relationele database:
1.Er staat maar één waarde in een kolom.
2.De waarden van een kolom zijn van hetzelfde soort.
3.Elke rij is uniek.
4.De volgorde van de kolommen doet er niet toe.
5.De volgorde van de rijen doet er niet toe.
6.Elke kolom heeft een unieke naam.
Werkboek databases en SQL (ROC van Twente)
11
Termen

Een keyword verwijst naar een individueel SQL commando.
SELECT / FROM / etc.
Een clause is een onderdeel van een SQL statement.
SELECT last_name
Een statement is een combinatie van meerdere clauses.
SELECT last_name FROM employees
Goede gewoontes
Een statement sluit je af met een puntkomma.
Een query plaats je op verschillende regels, bijvoorbeeld:
o Op de eerste regel SELECT.
o Op de tweede regel FROM.
o Op de derde regel WHERE.
o Op de vierde regel ORDER BY.
Gebruik geen spaties in kolomnamen. Als je meerdere woorden hebt voor de
kolomnaam verbind je de woorden met een underscore (liggend streepje).
Werkboek databases en SQL (ROC van Twente)
12
Sectie 1
SELECT
Met SELECT kun je data selecteren van een database. Met FROM geef je aan uit welke
tabel de informatie gehaald moet worden. En met WHERE kun je voorwaarden stellen aan
de selectie.
Om alle velden van een database op te halen, gebruik je de volgende query.
SELECT * FROM tabelnaam;
SELECT *
FROM countries;
Je kunt ook een beperkt aantal kolommen ophalen. Dat doe je als volgt.
SELECT kolom1, kolom2, …
FROM tabelnaam;
SELECT first_name, last_name, job_id
FROM employees;
Werkboek databases en SQL (ROC van Twente)
13
De voorwaarde waaraan voldaan moet worden, stel je met WHERE.
SELECT kolom1, kolom2, …
FROM tabelnaam
WHERE …;
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = ‘SA_REP’;
Aliassen
Werkboek databases en SQL (ROC van Twente)
14
Standaard worden de kolomnamen gehanteerd zoals die op de SELECT regel bij de
kolomnamen staan. De kolomnamen kun je aanpassen door een alias toe te voegen met
het commando AS.
SELECT last_name AS achternaam, salary AS maandsalaris, salary12 AS jaarsa laris FROM employees; Het werkt ook zonder het woord AS. SELECT last_name achternaam, salary maandsalaris, salary12 jaarsalaris
FROM employees;
Werkboek databases en SQL (ROC van Twente)
15
Als je meerdere woorden gebruikt met spaties voor een kolomnaam moet je dubbele
aanhalingstekens gebruiken.
SELECT last_name AS “achternaam werknemer”, salary AS “per maand”, salary1 2 AS “per jaar” FROM employees; Hetzelfde bereik je met enkele aanhalingstekens. SELECT last_name AS ‘achternaam werknemer’, salary AS ‘per maand’, salary1
2 AS ‘per jaar’
FROM employees;
En zonder AS.
SELECT last_name ‘achternaam werknemer’, salary ‘per maand’, salary*12 ‘per
jaar’
FROM employees;
Maar het gaat fout als je een alias hebt van meerdere woorden en je geen dubbele of
enkele aanhalingstekens gebruikt! Het is in de praktijk zeer ongebruikelijk kolomnamen
te gebruiken met daarin een spatie. Er wordt geadviseerd enkel kleine letters te
gebruiken voor de kolomnamen en woorden te verbinden met een underscore als er
meerdere woorden gebruikt worden. Gebruik dus achternaam_werknemer in plaats
van “Achternaam werknemer” of ‘Achternaam werknemer’.
Rekenkundige operatoren
Met kolommen die getallen bevatten kun je rekenen. SQL kent vier rekenkundige
operatoren. Je gebruikt daarbij de volgende symbolen:
Werkboek databases en SQL (ROC van Twente)
16
optellen – aftrekken
vermenigvuldigen
/ delen
De volgorde waarin je berekeningen moet toepassen is: * / + –
Dus eerst vermenigvuldigen, dan delen, dan optellen en tenslotte aftrekken.
Bij 12 * salary + 100 moet je eerst 12 keer salary nemen en daar 100 bij optellen.
Je kunt haakjes gebruiken om de volgorde te forceren, net zoals je dat bij wiskunde hebt
geleerd.
Bij 12 * (salary + 100) moet je vanwege de haakjes eerst bij het salaris 100 optellen en dat
met 12 vermenigvuldigen.
SELECT last_name, salary, 12 * salary + 100, 12 * (salary + 100)
FROM employees
WHERE last_name = ‘King’;
SELECT last_name AS achternaam, salary AS maandsalaris, salary * 12 AS jaar
salaris
FROM employees;
Als alle werknemers bijvoorbeeld 10% opslag krijgen, kun je dat als volgt doen.
Werkboek databases en SQL (ROC van Twente)
17
SELECT last_name AS werknemer, salary AS huidige_salaris, salary * 0.1 AS o
pslag, salary + salary * 0.1 AS nieuwe_salaris
FROM employees;
Rekenkundige operatoren kunnen alleen op getallen worden toegepast op één
uitzondering na: ook met datums kun je rekenen. Als je twee datums van elkaar aftrekt,
krijg je als resultaat het verschil in dagen. Bovendien kun je bij een datum met behulp
van een getal een aantal dagen optellen.
NULL
Bij SQL is NULL een belangrijk woord. Als de waarde NULL is, is de inhoud onbeschikbaar,
niet bekend of niet van toepassing. NULL is niet hetzelfde als 0 of een spatie, want een 0
is een getal en een spatie is een karakter. Maar soms weet je de inhoud van een kolom
niet. We zeggen dan dat de inhoud NULL is.
SELECT last_name, job_id, salary, commission_pct
FROM employees;
Werkboek databases en SQL (ROC van Twente)
18
Als je vermenigvuldigt met NULL is het resultaat ook NULL. Delen door 0 mag niet, maar
delen door NULL is wel mogelijk. De inhoud van een deling door NULL is ook NULL.
SELECT last_name, job_id, salary, commission_pct, salary * commission_pct,
salary / commission_pct
FROM employees;
TOP
Met TOP kun je bepalen hoeveel rijen er getoond moeten worden.
SELECT TOP(5) last_name, salary
FROM employees;
Werkboek databases en SQL (ROC van Twente)
19
De eerste vijf rijen van de ongesorteerde tabel worden getoond. Ze komen in een
ongedefinieerde volgorde terug, vaak hoe ze fysiek op de disk zijn opgeslagen.
Vragen
Vraag 1:
De afkorting van SQL staat voor Standard Query Language.
a. juist
b. onjuist
Vraag 2:
Hoe toon je alle rijen van de tabel employees?
a. SELECT all FROM employees
b. SELECT * FROM employees
c. DESC employees
d. DESC * FROM employees
Vraag 3:
Hoe zullen de koppen van de kolommen er uitzien bij de volgende query?
SELECT bear_id bears, color AS Color, age “Age”
FROM animals;
a. bears, color, Age
b. BEARS, Color, AGE
c. BEARS, color, Age
d. bears, Color, Age
Vraag 4:
Niet alle employees hebben een commission_pct. Bij de werknemers die
geen commission_pct hebben, staat er dan NULL in de kolom. Zal onderstaande query
werken of niet?
SELECT last_name, job_id, salary, commission_pct, salary / commission_pct
FROM employees;
a. ja
b. nee, delen door 0 mag niet
Werkboek databases en SQL (ROC van Twente)
20
Vraag 5:
Hoe toon je de eerste tien rijen van de tabel employees?
a. SELECT TOP(10) * FROM employees
b. SELECT * FROM employees<10
c. SELECT FROM employees LIMIT 10
d. SELECT 10 FROM employees
Vraag 6:
a. De volgorde van de kolommen in een tabel van een relationele database doet er niet
toe.
b. De volgorde van de rijen in een tabel van een relationele database doet er niet toe.
Wat is er waar?
a. zowel a als b zijn juist
b. zowel a als b zijn onjuist
c. a is juist en b is onjuist
d. a is onjuist en b is juist
Vraag 7:
Neem de volgende query:
SELECT first_name AS voornaam
FROM employees;
In deze query kun je het woordje AS op de bovenste regel gewoon weglaten en dan krijg
je exact hetzelfde resultaat.
a. waar
b. onwaar
Vraag 8:
Wat is de basis waarmee data wordt opgeslagen in een database?
a. een kolom
b. een rij
c. een cel
d. een tabel
Vraag 9:
Welk getal komt er uit deze query?
SELECT 8 / 4 + 2 * 10;
a. 22
b. 40
c. De query werkt niet.
d. Net zo vaak als er rijen zijn in de tabel wordt de uitkomst van de berekening gegeven.
Werkboek databases en SQL (ROC van Twente)
21
Vraag 10:
Een primary key verwijst naar een foreign key in een andere tabel.
a. juist
b. onjuist
Opdrachten sectie 1
Query 1: Toon alles van de tabel countries, zonder de kolomnamen te noemen op de
bovenste regel van het SELECT statement.
Query 2: Toon alle voornamen en achternamen van de tabel employees.
Werkboek databases en SQL (ROC van Twente)
22
Query 3: Toon job_title, het maximum salaris, het minimum salaris en het verschil tussen
die twee van de tabel jobs.
Werkboek databases en SQL (ROC van Twente)
23
Query 4: Toon enkel de naam van het land van de bovenste rij van de tabel countries.
Query 5: In de tabel employees staan de maandsalarissen. Maak een overzicht met
de jaarsalarissen en toon enkel de eerste vijf rijen. Let op de kolomnamen.
Query 6: Alle personeelsleden van de tabel employees krijgen 300 dollar opslag. Maak
een query voor onderstaande tabel.
Werkboek databases en SQL (ROC van Twente)
24
Query 7: Alle personeelsleden van de tabel employees krijgen 5% opslag. Maak een
query voor onderstaande tabel. In de Amerikaanse notatie bij getallen schrijf je een punt
waar wij een komma zetten.
Query 8: Hoeveel verdient King per dag als we uitgaan van 30 dagen in een maand?
Toon van de tabel employees enkel de eerste rij hiervoor zonder een WHERE te
gebruiken.
Query 9: Maak een overzicht zoals hieronder. Het zijn drie van de vier werknemers van
de tabel employees die commissie krijgen over hun salaris. Je toont maar drie rijen. De
bedragen zijn niet afgerond.
Werkboek databases en SQL (ROC van Twente)
25
Werkboek databases en SQL (ROC van Twente)
26
Sectie 2
Describe
Het kan belangrijk zijn dat je de precieze structuur van een tabel weet. Bijvoorbeeld als je
nieuwe rijen wil invoegen in de tabel. Je moet dan weten van welk type data elke kolom
is en of een kolom leeg mag worden gelaten of niet.
In SQL Server bestaan diverse mogelijkheden om de structuur van een tabel in je
database te bekijken. SQL Server kent hiervoor enkele system stored procedures:
sp_columns
In onderstaand voorbeeld vragen we de kolommen op van de tabel departments (niet alle
kolommen zijn te zien in de screenshot).
sp_columns departments;
In de kolom Nullable kun je zien dat het veld department_id niet mag worden
leeggelaten als er nieuwe rijen worden toegevoegd (waarde 0 betekent false). Dat veld
dient namelijk als primary key.
sp_help
We kunnen ook de stored procedure sp_help gebruiken. We krijgen dan nog heel wat meer
informatie over de tabel:
sp_help departments;
Werkboek databases en SQL (ROC van Twente)
27
CONCAT
Stel dat je het employee_id, de volledige naam en het e-mailadres wil laten zien van de
personeelsleden.
SELECT employee_id, first_name, last_name, email
FROM employees;
In dit geval krijg je twee aparte kolommen bij de naam. Eentje voor de voornaam en
eentje voor de achternaam. Je kunt die twee kolommen tonen in één kolom met de
CONCAT opdracht. Je plakt als het ware de voornaam vast aan de achternaam.
Werkboek databases en SQL (ROC van Twente)
28
SELECT employee_id, CONCAT(first_name, last_name), email
FROM employees;
Nu wil je uiteraard nog een spatie hebben tussen de voornaam en de achternaam. Dat
doe je door een spatie te plaatsen tussen enkele aanhalingstekens
tussen first_name en last_name.
SELECT employee_id, CONCAT(first_name, ‘ ‘, last_name), email
FROM employees;
Werkboek databases en SQL (ROC van Twente)
29
Je ziet dat de tweede kolom de naam krijgt van de concatenatie. Het is daarom wel zo
netjes de kolomnaam aan te passen.
SELECT employee_id nummer, CONCAT(first_name, ‘ ‘, last_name) AS naam, emai
l
FROM employees;
Werkboek databases en SQL (ROC van Twente)
30
Het is mogelijk zinnen te genereren met concatenatie en het gebruik van
aanhalingstekens.
SELECT CONCAT(last_name, ‘ verdient ‘, salary, ‘ dollar per maand.’) AS ‘ma
andsalaris_per_werknemer’
FROM employees;
Werkboek databases en SQL (ROC van Twente)
31
Het is ook mogelijk de concatenatie functie te gebruiken met behulp van het plus-teken:
SELECT first_name + ‘ ‘ + last_name AS naam
FROM employees;
DISTINCT
Stel dat je alle department_id’s wil zien van de tabel employees.
SELECT department_id
FROM employees;
Werkboek databases en SQL (ROC van Twente)
32
Je ziet dat er dubbele waarden te zien zijn. Om te voorkomen dat er dubbele waarden
worden getoond, kun je DISTINCT gebruiken. DISTINCT komt meteen na SELECT. Merk
op dat NULL ook één keer wordt getoond met DISTINCT als er rijen met NULL zijn.
SELECT DISTINCT department_id
FROM employees;
Werkboek databases en SQL (ROC van Twente)
33
WHERE
Om een query te maken heb je minstens een SELECT en een FROM nodig. Met WHERE
kun je het aantal rijen dat getoond moet worden beperken. De WHERE bevat een
bepaalde voorwaarde waaraan voldaan moet worden.
Met onderstaande query toon je alle employees.
SELECT employee_id, first_name, last_name
FROM employees;
Door een WHERE toe te voegen kun je het aantal rijen beperken. Stel dat je alleen
gegevens wil tonen van het personeelslid King.
SELECT employee_id, first_name, last_name
FROM employees
WHERE last_name = ‘King’;
Als je zijn employee_id weet, kan het ook zo:
Werkboek databases en SQL (ROC van Twente)
34
SELECT employee_id, first_name, last_name
FROM employees
WHERE employee_id = 100;
Merk op dat je bij zijn achternaam (tekst) wel enkele aanhalingstekens gebruikt en bij
zijn employee_id (nummer) niet. Ook bij datums gebruik je enkele aanhalingstekens.
De volgende rekenkundige operatoren kun je gebruiken in de WHERE:
= gelijk aan

groter dan
= groter dan of gelijk aan
< kleiner dan <= kleiner dan of gelijk aan <> ongelijk aan
Voor <> (ongelijk aan) kun je ook != gebruiken.
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;
Wie verdient er 12000 of meer?
SELECT last_name, salary
FROM employees
WHERE salary >= 12000;
Werkboek databases en SQL (ROC van Twente)
35
Wie werkt er allemaal op de afdeling IT_PROG?
SELECT last_name, job_id
FROM employees
WHERE job_id = ‘IT_PROG’;
Wie is er in dienst gekomen vanaf 1 januari 1998?
SELECT last_name, hire_date
FROM employees
WHERE hire_date >= ‘1998-01-01’;
Wie was er al in dienst vóór 1 januari 1998?
SELECT last_name, hire_date
FROM employees
WHERE hire_date <= ‘1998-01-01’; Werkboek databases en SQL (ROC van Twente) 36 Toon alle werknemers behalve de hoogste baas. Dat is de werknemer met als job_title AD_PRES. Dus King mag er niet in het antwoord voor komen. SELECT first_name, last_name, job_id FROM employees WHERE job_id <> ‘AD_PRES’;
BETWEEN … AND
Werkboek databases en SQL (ROC van Twente)
37
Met BETWEEN … AND kun je een query maken met een beperking tussen twee randen.
De randen doen mee met de selectie. De onderste rand moet als eerste genoemd
worden, want anders krijg je een leeg resultaat terug. De randen doen mee, zoals je in
onderstaand voorbeeld kunt zien.
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 9000 AND 11000;
Deze query kun je ook zo maken.
SELECT last_name, salary
FROM employees
WHERE salary >= 9000 AND salary <= 11000;
Ook voor datums kun je BETWEEN … AND gebruiken. Wie is er in dienst gekomen tussen
1 januari 1994 en 31 december 1997?
SELECT last_name, hire_date
FROM employees
WHERE hire_date BETWEEN ‘1994-01-01’ AND ‘1997-12-31’;
Werkboek databases en SQL (ROC van Twente)
38
IN
Met IN kun je gebruik maken van selecties van waarden die genoemd staan in een
lijst. Stel dat je van de tabel locations alleen maar de rijen wil zien van de
landen Canada (CA) en het United Kingdom (UK).
SELECT city, state_province, country_id
FROM locations
WHERE country_id IN(‘UK’, ‘CA’);
Deze query kun je ook zo maken:
SELECT city, state_province, country_id
FROM locations
WHERE country_id = ‘UK’ OR country_id = ‘CA’;
LIKE
LIKE maakt gebruikt van jokers. Er zijn twee soorten jokers. Met het procent teken (%)
kun je zoeken naar patronen in 0 of meer karakters. Het underscore teken () kun je Werkboek databases en SQL (ROC van Twente) 39 gebruiken als een joker voor één karakter. Als je LIKE gebruikt, moet je dus altijd minstens één van de twee jokers inzetten maar je kunt ze combineren! Toon alle werknemers waarvan de achternaam met een ‘H’ begint. SELECT last_name FROM employees WHERE last_name LIKE ‘H%’; Toon alle werknemers die de letter ‘a’ in de achternaam hebben. Merk op dat dit ook de eerste letter kan zijn, hoewel dat dan de hoofdletter ‘A’ is. SELECT last_name FROM employees WHERE last_name LIKE ‘%a%’; Toon alle werknemers waarvan de tweede letter van de achternaam een ‘o’ is. Voor het eerste karakter gebruik je dus de underscore, dan zoek je naar de letter ‘o’ en met het procentteken kijk je in de rest van de naam. SELECT last_name FROM employees WHERE last_name LIKE ‘_o%’; Werkboek databases en SQL (ROC van Twente) 40 Als je zoekt naar een underscore of het procentteken en je wil niet deze twee karakters inzetten als joker maar echt zoeken naar die karakters, dan kun je voor de underscore of het procentteken een escape teken plaatsen. In MSSQL kun je zelf bepalen welk teken je wilt gebruiken als escape teken. Toon alle werknemers met een job_id waarvan het job_id een underscore bevat gevolgd door een ‘R’. SELECT last_name, job_id FROM employees WHERE job_id LIKE ‘%_R%’ ESCAPE ‘\’ Werkboek databases en SQL (ROC van Twente) 41 Als je de backslash weglaat in de query krijg je onderstaand resultaat. Dat zijn alle job_id’s met een ‘R’ in de job_id. SELECT last_name, job_id FROM employees WHERE job_id LIKE ‘%_R%’; Ook voor datums kun je LIKE gebruiken. Wie is er in dienst gekomen in 1996? SELECT last_name, hire_date FROM employees WHERE hire_date LIKE ‘%1996%’; IS NULL Werkboek databases en SQL (ROC van Twente) 42 Als een waarde NULL is, is de waarde onbeschikbaar, onbekend, niet toegekend of niet van toepassing. Welke werknemer heeft er geen manager? SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL; Welke werknemer heeft er geen department_id? SELECT last_name, department_id FROM employees WHERE department_id IS NULL; IS NOT NULL Het tegenovergestelde van NULL is IS NOT NULL. Met IS NOT NULL toon je dus alle rijen die wel een waarde hebben in een bepaalde kolom. Welke werknemers hebben er allemaal commissie? Dat zijn de werknemers met een commission_pct. SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL; Werkboek databases en SQL (ROC van Twente) 43 Werkboek databases en SQL (ROC van Twente) 44 Vragen sectie 2 Vraag 1: Hoe kun je dubbele waarden voorkomen als je alle job_id’s wil tonen van de tabel employees? a. SELECT DESC job_id FROM employees b. SELECT LIMIT job_id FROM employees c. SELECT CONCAT job_id FROM employees d. SELECT DISTINCT job_id FROM employees Vraag 2: Wat zal deze query tonen? SELECT employee_id, CONCAT(first_name last_name), email FROM employees; a. Het employee_id, de voornaam en achternaam in hoofdletters en het e-mailadres. b. Het employee_id, de voornaam en achternaam aan elkaar en het e-mailadres. c. Het employee_id, de voornaam en achternaam met een spatie ertussen en het e mailadres. d. De query werkt niet. Je krijgt een foutmelding. Vraag 3: SELECT CONCAT(first_name, ‘ ‘, last_name) AS Volledige Naam FROM employees; Wat wordt er getoond in de kolomnaam van de uitvoer? a. volledige naam b. Volledige Naam c. VOLLEDIGE NAAM d. De query werkt niet. Je krijgt een foutmelding. Vraag 4: Je wil graag alle salarissen tonen tussen 10000 en 24000. Wat zou je gebruiken? a. LIKE b. IS NULL c. BETWEEN … AND d. IN Werkboek databases en SQL (ROC van Twente) 45 Vraag 5: Je wil graag alle rijen tonen die aan een bepaald patroon voldoen. Wat zou je gebruiken? a. IN b. SIMILAR c. LIKE d. ALMOST Vraag 6: Met welke WHERE wordt het getal 10 niet gekozen? a. WHERE hours <> 10 b. WHERE hours <= 10 c. WHERE hours BETWEEN 10 AND 20 d. WHERE hours IN(8, 9, 10) Vraag 7: Als je BETWEEN … AND gebruikt, maakt het niet uit in welke volgorde je de waarden invoert. Zo zal bijvoorbeeld BETWEEN 8 AND 10 hetzelfde resultaat opleveren als BETWEEN 10 AND 8. a. waar b. niet waar Vraag 8: Welke twee symbolen kun je gebruiken als joker als je zoekt naar patronen met LIKE? a. & (ampersand) en % (procent) b. % (procent) en (underscore)
c. $ (dollar) en # (hashtag)
d. @ (apenstaart) en & (ampersand)
Werkboek databases en SQL (ROC van Twente)
46
Opdrachten sectie 2
Query 1: Maak een overzicht met de volledige namen en de e-mailadressen eindigend
op @COMPANY.COM van de tabel employees. Let op de kolomnamen.
Query 2: Toon enkel de kolom commission_pct van de tabel employees.
Elk commission_pct mag maar één keer getoond worden maar de waarde NULL moet ook
getoond worden.
Query 3: Toon de jaarsalarissen van de eerste drie rijen van de tabel employees zoals in
onderstaand voorbeeld.
Werkboek databases en SQL (ROC van Twente)
47
Query 4: Maak een overzicht met de salarissen waarbij het dollarteken voor het salaris
komt. Toon de eerste vijf rijen van de tabel employees.
Query 5: Welke department_id’s zijn er in de tabel employees? Ze mogen maar één keer
getoond worden.
Query 6: Toon alle personeelsleden met een telefoonnummer dat begint met 011.44. De
informatie komt uit de tabel employees.
Werkboek databases en SQL (ROC van Twente)
48
Query 7: Welke afdeling(en) van de tabel departments hebben er geen manager?
Query 8: Welke personen werken er op de afdeling Administration (AD)? De informatie
komt uit de tabel employees.
Werkboek databases en SQL (ROC van Twente)
49
Werkboek databases en SQL (ROC van Twente)
50
Sectie 3
Logische operatoren
Er zijn drie logische operatoren waarmee je voorwaarden kunt stellen aan condities:

AND

OR

NOT
AND
Met AND moet aan alle voorwaarden worden voldaan om een resultaat te krijgen. Bij
numerieke waarden hoef je geen enkele aanhalingstekens te gebruiken (department_id =
50) maar bij alfanumerieke velden gebruik je enkele aanhalingstekens (job_id =
‘ST_CLERK’).
SELECT last_name, department_id, salary
FROM employees
WHERE department_id = 50 AND job_id = ‘ST_CLERK’;
Nog een voorbeeld met numerieke waarden in de WHERE. Gebruik geen
aanhalingstekens.
SELECT last_name, department_id, salary
FROM employees
WHERE department_id > 50 AND salary > 12000;
Werkboek databases en SQL (ROC van Twente)
51
NOOT: Als je de numerieke waarden als een string invoert dan zal SQL Server proberen
het te converteren naar een getal. Als dat lukt zal het resultaat wel worden getoond. We
noemen dat impliciete conversie. Maar daar moet je eigenlijk niet op vertrouwen. Laat
dus de aanhalingstekens altijd weg bij numerieke waarden.
Datums moeten wel tussen enkele aanhalingstekens.
SELECT last_name, hire_date, job_id
FROM employees
WHERE hire_date > ‘1998-01-01’ AND job_id LIKE ‘SA%’;
NOOT: De manier waarop een datum wordt weergegeven kan afhankelijk zijn van de landinstellingen
op de server.
OR
Werkboek databases en SQL (ROC van Twente)
52
Met OR moet aan één van de voorwaarden voldaan worden.
SELECT department_name, manager_id, location_id
FROM departments
WHERE location_id = 2500 OR manager_id = 124;
NOT
Met NOT moet er juist niet aan een voorwaarde worden voldaan.
SELECT department_name, location_id
FROM departments
WHERE location_id NOT IN(1700,1800);
Volgorde
Als query’s ingewikkeld worden, kan het probleem van de volgorde optreden, oftewel de
vraag: welke operator heeft precedentie (voorrang) op de onderste regel van de query?
SELECT CONCAT(last_name, ‘ ‘, salary * 1.05) employee_raise, department_id,
first_name
FROM employees
WHERE department_id IN(50,80) AND first_name LIKE ‘C%’ OR last_name LIKE ‘%
S%’;
Werkboek databases en SQL (ROC van Twente)
53

De AND komt voor de OR. Dus eerst worden alle werknemers geselecteerd die
werken op afdeling 50 of 80 EN die een voornaam hebben die begint met een C.
Dat is alleen maar Curtis.

Daarna komt de OR en worden alle werknemers getoond die een letter ‘s’ in hun
achternaam hebben.
De regels van de volgorde zijn als volgt:
Volgorde Operator
1
2
3
4
5
6
7
8
rekenkundige operatoren zoals + – * /
concatenatie (CONCAT)
vergelijkingsoperatoren <, <=, >, >=, <>
IS (NOT) NULL, LIKE, (NOT) IN
(NOT) BETWEEN
NOT
AND
OR
Stel dat je in de vorige query de OR en de AND omdraait.
SELECT CONCAT(last_name, ‘ ‘, salary * 1.05) employee_raise, department_id,
first_name
FROM employees
WHERE department_id IN(50,80) OR first_name LIKE ‘C%’ AND last_name LIKE ‘%
S%’;
Werkboek databases en SQL (ROC van Twente)
54
De beste manier om zeker te zijn van je resultaat is het gebruik van haakjes in de WHERE.
SELECT CONCAT(last_name, ‘ ‘, salary * 1.05) employee_raise, department_id,
first_name
FROM employees
WHERE (department_id IN(50,80) OR first_name LIKE ‘C%’) AND last_name LIKE
‘%S%’;
Werkboek databases en SQL (ROC van Twente)
55
ORDER BY
Als je de rijen gegarandeerd in een bepaalde volgorde wil zien, dan gebruik je daarvoor
een ORDER BY.
•De standaard volgorde is oplopend. De term daarvoor is ASCENDING.
•Numerieke waarden worden getoond van klein naar groot.
•Datums worden getoond van oud naar nieuw.
•Karakters worden getoond op alfabetische volgorde.
•NULL waarden komen eerst als er standaard op ASCENDING wordt gesorteerd.
•Het tegenovergestelde van ASCENDING is DESCENDING, kortweg DESC.
De ORDER BY is de laatste regel van de query. Tenzij er nog een LIMIT gebruikt wordt,
want dan komt LIMIT op de allerlaatste regel.
SELECT last_name, hire_date
FROM employees
ORDER BY hire_date;
Werkboek databases en SQL (ROC van Twente)
56
De kolom waarop je sorteert hoeft niet opgenomen te zijn in de SELECT op de bovenste
regel. Je kunt sorteren op elke kolom van de tabel.
SELECT employee_id, first_name
FROM employees
WHERE employee_id < 105 ORDER BY last_name; Het is mogelijk een alias te gebruiken voor de ORDER BY. SELECT last_name, hire_date AS date_started FROM employees ORDER BY date_started; Werkboek databases en SQL (ROC van Twente) 57 Het is mogelijk op meerdere kolommen te sorteren. SELECT department_id, last_name FROM employees WHERE department_id <= 50 ORDER BY department_id, last_name; Werkboek databases en SQL (ROC van Twente) 58 Als je op meerdere kolommen sorteert, kun je ook DESC gebruiken voor elke kolom waar dat is gewenst. SELECT department_id, last_name FROM employees WHERE department_id <= 50 ORDER BY department_id DESC, last_name; NULL waarden komen als eerst aan de beurt. SELECT last_name, commission_pct FROM employees ORDER BY commission_pct; Werkboek databases en SQL (ROC van Twente) 59 Het is ook mogelijk te sorteren op het nummer van de kolom op de SELECT regel. SELECT first_name, last_name FROM employees ORDER BY 2; Werkboek databases en SQL (ROC van Twente) 60 DESCENDING Standaard wordt er oplopend gesorteerd. Die volgorde is dan ASCENDING maar dat hoef je dus nooit te gebruiken want standaard wordt er in die volgorde gesorteerd. Je kunt de oplopende volgorde aanpassen naar een aflopende volgorde met gebruik van DESC. DESC is de afkorting van DESCENDING. Er wordt dan dus gesorteerd van hoog naar laag. Vergelijk onderstaande query’s met elkaar en let ook op de pijltjes achter last_name in de kop van de tweede kolom van de uitvoer. Bij de linker query staat dat pijltje omhoog (ASCENDING) en bi jde rechter query staat dat pijltje omlaag (DESCENDING). SELECT first_name, last_name FROM employees ORDER BY last_name; SELECT first_name, last_name FROM employees ORDER BY last_name DESC; Werkboek databases en SQL (ROC van Twente) 61 NULL waarden komen eerst als er oplopend (van laag naar hoog) wordt gesorteerd en laatst als er aflopend (van hoog naar laag) wordt gesorteerd. Vergelijk deze twee query’s met elkaar. SELECT first_name, manager_id FROM employees ORDER BY manager_id; SELECT first_name, manager_id FROM employees ORDER BY manager_id DESC; Werkboek databases en SQL (ROC van Twente) 62 Met datums wordt de volgorde ook omgedraaid. SELECT last_name, hire_date FROM employees ORDER BY hire_date; SELECT last_name, hire_date FROM employees ORDER BY hire_date DESC; Werkboek databases en SQL (ROC van Twente) 63 Vragen sectie 3 Vraag 1: Wat is de juiste volgorde in voorrang bij de onderstaande operatoren? a. NOT, OR, AND b. NOT, AND, OR c. AND, NOT, OR d. AND, OR, NOT Vraag 2: Bekijk onderstaande query. Er is één afdeling zonder manager (Contracting). Komt Contracting onderaan (als laatste) of bovenaan (als eerste) bij deze query? SELECT department_name, manager_id FROM departments ORDER BY manager_id; a. bovenaan b. onderaan c. de query geeft een foutmelding Vraag 3: Bekijk onderstaande query. Gaat deze query werken? SELECT last_name, department_id Werkboek databases en SQL (ROC van Twente) 64 FROM employees WHERE department_id = 60 OR department_id = 90 OR department_id = 110; a. ja b. nee, je mag maar één keer OR gebruiken in de WHERE Vraag 4: Kijk naar onderstaande twee query’s. Het verschil zit hem in de haakjes. Krijg je hetzelfde resultaat denk je? Komt Hunold met beide query’s tevoorschijn. SELECT employee_id, last_name FROM employees WHERE employee_id >= 200 AND last_name LIKE ‘F%’ OR last_name LIKE ‘H%’
ORDER BY achternaam;
SELECT employee_id, last_name
FROM employees
WHERE employee_id >= 200 AND (last_name LIKE ‘F%’ OR last_name LIKE ‘H%’)
ORDER BY achternaam;
a. ja, het resultaat is hetzelfde
b. nee, het resultaat is niet hetzelfde
c. de query geeft een foutmelding
Vraag 5:
Kijk naar onderstaande query. Hoe wordt een datumveld gesorteerd? Is dat van oud naar
nieuw of van nieuw naar oud?
SELECT last_name, hire_date
FROM employees
WHERE job_id = ‘AD_VP’;
a (van oud naar nieuw)
Werkboek databases en SQL (ROC van Twente)
65
b (van nieuw naar oud)
Vraag 6:
Kijk naar onderstaande query. Zal deze query werken?
SELECT first_name, job_id
FROM employees
ORDER BY last_name;
a. ja, dat werkt
b. nee, want je kunt niet sorteren op een veld dat niet op de bovenste regel is genoemd
Vraag 7:
Bekijk onderstaande query. Er is één afdeling zonder manager (Contracting).
Komt Contracting onderaan (als laatste) of bovenaan (als eerste) bij deze query?
SELECT department_name, manager_id
FROM departments
ORDER BY manager_id DESC;
a. bovenaan
b. onderaan
c. de query geeft een foutmelding
Vraag 8:
Bekijk onderstaande query. Wordt er gesorteerd of werkt de query niet?
SELECT last_name achternaam, job_id baan, salary salaris
FROM employees
ORDER BY 3 DESC, job_id, achternaam;
a. Er wordt gesorteerd op salaris van hoog naar laag, daarna op job_id en tenslotte
alfabetisch op achternaam.
b. De query werkt niet. Je mag niet sorteren op verschillende manieren zoals hier is
gedaan.
c. De query werkt niet. ORDER BY 3 is een verkeerde sortering.
Vraag 9:
ORDER BY ASC is precies hetzelfde als ORDER BY.
a. ja, dat is exact hetzelfde
b. nee, met ORDER BY ASC komen de NULLS bovenaan in plaats van onderaan
Vraag 10:
Werkboek databases en SQL (ROC van Twente)
66
Bekijk onderstaande query. Zal deze query werken op deze manier? Met andere
woorden: kun je sorteren door gebruik te maken van een alias?
SELECT last_name AS achternaam
FROM employees
ORDER BY achternaam;
a. ja
b. nee
Opdrachten sectie 3
Query 1: Toon de volledige naam in één kolom, het job_id en het salaris van alle
werknemers die ‘ST_CLERK’ zijn en een salaris hebben hoger dan 3000. Let op de
kolomnamen en sorteer op salaris.
Query 2: Toon de city en het country_id van de tabel locations van alleen maar de landen
Canada (CA) en het Verenigd Koninkrijk (UK).
Query 3: Toon de job_title, min_salary, max_salary, het verschil
tussen max_salary en min_salary van de tabel jobs waarvoor geldt dat het verschil groter
is dan 6000 en de job_title ongelijk is aan ‘President’. Let op de kolomnamen en sorteer
op het verschil tussen max_salary en min_salary van hoog naar laag.
Werkboek databases en SQL (ROC van Twente)
67
Query 4: Toon employee_id, last_name en salary zoals in het voorbeeld van alle
werknemers waarvoor het department_id niet NULL is, het department_id niet gelijk is aan
10, 20 of 50 en het salaris lager is dan 10000. Sorteer op het salaris van hoog naar laag.
Query 5: Toon alle manager_id’s behalve die van 100, 102, 103, 201 en 205.
De manager_id’s mogen maar één keer getoond worden in de lijst.
Werkboek databases en SQL (ROC van Twente)
68
Query 6: Toon vijftien achternamen achter elkaar van de employees waarvan de
achternaam met een letter ‘F’ of hoger begint gesorteerd op achternaam.
Query 7: Toon first_name, last_name en salary van alle werknemers die tussen de 12000
en 20000 verdienen of een voornaam hebben die met een ‘E’ begint. Sorteer op salaris en
daarna op voornaam. De gegevens komen uit de tabel employees.
Werkboek databases en SQL (ROC van Twente)
69
Query 8: Toon de last_name, salary, bonus en het totale salaris van alle employees die
een bonus hebben. Let op de kolomnamen, zorg dat het woordje “dollar” achter het
totale salaris komt en sorteer op het totale salaris van hoog naar laag. Maar let op! Zoals
je ziet is er niet gesorteerd op kolom vier. Dat lukt niet want dat is een geconcateneerde
kolom met cijfers en tekst (dollar) en dan wordt dat tekst. Dat moet je dus anders
oplossen!
Query 9: Toon de naam, het e-mailadres en het telefoonnummer zoals in het voorbeeld
van alle werknemers die een telefoonnummer hebben zonder een vijf (‘5’) in het nummer.
Werkboek databases en SQL (ROC van Twente)
70
Werkboek databases en SQL (ROC van Twente)
71
Sectie 4
Tekstfucties
Tekstfuncties geven per rij precies een rij terug. Als je een functie los laat op twintig rijen
krijg je dus precies twintig resultaten terug. Er zijn twee soorten tekstfuncties:
•functies die omzetten van hoofdletters naar kleine letters of andersom (case
manipulation), bijvoorbeeld
oLOWER
oUPPER
•functies die andere dingen kunnen doen met karakters (character-manipulation),
bijvoorbeeld
oCONCAT
oREPLACE
oSUBSTR
Tekstfuncties kun je gebruiken op de SELECT, WHERE en ORDER BY regel.
Een uitgebreid overzicht van tekstfuncties is te vinden bij de SQL cursus van W3 Schools
bij SQL Server String Functions
https://www.w3schools.com/sql/sql_ref_sqlserver.asp
Lower
Met LOWER zet je alles in kleine letters.
SELECT LOWER(first_name)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
72
Upper
Met UPPER zet je alles in hoofdletters.
SELECT UPPER(first_name)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
73
Concat
Met CONCAT plak je kolommen aan elkaar. Zie ook Sectie 2 voor meer uitleg over
CONCAT.
SELECT CONCAT(first_name, last_name)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
74
Replace
Met REPLACE vervang je karakters in een string. In onderstaand voorbeeld worden
spaties weggehaald in de achternaam. Dat geldt in dit geval enkel voor De Haan.
SELECT REPLACE(last_name, ‘ ‘, ”)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
75
Substring
Met SUBSTRING kun je een gedeelte van een string laten zien vanaf een bepaalde
positie, met eventueel een bepaalde lengte of default tot aan het einde van de string.
Met onderstaande query worden de eerste drie karakters van de achternaam getoond.
SELECT SUBSTRING(last_name, 1, 3)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
76
Left
Met de functie LEFT haal je de eerste x tekens van een string op.
SELECT LEFT(last_name , 2)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
77
Right
Met de functie RIGHT haal je de laatste x tekens van een string op.
SELECT RIGHT(last_name , 2)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
78
Rekenfuncties
Met ROUND, CEILING en FLOOR kun je getallen manipuleren.
Een uitgebreid overzicht van rekenfuncties is te vinden bij de SQL cursus van W3 Schools
bij SQL Server Numeric Functions
https://www.w3schools.com/sql/sql_ref_mysql.asp
Round
Met ROUND kun je getallen afronden op een bepaald aantal decimalen.
SELECT ROUND(45.926 , 2)
Ceiling
Werkboek databases en SQL (ROC van Twente)
79
Met CEILING kun je afronden naar boven op een heel getal.
SELECT CEILING(45.236)
Floor
Met FLOOR rond je af naar beneden op een heel getal. Eigenlijk is dit dus geen afronden
maar afkappen.
SELECT FLOOR(45.926)
Datumfuncties
Getdate
Met GETDATE() haal je de systeemdatum op, de datum van vandaag.
SELECT GETDATE()
Met DAY haal je het nummer van de dag op van een datum.
SELECT last_name, hire_date, DAY(hire_date)
FROM employees
WHERE last_name = ‘King’;
Werkboek databases en SQL (ROC van Twente)
80
DateName
Je kunt de functie DATENAME gebruiken om allerlei informatie van een datum op te
halen, zoals bijvoorbeeld de naam van de dag van de week of de naam van de maand.
SELECT
DATENAME(year, GETDATE()) AS Year,
DATENAME(quarter, GETDATE()) AS Quarter,
DATENAME(month, GETDATE()) AS Month,
DATENAME(dayofyear, GETDATE()) AS ‘Day of Year’,
DATENAME(day, GETDATE()) AS Day,
DATENAME(week, GETDATE()) AS Week,
DATENAME(weekday, GETDATE()) AS Weekday;
Month
Met MONTH haal je het nummer van de maand op van een datum.
SELECT last_name, hire_date, MONTH(hire_date)
FROM employees
WHERE last_name = ‘King’;
Year
Met YEAR haal je het jaar op van een datum.
SELECT last_name, hire_date, YEAR(hire_date)
FROM employees
WHERE last_name = ‘King’;
Werkboek databases en SQL (ROC van Twente)
81
Op deze manier kun je datums van elkaar aftrekken. Bij jaren wordt dan de uitkomst naar
beneden afgerond.
SELECT last_name, YEAR(hire_date), YEAR(GETDATE()), YEAR(GETDATE())
YEAR(hire_date) AS dienstjaren
FROM employees
WHERE last_name = ‘King’;
DateAdd
Met DATEAADD kun je dagen, maanden of jaren toevoegen aan een datum. In
onderstaande voorbeelden wordt achtereenvolgens 6 dagen, 6 maanden en 6 jaar
toegevoegd aan de hire_date.
SELECT last_name, hire_date, DATEADD(day , 6 , hire_date)
FROM employees
WHERE last_name = ‘King’;
SELECT last_name, hire_date, DATEADD(month , 6 , hire_date)
FROM employees
WHERE last_name = ‘King’;
SELECT last_name, hire_date, DATEADD(year , 6 , hire_date)
FROM employees
WHERE last_name = ‘King’;
Werkboek databases en SQL (ROC van Twente)
82
DateDiff
Met DATEDIFF kun je het verschil tussen twee datums uitrekenen, bijvoorbeeld in jaren of
maanden.
SELECT last_name, hire_date, DATEDIFF(YEAR, hire_date, GETDATE())
FROM employees
WHERE last_name = ‘King’;
SELECT last_name, hire_date, DATEDIFF(MONTH, hire_date, GETDATE())
FROM employees
WHERE last_name = ‘King’;
Format
Datumvelden kun je op allerlei mogelijke manieren vormgeven met FORMAT. Een
overzicht van de mogelijkheden vind je hier:
SELECT last_name, hire_date, FORMAT(hire_date, ‘d MMMM yyyy’)
FROM employees
WHERE last_name = ‘King’;
Werkboek databases en SQL (ROC van Twente)
83
Als je de uitvoer in het Nederlands wil, dan kun je een culture opgeven:
SELECT last_name, hire_date, FORMAT(hire_date, ‘d’ , ‘nl-NL’)
FROM employees
WHERE last_name = ‘King’;
Of in de Verenigde Staten:
SELECT last_name, hire_date, FORMAT(hire_date, ‘d’ , ‘us-US’)
FROM employees
WHERE last_name = ‘King’;
Groepsfuncties
Er zijn zeven groepsfuncties: MIN, MAX, SUM, AVG, COUNT, STDDEV, VARIANCE.
Groepsfuncties worden losgelaten op een set van waarden, waarna ze één waarde als
resultaat retourneren (multi-row functions). Groepsfuncties worden vaak in combinatie
met GROUP BY (en met HAVING) toegepast. STDDEV (standaarddeviatie) en VARIANCE
(variantie) worden hier niet verder behandeld.
•Groepsfuncties kunnen niet worden gebruikt in de WHERE regel.
SELECT last_name
FROM employees
WHERE salary = MAX(salary);
•Je kunt meerdere groepsfuncties op de SELECT regel gebruiken.
SELECT MIN(salary) Min, MAX(salary) Max, SUM(salary) Sum , AVG(salary)
Avg, COUNT(salary)Count
FROM employees;
Werkboek databases en SQL (ROC van Twente)
84

MIN, MAX en COUNT kun je met elk datatype gebruiken. SUM en AVG kun je
enkel met numerieke datatypes gebruiken.

NULL waardes worden genegeerd en niet als 0 geteld.

DISTINCT kan gebruikt worden met alle groepsfuncties.
Min
Met MIN haal je de kleinste waarde op van een kolom van een aantal rijen. Elk datatype
(numeriek, alfanumeriek, datum) kan hiervoor gebruikt worden.
SELECT MIN(salary)
FROM employees;
SELECT MIN(last_name)
FROM employees;
SELECT MIN(hire_date)
FROM employees;
Max
Met MAX haal je de hoogste waarde op van een kolom van een aantal rijen. Elk datatype
(numeriek, alfanumeriek, datum) kan hiervoor gebruikt worden.
SELECT MAX(salary)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
85
SELECT MAX(last_name)
FROM employees;
SELECT MAX(hire_date)
FROM employees;
Sum
Met SUM haal je de som op van een kolom van een aantal rijen, dus alle rijen bij elkaar
opgeteld. Het kan alleen maar gebruikt worden voor kolommen met een numeriek
datatype.
SELECT SUM(salary)
FROM employees;
Als je de dubbele waarden niet mee wil tellen met behulp van DISTINCT krijg je een
ander resultaat. Enkele salarissen komen twee of meer keer voor.
SELECT SUM(DISTINCT salary)
FROM employees;
Voorbeeld van een SUM met een WHERE.
Werkboek databases en SQL (ROC van Twente)
86
SELECT SUM(salary) som_salarissen_afdeling_90
FROM employees
WHERE department_id = 90;
Hetzelfde voorbeeld maar nu met de som van alle salarissen zonder de duplicaten.
SELECT SUM(DISTINCT salary) som_salarissen_afdeling_90
FROM employees
WHERE department_id = 90;
Avg
Met AVG haal je het gemiddelde op van een kolom van een aantal rijen. Het kan alleen
maar gebruikt worden voor kolommen met een numeriek datatype.
SELECT AVG(salary)
FROM employees;
NULL waarden worden niet meegeteld in de berekening.
SELECT AVG(commission_pct)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
87
Als je de NULL waarden wel wil meetellen, bijvoorbeeld als 0, dan kun je met IFNULL de
NULL waarden vervangen door 0.
SELECT AVG(IFNULL(commission_pct, 0))
FROM employees;
Count
Met COUNT tel je het aantal rijen.
SELECT COUNT(last_name)
FROM employees;
Duplicaatwaarden worden afzonderlijk meegerekend.
SELECT COUNT(job_id)
FROM employees;
Als je duplicaatwaarden wil negeren, moet je tussen de haakjes vóór de kolomnaam
DISTINCT toevoegen. Er zijn 19 verschillende job_id’s.
SELECT COUNT(DISTINCT job_id)
FROM employees;
NULL waarden worden genegeerd.
SELECT COUNT(commission_pct)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
88
Met COUNT() worden alle rijen in de tabel geteld en wordt er niet gekeken naar een specifieke kolom. SELECT COUNT()
FROM employees;
Voorbeeld van een COUNT() met een WHERE. SELECT COUNT()
FROM employees
WHERE hire_date < ‘1996-01-01’;
Vragen sectie 4
Vraag 1:
Op welke regel kun je geen tekstfunctie gebruiken, dus functies zoals LOWER en UPPER?
a. SELECT
b. FROM
c. WHERE
d. ORDER BY
Vraag 2:
Met welke functie kun je een gedeelte van een string laten zien vanaf een bepaalde
positie met eventueel een bepaalde lengte?
a. SUBSTR
b. CONCAT
c. DISTINCT
d. DESCRIBE
Vraag 3:
Werkboek databases en SQL (ROC van Twente)
89
Met welke functie kun je twee kolommen aan elkaar plakken?
a. SUBSTRING
b. REPLACE
c. CONCAT
d. DISTINCT
Vraag 4: Neem onderstaande query. Hoeveel rijen zullen er worden geretourneerd?
SELECT COUNT(department_id)
FROM employees;
a. alle rijen van de tabel employees (20)
b. alle rijen van de tabel employees waarvan de rijen van de kolom department_id niet de
waarde NULL hebben (19)
c. alle rijen van de tabel employees waarvan de rijen van de kolom department_id niet de
waarde NULL hebben en waarbij de dubbele waarden niet meetellen (7)
d. alle rijen inclusief de rijen waarvan de kolom department_id de waarde NULL hebben,
waarbij de dubbele waarden niet meetellen (8)
Vraag 5: Als je COUNT in combinatie met DISTINCT gebruikt, tellen de uitkomsten met
als waarde NULL niet mee.
a. waar
b. niet waar
Vraag 6: Wat haal je op met onderstaande query?
SELECT last_name achternaam, DAY(hire_date) AS dag
FROM employees
WHERE last_name = ‘King’;
a.
b.
c.
d. Deze query werkt niet.
Vraag 7: Wat is de uitkomst van deze query?
SELECT CEILING(ROUND(5.435, 2))
Werkboek databases en SQL (ROC van Twente)
90
a. 5
b. 5,430
c. 5.440
d. 6
Vraag 8: Wat moet er staan op de plaats van de puntjes in deze query?
SELECT last_name AS achternaam, hire_date AS in_dienst_gekomen, DATEDIFF(YE
AR, hire_date, …) AS jaren_in_dienst
FROM employees
WHERE last_name = ‘Varga’;
a. GETDATE
b. NOW
c. zowel SYSDATE als NOW zullen werken
d. geen van bovenstaande antwoorden is juist
Werkboek databases en SQL (ROC van Twente)
91
Opdrachten sectie 4
Query 1: Maak een overzicht met de namen van de werknemers en hun nieuwe e
mailadressen zoals in onderstaand overzicht. Als er een spatie in de naam staat (zoals bij
‘De Haan’), zal die spatie uit de naam moeten worden gehaald. Alles staat in kleine letters
en het e-mailadres bestaat uit de eerste letter van de voornaam, gevolgd door een punt
en daarna de achternaam met daaraan vastgeplakt de bedrijfsnaam. Er is gesorteerd op
achternaam.
Query 2: Vervang in de tabel jobs alle job_titles waarin ‘Manager’ voorkomt het woord
‘Manager’ door ‘Director’.
Query 3: Om in te loggen maken de werknemers gebruik van een inlognaam die bestaat
uit de eerste letter van de voornaam en de eerste letter van de achternaam, aan elkaar en
in kleine letters. Maak een overzicht van de inlognamen en sorteer op de achternaam.
Werkboek databases en SQL (ROC van Twente)
92
Query 4: Maak een overzicht van de salarissen zoals in onderstaand overzicht. In de
tweede kolom staat het maandsalaris afgerond op nul decimalen. Uitgaande van een 36
urige werkweek en vier weken in een maand staat dan in de derde kolom wat zo’n
werknemer per uur verdient in twee decimalen. In de vierde kolom is dat naar beneden
afgerond en in de vijfde kolom naar boven. Er is gesorteerd op achternaam.
Werkboek databases en SQL (ROC van Twente)
93
Query 5: Maak een overzicht zoals hieronder. De gegevens komen uit de
tabel job_history. De datums zijn in het Nederlands (maanden met een kleine letter
bijvoorbeeld) en er is geen voorloopnul gebruikt bij de dag (geen 01 januari maar 1
januari bijvoorbeeld). Er is gesorteerd op employee_id.
Query 6: De grote baas (King) wil een overzicht van welke werknemers binnenkort 35
jaar in dienst zijn. King wil zichzelf niet op de lijst en slechts de vijf eerste werknemers die
Werkboek databases en SQL (ROC van Twente)
94
in aanmerking komen worden getoond. Let op het formaat van de datum. Er is
gesorteerd op de hire_date.
Query 7: Maak onderstaand overzicht na. De informatie komt uit de tabel employees.
Het gaat om het aantal rijen. De eerste kolom is het aantal inclusief de NULL en de
tweede kolom is het aantal exclusief de NULL waarden bij manager_id.
Query 8: De werknemers van de afdeling sales (de personen met ‘SA_MAN’ of ‘SA_REP’
als job_id) krijgen 1,25% opslag. Maak het overzicht zoals hieronder. Er is gesorteerd op
achternaam.
Werkboek databases en SQL (ROC van Twente)
95
Werkboek databases en SQL (ROC van Twente)
96
Werkboek databases en SQL (ROC van Twente)

97
Sectie 5
Datatypes
In het algemeen kun je zeggen dat er drie soorten kolomgegevens zijn:
•getallen (numeriek)
•tekst (alfanumeriek)
•datums

Datatypes kunnen verschillende namen hebben in verschillende databases maar in
principe werkt het overal hetzelfde. Controleer altijd de documentatie voor de details van
de datatypen want zelfs bij dezelfde namen kunnen de details verschillend zijn.

Een overzicht van alle datatypes vind je hier:
https://www.w3schools.com/sql/sql_datatypes.asp

Numeriek
INTEGER
Het datatype INTEGER is een eindige verzameling van gehele getallen. Ze kunnen zowel
positief als negatief zijn en 0 hoort er ook bij. Als afkorting voor INTEGER wordt INT
gebruikt. Een aantal datatypen is gebaseerd op integers. Het is belangrijk het datatype zo
efficiënt mogelijk te kiezen zodat de opslag in de database zo klein mogelijk blijft. Zo is
het bijvoorbeeld niet nodig de lidnummers van een vereniging het datatype INT of zelfs
MEDIUMINT te geven aangezien een vereniging niet snel zoveel leden zal hebben als het
maximum aantal dat je hiermee kunt maken.

UNSIGNED
Met UNSIGNED kun je bepalen dat het datatype met 0 moet beginnen. Met een TINYINT
heb je bijvoorbeeld 256 mogelijkheden. Dat is één Byte van -128 tot en met 127. Het
getal 0 moet je ook meerekenen, vandaar het maximum van 127. Als je bepaalt dat het
datatype UNSIGNED is, begin je met 0 en dan heb je de mogelijkheden van 0 tot en met

Dat zijn ook weer in totaal 256 mogelijkheden (één Byte).

Type Bytes Minimum Maximum Maximum Unsigned
TINYINT 1 -128 127 255
SMALLINT 2 -32768 32767 65535
MEDIUMINT 3 -8388608 8388607 16777215
INT 4 -2147483648 2147483647 4294967295
BIGINT 8 -2 63 2 63-1 2 64-1

FLOAT
Een klein getal met een aantal decimalen.

DOUBLE
Een groot getal met een aantal decimalen.
DECIMAL
Een DOUBLE opgeslagen als een STRING.
Bij een FLOAT, DOUBLE en DECIMAL kun je het maximum aantal cijfers vastleggen en ook
het aantal cijfers achter de komma. Let op dat in de standaard notatie een punt wordt
gebruikt waar wij een komma gebruiken.
Alfanumeriek
De belangrijkste alfanumerieke datatypen zijn de volgende.
CHAR
Wordt gebruikt voor een veld waar een STRING in komt met een vaste lengte,
bijvoorbeeld een postcode. Je kunt er maximaal 255 letters, cijfers en speciale karakters
in stoppen. Het veld wordt opgevuld met spaties tot de maximale lengte. Je gebruikt
CHAR alleen als de inhoud van een veld altijd dezelfde lengte heeft. Bijvoorbeeld een
studentnummer dat altijd een lengte heeft van 7 tekens.
VARCHAR
Wordt gebruikt voor een veld waar een STRING in komt met een variabele lengte,
bijvoorbeeld een adres of e-mailadres. Je kunt er maximaal 255 letters, cijfers en speciale
karakters in stoppen.
Zowel CHAR als VARCHAR kennen een unicode variant, resp. NCHAR en NVARCHAR.
Deze variant gebruik je als een veld tekens bevat die niet tot het normale alfabet
behoren, zoals bijvoorbeeld een e met umlaut (ë). Ieder teken wat als unicode wordt
opgeslagen kost 2 bytes i.p.v. 1 byte. Dus een VARCHAR(50) kost 50 bytes en een
NVARCHAR(50) kost 100 bytes.
Datums
DATE
Het datatype DATE wordt gebruikt voor de dag van de maand, de maand en het jaar.
SELECT last_name, hire_date
FROM employees
WHERE last_name = ‘King’;
Werkboek databases en SQL (ROC van Twente)
98
DATETIME
Het datatype DATETIME is een combinatie van datum en tijd van het formaat YYYY-MM
DD HH:MI:SS.
SMALLDATETIME
Ook het datatype SMALLDATETIME is een combinatie van datum en tijd, maar kost
minder geheugenruimte. Daarentegen is het bereik van de mogelijke datums veel kleiner.
DATETIME
Datum
SMALLDATETIME DATETIME2
1-1-1753 tot 31
12-9999
1-1-1900 tot
6-6-2079
1-1-0001 tot
31-12-9999
Tijd
00:00:00 tot
23:59:59.999
00:00:00 tot
23:59:59.9999999
00:00:00 tot
23:59:59
Klik hier voor een gedetailleerde uitleg.
Conversie
We kennen 2 vormen van conversie:

Impliciete conversie: hierbij zorgt het DBMS automatisch voor conversie van het
ene gegevenstype naar het andere, bijvoorbeeld van een string naar een getal.
SELECT last_name, salary
FROM employees
WHERE salary > ‘15000’;
Het DBMS ‘weet’ nu dat salaris een getal moet zijn en converteert de string naar
een getal. Maar dat lukt alleen als het ook een getal is. Lukt het niet dan verschijnt
een foutmelding:
SELECT last_name, salary
FROM employees
WHERE salary > ‘vijftienduizend’;
Het is doorgaans een slechte gewoonte om te vertrouwen op impliciete conversie.
Expliciete conversie: hierbij gebruik je functies van SQL om zelf een gegevenstype
te converteren naar een ander type.
o CAST
Om datatypes om te zetten van het ene type naar het andere type kun je
CAST gebruiken. De syntax is dan:
Werkboek databases en SQL (ROC van Twente)
99
CAST(expression AS data_type(length))
Waarbij length optioneel is en die hoef je dus niet per se toe te voegen.
Zet een datum om naar tekst en toon enkel de eerste vier karakters:
SELECT last_name,hire_date, CAST(hire_date AS CHAR(4))
FROM employees
WHERE last_name = ‘King’;
Zet een datum van het formaat DATE om naar het formaat DATETIME:
SELECT last_name,hire_date, CAST(hire_date AS DATETIME)
FROM employees
WHERE last_name = ‘King’;
Zet een MONEY om naar een INTEGER (let op: eventuele getallen achter
de komma worden afgekapt en niet afgerond):
SELECT last_name, CAST(salary AS INTEGER)
FROM employees
WHERE last_name = ‘king’
o CONVERT
Met CONVERT kun je precies hetzelfde doen als met CAST: het datatype
omzetten van het ene type naar het andere. De syntaxis is:
CONVERT(type , waarde)
Zet de hire_date om van een DATETIME naar een DATE:
Werkboek databases en SQL (ROC van Twente)
100
SELECT last_name, CONVERT(date , hire_date)
FROM employees
WHERE last_name = ‘king’
Nesten
Functies kunnen worden genest. Ze worden dan van binnen naar buiten afgehandeld.
SELECT last_name,CONCAT(‘$ ‘, CAST(salary AS INTEGER))
FROM employees
WHERE last_name = ‘King’;
Salary wordt omgezet naar een INTEGER (heel getal). Daarna wordt er voor de uitkomst
een $ geplaatst met behulp van CONCAT.
Null functies
Als de waarde NULL is, is de inhoud onbeschikbaar, niet bekend of niet van toepassing.
NULL is niet hetzelfde als 0 of een spatie, want een 0 is een getal en een spatie is een
karakter. Maar soms weet je de inhoud van een kolom niet. We zeggen dan dat de
inhoud NULL is.
Als je vermenigvuldigt met NULL of deelt door NULL, dan is het resultaat NULL. Je kunt
dus wel delen door NULL maar niet door 0 want delen door 0 is verboden.
Soms wil je NULL vervangen door een andere waarde, bijvoorbeeld als je wil rekenen met
de cellen die NULL zijn of als je met een bepaalde tekst aan wil geven dat de inhoud
NULL is. Je kunt dan met een functie NULL vervangen door een andere inhoud,
bijvoorbeeld door 0 of door een bepaalde tekst.
Hier zijn twee verschillende functies voor te gebruiken: IFNULL en COALESCE.
ISNULL
Er zijn maar vier personeelsleden die een commission_pct hebben (commissie krijgen).
Commissie is een bepaald percentage dat je extra krijgt boven op je loon. Dat
Werkboek databases en SQL (ROC van Twente)
101
percentage kan gaan over de verkopen die je hebt gedaan of gewoon als percentage
boven op je loon. Hoe meer je dan verkoopt, hoe hoger je commissiepercentage dan kan
worden.
SELECT last_name, commission_pct AS commissie
FROM employees;
Nu is het dus mogelijk iets anders te laten zien dan NULL in de uitvoer, bijvoorbeeld de
waarde 0:
SELECT last_name, ISNULL(commission_pct, 0) AS commissie
FROM employees;
Werkboek databases en SQL (ROC van Twente)
102
Als je wil rekenen met de waarden in een kolom die NULL kunnen zijn, dan gaat het mis
met de berekeningen. Als je met NULL rekent, is het resultaat namelijk NULL.
SELECT last_name, salary, commission_pct AS commissiepercentage, salary * c
ommission_pct AS commissie, salary + salary * commission_pct AS totaalsalar
is
FROM employees;
Als je NULL vervangt door 0 in de kolom commission_pct gaan de berekeningen wel
goed.
SELECT last_name,
salary AS salaris,
ISNULL(commission_pct, 0) AS commissiepercentage,
salary * ISNULL(commission_pct, 0) AS commissie,
ROUND(salary + salary * ISNULL(commission_pct, 0) , 2) AS totaalsalaris
FROM employees;
Werkboek databases en SQL (ROC van Twente)
103
Tenslotte zetten we nog een procentteken en enkele dollartekens met CONCAT.
SELECT last_name, CONCAT(‘$ ‘,salary) AS basissalaris, CONCAT(IsNULL(commission_pct, 0), ‘ %’)
AS commissiepercentage, CONCAT(‘$ ‘, salary * IsNULL(commission_pct, 0)) AS commissie,
CONCAT(‘$ ‘, ROUND(salary + salary * IsNULL(commission_pct, 0) , 2)) AS totaalsalaris
FROM employees;
Werkboek databases en SQL (ROC van Twente)
104
Om de query overzichtelijk te houden, wordt soms de volgende notatie gehanteerd:
SELECT
last_name
, CONCAT(‘$ ‘,salary) AS basissalaris
, CONCAT(ISNULL(commission_pct, 0), ‘ %’) AS commissiepercentage
, CONCAT(‘$ ‘, salary * ISNULL(commission_pct, 0)) AS commissie
, CONCAT(‘$ ‘, ROUND(salary + salary * ISNULL(commission_pct, 0),2)) AS totaalsalaris
FROM employees;
Er zijn diverse tools op internet te vinden die jouw SQL code netjes kunnen opmaken. Kijk maar eens
op https://www.freeformatter.com/sql-formatter.html of op https://sqlformatter.org/ of op
https://poorsql.com/. Je kunt vast wel zelf uitvinden hoe het werkt. Vraag anders je docent om
toelichting.
COALESCE
COALESCE kan ongeveer hetzelfde als ISNULL.
SELECT last_name, COALESCE(commission_pct, ‘geen’) AS commissie
FROM employees;
Werkboek databases en SQL (ROC van Twente)
105
Maar COALESCE kan nog net iets meer. Bekijk onderstaand resultaat. Bij COALESCE geef
je een rijtje op dat gecontroleerd moet worden. Het gaat er om wat er in de laatste
kolom getoond moet worden. Als de eerste kolom (bonus) inhoud heeft, toon je die
inhoud in de laatste kolom. Als de eerste kolom NULL is, kijk je naar de tweede kolom
(department_id). Als die inhoud heeft, toon je die inhoud in de laatste kolom. Als zowel
de eerste (bonus) als de tweede kolom (department_id) NULL zijn, toon je de tekst ‘geen’.
SELECT last_name, commission_pct, department_id,
COALESCE(commission_pct, department_id, 0) AS comm
FROM employees;
Werkboek databases en SQL (ROC van Twente)
106
Of een ander voorbeeld. Je hebt een tabel met studentgegevens. Een student heeft voorletters,
roepnaam en achternaam.
Alleen de achternaam is verplicht. Als een student geen voorletters heeft en geen roepnaam dan kun
je de naam als volgt weergeven:
J.R.
SELECT CONCAT(COALESCE(roepnaam , voorletters , ”) , achternaam)
FROM student
Jaap
NULL
Jansen
M.G.
De Vries
Jaap Jansen
NULL
M.G. de Vries
NULL
Pietersen
Bennie
Pietersen
NULL
Waanders
CASE
Bennie Waanders
CASE werkt als een if-then-else constructie. Maar in plaats van if wordt when gebruikt.
SELECT last_name,
CASE department_id
WHEN 90 THEN ‘Management’
WHEN 80 THEN ‘Sales’
WHEN 60 THEN ‘It’
ELSE ‘Other department’
END AS department
FROM employees;
Werkboek databases en SQL (ROC van Twente)
107
SELECT last_name AS achternaam, salary AS salaris,
CASE
WHEN salary > 20000 THEN ’Salaris zeer hoog: boven de 20.000.’
WHEN salary > 15000 THEN ’Salaris hoog: tussen de 15.000 en 20.000.’
WHEN salary > 10000 THEN ’Salaris niet slecht: tussen de 10.000 en 15.000.’
WHEN salary > 5000 THEN ’Salaris kan beter: tussen de 5.000 en 10.000.’
ELSE ’Salaris niet best: minder dan 5.000.’
END AS categorie
FROM employees
ORDER BY last_name;
Vragen sectie 5
Vraag 1:
Het datatype INTEGER is een eindige verzameling van gehele getallen. Kan een integer
negatief zijn? Is bijvoorbeeld -1 een INTEGER of niet?
a. ja
b. nee
Vraag 2:
Hoort het getal 0 bij de verzameling van gehele getallen? Is 0 een INTEGER?
a. ja
b. nee
Vraag 3: Een UNSIGNED INTEGER kan niet negatief zijn.
a. juist
b. onjuist
Vraag 4: Welke van onderstaande twee statements is juist?
Werkboek databases en SQL (ROC van Twente)
108
a. een CHAR heeft een vaste lengte
b. een VARCHAR heeft een vaste lengte
Vraag 5: Met DATE() kun je ook uren, minuten en seconden weergeven.
a. ja
b. nee
Vraag 6: Hoe noem je conversie van een datatype die automatisch plaatsvindt?
a. impliciete conversie
b. expliciete conversie
Vraag 7: Welke functies kun je gebruiken om datatypes om te zetten van het ene type
naar het andere type?
a. CAST of CONVERT
b. CAST of COALESCE
c. CONVERT of COALESCE
Vraag 8: Welke functies kun je gebruiken als je NULL wil vervangen door 0?
a. CAST en COALESCE
b. IFNULL en CAST
c. IFNULL en COALESCE
Vraag 9: Wat hoort er te staan op de plaats van de puntjes?
SELECT last_name,
… department_id
WHEN 90 THEN ‘Management’
WHEN 80 THEN ‘Sales’
WHEN 60 THEN ‘It’
ELSE ‘Other department’
END AS department
FROM employees;
a. CASE
b. COALESCE
c. IF
d. CAST
Vraag 10: Bekijk onderstaande query. Slechts vier employees hebben
een commission_pct. Bij de rest is de inhoud NULL. Wat zal de uitkomst zijn in de
kolommen vier en vijf? In kolom vier vermenigvuldig je en in kolom vijf deel je.
SELECT last_name, salary, commission_pct, salary * commission_pct, salary /
commission_pct
FROM employees;
Werkboek databases en SQL (ROC van Twente)
109
a. Zowel in de vierde als de vijfde kolom zal de uitkomst NULL zijn voor de werknemers
die geen commission_pct hebben.
b. Zowel in de vierde als de vijfde kolom zal de uitkomst 0 zijn voor de werknemers die
geen commission_pct hebben.
c. Er komt een foutmelding want in de vijfde kolom deel je door 0 en dat mag niet.
Opdrachten sectie 5
Query 1: Maak onderstaand overzicht na. De informatie komt uit de
tabel departments en er is gesorteerd op department_name.
Query 2: Toon de datum van vandaag. In het voorbeeld staat 16 augustus 2018 maar
daar moet dus de datum van vandaag komen in het Nederlands.
Query 3: Maak een overzicht dat alle namen van de werknemers toont met de datum
van indiensttreding. Voeg een extra kolom toe waarin alleen jaar en maand van in
indiensttreding worden getoond (bv 1987-06). Sorteer op achternaam.
Werkboek databases en SQL (ROC van Twente)
110
Query 4: Maak een overzicht dat de namen van alle werknemers toont en de datum
waarop ze in dienst zijn getreden. Bijvoorbeeld: Ellen Abel kwam 11 mei 1996 in dienst. Er
moet geen voorloop nul voor de dag van de maand. De gegevens komen uit de
tabel employees en er is gesorteerd op last_name.
Werkboek databases en SQL (ROC van Twente)
111
Query 5: Maak onderstaand overzicht na. De informatie komt uit de tabel countries en er
is gesorteerd op country_name.
Regio 1 = Europa
Regio 2 = Amerika
Regio 3 = Azië
Regio 4 = Midden Oosten en Afrika
Werkboek databases en SQL (ROC van Twente)
112
Query 6: Maak onderstaand overzicht na. De informatie komt uit de tabel employees en
er is gesorteerd op salaris van hoog naar laag en daarna op achternaam.
Werkboek databases en SQL (ROC van Twente)
113
…
Werkboek databases en SQL (ROC van Twente)
114
Query 7: Maak onderstaand overzicht na. De informatie komt uit de tabel employees en
er is gesorteerd op achternaam. Het salaris zoals in de tabel staat vermeld is het
maandsalaris. Er zitten twaalf maanden in een jaar er zijn gemiddeld 365.25 dagen in een
jaar.
Query 8: Maak onderstaande mededeling na. Het is de huidige tijd en de datum. De
uitvoer op jouw scherm zal dus een andere tijd en datum weergeven.
Werkboek databases en SQL (ROC van Twente)
115
Database Design
Inleiding
Bekijk, voordat je verder gaat, eerst deze video van Roel Grid over databases (ook te vinden op
sqltrainer.nl onder het tabblad Database Design):
In de voorgaande secties heb je geleerd hoe je met behulp van SQL gegevens uit een database kunt
halen. De HR database die daarbij is gebruikt bevat een flinke hoeveelheid aan elkaar gekoppelde
tabellen, maar we hebben tot nu toe alleen nog maar queries geschreven die gegevens uit één tabel
ophalen.
In dit hoofdstuk gaan we bezig met het zelf maken van een (eenvoudige) database met enkele
tabellen. Daarbij maken we geen gebruik van de oefenomgeving van SqlTrainer, maar van SQL Server
en SQL Server Management Studio. Van de site van Microsoft kun je voor ontwikkeldoeleinden de
developer editie van SQL Server downloaden (SQL Server Downloads | Microsoft).
Als je klaar bent met de installatie krijg je ook de mogelijkheid SQL Server Management Studio te
installeren, maar dat kun je ook rechtstreeks vanaf de site van Microsoft doen (Download SQL Server
Management Studio (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Docs).
Hoewel je ook op grafische wijze tabellen kunt aanmaken gaan we het hier alleen doen vanuit SQL.
Database maken
Het beheren van SQL Server databases doe je vanuit SQL Server Management Studio. Hiermee kun je
databases beheren op je eigen systeem, maar ook databases die zich op andere servers bevinden
(bijvoorbeeld op Microsoft Azure, de cloud omgeving van Microsoft).
Werkboek databases en SQL (ROC van Twente)
116
Nadat je SQL Server en Management Studio hebt geïnstalleerd kun je Management Studio opstarten
door te klikken op de Windows button en in het zoekvenster SSMS in te tikken:
Nadat SSMS is opgestart verschijnt een venster waarin je een connectie moet opgeven naar een SQL
Server. Als je de standaard installatie hebt uitgevoerd kun je een connectie maken met je eigen
server door bij Server name een punt op te geven (dat is hetzelfde als localhost). Als de connectie is
gemaakt zie je links in de Object Explorer diverse mappen:
We gaan nu eerst een database aanmaken. Daarvoor klik je in de menubalk op New Query
Er verschijnt nu een query venster waarin we onze SQL commando’s kunnen geven. Voor het maken
van de database gebruiken we het volgende commando:
Werkboek databases en SQL (ROC van Twente)
117
Selecteer vervolgens het ingevoerd commando en klik op Execute.
Controleer vervolgens in de object explorer of de database is aangemaakt. Het kan zijn dat je eerst
even moet verversen door rechts te klikken op Databases en dan te kiezen voor Refresh.
Tabellen aanmaken
Bekijk, voordat je verder gaat, eerst deze video’s van Roel Grid over databases (ook te vinden op
sqltrainer.nl onder het tabblad Database Design):
Werkboek databases en SQL (ROC van Twente)
118
Selecteer, voordat je nu verder gaat, eerst de zojuist aangemaakte database in de lijst links in de
menubalk:
We gaan nu de eerste tabel aanmaken in deze database. In deze tabel gaan we de volgende
informatie over docenten opslaan:
Code: 3 letters gevolgd door 2 cijfers, bijvoorbeeld RSK01.
Deze code dient als primary key en dat betekent dat deze uniek is en altijd ingevuld moet
worden.
Voorletters: de voorletters, gescheiden door punten, bijvoorbeeld M.G.
Dit is een verplicht veld
Tussenvoegels: eventuele tussenvoegsels zoals van den.
Dit veld is niet verplicht.
Achternaam: de achternaam
Dit is een verplicht veld.
Natuurlijk zou je nog veel informatie over docenten kunnen opslaan, zoals adresgegevens,
salarisgegevens etc., maar we houden het hier simpel.
Maak de tabel aan met het volgende SQL commando:
Werkboek databases en SQL (ROC van Twente)
119
Selecteer alleen het commando dat je uit wilt voeren en klik op Execute. Als je het uit te voeren
commando niet eerst selecteert wordt alle in het venster uitgevoerd en dan krijg je (natuurlijk) een
foutmelding die aangeeft dat de database al bestaat.
Toelichting:
Voor iedere kolom (veld) van je tabel geef je eerst een naam en daarna het bijbehorende
datatype. CHAR(5) betekent dat de code exact 5 tekens bevat. Als minder tekens worden
ingevoerd voor de docentcode wordt het opgevuld met spaties. CHAR gebruik je daarom
alleen als de lengte van alle codes gelijk is. Alle tekens zijn toegestaan.
NVARCHAR(10) betekent dat maximaal 10 tekens ingevoerd kunnen worden. De uiteindelijke
lengte is afhankelijk van het aantal ingevoerde tekens. Er wordt dus niet opgevuld met
spaties. Ook nu zijn alle tekens toegestaan.
PRIMARY KEY geeft aan dat de code gebruikt wordt als primaire sleutel. Alle docenten
moeten dus een unieke code hebben van maximaal 5 tekens.
NOT NULL geeft aan dat een kolom geen NULL waarde mag bevatten en dus verplicht
ingevuld moet worden.
NULL geeft aan dat een kolom wel een NULL waarde mag bevatten. Als je niets opgeeft is
NULL de standaardwaarde.
Als alles goed is gegaan staat de tabel Docent nu onder de map Tables van de database Myschool.
Ververs indien nodig deze map even als de tabel niet is toegevoegd.
Straks gaan we wat gegevens toevoegen aan de gemaakte tabel, maar eerst gaan we nog een tabel
toevoegen voor het opslaan van studentgegevens:
Studentnummer (een code van altijd 7 cijfers). Dit is tevens de primary key.
Voornaam, verplicht, maximaal 25 tekens
Tussenvoegsels, niet verplicht
Achternaam, verplicht, maximaal 50 tekens
Adres, verplicht, maximaal 50 tekens
Postcode, verplicht, altijd 4 cijfers en 2 letters
Woonplaats, verplicht, maximaal 50 tekens
Geboortedatum, de geboortedatum zonder tijd
Klas, niet verplicht, altijd 5 tekens, bijvoorbeeld I0SDa
Werkboek databases en SQL (ROC van Twente)
120
SLBer, niet verplicht, moet de code zijn van een bestaande docent
Het SQL commando om deze tabel aan te maken is:
Toelichting:
Voor Geboortedatum is DATE gekozen, omdat geen tijd toegevoegd hoeft te worden. Als je
wel een tijd nodig hebt kies je voor DATETIME of DATETIME2.
REFERENCES Docent(Code) zorgt voor een relatie tussen de tabellen Student en Docent. De
waarde van het veld SLBer moet overeenkomen met een waarde in de kolom Code van de
tabel Docent. Hiermee dwingen we referentiële integriteit af en dat zorgt ervoor dat de
relatie tussen deze tabellen altijd bewaakt wordt. SLBer is nu een zogenaamde foreign key
die verwijst naar de primary key van de tabel Docent.
o Als je een code invoert voor SLBer die niet voorkomt in de tabel Docent dan geeft de
database een foutmelding.
o Als je probeert een docent te verwijderen waar nog studenten aan zijn gekoppeld
(omdat hij/zij daar SLBer van is) dan verschijnt ook een foutmelding.
Relaties tussen tabellen zijn erg belangrijk en het is van groot belang dat de gegevens in een
database betrouwbaar zijn. Referentiële integriteit is daarbij ZEER BELANGRIJK.
Nu we de tabellen Docent en Student hebben aangemaakt kunnen we grafische weergave maken van
de database, waarin de relaties tussen de tabellen zichtbaar worden gemaakt. Klik daarvoor in de
database rechts op Database Diagrams en kies in het submenu voor New Database Diagram
Er verschijnt nu een venster waarin wordt aangegeven dat eerst wat verplichte objecten aangemaakt
moeten worden. Klik op Yes om dat uit te voeren.
Werkboek databases en SQL (ROC van Twente)
121
Daarna opent een venster met de tabellen die je inmiddels hebt aangemaakt. Selecteer beide
tabellen en voeg ze toe aan het diagram.
Selecteer in het venster dat verschijnt beide tabellen en klik rechts op één van de tabellen en kies
dan voor Table View -> Standard. Daarmee wordt alle informatie van de kolommen weergegeven.
In de editor worden nu beide tabellen getoond inclusief de relatie tussen de tabellen. Deze relatie is
een 1-op-veel relatie: één docent kan SLBer zijn van meerdere studenten, maar een student kan
maar één SLBer hebben.
Werkboek databases en SQL (ROC van Twente)
122
Door ergens buiten de tabellen met de rechter muisknop te klikken kun je een submenu opvragen,
waarin je via Arrange Tables de layout kunt refreshen.
Gegevens toevoegen aan tabellen
Bekijk, voordat je verder gaat, eerst deze video van Roel Grid over gegevens beheren in een database
(ook te vinden op sqltrainer.nl onder tabblad Database Design:
Nu we de tabellen hebben aangemaakt kunnen we enkele gegevens gaan toevoegen. Daarbij is van
belang dat je begint met het toevoegen van de docenten. Bij studenten moet je namelijk de code van
een bestaande docent invoeren als SLBer en als je nog geen docenten hebt kun je ook geen
studenten toevoegen, tenzij je het veld SLBer eerst leeg laat en later pas gaat invoeren.
Voor het toevoegen van gegevens aan een tabel maken we gebruik van het INSERT commando.
Helemaal aan het begin van dit werkboek is de afkorting CRUD al eens naar voren gekomen. Dat zijn
de 4 basishandelingen die we op iedere tabel kunnen uitvoeren:
Create
Read
Update
Werkboek databases en SQL (ROC van Twente)
123
Delete
Het INSERT commando valt onder Create.
Om een eerste (denkbeeldige) docent toe te voegen gebruik je het volgende SQL commando:
We kunnen desgewenst op de volgende wijze meerdere docent met één commando toevoegen:
Opdracht:
Voeg alle docenten waar je zelf les van hebt toe aan de tabel Docent.
Vervolgens laten we nog zien hoe je, op 2 verschillende manieren, studenten kunt toevoegen aan de
tabel Student:
Let op het verschil tussen de eerste en tweede methode. Bij de eerste methode moet je in de juiste
volgorde alle velden een waarde geven. Bij de tweede methode bepaal je zelf in welke velden je wilt
invoeren en in welke volgorde. Je hoeft op die manier dus niet alle velden van een waarde te
voorzien, maar je moet wel zorgen dat alle verplichte velden worden ingevoerd.
Opdracht:
Voeg minstens 5 studenten uit je eigen klas toe aan de tabel Student en oefen met beide methodes.
We gaan nog wat extra functionaliteit aan onze database toevoegen. We willen namelijk ook cijfers
kunnen toevoegen die studenten voor vakken hebben gehaald. Daarbij moeten we bedenken dat
voor één vak meerdere studenten een cijfer halen en dat één student ook voor meerdere vakken
cijfers haalt. Tussen een vak en een student bestaat daarom een veel-op-veel relatie en een
relationele database ondersteunt geen veel-op-veel relaties. Daarom moeten we nog een tabel
cijfers toevoegen waarin wordt vastgelegd welke student op welke datum voor welk vak een bepaald
cijfer heeft gehaald en welke docent dat cijfer heeft gegeven.
Bekijk, voordat je verder gaat, eerst deze video van Roel Grid over gegevens beheren in een database
(ook te vinden op sqltrainer.nl onder tabblad Database Design:
Werkboek databases en SQL (ROC van Twente)
124
De SQL commando’s om deze tabellen aan te maken zien er als volgt uit:
De tabel Vak zal we duidelijk zijn, maar de tabel Cijfer behoeft wel toelichting:
Studentnummer is een foreign key die verwijst naar het studentnummer van een bestaande
student.
Vak is een foreign key die verwijst naar de code van een bestaand vak.
Er is een samengestelde primary key, die bestaat uit de velden Datum, Studentnummer en
Vak. Dit betekent dat de combinatie van deze velden uniek moet zijn, maar individueel
mogen de velden wel vaker voorkomen. Dus:
o Een student kan voor één vak meerdere cijfers halen, maar niet op precies hetzelfde
moment (datum is een DATETIME en bevat dus ook het tijdstip).
o Voor een vak kunnen meerdere studenten een cijfer halen.
o Een student kan voor meerdere vakken een cijfer halen.
Het behaalde toetsresultaat moet als een geheel getal worden ingevoerd. Omdat het een
TINYINT is moet de waarde liggen tussen 0 en 255, maar we hebben een zogenaamde CHECK
CONSTRAINT toegevoegd die ervoor zorgt dat de waarde tussen 1 en 10 moet liggen.
Opdracht:

Voeg de tabellen Vak en Cijfer aan het eerder gemaakte database diagram.
Voeg alle vakken van jouw opleiding toe aan de tabel Vak.
Voeg de door jou behaalde cijfers toe aan de tabel Cijfer.
Werkboek databases en SQL (ROC van Twente)
125
Bekijk de volgende video’s van Roel Grid, waarin voorbeelden van databases aan bod komen. Ook te
vinden op sqltrainer.nl op tabblad Database Design:
Werkboek databases en SQL (ROC van Twente)
126
Sectie 6
Joins
Tot nu toe hebben we vrijwel alleen nog maar informatie gehaald uit één tabel, maar heel
vaak zul je informatie op willen halen die uit verschillende tabellen komt. Stel
bijvoorbeeld dat je alle achternamen wil tonen met hun department_id en
de department_name. De last_name staat in de tabel employees, department_name staat
in departments en het department_id staat zowel in employees als in departments. Probeer
onderstaande query en je zult zien dat je een foutmelding krijgt!
SELECT last_name, department_id, department_name
FROM employees, departments;
Het systeem meldt dat de kolom department_id ambigu (tweeslachtig) is. Dat komt
omdat department_id zowel in employees staat als in departments en het systeem weet
niet welke kolom je bedoelt. Door het gebruik van een alias voor de
tabellen employees en departments kun je aangeven uit welke
tabel department_id genomen moet worden. We passen nu de query aan door een alias
toe te voegen aan de beide tabellen. De tabel employees noemen we tijdelijk ‘e’ en de
tabel departments noemen we tijdelijk ‘d’. De query zal het nu wel doen, maar we hebben
een ander probleem!
SELECT e.last_name, d.department_id, d.department_name
FROM employees e, departments d;
Er verschijnen 2889 rijen en je ziet dat alle employees allemaal op
alle departments werken. Er zijn 107 employees en 27 departments en je krijgt dus 107 x
27 = 2889 rijen terug. In onderstaande afbeelding wordt een deel van de
uitvoer getoond. In zo’n geval spreekt men van een Cartesiaans of Cartesisch product.
Werkboek databases en SQL (ROC van Twente)
127
Je begrijpt dat dit niet de bedoeling is. King en Kochar werken alleen maar op de
afdeling Executive. Om de juiste gegevens te krijgen moet je iets heel belangrijks doen en
dat is dat je een join voorwaarde inbouwt. Dat kan bijvoorbeeld op de volgende manier
met een WHERE component.
SELECT e.last_name, d.department_id, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
Nu krijg je de juiste informatie met 106 rijen. Geen 107 want Grant werkt niet op een
afdeling.
Werkboek databases en SQL (ROC van Twente)
128
We noemen dit een equijoin. Equi (gelijk) slaat op het is-gelijk-teken. Er is een aantal
mogelijkheden om de juiste informatie uit verschillende tabellen te halen. Hierna komen
ze allemaal aan bod zodat je ze herkent.
In de praktijk wordt echter vooral de LEFT JOIN ON gebruikt en daar moeten we de
grootste aandacht naar uit laten gaan.
CROSS JOIN
Een CROSS JOIN voegt elke rij van een tabel samen met elke andere rij in een andere
tabel. Als je een tabel hebt met twintig rijen (employees bijvoorbeeld) die je samenvoegt
met een tabel van acht rijen (departments bijvoorbeeld), dan krijg je 160 rijen als
resultaat. Elke werknemer (employee) wordt aan elke afdeling (department) gekoppeld. In
zo’n geval spreekt men van een Cartesiaans of Cartesisch product, zoals al eerder
besproken.
Werkboek databases en SQL (ROC van Twente)
129
SELECT last_name, department_name
FROM employees CROSS JOIN departments;
Je begrijpt dat dit niet de bedoeling kan zijn.
De CROSS JOIN wordt in de praktijk zelden gebruikt, maar je kunt bijvoorbeeld testen of
kolomnamen en tabelnamen kloppen in een query en daarna pas de query verfijnen.
Werkboek databases en SQL (ROC van Twente)
130
INNER JOIN
JOIN is de allerbelangrijkste manier om tabellen aan elkaar te knopen en daarom
moet er heel veel geoefend worden met JOIN ON!
Na ON benoem je de kolomnamen die aan elkaar gelijk moeten zijn. Eerst noem je de
tabel, dan volgt er een punt en dan volgt de kolomnaam. Er zijn twee kolomnamen die je
met elkaar wil vergelijken, maar ze heten anders. In de tabel employees heet de
kolom salary en in de tabel job_grades heet de kolom lowest_sal. In onderstaand
voorbeeld vergelijk je salary van employees met min_salary van jobs. Deze personen
zitten dus in de laagste salarisschaal van job_grade.
SELECT e.first_name, e.last_name, e.salary, j.min_salary, j.ma
x_salary
FROM employees e JOIN jobs j ON e.salary = j.min_salary;
De tabellen employees en jobs worden samengevoegd op kolommen die niet dezelfde
kolomnaam hebben. Merk op dat salary twee decimalen heeft en
dat min_salary en max_salary geen decimalen hebben. De kolom salary is van het type
DECIMAL en de kolommen min_salary en max_salary zijn van het type INT.
JOIN ON werkt uiteraard ook als de kolomnamen van de verschillende tabellen wel
hetzelfde zijn.
Werkboek databases en SQL (ROC van Twente)
131
SELECT last_name, job_title
FROM employees JOIN jobs ON employees.job_id = jobs.job_id;
Het is mogelijk drie of meer tabellen te gebruiken om alle informatie in één uitvoer te
zetten. Voor de leesbaarheid kun je dan de joins onder elkaar zetten.
SELECT last_name, department_name, city
FROM employees
JOIN departments ON (employees.department_id = departments.dep
artment_id)
JOIN locations ON departments.location_id = locations.location
id; Werkboek databases en SQL (ROC van Twente) 132 OUTER JOIN Als je rijen uit meerdere tabellen ophaalt, kan het zijn dat sommige rijen uit de ene tabel niet gekoppeld (ge-joined) kunnen worden (door de join-voorwaarde) met een rij uit de andere tabel. Zo’n rij komt dan niet in je query resultaat. Soms wil je die rijen uit die ene tabel toch in je resultaat. Door gebruik te maken van een outer-join zal het DBMS deze rijen dan toch in je resultaat behouden, waarbij ze ge-joined worden met een (denkbeeldige) geheel lege rij (allemaal NULL’s) uit de andere tabel. In het voorbeeld van onze database heeft werknemer Grant bijvoorbeeld geen department_id (ze werkt dus niet op een bepaalde afdeling) en er zijn 16 departments waar niemand werkt. Werkboek databases en SQL (ROC van Twente) 133 … Werkboek databases en SQL (ROC van Twente) 134 Als we de tabellen employees en departments samenvoegen met een INNER JOIN krijgen we een uitvoer van 106 records terwijl er 107 werknemers zijn. Grant staat hier namelijk niet bij. SELECT first_name, last_name, d.department_id, department_name FROM employees e JOIN departments d ON d.department_id=e.department_id; Dit soort problemen kunnen we oplossen met LEFT JOIN en RIGHT JOIN. LEFT OUTER JOIN Bij een LEFT JOIN worden rijen uit de linker tabel in het query resultaat behouden als ze niet met een rij uit de rechter tabel gejoined kunnen worden. In plaats van LEFT JOIN wordt ook wel LEFT OUTER JOIN gebruikt. In de eerste tabel komen zwart, blauw en wit voor en in de tweede tabel blauw, wit en oranje. Met een LEFT JOIN worden alle rijen getoond van de linker tabel, dus in dit geval zwart, blauw en wit. Maar van de rechter tabel wordt alleen maar blauw en wit getoond en oranje niet. Werkboek databases en SQL (ROC van Twente) 135 SELECT last_name, department_name FROM employees LEFT OUTER JOIN departments ON employees.department_id = departments.department_id; Je ziet dat Grant nu wel in het linker rijtje voorkomt, ondanks het feit dat ze geen department_name heeft. En dat komt dus door de toevoeging van LEFT bij de JOIN. Bij een LEFT JOIN is het linkerrijtje het langst. Je mag tussen LEFT en JOIN OUTER toevoegen, maar dat is niet verplicht. Alleen LEFT JOIN is voldoende. RIGHT OUTER JOIN Bij een RIGHT JOIN worden rijen uit de rechter tabel in het query resultaat behouden als ze niet met een rij uit de linker tabel gejoined kunnen worden. In plaats van RIGHT JOIN wordt ook wel RIGHT OUTER JOIN gebruikt. IN de linker tabel komen zwart, blauw en wit voor en in de rechter tabel blauw, wit en oranje. Zwart wordt dus niet getoond, want die staat alleen maar aan de linkerkant. Oranje wordt wel getoond. Werkboek databases en SQL (ROC van Twente) 136 SELECT last_name, department_name FROM employees RIGHT JOIN departments ON employees.department
id = departments.department_id;
Je ziet dat de afdelingen zonder werknemers nu wel in het rechterrijtje voorkomen,
ondanks het feit dat er niemand op die afdeling werkt. En dat komt dus door de
toevoeging van RIGHT bij de JOIN. Bij een RIGHT JOIN is het rechterrijtje het langst.
In de praktijk worden RIGHT JOINS niet gebruikt. Het is niet erg nuttig een
overzicht te laten beginnen met een kolom met NULL. Bovendien kun je een OUTER
JOIN namaken met een LEFT JOIN door de kolomnamen om te draaien.
SELECT department_name, last_name
Werkboek databases en SQL (ROC van Twente)
137
FROM departments LEFT JOIN employees ON departments.department
_id = employees.department_id;
FULL OUTER JOIN
Je kunt een LEFT JOIN en een RIGHT JOIN combineren tot een FULL JOIN. Je krijgt dan
alle rijen van een kolom uit de ene tabel gecombineerd met alle rijen van een kolom uit
een andere tabel, ongeacht of er een match is of niet. In plaats van FULL JOIN wordt ook
wel FULL OUTER JOIN gebruikt.
SELECT employees.last_name, departments.department_id, departm
ents.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = depar
tments.department_id;
Werkboek databases en SQL (ROC van Twente)
138
Zowel Grant als de afdelingen waar niemand werkt komen op deze manier tevoorschijn.
…
Werkboek databases en SQL (ROC van Twente)
139
SELF JOIN
Het is mogelijk een tabel met zichzelf te joinen door twee aliassen te gebruiken voor
dezelfde tabel. De database denkt dan dat er twee tabellen zijn. In de
tabel employees staat bij manager_id wie de manager is van de employee. Dat nummer
bij manager_id verwijst naar het employee_id.

Je ziet dat King geen manager_id heeft, hij is de hoogste baas.
Kochar heeft als manager_id 100 en dat is dus King.
Hunold heeft als manager_id 102 en dat is dus De Haan.
etc.
Werkboek databases en SQL (ROC van Twente)
140
Als je een alias gaat kiezen, moet je een duidelijke naam nemen. In dit geval hebben
we worker voor de werknemers gekozen en manager voor de managers. We kunnen nu
een overzicht maken met de werknemers en hun managers.
SELECT worker.last_name, worker.manager_id, manager.last_name
AS manager_name
FROM employees worker JOIN employees manager
ON worker.manager_id = manager.employee_id;
En als je daar KING bij wil hebben, maak je er een LEFT JOIN van.
Werkboek databases en SQL (ROC van Twente)
141
SELECT worker.last_name, worker.manager_id, manager.last_name
AS manager_name
FROM employees worker LEFT JOIN employees manager
ON worker.manager_id = manager.employee_id;
De SELF JOIN komt sporadisch voor. In de database die wij gebruiken is er slechts
één SELF-JOIN te maken en die staat hierboven uitgelegd.
Vragen sectie 6
Vraag 1: Er zitten twintig werknemers in de tabel employees en er zijn twaalf rijen in de
tabel jobs. Elke employee heeft een bepaalde job. Hoeveel rijen krijg je terug met de
volgende query?
SELECT last_name, job_title
FROM jobs CROSS JOIN employees;
a. 12
b. 20
c. 240
d. De query geeft een foutmelding.
Werkboek databases en SQL (ROC van Twente)
142
Vraag 2: Zal onderstaande query werken?
SELECT e.last_name, j.job_title
FROM employees JOIN jobs ON e.job_id = j.job_id;
a. ja
b. nee
Vraag 3: Wat moet er staan op de plaats van de puntjes in onderstaande query?
SELECT e.last_name, j.employee_id
FROM employees e … job_history j ON
e.employee_id = j.employee_id;
a. LEFT JOIN
b. RIGHT JOIN
Vraag 4: Het is mogelijk een tabel met zichzelf te joinen door twee aliassen te gebruiken
voor dezelfde tabel. De database denkt dan dat er twee tabellen zijn. Hoe noem je zo’n
JOIN?
a. SELF JOIN
b. INNER JOIN
c. CROSS JOIN
d. EQUI JOIN
Werkboek databases en SQL (ROC van Twente)
143
Vraag 5: Welke join is er gebruikt in onderstaande afbeelding?
a. left outer join
b. right outer join
c. full outer join
Vraag 6: LEFT OUTER JOIN en RIGHT OUTER JOIN zijn exact hetzelfde als LEFT JOIN en
RIGHT JOIN. Het woordje OUTER kun je dus gewoon weglaten.
a. ja
b. nee
Vraag 9: Welke join is er gebruikt in onderstaande afbeelding?
a. left outer join
b. right outer join
c. full outer join
Opdrachten sectie 6
Query 1: Maak een overzicht van de department_names met de managers van
die departments zoals hieronder. Contracting heeft geen manager maar
die department_name moet er dus ook bij staan. Gebruik aliassen van één letter.
Werkboek databases en SQL (ROC van Twente)
144
Query 2: Maak onderstaand overzicht na. Het is een overzicht van employees met
een job_history. Gebruik e als alias voor employees en h als alias voor job_history.
Query 3: Maak onderstaande query na. De gegevens komen uit de
tabellen employees en job_history. Er is gesorteerd op last_name en daarna
op employee_id in omgekeerde volgorde.
Werkboek databases en SQL (ROC van Twente)
145
Query 4: Maak een overzicht van de employees die werken op de department_name
Shipping met hun job_title. Shipping staat in departments. De job_titles staan in jobs en
de last_name haal je uit employees. Gebruik aliassen van één letter, bijvoorbeeld e
(employees), d (departments) en j (jobs). Let op de sortering.
Query 5: Maak onderstaand overzicht na. De informatie komt uit de
tabellen countries en regions. Toon alleen de regio’s Europe en Americas. Sorteer op
regio en daarbinnen op naam van het land.
Werkboek databases en SQL (ROC van Twente)
146
Query 6: Maak onderstaande tabel na. De gegevens komen uit employees,
departments en jobs. Gebruik de aliassen e, d en j voor de tabellen. Er is gesorteerd
op last_name. Let op dat Grant voorkomt in het overzicht!
Query 7: Maak onderstaand overzicht na. Noem elke department_name en daarachter
de first_name en last_name aan elkaar van de manager. De departments zonder manager
moeten ook genoemd worden. Er is gesorteerd op department_name.
Werkboek databases en SQL (ROC van Twente)
147
Query 8: Maak onderstaand overzicht na. De gegevens komen uit de
tabellen employees en jobs. Er is gesorteerd op verschil van hoog naar laag en als dat
gelijk is op last_name. Met het verschil bedoelen we max_salary – min_salary.
Query 9: Maak onderstaand overzicht. De informatie komt uit vijf verschillende tabellen,
namelijk employees, departments, locations, countries en regions. Je moet dus alle vijf
tabellen aan elkaar knopen. Er is achtereenvolgens gesorteerd
Werkboek databases en SQL (ROC van Twente)
148
op region_name, country_name, city en last_name. Ook Grant moet voorkomen in het
overzicht en er mag geen NULL komen staan bij
haar department_name, city, country_name en region_name.
Werkboek databases en SQL (ROC van Twente)
149
Sectie 7
Subqueries
Met een subquery kun je eerst informatie opvragen die je nog niet weet. Op basis van die
informatie kun je vervolgens een nieuwe query maken. Stel bijvoorbeeld dat je een
overzicht wil maken van alle werknemers die in dienst zijn gekomen na Peter Vargas. Dan
zul je eerst moeten weten wanneer Peter Vargas in dienst is gekomen!
Eerst maar eens een heel foute manier!
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > ‘1998-07-09’;
Deze manier is erg verkeerd omdat je niet kunt weten dat de hire_date van Peter
Vargas 1998-07-09 is. Dat heb je waarschijnlijk opgezocht in de papieren versie of in in
de tabel employees. En dat mag dus niet!
Je moet eerst de hire_date van Peter Vargas ophalen met een query.
SELECT hire_date
FROM employees
WHERE last_name = ‘Vargas’
Dan neem je die query en die zet je tussen haakjes. Dit gedeelte noemen we
de subquery of ook wel de inner query.
(SELECT hire_date
FROM employees
WHERE last_name = ‘Vargas’)
Werkboek databases en SQL (ROC van Twente)
150
En dan zetten we daar een andere query voor. Dit gedeelte is de main query of ook wel
de outer query genoemd.
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date >
(SELECT hire_date
FROM employees
WHERE last_name = ‘Vargas’);
Een ander voorbeeld: Wie werkt er op dezelfde afdeling als Lex De Haan?
Eerst vraag je het department_id op van De Haan.
SELECT department_id
FROM employees
WHERE last_name = ‘De Haan’
Neem die query en zet die tussen haakjes.
(SELECT department_id
FROM employees
WHERE last_name = ‘De Haan’)
En dan zetten we daar een andere query voor.
Werkboek databases en SQL (ROC van Twente)
151
SELECT last_name
FROM employees
WHERE department_id =
(SELECT department_id
FROM employees
WHERE last_name = ‘De Haan’);
Wil je daar De Haan zelf niet bij? Dan zet je in de WHERE met != erbij dat de achternaam
niet gelijk mag zijn aan De Haan.
SELECT last_name
FROM employees
WHERE last_name != ‘De Haan’ AND department_id =
(SELECT department_id
FROM employees
WHERE last_name = ‘De Haan’);
Er zijn twee soorten subquery’s:
single-row subquery’s
multiple-row subquery’s
Single-row subqueries
Single-row subquery’s geven maar één rij terug.

Ze gebruiken single-row vergelijkingsoperatoren: #

=
<= <>

De subquery komt tussen haakjes.

Voor de bovenste en de onderste query kun je verschillende tabellen gebruiken.
Werkboek databases en SQL (ROC van Twente)
152

Je mag maar één keer ORDER BY gebruiken en dat moet de laatste regel zijn van
de bovenste query (main SELECT statement). Die komt dus helemaal onderaan.
Wie werkt er allemaal op de afdeling Marketing? Dat zijn de personen met
hetzelfde department_id, het department_id van de afdeling Marketing.
Eerst haal je dus het department_id op van de afdeling Marketing. Dat staat in de
tabel departments.
SELECT department_id
FROM departments
WHERE department_name = ‘Marketing’
Zet die query tussen haakjes en zet er een andere query voor. Voor de duidelijkheid laten
we de subquery inspringen.
SELECT last_name, job_id, department_id
FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name = ‘Marketing’)
ORDER BY job_id;
Werkboek databases en SQL (ROC van Twente)
153
Je kunt meerdere subquery’s gebruiken.
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND department_id =
(SELECT department_id
FROM departments
WHERE location_id = 1500);
Multiple-row

Multiple-row subquery’s kunnen meer dan één resultaat opleveren. Dan kun je de
single-row operators (zoals bijvoorbeeld het = teken) niet gebruiken.

Ze gebruiken multiple-row vergelijkingsoperatoren:
IN
ANY
ALL

De NOT operators kan bij alle drie gebruikt worden.
Naar IN zullen we kijken maar ANY en ALL laten we buiten beschouwing in deze
cursus.
Met de IN operator in de outer query kun je alle rijen selecteren die voorkomen in een
lijstje van waarden dat gemaakt is met de inner query.
Stel dat we alle namen van werknemers willen die in hetzelfde jaar zijn aangenomen als
werknemers van afdeling 90. Eerst moeten we een subquery (inner query) maken om een
lijstje op te halen met de jaren waarin werknemers van afdeling 90 zijn aangenomen.
SELECT YEAR(hire_date)
Werkboek databases en SQL (ROC van Twente)
154
FROM employees
WHERE department_id = 90
Die subquery zetten we tussen haakjes en daar zetten we de outer query boven.
SELECT last_name, YEAR(hire_date)
FROM employees
WHERE YEAR(hire_date) IN
(SELECT YEAR(hire_date)
FROM employees
WHERE department_id = 90);
Exists
EXISTS en NOT EXISTS kunnen worden gebruikt voor subquery’s.
Stel dat je wil onderzoeken welke department_id’s in de tabel departments staan en ook
bestaan (exist) in de tabel employees.
SELECT department_id
FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id)
ORDER BY department_id;
Werkboek databases en SQL (ROC van Twente)
155
Of juist het tegenovergestelde. Welk department_id komt wel voor in departments maar
bestaat niet (not exists) niet in employees?
SELECT department_id
FROM departments
WHERE NOT EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id)
ORDER BY department_id;
Welke werknemers zijn geen manager?
Werkboek databases en SQL (ROC van Twente)
156
Dat kun je onderzoeken met een SELF JOIN en een SUBQUERY. Bij een SELF JOIN gebruik
je dezelfde tabel twee maal met verschillende aliassen. In dit voorbeeld wordt de
tabel employees één keer gebruikt als emp en één keer als mgr.
SELECT last_name AS not_a_manager
FROM employees e
WHERE NOT EXISTS
(SELECT *
FROM employees m
WHERE m.manager_id = e.employee_id);
Null
Het kan zijn dat bij een single-row subquery de subquery als resultaat NULL oplevert. Zo
heeft Grant bijvoorbeeld geen department_id.
SELECT department_id
FROM employees
WHERE first_name = ‘Kimberely’ and last_name = ‘Grant’
En we stellen de vraag wie er op dezelfde afdeling werken als Grant.
SELECT last_name
Werkboek databases en SQL (ROC van Twente)
157
FROM employees
WHERE department_id =
(SELECT department_id
FROM employees
WHERE last_name = ‘Grant’);
Dan krijg je een leeg resultaat terug. Geen enkele rij dus.
Een vergelijking met NULL levert altijd FALSE op. En als een conditie FALSE is, worden de
rijen uitgesloten van het query resultaat.
Als bij een multiple-row query één van de waarden van de subquery NULL is, dan worden
enkel de waarden die niet NULL zijn gebruikt.
Welke werknemers zijn er allemaal manager? Dat zijn de manager_id’s die voorkomen in
de lijst van employee_id’s.
Eerst halen we alle manager_id’s op. Daar zit één NULL waarde bij. King is de grootste
baas en hij heeft geen manager.
SELECT DISTINCT manager_id
FROM employees;
Die subquery (inner query) zetten we tussen haakjes en daar zetten we de outer
query boven.
Werkboek databases en SQL (ROC van Twente)
158
SELECT last_name, employee_id
FROM employees
WHERE employee_id IN
(SELECT DISTINCT manager_id
FROM employees);
Het is altijd oppassen met NULLS in de subquery bij het gebruik van IN of NOT IN. Als je
niet zeker weet of een NULL voor gaat komen in de subquery en er misschien een
verkeerd resultaat uit volgt, kun je beter die NULL uitsluiten met een WHERE.
SELECT last_name, employee_id
FROM employees
WHERE employee_id IN
(SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL);
Groepsfuncties
Het is mogelijk groepsfuncties te gebruiken in subquery’s, zoals MIN, MAX, SUM en AVG.
Wie verdient er minder dan het gemiddelde salaris?
Eerst maak je de query voor het gemiddelde salaris met de groepsfunctie AVG.
SELECT AVG(salary)
FROM employees;
Werkboek databases en SQL (ROC van Twente)
159
Dat zet je tussen haakjes en daar zet je een andere query voor.
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees); Werkboek databases en SQL (ROC van Twente) 160 Vragen sectie 7 Vraag 1: Hoe goed is jouw Nederlands? Wat is het meervoud van query? a. queries b. query’s Vraag 2: Bekijk de volgende query. SELECT first_name, last_name, hire_date FROM employees WHERE hire_date >
(SELECT hire_date
FROM employees
WHERE last_name = ‘Vargas’);
Deze query bestaat uit twee query’s. Welk gedeelte is nu de subquery?
a. de bovenste (niet tussen haakjes)
b. de onderste (tussen haakjes)
Vraag 3: Wat is het verschil tussen een subquery en een inner query?
Vraag 4: Geef een andere naam voor main query.
a. outer query
b. inner query
c. subquery
Vraag 5: Een inner query staat tussen haakjes.
a. ja
b. nee
Vraag 6: IN en NOT IN zijn multiple-row vergelijkingsoperatoren.
a. juist
b. onjuist
Vraag 7: Een subquery kan geen ORDER BY hebben.
a. juist
b. onjuist
Werkboek databases en SQL (ROC van Twente)
161
Vraag 8: Je kunt meerdere subquery’s gebruiken binnen één statement.
a. juist
b. onjuist
Vraag 9: Bekijk deze query.
SELECT last_name, YEAR(hire_date)
FROM employees
WHERE YEAR(hire_date) =
(SELECT YEAR(hire_date)
FROM employees
WHERE department_id = 90);
Wat zal het resultaat zijn?
a. alle employees van afdeling 90 worden getoond met het jaar van hun hire_date
b. je krijgt een foutmelding
Vraag 10: Wat is het resultaat van deze query?
SELECT last_name
FROM employees
WHERE department_id =
(SELECT department_id
FROM employees
WHERE last_name=’Grant’);
a. je krijgt de naam Grant als enige
b. je krijgt alle namen van de employees die op dezelfde afdeling werken als Grant
c. je krijgt een lege set terug (0 rijen)
d. je krijgt een foutmelding, de query werkt niet
Werkboek databases en SQL (ROC van Twente)
162
Opdrachten sectie 7
Query 1: Wie heeft er dezelfde manager (hetzelfde manager_id) als de
werknemer Davies?
Query 2: Maak nu dezelfde query als bij 1 maar zorg dat Davies er zelf niet bij staat en
sorteer op last_name.
Query 3: Wie hebben er allemaal dezelfde job_id als Matos? Zorg dat Matos er zelf niet bij
staat.
Werkboek databases en SQL (ROC van Twente)
163
Query 4: Wie verdient er minder dan Bernstein? Let op de sortering.
Werkboek databases en SQL (ROC van Twente)
164
Query 5: Wie werken er allemaal op afdeling 50 maar hebben niet
hetzelfde job_id als Mourgos? Let op de sortering.
Query 6: Toon alle werknemers die minder verdienen dan het gemiddelde salaris. Sorteer
op salaris en daarna op achternaam.
Werkboek databases en SQL (ROC van Twente)
165
Query 7: Wie zijn er allemaal géén manager? Dat zijn de werknemers
die niet voorkomen met hun employee_id in de kolom manager_id. Maar let op bij Steven
King, want die heeft bijvoorbeeld géén manager_id want hij is de grootste baas. Je moet
de rijen waarvan het manager_id NULL is uitsluiten, anders krijg je een leeg resultaat. Zie
de uitleg bij Subquery – Null.
Werkboek databases en SQL (ROC van Twente)
166
Query 8: Welke regions komen niet voor in de tabel countries? Gebruik NOT EXISTS.
Query 9: In welke landen hebben ze één of meerdere locaties? Gebruik WHERE EXISTS.
Query 10: Welke employees staan wel in de tabel job_history maar komen niet voor in de
tabel employees?
Werkboek databases en SQL (ROC van Twente)
167
Sectie 8
GROUP BY
Tot nu toe hebben we uitsluitend query’s gezien waarbij informatie werd verwacht met
betrekking tot individuele rijen uit de tabellen. Het komt echter regelmatig voor dat we
geïnteresseerd zijn in geaggregeerde informatie. Hiermee wordt bedoeld informatie die
niet meer is gebaseerd op afzonderlijke rijen, maar op verzamelingen van rijen. Rijen die
op een bepaalde manier bij elkaar horen.
Stel bijvoorbeeld dat we een overzicht willen van het gemiddelde salaris (salary) van elke
afdeling (department_id). Bij dit soort query’s hebben we de GROUP BY component van
het SELECT commando nodig. Voor het gemak hebben we de gemiddelde salarissen
afgerond op 2 decimalen.
SELECT ROUND(AVG(salary),2)
FROM employees
GROUP BY department_id;
Nu zegt zo’n overzicht met enkel gemiddelde salarissen niet zo veel. Je ziet niet meteen
waar die gemiddelde salarissen op slaan. Daarom zetten we er een kolom bij die
aangeeft welke afdeling (department_id) bij welk gemiddelde salaris hoort.
SELECT department_id, ROUND(AVG(salary),2)
FROM employees
GROUP BY department_id;
Werkboek databases en SQL (ROC van Twente)
168
De rijen worden eerst gegroepeerd op department_id en daarna wordt de AVG functie
(gemiddelde) toegepast op elke groep.
Of stel dat je het maximum salaris wil weten van elke afdeling. Eerst een overzicht zonder
de afdelingsid’s.
SELECT MAX(salary)[Hoogste salaris]
FROM employees
GROUP BY department_id;
Om te laten zien welk maximum salaris bij welke afdeling hoort, voeg je
het department_id toe op de SELECT regel.
SELECT department_id [Afdeling], MAX(salary) [Hoogste salaris]
FROM employees
GROUP BY department_id;
Werkboek databases en SQL (ROC van Twente)
169
Als je een groepsfunctie gebruikt op de SELECT regel voorafgegaan door een
kolomnaam, dan moet die kolomnaam genoemd worden op de GROUP BY regel.
Dus als er staat SELECT department_id, MAX (salary) op de SELECT regel, dan moet op de
GROUP BY regel staan GROUP BY department_id.
De syntaxis is altijd in deze volgorde:
SELECT kolomnaam, groepsfunctie
FROM tabel
WHERE
GROUP BY kolomnaam
HAVING
ORDER BY
Voorbeeld: Tel het aantal per job_id. Maar IT_PROG mag niet voorkomen in het overzicht
en alleen de job_id’s die meer dan één keer voorkomen mogen worden getoond.
SELECT job_id [Functie], COUNT(job_id)[Aantal werknemers]
FROM employees
WHERE job_id <> ‘IT_PROG’
GROUP BY job_id
HAVING COUNT() > 1 ORDER BY COUNT(job_id); Werkboek databases en SQL (ROC van Twente) 170 Null waarden Groepsfuncties negeren NULL waarden. Vergelijk maar eens de volgende twee query’s waarbij belangrijk is om te weten dat er één werknemer is zonder een department_id (Grant). En let dan op het verschil in de bovenste rij. SELECT department_id, COUNT(department_id) FROM employees GROUP BY department_id; SELECT department_id, COUNT()
FROM employees
GROUP BY department_id;
Werkboek databases en SQL (ROC van Twente)
171
Where
Het is mogelijk een WHERE te gebruiken in combinatie met GROUP BY.
Stel dat je een overzicht wil maken met de hoogste salarissen per afdeling. Dat kan met
de volgende query.
SELECT department_id, MAX(salary) [Hoogste salaris]
FROM employees
GROUP BY department_id;
In de WHERE kun je voorwaarden stellen. Stel bijvoorbeeld dat het salaris van de hoogste
baas niet mee mag tellen voor het overzicht. King werkt op afdeling 90. Van die afdeling
zal nu het hoogste salaris worden aangepast.
SELECT department_id, MAX(salary) [Hoogste salaris]
Werkboek databases en SQL (ROC van Twente)
172
FROM employees
WHERE last_name != ‘King’
GROUP BY department_id;
Order by
Sorteren bij groepsfuncties kan op verschillende manieren. Het kan met het noemen van
de groepsfunctie die op de eerste regel staat (MAX(salary)), met behulp van de alias van
de kolom (maximum_salaris) of door het nummer van de volgorde van de kolom te
noemen, in dit geval 2.
SELECT department_id AS afdeling, MAX(salary)AS maximum_salaris
FROM employees
GROUP BY department_id
ORDER BY MAX(salary);
SELECT department_id AS afdeling, MAX(salary) AS maximum_salaris
FROM employees
GROUP BY department_id
ORDER BY maximum_salaris;
SELECT department_id AS afdeling, MAX(salary) AS maximum_salaris
FROM employees
GROUP BY department_id
ORDER BY 2;
Werkboek databases en SQL (ROC van Twente)
173
Having
Stel dat je het maximum salaris wil zien van elke afdeling, maar enkel van de afdelingen
met meer dan één werknemer. Dat kun je niet bereiken met een WHERE maar je moet
daar een HAVING voor gebruiken.
Eerst maken we de query zonder HAVING.
SELECT department_id, MAX(salary) [Hoogste salaris]
FROM employees
GROUP BY department_id;
Werkboek databases en SQL (ROC van Twente)
174
En dan de query met HAVING zodat je het verschil ziet.
SELECT department_id, MAX(salary) [Hoogste salaris]
FROM employees
GROUP BY department_id
HAVING COUNT() > 1; We maken een alias ‘afdeling’ voor department_id en een alias ‘max_salaris’ voor MAX(salary). We sorteren op het maximum salaris (kolom 2 op de bovenste regel) van hoog naar laag (DESC). SELECT department_id AS afdeling, MAX(salary) AS max_salaris FROM employees GROUP BY department_id HAVING COUNT() > 1
ORDER BY 2 DESC;
Werkboek databases en SQL (ROC van Twente)
175
En we voegen nog een WHERE toe om de query compleet te maken. De afdeling met
als department_id 90 mag niet meetellen. Op de regel met ORDER BY mag je ook het
alias (max_salaris) gebruiken dat op de SELECT regel staat.
SELECT department_id AS afdeling, MAX(salary) AS max_salaris
FROM employees
WHERE department_id != 90
GROUP BY department_id
HAVING COUNT() > 1 ORDER BY 2 DESC; Voor het ongelijk teken != mag je ook <> gebruiken. Werkboek databases en SQL (ROC van Twente) 176 Vragen sectie 8 Vraag 1: Bekijk onderstaande query en het resultaat. SELECT last_name, commission_pct FROM employees WHERE last_name = ‘Zlotkey’ OR last_name = ‘Hartstein’; Wat zal dan het resultaat zijn van deze query? SELECT ROUND(AVG(commission_pct),2) FROM employees WHERE last_name = ‘Zlotkey’ OR last_name = ‘Hartstein’; a. 0.10 b. 0.20 c. NULL. Als er in één rij het resultaat NULL is, dan is het gemiddelde van die rijen ook NULL. d. De query werkt niet, je mag niet delen door NULL. Vraag 2: Welke query zal werken? a. SELECT department_id AS afdeling, MAX(salary) AS max_salaris FROM employees GROUP BY afdeling WHERE department_id =! 90 HAVING COUNT() > 1
ORDER BY max_salaris DESC;
b.
SELECT department_id AS afdeling, MAX(salary) AS max_salaris
FROM employees
WHERE department_id =! 90
HAVING COUNT() > 1 GROUP BY afdeling ORDER BY max_salaris DESC; c. SELECT department_id AS afdeling, MAX(salary) AS max_salaris FROM employees GROUP BY afdeling HAVING COUNT() > 1
WHERE department_id =! 90
Werkboek databases en SQL (ROC van Twente)
177
ORDER BY max_salaris DESC;
d.
Ze werken geen van alle.
Vraag 3: Wat moet er staan op de laatste regel bij de drie puntjes om te sorteren?
SELECT department_id AS afdeling, MAX(salary) AS maximum_salar
is
FROM employees
GROUP BY department_id
ORDER BY …;
a. MAX(salary)
b. maximum_salaris
c. 2
d. Alle drie genoemde mogelijkheden die hierboven genoemd zijn, zullen exact hetzelfde
resultaat opleveren.
Vraag 4: Het is mogelijk een WHERE te gebruiken in combinatie met GROUP BY.
a. waar
b. niet waar
Vraag 5: Noem vijf groepsfuncties.
Vraag 6: Hoeveel rijen worden er geretourneerd met onderstaande query.
SELECT SUM(*)
FROM employees;
a. Eén.
b. Net zoveel als er employees zijn.
c. Het aantal employees min de employee die geen department_id heeft.
d. De query werkt niet.
Vraag 7: Wat wordt er geretourneerd met onderstaande query?
SELECT MIN(last_name)
FROM employees;
a. Alle achternamen die met de letter ‘A’ beginnen.
b. Alle achternamen die met de letter ‘Z’ beginnen.
c. Eén achternaam, namelijk de achternaam die helemaal bovenaan komt als je sorteert
op alfabet.
d. Eén achternaam, namelijk de achternaam die helemaal onderaan komt als je sorteert
op alfabet.
Werkboek databases en SQL (ROC van Twente)
178
e. De query werkt niet. Je kunt MIN niet gebruiken voor alfanumerieke velden.
Vraag 8: Gaat onderstaande query werken?
SELECT MIN(salary), MAX(salary), AVG(salary), COUNT(salary), S
UM(salary)
FROM employees;
a. Ja, je mag meerdere groepsfuncties op de SELECT regel gebruiken.
b. Nee, je mag niet meer dan één groepsfunctie op de SELECT regel gebruiken.
Vraag 9: Mag je een ALIAS gebruiken in de GROUP BY. Werkt bijvoorbeeld een query
zoals onderstaand?
SELECT department_id AS afdeling, COUNT(department_id)
FROM employees
GROUP BY afdeling
a. ja
b. nee
Vraag 10: Wat toon je met deze query?
SELECT last_name, hire_date
FROM employees
WHERE hire_date =
(SELECT MAX(hire_date)
FROM employees);
a. De last_name en de hire_date van de werknemer die het eerst in dienst is gekomen.
b. De last_name en de hire_date van de werknemer die het laatst in dienst is gekomen.
c. Alle achternamen van de werknemers met de datum waarop ze in dienst zijn gekomen.
d. De query werkt niet, want je kunt MIN en MAX niet gebruiken met datatypes.
Werkboek databases en SQL (ROC van Twente)
179
Opdrachten sectie 8
Query 1: Maak een overzicht dat per job_id het aantal medewerkers met die job_id
toont. Er is niet gesorteerd, maar let op de kolomnamen.
Query 2: Maak een overzicht dat per job_id het aantal medewerkers met die job_id
toont. Sorteer op aantal van hoog naar laag en daarna alfabetisch op functie. Alleen de
jobs met meer dan 1 werknemer moeten worden getoond.
Werkboek databases en SQL (ROC van Twente)
180
Query 3: Maak een overzicht dat per land het aantal locaties laat zien. Let op de
kolomnamen. De informatie komt uit de tabel locations. Er is niet gesorteerd. Alleen de
volgende landen moeten worden getoond: CA, UK en US.
Query 4: Maak een overzicht dat alle afdelingsnummers toont met daarachter het aantal
werknemers op die afdeling. De informatie komt uit de tabel employees.
Query 5: Pas de query van opdracht 4 aan zodat je onderstaand overzicht krijgt. Enkel de
afdelingen met een aantal van meer dan twee moeten worden geselecteerd. Er is
gesorteerd op aantal van hoog naar laag en daarna op afdeling.
Werkboek databases en SQL (ROC van Twente)
181
Query 6: Maak een overzicht dat alle afdelingsnummers toont met daarachter het aantal
werknemers op die afdeling.
De informatie komt uit de tabel employees.
Alleen de afdelingen met meer dan 2 werknemers moeten worden getoond. De afdeling
waar Steven King werkt moet niet worden getoond en de afdeling waar Hunold werkt
ook niet.
Query 7: Maak een overzicht dat alle department_id’s toont met de totale salarissen die
daar bij horen. De informatie komt uit de tabel employees.
Query 8: Pas de query van opdracht 7 aan. De afdeling die NULL is mag niet meer
meetellen. Enkel de afdelingen met meer dan 20.000 totaal moeten in beeld komen en er
is gesorteerd op totaal salaris van hoog naar laag.
Werkboek databases en SQL (ROC van Twente)
182
Query 9: Tenslotte gaan we de query van de opdrachten 7 en 8 nog een beetje
aanpassen. Bij totaal_salaris moet er een dollarteken voor het bedrag komen, het bedrag
is afgerond op nul decimalen en er staat een komma met een is-gelijk teken achter het
bedrag.
Query 10: Toon het gemiddeld aantal maanden dat er gewerkt is per afdeling. De
informatie komt uit de tabel job_history. Er is gesorteerd van hoog naar laag op de
tweede kolom. Je neemt het verschil tussen de datums start_date en end_date en daar
moet het gemiddelde van worden getoond per department_id.
Werkboek databases en SQL (ROC van Twente)
183

Windows Server

Cisco

Powershell

Python

SQL

Flowchart

SEC

Werkboek Databases V3.2 2021

Ze gebruiken single-row vergelijkingsoperatoren: #

Ze gebruiken single-row vergelijkingsoperatoren: #

Share This Article :