Verschillende talen worden ondersteund door SQL Server, wat de mogelijkheid biedt om met veel verschillende contexten te werken. Om een reeks van doelen te bereiken. SQL is de meest gebruikte, maar niet de enigste, taal voor onder andere data-analyse en datamining.

Leerdoelen van deze les

Na afronding van deze les, ben je in staat om

• De componenten van SQL beschrijven
• Een SELECT query met WHERE voorwaarde schrijven
• Het doel van de andere talen die door SQL Server ondersteund worden beschrijven.

Voor het creëren, beheren en benaderen van RDBMS systemen is Structured Query Language (SQL) een standaardtaal. SQL wordt ondersteund door onder andere Microsoft SQL Server, Oracle, Sybase en IBM DB2.

Het American National Standards Institute (ANSI) en de International Organization for Standardization (ISO) beheersen SQL, waarvan versie SQL:2019 de meest recente is. De diverse RDBMS systemen respecteren deze standaarden, maar verschillen in geringe mate van de standaarden. Microsoft heeft hun versie voor SQL Server de naam Transact-SQL (TSQL) genoemd.

SQL kent drie subsets, waarover het volgende

Data Definition Language (DDL)

DDL bevat commando’s voor het definiëren van database onderdelen, zoals tabellen en indexen. Een index is een hulp tabel, waarmee het zoeken in de zoeken in de hoofdtabel versneld wordt, net als een inhoudsopgave in een boek. Met DDL worden CREATE, ALTER en DROP gebruikt voor respectievelijk maken, aanpassen en verwijderen van onderdelen

Het onderstaande voorbeeld maakt een tabel met de naam Verkoop.Klant met als kolommen KlantID, WinkelID, RegioID, KlantNummer en WijzigDatum:

Voorbeeld DDL:

Elke kolom in een tabel heeft een datatype in dit voorbeeld worden de type Int en datetime gebruikt. De eerste voor het vastleggen van integere waarden en de tweede voor datum- en tijdgegevens.

Tevens wordt bij elke kolom aangegeven of de kolom verplicht gevuld moet worden. In dat geval krijgt de kolomdefinitie de toevoeging NOT NULL, als het niet verplicht is een waarde in te vullen de toevoeging NULL.

•

Opmerking: Het wordt aanbevolen om de tabelnamen uit twee delen te laten bestaan. Het eerste deel verwijst naar de subset in de database waartoe de tabel behoord en het tweede deel verwijst naar de gegevenssoort in de tabel. Tabellen worden over het algemeen verdeeld in subsets, zoals Verkoop voor alle tabellen die relevant zijn voor de verkoop.

Data Manipulation Language (DML)

DML omvat commando’s voor het bekijken en wijzingen van gegevens in de database. DML commando’s zijn bijvoorbeeld SELECT, INSERT, UPDATE en DELETE.

Met het SELECT commando worden gegevens uit een database gelezen. Met INSERT, UPDATE en DELET worden respectievelijk rijen toegevoegd, aangepast en verwijderd. Als een klant zich aan meldt voor een webshop zal een nieuwe regel aan de database toegevoegd worden met het INSERT commando.

Als deze klant, in een later stadium, het telefoonnummer aanpast wordt de aanwezige regel aangepast met het UPDATE commando. 

Het onderstaande voorbeeld leest alle KlantID’s en Klantnummers uit de Verkoop.Klant tabel:

DML voorbeeld

Het SELECT commando wordt in de volgende paragraaf verder belicht.

Data Control Language (DCL)

Tot DCL behoren drie commando’s voor het definiëren rechten tot de gegevens in de database. Deze commando’s zijn GRANT, DENY en REVOKE. Het GRAND commando wordt gebruikt om specifieke rechten toe te kennen, zoals het mogen uitvoeren van het SELECT of UPDATE commando. Dit kan zowel aan gebruikers specifiek of per groep. Het DENY commando wordt gebruikt om expliciet te voorkomen dat gebruikers een van deze commando’s uit mogen voeren. Dit kan gebruikt worden als uit een groep enkele gebruikers afwijkende rechten moeten krijgen. Het REVOKE commando ten slotte wordt gebruikt om de rechten ingesteld met GRAND of DENY in te trekken.

Het onderstaande voorbeeld geeft gebruiker Jaap rechten om het SELECT commando uit te voeren op de Verkoop.Klant tabel:

Transact SQL (TSQL) Queries

Zoals in de voorgaande paragraaf al getoond kan met het SELECT commando gegevens uit een database gelezen worden. Een SELECT commando begint met het SELECT sleutelwoord, gevolgd door een lijst met kolomnamen, gescheiden door komma’s, waarin de gegevens staan die je wil zien. Hierna volgt het FROM sleutelwoord waarna de naam volgt van de tabel waarin de gegevens staan.

Als alle kolommen uit de tabel gewenst zijn kan volstaan worden met een ster achter het SELECT sleutelwoord. Einde van elk SQL commando wordt gemarkeerd door een puntkomma, hoewel dit niet in alle SQL variaties verplicht is. Het wordt echter wel aanbevolen.

Een filter op het SELECT commando gebruiken met een WHERE voorwaarde

Het SELECT commando bij het DML voorbeeld hiervoor, zal alle rijen uit de Verkoop.Klant tabel als resultaat hebben. Het zal weinig voorkomen dat je alle rijen wil hebben. Hat kan bijvoorbeeld voorkomen dat de verkoopresultaten uit een specifieke maand gewenst zijn, eventueel uit een specifieke regio. Dan zijn alle overige rijen overbodig.

Een belangrijke eigenschap van queries is dat deze alleen die gegevens uit de database halen waarin je geïnteresseerd bent. Aanvullend op het feit dat het resultaat eenvoudiger te begrijpen is en accuraat, zorgt het filteren ook voor een snellere verwerking omdat de database minder gegevens terug hoeft te geven.

Om een filter voorwaarde aan een SELECT commando toe te voegen is het WHERE sleutelwoord beschikbaar. De voorwaarde achter het sleutelwoord moet als resultaat waar, onwaar of onbekend opleveren.

In onderstaan voorbeeld worden uit de verkooporder tabel alle orders geselecteerd van de verkoper met als ID 234.

Operatoren

In bovenstaand voorbeeld staat bij de WHERE voorwaarde “VerkoperID = 234”. Het = (is gelijk) teken is een voorbeeld van een operator. Deze definiëren hoe een voorwaarde wordt geëvalueerd. Andere operatoren zijn:

•	>	groter dan
•	<	kleiner dan
•	>=	groter dan of gelijk aan
•	<=	kleiner dan of gelijk aan
•	<>	niet gelijk aan
•	!=	niet gelijk aan

Met deze operatoren kunnen meerdere voorwaarden geëvalueerd worden. Met de AND operator kunnen twee voorwaarden bepaald worden die allebei waar moeten zijn. Met de OR operator kunnen twee voorwaarden bepaald worden waarvan er een waar moet zijn. Daarnaast is de operator LIKE mogelijk om op tekst kolommen te filteren met een joker teken. Tenslotte kan IS NULL of IS NOT IS NULL gebruikt worden om te filter op kolommen die leeg of juist niet leeg zijn.

Demonstratie: Een SQL Server database uitvragen

In deze demonstratie wordt getoond:

• Met een SELECT commando alle rijen en kolommen van een tabel opvragen
• Met een SELECT commando een alle rijen van een paar kolommen opvragen uit en tabel
• Een SELECT commando voorzien van een WHERE voorwaarde om een gedeelte van de rijen uit een tabel op te vragen.
• Verschillende operatoren gebruiken in een WHERE voorwaarde.

Demonstratie stappen

Ander uitvraag talen voor SQL Server naast als primaire Transact SQL.

• XQuery
• MDX
• Data Analysis Expression (DAX)
• Data Mining Expression (DMX)
• R

XQuery

De opmaaktaal XML vertoond overeenkomsten met HTML, omdat beide tags gebruiken. Deze tags hebben punt halen (< en >) om ze herkenbaar te maken. De tags begrenzen met een begin- en bijpassend eindtype de gegevens. In dit voorbeeld is in XML het uiterlijk van een persoon opgeslagen.

Een XML document begint meestal met een XML schema waarin bepaald wordt hoe het XML document moet zijn opgebouwd. Hierin worden de structuur van het document bepaald, samen met de waarde types eb eventueel standaardwaarden. In SQL Server kan een kolom als type XML waardoor het mogelijk is met XQuery deze kolom uit te vragen. Bijvoorbeeld kan met XQuery uit een tabel met persoonsgegevens, waarbij het uiterlijk zoals in bovenstaand voorbeeld vast ligt, alleen die personen opgevraagd worden met blauwe ogen of zwart haar.

MDX

MDX is een uitvraag taal die vergelijkbaar is met SQL. Ook gebruikt het SELECT, FROM en WHERE, maar de syntax binnen deze onderdelen verschilt met die van SQL.

MDX is een uitvraagtaal speciaal voor multidimensionale datamodellen en heeft als resultaat ook een multidimensionaal model. In een MDX wordt bij het SELECT deel per uitgevraagd element aangegeven op welke as van met resultaat model geplaatst moet worden. Bijvoorbeeld landen langs de X as, producten lang de Y as en perioden lang de Z as. Omdat het resultaat een multidimensionaal model is, kan het ui t meer als 3 assen bestaan. In het FROM deel wordt met model waar de gegevens in staan opgegeven en in het WHERE deel de filter voorwaarden. Dit kan bijvoorbeeld op een specifieke verkoper.

De door dataprofessionals gebruikte software, zoals Excel, kunnen MDX uitvragingen gebruiken op gegevens uit multidimensionale modellen te halen. Met de opgehaalde gegevens kunnen vervolgens draaitabellen en grafieken gemaakt worden als visuele weergaven van deze gegevens. Excel stelt zelf de

MDX uitvraging samen naar de behoefte van de dataprofessional, zodat deze het niet zelf hoeft te doen.

DAX

Om bedrijfslogica toe te passen bij het werken met datamodellen in tabellen kan DAX gebruikt worden. DAX werkt met formules die grote overeenkomst vertonen met Excel formules. Met DAX functies kunnen metingen aan tabellen worden toegevoegd, relaties tussen tabellen gedefinieerd of query’s gemaakt om gegevens uit meerdere tabellen te combineren.

DMX

SQL Server Analysis Services omvat onder andere datamining. Dit is een proces voor het onderzoeken van grote hoeveelheden gegevens om correlaties en trends te vinden die vooraf niet duidelijk zijn. Met DMX kan datamining gebruikt worden om modellen te maken en beheren en daaruit voorspellingen te maken.

R

Met R kunnen uitgebreide statistische analyses uitgevoerd worden op complexe datamodellen.

De analytische mogelijkheden van R gaan verder dan de mogelijkheden van andere talen die SQL Server ondersteunt. Het biedt de mogelijkheid om diverse complexe gegevenssets te onderzoeken en hieruit sterke datamodellen te maken. Voor het presenteren van de gegevens aan gebruikers bevat R ook grafische mogelijkheden. Hiermee worden ze gemakkelijker te begrijpen. Omdat R standaard in SQL Server aanwezig is, kan het direct gebruikt worden. Het is niet nodig om de gegevens uit SQL server te halen voordat de analyse begint.