SQL Server - Telenet Users

Overzicht databanken - Oefeningen
• SQL - SQL Server 2000
Werken met 1 tabel : SELECT, Statistische functies,
GROUP BY
Werken met meerdere tabellen : JOIN, UNION,
subselects, gecorreleerde subqueries
Definitie van tabellen, indexen
Wijzigen van tabellen : insert, update, delete
Views
Rechten en rollen
De catalogus
Triggers en stored procedures
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 1
Overzicht databanken - Oefeningen
Cursus
 Slides : op Dokeos
 Syllabus
Punten
 Permanente evaluatie : 20%
– 1 gequoteerde oefening
 Examen
– Theorie : 40%
– Oefeningen : 40%
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 2
Overzicht databanken - Oefeningen
Voor de oefeningen en voorbeelden wordt gebruik gemaakt
van Microsoft SQL Server 2000
Downloaden software http://www.ma3d.com/
“De hogeschool heeft met Signpost een overeenkomst i.v.m. het Microsoft
MSDN Academic Alliance programma afgesloten. Het betreft hier het
(zonder licentiekosten) ter beschikking stellen van de Microsoft serveren ontwikkelingsprogrammatuur voor studenten en docenten uit
"informatica"-opleidingen. Het gebruik is uitdrukkelijk beperkt tot zuiver
pedagogische toepassingen(op de hogeschool en thuis).
Signpost staat in voor de distributie van de software via internet. Voor de
aanbieding MSDNAA wordt per gebruiker een jaarlijkse bijdrage van €
27.50, excl. BTW gevraagd met een downloadlimiet van 12.5 Gbytes.
Deze bevat o.a. SQL Server 2000, Visual Studio.Net,… Studenten
registreren zich rechtstreeks bij Signpost via
http://www.ma3d.com/msdnaa en betalen het bedrag rechtstreeks aan
Signpost. Ook alle info kan op deze site gevonden worden.”
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 3
Overzicht databanken - Oefeningen
Hardware en Software vereisten + uitleg verschillende
SQL server edities en vereisten qua OS :
http://msdn.microsoft.com/library/default.asp?url=/library/e
n-us/instsql/in_overview_74vn.asp
Installatie procedure :
http://msdn.microsoft.com/library/default.asp?url=/library/e
n-us/howtosql/ht_install_1xwl.asp
SQL Server Home : http://www.microsoft.com/sql
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 4
Overzicht databanken - Oefeningen
• SQL Server :
Beheer
 Installatie, configuratie en beveiliging van SQL Server.
 Aanmaken van databanken
 Beheer van databanken : backup, restore, ...
 Gebeurt adhv Enterprise manager
– Client tool
– Is een Microsoft Management Console snap-in
– Toegankelijk via Programs > Microsoft SQL Server 2000 > Enterprise Manager
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 5
Overzicht databanken - Oefeningen
 Voor de oefening maken we gebruik van de planten database
die deel uitmaakt van de SQL Server, geinstalleerd op de
SQLSERVER server. Om vanop je client deze server te kunnen
bekijken dien je
 Start Enterprise Manager
 In linker venster, rechtermuisklik op SQL Server Group. Selecteer New SQL
Server Registration
 Vul de gegevens in
– Op eigen PC
» SQL Server = naam PC of localhost
» “Use Windows Authentication”
– Op school
» SQL Server = SQLSERVER
» “Use SQL Server Authentication”
» Login Name : studentsql
» paswoord : studentsql
 Nu is de database server beschikbaar
en kan je de verschillende databanken
beheren
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 6
Overzicht databanken - Oefeningen
 Bevragen gegevens in SQL Server : via de Query Analyzer
 Programs > Microsoft SQL Server 2000 > Query Analyzer
 Connecteer met
– Op eigen PC
» SQL Server = naam PC of localhost
» Connecting using “Use Windows Authentication”
– Op school
» SQL Server = SQLSERVER
» SQL Server Authentication
» Login Name : studentsql, paswoord : studentsql
 In de Query Analyzer
– Selecteer eerste de gewenste database in de DB listbox, bvb Northwind
– Typ de query in
– Selecteer de Execute query –knop (F5)
– Transact SQL-help :
selecteer een instructienaam
en druk op SHIFT+F1 of via Programs >
SQL Server 2000 > Books Online
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 7
Overzicht databanken - Oefeningen
Oefeningen maken gebruik van
 planten database
 Installatie procedure : zie Dokeos
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 8
Overzicht databanken - Oefeningen
 De voorbeelden in de theorie maken gebruik van de Products tabel
uit de Northwind databank
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 9
SQL - standaarden en dialecten
• Definitie
Relationele gegevenstaal voor relationele database
systemen.
Niet procedurele taal
• Standaard : ANSI/ISO-1992
• Database systemen
Oracle : PL/SQL
SQL Server : TRANSACT-SQL
DB2 (IBM)
Informix
Sybase
Access
MySQL
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 10
SQL - Overzicht
• SQL bestaat uit 3 subtalen
Data Definition Language (DDL) : creatie van een database,
en het definiëren van database objecten (tabellen, stored
procedures, views,…)
 CREATE, ALTER, DROP
Data Manipulation Language (DML) : opvragen en
manipuleren van de gegevens in een database
 SELECT, INSERT, UPDATE, DELETE
Data Control Language (DCL) : gegevensbeveiliging en
authorisatie
 GRANT, REVOKE, DENY
Bijkomende taal elementen : operatoren, functies , control
of flow (dialect gebonden)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 11
SQL - DML
•
Eén tabel raadplegen
1. Basisvorm
2. SELECT clausule
3. WHERE clausule
4. Formatteren van rijen
5. Statistische functies
6. Groeperen
•
Meerdere tabellen raadplegen
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 12
SQL - DML
•
Eén tabel raadplegen
1. Basisvorm
SELECT [ALL | DISTINCT] {*|uitdrukking [,uitdrukking ...]}
FROM tabelnaam
[WHERE voorwaarde(n)]
[GROUP BY kolomnaam [,kolomnaam ...]
[HAVING voorwaarde(n)]
[ORDER BY {kolomnaam|volgnr}{ASC|DESC}[,...]
Toont een lijst
–
SELECT clausule : specifieert de kolommen die je wenst te zien. DISTINCT zorgt
ervoor dat de getoonde rijen alle uniek zijn
–
FROM clausule : geeft aan uit welke tabel de gegevens afkomstig zijn
–
WHERE clausule : opgave van de voorwaarden waaraan de getoonde rijen moeten
voldoen
–
ORDER BY clausule : bepaalt de volgorde waarin de rijen getoond moeten worden
–
GROUP BY en HAVING clausule : groeperen van de gegevens
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 13
SQL - DML
2. SELECT clausule : Specificatie van de kolommen
a) * : Ophalen van alle kolommen uit 1 tabel
– Voorbeeld : Toon alle gegevens van de producten
SELECT *
FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 14
SQL - DML
b) Ophalen van een aantal kolommen uit 1 tabel : door opgave van
kolomnaam of uitdrukking
– Voorbeeld : Toon van alle producten het productID, de naam
en eenheidsprijs
SELECT productid, productname, unitprice
FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 15
SQL - DML
3. WHERE clausule : Specificatie van voorwaarden waaraan
de getoonde rijen moeten voldoen
 Voorbeeld : Toon productID, naam en eenheidsprijs van de
producten die behoren tot categorie 1
SELECT productid, productname, unitprice
FROM products
WHERE categoryid = 1
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 16
SQL - DML
 Gebruik van literals
– Numerische waarden : ... WHERE categoryID = 1
– Alfanumerische waarden : ... WHERE productName = ‘Chai’
– Datums : ... WHERE orderDate = ‘4/15/1998’ (15 april 1998)
 Voorwaarden aan rijen adhv
a)
b)
c)
d)
e)
f)
g)
Vergelijkingsoperatoren
Wildcards
Logische operatoren
Een interval van specifieke waarden
Een lijst van waarden
Onbekende waarden
Je kan haakjes gebruiken om de prioriteitsregels te doorbreken
of het geheel leesbaarder te maken
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 17
SQL - DML
a) Vergelijkingsoperatoren
 =, >, >=, <, <=, <>
 Voorbeelden
– Toon productID, naam, aantal in
stock van de producten waarvan
er minder dan 5 in stock
select productid, productname, unitsinstock
from products
where unitsinstock < 5
– Toon productID, naam, aantal in
stock van de producten waarvan
naam begint met een A
select productid, productname
from products
where productname >= 'A' and
productname < 'B'
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 18
SQL - DML
b) Wildcards (zoeken naar patronen)
 De operator LIKE, NOT LIKE
 In combinatie met wildcards:
% : willekeurige tekenrij met 0 of meerdere tekens
_ : 1 teken
[ ] : 1 teken binnen de gespecifieerde range of verzameling
[^] : elk teken niet binnen de gespecifieerde range of verzameling
 Voorbeeld
– Toon productID, naam van de producten waarbij de tekenreeks anton
voorkomt in de naam
SELECT productid, productname
FROM products
WHERE productname LIKE '%anton%'
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 19
SQL - DML
c) Logische operatoren
 OR, AND, NOT
(volgens stijgende prioriteit)
 Voorbeelden
SELECT productid, productname, supplierid, unitprice
FROM products
WHERE (productname LIKE ’T%’ OR productid = 46) AND unitprice > 16.00
SELECT productid, productname, unitprice
FROM products
WHERE (productname LIKE ’T%’) OR (productid = 46 AND unitprice > 16.00)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 20
SQL - DML
d) Waarden in een interval
 BETWEEN, NOT BETWEEN
 Voorbeeld
– Selecteer de producten (naam en eenheidsprijs) waarvan de eenheidsprijs
tussen 10 en 15 euro (grenzen inbegrepen)
SELECT productid, unitprice
FROM products
WHERE unitprice BETWEEN 10 AND 15
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 21
SQL - DML
e) Lijst van waarden
 IN, NOT IN
 Voorbeeld
– Geef productID, naam, supplierID van de producten die geleverd worden
door de suppliers met ID 1, 3 of 5
SELECT productid, productname, supplierid
FROM products
WHERE supplierid in (1,3,5)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 22
SQL - DML
f) Onbekende waarden (niet ingevulde waarden)
 IS NULL, IS NOT NULL
 Komen voor wanneer er bij input in een bepaalde kolom geen waarde
werd ingebracht en er geen defaultwaarde voor die kolom voorzien was.
 Een NULL waarde verschilt van 0 (numerische waarden) en blanco
(character waarden) !
 NULL velden worden onderling gelijk beschouwd (voor testen met
DISTINCT)
 Als in een rekenkundige uitdrukking een NULL-veld wordt verwerkt is het
resultaat ook NULL
 Voorbeeld
– Selecteer de klanten waarvan region
onbekend
SELECT companyname, region
FROM suppliers
WHERE region IS NULL
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 23
SQL - DML
 Opmerking
SELECT companyname, region
FROM suppliers
WHERE region <> ‘OR’
SELECT companyname, region
FROM suppliers
WHERE region <> ‘OR’
OR region IS NULL
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 24
SQL - DML
g) Oefeningen :
 tabel Werknemer
NR VNAAM
INIT
FNAAM
AFD
IN
10 Christine
I
Haas
A00
DIENST
650101
66
18 V
330814
52750
20 Michel
L
Theunis
B01
731001
61
18 M
480202
41250
30 Sally
A
Kramer
C01
750405
60
20 V
410511
38250
50 Johan
B
Geysen
E01
490817
58
16 M
250915
40175
60 Irving
F
Steur
D11
730914
55
16 M
450707
32250
70 Eva
D
Pulanski
D21
800930
56
16 V
530526
36170
90 Evelien
W
Hendriks
E11
700815
55
16 V
410515
29750
100 Theo
Q
Spencer
E21
800619
54
14 M
561218
26150
110 Vincent
G
Leman
A00
631205
58
19 M
291105
46500
Connors
A00
580516
58
14 M
421018
29250
120 Sean
CODE NIV GESL GEBDAT SALARIS
130 Danielle
M
Scheire
C01
710728
55
16 V
250915
23800
140 Hilde
A
Nagels
C01
761215
56
18 V
460119
28420
Adams
D11
720212
55
16 M
470517
25280
150 Bruno
160 Els
R
Placke
D11
771011
54
17 V
550412
22250
170 Mats
J
Sierens
D11
780915
54
16 M
510105
24680
180 Marleen
S
Schouters
D11
730707
53
17 V
490221
21340
190 Jan
E
Wauters
D11
740726
53
16 M
520625
20450
De Bruyn
D11
660303
55
16 M
410529
27740
Jansens
D11
790411
25
17 M
530223
18270
220 Jennifer
K
Luyckx
D11
680829
academiejaar 2005-2006 A.Sennesael, A. Van Achter
55
18 V
200 David
210 Willem
T
 tabel Afdeling
AFDNR
A00
B01
C01
D01
E01
D11
D21
E21
480319
29840
Karine Samyn - Databanken oefeningen
AFDNAAM
MANNR
Computer
10
Planning
20
Informatie
30
Ontwikkelingsc
50
Support
60
Administratie
70
Software
80
Tools
90
Pag. 25
SQL - DML
 Geef voornaam en achternaam van werknemers geboren voor
01/01/49
 Geef voornaam en familienaam van werknemers met code 54, die in
een willkeurige afdeling werken met uitsluiting van afdeling D11
 Geef nummer, naam en afdelingsnummer van alle werknemers met
salaris tussen 15000 en 24000 en niveau tussen 17 en 20
 Geef nummer, naam en opleidingsniveau van alle werknemers met
niveau 16, 18 of 20
 Geef nummer, naam van vrouwelijke werknemers waarvan
familienaam start met een ‘S’ of ‘T’
 Geef nummer, naam van alle werknemers met onbekende jobcode
 Geef nummer, naam en afdelingsnummer van alle werknemers,
waarvan de familienaam start met een P en die in een afdeling werken
beginnend met D en als 3° karakter een 1 hebben.
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 26
SQL - DML
4. Formatteren van de resultaten
a) Sorteren data
b) Eliminatie van duplicaten
c) Wijzigen van kolomnaam
d) Berekende resultaatkolommen
e) Commentaar
– /* commentaar */
– -- commentaar (beperkt zich tot 1 lijn)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 27
SQL - DML
a) Sorteren van de data
 ORDER BY clausule
– Kan 1 of meerdere sorteervelden bevatten
– Een sorteerveld kan gespecifieerd worden via de kolomnaam, of door een
volgnummer op te geven dat overeenkomt met de volgorde van het
gegeven achter de SELECT clausule (startend vanaf 1)
– Indien meerdere sorteervelden voorkomen, gebeurt het sorteren eerst op
basis van het eerste veld, bij gelijkheid op basis van het tweede,...
– Standaard gebeurt het sorteren in stijgende volgorde (volgens numerieke
waarde, of volgens computercode bvb ASCII). Een dalende volgorde moet
expliciet vermeld worden met DESC
 Voorbeeld :
– Toon een alfabetische lijst van de productnamen
SELECT productname
FROM products
ORDER BY productname
academiejaar 2005-2006 A.Sennesael, A. Van Achter
(of ORDER BY 1)
Karine Samyn - Databanken oefeningen
Pag. 28
SQL - DML
– Toon productid,naam, categoryid en eenheidsprijs van de producten
gesorteerd op categoryid. Indien binnen 1 categorie producten dezelfde
prijs hebben, dan dient het product met de hoogste prijs bovenaan te staan.
SELECT productid, productname, categoryid, unitprice
FROM products
ORDER BY categoryid, unitprice DESC
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 29
SQL - DML
b) Distinct/ALL
 Distinct : produceert een lijst waar alle rijen uniek zijn, gelijke rijen
worden uit resultaat verwijderd
 ALL(default) : toont alle rijen, ook duplicaten
 Voorbeeld
– Toon de leveranciers die producten leveren
SELECT supplierid
SELECT DISTINCT supplierid
FROM products
FROM products
ORDER BY supplierid
ORDER BY supplierid
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 30
SQL - DML
c) Gebruiken van leesbare namen voor de kolommen
 Standaard : kolomtitel = naam van kolom in tabel; Berekende
kolommen krijgen geen kolomnaam
 Via “AS” keyword kan je een kolom een andere titel geven, of door
opgave van kolom, dan een spatie en vervolgens de uitdrukking
– Opm. Die nieuwe kolomnaam kan je enkel gebruiken in ORDER BY
(niet in WHERE, HAVING, GROUP BY)
 Voorbeeld : Selecteer ProductID en ProductNaam van de producten
en geef als kolomtitel ProductNummer en Naam Product.
SELECT productid AS ProductNummer,
productname AS ‘Naam Product’
FROM products
SELECT productid ProductNummer,
productname ‘Naam Product’
FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 31
SQL - DML
d) Berekende resultaatkolommen
 Wiskundige operatoren : +,-,/,*
 Voorbeeld : Geef naam en inventariswaarde van de producten
SELECT ProductName, Unitprice * UnitsInStock AS InventoryValue
FROM Products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 32
SQL - DML
 Functies
– String functies : left, right, len, ltrim, rtrim, substring, replace,...
– DateTime functies : DateAdd, DateDiff, DatePart, Day, Month, Year, ...
» GETDATE() : functie die huidige datum en tijd retourneert in
DATETIME format van MS-SQL Server.
– Rekenkundige functies : round, floor, ceiling, cos, sin,...
– Aggregate functies : AVG, SUM,...
– System functies
» CONVERT (<data type> [(<length>)], <data to convert> [, <style>])
Voorbeeld : CONVERT(VARCHAR,getdate(),6) -> 20 jan 2004
» CAST : conversie van 1 data type naar een ander
CAST (<value expression> AS <data type>)
Voorbeeld : PRINT CAST(-25.25 AS INTEGER) -> -25
» ISNULL : vervangt NULL waarde met opgegeven waarde
Voorbeeld :SELECT ISNULL(unitprice, 10.00) FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 33
SQL - DML
» CASE
SELECT
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
FROM pubs.dbo.titles
ORDER BY price
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 34
SQL - DML
 Stringoperator : concatenatie
SELECT STR(productid) + ‘,’ + productname AS Product
FROM Products
 Gebruik maken van tekst (literals)
SELECT ProductName, ‘$’,Unitprice
FROM Products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 35
SQL - DML
5. Statistische functies (Aggregate functies)
 SQL voorziet 5 standaardfuncties
a) SUM (uitdrukking) : som
b) AVG (uitdrukking) : gemiddelde
c) MIN (uitdrukking) : minimum
d) MAX (uitdrukking) : maximum
e) COUNT (*|[DISTINCT] kolomnaam) : aantal
 Deze functies geven 1 antwoord per kolom (en mogen dus niet
in een WHERE clause gebruikt worden)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 36
SQL - DML
a) SUM
– Retourneert het totaal van NIET NULL numerieke waarden in één
kolom
– Enkel te gebruiken met numerieke argumenten
– Voorbeeld : Geef de totale stock waarde
SELECT SUM(UnitsInStock * UnitPrice) as inventoryvalue
FROM products
b) AVG
–
–
–
Retourneert het gemiddelde van NIET NULL numerieke
waarden in een kolom
Enkel te gebruiken met numerieke argumenten
Voorbeeld : Hoeveel producten zijn er gemiddeld in stock?
SELECT AVG(unitsinstock) AS AverageStock
FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 37
SQL - DML
c) COUNT
– Retourneert het aantal rijen, of een aantal waarden in een kolom
– COUNT(*) : telt het aantal rijen van de selectie
» Tel het aantal producten (= het aantal rijen)
SELECT COUNT(*) AS Aantal
FROM products
– COUNT(kolomnaam) : telt het aantal niet-lege velden in een kolom
» Tel het aantal NIET NULL waarden in de kolom categoryid
SELECT COUNT(categoryid) AS cat_count
FROM products
– COUNT(DISTINCT kolomnaam) : telt het aantal verschillende nietlege velden in een kolom
» Tel het aantal verschillende NIET NULL categoriën in products
SELECT COUNT(DISTINCT categoryid) AS cat_count
FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 38
SQL - DML
d) MIN en MAX
– retourneert de kleinste en de grootste waarde in een kolom
– Gelden zowel op numerieke als alfanumerieke argumenten
– Voorbeeld : Wat is de goedkoopste en duurste eenheidsprijs?
SELECT MIN(unitprice) AS Minimum,
MAX(unitprice) AS Maximum
FROM products
Opmerkingen :
 Omdat een statistische functies maar 1 antwoord oplevert, moeten ofwel alle
uitdrukkingen in de SELECT clausule een statische functie bevatten, ofwel geen
enkele!
Statistische functies en NULL waarden : Houden geen rekening met NULL
waarden. Uitzondering : COUNT(*) (telt ook rijen die null waarden bevatten)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 39
SQL - DML
e) Een aantal dialecten in SQL Server
– STDEV : spreiding van kolomwaarden uitgedrukt in de
standaardafwijking
– VAR : Spreiding van kolomwaarden uitgedrukt in de variantie
– TOP :
» Selecteer de 5 goedkoopste producten
SELECT TOP 5 productid, unitprice
FROM products
ORDER BY unitprice
» Igv de 5 duurste producten sorteren op unitprice DESC
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 40
SQL - DML
6. Groeperen : Statistische functies over meerdere groepen.
 GROUP BY clausule :
 Indeling van tabel in groepen van rijen
met gemeenschappelijke kenmerken.
 Per groep onstaat 1 unieke rij!
 Voorbeeld :
– Tot welke categoriën behoren
de producten?
SELECT CategoryID
FROM Products
GROUP BY CategoryID
 Elke groep is een afzonderlijke verzameling waarop eventueel
statistische functies gebruikt kunnen worden. De kolomnamen vermeld
in de GROUP BY mogen nu ook samen met de statistische functies in
de SELECT voorkomen.
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 41
SQL - DML
 Voorbeelden
– Toon per category het aantal producten
SELECT CategoryID, COUNT(productID) AS number
FROM Products
GROUP BY CategoryID
–Toon per categorie het aantal
producten, waarvan er meer dan 10 in
stock
SELECT CategoryID, COUNT(productID) AS number
FROM Products
WHERE UnitsInStock > 10
GROUP BY CategoryID
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 42
SQL - DML
 HAVING clausule
 Selecteren of verwerpen van groepen op basis van bepaalde
groepseigenschappen
 Voorbeelden
– Toon per category die meer dan 10 producten
bevat, het aantal producten
SELECT CategoryID, COUNT(productID) AS number
FROM Products
GROUP BY CategoryID
HAVING COUNT(productID) > 10
– Toon per category die meer dan 10 producten bevat, waarvan er meer dan
10 in stock, het aantal producten
SELECT CategoryID, COUNT(productID) as number
FROM Products
WHERE UnitsInStock >10
GROUP BY CategoryID
HAVING COUNT(productID) > 10
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 43
SQL - DML
 Opmerkingen
 Verschil tussen WHERE en HAVING
– WHERE : heeft betrekking op rijen
– HAVING : heeft betrekking op groepen
 Statistische functies enkel gebruiken in SELECT, HAVING, ORDER
BY niet in WHERE, GROUP BY
 Indien er functies voorkomen in de select-clausule, dan moeten alle
items van de SELECT-lijst, als argument van één of andere functie
optreden met uitzondering van de items van SELECT die
voorkomen in de GROUP BY!!!
SELECT categoryID, MIN(unitprice) AS Minimum
FROM products
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 44
SQL - DML
 Oefeningen
 Tel het aantal werknemers uit de afdeling D11 en geef het
maximum, minimum en gemiddeld salaris voor deze afdeling,
alsook het aantal verschillende jobcodes uit deze afdeling. Geef
ook de som van alle lonen betaald in D11.
 Geef per afd, het afdnr en het aantal werknemers, gesorteerd
volgens afdelingsnummer.
 Idem, maar nu gesorteerd volgens aantal werknemers.
 Idem maar nu wens je het aantal werknemers te kennen per
afdeling en per jobcode
 Tel per afd het aantal mannen en vrouwen en sorteer volgens
opklimmende afdeling en afdalend geslacht.
 Geef een overzicht van de afdelingen die tenminste 2 werknemers
hebben die meer dan 1000 verdienen.
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 45
SQL - DML
•
Eén tabel raadplegen
•
Meerdere tabellen raadplegen
1. JOIN
a) Inner join
b) Outer join
c) Cross join
2. UNION
3. Subqueries
a) Geneste subqueries
b) Gecorreleerde subqueries
c) Operator EXISTS
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 46
SQL - DML
• Meerdere tabellen raadplegen
1) JOIN
 Selecteren van kolommen uit meerdere tabellen
JOIN keyword : specifieert de tabellen die samengevoegd moeten worden, en
hoe ze moeten worden samengevoegd
Inner join
Outer join
Cross join
ON keyword : specifieert de JOIN voorwaarde
 Produceert 1 resultaatset, waarin de rijen uit die tabellen gekoppeld
worden
 Basisvorm (ANSI JOIN (SQL-92) <-> Old style join)
SELECT uitdrukking
SELECT uitdrukking
FROM tabel JOIN tabel ON voorwaarde
FROM tabel, tabel [, tabel...]
[JOIN tabel ON voorwaarde...]
academiejaar 2005-2006 A.Sennesael, A. Van Achter
WHERE voorwaarde
Karine Samyn - Databanken oefeningen
Pag. 47
SQL - DML
De voorbeelden maken gebruik van de Pubs database
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 48
SQL - DML
a) Inner Join
 Koppelen van rijen uit 1 tabel met rijen uit een andere tabel op basis
van gemeenschappelijke waarden in de overeenkomstige
kolommen.
 De relatie tussen de velden in de verschillende tabellen kan je
uitdrukken a.d.h.v. = (equi-join), <,>,<>,>=,<=
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 49
SQL - DML
 Voorbeeld van equi-join
 Geef een overzicht van de auteurs (naam, voornaam) die niet in California
wonen en de boeken (titelID) die ze geschreven hebben.
– ANSI JOIN (SQL-92)
SELECT au_lname, au_fname, title_id
FROM authors
JOIN titleauthor ON authors.au_id = titleauthor.au_id
WHERE state <> 'CA'
– OF “old style join”
SELECT au_lname, au_fname, title_id
FROM authors, titleauthor
WHERE authors.au_id = titleauthor.au_id
AND state <> 'CA'
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 50
SQL - DML
 Gebruiken van tabel aliassen (via ‘AS’ of spatie)
– SQL-92
SELECT au_lname, au_fname, title_id
FROM authors AS A
JOIN titleauthor AS TA ON A.au_id = TA.au_id
WHERE state <> 'CA'
– “old style join”
SELECT au_lname, au_fname, title_id
FROM authors A, titleauthor TA
WHERE A.au_id = TA.au_id
AND state <> 'CA'
Opmerkingen :

als een kolomnaam in meerdere tabellen (gebruikt in de query) voorkomt, dan dient die steeds te
worden voorafgegaan door de tabelnaam (of alias)

Inner joins geven enkel die rijen terug die voldoen aan de ON conditie. Dit betekent dat als een rij in
de eerste tabel niet matcht met een rij uit de tweede tabel (vb. een auteur die niet in California woont,
en die nog geen boeken geschreven heeft) de rij niet zal geretourneerd worden en omgekeerd.

Als
je in A.Sennesael,
de old style
joinAchter
de where
academiejaar
2005-2006
A. Van
clause vergeet,
dan
krijg- Databanken
je de cross
join (zie verder).
Karine
Samyn
oefeningen
Pag. 51
SQL - DML
 JOIN van meer dan 2 tabellen
- Voorbeeld : Geef een overzicht van de auteurs (naam, voornaam) die niet
in California wonen en de boeken (titel) die ze geschreven hebben.
- SQL-92 :
SELECT au_lname, au_fname, title
FROM authors A
JOIN titleauthor TA ON A.au_id = TA.au_id
JOIN titles T ON TA.title_id = T.title_id
WHERE state <> ‘CA’
Opm : Gegevens kunnen over meer
dan 2 tabellen verspreid zitten.
Soms worden enkel gegevens uit 2
tabellen getoond, maar zijn toch
extra tabellen nodig zijn daar geen
directe koppeling tussen de 2
tabellen waaruit de informatie moet
komen.
- OF “old style join”
SELECT au_lname, au_fname, title
FROM authors A, titleauthor TA, titles T
WHERE A.au_id = TA.au_id
AND TA.title_id = T.title_id
AND state <> ‘CA’
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 52
SQL - DML
 Joining van een tabel met zichzelf
– Toon van alle werknemers naam en voornaam, en naam en voornaam van
hun manager
SELECT X.Lastname, X.FirstName,Y.Lastname, Y.FirstName
FROM Employees AS X
JOIN Employees AS Y ON X.Reportsto = Y.EmployeeID
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 53
SQL - DML
b) Outer join
 Retourneert alle records van 1 tabel, zelfs als er geen gerelateerd
record bestaat in de andere tabel.
 Er zijn 3 types van outer join
– LEFT OUTER JOIN : retourneert alle rijen van de eerst genoemde
tabel in de FROM clause (SQL-92)
– RIGHT OUTER JOIN : retourneert alle rijen van de tweede tabel in
de FROM clause (SQL-92)
– FULL OUTER JOIN : retourneert ook rijen uit de eerste en tweede
tabel die geen corresponderende entry hebben in andere tabel
(SQL-92)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 54
SQL - DML
 Voorbeeld left outer join
– Geef een overzicht van de auteurs (naam, voornaam), die niet wonen in
California, en de boeken (titel) die ze geschreven hebben. Ook de auteurs
die GEEN boeken geschreven hebben dienen op de lijst voor te komen
SELECT au_lname, au_fname, title
FROM authors A
LEFT JOIN titleauthor TA ON A.au_id = TA.au_id
LEFT JOIN titles T ON TA.title_id = T.title_id
WHERE state <> 'CA'
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 55
SQL - DML
 Voorbeeld right outer join
– Toon een lijst van de boeken, met naam en voornaam van de auteurs.
Enkel de boeken geschreven door auteurs die niet in CA wonen of de
boeken waarvoor de auteur niet bekend is mogen op het overzicht
voorkomen.
SELECT au_lname, au_fname, title
FROM authors AS A
JOIN titleauthor AS TA ON A.au_id = TA.au_id
RIGHT JOIN titles AS T ON TA.title_id = T.title_id
WHERE state <> 'CA' OR A.state IS NULL
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 56
SQL - DML
c) Cross join
 Het aantal rijen in de resultaattabel is gelijk aan het aantal rijen in de
eerste tabel maal het aantal rijen in de tweede tabel.
– SQL-92
SELECT au_lname, au_fname, title_id
FROM authors CROSS JOIN titleauthor
– “old style join”
SELECT au_lname, au_fname, title_id
FROM authors, titleauthor
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 57
SQL - DML
2) UNION
 Combineert het resultaat van 2 of meerdere queries in 1 resultaattabel
 Basisvorm
SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...
ORDER BY ...
 De regels die bij een UNION gelden
– De resultaten van de 2 SELECT opdrachten moeten evenveel kolommen bevatten.
– Overeenkomstige kolommen uit beide SELECT’s moeten van hetzelfde data type zijn
en beide NOT NULL toelaten of niet.
– Kolommen komen voor in dezelfde volgorde
– De kolomnamen/titels van de UNION zijn deze van de eerste SELECT
– Het resultaat bevat echter steeds alleen unieke rijen
– Aan het einde van de UNION kan je een ORDER BY toevoegen. In deze clausule mag
geen kolomnaam of uitdrukking voorkomen indien kolomnamen van beide select’s
verschillend. Gebruik in dat geval kolomnummers.
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 58
SQL - DML
 Voorbeeld
– Geef een overzicht van alle bedienden (naam en voornaam, stad en
postcode) en alle klanten (naam, stad en postcode)
SELECT firstname + ‘ ‘ + lastname as name, city, postalcode
FROM Employees
UNION
SELECT companyname, city, postalcode
FROM Customers
Opm. Daar kolomnamen van
UNION deze zijn van de eerste
select, dien je de titel ‘name’ in de
2de select niet meer te herhalen
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 59
SQL - DML
3) Subqueries (subvragen)
 Betekent dat een selectie voorkomt als onderdeel van een andere selectie
 Basisvorm
SELECT
FROM
WHERE voorwaarde
Bevat in het rechterlid tussen ronde haakjes
een SELECT (kan in SELECT, FROM,
WHERE, HAVING)
 Waarom gebruiken?
 Retourneren van resultaat waarbij subquery een procesgegeven bevat
 Of waarbij gegevens nodig zijn uit meerdere tabellen. Kan je ook oplossen met een
JOIN, maar nu worden de tabellen afzonderlijk verwerkt. De relatie tussen de rijen
wordt hier bepaald door de opdrachthiërarchie. Gebruik zoveel mogelijk JOIN, ipv
subqueries
 3 vormen in WHERE clause
 Geneste subvragen
 Gecorreleerde subvragen
 Operator Exists
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Overige
Tabel subquery in FROM clause
Scalaire subquery in SELECT clause
Karine Samyn - Databanken oefeningen
Pag. 60
SQL - DML
De voorbeelden werken met
de Werknemers database
AfdNr
A00
B01
C01
D01
E01
D11
D21
E21
Nr
AfdNaam
Computer Service
Planning
Informatie Centr.
Ontwikkelingscentr.
Support Services
Administratie
Software support
Tools
ManagerNr
10
20
30
50
60
70
100
90
10
20
30
50
60
70
90
100
110
120
130
140
150
160
170
180
Vnaam
Christine
Michel
Sally
Johan
Irving
Eva
Evelien
Theo
Vincent
Sean
Danielle
Hilde
Bruno
Els
Mats
Marleen
Fnaam
Haas
Theunis
Kramer
Geysen
Steur
Pulanski
Hendriks
Spencer
Leman
Connors
Scheire
Nagels
Adams
Placke
Sierens
Schouters
Afdnr
A00
B01
C01
E01
D11
D21
E01
E01
A00
A00
C01
C01
D11
D11
D11
D11
InDienst
Jobcode
1/01/1965
66
1/10/1973
61
5/04/1975
60
7/08/2000
58
4/09/1973
66
30/09/1996
56
15/08/2001
55
16/08/1985
54
5/12/1995
58
16/05/1996
58
28/07/1997
55
15/12/1976
56
2/02/2001
25
11/07/1994
53
15/09/2000
54
7/07/1999
55
190
200
210
220
Jan
David
Willem
Jennifer
Wauters
De Bruyn
Jansens
Luyckx
D11
D11
D11
D11
26/07/2000
3/03/1982
11/04/2003
28/02/2000
academiejaar 2005-2006 A.Sennesael, A. Van Achter
56
58
60
61
Niv
Gesl
18
18
20
16
16
16
16
14
19
14
16
18
16
17
16
17
V
M
V
M
M
V
V
M
M
M
V
V
M
V
M
V
GebDat
2/6/1943
11/6/1951
5/10/1953
9/12/1978
10/10/1951
11/5/1974
9/20/1979
9/21/1963
1/9/1974
6/21/1970
9/2/1975
1/20/1955
3/10/1979
8/15/1972
10/21/1978
8/11/1977
16
16
17
18
M
M
M
V
8/31/1978
4/7/1960
5/16/1981
4/4/1975
Salaris
1308
1023
948
996
799
897
737
648
1153
725
590
705
627
552
612
529
Jobcode
25
53
54
55
56
58
60
61
66
Omschrijving
Analist
Programmeur
Projectleider
Verkoper
Secretaresse
Boekhouder
Marketing
Customer Support
Consultant
507
688
453
740
Karine Samyn - Databanken oefeningen
Pag. 61
SQL - DML
a) Geneste subvragen
 Basisvorm
SELECT
FROM
WHERE voorwaarde
Bevat in het rechterlid tussen ronde haakjes
een SELECT
–
Outer level query = de eerste SELECT. Deze bevat de hoofdvraag.
–
Inner level query = de SELECT in de WHERE clause (of HAVING clause).
D.i. De subvraag.
–
»
Deze wordt altijd eerst uitgevoerd.
»
Ze moeten steeds tussen haakjes staan.
»
Subvragen kunnen in meerdere niveau’s genest zijn.
Subquery kan
i.
1 waarde retourneren
ii.
Of een lijst van waarden retourneren
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 62
SQL - DML
i) Retourneren 1 waarde
– Operatoren : =, >, <, <=,>=,<>
– Voorbeeld :
» Wat is de hoogste wedde?
SELECT MAX(salaris)
FROM werknemer
» Wie heeft de hoogste wedde? -> oplossen met subqueries
SELECT FNaam, VNaam
FROM werknemer
WHERE Salaris =
(SELECT MAX(salaris)
FROM werknemer)
De tabel werknemer wordt de eerste keer doorlopen om het hoogste salaris
te bepalen (subvraag). Vervolgens een tweede keer (hoofdvraag), waarbij
voor elke rij (elke werknemer) het salaris wordt vergeleken met het
berekende maximum
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 63
SQL - DML
» Geef salaris van werknemers waarvan salaris groter is dan gemiddelde salaris
SELECT Vnaam, Fnaam, salaris, afdnr
FROM werknemer
WHERE salaris >
(SELECT AVG(salaris)
FROM werknemer)
» Wie is de jongste vrouwelijke werknemer
SELECT FNaam, VNaam, Gebdat
FROM werknemer
WHERE Gesl= 'V‘ AND Gebdat =
(SELECT max(gebdat)
FROM werknemer
WHERE Gesl = ‘V’)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 64
SQL - DML
ii) Retourneren lijst van waarden
– Operatoren IN, NOT IN, ANY, ALL
– IN / =ANY operator
» Geef naam en voornaam van de managers (kan ook met join)
SELECT Vnaam, Fnaam
FROM werknemer
WHERE nr IN (SELECT ManagerNr
FROM afdeling)
» Geef naam en voornaam van de werknemers die op dezelfde afdeling
werken als Bruno Adams (kan je ook oplossen met JOIN)
SELECT FNaam, Vnaam
FROM werknemer
WHERE afdnr IN
(SELECT afdnr
FROM werknemer
Where Vnaam = “Bruno” AND Fnaam=“Adams”)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 65
SQL - DML
– NOT IN / <>ALL operator
» Geef naam en voornaam van alle niet managers (niet oplosbaar met
INNER JOIN wel met LEFT JOIN of exists (zie verder)))
SELECT Vnaam, Fnaam
FROM werknemer
WHERE nr NOT IN
(SELECT ManagerNr
FROM afdeling)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 66
SQL - DML
– ANY/ALL operator
» Worden gebruikt in combinatie met de relationele operatoren
» ALL : retourneert TRUE als alle waarden geretourneerd in de
subquery voldoen aan de voorwaarde
» ANY : retourneert TRUE als minstens 1 waarde geretourneerd
in de subquery voldoet aan de voorwaarde
» Voorbeeld : Selecteer alle werknemers die langer werkzaam in het
bedrijf, dan alle werknemers uit de afdeling C01 en die jonger zijn dan
tenminste 1 werknemer uit afdeling E21.
SELECT Vnaam, Fnaam
FROM werknemer
WHERE InDienst < ALL
(SELECT InDienst
FROM werknemer
WHERE Afdnr = 'C01')
AND GebDat > ANY
(SELECT GebDat
FROM werknemer
WHERE Afdnr = ‘E21')
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 67
SQL - DML
 Oefeningen
– Welke werknemer is het langst in dienst
– Welke werknemers zijn jonger dan de gemiddelde leeftijd
– Wie is de oudste programmeur
– Welke afdeling heeft het meest aantal werknemers
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 68
SQL - DML
b) Gecorreleerde subqueries
 De Inner Query hangt af van informatie van de Outer Query : De
subvraag bevat een zoekconditie dat relateert naar de hoofdvraag,
waardoor de subvraag van de hoofdvraag afhankelijk wordt.
 Voor elke rij uit hoofdvraag wordt de subvraag opnieuw uitgevoerd. De
volgorde is hier dus niet van onder naar boven, maar van boven naar
onder ( per rij)
 Gebruik joins indien mogelijk
 Principe
SELECT
FROM tabel a
WHERE uitdrukking operator
(SELECT ...
FROM tabel
WHERE uitdrukking operator a.kolomnaam)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 69
SQL - DML
 Voorbeeld :
– Geef werknemers waarvan salaris
groter is dan gemiddeld salaris
SELECT Vnaam, Fnaam
FROM Werknemer
WHERE sal > (SELECT AVG(sal)
FROM Werknemer)
– Geef werknemers waarvan salaris
groter is dan gemiddeld salaris van zijn afdeling
SELECT Vnaam, Fnaam
FROM Werknemer W1
WHERE sal > (SELECT AVG(sal)
FROM Werknemer W2
WHERE W1.afdnr = W2.afdnr)
0. Rij 1 in de outer query
1. Outer query geeft
kolomwaarden van die rij door
aan inner query
2. Inner query gebruikt die
waarden om inner query te
evalueren
3. Inner query retourneert een
waarde naar de Outer query,
en de rij in de Outer query
wordt al dan niet weerhouden.
4. Dit proces wordt herhaald
voor de volgende rij in de
outerquery
Opm : in de inner query mag je velden gebruiken uit de tabellen die
voorkomen in zijn outer queries, en in de query zelf, MAAR niet van
zijn inner queries (di. In de hoofdvraag mag je geen velden gebruiken
uit de subvraag, maar wel omgekeerd)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Terug naar stap 1
Pag. 70
SQL - DML
 Oefeningen
– Geef naam en voornaam van de werknemers die het hoogste salaris hebben
in hun jobcode.
– Geef per departement de werknemer die het laatst in dienst gekomen is
– Geef naam en voornaam op van de mannelijke en vrouwelijke werknemer
die het meeste verdienen
– Welke afdeling heeft het meest aantal werknemers
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 71
SQL - DML
c) De operator EXISTS (NOT EXISTS)
 Testen op het bestaan van iets of het niet bestaan van iets
 Voorbeeld
– Selecteer de afdelingen zonder werknemers
SELECT afdnaam
FROM afdeling
WHERE NOT EXISTS (SELECT *
FROM werknemers
WHERE werknemers.afdnr = afdeling.afdnr)
– Selecteer de afdelingen met werknemers
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 72
SQL - DML
 Nog een stapje verder
– In welke afdeling komen alle jobcodes voor?
SELECT afdnaam
FROM afdeling
WHERE NOT EXISTS
(SELECT *
FROM job
WHERE NOT EXISTS
(SELECT *
FROM werknemer
WHERE afdeling.afdnr = werknemer.afdnr
AND job.jobcode = werknemer.jobcode))
(Aanwijzing : Ga uit van de formulering met dubbele ontkenning : Welke afdelingen bevatten geen
enkele job die niet wordt uitgevoerd door 1 van de werknemers van die afdeling)
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 73
SQL - DML
– Een andere mogelijke oplossing
SELECT afdnaam
FROM afdeling
JOIN werknemer ON afdeling.afdnr = werknemer.afdnr
GROUP BY afdnaam
HAVING COUNT(DISTINCT jobcode) =
(SELECT COUNT(*) FROM job)
– Oefening :
» Welke afdelingen hebben TEN MINSTE dezelfde jobcodes
als afdeling A00
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 74
SQL - DML
– Oefeningen :
President
PRES_NAAM
GEBJAAR
OVERLIJDEN
GEBOORTESTAAT
Administratie
ADMIN_NR
PRES_NAAM
JAAR_INHULDIG
Pres_Huwelijk
PRES_NAAM
NAAM_ECHTGEN
PR_LFT
AANT_KIND
Verkiezing
VERKIES_JAAR
KANDIDAAT STEMMEN
WIN_VERLIES_INDEX
Hobby
PRES_NAAM
academiejaar 2005-2006 A.Sennesael, A. Van Achter
HOBBY
Karine Samyn - Databanken oefeningen
Pag. 75
SQL - DML
» Geef naam en geboortejaar van de presidenten, die ingehuldigd werden voor
hun 45° jaar.
» Geef verkiezingsjaar, en winnaar van die verkiezingen waarbij de winnaar meer
dan 80% van de stemmen behaalde in die verkiezing.
» Selecteer de naam van de presidenten, geboortejaar en installatiejaar van hun
eerste ambtsperiode. Rangschik in volgorde van installatiejaar.
» Zoek die presidenten van wie het aantal huwelijken gelijk is aan het aantal
ambtsperiodes als president. Geef naam, alsook dit aantal.
» Geef de namen en sterfdata van alle presidenten die gehuwd waren.
» Geef de namen en sterfdata van die presidenten die ongehuwd bleven.
» Geef verkiezing en naam van de winnende kandidaten die nooit president
werden
» Geef de kandidaten die zich ten minste in dezelfde jaren als president Clinton
kandidaat gesteld hebben
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 76
SQL - DML
d) SELECT instructie met tabel subquery in FROM clause
 Als resultaat van een subquery terug een tabel is, mag die ook in de
FROM clause gespecifieerd worden. De tabel die de subquery oplevert
krijgt een naam
 Voorbeeld
– Toon de afdelingen waarvan het leeftijdsverschil tussen de oudste en
jongste werknemer groter is dan 20 jaar.
select afd, minprijs, maxprijs
from (select afdnr, min(gebdat), max(gebdat)
from werknemer
group by afdnr)
as leeftijden(afd, mingebdat, maxgebdat)
where datediff(year, mingebdat, maxgebdat) > 20
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 77
SQL - DML
e) SELECT instructie met scalaire subquery in SELECT clause
 In SELECT clause van de SELECT instructie mogen scalaire
subqueries gebruikt worden
 Voorbeeld
– Toon per afdeling de som van de salarissen
select afdnr, afdnaam, (select sum(sal)
from werknemer W
where W.afdnr=A.afdnr) as TotaalSalaris
from afdeling A
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 78
SQL - DML
• Pubs database
academiejaar 2005-2006 A.Sennesael, A. Van Achter
Karine Samyn - Databanken oefeningen
Pag. 79