Tag 14/24: SQL-Uebung

aufgaben.sql

-- a)
UPDATE mitarbeiter
SET tagesarbeitszeit = 7
WHERE ma_id = 812;

-- b)
SELECT count(*)
FROM mitarbeiter
WHERE tagesarbeitszeit < 7.33;

--c)
SELECT mitarbeiter.ma_id, mitarbeiter.nachname, fehlzeit.fehlgrund, SUM(fehlzeit.fehltage) AS fehltage
FROM fehlzeit, mitarbeiter
WHERE fehlzeit.ma_id = mitarbeiter.ma_id
GROUP BY mitarbeiter.ma_id, fehlzeit.fehlgrund;

SELECT mitarbeiter.ma_id, mitarbeiter.nachname, fehlzeit.fehlgrund, IFNULL(SUM(fehlzeit.fehltage), 0) AS fehltage
FROM mitarbeiter LEFT OUTER JOIN fehlzeit
ON fehlzeit.ma_id = mitarbeiter.ma_id
GROUP BY mitarbeiter.ma_id, fehlzeit.fehlgrund;

--d)
SELECT mitarbeiter.ma_id, mitarbeiter.nachname, mitarbeiter.vorname, SUM(einsatz.einsatzzeit) AS einsatzzeit
FROM mitarbeiter LEFT OUTER JOIN  (
	SELECT ma_id, cast((strftime('%s', einsatzbis_zeit) - strftime('%s', einsatzvon_zeit)) AS real)/60/60 AS einsatzzeit
	FROM einsatz
	WHERE datum BETWEEN "2009-04-01" AND "2009-04-30"
) AS einsatz
ON mitarbeiter.ma_id = einsatz.ma_id
GROUP BY mitarbeiter.ma_id;

SELECT ma_id, nachname, vorname, (
	SELECT SUM(CAST((strftime('%s', einsatzbis_zeit) - strftime('%s', einsatzvon_zeit)) AS real)/60/60)
	FROM einsatz
	WHERE ma_id = mitarbeiter.ma_id AND datum BETWEEN "2009-04-01" AND "2009-04-30"
) AS einsatzzeit
FROM mitarbeiter
GROUP BY ma_id;

SELECT mitarbeiter.ma_id, mitarbeiter.nachname, mitarbeiter.vorname, IFNULL(SUM(CAST((strftime('%s', einsatzbis_zeit) - strftime('%s', einsatzvon_zeit)) AS real)/60/60), 0)
FROM mitarbeiter LEFT OUTER JOIN einsatz
ON einsatz.ma_id = mitarbeiter.ma_id AND datum between "2009-04-01" AND "2009-04-30"
GROUP BY mitarbeiter.ma_id;

--e)
SELECT mitarbeiter.ma_id, mitarbeiter.nachname, mitarbeiter.vorname, (mitarbeiter.urlaubsanspruchjahr - fehlzeit.fehltage) AS resturlaub
FROM mitarbeiter, (
	SELECT ma_id, SUM(fehltage) AS fehltage
	FROM fehlzeit
	WHERE fehlgrund = 'urlaub' AND von_datum >= '2009-01-01' AND bis_datum <= '2009-12-31'
	GROUP BY ma_id
) AS fehlzeit
WHERE mitarbeiter.ma_id = fehlzeit.ma_id
ORDER BY mitarbeiter.ma_id;

SELECT mitarbeiter.ma_id, mitarbeiter.nachname, mitarbeiter.vorname, IFNULL((mitarbeiter.urlaubsanspruchjahr - fehlzeit.fehltage), 0) AS resturlaub
FROM mitarbeiter LEFT OUTER JOIN (
	SELECT ma_id, SUM(fehltage) AS fehltage
	FROM fehlzeit
	WHERE fehlgrund = 'urlaub' AND von_datum >= '2009-01-01' AND bis_datum <= '2009-12-31'
	GROUP BY ma_id
) AS fehlzeit
ON mitarbeiter.ma_id = fehlzeit.ma_id
ORDER BY mitarbeiter.ma_id;

SELECT ma_id, nachname, vorname, IFNULL((urlaubsanspruchjahr - (
		SELECT SUM(fehltage)
		FROM fehlzeit
		WHERE fehlgrund = 'urlaub'
			AND von_datum >= '2009-01-01'
			AND bis_datum <= '2009-12-31'
			AND ma_id = mitarbeiter.ma_id
		GROUP BY ma_id
	)
), 0)
FROM mitarbeiter
ORDER BY ma_id;

--f)
SELECT *
FROM mitarbeiter
WHERE nachname LIKE 'sch%' AND (strftime('%s', '2009-06-22') - strftime('%s', geb_datum))/60/60/24/360 > 30
ORDER BY geb_datum DESC, nachname ASC;

ini.sql

PRAGMA foreign_keys = "0";

.headers on
.mode column

begin transaction;

drop table if exists mitarbeiter;
drop table if exists einsatz;
drop table if exists fehlzeit;

create table mitarbeiter (
	[ma_id] integer primary key,
	nachname text not null check (nachname != ''),
	vorname text  not null check (vorname != ''),
	geb_datum date default current_date,
	tagesarbeitszeit integer not null check (tagesarbeitszeit != ''),
	urlaubsanspruchjahr integer not null check (urlaubsanspruchjahr != '')
);

create table einsatz (
	[e_id] integer primary key,
	ma_id integer not null check (ma_id != ''),
	datum date default current_date,
	einsatzvon_zeit time default current_time,
	einsatzbis_zeit time default current_time,
	foreign key(ma_id) references mitarbeiter(ma_id) on update cascade
);

create table fehlzeit (
	[fz_id] integer primary key,
	ma_id integer not null check (ma_id != ''),
	von_datum date default current_date,
	bis_datum date default current_date,
	fehlgrund text not null check (fehlgrund != ''),
	fehltage integer not null check (fehltage != ''),
	foreign key(ma_id) references mitarbeiter(ma_id) on update cascade
);

insert into mitarbeiter values
(811, 'mueller', 'jens', '1982-04-14', 8, 26),
(812, 'scholz', 'birgit', '1964-08-23', 4, 27),
(815, 'schmidt', 'ulrich', '1957-11-02', 8, 28),
(817, 'storck', 'hans', '1990-11-14', 6, 24),
(841, 'ullmann', 'franz', '1959-12-21', 8, 28),
(902, 'sorge', 'susanne', '1952-03-02', 8, 30),

(999, 'mustermann', 'max', '1901-01-01', 0, 0);

insert into einsatz values
(1, 811, '2009-04-17', '07:00:00', '11:45:00'),
(2, 811, '2009-04-17', '12:15:00', '16:00:00'),
(3, 811, '2009-04-18', '07:32:00', '08:10:00'),
(4, 902, '2009-04-17', '07:21:00', '12:06:00');

insert into fehlzeit values
(1, 811, '2009-04-18', '2009-04-23', 'urlaub', 4),
(2, 902, '2009-04-18', '2009-05-08', 'krank', 14),
(3, 811, '2009-06-19', '2009-06-20', 'krank', 2),
(4, 811, '2009-11-17', '2009-11-17', 'urlaub', 1),
(5, 904, '2009-12-31', '2009-12-31', 'urlaub', 1),
(6, 904, '2010-01-01', '2010-01-09', 'urlaub', 6),

(7, 999, '2009-04-18', '2009-04-23', 'urlaub', 4),
(8, 999, '2009-11-17', '2009-11-17', 'urlaub', 1);

commit;

-- vim: set ft=sql :

Tested in SQLite

ifnull()
timediff()
datediff()