Lösungen Aufgaben 5 alteSemesterprüfung

[tobru]

Lösungen zu Aufgabe 5 von alteSemesterprüfungen.pdf (website)

5.1

Code:

SELECT Name, Funktion, sal as Gehalt FROM t_mitarbeiter WHERE Funktion NOT LIKE 'Inhaber' ORDER BY sal DESC LIMIT 3

5.2
SELECT ma.name as Name, abt.name as Abteilung, ma.sal as Gehalt
FROM t_mitarbeiter as ma, t_abteilung as abt
WHERE ma.abt = abt.abt_nr
    AND ma.funktion != 'Inhaber'
ORDER BY Gehalt DESC
LIMIT 3

5.3

Code:

SELECT ma.name as Name, ma.funktion as Funktion, DATE_FORMAT(ma.anstellung,'%e. %M %Y') as Eintritt
FROM t_mitarbeiter as ma, t_abteilung as abt
WHERE ma.abt = abt.abt_nr
    AND abt.abt_nr = 10
    AND ma.anstellung > FROM_DAYS(TO_DAYS(CURDATE())-365)

5.4


5.5

Code:

SELECT proj.Name as Projekt, COUNT(ma.name) as Mitarbeiter
FROM t_projekt as proj LEFT OUTER JOIN t_arbeitet_an as arban
    ON arban.prj_nr = proj.prj_nr LEFT OUTER JOIN t_mitarbeiter as ma
    ON arban.ma_nr = ma.ma_nr
GROUP BY proj.Name
ORDER BY Mitarbeiter

5.6

Code:

SELECT ma.name Name, ma.funktion Funktion, COUNT(*) as Projekte
FROM t_mitarbeiter as ma, t_abteilung as abt, t_projekt as proj, t_arbeitet_an as arban
WHERE ma.abt = abt.abt_nr
    AND arban.ma_nr = ma.ma_nr
    AND arban.prj_nr = proj.prj_nr
GROUP BY ma.name HAVING COUNT(*) > 1

5.7

Code:

SELECT ma.name Name, ma.funktion Funktion,
    CASE
        WHEN ma.sal > 100000 THEN 4
        WHEN ma.sal <= 100000 AND ma.sal > 90000 THEN 3
        WHEN ma.sal <= 90000 AND ma.sal > 80000 THEN 2
        WHEN ma.sal <= 80000 AND ma.sal > 70000 THEN 1
        WHEN ma.sal <= 70000 THEN 0
    END as Klasse
FROM t_mitarbeiter as ma
ORDER BY Klasse DESC