Structured Query Language
door ir. C. Daniels

 I. Inleiding: wat zijn databases?

A. Relaties
B. Velden 
C. Primaire sleutel
D. Vreemde sleutel II. Access A. Het programma Access gebruiken
B. Hoe opstarten?
C. Hoe een nieuwe query aanmaken in sql? III. Oefeningen SQL A. CREATE TABLE: een nieuwe database aanmaken:
B. ALTER TABLE: de velden wijzigen van een bestaande relatie.
C. CREATE INDEX: indexen aanmaken
D. INSERT INTO: records toevoegen
E. UPDATE: gegevens van records wijzigen
F. SELECT: gegevens opvragen
G. Berekeningen maken in een selectiequery
H. Samenvoegen van velden
I. DISTINCT: voorkom dubbele waarden.
J. WHERE-clausule
K. SQL-operatoren in WHERE-clausules
L. Groepfuncties: COUNT(), AVG(), MAX(), MIN(), SUM()
M. GROUP BY: records groeperen
N. SELECT: gegevens uit meerdere tabellen gebruiken
O. UNION: vereniging van twee tabellen
P. Subquery’s
Q. JOIN: tabellen samenvoegen
R. Parameters  
  1. Inleiding: wat zijn databases?

    2. Databases zijn bestemd om op een ordelijke manier uiteenlopende informatie op te slaan.

    Deze informatie wordt ingedeeld in relaties.

    1. Relaties

      2. Een relatie bevat informatie die op een andere manier samenhoren, zoals naam, voornaam, adres en woonplaats in een adressenlijst. Een enkele database bevat meestal meerdere relaties.

      Een relaties bevat rijen: records genaamd en kolommen: velden genoemd
       

    2. Velden

      3. De records bestaan dus uit velden (de kolommen) die elk de benodigde informatie bevatten. Elk veld heeft een veldnaam. Sommige velden kunnen leeg zijn.

      Voorbeeld: stel dat we de boeken in een bibliotheek willen inventariseren dan zou dit kunnen met onderstaande relaties Boeken en Secties:

      Relatie Boeken:
       
      ISBN Titel Auteur Prijs Uitgeleend_Op Terug_Op Keren_uitgeleend SectieID
      21 Help B. Baker 595 20/12/97 28/12/97 12 6
      87 Killer Bees E.F.Hammond 690 20/09/97   2 4
      91 Dirty steam trains J.SP.Smith 895 12/02/98 21/02/98 9 7
      101 The story of trent T.Wilbury 510 10/01/98 16/01/98 12 6
      8 Over the past again K.Jenkins 615       10
      79 Courses for horses H.Harriot 395 17/1/98   12 9
      989 Leaning on a tree M.Kilner 595 12/11/97 22/11/97 56 11
      Relatie Secties:
       
      SectieID SectieNaam AantalBoeken
      10 Fictie 321
      5 Roman 254
      6 Science Fiction 652
      7 Wetenschap 235
      9 Referentie 124
      15 Wetteksten 451
      Je ziet hier duidelijk de reden om meerdere relaties in deze database te hebben. Anders zou er in de relatie Boek nog twee velden moeten bijkomen: SectieNaam en AantalBoeken. Dit geeft aanleiding tot een grote hoeveelheid extra benodigde opslagruimte: zo zou in de 321 boeken van sectie 10 de naam Fictie moeten ingevuld worden. Nu moet dit maar éénmaal ingevuld. Dus een grote besparing aan opslagruimte!

       

    3. Primaire sleutel

      4. Een primaire sleutel is een veld van een record (of een groepering van velden) waarmee een bepaalde record uit de reeks kan geselecteerd worden. Het ISBN-nummer is een goede sleutel, want het laat toe elk boek te identificeren. Het veld Auteur is geen goede sleutel, want van een auteur kunnen er uiteraard meerdere boeken in de bibliotheek aanwezig zijn! De combinatie Titel+Auteur is weer een goede primaire sleutel, maar wel wat omslachtig.
       

    4. Vreemde sleutel
    Een vreemde sleutel is een veld in een relatie, die een primaire sleutel is in een andere relatie.
    Zo is het veld SectID uit de relatie Boeken een vreemde sleutel, want het veld SectieID is de primaire sleutel van de relatie Secties. Door middel van de vreemde sleutel SectieID kan er een verband gelegd worden van een boek naar de sectie van dat boek.

     

  2. Access
    1. Het programma Access gebruiken

      2. We gaan het programma access gebruiken in zijn kwaliteit om ook sql-queries uit te voeren.

      SQL staat voor Structured Query Language. Een taal om database query’s op te stellen. In zo’n query kan een databse worden geschapen, gegevens worden ingevuld, en gegevens worden opgevraagd uit één of meerdere relaties tesamen.

       

    2. Hoe opstarten?
 
    1. Hoe een nieuwe query aanmaken in sql?
We zijn nu klaar om een nieuwe querie in te voeren.

 

  1. Oefeningen SQL

    1. CREATE TABLE: een nieuwe database aanmaken:

      2. Om de eerste relatie Boeken aan te maken maken we gebruik van onderstaande query met naam CreateRel1. De gebruikte SQL-instructie is CREATE TABLE.

      CreateRel1:
       

      CREATE TABLE Boeken ( 
         ISBN INTEGER2, 
         Titel VARCHAR(200), 
         Auteur VARCHAR(50), 
         Prijs REAL, 
         Uitgeleend_Op DATE, 
         Terug_Op DATE, 
         Keren_Uitgeleend integer2, 
         Sectie_ID integer1 
      ) 
       
      Sluit het ontwerp venster.

      Om de query uit te voeren klik je op Openen.

      Oefening: Zoek de hier gebruikte variabeltypes op in Help.

       

       

       

       

       

       

      Oefening: Maak een tweede query aan die de relatie Secties aanmaakt, en lat ze uitvoeren. Vul de nodige commando’s hieronder in:

      CreateRel2:
       
       

      CREATE TABLE Secties ( 
      SectieID Integer1, 
      SectieNaam Varchar(30), 
      AantalBoeken Integer2 
      ) 
       
       
      Controle: Met het tabblad tabellen, kun je nagaan (in Access) hoe de aangemaakte tabellen eruitzien. (Vul nog geen gegevens in! Nogmaals: het is geen cursus access…)

       

    2. ALTER TABLE: de velden wijzigen van een bestaande relatie.

      3. In een relatie kun je kolommen wijzigen, bijvoegen of wissen. Met dit laatste moet je zeer voorzichtig zijn, want het kan belangrijke gegevens wissen!

      De SQL-instructie hiervoor is ALTER TABLE.

      Voorbeeld van het bijvoegen van een kolom Salaris in de relatie Werknemers.

      ALTER TABLE Werknemers ADD COLUMN Salaris CURRENCY;

      Voorbeeld van het wissen van een kolom:

      ALTER TABLE Werknemers DROP COLUMN Salaris;

      Onderstaande querie Alter1 voegt de kolom reknummer bij in de relatie Secties:

      Alter1:
       

      ALTER TABLE Secties ADD COLUMN RekNummer Integer1
       
      Controleer het resultaat in het tabblad Tabellen!

       

    3. CREATE INDEX: indexen aanmaken

      4. Indexen versnellen aanzienlijk het opzoeken van gegevens in een tabel.

      Met het woordje UNIQUE kan men aangegeven dat elke waarde in deze kolom uniek moet zijn. Dus slechts éénmaal mag voorkomen.

      Onderstaande query Index1 maakt een unieke index aan voor het veld ISBN in de abel Boeken:

      Index1:
       

      CREATE UNIQUE INDEX ISBN_ind ON Boeken(ISBN)
       
       
      Oefening: maak zelf een index Index2 aan op het veld Titel (niet uniek) in de tabel Boeken, en eentje op het veld SectieID in de tabel Secties met naam Index3 (wel uniek).

      Index2:
       
       

       

      Index3:
       
       

       
    4. INSERT INTO: records toevoegen

      5. Met deze instructie kan je nieuwe datarecords invoeren in een tabel.

      Onderstaande query Insert1 voegt de eerste record in van onze tabel Boeken:

      Insert1:

       

      INSERT INTO BOEKEN 
      VALUES (21, "Help", "B.Baker", 595, "20/12/97", "28/12/97", 12, 9); 
       
       

      Oefening: wijzig de gegevens in Index1 zodat alle reords in de tabel worden ingevuld.

      Oefening: maak ook een query Insert2 om de gegevens van de tabel Secties in te vullen. Maak ook deze tabel volledig.

       

       

       

    5. UPDATE: gegevens van records wijzigen

      6. Om de gegevens van een of meer velden in een record te wijzigen gebruik je de instructie UPDATE.

      Stel dat we in de tabel Secties de naam "Referentie" willen vervangen door "Naslagwerken" dan kan dit met de query Update1 zoals hieronder aangegeven:

      Update1:
       

      UPDATE Secties SET SectieNaam = "Naslagwerken" WHERE SectieID=9
       

      Oefeningen: - verander de auteurnaam E.F.Hammond in P.J. Minaugh

      - verander het SectieID van boek "Learning on a tree" in 9.

       

    6. SELECT: gegevens opvragen

      7. Stel dat je alle gegevens van de tabel Boeken wil opvragen, dan gaat dit met de volgende query Select1:

      Select1:
       

      Select * 
      FROM Boeken; 
       
      Je kan ook specifiëren welke velden je wil zien, en in welke volgorde zoals in de query Select2 hieronder:

      Select2:
       

      SELECT Auteur, Titel, ISBN 
      FROM Boeken 
      ORDER BY Auteur; 
       
       

      Oefening: maak een query Select3, die de velden AantalBoeken, SectieID en SectieNaam toont gesorteerd volgens het aantal boeken.

      Select3:
       

       
       
       
    7. Berekeningen maken in een selectiequery

      8. We kunnen ook berekeningen maken. Stel dat elke auteur 1% krijgt van de waarde van een boek, telkens het uitgeleend wordt. Dit kan berekend worden met de volgende query Select4.

      Select4:
       

      SELECT Auteur, Keren_Uitgeleend*Prijs*0.01 
      FROM Boeken; 
       
       
    8. Samenvoegen van velden

      9. Meerdere velden kunnen ook als één enkel veld worden samengevoegd met de +-operator, als volgt:

      Select5:
       

      SELECT Titel + '-' + Auteur 
      FROM Boeken; 
       
      Probeer dit eens uit.
      Voeg er ook de prijs nog bij, zoals in: "Courses for horses-H.Harriot Prijs: 395".
      Daartoe moet de prijs eerst naar tekst worden omgezet. Doe dat met de functie str(Prijs).
       
    9. DISTINCT: voorkom dubbele waarden.

      10. Stel dat we alle Sectie_ID willen kennen uit de tabel Boeken. Een query zoals
      Select Sectie_ID from Boeken ORDER BY Sectie_ID
      geeft een lijst waarin deze nummers meerdere keren terugkomen.
      Voeg daarom het woordje DISTINCT toe, zoals hieronder:

      Select6:
       

      SELECT DISTINCT Sectie_ID 
      FROM boeken 
      ORDER BY Sectie_ID; 
       
       
       
    10. WHERE-clausule

      11. Met de WHERE-clausule kun je records gaan selecteren op bepaalde kenmerken.
      Stel dat je alleen de boeken wil die duurder zijn dan 600F. Dan kan dit als volgt:

      Select 7: 
       

      SELECT ISBN, Titel, Auteur, Prijs 
      FROM Boeken 
      WHERE Prijs>600 
      ORDER BY Prijs; 
       
       
    11. SQL-operatoren in WHERE-clausules
            Volgende operatoren kunnen in WHERE-clausules worden gebruikt:

                Operator                                 Description
                BETWEEN ..... AND .....         tussen twee waarden gelegen
                IN(condition list)                       komt overeen met waarden uit een lijst
                LIKE                                          komt overeen met een tekstpatroon
                IS NULL                                    is een NULL waarde

 

Enkele voorbeelden:

      - Boeken met de prijs gelegen tussen 300 en 500 F:

        Select * FROM Boeken WHERE Prijs BETWEEN 300 AND 500;

       - Boeken met Sectie_ID gelijk aan 6 of 7

SELECT * FROM Boeken WHERE Sectie_ID in(6,7);
  - Boeken met de tekst "st" in de titel: SELECT * FROM Boeken WHERE Titel LIKE '*st*';
  - Boeken die nog niet weden teruggebracht:         SELECT * FROM Boeken WHERE Terug_Op IS NULL; 

        - Ook de NOT-operator kan gebruikt worden, zoals in

        SELECT * FROM Boeken WHERE Sectie_ID NOT IN (9,10,11);

        - Gebruik van de AND-operator:

        SELECT * FROM Boeken WHERE Sectie_ID NOT IN (10,11)
            AND Prijs > 600;

Probeer uit!

 

    1. Groepfuncties: COUNT(), AVG(), MAX(), MIN(), SUM()
- Het aantal records tellen: COUNT(*)

        SELECT COUNT(*) FROM Boeken;

- Gemiddelde berekenen: AVG()

        SELECT AVG(Prijs) FROM Boeken;

- Maximum berekenen: MAX()

SELECT MAX(Prijs) FROM Boeken; - Minimum berekenen: MIN() SELECT MIN(Prijs) FROM Boeken; - De som berekenen: SUM() SELECT SUM(Prijs) FROM Boeken; Probeer uit!

 
 

    1. GROUP BY: records groeperen

      2. Stel dat we het aantal boeken willen tellen per Sectie_ID
      Dit kan met de GROUP BY – clausule, zoals hieronder:

      Select10:
       

      SELECT Sectie_ID, COUNT(*)
      FROM Boeken
      GROUP BY Sectie_ID;
       
       

      Als we ook nog de totale kostprijs van de boeken per groep willen kennen:

      Select11:
       

      SELECT SectieID, COUNT(*), SUM(Prijs)
      FROM Boeken
      GROUP BY SectieID;
       
       
    2. SELECT: gegevens uit meerdere tabellen gebruiken

      3. Als men gegevens uit meerdere relaties in één enkele tabel wil samenbrengen spreekt men van een join. Zij bevatten altijd één of meer SELECT- instructies.

      Onderstaande query geeft de titels van de boeken uit de tabel Boeken, samen met de betreffende SectieNaam uit de tabel Secties:

      Select12:
       

      SELECT A.Titel, B.SectieNaam
      FROM Boeken AS A, Secties AS B
      WHERE A.SectieID=B.SectieID;
       
       

       

    3. UNION: vereniging van twee tabellen

      4. Stel at we alle waarden willen kennen van het SectieID die of in Boeken, of in Secties voorkomen:

      Select13:
       

      SELECT SectieID FROM Boeken UNION SELECT SectieID FROM Secties;

      Noteer dat de waarden ook gegroepeerd werden per SectieID. Als je alle waarden wil zien gebruik je de ALL-operator:

      Select14:
       

      SELECT SectieID FROM Boeken UNION ALL SELECT SectieID FROM Secties;
       
    4. Subquery’s

      5. Stel dat ik de titel wens van het duurste boek. In een subquery wordt het hoogste prijs gezocht. Daarna wordt het boek gezocht met de prijs gelijk aan deze hoogste prijs:

      Select15:
       

      SELECT Titel
      FROM Boeken
      WHERE Prijs = (SELECT MAX(Prijs) FROM Boeken);
       

       
      Stel dat ik het duurste boek van elke sectie wil vinden:

      Select16:
       

      SELECT SectieID, Titel
      FROM Boeken
      WHERE Prijs IN (SELECT MAX(Prijs) FROM Boeken
      GROUP BY SectieID);
       
       

      De tweede SELECT geeft een reeks waarden, nml de hoogste prijs per SectieID. De eerste SELECT zoekt dan het boek met een prijs die tot deze lijst behoort.

      Deze query werkt niet altijd zoals verwacht. Waarom?

       
       

      Oefening: wijzig de gegevens zodanig dat er twee boeken met eenzelfde SectieID worden weergegeven.
       

    5. JOIN: tabellen samenvoegen

    Gegevens uit meerdere tabellen kunnen samengevoegd worden met de JOIN-instructie.

    Zo kunnen we bv. gegevens uit de tabel Boeken, weergeven samen met de overeenkomende gegevens uit de tabel Secties, via het gemeenschappelijke veld SectieID.

    Er zijn drie soorten:

  1. INNER JOIN: alleen die records worden gekozen wanneer het veld SectieID overeenkomende waarden vertoont.
  2. LEFT JOIN: alle records uit de eerste tabel worden gekozen, zelfs als er geen overeenstemmende waarde is in de tweede tabel.
  3. RIGTH JOIN: alle records uit de tweede tabel worden getoond, ook als er geen overeenstemmende waarde in de eerste tabel bestaat.
 

Oefening: Probeer onderstaande query uit.

Om een duidelijk beeld te krijgen van de verschillen tussen inner, left en rigth join te verkrijgen, doe je als volgt: voeg records bij in beide tabellen, en zorg ervoor dat er gegevens in de tabel Boeken staan met een SectieID dat niet voorkomt in in de tabel Secties, en omgekeerd.

Select17:
 

SELECT Titel, SectieNaam, Boeken.SectieID, Secties.SectieID
FROM Boeken INNER JOIN Secties ON Boeken.SectieID=Secties.sectieID;

Merk op dat het veld Titel een veld is van de tabel Boeken, het veld SectieNaam is uit de tabel Secties, terwijl het veld SectieID in beide tabellen voorkomt. We drukken deze laatste alletwee af, en moeten dus specifiëren uit welke tabel dit veld afkomstig is: Boeken.SectieID en Secties.SectieID. Voor de beide andere velden Titel en SectieNaam is dit niet nodig want ze komen slechts in één tabel voor.

Bekijk het resultaat. Vervang vervolgens INNER JOIN door LEFT JOIN en daarna door RIGHT JOIN.
 

    1. Parameters
Met behulp van het woordje PARAMETERS kun je ervoor zorgen dat naar een of meerdere gegevens gevraagd worden.

Select17:
 

PARAMETERS [Typ de naam van de auteur:] Text;
SELECT *
FROM Boeken
WHERE Auteur LIKE [Typ de naam van de auteur:];
 

Hier zal eerst naar de naam van de auteur worden gevraagd. Je kan hierbij van de jokertekens * (meerdere tekens) en ? (één teken) gebruik maken. Om alle auteurs met een k in hun namen weer te geven, type je *k* in.