Aufgabe zu MySQL

Aufgabe zu MySQL
Es soll eine Kurs- Datenbank (unten vereinfachtes Modell) in MySQL erstellt werden.
Die Daten stehen In Excel – Tabellen zur Verfügung. Ferner sollen einige Abfragen
an dieser Datenbank durchgeführt werden.
Funktionen
1
m
c
Kurskontrolle
m
Kursthemen
1
1
1
m
Personen
1
m
c
1
m
c
Kursbesuche
m
c
Kurse
m
c
1
Kursleiter
Lokal Attribute
Personen (PNr, Name, Vorname, FNr, Lohnstufe)
Funktionen (FNr, Funktion)
Kurse (KNr, Kursbezeichnung, KTNr, Kursort)
Kursthemen (KTNr, Themengebiet)
Kursleiter (KLNr, Status, PNr, Name, Vorname, Firma, Kurserfahrung)
Kursbesuche (Pnr, KNr, KLNr, Datum)
Kurskontrolle (Fnr, KNr)
Erstellen Sie zunächst die Datenbank mit Hilfe von PHP-MyAdmin und importieren
Sie die Daten aus den Excel - Tabellen.
Zunächst sollen DDL- (Data Definitieon Language) und DML-Befehle an einer
weiteren Übungsdatenbank (KURSE1) geübt werden. Notieren Sie sich dazu
zunächst schriftlich die entsprechenden SQL- Befehle und geben Sie diese dann im
SQL—Editor von php-MyAdmin ein.
1. Erstellen Sie die Tabelle Kursleiter mit folgenden Feldern und Datentypen:
Status VARCHAR(1), PNr LONG, Nachname VARCHAR(15), Vorname
VARCHAR (15), Kerf (1 BYTE Länge)
Abfrage:
2. Ändern Sie den Namen von Kursleiter in Kursleiter1 um
Abfrage:
3. Indizieren Sie das Feld Nachname um Such- und Sortiervorgänge, die den
Nachnamen beinhalten zu beschleunigen.
Abfrage:
4. Fügen Sie der Tabelle Kursleiter1 das Feld Firma VARCHAR(20) an
Abfrage:
5. Fügen Sie der Tabelle Kursleiter1 das Feld KLNr (BYTE) als Primärschlüssel
an
Abfrage:
6. Erstellen Si die Tabelle Personen1 (ohne ID-Schlüssel) mit den Attributen: PNr
(Integer), Name (20 Zeichen variable Speicherung , Vorname (15 Zeichen fixe
speicherung), GebDat (Geburtsdatum: Datumsfeld, Groesse (Zahl: maximale
Zahl der Ziffern=3, Ziffern nach dem Dezimalpunkt=2))
Abfrage:
7. Fügen Sie der Tabelle Personen das Feld Lohnstufe (1 Byte) an.
Abfrage:
8. Ändern Sie den Datentyp von Lohnstufe auf eine 2-stellige Dezimalzahl ohne
Nachkommastelle. Nullwerte sind zugelassen.
Abfrage:
9. Erstellen Sie die Tabelle Kurse1(KNr (Integer:6), Kursbezeichnung (Länge
20), Kursort(Länge 20))
Abfrage:
10. Erstellen Sie die Tabelle Kursbesuche1(PNR, KNr, Datum) und erzeugen Sie
(danach) einen eindeutigen Index (ID_Kursbesuche) auf die beiden Attribute
PNr und KNr.
Abfrage:
11. Erstellen Sie einen Index (ID-Kursdatum) auf das Datumsfeld von
Kursbesuche
Abfrage:
12. Stellen Sie zu den Fremdschlüsseln in Kursbesuche1 Referentielle Integrität
her.
Abfrage:
Arbeiten Sie nun mit der Original- Datenbank (KURSE) weiter
13. Fügen Sie in die Tabelle Kursleiter den internen Kursleiter Hugo Meier
(Kursleiternummer 1) und mit 3 Jahren Kurserfahrung ein. Firmenname und
Personennummer werden nicht mit angegeben.
Abfrage:
14. Speichern Sie in der Tabelle Kursleiter1 einen neuen Kursleiter mit der
Kurleiternummer 5 der bereits in der Tabelle Personen1 gespeichert ist und
die PNr 345678 besitzt.
Abfrage:
15. Erhöhen Sie die Lohnstufen aller Personen der Funktionsgruppe 4 um eine
Stufe
Abfrage:
16. Ändern Sie alle Datensätze der Tabelle Kursbesuche mit der Kursnummer 412
dahingehend, dass als Instruktor Stefan Krieg auftritt.
Abfrage:
17. Löschen Sie alle Datensätze aus der Tabelle Kursbesuche welche die
Personalnummer 100001 enthalten.
Abfrage:
18. Lassen Sie alle Daten der Tabelle Kursthemen anzeigen.
Abfrage:
19. Lassen Sie die Personennummer, Name und Vorname sowie das Gehalt
(Berechnet aus (Lohnstufe-1)-10000+50000) ausgeben.
Abfrage:
20. Lassen Sie die Anzahl der Kursbesuche sowie das erste und das letzte
Kursbesuchsdatum ausgeben.
Abfrage:
21. Lassen Sie anzeigen, wie viele verschiedene Kurse angeboten werden.
Abfrage:
22. Lassen Sie alle Vorarbeiter mit Personennummer, Namen und Vornamen
ausgeben
Abfrage:
23. Lassen Sie alle Meister und Chemiker (außer Herrn Steffen) ausgeben deren
Lohnstufe höher als 5 ist.
Abfrage:
24. Geben Sie alle Funktionsnummern mit dazugehöriger Funktion aus. Die
Sortierung soll nach dem Funktionsnamen erfolgen.
Abfrage:
25. Geben Sie alle Funktionsnummern mit dazugehöriger Funktion aus. Die
Sortierung soll absteigend nach dem Funktionsnummer erfolgen.
Abfrage:
26. Aus Kursbesuche sollen die Personennummer die Kursnummer und das
Kursdatum ausgegeben werden. Die Sortierung soll nach Personennummer
und nach Kursnummer aufsteigend, aber nach Datum absteigend erfolgen.
Abfrage:
27. Ausgegeben werden sollen die Anzahl der Personen innerhalb einer
Funktionsgruppe sowie das mittlere Gehalt innerhalb der einzelnen
Funktionsgruppen. (Berechnung des Gehalts s.o.). Sortierung nach
Funktionsnummer absteigend.
Abfrage:
28. Lassen Sie die Personennummern der Kursteilnehmer die mindestens einen
Kurs nach dem 1.9.1991 besucht haben und die Anzahl der Kursbesuche
dieser Personen ausgeben.
Abfrage:
29. Welche Kurse (Kursnummer und Kursbezeichnung) hat Herr Felix Steffen
schon besucht? Ausgabe sortiert nach Kursbezeichnung.
Abfrage:
30. Listen Sie die Spitzenverdiener der Lohnstufen mit Funktionsnummer (höcste
zuerst) Name (aufsteigend), Vorname und Lohnstufe auf.
Abfrage:
31. Lassen Sie alle möglichen Kombinationen aus den Tabellen Funktionen und
Personen anzeigen
Abfrage:
32. Lassen Sie alle zusammengehörigen Datensätze aus den Tabellen
Funktionen und Personen anzeigen
Abfrage:
33. Welche Personen (PNr, Name, Vorname) haben gemäß Kurskontrolle noch
mehr als drei Kurse zu besuchen?
Abfrage:
A1:
CREATE TABLE kursleiter (
Status CHAR( 1 ) NOT NULL ,
PNr INT NOT NULL ,
Nachname VARCHAR( 15 ) NOT NULL ,
Vorname VARCHAR( 15 ) NOT NULL ,
Kerf TINYINT NOT NULL
);
A2:
ALTER TABLE kursleiter RENAME kursleiter1;
A3:
ALTER TABLE kursleiter1 ADD INDEX ( Nachname );
oder
CREATE INDEX ind_name ON kursleiter1 (nachname);
A4:
ALTER TABLE `kursleiter1` ADD `Firma` VARCHAR( 20 ) NOT NULL ;
A5:
ALTER TABLE `kursleiter1` ADD `KLNr` SMALLINT NOT NULL ;
ALTER TABLE `kursleiter1` ADD PRIMARY KEY ( `KLNr` ) ;
Oder
ALTER TABLE `kursleiter1` ADD `KLNr` TINYINT PRIMARY KEY;
A6:
CREATE TABLE `Personen1` (
`PNr` INT( 6 ) NOT NULL ,
`Name` VARCHAR( 20 ) NOT NULL ,
`Vorname` CHAR( 15 ) NOT NULL ,
`GebDat` DATE NOT NULL ,
`Groesse` DECIMAL( 3, 2 ) NOT NULL
);
A7:
ALTER TABLE `personen1` ADD `Lohnstufe` TINYINT NOT NULL;
A8:
ALTER TABLE `personen1` CHANGE `Lohnstufe` `Lohnstufe`
DECIMAL( 2 ) DEFAULT '0' NULL
A9:
CREATE TABLE `Kurse1` (
`KNr` INT( 6 ) NOT NULL ,
`Kursbezeichnung` VARCHAR( 20 ) NOT NULL ,
`Kursort` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `KNr` )
);
A10:
CREATE TABLE `Kursbesuche1` (
`PNr` INT( 6 ) NOT NULL ,
`KNr` INT( 6 ) NOT NULL ,
`Datum` DATE NOT NULL
);
CREATE UNIQUE INDEX ID_Kursbesuche ON kursbesuche1(
PNr,
KNr
);
oder
CREATE TABLE
`PNr` INT( 6
`KNr` INT( 6
`Datum` DATE
UNIQUE (
`PNr` ,
`KNr`
)
);
`Kursbesuche1` (
) NOT NULL ,
) NOT NULL ,
NOT NULL ,
A11:
ALTER TABLE `kursbesuche1` ADD INDEX ( `Datum` )
A12:
ALTER TABLE kursbesuche1 ADD CONSTRAINT BEZ1 FOREIGN KEY (
PNr ) REFERENCES Personen1( PNr )
ALTER TABLE kursbesuche1 ADD CONSTRAINT BEZ2 FOREIGN KEY (
KNr ) REFERENCES Kurse1( KNr )
A13:
INSERT INTO Kursleiter( KLNr, Status, Vorname, Name,
Kurserfahrung)
VALUES (1, ‘I’, ‘Hugo’, ‘Meier’, 3)
A14:
INSERT INTO Kursleiter( KLNr, PNr, Name, Vorname, STATUS )
SELECT 5 , PNr, Name, Vorname, 'I'
FROM Personen
WHERE PNr =345678
A15:
UPDATE Personen SET Lohnstufe = Lohnstufe +1 WHERE FNr =4
A16:
UPDATE Kursbesuche SET KlNr = (SELECT KlNr
FROM Kursleiter
WHERE Name = 'Krieg'
AND Vorname = 'Stefan' )
WHERE KNr =412;
A17
DELETE FROM Kursbesuche WHERE PNr=10001;
A18
SELECT *
FROM kursthemen;
A19
SELECT PNr, Name, Vorname,
(Lohnstufe -1) *10000 +50000 AS Salaer
FROM personen;
A20
SELECT COUNT( * ) Anzahl, MIN( Datum ) Erster, MAX( Datum )
Letzter
FROM Kursbesuche;
A21
SELECT COUNT( DISTINCT KNr ) Verschiedene_Kurse
FROM Kursbesuche;
A22
SELECT PNr, Name, Vorname
FROM Personen
WHERE fnr =1;
A23
SELECT PNr, Name, Vorname
FROM Personen
WHERE Lohnstufe >=5
AND (FNr =2 OR FNr =3)
AND NOT (Name = 'Steffen')
A24
SELECT *
FROM Funktionen
ORDER BY Funktion
A25
SELECT *
FROM Funktionen
ORDER BY FNr DESC;
A26
SELECT PNr, KNr, Datum
FROM Kursbesuche
ORDER BY PNr ASC, KNr ASC, Datum DESC;
A27
SELECT Personen.FNr, COUNT( Personen.FNr ) AS Anzahl, AVG( (
Personen.Lohnstufe -1) *10000 +50000 ) AS DSalaer
FROM Personen
GROUP BY Personen.FNr
ORDER BY Personen.FNr DESC;
A28
SELECT PNr, count( KNr ) Anzahl
FROM Kursbesuche
WHERE Datum >= ’91-09-01’
GROUP BY PNR
HAVING COUNT( KNr ) >1;
A29
SELECT KNr, Kursbezeichnung
FROM Kurse
WHERE KNr IN(
SELECT KNr
FROM Kursbesuche
WHERE PNr = (
SELECT PNr
FROM Personen
WHERE Name = 'Steffen'
AND Vorname = 'Felix' ))
ORDER BY Kursbezeichnung;
Oder
SQL-Befehl:
SELECT Kurse.KNr, Kurse.Kursbezeichnung
FROM Personen
INNER JOIN (
Kurse
INNER JOIN Kursbesuche ON Kurse.KNr = Kursbesuche.KNr
) ON Personen.PNr = Kursbesuche.PNr
WHERE (
(
(
Personen.Name
) = "Steffen"
)
AND (
(
Personen.Vorname
) = "Felix"
))
ORDER BY Kursbezeichnung
A30
SELECT FNr, Name, Vorname, Lohnstufe
FROM personen
WHERE (
FNr, Lohnstufe
)
IN (
SELECT FNr, max( Lohnstufe )
FROM Personen
GROUP BY FNr
)
A31
SELECT *
FROM Funktionen, Personen
SELECT Personen.PNr, Personen.Name, Personen.Vorname,
Funktionen.Funktion
FROM Funktionen INNER JOIN Personen ON Funktionen.FNr =
Personen.FNr;
SELECT Personen.PNr, Personen.Name, Personen.Vorname,
Funktionen.Funktion
FROM Funktionen LEFT JOIN Personen ON Funktionen.FNr =
Personen.FNr;
A32
SELECT PNr, Name, Vorname, Funktion
FROM Funktionen, Persone
WHERE Personen.FNr=Funktionen.FNr;