konzmod.sql

#create database klausur1;
use klausur1;

# Filiale(FID, Name, Standort)
# Mitarbeiter(MID, Vorname, Nachname, Wohnort)
# Arbeitsvertrag(AVID, FID[Filiale], MID[Mitarbeiter], Datum)



create table Filiale (
FID integer primary key,
Name Varchar(20),
Standort Varchar(20)
);

create table Mitarbeiter (
MID integer primary key,
Vorname Varchar(20),
Nachname Varchar(20),
Wohnort Varchar(20)
);

create table Arbeitsvertrag (
AVID integer primary key,
FID integer NOT NULL references Filiale(FID),
MID integer NOT NULL references Mitarbeiter(MID),
Datum Varchar(12)
);



insert into Filiale values (1, "Mainheim", "Plärrer");
insert into Filiale values (2, "1897", "Plärrer");
insert into Filiale values (3, "Hempels", "Plärrer");
insert into Filiale values (4, "Hot Taco", "Bärenschanze");
insert into Filiale values (5, "Regina", "Bärenschanze");
insert into Filiale values (6, "Cafe Katz", "Lorenzkirche");
insert into Filiale values (7, "Ill Amore", "Lorenzkirche");


insert into Mitarbeiter values (1, "Tilman", "Marquart", "Plärrer");
insert into Mitarbeiter values (2, "Tobias", "Klaus", "Plärrer");
insert into Mitarbeiter values (3, "Jannik", "Zinkl", "Aufsesplatz"); #
insert into Mitarbeiter values (4, "Max", "Gareis", "Wöhrder Wieße");
insert into Mitarbeiter values (5, "Benedikt", "Mangold", "Bärenschanze");


insert into Arbeitsvertrag values (1, 3, 1, "2017-05-12");
insert into Arbeitsvertrag values (2, 4, 2, "2017-05-12");
insert into Arbeitsvertrag values (3, 2, 3, "2017-05-12");

select distinct * from (
(select standort as Ort from Filiale)
 union
(select m.Wohnort as Ort from Mitarbeiter m WHERE NOT m.MID IN (SELECT MID from Arbeitsvertrag) )
) as a



#select mid from arbeitsvertrag;


# Zeiterfassung(MID, Datum, AnzahlStunden)

create table Zeiterfassung(
MID integer NOT NULL,
Datum Varchar(20) NOT NULL,
AnzahlStunden integer,
PRIMARY KEY(MID, Datum)
)



insert into Zeiterfassung values (1, "2017-05-03", 7);
insert into Zeiterfassung values (1, "2017-05-04", 5);
insert into Zeiterfassung values (1, "2017-05-05", 3);
insert into Zeiterfassung values (1, "2017-05-06", 7);
insert into Zeiterfassung values (1, "2017-05-07", 6);
insert into Zeiterfassung values (1, "2017-05-08", 8);
insert into Zeiterfassung values (1, "2017-05-09", 9);
insert into Zeiterfassung values (1, "2017-06-03", 2);
insert into Zeiterfassung values (1, "2017-07-03", 4);
insert into Zeiterfassung values (1, "2017-08-03", 3);
insert into Zeiterfassung values (1, "2017-09-03", 4);
insert into Zeiterfassung values (2, "2017-05-03", 7);
insert into Zeiterfassung values (2, "2017-05-04", 5);
insert into Zeiterfassung values (2, "2017-05-05", 3);
insert into Zeiterfassung values (3, "2017-05-06", 7);
insert into Zeiterfassung values (3, "2017-05-07", 6);
insert into Zeiterfassung values (3, "2017-05-08", 8);
insert into Zeiterfassung values (2, "2017-05-09", 9);
insert into Zeiterfassung values (2, "2017-06-03", 2);
insert into Zeiterfassung values (2, "2017-07-03", 4);
insert into Zeiterfassung values (3, "2017-08-03", 3);
insert into Zeiterfassung values (4, "2017-09-03", 4);


SELECT MID 
FROM Zeiterfassung 
GROUP BY MID
having sum(AnzahlStunden) < ANY (select sum(AnzahlStunden) as sumh from Zeiterfassung group by MID);

drop table ware;
drop table verkauf;


create table ware(
WID int NOT NULL PRIMARY KEY,
Preis int,
Regalbestand int,
Lagerbestand int);

create table verkauf(
VID int NOT NULL PRIMARY KEY,
WID Int NOT NULL REFERENCES ware(WID),
Datum varchar(12));

insert into ware values (1, 12, 200, 100);
insert into ware values (2, 12, 2, 400);
insert into ware values (3, 12, 400, 10);
insert into ware values (4, 12, 1, 3);
insert into ware values (5, 12, 100, 100);
insert into ware values (6, 12, 2, 1);

insert into Verkauf values (1, 1, "2017-04-02");
insert into Verkauf values (2, 1, "2017-04-02");
insert into Verkauf values (3, 1, "2017-04-02");
insert into Verkauf values (4, 1, "2017-04-02");
insert into Verkauf values (5, 1, "2017-04-04");
insert into Verkauf values (6, 1, "2017-04-05");
insert into Verkauf values (7, 1, "2017-04-06");




insert into Verkauf values (31, 2, "2017-04-02");
insert into Verkauf values (32, 2, "2017-04-02");
insert into Verkauf values (33, 2, "2017-04-02");
insert into Verkauf values (34, 2, "2017-04-02");
insert into Verkauf values (35, 2, "2017-04-04");
insert into Verkauf values (36, 2, "2017-04-05");
insert into Verkauf values (37, 2, "2017-04-06");

insert into Verkauf values (8, 3, "2017-04-02");
insert into Verkauf values (9, 3, "2017-04-02");
insert into Verkauf values (10, 3, "2017-04-02");
insert into Verkauf values (11, 3, "2017-04-02");
insert into Verkauf values (12, 3, "2017-04-04");
insert into Verkauf values (13, 3, "2017-04-05");
insert into Verkauf values (14, 3, "2017-04-06");

insert into Verkauf values (15, 4, "2017-04-02");
insert into Verkauf values (16, 4, "2017-04-02");
insert into Verkauf values (17, 4, "2017-04-02");
insert into Verkauf values (18, 4, "2017-04-02");
insert into Verkauf values (19, 4, "2017-04-04");
insert into Verkauf values (20, 4, "2017-04-05");

insert into Verkauf values (21, 5, "2017-04-02");
insert into Verkauf values (22, 5, "2017-04-02");
insert into Verkauf values (23, 5, "2017-04-02");
insert into Verkauf values (24, 5, "2017-04-02");
insert into Verkauf values (25, 5, "2017-04-04");
insert into Verkauf values (26, 5, "2017-04-05");
insert into Verkauf values (27, 5, "2017-04-06");

insert into Verkauf values (28, 6, "2017-04-02");
insert into Verkauf values (29, 6, "2017-04-02");
insert into Verkauf values (30, 6, "2017-04-02");
#insert into ware values (1, 12, 200, 100);
#insert into ware values (7, 12, 0, 0);
#insert into Verkauf values (39, 7, "2017-04-02");
#update ware set regalbestand = 1 where WID = 6;


select wid as summe from ware w
JOIN (
  select wid, avg(cnt) as avgCnt from 
    (select wid, count(*) as cnt, datum from verkauf group by wid, datum)
  as a group by wid
) as c USING (WID)
WHERE (w.Regalbestand+w.Lagerbestand) < avgCnt