SQL Casus

Casus SQL

Je eerste opdracht als functioneel beheerder en database beheerder begint vandaag. Je mag op opdracht bij het bedrijf Adventureworks en je bent onder andere verantwoordelijk voor het maken van query’s voor de business en voer je wijzigingen door in de database op verzoek van de manager. Je hebt je computer opgestart en begint de dag vol goede moed. In de verte zie je de manager naar je toekomen.

 

VOORBEREIDING

 

Start een lab-sessie op door naar de volgende link te gaan:

 

https://vlabs.holsystems.com/vlabs/technet?eng=VLabs&auth=none&src=vlabs&altadd=true&labid=20378&lod=true

 

Als voorbereiding dient een opschoningsscript te worden uitgevoerd. Download deze via onderstaande link en laad deze in, in SMSS, en voer de query uit.

 

http://www.gierveld.eu/OpschoonQuery.sql

 

 

OPDRACHT 1

 

Het afgelopen jaar is goed verlopen; de cijfers waren dik in de plus en er is weer ruimte voor salarisverhogen vertelt je manager. Voordat hij gaat bepalen hoeveel ruimte er is voor verhoging wil hij graag weten wat de totale som is van het salaris van alle werkgevers. Indien de som van alle salarissen boven de 1.8 miljoen liggen dan gaat het feest niet door. Aan jou wordt gevraagd dit uit te zoeken.

 

Er zijn drie tabellen die je nodig hebt om aan de gegevens te komen:

 

 

 

 

 

 

 

 

 

 

In de tabel Person.Person staan alle personen betrokken bij de organisatie: werknemers, leveranciers, klanten. Wij zijn alleen geïnteresseerd in de werknemers. Daarom moeten we de tabel koppelen aan HumanResources.Employee door middel van een inner join. Op deze manier blijven alleen de werknemers over.

 

We gaan de query nu stapsgewijs opbouwen:

 

Stap 1:

 

We willen weten hoeveel werknemers we hebben. Dit kunnen we doen door alles op te vragen uit een bepaalde tabel (met het ‘smerige’ commando select * from [tabel]. Welke tabel moeten het aantal werknemers uit halen? Voer deze query in SMSS in. Met F5 voer je de query uit.

 

Stap 2:

 

Het is goed om tabelnamen een afkorting te geven. Dit kun je doen door as [afkorting] achter de tabelnaam te zetten. In dit geval voeg je in je query toe: from [tabel] as he

 

Stap 3:

 

We gaan nu de salarisgegevens koppelen, want deze staan in een aparte tabel. Je kunt dit doen door een join-regel toe te voegen.

 

Join [tabel] as afkorting on sleutelveld tabel 1 = sleutelveld tabel 2

 

Als je de query nu opnieuw uitvoert zal je ook de salarisgegevens aan de rechterkant erbij zien komen. (Controleer of het aantal regels niet veranderd is)

 

Stap 4:

 

We zijn op dit moment alleen geïnteresseerd in het salaris. Daarom veranderen we de * in [afkorting_tabel].rate. Als je de query nu opnieuw uitvoert zal je alleen de salarisgegevens zien.

 

Stap 5:

 

Je ziet dat de salarisgegevens nog moeten vermenigvuldigd met 1000. Je kunt dit doen door * 1000 achter [afkorting_tabel].rate te zetten. Voer nu de query opnieuw uit.

 

Omdat je nu een berekende kolom hebt gemaakt zie je geen kolomnaam meer. Je kunt zelf een kolomnaam toevoegen door as Salaris (of een willekeurige andere naam) er achter te voegen. Voer de query nogmaals uit om dit te controleren.

 

Stap 6:

 

We kunnen nu het totaal gaan berekenen. Dit doen we door een rekenfunctie in SQL te gebruiken. Verander in het select-statement je huidige reken door:

 

select sum([afkorting_tabel].rate * 1000)

 

Als je nu de query nog een keer uitvoert krijg je 1 regel terug. Welk antwoord kun je je manager geven?

 

 

OPDRACHT 2

 

Het blijkt de manager bij nader inzien niet uit te maken wat de som van alle salarissen zijn. Het is tijd voor een salarisverhoging! Elke werknemer krijgt een salarisverhoging van 5%. Omdat dit een grote verandering is wil de manager graag een overzicht van de nieuwe salarissen in Excel. Hij wil daarin de volgende kolommen in terugzien: Voornaam, Achternaam, Functietitel, Salaris.

 

Ook nu gaan we de query weer stapsgewijs opbouwen:

 

Stap 1:

 

Je kunt verder werken met de huidige query. We moeten alleen een derde tabel gaan toevoegen voor de voornaam en achternaam. Dit kunnen we doen door nog een join te gaan toevoegen. Voer onderaan de huidige query een nieuwe join toe waarmee je de tabel person.person toevoegt en voer de query opnieuw uit.

 

Stap 2:

 

Je ziet dat er niets veranderd is, maar op de achtergrond kun je nu wel gegevens uit deze tabel toevoegen. Voer nu in het select-statement de kolommen in die je vanuit de instructie van de manager nodig hebt. Voer de query uit om te controleren of er wordt teruggegeven wat gevraagd is.

 

Stap 3:

 

De volgende stap is om van onze query een soort van tijdelijke tabel in het geheugen te maken. Dit wordt een 'common table expression' genoemd. Deze tabel gaan we gebruiken om het salaris te kunnen verhogen. Je maakt deze door om de query de volgende code toe te voegen:

 

With tijdelijke_tabelnaam as

(

Query

)

 

Hieronder voeg je het statement:

 

Select * from tijdelijke_tabelnaam

 

Om te kijken of je dezelfde gegevens weer terugkrijgt.

 

Stap 4:

 

Om te updaten zetten we het select-statement onder de tijdelijke tabel tijdelijk als commentaar door er ‘—‘ voor te zetten en voegen we toe:

 

Update tijdelijke_tabelnaam

 

Set Rate = Rate * 1.05

 

Hiermee verhogen we het salaris met 5%

 

Stap 5:

 

Markeer nu het update-gedeelte als commentaar en maak het select-statement weer actief door ‘—‘ weg te halen. Controleer of de update heeft gewerkt.

 

Stap 6:

 

Verwijder alle code behalve de originele query en sla deze op.

 

Stap 7:

 

Klik nu aan de linkerkant op de ‘Adventureworks20012’ database met de rechtermuisknop en kies vervolgens ‘Tasks’ – ‘Export Data’.

 

Kies Next

 

Kies als Data Source, SQL Server Native Client 11.0 en als Database Adventureworks2012

 

Kies Next

 

Kies als Destination, Microsoft Excel, daarna Next

 

Kies ‘Write a query to specifiy the data to transfer

 

Kies Browser en selecteer vervolgens de query die je hebt opgeslagen

 

Kies vervolgens 3x Next

 

Kies ‘Run script immediately’

 

Het Excel-bestand is nu aangemaakt door sql.

 

 

OPDRACHT 3

 

Een nieuwe medewerker begint vanaf aanstaande maandag en hij moet als gebruiker worden toegevoegd aan de database. Omdat het een collega van T&P betreft dient hij alle rechten te krijgen die een databasebeheerder kan hebben. Jij moet de betreffende gebruiker toevoegen als gebruiker.

 

Stap 1:

 

Kies aan de linkerkant voor ‘Security’ en vervolgens ‘Logins’

 

Stap 2:

 

Klik met de rechtermuisknop op ‘Logins’ en vervolgens voor ‘New login’

 

Stap 3:

 

Kies vervolgens op het tabblad ‘general’ voor SQL Server authentication, kies een willekeurige naam en een sterk wachtwoord. Vink ‘User must change password at next login' uit

 

Stap 4:

 

Ga vervolgens naar het tabblad server roles.

 

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles

 

Lees vervolgens bovenstaande link om te bepalen welke server role(s) moet(en) worden toegevoegd.

 

Stap 5:

 

Ga daarna terug naar het tabblad General en kies voor Script.

 

Stap 6:

 

Inspecteer het script en voer dit vervolgens uit.

 

Stap 7:

 

Sluit vervolgens SMSS af en start deze opnieuw op.

 

Stap 8:

 

Log in met de aangemaakte login.