- B
- A
- C
- D
- B
- A
- A
- D
- A
- C
Opdrachten
SELECT CONCAT(first_name
, ‘ ‘, last_name
) AS naam
,
CONCAT(LOWER(SUBSTR(first_name
, 1, 1)), ‘.’, LOWER(REPLACE(last_name
, ‘ ‘, ‘’)), ‘@company.com’) AS email
FROM employees
;
SELECT job_title
AS old
,
REPLACE(job_title
, ‘Manager’, ‘Director’) AS new
FROM jobs
WHERE job_title
LIKE ‘%Manager%’;
SELECT CONCAT(first_name
, ‘ ‘, last_name
) AS naam
,
LOWER(CONCAT(SUBSTR(first_name
, 1, 1), SUBSTR(last_name
, 1, 1))) AS inlognaam
FROM employees
ORDER BY last_name
;
SELECT CASE department_id
WHEN NULL THEN ‘Management’
WHEN 10 THEN ‘Administration’
WHEN 20 THEN ‘Marketing’
WHEN 50 THEN ‘Shipping’
WHEN 60 THEN ‘IT’
WHEN 80 THEN ‘Sales’
WHEN 90 THEN ‘Executive’
WHEN 110 THEN ‘Accounting’
WHEN 190 THEN ‘Contracting’
END AS afdeling
,
ROUND(AVG(salary
), 2) AS gemiddeld salaris
,
SUM(salary
) AS totaal salaris
,
COUNT(employee_id
) AS aantal personeelsleden
FROM employees
GROUP BY department_id
;
SELECT CASE department_id
WHEN NULL THEN ‘Management’
WHEN 10 THEN ‘Administration’
WHEN 20 THEN ‘Marketing’
WHEN 50 THEN ‘Shipping’
WHEN 60 THEN ‘IT’
WHEN 80 THEN ‘Sales’
WHEN 90 THEN ‘Executive’
WHEN 110 THEN ‘Accounting’
WHEN 190 THEN ‘Contracting’
ELSE ‘Geen afdeling’
END AS afdeling
,
CONCAT(‘$’, ROUND(AVG(((3 * salary
)/13)/40), 2)) AS uurloon
FROM employees
WHERE last_name
!= ‘King’
GROUP BY department_id
;
SELECT CONCAT(SUBSTR(first_name
, 1, 1), ‘. ‘, last_name
) AS personeelslid
,
DATE_FORMAT(hire_date
, ‘%a %e %b %Y’) AS datum in dienst
,
TIMESTAMPDIFF(YEAR, hire_date
, NOW()) AS jaar in dienst
FROM employees
WHERE MONTH(hire_date
) = 1
ORDER BY jaar in dienst
DESC;