Klausur Datenbanken SQL - von Wolfgang Tschallener

SQL-Klausuraufgaben zur Datenbank Zoo
von Wolfgang Tschallener Bertolt-Brecht-Berufskolleg Duisburg
Erstellen Sie SQL-Abfragen zu folgenden Aufgabenstellungen:
1.
Ausgabe einer Telefonliste aller Mitarbeiter, die im Freigehege arbeiten,
mit TierpflegerID, Anrede, Vorname, Name und Telefon geordnet nach Name
2.
Die Tabelle Tierpfleger soll um die Emailadresse (email) erweitert werden.
3.
Bestimmen Sie alle Schimpansen mit TierID, Name, Geschlecht und
Geburtsdatum, die im Affenhaus untergebracht sind.
4.
Bestimmen Sie die Gehegenamen und die Anzahl der Tierpfleger, die in den
einzelnen Gehegen arbeiten, geordnet nach Gehegename!
5.
Der Zoo hat Neuzugänge: die Schimpansen Toto (männlich geb. 2000-11-27) und
Lola (Weiblich geb. 2001-03-11). Bringen Sie diese Tiere im Affenhaus unter
und sorgen Sie dafür, dass die Tiere um 9.00 Uhr und um 15.00 je ein kg
Banane(n) bekommen.
6.
Richten Sie ein Elefantenhaus ein.
7.
Welche Tiere sind im Affenhaus untergebracht (geordnet nach Gattung)?
8.
Erstellen Sie einen Fütterungsplan für Toto den Schimpansen geordnet nach
der Fütterungszeit. Die Zeitwerte sind abgespeichert im Format 'HH:MM:SS'.
9.
Welche Tiere im Zoo bekommen um 9 Uhr Bananen (mit TierID, Name und
Gattungsname)?
10.
Die Fortpflanzungsversuche mit Toto waren erfolglos. Deshalb wird Lola in
den Nachbarzoo gebracht. Ändern Sie die Datenbank entsprechend (auch
Fütterungsdaten entfernen)!
11.Welche Tiere werden mehr als einmal am Tag gefüttert?
12.
Es wird ein neuer Mitarbeiter Karl Zeller( 45430 Bergheim, Oststrasse 10,
tel. 0290-4711) eingestellt.
Dieser soll im Affenhaus und im Freigehege arbeiten. Ändern Sie die
Datenbank entsprechend.
13.
Die Fortpflanzungsversuche im Gehege Streichelzoo waren erfolgreich.
Am 28.11.2007 wurde das Eselbaby Eisbein (weiblich) geboren. Aktualisieren
Sie die Datenbank entsprechend.
14.
Ermitteln Sie alle Gehege, für die noch keine Gehegebetreuung festgelegt
wurde.
15.
Welche Tiere (mit Gattungsangabe) erhalten kein Futter?
16.
Welche Tiergehege sind nicht belegt?
17.
Welche Tiergehege sind mit mehr als 2 Tieren belegt?
18.
Geben Sie Tiername, Tiergeburtsdatum, das heutige Datum
Tiere aus.
und Alter für alle
Mögliche Lösungen:
1.
Select tierpfleger.tierpflegerid, tierpfleger.anrede, tierpfleger.vorname,
tierpfleger.name, tierpfleger.telefon
FROM tierpfleger, gehege, gehegebetreuung
Where tierpfleger.tierpflegerid=gehegebetreuung.mitarbeiterid and
gehege.gehegeid=gehegebetreuung.gehegeid and
gehege.gehegename='Freigehege'
ORDER BY tierpfleger.name
2.
ALTER TABLE tierpfleger ADD COLUMN ( email varchar(30) )
3.
SELECT tier.tierid, tier.name, tier.geschlecht, tier.geburtsdatum
FROM tier, gehege, gattung
WHERE tier.gehegeid = gehege.gehegeid
AND gattung.gattungsid = tier.gattungsid
AND gehege.gehegename = 'Affenhaus'
AND gattung.gattungsname = 'Schimpanse'
4.
SELECT gehege.gehegename, count( tierpfleger.tierpflegerid )
FROM gehege, tierpfleger, gehegebetreuung
WHERE tierpfleger.tierpflegerid = gehegebetreuung.mitarbeiterid
AND gehegebetreuung.gehegeid = gehege.gehegeid
GROUP BY (
gehege.gehegeid
)
ORDER BY gehege.gehegename
5.
INSERT INTO tier
VALUES (
NULL , 'Toto', 'm', '2000-11-27', (
SELECT gattungsid
FROM gattung
WHERE gattungsname = 'Schimpanse'
), (
SELECT gehegeid
FROM gehege
WHERE gehegename = 'Affenhaus'
)
)
--------------------------------------------INSERT INTO tier
VALUES (
NULL , 'Lola', 'w', '2001-03-11', (
SELECT gattungsid
FROM gattung
WHERE gattungsname = 'Schimpanse'
), (
SELECT gehegeid
FROM gehege
WHERE gehegename = 'Affenhaus'
)
)
---------------------------------------------INSERT INTO futterration
VALUES (
NULL , (
SELECT tierid
FROM tier
WHERE name = 'Toto'
), (
SELECT futterid
FROM futter
WHERE futtername = 'banane'
), '1 kg', '09:00'
)
-----------------------------------------------INSERT INTO futterration
VALUES (
NULL , (
SELECT tierid
FROM tier
WHERE name = 'Lola'
), (
SELECT futterid
FROM futter
WHERE futtername = 'banane'
), '1 kg', '09:00'
)
----------------------------------------------INSERT INTO futterration
VALUES (
NULL , (
SELECT tierid
FROM tier
WHERE name = 'Toto'
), (
SELECT futterid
FROM futter
WHERE futtername = 'banane'
), '1 kg', '11:00'
)
-----------------------------------------------INSERT INTO futterration
VALUES (
NULL , (
SELECT tierid
FROM tier
WHERE name = 'Lola'
), (
SELECT futterid
FROM futter
WHERE futtername = 'banane'
), '1 kg', '11:00'
)
-------------------------------------------------
6.
insert into gehege values(
NULL,'Elefantenhaus'
)
7.
select tier.tierid, tier.name, gattung.gattungsname
from tier, gattung, gehege
where tier.gattungsid=gattung.gattungsid and
tier.gehegeid=gehege.gehegeid and
gehege.gehegename='Affenhaus'
order by gattung.gattungsname
8.
select futterration.fuetterungszeit as 'Fütterungszeit', futter.futtername
as 'Futter', futterration.menge as 'Menge'
from tier, futterration, futter
where tier.tierid=futterration.tierid and
futter.futterid=futterration.futterid
and tier.name='Toto'
order by futterration.fuetterungszeit
9.
SELECT tier.tierid, tier.name, gattung.gattungsname
FROM tier, gattung, futterration, futter
WHERE tier.gattungsid = gattung.gattungsid
AND tier.tierid = futterration.tierid
AND futter.futterid = futterration.futterid
AND futterration.fuetterungszeit = '09:00'
AND futter.futtername = 'Banane'
10.
delete from futterration
where tierid = (select tierid from tier where name='Lola')
---------------------------------------------------------delete from tier
where name='Lola'
11.
select tier.tierid, tier.name, count(futterration.tierid) as 'Fütterungen'
from tier, futterration
where futterration.tierid=tier.tierid
group by(tier.tierid) having count(futterration.tierid)>1
oder ohne Angabe der Anzahl der Fütterungen:
select tier.tierid, tier.name
from tier, futterration
where futterration.tierid=tier.tierid
group by(tier.tierid) having count(futterration.tierid)>1
12.
insert into tierpfleger values(
NULL, 'Herr','Zeller','Karl','Oststr. 10','45430','Bergheim','02904711',NULL)
--------------------------------------------insert into gehegebetreuung values
(
NULL, (select tierpflegerid from tierpfleger where telefon='0290-4711'),
(select gehegeid from gehege where gehegename='Affenhaus')
),(
Null, (select tierpflegerid from tierpfleger where telefon='0290-4711'),
(select gehegeid from gehege where gehegename='Freigehege')
)
13.
insert into tier values(
NULL, 'Eisbein','w','2007-11-28',
(select gattungsid from gattung where gattungsname='Esel'),
(select gehegeid from gehege where gehegename='Streichelzoo')
)
14.
select gehege.gehegename
from gehege left join gehegebetreuung
on gehege.gehegeid=gehegebetreuung.gehegeid
where gehegebetreuung.betreuungsid is NULL
15.
SELECT tier.tierid, tier.name, gattung.gattungsname
FROM tier
LEFT JOIN futterration ON ( tier.tierid = futterration.tierid ),
gattung
WHERE gattung.gattungsid=tier.gattungsid
and futterration.futterrationsid IS NULL
16.
SELECT gehege.gehegeid, gehege.gehegename
FROM gehege
LEFT JOIN tier ON ( tier.gehegeid = gehege.gehegeid )
WHERE tier.gehegeid IS NULL
Oder (mit USING, wenn Primär- und Fremdschlüsselnamen übereinstimmen)
SELECT gehege.gehegeid, gehege.gehegename
FROM gehege
LEFT JOIN tier
USING ( gehegeid )
WHERE tier.gehegeid IS NULL
17.
SELECT gehege.gehegeid, gehege.gehegename, count( gehege.gehegeid ) AS
'Anzahl Tiere'
FROM gehege, tier
WHERE tier.gehegeid = gehege.gehegeid
GROUP BY (
gehege.gehegeid
)
HAVING (
count( gehege.gehegeid ) >2
)
18.
SELECT tier.tierid AS 'Nr.',
tier.name AS 'Tiername',
tier.geburtsdatum AS 'Geburtstag',
curdate( ) AS 'Heutiges Datum',
@a := ( year( curdate( ) ) - year( tier.geburtsdatum ) - ( dayofyear(
curdate( ) ) < dayofyear( tier.geburtsdatum ) ) ) AS 'Alter'
FROM tier
ORDER BY @a DESC
Für die exakte Altersberechnung wird zunächst die Differenz des
gegenwärtigen Jahres und des Geburtsjahres gebildet. Für den Fall, dass der
Geburtstag in diesen Jahr noch kommt, wird dann ein Jahr abgezogen.
Die komplizierte Berechnung wird der MYSQL-Variablen @a zugewiesen.
Mit Hilfe dieser Variablen kann dann auch absteigend sortiert werden.
Anhang:
Datenbank zoo zum Importieren mit phpMyAdmin von XAMPP:
------------
phpMyAdmin SQL Dump
version 2.8.0.3
http://www.phpmyadmin.net
Host: localhost
Erstellungszeit: 09. Dezember 2007 um 12:16
Server Version: 5.0.20
PHP-Version: 5.1.2
Datenbank: `zoo`
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `futter`
-DROP TABLE IF EXISTS `futter`;
CREATE TABLE IF NOT EXISTS `futter` (
`FutterID` int(11) NOT NULL auto_increment,
`Futtername` varchar(30) collate latin1_german1_ci NOT NULL,
`Futterbeschreibung` varchar(100) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`FutterID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `futter`
-INSERT INTO `futter` (`FutterID`, `Futtername`, `Futterbeschreibung`)
VALUES (1, 'Brot', ''),
(2, 'Obst', ''),
(3, 'Fischfutter trocken', ''),
(4, 'Fleisch', ''),
(5, 'Gemuese', ''),
(6, 'Fisch', ''),
(7, 'Hafer', ''),
(8, 'Heu', ''),
(9, 'Maus', ''),
(10, 'Banane', '');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `futterration`
-DROP TABLE IF EXISTS `futterration`;
CREATE TABLE IF NOT EXISTS `futterration` (
`FutterrationsID` int(11) NOT NULL auto_increment,
`TierID` int(11) NOT NULL,
`FutterID` int(11) NOT NULL,
`Menge` varchar(30) collate latin1_german1_ci NOT NULL,
`Fuetterungszeit` time NOT NULL,
PRIMARY KEY (`FutterrationsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `futterration`
-INSERT INTO `futterration` (`FutterrationsID`, `TierID`, `FutterID`,
`Menge`, `Fuetterungszeit`) VALUES (1, 2, 3, 'klein', '11:00:00'),
(2, 1, 3, 'klein', '11:00:00'),
(3, 3, 2, '0.5 kg', '09:00:00'),
(4, 4, 2, '0.5 kg', '09:00:00'),
(5, 3, 5, '1 kg', '14:00:00'),
(6, 4, 5, '1kg', '14:00:00'),
(7, 5, 7, '1 Fuder', '11:00:00'),
(8, 6, 7, '1 Fuder', '11:00:00'),
(9, 5, 8, '1 Gabel', '16:00:00'),
(10, 6, 8, '1 Gabel', '16:00:00'),
(11, 7, 6, '0.5 kg', '09:00:00'),
(12, 8, 6, '0.5 kg', '09:00:00'),
(13, 7, 9, '2', '13:00:00'),
(14, 8, 9, '2', '13:00:00');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `gattung`
-DROP TABLE IF EXISTS `gattung`;
CREATE TABLE IF NOT EXISTS `gattung` (
`GattungsID` int(11) NOT NULL auto_increment,
`Gattungsname` varchar(30) collate latin1_german1_ci NOT NULL,
`Beschreibung` varchar(200) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`GattungsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `gattung`
-INSERT INTO `gattung` (`GattungsID`, `Gattungsname`, `Beschreibung`) VALUES
(1, 'Loewe', ''),
(2, 'Goldfisch', ''),
(3, 'Elefant', ''),
(4, 'Weissstorch', ''),
(5, 'Orang-Utan', ''),
(6, 'Schimpanse', ''),
(7, 'Haengebauchschwein', ''),
(9, 'Hausgans', ''),
(10, 'Esel', '');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `gehege`
-DROP TABLE IF EXISTS `gehege`;
CREATE TABLE IF NOT EXISTS `gehege` (
`GehegeID` int(11) NOT NULL auto_increment,
`Gehegename` varchar(30) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`GehegeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--
-- Daten für Tabelle `gehege`
-INSERT INTO `gehege` (`GehegeID`, `Gehegename`) VALUES (1, 'Affenhaus'),
(2, 'Streichelzoo'),
(3, 'Aquarium'),
(4, 'Freigehege');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `gehegebetreuung`
-DROP TABLE IF EXISTS `gehegebetreuung`;
CREATE TABLE IF NOT EXISTS `gehegebetreuung` (
`BetreuungsID` int(11) NOT NULL auto_increment,
`MitarbeiterID` int(11) NOT NULL,
`GehegeID` int(11) NOT NULL,
PRIMARY KEY (`BetreuungsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `gehegebetreuung`
-INSERT
VALUES
(2, 2,
(3, 3,
(4, 4,
(5, 2,
(6, 5,
(7, 5,
INTO `gehegebetreuung` (`BetreuungsID`, `MitarbeiterID`, `GehegeID`)
(1, 1, 1),
2),
3),
4),
1),
1),
4);
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `tier`
-DROP TABLE IF EXISTS `tier`;
CREATE TABLE IF NOT EXISTS `tier` (
`TierID` mediumint(9) NOT NULL auto_increment,
`Name` varchar(30) collate latin1_german1_ci NOT NULL,
`Geschlecht` varchar(10) collate latin1_german1_ci NOT NULL,
`Geburtsdatum` date NOT NULL,
`GattungsID` int(11) NOT NULL,
`GehegeID` int(11) NOT NULL,
PRIMARY KEY (`TierID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `tier`
-INSERT INTO `tier` (`TierID`, `Name`, `Geschlecht`, `Geburtsdatum`,
`GattungsID`, `GehegeID`) VALUES (1, 'Nemo', 'm', '2006-11-20', 2, 3),
(2, 'Eva', 'w', '2005-05-12', 2, 3),
(3, 'Hugo', 'm', '2004-01-01', 5, 1),
(4, 'Elise', 'w', '2004-03-03', 5, 1),
(5, 'Hansi', 'm', '2000-01-02', 10, 2),
(6, 'Wilhelmine', 'w', '0000-00-00', 10, 2),
(7, 'Omo', 'm', '1999-01-01', 3, 4),
(8, 'Uli', 'm', '2000-01-01', 4, 4),
(12, 'Horst', 'w', '2007-12-06', 2, 0);
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `tierpfleger`
-DROP TABLE IF EXISTS `tierpfleger`;
CREATE TABLE IF NOT EXISTS `tierpfleger` (
`TierpflegerID` int(11) NOT NULL auto_increment,
`Anrede` varchar(10) collate latin1_german1_ci NOT NULL,
`Name` varchar(30) collate latin1_german1_ci NOT NULL,
`Vorname` varchar(30) collate latin1_german1_ci NOT NULL,
`Strasse` varchar(30) collate latin1_german1_ci NOT NULL,
`PLZ` varchar(6) collate latin1_german1_ci NOT NULL,
`Ort` varchar(30) collate latin1_german1_ci NOT NULL,
`Telefon` varchar(30) collate latin1_german1_ci NOT NULL,
`email` varchar(30) collate latin1_german1_ci default NULL,
PRIMARY KEY (`TierpflegerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `tierpfleger`
-INSERT INTO `tierpfleger` (`TierpflegerID`, `Anrede`, `Name`, `Vorname`,
`Strasse`, `PLZ`, `Ort`, `Telefon`, `email`) VALUES (1, 'Herr', 'Meier',
'Otto', 'Otto-Strasse 10', '47259', 'Duisburg', '0203-889911', NULL),
(2, 'Frau', 'Werter', 'Iris', 'Werter Platz 5', '47045', 'Duisburg', '0203778822', NULL),
(3, 'Frau', 'Heizmann', 'Eva', 'Heizstr. 6', '47200', 'Duisburg', '020360889', NULL),
(4, 'Herr', 'Mieser', 'Gerd', 'Mieser Weg 1', '57300', 'Duisburg', '0203456789', NULL),
(5, 'Herr', 'Zeller', 'Karl', 'Oststr. 10', '45430', 'Bergheim', '02904711', NULL);