SQL leren – Een tutorial met co­de­voor­beel­den

De syntaxis van SQL is gebaseerd op re­la­ti­o­ne­le algebra, waardoor deze pro­gram­meer­taal verschilt van andere talen. Door uzelf vertrouwd te maken met de syntaxis aan de hand van prak­ti­sche voor­beel­den, kunt u SQL effectief leren.

Wat is SQL-syntaxis?

In de pro­gram­me­ring verwijst syntaxis naar de manier waarop een pro­gram­meer­taal wordt ge­schre­ven. De syntaxis bepaalt de ba­sis­con­struc­ties van de code en hoe deze met elkaar worden verbonden. Inzicht in de syntaxis is een fun­da­men­te­le vereiste voor het lezen en schrijven van code in pro­gram­meer­ta­len.

De be­lang­rijk­ste syn­taxis­con­struc­ties in SQL zijn SQL-sta­te­ments, die ook clausules kunnen bevatten. Beide worden ge­woon­lijk ‘SQL-commando’s’ genoemd, hoewel dit vanuit technisch oogpunt niet helemaal correct is. Dit zijn echter niet de enige SQL-syn­taxis­con­struc­ties. Hieronder vindt u een tabel met een overzicht van de SQL-syn­taxis­con­struc­ties.

Hoe wordt SQL-code uit­ge­voerd?

SQL-code bestaat als broncode in tekst­be­stan­den. De code komt alleen tot leven in een geschikte uit­voe­ringsom­ge­ving. De broncode wordt gelezen door een SQL-in­ter­pre­ter en omgezet in acties van een RDBMS. Hierbij zijn er twee ba­sis­be­na­de­rin­gen:

1. SQL-code in­ter­ac­tief uitvoerenBij deze aanpak wordt SQL-code recht­streeks in een tekst­ven­ster ingevoerd of ge­ko­pi­eerd. De SQL-code wordt uit­ge­voerd en het resultaat wordt weer­ge­ge­ven. U kunt de code aanpassen en opnieuw uitvoeren. Door de snelle op­een­vol­ging van co­de­ma­ni­pu­la­tie en weergave van re­sul­ta­ten is deze aanpak het meest geschikt voor het leren en maken van complexe query’s. 2. SQL-code als script uitvoerenBij deze aanpak wordt een volledig bron­co­de­be­stand met SQL-code regel voor regel uit­ge­voerd. Indien nodig wordt er pas aan het einde van de uit­voe­ring feedback naar de gebruiker gestuurd. Deze aanpak is het meest geschikt voor het au­to­ma­ti­se­ren van processen en voor het im­por­te­ren van MySQL-da­ta­ba­se­back-ups met MySQL dump.

Hoe een pro­duct­be­heer­sys­teem opzetten met behulp van SQL

De een­vou­dig­ste manier om een pro­gram­meer­taal te leren, is door zelf code te schrijven en uit te voeren. In deze tutorial maken we een mi­ni­da­ta­ba­se en voeren we daar query’s op uit. Hiervoor gebruiken we de online SQL-in­ter­pre­ter van de website sql.js. Om de tutorial te volgen, ga je naar de website en vervang je de reeds in­ge­voer­de SQL-code door de code uit onze voor­beel­den. Voer de code stukje voor stukje uit om de re­sul­ta­ten weer te geven.

Een SQL-database opzetten

In dit voorbeeld gaan we een com­mer­ci­eel pro­duct­be­heer­sys­teem voor een winkel bouwen. Dit zijn de vereisten:

• Er zijn ver­schil­len­de producten en we hebben van elk product een bepaalde hoe­veel­heid op voorraad.
• Ons klan­ten­be­stand omvat veel klanten en afnemers.
• Be­stel­lin­gen van klanten kunnen meerdere producten bevatten.
• Voor elke be­stel­ling slaan we de be­stel­da­tum en de gegevens van de persoon die de be­stel­ling heeft geplaatst op, evenals de bestelde producten en de bestelde hoe­veel­heid.

Deze vereisten worden vertaald naar een abstracte be­schrij­ving en ver­vol­gens naar SQL-code:

1. Model maken
2. Schema de­fi­ni­ë­ren
3. Ge­ge­vens­re­cords invoeren
4. Query’s de­fi­ni­ë­ren

Maak een model van en­ti­tei­ten en relaties

De eerste stap vindt plaats op papier of met speciale mo­del­leer­tools. We ver­za­me­len in­for­ma­tie over het te mo­del­le­ren systeem om en­ti­tei­ten en relaties af te leiden. Deze stap wordt vaak ge­re­a­li­seerd als een Entity Re­la­ti­ons­hip (ER)-diagram.

Welke en­ti­tei­ten zijn er en hoe zijn ze met elkaar verbonden? En­ti­tei­ten zijn ca­te­go­rie­ën van dingen. In ons voorbeeld van het pro­duct­be­heer­sys­teem zijn de en­ti­tei­ten producten, klanten en be­stel­lin­gen. Voor elke entiteit is een tabel nodig. Vanwege de spe­ci­fie­ke kenmerken van het re­la­ti­o­ne­le model worden er extra tabellen toe­ge­voegd om de relaties te mo­del­le­ren. Om dit te herkennen en correct te im­ple­men­te­ren, is ervaring nodig.

Een centrale vraag die be­ant­woord moet worden, is hoe de en­ti­tei­ten zich tot elkaar verhouden. Hierbij moeten we beide rich­tin­gen van een relatie in ogen­schouw nemen en on­der­scheid maken tussen enkelvoud en meervoud. Hier volgt een voorbeeld met auto’s en au­to­be­zit­ters:

1. Eén eigenaar kan mogelijk meerdere auto’s bezitten.
2. Een auto kan slechts aan één eigenaar toe­be­ho­ren.

Er zijndrie mogelijke re­la­tie­pa­tro­nen tussen de twee en­ti­tei­ten:

Producten im­ple­men­te­ren

Eerst im­ple­men­te­ren we de pro­duc­ten­ta­bel. Hiervoor moeten we een schema de­fi­ni­ë­ren, ge­ge­vens­re­cords invoeren en, voor test­doel­ein­den, een paar een­vou­di­ge query’s uitvoeren.

Schema de­fi­ni­ë­ren

Het centrale SQL-commando voor het de­fi­ni­ë­ren van da­ta­ba­se­ta­bel­len is CREATE TABLE. Met dit commando kunt u een tabel met een naam aanmaken en ko­lo­mei­gen­schap­pen spe­ci­fi­ce­ren. Te­ge­lij­ker­tijd worden ge­ge­vens­ty­pen en, indien nodig, be­per­kin­gen op de op te slaan waarden ge­de­fi­ni­eerd:

DROP TABLE IF EXISTS Products;
CREATE TABLE Products ( product_id int, product_name text, stocked int, price int );

Dataset toevoegen

Nu gaan we een paar test­re­cords aanmaken. We gebruiken het SQL-commando INSERT INTO en de functie VALUES om de velden in te vullen:

INSERT INTO Products VALUES (10, 'ABC Product', 74, 1050);
INSERT INTO Products VALUES (20, 'KLM Product', 23, 750);
INSERT INTO Products VALUES (30, 'XYZ Product', 104, 350);

Queries de­fi­ni­ë­ren

Om de status van de tabel Products te con­tro­le­ren, schrijven we een een­vou­di­ge query. We gebruiken het commando SELECT FROM en geven de volledige tabel weer:

SELECT * FROM Products;

Nu gaan we een iets com­plexe­re query schrijven die de totale waarde berekent van de producten die we op voorraad hebben:

SELECT product_name AS 'Name', (stocked * price) AS 'Value' FROM Products;

Extra tabellen im­ple­men­te­ren

Ver­vol­gens maken we de res­te­ren­de tabellen die we nodig hebben. We volgen dezelfde stappen als voor de tabel Producten. Eerst maken we de tabel Klanten:

DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers ( customer_id int, customer_name text, contact text );

Ver­vol­gens voeren we ge­ge­vens­re­cords in voor twee voor­beeld­klan­ten:

INSERT INTO Customers VALUES (100, 'EDC Customer', 'ED@example.com');
INSERT INTO Customers VALUES (200, 'WVU Customer', 'WV@example.com');

Om te con­tro­le­ren of het gewerkt heeft, geven we de klan­ten­ta­bel weer:

SELECT * FROM Customers;

De volgende stap is het aanmaken van de tabel Orders:

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders ( order_id int, customer_id int, order_date text );

Nu voeren we drie voor­beeld­in­koop­or­ders in. Voor de eerste waarde van de records wijzen we een ID toe als primaire sleutel. De tweede waarde is voor bestaande klant-ID’s, die fungeren als externe sleutels. Ver­vol­gens slaan we de datum van de be­stel­ling op:

INSERT INTO Orders VALUES (1000, 100, '2022-05-03');
INSERT INTO Orders VALUES (1001, 100, '2022-05-04');
INSERT INTO Orders VALUES (1002, 200, '2022-05-08');

Om dit te testen, geven we de volgende op­drach­ten:

SELECT * FROM Orders;

Ten slotte hebben we een tabel nodig voor de producten in een be­stel­ling, samen met hun hoe­veel­heid. Dit is een m:n-relatie, omdat een be­stel­ling meerdere producten kan bevatten en een product in meerdere be­stel­lin­gen kan voorkomen. We de­fi­ni­ë­ren een tabel die de ID’s van be­stel­lin­gen en producten als externe sleutels bevat:

DROP TABLE IF EXISTS OrderItems;
CREATE TABLE OrderItems ( orderitem_id int, order_id int, product_id int, count int );

Nu voeren we een aantal bestelde producten in. We kiezen de ID’s van de be­stel­lin­gen en producten zo dat er een be­stel­ling is met twee producten en een andere be­stel­ling met slechts één product:

INSERT INTO OrderItems VALUES (10001, 1000, 10, 3);
INSERT INTO OrderItems VALUES (10002, 1000, 20, 2);
INSERT INTO OrderItems VALUES (10003, 1002, 30, 12);

Om dit te con­tro­le­ren, zullen we de bestelde producten verzenden:

SELECT * FROM OrderItems;

Schrijf complexe query’s

Als u alle tot nu toe getoonde co­de­frag­men­ten hebt uit­ge­voerd, zou u de structuur van onze test­da­ta­ba­se moeten begrijpen. Laten we nu ver­der­gaan met com­plexe­re query’s die de kracht van SQL de­mon­stre­ren. Laten we eerst een query schrijven die gegevens uit meerdere tabellen sa­men­voegt. We gebruiken een SQL JOIN-opdracht om de tabellen met klant­ge­ge­vens en be­stel­lin­gen samen te voegen. Daarbij geven we de kolommen een naam en stellen we een over­een­ko­men­de klant-ID in als JOIN-voor­waar­de. Houd er rekening mee dat we ge­kwa­li­fi­ceer­de iden­ti­fi­ca­tie­co­des gebruiken om on­der­scheid te maken tussen de kolommen van de twee tabellen:

SELECT customers.customer_name as 'Customer', customers.customer_id, orders.order_id, orders.order_date AS 'Date' FROM Customers JOIN Orders ON Orders.customer_id = Customers.customer_id ORDER BY Customers.customer_id;

Nu gebruiken we nog een JOIN-commando om de totale kosten van de bestelde producten te berekenen:

SELECT OrderItems.order_id, OrderItems.orderitem_id AS 'Order Item', Products.product_name AS 'Product', Products.price AS 'Unit Price', OrderItems.count AS 'Count', (OrderItems.count * Products.price) AS 'Total' FROM OrderItems JOIN Products ON OrderItems.product_id = Products.product_id;