SQL herhaling Flashcards
SELECT … FROM … WHERE: Retrieve specific columns from a table based on a condition. Write a query to select the naam (name) column from the medewerker (employee) table where the functie (job title) is ‘TRAINER’.
SELECT naam
FROM medewerker
WHERE functie = ‘TRAINER’;
JOIN: Combine rows from two or more tables based on a related column. Write a query to retrieve the naam (name) from the medewerker table and join it with the inschrijving (enrollment) table on the corresponding id_medewerker and id_deelnemer columns.
SELECT medewerker.naam
FROM medewerker
INNER JOIN inschrijving ON medewerker.id_medewerker = inschrijving.id_deelnemer;
ORDER BY: Sort the result set by one or more columns. Write a query to select maandsalaris (monthly salary) from medewerker and sort the results in descending order.
SELECT maandsalaris
FROM medewerker
ORDER BY maandsalaris DESC;
GROUP BY: Group rows that have the same values in specified columns into summary rows. Write a query to select id_afdeling (department ID) and count the number of employees in each department from the medewerker table, grouping by id_afdeling.
SELECT id_afdeling, COUNT(*)
FROM medewerker
GROUP BY id_afdeling;
WHERE … LIKE: Filter results based on a pattern. Write a query to select the omschrijving (description) from the cursus table where the omschrijving starts with ‘Java’.
SELECT omschrijving
FROM cursus
WHERE omschrijving LIKE ‘Java%’;
Aggregate Functions (MIN, MAX, AVG, COUNT, SUM): Perform calculations on a set of values to return a single value. Write a query to find the maximum maandsalaris (monthly salary) from the medewerker table.
SELECT MAX(maandsalaris)
FROM medewerker;
LIMIT: Restrict the number of rows returned by a query. Write a query to select the naam from the medewerker table, ordered by maandsalaris, and limit the result to the top 1 row.
SELECT naam
FROM medewerker
ORDER BY maandsalaris DESC
LIMIT 1;
DISTINCT: Retrieve only unique values. Write a query to select all the unique functie (job title) from the medewerker table.
SELECT DISTINCT functie
FROM medewerker;
LEFT JOIN (LEFT OUTER JOIN): Retrieve all rows from the left table and the matching rows from the right table. If there is no match in the right table, the result is NULL on the right side. Write a query to select omschrijving from cursus and id_deelnemer from inschrijving showing all courses even if there are no enrollments.
SELECT omschrijving, id_deelnemer
FROM cursus
LEFT JOIN inschrijving ON cursus.code = inschrijving.id_cursus;
Subquery: A query nested inside another query. Write a query to select naam from medewerker where their id_medewerker is in the result of a subquery that selects id_deelnemer from inschrijving for id_cursus = ‘S02’
SELECT naam
FROM medewerker
WHERE id_medewerker IN (SELECT id_deelnemer FROM inschrijving WHERE id_cursus = ‘S02’);
String Concatenation: Combine two or more strings into one. Write a query to concatenate naam (last name) and initialen_voornaam (initials) from the medewerker table with a space in between, aliasing the column as volledige_naam. Use || operator.
SELECT naam || ‘ ‘ || initialen_voornaam AS volledige_naam
FROM medewerker;
Date Functions (EXTRACT, AGE): Extract parts of a date or calculate the difference between dates. Write a query to extract the year from the geboortedatum (birthdate) of employees and calculate the age of each employee, aliasing the column as leeftijd. Use EXTRACT function.
SELECT EXTRACT(YEAR FROM AGE(NOW(), geboortedatum)) AS leeftijd
FROM medewerker;
HAVING: Filter groups based on a condition after grouping with GROUP BY. Write a query to select id_cursus and calculate the average evaluatie from inschrijving, grouping by id_cursus, and only including groups where the average evaluation is greater than 3.
SELECT id_cursus, AVG(evaluatie)
FROM inschrijving
GROUP BY id_cursus
HAVING AVG(evaluatie) > 3;
Casting (::): Convert a value from one data type to another. Write a query to select maandsalaris from medewerker and cast it to NUMERIC(10, 2) to format it with two decimal places.
SELECT maandsalaris::NUMERIC(10, 2)
FROM medewerker;
