Pag. 1

1
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

2
Overzicht databanken - Oefeningen

• Cursus
• Slides op Dokeos
• Syllabus
• Punten
• Permanente evaluatie 20
• 1 gequoteerde oefening
• Examen
• Theorie 40
• Oefeningen 40

3
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 server- en
  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.

4
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/en-us/instsql/in_overview_74vn.asp
• Installatie procedure http//msdn.microsoft.com/
  library/default.asp?url/library/en-us/howtosql/ht
  _install_1xwl.asp
• SQL Server Home http//www.microsoft.com/sql

5
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 gt Microsoft SQL Server
  2000 gt Enterprise Manager

6
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 beschikbaaren kan je de
  verschillende databankenbeheren

7
Overzicht databanken - Oefeningen

• Bevragen gegevens in SQL Server via de Query
  Analyzer
• Programs gt Microsoft SQL Server 2000 gt 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 SHIFTF1 of via Programs gtSQL
  Server 2000 gt Books Online

8
Overzicht databanken - Oefeningen

• Oefeningen maken gebruik van
• planten database
• Installatie procedure zie Dokeos

9
Overzicht databanken - Oefeningen

• De voorbeelden in de theorie maken gebruik van de
  Products tabel uit de Northwind databank

10
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

11
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)

12
SQL - DML

• Eén tabel raadplegen
• Basisvorm
• SELECT clausule
• WHERE clausule
• Formatteren van rijen
• Statistische functies
• Groeperen
• Meerdere tabellen raadplegen

13
SQL - DML

• Eén tabel raadplegen
• Basisvorm
• 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

SELECT ALL DISTINCT uitdrukking
,uitdrukking ... FROM tabelnaam WHERE
voorwaarde(n) GROUP BY kolomnaam ,kolomnaam
... HAVING voorwaarde(n) ORDER BY
kolomnaamvolgnrASCDESC,...
14
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
15
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
16
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
17
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
• 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

18
SQL - DML

• a) Vergelijkingsoperatoren
• , gt, gt, lt, lt, ltgt
• Voorbeelden
• Toon productID, naam, aantal in stock van de
  producten waarvan er minder dan 5 in stock
• Toon productID, naam, aantal in stock van de
  producten waarvan naam begint met een A

select productid, productname, unitsinstock from
products where unitsinstock lt 5
select productid, productname from products where
productname gt 'A' and productname lt 'B'
19
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'
20
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 gt 16.00
SELECT productid, productname, unitprice FROM
products WHERE (productname LIKE T) OR
(productid 46 AND unitprice gt 16.00)
21
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
22
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)
23
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 regiononbekend

SELECT companyname, region FROM suppliers WHERE
region IS NULL
24
SQL - DML

• Opmerking

SELECT companyname, region FROM suppliers WHERE
region ltgt OR
SELECT companyname, region FROM suppliers WHERE
region ltgt OR OR region IS NULL
25
SQL - DML

• g) Oefeningen
• tabel Werknemer

• tabel Afdeling

26
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.

27
SQL - DML

• 4. Formatteren van de resultaten
• Sorteren data
• Eliminatie van duplicaten
• Wijzigen van kolomnaam
• Berekende resultaatkolommen
• Commentaar
• / commentaar /
• -- commentaar (beperkt zich tot 1 lijn)

28
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 (of ORDER BY 1)
29
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
30
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 DISTINCT supplierid FROM products ORDER BY
supplierid
SELECT supplierid FROM products ORDER BY
supplierid
31
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
32
SQL - DML

• d) Berekende resultaatkolommen
• Wiskundige operatoren ,-,/,
• Voorbeeld Geef naam en inventariswaarde van de
  producten

SELECT ProductName, Unitprice UnitsInStock AS
InventoryValue FROM Products
33
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 (ltdata typegt (ltlengthgt), ltdata to
  convertgt , ltstylegt)
• Voorbeeld CONVERT(VARCHAR,getdate(),6) -gt
  20 jan 2004
• CAST conversie van 1 data type naar een ander
• CAST (ltvalue expressiongt AS ltdata typegt)
• Voorbeeld PRINT CAST(-25.25 AS INTEGER) -gt
  -25
• ISNULL vervangt NULL waarde met opgegeven
  waarde
• Voorbeeld SELECT ISNULL(unitprice, 10.00)
  FROM products

34
SQL - DML

• CASE
• SELECT
• CASE
• WHEN price IS NULL THEN 'Not yet priced'
• WHEN price lt 10 THEN 'Very Reasonable
  Title'
• WHEN price gt 10 and price lt 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

35
SQL - DML

• Stringoperator concatenatie
• Gebruik maken van tekst (literals)

SELECT STR(productid) , productname AS
Product FROM Products
SELECT ProductName, ,Unitprice FROM Products
36
SQL - DML

• 5. Statistische functies (Aggregate functies)
• SQL voorziet 5 standaardfuncties
• SUM (uitdrukking) som
• AVG (uitdrukking) gemiddelde
• MIN (uitdrukking) minimum
• MAX (uitdrukking) maximum
• COUNT (DISTINCT kolomnaam) aantal
• Deze functies geven 1 antwoord per kolom (en
  mogen dus niet in een WHERE clause gebruikt
  worden)

37
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
38
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 niet-lege velden in een kolom
• Tel het aantal verschillende NIET NULL categoriën
  in products
• SELECT COUNT(DISTINCT categoryid) AS cat_count
• FROM products

39
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)

40
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
• Igv de 5 duurste producten sorteren op unitprice
  DESC

SELECT TOP 5 productid, unitprice FROM
products ORDER BY unitprice
41
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?
• 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.

SELECT CategoryID FROM Products GROUP BY
CategoryID
42
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 GROUP BY CategoryID
WHERE UnitsInStock gt 10
43
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) gt 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 gt10 GROUP
BY CategoryID HAVING COUNT(productID) gt 10
44
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
45
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.

46
SQL - DML

• Eén tabel raadplegen
• Meerdere tabellen raadplegen
• JOIN
• Inner join
• Outer join
• Cross join
• UNION
• Subqueries
• Geneste subqueries
• Gecorreleerde subqueries
• Operator EXISTS

47
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) lt-gt Old style join)

SELECT uitdrukking FROM tabel JOIN tabel ON
voorwaarde JOIN tabel ON
voorwaarde...
SELECT uitdrukking FROM tabel, tabel ,
tabel... WHERE voorwaarde
48
SQL - DML

• De voorbeelden maken gebruik van de Pubs database

49
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), lt,gt,ltgt,gt,lt

50
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 WHERE state
ltgt 'CA'
FROM authors JOIN titleauthor
ON authors.au_id titleauthor.au_id

• OF old style join

SELECT au_lname, au_fname, title_id FROM authors,
titleauthor WHERE authors.au_id
titleauthor.au_id AND state ltgt 'CA'
51
SQL - DML

• Gebruiken van tabel aliassen (via AS of spatie)
• SQL-92
• old style join

SELECT au_lname, au_fname, title_id FROM authors
AS A JOIN titleauthor AS TA ON
A.au_id TA.au_id WHERE state ltgt 'CA'
SELECT au_lname, au_fname, title_id FROM authors
A, titleauthor TA WHERE A.au_id TA.au_id
AND state ltgt '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 de old style join de where clause
  vergeet, dan krijg je de cross join (zie verder).

52
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

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.
SELECT au_lname, au_fname, title FROM authors A
JOIN titleauthor TA ON A.au_id
TA.au_id WHERE state ltgt CA
JOIN titles T ON TA.title_id T.title_id

• - 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 ltgt CA

53
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
54
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)

55
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 ltgt 'CA'
56
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 ltgt 'CA' OR A.state IS NULL
57
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

58
SQL - DML

• 2) UNION
• Combineert het resultaat van 2 of meerdere
  queries in 1 resultaattabel
• Basisvorm
• De regels die bij een UNION gelden
• De resultaten van de 2 SELECT opdrachten moeten
  evenveel kolommen bevatten.
• Overeenkomstige kolommen uit beide SELECTs
  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
  selects verschillend. Gebruik in dat geval
  kolomnummers.

SELECT ... FROM ... WHERE ... UNION SELECT ...
FROM ... WHERE ... ORDER BY ...
59
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
60
SQL - DML

• 3) Subqueries (subvragen)
• Betekent dat een selectie voorkomt als onderdeel
  van een andere selectie
• Basisvorm
• 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

SELECT FROM WHERE voorwaarde
Bevat in het rechterlid tussen ronde haakjes een
SELECT (kan in SELECT, FROM, WHERE, HAVING)

• Overige
• Tabel subquery in FROM clause
• Scalaire subquery in SELECT clause

61
SQL - DML

• De voorbeelden werken met de Werknemers database

62
SQL - DML

• a) Geneste subvragen
• Basisvorm
• 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 niveaus genest
  zijn.
• Subquery kan
• 1 waarde retourneren
• Of een lijst van waarden retourneren

SELECT FROM WHERE voorwaarde
Bevat in het rechterlid tussen ronde haakjes een
SELECT
63
SQL - DML

• i) Retourneren 1 waarde
• Operatoren , gt, lt, lt,gt,ltgt
• Voorbeeld
• Wat is de hoogste wedde?

SELECT MAX(salaris) FROM werknemer

• Wie heeft de hoogste wedde? -gt 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
64
SQL - DML

• Geef salaris van werknemers waarvan salaris
  groter is dan gemiddelde salaris

SELECT Vnaam, Fnaam, salaris, afdnr FROM
werknemer WHERE salaris gt
(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)
65
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 FnaamAdams)
66
SQL - DML

• NOT IN / ltgtALL 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)
67
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
lt ALL
(SELECT InDienst FROM werknemer WHERE Afdnr
'C01')
(SELECT GebDat FROM werknemer WHERE Afdnr
E21')
AND GebDat gt ANY
68
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

69
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)
70
SQL - DML

• Voorbeeld
• Geef werknemers waarvan salarisgroter is dan
  gemiddeld salaris

0. Rij 1 in de outer query
1. Outer query geeft kolomwaarden van die rij
door aan inner query
SELECT Vnaam, FnaamFROM WerknemerWHERE sal gt
(SELECT AVG(sal) FROM Werknemer)
2. Inner query gebruikt die waarden om inner
query te evalueren

• Geef werknemers waarvan salarisgroter is dan
  gemiddeld salaris van zijn afdeling

3. Inner query retourneert een waarde naar de
Outer query, en de rij in de Outer query wordt al
dan niet weerhouden.
SELECT Vnaam, FnaamFROM WerknemerWHERE sal gt
(SELECT AVG(sal) FROM
Werknemer
W1
4. Dit proces wordt herhaald voor de volgende rij
in de outerquery
W2
WHERE W1.afdnr W2.afdnr)
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)
Terug naar stap 1
71
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

72
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
• Selecteer de afdelingen met werknemers

SELECT afdnaam FROM afdeling WHERE NOT EXISTS
(SELECT FROM werknemers WHERE werknemers.afdnr
afdeling.afdnr)
73
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)

74
SQL - DML

• Een andere mogelijke oplossing
• Oefening
• Welke afdelingen hebben TEN MINSTE dezelfde
  jobcodes als afdeling A00

SELECT afdnaam FROM afdeling JOIN
werknemer ON afdeling.afdnr werknemer.afdnr
GROUP BY afdnaam HAVING COUNT(DISTINCT jobcode)

(SELECT COUNT() FROM job)
75
SQL - DML

• Oefeningen

76
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

77
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) gt 20
78
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.afdnrA.afdnr) as
TotaalSalaris from afdeling A
79
SQL - DML

• Pubs database

80
SQL - DML