DATABASE FUNDAMENTALS

SQL: SELECT – FROM – WHERE – ORDER BY

  • Odisee professionele bachelor Toegepaste Informatica

  • DE CO-HOGESCHOOL

SQL Intro

  • Herhaling van de basisprincipes van SQL:

    • Voorbeeld van een eenvoudige SQL-query:

    • SELECT Book

    • COUNT (*) AS Auth

    • FROM Book JOIN Book a ON Book.isbn = Book

    • GROUP BY Book.title

    • Output: mi

STRUCTURED QUERY LANGUAGE (SQL)

  • Definitie: SQL, uitgesproken als 'sequel', is de taal die gebruikt wordt voor instructies zoals het bevragen of aanpassen van gegevens, toekennen van rechten, en het aanmaken of veranderen van tabelstructuren.

  • Standarisatie: SQL is theoretisch gestandaardiseerd en zou identiek moeten zijn voor alle DBMS'en, maar in de praktijk hebben de meeste database systemen hun eigen uitbreidingen en dialecten.

  • Hoofdcategorieën van SQL statements:

    1. Data definition language (DDL)

    2. Data manipulation language (DML)

    3. Data control language (DCL)

    4. Transaction control language (TCL)

SQL – DATA DEFINITION LANGUAGE (DDL)

  • Definitie: DDL-commando’s manipuleren de datastructuren van de databank: creëren, veranderen en verwijderen.

  • Commando's: beginnen met CREATE, ALTER, DROP, RENAME, en TRUNCATE.

  • Voorbeelden (geen vereiste om ze te kennen):

    • CREATE TABLE products ( id int(10) NOT NULL, name varchar(255) NOT NULL, description text NOT NULL, price decimal(10,0) NOT NULL, category enum('clothes','furniture','objects','lightning') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    • ALTER TABLE suppliers RENAME TO vendors;

SQL – DATA MANIPULATION LANGUAGE (DML)

  • Definitie: DML-commando’s manipuleren tabeldata: nieuwe rijen toevoegen, bestaande rijen aanpassen of verwijderen.

  • Commando's: beginnen met INSERT, UPDATE, DELETE of MERGE.

  • Voorbeelden (geen vereiste om ze te kennen):

    • INSERT INTO products (name, description, price, category) VALUES('Elegant Accolay Vase', 'This elegant vase was made in the Ceramic Studio of Accolay in France in the 1970s.<br> The light blue glaze with the special pattern of craquelures is beautiful.', '750', 'objects');

    • SELECT name, category FROM products WHERE price > 500;

  • Focus in deze cursus: beperken tot DML, en dan vooral SELECT queries.

SQL – DATA CONTROL LANGUAGE (DCL)

  • Definitie: DCL-commando’s beheren gebruikersrechten: toekennen of verwijderen van rechten aan de data en structuren van de databank.

  • Commando's: beginnen met GRANT en REVOKE.

  • Voorbeelden (niet te kennen):

    • GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;

    • REVOKE ALL ON suppliers FROM anderson;

    • CREATE ROLE db_sales;

SQL – TRANSACTION CONTROL LANGUAGE (TCL)

  • Definitie: TCL-commando’s beheren de uitvoering van DML-commando’s: uitvoeren, terugdraaien en savepoints instellen.

  • Commando's: beginnen met COMMIT, ROLLBACK en SAVEPOINT.

  • Voorbeeld: COMMIT; SAVEPOINT my_savepoint; ROLLBACK my_savepoint;

SELECT Statements

Alle kolommen, alle rijen opvragen

  • Een minimale SQL-query bestaat uit de volgende onderdelen:

    • Met het sleutelwoord FROM geef je aan uit welke tabel je gegevens wilt ophalen.

    • Met het sleutelwoord SELECT geef je aan welke kolom(men) je in het resultaat wilt zien (uit de geselecteerde tabel).

    • Volgorde is belangrijk: eerst SELECT, daarna FROM.

  • Bij de SELECT kan je met het teken ‘*’ aangeven dat alle kolommen moeten worden opgehaald.

  • Voorbeeld: geef alle gegevens uit de tabel Jobs.

  • Resultaat: weergegeven in tabelvorm, de resultatentabel genoemd.

Specifieke kolommen van een tabel opvragen

  • Soms wil je niet alle kolommen uit een tabel zien; dan vermeld je de namen van de kolommen die je wilt zien, gescheiden door een komma.

  • Volgorde: de volgorde van de SELECT-lijst bepaalt de volgorde van de kolommen in de resultatentabel.

  • Voorbeeld: geef alle jobs met hun id en titel.

  • Tip: vraag enkel die kolommen op die nodig zijn voor performantie.

Duplicaten onderdrukken

  • Soms komen dezelfde waarden meerdere keren voor in de resultatentabel. Deze duplicaten kunnen worden onderdrukt met het sleutelwoord DISTINCT.

  • Voorbeeld: welke verschillende jobs voeren de medewerkers uit? Toon iedere job slechts één keer, gesorteerd.

  • Opmerking: DISTINCT werkt op de volledige SELECT-lijst!

  • Voorbeeld: Welke jobs worden er in de verschillende departementen uitgevoerd? Toon iedere combinatie slechts één keer.

ORDER BY

Sorteren van de resultantentabel

  • Met de sleutelwoorden ORDER BY kan je aangeven op welke manier je de resultantentabel gesorteerd dient te worden.

  • Na ORDER BY komt de naam van een kolom. Standaard wordt er stijgend gesorteerd (ASC). Om aflopend te sorteren, volgt na de kolomnaam een spatie gevolgd door het sleutelwoord DESC.

  • Omwille van de performantie: sorteer alleen op de kolommen die nodig zijn.

Voorbeelden van sorteren

  • Voorbeeld: geef alle jobs met hun titel en minimumloon, het resultaat moet stijgend gesorteerd zijn op het minimumloon.

  • Voorbeeld: geef alle jobs met hun titel en maximumloon, het resultaat moet dalend gesorteerd zijn op het maximumloon.

  • Bij het sorteren op meerdere kolommen wordt eerst op de eerste kolom gesorteerd, en binnen de groepen van gelijken op de tweede kolom.

    • Voorbeeld: geef alle jobs met hun titel en minimum- en maximumloon; sorteer dalend op maximumloon en stijgend op minimumloon bij gelijken.

Top-X resultaten

  • Hoe kan je de queryresultaten beperken tot de eerste X resultaten?

  • Voorbeeld: Geef de 5 medewerkers met het hoogste loon.

  • Syntax variaties:

    • ‘FIRST’ kan worden vervangen door ‘NEXT’.

    • ‘ROWS’ kan worden vervangen door ‘ROW’.

  • Verschillende RDBMS hanteren verschillende manieren om de eerste N elementen te selecteren:

    • Oracle: FETCH FIRST/NEXT,

    • SQL Server: TOP,

    • MySQL: LIMIT.

Omgaan met gelijke waarden

  • ‘ONLY’ kan worden vervangen door ‘WITH TIES’. Dit zorgt ervoor dat meer records worden weergegeven als er gelijke waarden zijn als de laatste rij in de resultatentabel voor de expressie in de ORDER BY.

    • Voorbeeld: ondanks dat slechts de eerste 2 records werden gevraagd, worden er 3 getoond.

  • De FETCH FIRST-clausule kan worden voorafgegaan door de OFFSET-clausule. Hiermee kan je het aantal te negeren records aangeven voordat je het aantal records limiteert.

    • Voorbeeld: Geef de 5 medewerkers met het hoogste loon, maar tel de medewerker met het allerhoogste loon niet mee.

WHERE Statements

  • Om specifieke rijen op te vragen wordt het sleutelwoord WHERE gebruikt. Hierop legt men een voorwaarde, restrictie of beperking op aan de vraag.

    • Voorbeeld: geef alle gegevens van de werknemers uit departement 80. Resultaat: 3 (van de 20) werknemers worden getoond.

Datatypes & Vergelijkingsoperatoren

  • Kennis van vergelijkings- en logische operatoren is nodig om goed te werken met uitdrukkingen bij WHERE.

  • De datatypes aan beide kanten van de operator moeten vergelijkbaar zijn.

    • Voorbeeld: 25 is een numeriek gegeven.

  • Definitie van datatypes: Datatypes bepalen hoe data opgeslagen is in een veld en zijn cruciaal voor de controle en validatie van waarden in velden.

Data Types: Categorieën (1)

  • Tekst datatypes (character data types): worden opgeslagen in strings.

    • Voorbeelden:

    • CHAR: vaste lengte.

    • VARCHAR: variabele lengte.

  • Numerieke datatypes: slaan positieve en negatieve getallen op.

    • Voorbeelden:

    • SMALLINT: geheel getal tussen -32000 en +32000.

    • INTEGER: geheel getal tussen -2 miljard en +2 miljard.

    • DECIMAL en NUMBER: voor decimale waarden.

Data Types: Categorieën (2)

  • Datum datatypes: slaan datum- en tijdsaanduidingen op.

    • Voorbeelden:

    • DATE: datumveld.

    • TIME: tijdsveld tot op de seconde.

    • TIMESTAMP: tijdsveld tot op milliseconde.

  • LOB datatypes: Grote blokken van ongestructureerde data.

    • Voorbeelden:

    • BLOB: ongestructureerde binaire data.

    • CLOB: ongestructureerde karakters.

Vergelijkingsoperatoren

  • Operatoren: =, >, <, >=, <=, <>.

  • Voorbeelden van gebruik:

    • BETWEEN … AND …: voor binnen een bereik.

    • IN: voor meerdere waarden tegelijk.

    • LIKE: voor het vergelijken met jokertekens.

    • IS NULL: om NULL-waarden te identificeren.

    • NOT: om een vergelijking om te keren.

    • AND / OR: voor meerdere voorwaarden.

Aanhalingstekens

  • Bij numerieke datatypes zijn geen aanhalingstekens nodig.

  • Bij tekst- of datumdatatypes zijn enkel enkele aanhalingstekens nodig.

  • Fouten kunnen ontstaan bij verwijzingen naar kolomnamen of -waarden als de juiste aanhalingstekens niet worden gebruikt.

Voorbeelden

  • Voorbeeld: Toon alle werknemers die op commissie werken. => resultaat: 4 (van de 20) werknemers.

  • Voorbeeld: Toon alle werknemers die aangeworven zijn in 1999. => resultaat: 3 (van de 20).

  • Voorbeeld: Toon alle werknemers van departement 50 die aangeworven zijn in 2019. => resultaat: 1 (van de 20).

  • Voorbeeld: Toon alle werknemers uit departement 80 en 90. => resultaat: 6 (van de 20).

  • Voorbeeld: Toon alle werknemers uit departement 80, 90 en 110. => resultaat: 8 (van de 20).

  • Voorbeeld: Toon alle werknemers behalve zij die werken voor departement 50, 80, 90 of 110. => resultaat: 8 (van de 20).

  • Voorbeeld: Toon alle jobs waarbij ‘manager’ in de titel voorkomt => resultaat: 4 (van de 12).

  • Voorbeeld: Geef alle medewerkers wiens departementnummer niet gekend is => resultaat: 1 (van de 20).

Terminologie

  • Literal: een constante waarde.

  • Keyword: gereserveerd woord voor SQL-commando's.

  • Identifier: naam die gebruikt wordt om een tabel, kolom of andere objecten te verwijzen.

  • Operator: symbool dat een actie of vergelijking aanduidt.

  • Expressie: combinatie van waardes en operatoren.