Probeklausur März 2021, Antworten PDF

Preview

Summary

Wintersemester...

Description

DROP TABLE Gaeste CASCADE CONSTRAINTS; DROP TABLE mahnung CASCADE CONSTRAINTS; DROP TABLE Buchung CASCADE CONSTRAINTS; DROP SEQUENCE Mahnungs_seq; DROP SEQUENCE Buchungs_seq; DELETE FROM Gaeste DELETE FROM Mahnung DELETE FROM Buchung

CREATE TABLE Gaeste ( Kunden_Nr NUMBER(10) PRIMARY KEY, Name Varchar(45), Land Varchar(45), Stadt VARCHAR(45), Geschlecht CHAR(1) ); CREATE TABLE buchung ( BuchungsNr NUMBER(10)PRIMARY KEY, Kunden_Nr NUMBER(10) NOT NULL, zimmer NUMBER(38) NOT NULL, tag NUMBER(10) NOT NULL, jahr NUMBER(10) NOT NULL, betrag NUMBER(38) NOT NULL, dauer NUMBER(38) NOT NULL ); ALTER TABLE buchung ADD CONSTRAINT fk_kundenNr FOREIGN KEY (Kunden_Nr) REFERENCES Gaeste; CREATE SEQUENCE Buchungs_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10; CREATE TABLE Mahnung ( mahnungsNr NUMBER(10) PRIMARY KEY, kunden_Nr NUMBER(10) NOT NULL, tag NUMBER(10) NOT NULL, jahr NUMBER(10) NOT NULL, betrag NUMBER(38) NOT NULL, CONSTRAINT fkkundenNr FOREIGN KEY (Kunden_Nr) REFERENCES Gaeste(Kunden_Nr) );

CREATE SEQUENCE Mahnungs_seq INCREMENT BY 1 START WITH 7 NOMAXVALUE NOCYCLE CACHE 10; INSERT INTO Gaeste VALUES(451, 'Antonio Luigi', 'I', 'Roma','M'); INSERT INTO Gaeste VALUES(341, 'Maria Esmeralda', 'E', 'Valencia','W'); INSERT INTO Gaeste VALUES(631, 'Thorsten Wendelin', 'D', 'Gummersbach','M'); INSERT INTO Gaeste VALUES(837, 'Fritz Hammer', 'D', 'Köln','M'); INSERT INTO Gaeste VALUES(545, 'Attiwa Osman', 'TR', 'Ankara','M'); INSERT INTO Gaeste VALUES(213, 'Luise Mann', 'D', 'Kassel','W'); INSERT INTO Gaeste VALUES(452, 'Hans Man', 'D', 'Kassel','M'); INSERT INTO Gaeste VALUES(454, 'Hansotto Nagel', 'D', 'Wuppertal','M'); INSERT INTO Gaeste VALUES(566, 'Bill Wilton', 'GB', 'London','M'); INSERT INTO Gaeste VALUES(732, 'Mary Winston', 'GB', 'Liverpool','W'); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 631, 105, 45, 2020, 660, 6); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 837, 109, 47, 2020, 1920, 4); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 451, 104, 61, 2020, 800, 8); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 545, 207, 103, 2020, 600, 3); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 451, 104, 122, 2020, 300, 3); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 837, 109, 145, 2020, 330, 3); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 631, 106, 287, 2020, 1020, 6); INSERT INTO Buchung VALUES(BUCHUNGS_SEQ.nextval, 631, 105, 73, 2019, 330, 3); INSERT INTO Mahnung VALUES(3, 452, 720, 143, 2020); INSERT INTO Mahnung VALUES(4, 631, 1000, 143, 2020); INSERT INTO Mahnung VALUES(MAHNUNGS_SEQ.nextval, 566, 380, 77, 2019); INSERT INTO Mahnung VALUES(MAHNUNGS_SEQ.nextval, 732, 440, 65, 2020); COMMIT; --a) Select Distinct Gaeste.Kunden_nr, Name, Land From Gaeste, Buchung, Mahnung Where Gaeste.kunden_nr=Buchung.kunden_nr And buchung.kunden_nr=mahnung.kunden_nr And Gaeste.land='D'; --b) Select kunden_nr, Name From Gaeste Where land='GB' and not exists (Select NULL from Mahnung Where jahr=2020 and gaeste.kunden_nr=mahnung.kunden_nr); --c) Select distinct gaeste.kunden_nr|| Name AS "AktiveGaeste" From Gaeste, buchung

Where gaeste.kunden_nr=buchung.kunden_nr or exists (Select NULL from Mahnung Where jahr=2020 and gaeste.kunden_nr=mahnung.kunden_nr); --d) Select gaeste.kunden_nr|| Name AS "PassiveGaeste" From Gaeste Where kunden_nr not in (Select gaeste.kunden_nr from Mahnung,buchung,gaeste where buchung.kunden_nr=gaeste.kunden_nr or gaeste.kunden_nr = mahnung.kunden_nr); --e) Select gaeste.kunden_nr, gaeste.Name, SUM(Dauer) AS Gesamt From Gaeste, Buchung Where gaeste.kunden_nr=buchung.kunden_nr And Land='I' And Geschlecht='M' And buchung.jahr Between 2019 and 2020 Group by gaeste.kunden_nr, gaeste.name --f) select gaeste.kunden_nr, gaeste.name, gaeste.land, gaeste.stadt from gaeste -- Für welche gaeste gilt.. where not exists (select kunden_nr from buchung -- dass es keine buchung gibt where not exists (select kunden_nr from buchung where buchung.kunden_nr=gaeste.kunden_nr and jahr=2020 intersect select kunden_nr from buchung where jahr=2019)); -- die sie im J 19/20 nicht gebucht haben können...