SQL oefentoets met uitleg

 avatar
unknown
plain_text
a month ago
6.9 kB
5
Indexable
Vraag 1 (10 punten)

Question:
Geef de query om tot een lijst van medewerkers te komen die tussen de 20 en 30 jaar oud zijn. Geef de achternaam gevolgd door een komma en vervolgens het tussenvoegsel en de voornaam (gescheiden door een spatie). Limiteer op 5, aflopend gesorteerd op geboortemaand.

Answer:

sql
Copy code
SELECT voornaam || CASE WHEN tussenvoegsel IS NULL THEN '' ELSE ' ' || tussenvoegsel || ' ' END || achternaam AS naam, geboortedatum
FROM medewerkers
WHERE geboortedatum > '1994-12-31'
ORDER BY geboortedatum DESC
LIMIT 10;
Explanation:

The SELECT statement combines the voornaam, tussenvoegsel, and achternaam fields into a single field using string concatenation (||).
The CASE statement is used to check whether tussenvoegsel is NULL. If it is, no space is added; otherwise, the space and the tussenvoegsel are included.
The WHERE clause filters employees born after December 31, 1994 (meaning between 20 and 30 years old).
The results are ordered by the geboortedatum in descending order, meaning the most recent birthdays are at the top.
The LIMIT clause restricts the result to the top 5 records.

Vraag 2 (20 punten)

Question:
Geef de brouwerijnaam en het maximum promillage (van alle bieren van die brouwer) per brouwer van alle brouwers die een vestiging hebben in ‘Germany’ zonder het woord ‘Bit’ of het woord ‘Brauerei’in de brouwerijnaam.

Answer:

sql
Copy code
SELECT brouwerijen.naam, MAX(promillage)  
FROM brouwerijen, vestigingen, bieren  
WHERE bieren.vestiging = vestigingen.vestigings_id 
  AND vestigingen.brouwerij = brouwerij_id  
  AND vestigingen.land = "Germany"  
  AND brouwerijen.naam NOT IN (SELECT brouwerijen.naam FROM brouwerijen WHERE brouwerijen.naam LIKE "%Bit%" 
                                UNION 
                                SELECT brouwerijen.naam FROM brouwerijen WHERE brouwerijen.naam LIKE "%Brauerei%") 
GROUP BY brouwerijen.naam
LIMIT 10;
Explanation:

The query joins the brouwerijen, vestigingen, and bieren tables to retrieve the brewery names and their maximum beer alcohol content (promillage).
The WHERE clause filters the breweries that are located in Germany and ensures that the name doesn't contain "Bit" or "Brauerei" (using NOT LIKE with a wildcard).
GROUP BY groups the results by brewery name and calculates the MAX(promillage) for each group.
The LIMIT 10 limits the output to the top 10 results.

Vraag 3 (20 punten)
Question:
Geef van alle leidinggevenden de achternaam en alle medewerkernummers (id), medewerkersnaam en vestigingsnummer van de mensen waar ze leiding aan geven. Sorteer op vestiging en manager.

Answer:

sql
Copy code
SELECT m2.achternaam AS 'manager', m1.achternaam AS mdwnaam, m1.medewerker_id AS id, m1.vestiging
FROM medewerkers m1, medewerkers m2, vestigingen v
WHERE m1.manager = m2.medewerker_id 
  AND m1.vestiging = v.vestigings_id 
ORDER BY m1.vestiging, manager;
Explanation:

The query retrieves managers (m2) and the employees (m1) they manage.
The WHERE clause ensures that only employees with a valid manager (matching m1.manager to m2.medewerker_id) are selected.
The result is sorted by the vestiging (location) and the manager (manager’s last name).

Vraag 4 (20 punten)
Question:
Geef de query om van alle vestigingen, de vestigings_id en leeftijd van de oudste persoon die op het moment van de toets op die vestiging werkt. Limiteer op 10, sorteer op leeftijd.

Answer:

sql
Copy code
SELECT vestigings_id, MAX(strftime('%Y', 'now') - strftime('%Y', geboortedatum) - (strftime('%m-%d', 'now') < strftime('%m-%d', geboortedatum))) AS leeftijd 
FROM vestigingen, medewerkers
WHERE vestigings_id = medewerkers.vestiging
GROUP BY vestiging 
ORDER BY leeftijd DESC 
LIMIT 10;
Explanation:

The query uses strftime('%Y', 'now') to calculate the current year and subtracts the year of birth (geboortedatum) from it.
To adjust for birthdays that haven't occurred yet this year, a comparison is made between the current date and the birth date.
The result is grouped by vestiging (location) and the oldest age (MAX(leeftijd)) for each location is selected.
Finally, the results are ordered by leeftijd in descending order, showing the oldest first, and limited to the top 10.

Vraag 5 (15 punten)
Question:
We gaan de database uitbreiden met de tabel Ketels. Deze hebben de volgende eigenschappen:

Eén bier kan in één of meer Ketels gebrouwen worden op een bepaald tijdstip.
Lever de queries aan om het volgende voor elkaar te krijgen:
Maak een tabel ketels aan. Aan de tabel ketels hang je een primary key van het type int en maak je kolommen aan voor: Keteltype (hierin staat van welk materiaal de ketel is gemaakt), aantal liters inhoud (hierin staat hoeveel liter er in de ketel kan), de datum en begintijdstip van brouwen. Geef ook de queries voor het vullen van tabel met de volgende ketelsoorten bij alle vestigingen van de brouwerij ‘Kaapse Brouwers’ met de drie keteltypes: Koper, RVS, Titanium, en alle drie in de maten 100 liter.
Answer:

sql
Copy code
CREATE TABLE ketels (
    ketel_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    keteltype TEXT,  
    ketelinhoud INT, 
    brouwtijdstip DATETIME,
    bier_id INT,
    vestigings_id INT,
    FOREIGN KEY (bier_id) REFERENCES bieren(bier_id),
    FOREIGN KEY (vestigings_id) REFERENCES vestigingen(vestigings_id)
);

INSERT INTO ketels (keteltype, ketelinhoud, vestigings_id) VALUES ('koper', 100, 1424);
INSERT INTO ketels (keteltype, ketelinhoud, vestigings_id) VALUES ('RVS', 100, (SELECT vestigings_id FROM brouwerijen, vestigingen WHERE naam = 'Kaapse Brouwers' AND brouwerijen.brouwerij_id = vestigingen.brouwerij));
INSERT INTO ketels (keteltype, ketelinhoud, vestigings_id) VALUES ('staal', 100, (SELECT vestigings_id FROM brouwerijen, vestigingen WHERE naam = 'Kaapse Brouwers' AND brouwerijen.brouwerij_id = vestigingen.brouwerij));
Explanation:

The CREATE TABLE statement defines the ketels table with columns for the ketel_id (auto-incrementing primary key), keteltype (material), ketelinhoud (capacity), and brouwtijdstip (brewing time).
It also links the bier_id and vestigings_id as foreign keys to the bieren and vestigingen tables respectively.
The INSERT INTO statements add three different types of kettles (Copper, Stainless Steel, Steel) at various brewery locations.

Vraag 6 (5 punten)
Question:
Maak een controle op database niveau (“check”) zodat alleen mensen van 18 jaar en ouder ingevoerd kunnen worden als medewerker.

Answer:

sql
Copy code
ALTER TABLE medewerkers ADD CHECK (strftime('%Y', 'now') - strftime('%Y', geboortedatum) >= 18);
Explanation:

This query adds a CHECK constraint to the medewerkers table to ensure that only employees who are 18 years or older can be added.
It uses strftime('%Y', 'now') to get the current year and compares it with the year of birth (geboortedatum) to ensure that the person is at least 18 years old.
Leave a Comment