SQL-Coach (Oracle/PostgreSQL)

(mit freundlicher Genehmigung von Prof. Dr. Bernhard Schiefer)

Erweiterte Aufgabenstellung:

  1. verwenden Sie Postgres statt der Oracle-DB
  2. erstellen Sie alle Relationen
  3. füllen Sie die Relationen mit den entsprechenden Daten
  4. beantworten Sie alle gestellten Fragen

Daten

create table personal(
     persnr         integer  primary key,
     vname          VARCHAR(10),
     nname          VARCHAR(15),
     projnr         integer,
     telefonnr      integer,
     gehalt         numeric);

insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (1,'Donald','Duck',1,1201,1000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (2,'Dagobert','Duck',1,1202,200);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (3,'Zita','Delle',1,1203,50);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (4,'Axel','Nässe',1,1203,50);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (5,'Anna','Bolika',1,1203,50);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (6,'Gustav','Gans',23,2201,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (7,'Gitta','Gans',23,2202,0);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (8,'Daniel','Duesentrieb',2,2203,5000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (10,'Klaas','Klever',1,1204,50);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (21,'Klaus','Trophobie',3,3201,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (71,'Micky','Maus',3,3201,3000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (72,'Anna','Nass',3,3202,1000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (73,'Minni','Maus',3,3203,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (51,'Rainer','Wein',6,6202,100);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (52,'Rainer','Zufall',6,6203,100);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (81,'Tick',NULL,7,5201,1500);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (82,'Trick',NULL,7,5201,1500);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (83,'Franz','Gans',7,5205,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (84,'Track',NULL,4,4201,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (90,'Klaus','Trophobie',4,4201,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (91,'Axel','Schweiss',4,4201,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (93,'Fenn','Sterputz',4,4203,2000);
insert into personal (PERSNR,VNAME,NNAME,PROJNR,TELEFONNR,GEHALT) values (94,'Heinz','Elmann',4,4204,7000);


create table projekt (
  PROJNR integer,
  BUDGET integer,
  ABTNR integer);
  
insert into projekt (PROJNR,BUDGET,ABTNR) values(1,5000,1);
insert into projekt (PROJNR,BUDGET,ABTNR) values(2,1000,1);
insert into projekt (PROJNR,BUDGET,ABTNR) values(3,2200,10);
insert into projekt (PROJNR,BUDGET,ABTNR) values(4,100,3);
insert into projekt (PROJNR,BUDGET,ABTNR) values(5,500,4);
insert into projekt (PROJNR,BUDGET,ABTNR) values(6,1000,1);
insert into projekt (PROJNR,BUDGET,ABTNR) values(7,3000,4);
insert into projekt (PROJNR,BUDGET,ABTNR) values(8,1000,8);
insert into projekt (PROJNR,BUDGET,ABTNR) values(9,1000,4);
insert into projekt (PROJNR,BUDGET,ABTNR) values(10,1000,7);
insert into projekt (PROJNR,BUDGET,ABTNR) values(23,5500,3);

create table akte (
  PERSNR integer not null,
  DATUM date,
  POSITION varchar(30),
  GEHALT integer);
  
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (8,'2008-01-01','Tüftler',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (8,'2008-06-01','Erfinder',3000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (8,'2009-04-01','Cheferfinder',5000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (1,'2009-04-01','Oberbuchhalter',1000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (1,'2006-01-01','Hilfsbuchhalter',100);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (1,'2007-09-01','Buchhalter',500);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (2,'2007-11-01','Buchhalter',200);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (2,'2007-01-01','Hilfsuchhalter',90);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (3,'2007-01-01','Hilfsbuchhalter',90);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (4,'2007-01-01','Hilfsbuchhalter',90);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (5,'2007-01-01','Hilfsbuchhalter',90);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (6,'2003-01-01','Angestellter',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (7,'2009-01-01','Praktikant',0);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (10,'2008-01-01','Hilfsbuchhalter',50);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'1982-03-01','Schuhputzer',50);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'1982-09-01','Tellerwäscher',100);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'1983-05-01','Tellerstapler',200);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'1988-05-01','Küchenchef',1000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'1999-05-01','Abteilungsleiter',3000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'2008-05-01','Hauptabteilungsleiter',7000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (91,'1997-05-01','Angestellter',1000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (91,'2007-05-01','Abteilungsleiter',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (71,'2002-01-01','Junior Berater',1000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (71,'2003-04-01','Berater',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (71,'2008-04-01','Senior Berater',3000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (21,'2001-01-01','Junior Berater',800);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (21,'2002-04-01','Berater',1200);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (21,'2007-04-01','Senior Berater',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (90,'2008-01-01','Angestellter',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (91,'2003-01-01','Praktikant',20);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (91,'2003-05-01','Angestellter',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (93,'2003-05-01','Angestellter',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (94,'2004-01-01','Angestellter',2000);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (81,'2008-01-01','Berater',1500);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (82,'2008-01-01','Berater',1500);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (84,'2008-01-01','Berater',1500);
insert into akte (PERSNR,DATUM,POSITION,GEHALT) values (84,'2009-01-01','Berater',2000);

create table abteilung (
  ABTNR integer primary key,
  ABTNAME varchar(30),
  BUDGET integer,
  CHEFNR integer);

insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (1,'Buchhaltung',10000,1);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (2,'FuE',15000,71);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (3,'Immobilien',4000,91);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (4,'Marketing',50000,81);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (5,'Controling',20000,94);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (6,'Hausmeister',1000,94);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (7,'Kantine',1000,94);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (8,'Vorstand',100000,94);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (9,'Fuhrpark',7000,94);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (10,'Service',30000,71);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (11,'Einkauf',15000,1);
insert into abteilung (ABTNR,ABTNAME,BUDGET,CHEFNR) values (12,'Verkauf',30000,1);