Für eine Abteilung wird der Name (NAME) und eine eindeutige Kennnummer (KNR) gespeichert. In einer Abteilung arbeiten verschiedene Teams an verschiedenen Projekten. Ein Team setzt sich aus mehreren Mitarbeitern zusammen, hier soll gespeichert werden seit wann (SEIT) der Mitarbeiter in dem Team ist. Das Team ist durch eine eindeutige Teamnummer (TEAMNR) gekennzeichnet und hat auch einen Teamnamen (NAME). Ein Mitarbeiter hat eine eindeutige Personalnummer (PNR), einen Namen (NAME), er hat eine Wohnbezirk (BEZIRK), sowie eine Durchwahl (DURCHWAHL), ein Gehalt (GEHALT) und ein Eintrittsdatum (SEIT). Eine Abteilung wird von genau einem Mitarbeiter geleitet, das gleiche gilt für ein Team ? hier wird genau ein Teamleiter festgelegt. Jeder Mitarbeiter kann in mehreren Teams mitwirken. Weiters ist anzugeben, wer der generelle Vorgesetzte eines Mitarbeiters ist. Es gibt auch Schulungen, die ein Mitarbeiter besuchen kann. Eine Schulung ist eindeutig identifizierbar durch das Thema (THEMA) und das Datum (DATUM) an dem sie stattfindet. Außerdem wird auch ein interner Preis (PREIS) gespeichert. Es soll bekannt sein, welche Mitarbeiter welche Schulungen besucht haben. Ein Projekt wird durch seine Projektnummer identifiziert (PNR), es hat einen Namen (NAME), eine Durchführungssprache (SPRACHE) als auch ein Projektbudget (BUDGET), das zur Verfügung steht. Weiters gibt es auch Sonderprojekte, zu welchen zusätzlich der Sponsor (SPONSOR) gespeichert wird. Projekte werden für verschiedene Länder durchgeführt. z.B. Das Projekt Maut4712 wird für Belgien, Russland und Australien realisiert. Ein Land wird durch den Ländercode (LCODE) identifiziert, die Anzahl von Mautstraßen (MANZ) und eine festgelegte Verhandlungssprache (SPRACHE). Es soll weiters gespeichert werden, für welche Projekte spezielle Mitarbeiter verschiedene Aufgabenbereiche übernehmen. Ein Aufgabenbereit wird durch seine Bezeichnung (BEZEICHNUNG) identifiziert. Aus strategischen Gründen möchte die Führungsebene die neue Datenbank nutzen, um die Meetingkultur ein wenig verbessern. Dazu ist es notwendig, projektspezifische Meetings in die Datenbank aufzunehmen. Hierfür werden Meetings gespeichert, die durch eine Meetingnummer (MNR) identifiziert werden, ein Datum (DATUM), und die Zeitspanne (VON), (BIS) werden ebenso vermerkt. Weiters soll es optional möglich sein, einen Link (ULINK) zu weiteren Unterlagen anzugeben, damit diese später leichter aufzufinden sind. Ein Meeting wird immer nur für ein bestimmtes Projekt abgehalten.
1)
Geben Sie alle Daten eines Mitarbeiters aus, der im 3. Bezirk wohnhaft ist und sortieren Sie diese nach Namen aufsteigend. Die Postleitzahl des 3. Bezirkes ist 1030.
SELECT * FROM MITARBEITER WHERE BEZIRK = 1030 ORDER BY NAME ASC;
2)
Geben Sie die den Namen, Wohnbezirk und das Eintrittsdatum aller Mitarbeiter aus, die vor dem 1.1.2000 Mitarbeiter der Firma waren, zusammen mit der Anzahl an Meetings an denen sie teilgenommen haben. Verwenden Sie hierzu die Funktion TO_DATE mit dem Datumsformat 'DD-MM-YY'.
SELECT m.NAME, m.BEZIRK, m.SEIT, count(*) as ANZAHL
FROM MITARBEITER m JOIN NIMMT_TEIL n on n.MITARBEITER = m.PNR
WHERE m.SEIT < TO_DATE('01-01-2000', 'DD-MM-YYYY')
group by m.PNR, m.NAME, m.BEZIRK, m.SEIT;
3.1)
Geben sie das niedrigste Gehalt in der ganzen Firma aus.
SELECT MIN(GEHALT) as GEHALT FROM MITARBEITER;
3.2)
Geben Sie den Namen, das Eintrittsdatum sowie das Gehalt jenen Mitarbeiters aus, der das niedrigste Gehalt verdient.
SELECT NAME, SEIT, GEHALT FROM MITARBEITER WHERE GEHALT = (SELECT MIN(GEHALT) FROM MITARBEITER);
SELECT NAME, SEIT, GEHALT FROM MITARBEITER WHERE GEHALT <= ALL (SELECT gehalt FROM MITARBEITER);
SELECT m.NAME, m.SEIT, m.GEHALT FROM MITARBEITER m WHERE NOT EXISTS (SELECT * FROM MITARBEITER WHERE gehalt<m.gehalt);
3.3)
Geben alle Mitarbeiter aus dem Bezirk mit der PLZ 1010 mit dem geringsten Gehalt aus.
SELECT NAME, SEIT, GEHALT FROM MITARBEITER WHERE BEZIRK = 1010 AND gehalt <= ALL (SELECT GEHALT FROM MITARBEITER WHERE BEZIRK = 1010)
SELECT NAME, SEIT, GEHALT FROM MITARBEITER WHERE BEZIRK = 1010 AND gehalt = (SELECT min(GEHALT) FROM MITARBEITER WHERE BEZIRK = 1010);
SELECT m.NAME, m.SEIT, m.GEHALT FROM MITARBEITER m WHERE m.BEZIRK = 1010 AND NOT EXISTS(SELECT * FROM MITARBEITER WHERE BEZIRK = 1010 AND gehalt<m.gehalt);
4.1)
Geben Sie den Namen und das Gehalt jener Mitarbeiter aus, die mehr verdienen als der Durchschnitt, aber weder eine Abteilung noch ein Team leiten und auch nicht in einem Projekt mitarbeiten.
SELECT NAME, GEHALT FROM MITARBEITER WHERE GEHALT > (SELECT AVG(GEHALT) FROM MITARBEITER) AND PNR NOT in (SELECT LEITER FROM TEAM) AND PNR NOT in (SELECT LEITER FROM ABTEILUNG) AND PNR NOT in (SELECT MITARBEITER FROM HAT_IN);
4.2)
Geben Sie den Namen und das Gehalt aller Mitarbeiter an, die noch nie eine Schulung besucht haben aber ein Team leiten und schon in einem Projekt mitgearbeitet haben.
SELECT NAME, GEHALT FROM MITARBEITER WHERE MITARBEITER.PNR NOT IN (SELECT MITARBEITER FROM BESUCHT) AND MITARBEITER.PNR IN (SELECT LEITER FROM TEAM) AND MITARBEITER.PNR IN (SELECT MITARBEITER FROM HAT_IN);
5.1)
Geben Sie die Projektnummer und den Sponsor aller Sonderprojekte an, die in allen Laendern umgesetzt werden.
SELECT s.PNR, s.SPONSOR FROM SONDERPROJEKT s WHERE NOT EXISTS( SELECT * FROM LAND WHERE LCODE NOT IN (SELECT LCODE from PRJ_LAND WHERE PRJ_LAND.PNR = s.PNR));
SELECT PNR, SPONSOR FROM SONDERPROJEKT NATURAL JOIN PRJ_LAND GROUP BY PNR, SPONSOR HAVING COUNT(*)>= (SELECT count(*) FROM LAND);
5.2)
Geben Sie die Projektnummer und den Namen aller Projekte an, die in allen Laendern mit der Sprache Deutsch umgesetzt werden.
SELECT p.PNR, p.NAME FROM PROJEKT p WHERE NOT EXISTS( SELECT * FROM LAND WHERE SPRACHE='Deutsch' AND LCODE NOT IN (SELECT LCODE from PRJ_LAND WHERE PRJ_LAND.LCODE = LAND.LCODE AND PRJ_LAND.PNR = p.PNR));
SELECT t.PNR, t.NAME FROM (Select p.pnr as PNR, p.name as NAME, pl.lcode as LCODE from projekt p join prj_land pl on p.pnr = pl.pnr) t JOIN LAND l on l.LCODE=t.LCODE WHERE l.SPRACHE='Deutsch' GROUP BY PNR, NAME HAVING COUNT(*)>= (SELECT count(*) FROM LAND WHERE SPRACHE='Deutsch')
6.1)
Geben Sie die Personalnummer der Mitarbeiter zusammen mit den Projekten (NAME), in denen Sie arbeiten, aus.
SELECT h.MITARBEITER, p.NAME FROM HAT_IN h JOIN PROJEKT p on h.PROJEKT=p.PNR
6.2)
Geben Sie ALLE Mitarbeiter (PNR und NAME) aus zusammen mit den Projekten (PNR) in denen sie mitwirken, sowie die dazugehoerigen Aufgabenbereiche.
SELECT m.PNR, m.NAME, h.PROJEKT, h.AUFGABENBEREICH FROM MITARBEITER M LEFT OUTER JOIN HAT_IN h on m.PNR=h.MITARBEITER
6.3)
Geben Sie ALLE Mitarbeiter (PNR und NAME) aus zusammen mit den großen Projekten (PNR), in denen sie mitwirken, sowie die dazugehoerigen Aufgabenbereiche. Große Projekte sind Projekte mit einem Budget ueber 10.000 Euro. Falls ein Mitarbeiter bei keinem Projekt mitarbeitet, soll stattdessen 0 sowie 'kein Aufgabenbereich' ausgegeben werden. (Verwenden Sie dazu die Oracle Funktion COALESCE)
SELECT m.PNR, m.NAME, COALESCE(t.pnr, 0) as PROJEKT, COALESCE(t.AUFGABENBEREICH, 'kein Aufgabenbereich') as AUFGABE FROM MITARBEITER M LEFT OUTER JOIN (Select * from HAT_IN h join PROJEKT p on h.PROJEKT=p.PNR where p.budget>10000) t on m.PNR=t.MITARBEITER;
select m.pnr, m.name, coalesce(h.projekt,0) as PROJEKT, coalesce(h.aufgabenbereich, 'kein Aufgabenbereich') as Aufgabenbereich from mitarbeiter m left outer join hat_in h on h.mitarbeiter=m.pnr left outer join projekt p on p.pnr=h.projekt where p.budget>10000 or p.budget is null;
7)
Geben Sie den Name, Bezirk und Durchwahl jener Mitarbeiter aus, die ein Gehalt kleiner als 1800 haben aber an den meisten Projekten mitarbeiten.
SELECT NAME, BEZIRK, DURCHWAHL FROM MITARBEITER LEFT JOIN HAT_IN ON HAT_IN.MITARBEITER = MITARBEITER.PNR WHERE GEHALT < 1800 GROUP BY PNR, NAME, BEZIRK, DURCHWAHL HAVING COUNT (DISTINCT PROJEKT) >= ALL(SELECT COUNT (DISTINCT PROJEKT) FROM MITARBEITER LEFT JOIN HAT_IN ON HAT_IN.MITARBEITER = MITARBEITER.PNR WHERE GEHALT < 1800 GROUP BY PNR);
SELECT NAME, BEZIRK, DURCHWAHL FROM MITARBEITER LEFT JOIN HAT_IN ON HAT_IN.MITARBEITER = MITARBEITER.PNR WHERE GEHALT < 1800 GROUP BY PNR, NAME, BEZIRK, DURCHWAHL HAVING COUNT (DISTINCT PROJEKT) = (SELECT max(anzahl) from( SELECT COUNT (DISTINCT PROJEKT) as Anzahl FROM MITARBEITER LEFT JOIN HAT_IN ON HAT_IN.MITARBEITER = MITARBEITER.PNR WHERE GEHALT < 1800 GROUP BY PNR));
8)
Geben Sie die Namen der Teams aus zusammen mit der Anzahl an Projekten in denen sie arbeiten und der Anzahl der Mitarbeiter die fuer das Team arbeiten.
SELECT t.NAME, count(*) AS PROJEKTE, (SELECT COUNT(*) FROM MA_TEAM WHERE TEAM=t.TEAMNR) AS MITARBEITER FROM TEAM t join ARBEITET_AN a on t.TEAMNR=a.TEAM GROUP by t.TEAMNR, t.NAME;
9)
Geben Sie die Projektnummer jener Projekte mit den meisten Mitarbeitern aus, sowie die Anzahl der Mitarbeiter, die an diesem Projekt beteiligt sind.
SELECT PROJEKT, COUNT(distinct HAT_IN.mitarbeiter) AS ANZAHL
FROM HAT_IN
GROUP BY PROJEKT
HAVING COUNT(distinct mitarbeiter) >= ALL (SELECT COUNT(distinct mitarbeiter)
FROM HAT_IN
GROUP BY PROJEKT);
10)
Geben Sie die vorhandenen Daten (mnr, datum, von, bis und falls vorhanden den Link zu den Unterlagen) fuer ALLE Meetings an, deren Beginn und Endzeit zwischen 09:00 und 13:00 Uhr liegen, oder die nach dem 01.02.13 stattgefunden haben. Verwenden Sie hierzu die Funktion TO_DATE mit dem Datumsformat 'DD-MM-YY'.
SELECT MEETING.MNR, MEETING.DATUM, MEETING.VON, MEETING.BIS, UNTERLAGE.ULINK
FROM MEETING LEFT OUTER JOIN UNTERLAGE ON MEETING.MNR = UNTERLAGE.MNR
WHERE (MEETING.VON >= '09:00' and MEETING.BIS <= '13:00') or (MEETING.DATUM >= to_date('01.02.13', 'DD-MM-YY'));