Voorbeelden van SQL-query's en tutorial

De structuur van een database voelt erg vergelijkbaar met Excel. Maar databases zijn over het algemeen krachtiger dan Excel-spreadsheets. Hier zijn drie redenen waarom: 

1. Databases kunnen met veel andere programmeertalen samenwerken, waardoor een programmeur meer mogelijkheden heeft om informatie uit data te beheren en op te halen.
2. Databases kunnen meer data verwerken dan Excel-spreadsheets. Excel kan tot 1 miljoen rijen aan, terwijl een database meer dan een miljard rijen aankan.
3. Databases zijn eenvoudiger te koppelen aan het internet, zodat honderden gebruikers en applicaties er tegelijkertijd toegang toe kunnen hebben.

Sta je te popelen om met SQL te beginnen? DataCamp heeft geweldig materiaal om je op weg te helpen; leer SQL laat de tracks zien waarmee je je SQL-skills kunt aanscherpen!

Duiken we nu dieper in SQL-query's… 

Een SQL-query is een instructie die is opgebouwd uit verschillende SQL-commando's. Samen voeren deze SQL-commando's een specifieke taak uit om je data in een database te benaderen, beheren, wijzigen, updaten, controleren en organiseren, beheerd via een DBMS. 

Waarom SQL gebruiken?

SQL is enorm veelzijdig en wordt gebruikt door iedereen van datawetenschappers en -engineers tot marketeers en HR-teams – eigenlijk door iedereen die simpelweg datavaardiger wil worden!

Het wordt niet alleen in de techsector gebruikt of beperkt tot grote bedrijven. Meerdere sectoren van elke omvang gebruiken het direct of indirect. Zo gebruiken professionals in de financiële sector en de gezondheidszorg vaak SQL, gezien de grote hoeveelheid data die zij produceren. 

Data is het nieuwe goud, en SQL is een zeer krachtig hulpmiddel voor het werken met big data. Benieuwd hoe je SQL maximaal benut? Bekijk DataCamps tutorial What can you do with SQL om die vraag te beantwoorden.

Hoe schrijf je SQL-query's

Voordat we in de formulering van query's duiken, eerst de vraag: “Waar schrijf en voer ik mijn SQL-query uit?” 

DataCamps Learn SQL-hub is een goed startpunt om met SQL te werken op een Mac of Windows-machine en te beginnen.

Wil je simpelweg de moedertaal van databases – SQL – leren, dan kun je starten met DataCamps Introduction to SQL-cursus. Deze cursus behandelt SQL-syntaxis die door veel typen databases wordt gedeeld, zoals PostgreSQL, MySQL, SQL Server en Oracle. Je werkt met DataCamps ingebouwde SQL-interface en hoeft je niet bezig te houden met de initiële setup van een RDBMS. 

DataCamp heeft ook een geweldig notitieboekhulpmiddel waarin je je eigen notebooks kunt maken: DataLab. DataLab is een collaboratief cloudgebaseerd notebook waarmee je data kunt analyseren, met je team kunt samenwerken en inzichten kunt delen. DataLab is ontworpen om je van leren data science naar doen van data science te brengen. Met ingebouwde datasets kun je binnen enkele minuten data gaan analyseren.

Bekijken we nu de dataset waarmee we in deze tutorial werken. 

We beginnen met ‘films’ – een SQL-tabel met gegevens van 10 films. Deze data is slechts een kleine subset van de IMDb Film-dataset die wordt gebruikt in DataCamps Introduction to SQL-cursus die we eerder noemden.

De tabel ‘films’ heeft tien records – de filmtitels staan verspreid over 10 rijen. De velden zijn de vijf kolommen in de tabel met informatie over de film. 

• ‘title’ is de naam van de film
• ‘release_year’ is het jaar waarin de film uitkwam
• ‘budget’ zijn de productiekosten van de film in Amerikaanse dollars
• ‘gross’ is hoeveel de film heeft opgebracht in Amerikaanse dollars. 
• ‘imdb_score’  toont de beoordelingen op basis van stemmen van IMDb-gebruikers, niet van filmcritici. 

Laten we beginnen met een paar basale SQL-commando's om de bovenstaande tabel ‘films’ te verkennen. We verwachten niet dat je de oplossingsquery's meteen uitvoert… gebruik ze vooral als basis om enkele kernconcepten te leren. Maak je dus geen zorgen als je nog niet hebt gekozen op welk platform je de query's wilt draaien.

Basale SQL-query's

Onthoud dat SQL-trefwoorden of -commando's niet hoofdlettergevoelig zijn; ‘select’ schrijven is hetzelfde als ‘SELECT’. 

We gebruiken een puntkomma aan het einde van elke SQL-instructie die we schrijven. Dat is de norm bij sommige databasesystemen en wordt gedaan om elke SQL-instructie te scheiden zodat ze in dezelfde call naar de server kunnen worden uitgevoerd. 

We beginnen met een vraag. V1: Welke films zijn er opgeslagen in de tabel?

SELECT - FROM

Met de SELECT-instructie kies je de velden die je uit de databasetabel wilt zien. Voor V1 wil je het veld ‘title’.

Het trefwoord/commando FROM specificeert de specifieke tabel in de database waaruit je de data wilt ophalen. Onthoud: een database kan uit meerdere tabellen bestaan.

Syntaxis: SELECT column1, column2, … FROM table_name;

SQL-query: SELECT title FROM films;

Resultaat: 

Je vraagt je misschien af: wat als je de tabel hierboven nooit hebt gezien, zodat je de kolomnamen niet kent om in de SELECT-instructie te specificeren? Goed punt! Later in de tutorial leren we over de magische ‘*’ (asterisk). 

WHERE

V2: Welke films verschijnen in 2010?

Voor deze vraag wil je de films “filteren” zodat je alleen filmtitels hebt die voldoen aan de voorwaarde: ‘release_year’ is 2010. 

Je kunt de ‘WHERE’-clausule gebruiken om alleen die records op te halen die aan een opgegeven voorwaarde voldoen.

Syntaxis: SELECT column1, column2, … FROM table_name WHERE condition; 

SQL-query: SELECT title, release_year FROM films WHERE release_year = 2010;

Resultaat: 

Operatoren in de WHERE-clausule

De volgende operatoren kunnen binnen de WHERE-clausule worden gebruikt:

Rekenen in SQL

Je kunt eenvoudige rekenkundige bewerkingen in SQL uitvoeren met de wiskundige symbolen: +, -, *, /. Je kunt deze bewerkingen echter alleen over kolommen heen uitvoeren. Je kunt ook haakjes gebruiken om de volgorde van bewerkingen te regelen.

Maar wees gerust, voor complexere bewerkingen heeft SQL aggregatiefuncties waarmee je bewerkingen binnen dezelfde rij kunt uitvoeren. We behandelen dit onderwerp later in de tutorial.

V3: Bereken de winst die elke film in de tabel ‘films’ heeft gemaakt. 

Hint: Voer Gross - Budget uit en sla het resultaat op in een kolom met de naam ‘movie_profit’ met behulp van AS

SQL-query: SELECT title, (gross - budget) AS movie_profit FROM films

Resultaat: 

Disclaimer: Filmliefhebbers zullen zich misschien storen aan de negatieve winst voor Fight Club. De gebruikte formule ((gross - budget) AS movie_profit) is heel eenvoudig en gegeven de formule en de data is dit inderdaad het correcte resultaat. 

Maar het bovenstaande voorbeeld herinnert je eraan alert te zijn op het dataverzamelingsproces! Zelfs eenvoudige data-exploratie kan fouten in de data blootleggen die kunnen zijn ontstaan door fouten bij het verzamelen of opslaan (wijziging van datatype, enz.) – dat kan zijn gebeurd bij het samenstellen van de gebruikte dataset. 

Let altijd goed op de data waarmee je werkt!

AND, OR, NOT

De WHERE-clausule kan worden gecombineerd met andere voorwaardelijke clausules met de operatoren AND, OR en NOT. Met AND en OR filter je records op basis van meer dan één voorwaarde, en met NOT keer je een voorwaarde om. 

Bekijken we hun gebruik met voorbeelden:

V4: Welke films verschijnen in 2010 en hebben een rating hoger dan 9?

Syntaxis: SELECT column1, column2, … FROM table_name WHERE condition1 AND condition2 AND condition3 AND condition4…..;

SQL-query: SELECT title, release_year, imdb_score FROM films WHERE release_year = 2010 AND imdb_score >= 9;

De AND-operator toont een record alleen als alle door AND gescheiden voorwaarden waar zijn.

In de tabel ‘films’ hebben we de film ‘Inception’. Die is de enige waarde waarvoor release_year = 2010 waar is, maar imdb_score >= 9 is onwaar en voldoet dus niet aan beide AND-voorwaarden. De query levert daarom een lege resultset op.

V5: Welke films verschijnen in 2010 of hebben een rating hoger dan 9?

Syntaxis: SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3 OR condition4…..;

SQL-query: SELECT title, release_year, imdb_score FROM films WHERE release_year = 2010 OR imdb_score >= 9;

Resultaat: 

De OR-operator toont een record als een van de door OR gescheiden voorwaarden WAAR is.

De films: The Godfather, The Dark Knight, The Godfather: Part II, The Shawshank Redemption zijn waar voor imdb_score >= 9, terwijl Inception waar is voor release_year = 2010, en daarom voldoen ze aan de OR-voorwaarde.

V6: Welke films hebben een ander releasejaar dan 2010?

Syntaxis: SELECT column1, column2, … FROM table_name WHERE NOT condition1;

SQL-query: SELECT title, release_year FROM films WHERE NOT release_year = 2010;

Resultaat: 

Je zou ook kunnen schrijven: SELECT title, release_year FROM films WHERE release_year != 2010;

Dit geeft hetzelfde resultaat als hierboven. Er zijn vaak meerdere manieren om in SQL hetzelfde resultaat te behalen. Sommige query's kunnen echter beter presteren dan andere. De performance hangt af van meerdere factoren – de hoeveelheid data, commando's in de query, hoe de database is gestructureerd, datatype, enzovoort. Dit is een wat ingewikkelder en geavanceerder onderwerp dat we in deze SQL-query-tutorial niet behandelen. 

Ben je geïnteresseerd, dan gaat DataCamps Database Design-cursus dieper op deze onderwerpen in.

ORDER BY

Het trefwoord ORDER BY wordt gebruikt om het resultaat te sorteren in oplopende (met het trefwoord ASC) of aflopende volgorde (met het trefwoord DESC). Standaard wordt oplopend gesorteerd.

V7: Welke films zijn er opgeslagen in de tabel? Sorteer in aflopende volgorde van release_year.

Syntaxis: SELECT column1, column2, … FROM table_name ORDER BY column1, column2, ... ASC|DESC;

SQL-query: SELECT title, release_year FROM films ORDER BY release_year DESC;

Resultaat: 

LIMIT

Soms kan het lang duren voordat de database je query uitvoert; vooral als de hoeveelheid data in de database erg groot is. 

Een snelle manier om een query te testen of te proeven van het soort resultaat is de functie LIMIT te gebruiken. LIMIT laat je het aantal resultaten beperken.

V8: Wat zijn de 5 meest recente films in de tabel? 

Hint: Sorteer de resultset aflopend op release_year en gebruik daarna LIMIT.

Syntaxis: SELECT column1, column2, … FROM table_name LIMIT x;

SQL-query: SELECT title, release_year FROM films ORDER BY release_year DESC LIMIT 5;

Resultaat: 

INSERT INTO

INSERT INTO wordt gebruikt om nieuwe records aan een databasetabel toe te voegen. Je kunt een INSERT INTO-instructie op twee manieren schrijven: 

• Specificeer zowel kolomnamen als de bijbehorende waarden

Syntaxis: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

• Als je waarden toevoegt voor ALLE kolommen van de tabel, hoef je de kolomnamen niet in de query te zetten. Wees voorzichtig met deze methode en zorg dat de volgorde van de waarden dezelfde is als de kolomvolgorde in de tabel. 

Syntaxis: INSERT INTO table_name VALUES (value1, value2, value3, ...);

V9: Voeg een entry voor ‘The Godfather: Part III’ toe aan de tabel met de details: (‘The Godfather: Part III’, 1990, 54000000, 136900000, 7.6)

Syntaxis: SELECT column1, column2, … FROM table_name ORDER BY column1, column2,... ASC|DESC;

SQL-query: INSERT INTO films (title, release_year, budget, gross, imdb_score)

VALUES (‘The Godfather: Part III’, 1970, 54000000, 136900000, 7.6);

Resulterende tabel: 

UPDATE

We gebruiken de UPDATE-instructie om bestaande records in een tabel te wijzigen. 

V10: Corrigeer het releasejaar voor ‘The Godfather: Part III’ naar 1990 in plaats van 1970

Syntaxis: UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

SQL-query: UPDATE films SET release_year = 1990 WHERE title = ‘The Godfather: Part III’;

Resulterende tabel: 

DELETE

De DELETE-instructie wordt gebruikt om een bestaand record in een tabel te verwijderen. 

V11: Verwijder de entry voor ‘The Godfather: Part III’ uit de tabel ‘films’.

Syntaxis: DELETE FROM table_name WHERE condition;

SQL-query: DELETE FROM films WHERE title = ‘The Godfather: Part III’;

Resulterende tabel: Zelfde als de oorspronkelijke tabel waarmee we begonnen, met in totaal 10 records.

Gevorderde SQL-query's

We hadden de magische ‘*’ even bewaard voor later aan het einde van V1. De * is een asterisk. Het is een overkoepelend teken dat je kunt lezen als ‘alles’. 

Terug naar V1: Welke films zijn er opgeslagen in de tabel?

We gebruikten SELECT - FROM met de Syntaxis: SELECT column1, column2, … FROM table_name;

We gebruikten de query: SELECT title FROM films;

Maar wat als je niet wist dat ‘title’ een veld is in de tabel ‘films’? Of de naam van welk veld dan ook in de tabel? Of je wilt gewoon alle velden in de tabel zien om gevoel voor de data te krijgen? Dan komt * van pas.

We kunnen de query herschrijven als: SELECT * FROM films; 

Dit haalt alle velden op die in de tabel beschikbaar zijn.

SQL-wildcards

LIKE

De operator LIKE wordt gebruikt in een WHERE-clausule om naar een opgegeven patroon in een kolom te zoeken. 

V12: Haal alle filmtitels op die beginnen met ‘The’

Syntaxis: SELECT column1, column2, … FROM table_name WHERE column1 LIKE patternToMatch;

SQL-query: Select * FROM films WHERE title LIKE ‘The%’;

Resulterende tabel:

Wat fijn is: we kunnen wildcards combineren! Zo kun je behoorlijk complexe match-statements maken met wildcard-tekens in combinatie met LIKE.

Aggregatiefuncties

SQL is uitstekend in het aggregeren van data, vergelijkbaar met een draaitabel in Excel. Aggregatiefuncties voeren berekeningen uit niet tussen twee kolommen, maar binnen een kolom, dus op alle of sommige rijen in dezelfde kolom. 

Dit zijn de aggregatiefuncties in SQL: 

V13: Wat is de meest recente film in de tabel?

SQL-query: SELECT title AS latest_movie, MAX(release_year) AS released_in FROM films;

Resultaat: 

GROUP BY

SQL-aggregatiefuncties aggregeren over een hele kolom. Maar wat als je slechts een deel van een kolom wilt aggregeren? Je wilt bijvoorbeeld het aantal films tellen dat in een jaar is uitgebracht.

Daar heb je de GROUP BY-clausule voor nodig. Met GROUP BY kun je data in groepen verdelen, die vervolgens onafhankelijk van elkaar kunnen worden geaggregeerd.

V13: Tel het aantal films dat in een jaar is uitgebracht.

SQL-query: SELECT release_year, COUNT(*) AS number_of_movies FROM films GROUP BY release_year;

Resultaat: 

Voorbeelden van SQL-query's uit meerdere tabellen

Tot nu toe hebben we steeds met één tabel tegelijk gewerkt. Maar de echte kracht van databases en SQL komt uit het werken met data uit meerdere tabellen. 

De term "relationele database" in RDBMS komt doordat de tabellen in de database aan elkaar gerelateerd zijn. Ze hebben gemeenschappelijke identifiers waarmee data uit meerdere tabellen eenvoudig gecombineerd kan worden. Hiervoor gebruiken we SQL-tabel-JOINs.

Bekijken we een eenvoudig voorbeeld om vertrouwd te raken met de JOIN-syntaxis en om iets te leren over de verschillende soorten JOINs.

Beschouw een andere tabel, ‘movieDirectors’, waarin de naam van de regisseur staat voor enkele films die we in de tabel ‘films’ hebben. 

We kunnen de data in deze twee tabellen combineren met een gemeenschappelijke identifier: ‘title’.

V14: Voeg een kolom toe aan de tabel ‘films’ en vul deze met de naam van de regisseur uit de tabel ‘movieDirectors’.

Syntaxis: SELECT leftTable.column1, rightTable.column1, leftTable.column2…. FROM leftTable INNER JOIN rightTable ON leftTable.commonIdentifier = rightTable.commonIdentifier;

SQL-query: Select films.title, films.release_year, movieDirectors.director FROM films INNER JOIN movieDirectors ON films.title = movieDirectors.title

Resultaat: 

Verschillende soorten SQL-joins

In de voorbeeldquery hierboven is ‘films’ de linkertabel en ‘movieDirectors’ de rechtertabel. Met de INNER JOIN die we uitvoerden, kregen we alleen films terug die in beide tabellen bestonden. De resultset was de doorsnede van de twee tabellen.

SQL UNION

Met SQL JOINs combineer je twee datasets naast elkaar. Met SQL UNION kun je één dataset boven op de andere stapelen. De operator UNION wordt gebruikt om de resultset van twee of meer SELECT-instructies te combineren als aan de volgende “regels” is voldaan: 

1. Elke SELECT-instructie moet hetzelfde aantal kolommen hebben.
2. De kolommen moeten ook vergelijkbare datatypes hebben.
3. De kolommen die in de SELECT-instructies worden gebruikt, moeten in dezelfde volgorde staan.

Syntaxis: SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Beschouw de tabel ‘movies_2000s’:

Als we de twee tabellen nu combineren…

SQL-query: SELECT title FROM films UNION SELECT title FROM movies_2000s;

Resultaat: 

Je hebt misschien gemerkt dat ‘Inception’ maar één keer in de resultset voorkomt. Dat komt omdat UNION alleen unieke waarden selecteert; als je alle waarden wilt, gebruik je UNION ALL.

GENESTE SQL-query's

SQL kan wonderen verrichten met data. Geneste query's zijn weer een hulpmiddel in SQL dat het de moeite waard maakt om de taal te kennen. Een geneste query is niets anders dan een query binnen een query (een beetje zoals dromen in Inception!).

Met geneste query's kun je zeer complexe bewerkingen uitvoeren, maar dan in meerdere stappen, zodat de leesbaarheid en begrijpelijkheid van code intact blijft.

V15: Bepaal de gemiddelde winstgevendheid van de films in de tabel ‘films’.

Hint: Je hebt eerder bij V3 de winstgevendheid berekend. Nu moet je het resultaat van die query nemen en er de functie AVG op toepassen.

Geneste SQL-query: 

SELECT AVG(*) AS average_profit FROM 

(SELECT title, (gross - budget) AS movie_profit FROM films where gross > budget);

We hebben de negatieve waarde van ‘Fight Club’ uit de berekening in de binnenste SQL-query gehaald door gross > budget toe te voegen. 

In de geneste query hierboven wordt eerst de binnenste query uitgevoerd en vervolgens wordt deze resultset gebruikt als een tijdelijke tabel voor de buitenste query om uit op te vragen en data op te halen. 

We gaan niet te diep in op het onderwerp geneste SQL-query's. Maar we raden je van harte aan DataCamps Intermediate SQL-cursus te volgen om SQL-query's te beheersen. Je werkt met de European Soccer-database terwijl je meer leert over geneste query's. Je leert ook over CASE-statements en windowfuncties – onderwerpen die we in deze SQL-query-tutorial niet konden behandelen. 

Tot slot

Je hebt veel geleerd over SQL-query's en bent in een uitstekende positie om te beginnen met het oplossen van echte vraagstukken met SQL. In deze SQL-query-tutorial leerde je wat een SQL-query is en hoe je SQL-query's schrijft. 

Je zag de query-syntaxis en beantwoordde onderweg een paar vragen. We begonnen met eenvoudige voorbeelden met SELECT - FROM - WHERE en werkten toe naar iets complexere query's met joins, union en geneste query's. 

We hebben veel behandeld in de tutorial, maar dit is GEEN uitputtende lijst van alle mogelijke eenvoudige en geavanceerde SQL-query's – er is meer. We raden je ten zeerste aan om DataCamps SQL Fundamentals-track te volgen voor een diepere en meer complete SQL-kennis. 

Onthoud: oefenen is de sleutel tot het beheersen van elke vaardigheid en SQL is daarop geen uitzondering! Oefening tilt je SQL-skills van beginner naar gevorderd.

Dus, ga aan de slag en veel plezier met query'en!