NF 1, SS 1997: Lösungsblatt 11:

Aufgabe 1: Abfragen mit logischen Konnektoren

Lösung dazu

• Welche männlichen Mitarbeiter der Abteilung U2 bekommen ein Gehalt, das über 80000 DM liegt?

select id,vorname,name,gehalt 
  from ma 
  where abt='U2' and 
    sex='m' and 
    gehalt>80000 
  order by gehalt desc

• Wieviele dieser Mitarbeiter sind unter 30 oder gehen in den nächsten 5 Jahren in Rente?

select id,vorname,name,gehalt,alter
  from ma 
  where abt='U2' and 
    sex='m' and 
    gehalt>80000 and 
    (alter < 30 or alter >=60)
  order by gehalt desc

• Welche Mitarbeiter der Abteilungen K3 und A2 sind unter 25 oder gehen dieses oder nächstes Jahr in Rente?

select abt, vorname,name,alter 
  from ma 
  where (abt = 'K3' or abt = 'A2') and 
        (alter<22 or 
         (sex = 'm' and alter >=64) or 
         (sex= 'w' and alter >= 61))

Aufgabe 2: Erweiterung der Anwendung

Lösung dazu

Erstellen Sie eine Abteilungstabelle

create table ab (           
  abt char(2),
  funktion char(20),
  leiter int)

Füllen Sie die neu erstellte Tabelle mit den notwendigen Daten.

insert into ab values ('A2', 'Einkauf', 549)
insert into ab values ('X1', 'Buchhaltung', 592)
insert into ab values ('U2', 'Verwaltung', 5)
insert into ab values ('B1', 'Verkauf', 686)
insert into ab values ('K3', 'Geschaeftsleitung', 885)
insert into ab values ('A1', 'PR', 98)

Erstellen Sie eine Tabelle für den Betriebsausflug

create table ba (
  ma_id int,
  bezahlt char(1),
  rudern char(1),
  volley char(1),
  fahrzeug char(4))

Füllen Sie die neu erstellte Tabelle mit den notwendigen Daten.

insert into ba values (310, 'n', 'n', 'j', 'auto')
...

Aufgabe 3: Mehrtabellenabfragen

Lösung dazu

• Geben Sie eine Liste der Abteilungsleiter aus.

select ma.name,ma.vorname,ab.abt,ab.funktion 
  from ma,ab 
  where ma.id = ab.leiter

• Geben Sie eine Liste der Mitarbeiter aus, die mindestens soviel verdienen wie ihr Abteilungsleiter.

select ma.id,ma.name,ma.vorname,ma.abt,ma.gehalt
  from ma,ab,ma as ma2
  where ma.abt = ab.abt and
    ma2.id = ab.leiter and
    ma.id <> ma2.id and
    ma.gehalt >= ma2.gehalt

• In der vorigen Teilaufgabe ist es notwendig, drei Tabellen in die Abfrage zu integrieren. Sie werden dabei u.U. feststellen, daß die Durchführung der Abfrage einige Zeit benötigt. Woran kann das liegen? Gibt es eine bezüglich der Ausführungszeit optimale Art, die Anfrage zu stellen?

Die eben vorgestellte Lösung geht von der Mitarbeitertabelle aus und sucht zunächst den Abteilungsleiter. Das geschieht folglich für jeden Mitarbeiter, was überflüssig ist. Eine (hoffentlich) optimale Lösung ist

select ma2.id,ma2.name,ma2.vorname,ma2.abt,ma2.gehalt
  from ab,ma,ma as ma2
  where ab.leiter = ma.id and
    ab.abt = ma2.abt and
    ab.leiter <> ma2.id and
    ma.gehalt <= ma2.gehalt

, die zunächst die relevanten Daten der Abteilungsleiter (im wesentlichen sein Gehalt) ermittelt und dann mit allen Mitarbeitern vergleicht (die dann aus ma2 genommen werden). Gute Datenbanksysteme versuchen, optimale Zugriffswege zu Datenbanken zu ermitteln und so die Ausführungszeiten zu minimieren. Passiert dies nicht, so kann die Art, in der eine Anfrage formuliert ist, sogar über die Durchführbarkeit entscheiden...

• Welche Abteilungsleiter kommen mit auf den Betriebsausflug?

select ma.vorname, ma.name, ma.id, ma.abt 
  from ab,ma,ba 
  where ab.leiter = ma.id and 
    ab.leiter = ba.ma_id

• Welche Mitarbeiter kommen zwar mit, sind aber offensichtlich "Vergnügungsmuffel", da sie an keiner Aktivität teilnehmen und separat anreisen?

select ma.vorname,ma.name 
  from ba,ma 
  where ma.id = ba.ma_id and 
  ba.rudern = 'n' and 
  ba.volley = 'n' and 
  ba.fahrzeug = 'auto'

• Auf dem Betriebsausflug wird ein Volleyballturnier der Abteilungen geplant. Sind dazu genügend Mitarbeiter (Sechs und zwei Ersatzspieler) vorhanden?
• Lösung mit count()

select ma.abt, count(*) 
  from ba,ma 
  where ma.id = ba.ma_id and
    ba.volley = 'j' 
  group by ma.abt
  order by ma.abt

• Lösung als Liste:

select ma.abt,ma.vorname,ma.name 
  from ba,ma 
  where ma.id = ba.ma_id and
    ba.volley = 'j' 
  order by ma.abt

• Lösung mit xmsql:

xmsql -h nats13 xamtrup "select ma.abt from \
  ba,ma \
  where ma.id = ba.ma_id and \
    ba.volley = 'j' \
  order by ma.abt" | \
  uniq -c

---

Author:	Jan W. Amtrup
Document:
Last modified: