sql db schultz

drop table liefert;
drop table Lieferant;
drop table Bewertung;
drop table besteht;
drop table Artikel;
drop table Bestellung;
drop table Kreditkarte;
drop table Lastschrift;
drop table Zahlungsmittel;
drop table supportet;
drop table Telefonnummern;
drop table Mitarbeiter;
drop table Kunde;

create table Kunde(
Kundennummer NUMBER NOT NULL,
name VARCHAR(45) NULL,
GEBURTSDATUM VARCHAR(45) NULL,
email varchar(45) null,
primary key (Kundennummer)
);
CREATE TABLE Mitarbeiter(
Mitarbeiterid NUMBER NOT NULL,
Name VARCHAR(45) NULL,
Geburtsdatum VARCHAR(8) Null,
PRIMARY KEY (Mitarbeiterid)
);
create table Telefonnummern(
Mobilfunknummer NUMBER NULL,
privatnummer NUMBER NULL,
Diensthandy NUMBER NOT NULL,
Mitarbeiterid NUMBER NOT NULL,
PRIMARY KEY (Diensthandy,Mitarbeiterid),
FOREIGN KEY(Mitarbeiterid) REFERENCES Mitarbeiter
);
create table supportet(
Mitarbeiterid number not null,
Kundennummer number not null,
foreign Key (Mitarbeiterid) references Mitarbeiter,
foreign key (Kundennummer) REFERENCES Kunde,
primary key(Mitarbeiterid, Kundennummer)
);
create table Zahlungsmittel(
ZahlungsmittelID number not null primary key,
Kundennummer number not null,
foreign key(Kundennummer) REFERENCES Kunde
);
create table Lastschrift(
ZahlungsmittelID number not null primary key,
BLZ int not null,
Kontonummer int not null,
foreign key(ZahlungsmittelID)REFERENCES Zahlungsmittel
);
create table Kreditkarte (
ZahlungsmittelID number not null primary key,
Kreditkartenummer number not null,
Ablaufjahr int not null,
foreign key (ZahlungsmittelID) REFERENCES Zahlungsmittel
);
create table Bestellung(
BestellID int not null primary key,
anzahl int null,
Datum VARCHAR(12) null,
Kundennummer int not null,
ZahlungsmittelID int not null,
foreign key(Kundennummer) REFERENCES Kunde,
foreign key(ZahlungsmittelID) REFERENCES Zahlungsmittel
);
create table Artikel(
ISBN int not null primary key,
Preis int constraint Valider_Preis check (preis > 0 and Preis < 1000),
Lagerposition int null,
Text varchar(256) null,
Bild varchar(256) null
);
create table besteht(
BestellID int not null,
ISBN int not null,
Menge int null,
primary key(BestellID, ISBN),
foreign key (BestellID) REFERENCES Bestellung,
foreign key (ISBN) REFERENCES Artikel
);
create table Bewertung (
Kundennummer int not null,
BewertungsID int not null,
ISBN int not null,
Note int null,
Kommentar varchar(256) null,
primary key (Kundennummer, BewertungsID),
foreign key (Kundennummer) REFERENCES Kunde,
foreign key (ISBN) REFERENCES Artikel,
constraint IsbnKunde_Unique unique(Kundennummer, ISBN)
);
Create table Lieferant(
lieferantenID int not null primary key
);

create table liefert(
ISBN int not null,
lieferantenID int not null,
foreign key(ISBN) REFERENCES Artikel,
foreign key(LieferantenID) REFERENCES Lieferant,
primary key (ISBN, lieferantenID)
);

--_______________________________________________________________//Insert Artikel
insert into artikel (ISBN, Preis, Lagerposition, Text, Bild)
values (12345, 20, 0, 'blabla', 'ASCII_FUU');


insert into artikel (ISBN, Preis, Lagerposition, Text, Bild)
values (0, 19, 1, 'blabla1', 'ASCII_FUU1');

insert into artikel (ISBN, Preis, Lagerposition, Text, Bild)
values (1, 18, 2, 'blabla2', 'ASCII_FUU2');

insert into artikel (ISBN, Preis, Lagerposition, Text, Bild)
values (2, 17, 3, 'blabla3', 'ASCII_FUU3');

insert into artikel (ISBN, Preis, Lagerposition, Text, Bild)
values (3, 16, 4, 'blabla4', 'ASCII_FUU4');
--__________________________________________________________//Insert Kunde

insert into Kunde (Kundennummer, Name, Geburtsdatum, email)
values (0, 'Kaniwar', '01.01.2019', 'kaniwar@haw.de');

insert into Kunde (Kundennummer, Name, Geburtsdatum, email)
values (1, 'Albert', '02.01.2019', 'Albert@haw.de');

insert into Kunde (Kundennummer, Name, Geburtsdatum, email)
values (2, 'Sami', '03.01.2019', 'Sami@haw.de');

insert into Kunde (Kundennummer, Name, Geburtsdatum, email)
values (3, 'Herberto', '04.01.2019', 'Herberto@haw.de');
--___________________________________________________________//Insert Zahlungsmittel

insert into Zahlungsmittel(ZahlungsmittelID, Kundennummer)
values (0, 0);
insert into Zahlungsmittel
values (1,1);

insert into Zahlungsmittel
values (2,2);
--_____________________________________//Insert Bestellung

insert into Bestellung (BestellID, anzahl, Datum, Kundennummer,ZahlungsmittelID)
values (0, 1, '01.11.19', 0, 0);
insert into Bestellung (BestellID, anzahl, Datum, Kundennummer,ZahlungsmittelID)
values (1, 1, '01.11.19', 0, 0);

insert into Bestellung (BestellID, anzahl, Datum, Kundennummer,ZahlungsmittelID)
values (2, 1, '01.11.19', 1, 1);

insert into Bestellung (BestellID, anzahl, Datum, Kundennummer,ZahlungsmittelID)
values (3, 1, '01.11.19', 1, 1);

insert into Bestellung (BestellID, anzahl, Datum, Kundennummer,ZahlungsmittelID)
values (4, 1, '01.11.19', 2, 2);
insert into Bestellung (BestellID, anzahl, Datum, Kundennummer,ZahlungsmittelID)
values (5, 1, '01.11.19', 2, 2);
--________________________________________________________//besteht
insert into besteht (BestellID, ISBN, Menge)
values (0, 0, 2);

insert into besteht
values (0,1,2);

insert into besteht (BestellID, ISBN, Menge)
values (1, 0, 1);

insert into besteht
values (1,1,1);

insert into besteht (BestellID, ISBN, Menge)
values (2, 0, 2);

insert into besteht
values (2,1,2);

insert into besteht (BestellID, ISBN, Menge)
values (3, 0, 1);

insert into besteht
values (3,1,1);

insert into besteht (BestellID, ISBN, Menge)
values (4, 0, 1);

insert into besteht (BestellID, ISBN, Menge)
values (4, 1, 1);

insert into besteht
values (5,1,1);

insert into besteht
values (5,0,1);








Update Kunde
set
name = 'Müller'
where name = 'Kaniwar';

Update Artikel
set Preis = Preis *1.2;



Alter table Artikel
add Preis_RBL int;

--Insert into Artikel(ISBN,Preis,Lagerposition,Text,Bild,Preis_RBL)
    Select Preis * 69.13 as PREIS_RBL  from Artikel;
Update Artikel
set Preis = Preis *69.13; -- Updatet alle Preise zu RBL, funktioniert wegen Valider Preis Constraint (>1000) nicht.

--funktioniert
Delete from Kunde
where Kundennummer in (select Kundennummer from Kunde where Kundennummer NOT IN (Select Kundennummer from Bestellung));

Delete from Bewertung
Delete from besteht
Delete from Bestellung
Delete from Kreditkarte
Delete from Lastschrift
Delete from Zahlungsmittel
Delete from Supportet;
Delete from Kunde

Delete from Kunde inner join Bewertung on Kundennummer = Kundennummer and inner join Bestellung on Kundennummer = Kundennummer
and inner join Kreditkarte on Kundennummer = Kundennummer and inner join Lastschrift on Kundennummer = Kundennummer
and inner join Bestellung on Kundennummer = Kundennummer and inner join Supportet on Kundennummer = Kundennummer;

delete from Kunde;
Truncate table besteht
Truncate table Bewertung;Truncate table Kreditkarte;Truncate table Lastschrift;Truncate table Zahlungsmittel;Truncate table Bestellung Truncate table Supportet;Truncate table Kunde;
ALTER TABLE Kunde NOCHECK CONSTRAINT ALL;
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'Kunde';
SELECT
 [Current LSN],
 [Transaction ID],
     Operation,
     Context,
     AllocUnitName

FROM
    fn_dblog(NULL, NULL)
WHERE
    Operation = 'LOP_DELETE_ROWS'



ALTER SESSION SET CURRENT_SCHEMA = db_schultz;
--1
Select pid,pname,sum(numitems) from OSPRODUCT
join OSSTORED_IN using (PID)
join OSSHELF using (WHID)
join OSWAREHOUSE on WHID = WAREHOUSEID
where warehousename = 'Einkauf'
group by pid,pname order by sum(numitems) asc;

--2
Select warehouseid,warehousename,sid,shelfdescription,
sum(numitems) from OSWAREHOUSE
join OSSHELF on WHID=WAREHOUSEID
join OSSTORED_IN using (WHID,SID)
group by warehouseid,warehousename,sid,shelfdescription
order by sum(numitems) desc;