Sql-Übung

aufgaben.sql

-- a)
SELECT fahrten.*, fahrer.fa_name
FROM fahrten, fahrer
WHERE f_fa_id = fa_id;

-- b)
DELETE FROM kunde
WHERE kd_id NOT IN (SELECT au_kd_id FROM auftrag);

-- c)
SELECT teilstrecke.ts_id, teilstrecke.ts_strecke, fahrer.fa_name, auftrag.au_auftrag, kunde.*
FROM teilstrecke, fahrer, auftrag, fahrten, kunde
WHERE teilstrecke.ts_f_id = fahrten.f_id
AND fahrten.f_fa_id = fahrer.fa_id
AND fahrten.f_au_id = auftrag.au_id
AND auftrag.au_kd_id = kunde.kd_id
AND auftrag.au_auftrag = 'au-00012';

-- d)
-- What a mess ...
SELECT kunde.*, COUNT(auftrag.AnzahlAuftr) AS AnzahlAuftr, SUM(auftrag.AnzahlFahrt) AS AnzahlFahrt, SUM(auftrag.SumStrecke) AS SumStrecke
FROM kunde LEFT OUTER JOIN (
	SELECT auftrag.au_kd_id, COUNT(au_kd_id) AS AnzahlAuftr, fahrten.AnzahlFahrt, fahrten.SumStrecke FROM auftrag LEFT OUTER JOIN (
		SELECT fahrten.f_au_id, COUNT(fahrten.f_au_id) AS AnzahlFahrt, SUM(teilstrecke.SumStrecke) AS SumStrecke
		FROM fahrten LEFT OUTER JOIN (
			SELECT ts_f_id, SUM(ts_strecke) AS SumStrecke
			FROM teilstrecke
			GROUP BY ts_f_id
		) AS teilstrecke
		ON teilstrecke.ts_f_id = fahrten.f_id
		GROUP by fahrten.f_au_id
	) AS fahrten
	ON auftrag.au_id = fahrten.f_au_id
	GROUP by auftrag.au_id
) AS auftrag
ON kunde.kd_id = auftrag.au_kd_id
GROUP BY auftrag.au_kd_id
ORDER BY kunde.kd_id;

ini.sql

PRAGMA foreign_keys = "1";

begin transaction;

	drop table if exists kunde;
	drop table if exists auftrag;
	drop table if exists teilstrecke;
	drop table if exists fahrer;
	drop table if exists fahrten;

create table kunde (
	[kd_id] integer primary key,
	kd_firma text unique not null check (kd_firma != ''),
	kd_strasse text not null check (kd_strasse != ''),
	kd_plz text integer not null check (kd_plz != ''),
	kd_ort text not null check (kd_ort != '')
);

create table auftrag (
	[au_id] integer primary key,
	au_kd_id integer not null check (au_kd_id != ''),
	au_auftrag text unique not null check (au_auftrag != ''),
	foreign key(au_kd_id) references kunde(kd_id) on update cascade
);

create table teilstrecke (
	[ts_id] integer primary key,
	ts_f_id integer not null check (ts_f_id != ''),
	ts_strecke integer not null check (ts_strecke != ''),
	foreign key(ts_f_id) references fahrten(f_id) on update cascade
);

create table fahrer (
	[fa_id] integer primary key,
	fa_name text not null check (fa_name != ''),
	fa_strasse text not null check (fa_strasse != ''),
	fa_plz text integer not null check (fa_plz != ''),
	fa_ort text not null check (fa_ort != '')
);

create table fahrten (
	[f_id] integer primary key,
	f_fa_id integer not null check (f_fa_id != ''),
	f_au_id integer not null check (f_au_id != ''),
	foreign key(f_fa_id) references fahrer(fa_id) on update cascade,
	foreign key(f_au_id) references auftrag(au_id) on update cascade
);

insert into kunde values
(1, 'trapo', 'meinicher weg 8', 55657, 'koeln'),
(2, 'ollandi', 'frentroper str. 103', 47785, 'essen'),
(3, 'mueller', 'mannstedter platz 1', 44544, 'essen');

insert into fahrer values
(1, 'petermann', 'stremmweg', 45999, 'essen'),
(2, 'hansen', 'reitweg', 56676, 'koeln'),
(3, 'mutzner', 'schreikamp', 55555, 'koeln');

insert into auftrag values
(1, 1, 'au-00012'),
(2, 1, 'au-00133'),
(3, 3, 'au-01101');

insert into fahrten(f_fa_id, f_au_id) values
(1, 1),
(1, 1),
(1, 1),
(2, 1),
(3, 1),
(3, 2),
(3, 2);

insert into teilstrecke(ts_f_id, ts_strecke) values
(1, 400),
(1, 122),
(1, 4),
(1, 55),
(1, 89),
(1, 233),
(2, 121),
(2, 44),
(2, 66),
(2, 322),
(3, 850),
(4, 333),
(4, 560);

commit;

-- vim: set ft=sql :

d) ergibt bei mir also:

kd_id       kd_firma    kd_strasse       kd_plz      kd_ort      AnzahlAuftr  AnzahlFahrt  SumStrecke
----------  ----------  ---------------  ----------  ----------  -----------  -----------  ----------
1           trapo       meinicher weg 8  55657       koeln       2            7            3199
2           ollandi     frentroper str.  47785       essen       0
3           mueller     mannstedter pla  44544       essen       1

Tested with sqlite

She says:

SELECT Kunde.*,
(
	SELECT COUNT(*)
	FROM Auftrag
	WHERE Auftrag.Au_Kd_ID = Kunde.Kd_ID
) AS AnzahlAuftr,
(
	SELECT COUNT(*)
	FROM Fahrten, Auftrag
	WHERE Fahrten.F_Au_ID = Auftrag.Au_ID 
	AND Auftrag.Au_Kd_ID = Kunde.Kd_ID
) AS AnzahlFahrten,
(
	SELECT SUM(Ts_Strecke) 
	FROM Teilstrecke, fahrten, auftrag
	WHERE teilstrecke.ts_f_id = fahrten.f_id
	AND fahrten.f_au_id = auftrag.au_id
	AND auftrag.au_kd_id = kunde.kd_id
) 
FROM Kunde;