ZFSL/MS/velo2000

Wiki-Quelltext dieser Seite.

<language>SQL</language>
Für die Mitarbeiterverwaltung der Velo2000 GmbH liegt die folgende Datenbank vor:

[[Bild:Velo-relationenmodell.gif]]

[[Bild:Velo-tabellenstruktur.gif]]



<code id="53a2dc4695c30"  hidden>
Create Table abteilung (
AbteilungsID varchar(3),
Abteilungsbez varchar(50),
AbteilungsleiterID integer,
Primary key (AbteilungsID));

INSERT INTO abteilung (AbteilungsID,Abteilungsbez, AbteilungsleiterID) VALUES
('EDV', 'Elektonische Datenverarbeitung', 1060),
('EGF', 'Erweiterte Geschäftsführung', 1040),
('EK', 'Einkauf', 4020),
('GF', 'Geschäftsführung', 1001),
('LA', 'Lager', 4145),
('LW', 'Lehrwerkstatt', 3020),
('MF', 'Mechanische Fertigung', 3010),
('ORG', 'Organisation', 4060),
('PA', 'Personalangelegenheiten', 1050),
('RW', 'Rechnungswesen', 4040),
('TE', 'test', 222),
('VE', 'Vertrieb', 4065);
;



CREATE TABLE ort (
  PLZ varchar(5) NOT NULL,
  Ortsname varchar(50) NOT NULL,
  PRIMARY KEY (PLZ)
) ;


INSERT INTO ort (PLZ, Ortsname) VALUES
('45721', 'Haltern am See'),
('45768', 'Marl'),
('48143', 'Münster'),
('48155', 'Münster'),
('48301', 'Nottuln'),
('48329', 'Havixbeck'),
('48727', 'Billerbeck');

CREATE TABLE mitarbeiter (
  PersonalNr integer NOT NULL,
  Nachname varchar(30) NOT NULL,
  Vorname varchar(30) NOT NULL,
  Strasse varchar(80) NOT NULL,
  Hausnummer varchar(10) NOT NULL, 
  PLZ varchar(5) NOT NULL,
  istTarif integer NOT NULL,
  Geburtsdatum date NOT NULL,
  Eintrittsdatum date NOT NULL,
  Geschlecht varchar(1) NOT NULL,
  Steuerklasse integer NOT NULL,
  Monatsentgelt double NOT NULL,
  AbteilungsID varchar(3) NOT NULL,
PRIMARY KEY (PersonalNr)

);

INSERT INTO mitarbeiter (PersonalNr, Nachname, Vorname, Strasse, Hausnummer, PLZ, istTarif, Geburtsdatum, Eintrittsdatum, Geschlecht, Steuerklasse, Monatsentgelt,AbteilungsID) VALUES
(1001, 'Birkhohl', 'Rolf', 'Toni-Turek-Straße', '19', '48301', 0, '1958-09-07', '1990-01-01', 'm', 3, 6200,'GF'),
(1020, 'Birkhohl', 'Heinz ', 'Am Bagno', '2', '48301', 0, '1951-01-27', '1990-01-01', 'm', 1, 6200,'GF'),
(1030, 'Starke', 'Klaus', 'Am Bagno', '10', '48301', 0, '1946-08-02', '1990-01-01', 'm', 4, 4875,'EGF'),
(1040, 'Kunze', 'Patrick', 'Am Sportplatz', '32', '48727', 0, '1958-07-05', '1990-01-01', 'm', 3, 5160,'EGF'),
(1050, 'Jochmann', 'Silke', 'Olympiastraße', '22', '48301', 1, '1970-10-16', '2000-03-01', 'w', 4, 4380,'PA'),
(1060, 'Zimmer', 'Michael', 'Kolpingstraße', '18', '48301', 0, '1972-12-16', '1997-07-02', 'm', 3, 4750,'EDV'),
(2010, 'Kaes', 'Sarah', 'Carl-Diem-Ring', '3', '48301', 1, '1964-11-27', '1990-01-01', 'w', 1, 3312,'EK'),
(2020, 'Rose', 'Daniela', 'Mondstraße', '12', '48143', 1, '1960-12-29', '1998-10-01', 'w', 5, 3211,'RW'),
(2025, 'Hinze', 'Herbert', 'Schillerstraße', '23', '48143', 1, '1963-05-02', '1992-06-15', 'm', 3, 2915,'ORG'),
(2030, 'Zurni', 'Kevin', 'Hauptstraße', '33', '48329', 1, '1968-07-19', '1990-07-01', 'm', 3, 3204,'VE'),
(3010, 'Breke', 'Ernst', 'Hauptstraße', '34', '48329', 0, '1967-08-25', '2010-07-01', 'm', 3, 4558,'MF'),
(3020, 'Lätzler', 'Sabine', 'Grauten Ihl', '24', '48301', 1, '1973-05-20', '1990-08-01', 'w', 4, 3239,'LW'),
(3030, 'Kraus', 'Steffen', 'Krägerweg', '3', '48155', 1, '1964-09-13', '1991-07-01', 'm', 1, 2641,'LA'),
(4005, 'Griese', 'Anette', 'Rappaportstaße', '4', '45768', 1, '1969-03-27', '1992-01-01', 'w', 4, 2258,'EK'),
(4010, 'Kohn', 'Edgar', 'Recklinghäuser Straße', '28', '45721', 1, '1972-08-22', '1992-07-01', 'm', 3, 3482,'EK'),
(4015, 'Müller', 'Albrecht', 'Toni-Turek-Straße', '20', '48301', 1, '1973-12-25', '1993-01-01', 'm', 3, 2746,'EK'),
(4020, 'Beyer', 'Bernhard', 'Am Bagno', '18', '48301', 1, '1969-07-25', '1993-01-01', 'm', 3, 3954,'EK'),
(4025, 'Zachow', 'Barbara', 'Am Bagno', '26', '48301', 1, '1972-06-05', '1996-06-01', 'w', 3, 720,'EK'),
(4030, 'Ostendorf', 'Anne', 'Am Sportplatz', '33', '48727', 1, '1992-12-26', '2009-08-01', 'w', 1, 3207,'RW'),
(4035, 'Kramer', 'Alfred', 'Olympiastraße', '23', '48301', 1, '1960-02-20', '1994-02-01', 'm', 1, 3391,'RW'),
(4040, 'Elten', 'Eckhard', 'Kolpingstraße', '19', '48301', 1, '1962-04-09', '1994-03-14', 'm', 4, 3845,'RW'),
(4045, 'Krieg', 'Birgit', 'Carl-Diem-Ring', '4', '48301', 1, '1994-09-01', '2010-08-01', 'w', 1, 680,'RW'),
(4050, 'Blume', 'Dagmar', 'Mondstraße', '13', '48143', 1, '1976-12-01', '1995-08-01', 'w', 5, 3107,'ORG');

'''Orientierung in der Datenbank'''
Die InSoft GmbH hat die Personaldatenbank fristgerecht fertiggestellt und an den Auftrag-geber übergeben. Für die Verwaltung der Personaldatenbank bei der Velo2000 GmbH ist die Informatikkauffrau Petra Neuer zuständig. Zu ihrem Aufgabengebiet gehört unter anderem die Pflege der Stammdaten. 
Bevor sie ihre ersten Aufgaben erledigt, verschafft sie sich einen ersten Überblick über die Datenbank. 


</code>
Erstellen Sie die folgenden Abfragen:

'''Handlungssituation 2:'''

- alle Mitarbeiterinnen und Mitarbeiter

- die Personal-Nr und den Nachnamen aller Mitarbeiterinnen und Mitarbeiter

- alle Mitarbeiterinnen und Mitarbeiter der Abteilung Einkauf

- den Nach- und Vornamen sowie das Geburtsdatum alle weibl. Mitarbeiter

- alle Mitarbeiterinnen und Mitarbeiter  aus Haltern am See

- alle Mitarbeiterinnen und Mitarbeiter, deren Nachnamen mit „S“ beginnen

- alle Mitarbeiterinnen und Mitarbeiter aus dem PLZ-Bereich 45

- den Namen und das Monatsentgelt aller AT-Mitarbeiterinnen und Mitabteiter 

- alle männlichen Mitarbeiter, die mehr als 3000,00 Euro Monatsentgelt haben

- alle Mitarbeiter, die nach 1990 geboren sind

- alle Mitarbeiterinnen und Mitarbeiter, die seit dem 01.01.2005 im Unternehmen sind

- alle Mitarbeiter, die zwischen 1980 und 1999 geboren sind

- alle Mitarbeiterinnen und Mitarbeiter, deren Personalnummern mit 4 beginnen

- alle Mitarbeiterinnen und Mitarbeiter der Abteilungen EDV, LA und VE

<run id="53a2f94015cf3"></run>


'''Handlungssituation 3:'''

- Das Alter aller Mitarbeiterinnen und Mitarbeiter

- Alle Mitarbeiter, die in den letzten 5 Jahren in das Unternehmen eingetreten sind. 

- Alle tariflichen Mitarbeiter aus den Abteilungen Rechnungswesen, Organisation und EDV, die nach dem 15. Juli 2009 in das Unternehmen eingetreten sind
<run id="53a2f94015d87"></run>


'''Handlungssituation 4:'''

- Liste aller tariflichen Mitarbeiter der Einkaufsabteilung, die zwischen 40 und 50 Jahre alt sind.

- Liste aller Mitarbeiter, die ihr 10-jähriges Dienstjubiläum feiern. 

- Auflistung der Termine erstellen, an denen wir bislang eingestellt haben

- Liste vor und nach Entgelterhöhung um Sockelbetrag von 50,00 und zusätzlich 3 % auf das erhöhte Entgelt. 

<run id="53a2f94015e11"></run>



'''Handlungssituation 5: Mitarbeiterlisten'''

1)	Mitarbeiterlisten, die nach unterschiedlichen Kriterien sortiert sind 

- Alle Mitarbeiter nach PersonalNr sortiert

- Alle Mitarbeiter nach Nachname sortiert

- Alle Mitarbeiter nach Abteilung, Name und Vorname sortiert

- Alle Mitarbeiter nach Alter sortiert, beginnend bei den jüngsten.

- Alle Mitarbeiter, sortiert nach Wohnort



2)	Mitarbeiterlisten für tarifl. Mitarbeiter (gesamt und f. eine einzelne, mehrere Abteilungen)

- Alle tariflichen Mitarbeiter

- Alle nicht tariflichen Mitarbeiter

- Alle tariflichen Mitarbeiter der Abteilung Einkauf

- Alle tariflichen Mitarbeiter der Abteilungen Einkauf und Vertrieb

- Alle tariflichen Mitarbeiter der Abteilungen Einkauf und Vertrieb (mit Abteilungsbezeichnungen)



- Alle Mitarbeiter, die heute Geburtstag haben

- Alle Mitarbeiter, die seit 2008 neu eingestellt wurden
<run id="53a2fa839c369"></run>


'''Handlungssituation 6: Mitarbeiterstatistiken'''

1) Anzahl Mitarbeiter

- Die Anzahl aller Mitarbeiter

- Die Anzahl der Mitarbeiter der einzelnen Abteilungen

- Die Anzahl der tariflichen und außertariflichen Mitarbeiter je Abteilung

- Die Anzahl der Mitarbeiter nach Geschlecht

- Die Anzahl der Mitarbeiter nach Geschlecht in den einzelnen Abteilungen

- Die Anzahl der Mitarbeiter nach Geschlecht und Tarif in den einzelnen Abteilungen



2)	jüngste und älteste Mitarbeiter 

Das Geburtsdatum und Alter des ältesten Mitarbeiters

Das Geburtsdatum und Alter des ältesten männlichen Mitarbeiters und der ältesten weiblichen Mitarbeiterin

Das Geburtsdatum und Alter der ältesten Mitarbeiter in den einzelnen Abteilungen 



<run id="53a2a153a8cd2"  ></run>

3)	Durchschnittsalter 

- Das Durchschnittsalter der Mitarbeiter

- Das Durchschnittsalter der männlichen und weiblichen Mitarbeiter

- Das Durchschnittsalter der Mitarbeiter in den einzelnen Abteilungen

<run id="53a30a235e273"></run>

4)	Durchschnittliche Betriebszugehörigkeit 

- Liste aller Mitarbeiter sortiert nach Jahren der Betriebszugehörigkeit 

- Die durchschnittliche Betriebszugehörigkeit aller Mitarbeiter

- Die durchschnittliche Betriebszugehörigkeit der Mitarbeiter der einzelnen Abteilungen

- Die durchschnittliche Betriebszugehörigkeit der männlichen und weiblichen Mitarbeiter der einzelnen Abteilungen
<run id="53a30a235e316"></run>


5)	Durchschnittliches monatliches Brutto- und Nettoentgelt

- Liste der Mitarbeiter mit Brutto- und Nettoentgelt (inkl. Steuern und Sozialversicherungsabgaben)

- Durchschnittliches Monatsentgelt aller tariflichen und aller außertariflichen Mitarbeiter

- Durchschnittliches Monatsentgelt aller weiblichen und aller männlichen Mitarbeiter


- Durchschnittliches Monatsentgelt der Mitarbeiter der einzelnen Abteilungen

- Die Höhe der Personalkosten je Abteilung

<run id="53a3009cf05a8"></run>