SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Artikel ( ANr int AUTO_INCREMENT NOT NULL PRIMARY KEY, aKat varchar(30), aBez varchar(30), aPreis decimal(6,2), aEinheit varchar(10) ); CREATE TABLE Kunde ( KNr int AUTO_INCREMENT NOT NULL PRIMARY KEY, kNName varchar(30), kVName varchar(30), kFemale boolean, kStrasse varchar(30), kPLZ varchar(10), kOrt varchar(30), kBonitaet varchar(30), kTyp varchar(30) ); CREATE TABLE Mitarbeiter ( MNr int NOT NULL PRIMARY KEY, mNName varchar(30), mVName varchar(30), mFemale boolean, mStrasse varchar(30), mPLZ varchar(10), mOrt varchar(30), mSVNR varchar(10), mIBAN varchar(30) ); CREATE TABLE Bestellung ( BNr int AUTO_INCREMENT NOT NULL PRIMARY KEY, mNr int, kNr int, bRNr int, bDatum timestamp DEFAULT CURRENT_TIMESTAMP(), bRDatum timestamp DEFAULT CURRENT_TIMESTAMP(), bSkonto decimal(6,2), FOREIGN KEY (mNr) REFERENCES Mitarbeiter(MNr), FOREIGN KEY (kNr) REFERENCES Kunde(KNr) ); CREATE TABLE Position ( PNr int AUTO_INCREMENT NOT NULL PRIMARY KEY, bNr int, aNr int, pMenge decimal(5,2) NOT NULL DEFAULT 1, pPreis decimal (6,2), pRabatt decimal(5,2), FOREIGN KEY (bNr) REFERENCES Bestellung(BNr), FOREIGN KEY (aNR) REFERENCES Artikel(ANr) ); CREATE TABLE Reklamation ( RNr int AUTO_INCREMENT NOT NULL PRIMARY KEY, mNr int, pNr int, rGRund varchar(30), rDatum timestamp DEFAULT CURRENT_TIMESTAMP(), Foreign key (mNr) references Mitarbeiter(MNr), Foreign key (pNr) references Position(PNr) ); SELECT * FROM Artikel; SELECT * FROM Mitarbeiter; SELECT * FROM Kunde; SELECT * FROM Bestellung; SELECT * FROM Position; SELECT * FROM Reklamation; /*ÜBUNG 07*/ /*-----------------------------------*/ /*7/1*/ ALTER TABLE Position RENAME Positionen; /*7/2*/ ALTER TABLE Bestellung ADD (bRStrasse char(40)); /*7/3*/ ALTER TABLE Bestellung CHANGE bRStrasse bRStrasse varchar(50); /*7/4*/ SHOW COLUMNS FROM Mitarbeiter; SHOW CREATE TABLE Positionen; SHOW CREATE TABLE Bestellung; /*Zum herausfinden wie der KEY heißt*/ /*7/5*/ ALTER TABLE Bestellung DROP CONSTRAINT Bestellung_ibfk_1; ALTER TABLE Mitarbeiter CHANGE MNr MNr INT AUTO_INCREMENT; ALTER TABLE Bestellung ADD CONSTRAINT FK_BestMNr FOREIGN KEY (mNr) REFERENCES Mitarbeiter(MNr); /* 7/6*/ ALTER TABLE Artikel ADD (CONSTRAINT aPreis CHECK (aPreis >0)); INSERT INTO Artikel values (null,"test1", "test1", -10.65, "9"); INSERT INTO Artikel values (null,"test2", "test2", 10.65, "7"); SHOW COLUMNS FROM Mitarbeiter; /*----------------------------------------*/ /*ÜBUNG 08*/ /*8/1*/ INSERT INTO Artikel values (null, "Elektronik", "Motherboard", 159.99, "5"), (null, "Elektronik", "GPU", 485.99, "9"), (null, "Möbel", "Gamingtisch3000", 120.99, "3"), (null, "Möbel", "Streamingchair XXXL", 424.99, "17"), (null, "Zubehör", "Mousepad", 12.99, "59"); INSERT INTO Kunde values (null, "Deutsch", "Christoph", 0, "Musterweg 1", "8020", "Graz", "Befriedigend", "privat"), (null, "Maier", "Max", 0, "Grazerstrasse 17", "8010", "Sauzipf", "Sehr Gut", "geschäftlich"), (null, "Stricker", "Nadine", 1, "Hauptstrasse 45", "8010", "Graz", "Gut", "privat"), (null, "Märzel", "Katharina", 1, "Neudorferweg 109", "8962", "Gersdorf", "Genügend", "privat"); INSERT INTO Mitarbeiter values (null, "Rüdiger", "Rolf", 0, "ORF-Strasse 1", "1000", "Wien", "1234030279", "AT003500000001070671"), (null, "Nordman", "Susi", 1, "Feldweg 10", "7551", "Bocksdorf", "5984130599", "AT021860000012387890"), (null, "Weichmann", "Robert", 0, "Ringgasse 58", "9300", "Baiersdorf", "9874121185", "AT031860000012389590"); INSERT INTO Bestellung values (null, 1, 1, 101, DEFAULT, DEFAULT, 0.00, "Teststraße 1"), (null, 3, 2, 102, DEFAULT, DEFAULT, 0.00, "Teststraße 2"), (null, 2, 1, 103, DEFAULT, DEFAULT, 0.00, "Teststraße 31"), (null, 3, 3, 104, DEFAULT, DEFAULT, 0.00, "Teststraße 4"); INSERT INTO Positionen values (null, 1, 3, 2, 120.99, 0.3), (null, 1, 5, DEFAULT, 12.99, 0.05), (null, 2, 3, DEFAULT, 120.99, 0.03), (null, 3, 4, 3, 424.99, 0.15); /*8/2*/ UPDATE Kunde SET kNName = "Berger" WHERE KNr = 2; select * from Kunde; /*8/3*/ UPDATE Bestellung SET bSkonto = 0.03 ORDER BY BNr DESC limit 1; select * from Bestellung; /*8/4*/ UPDATE Positionen SET pRabatt = 0.5 ORDER BY BNr DESC limit 1; /*8/5*/ INSERT INTO Mitarbeiter values (null, "Hartfrau", "Roberta", 1, "Quadratstraße 87", "9300", "Eiersdorf", "9874122485", "AT101860000012389590"); select * from Mitarbeiter; DELETE FROM Mitarbeiter ORDER BY MNr DESC limit 1; select * from Mitarbeiter; /*8/6*/ INSERT INTO Bestellung values (null, 6, 1, 103, DEFAULT, DEFAULT, 0.00, "Teststraße 8"); /*Es gibt keinen Mitarbeiter mit mNr "6"*/ /*Cannot add or update a child row: a foreign key constraint fails (`DB`.`Bestellung`, CONSTRAINT `FK_BestMNr` FOREIGN KEY (`mNr`) REFERENCES `Mitarbeiter` (`MNr`))*/ /*------------------------------*/ /*Übung 09*/ /*9/1*/ SELECT * from Bestellung WHERE EXTRACT(YEAR FROM bDatum) = 2019; INSERT INTO Bestellung values (null, 2, 3, 054,"2019-05-14", DEFAULT, 0.00, "NEUE-Teststraße 57"); SELECT * from Bestellung WHERE EXTRACT(YEAR FROM bDatum) = 2019; /*oder*/ SELECT * FROM Bestellung WHERE bDatum BETWEEN "2019-01-01 00:00:00" AND "2019-12-31 00:00:00"; /*9/2*/ SELECT * FROM Kunde Where kOrt = "Graz" and kFemale = 1 ORDER BY kNName; /*9/3*/ INSERT INTO Artikel values (null, "Kosmetik", "Salbe", 8.99, "12"), (null, "Kosmetik", "Lippenstift", 4.99, "2"), (null, "Kosmetik", "Duschgel", 1.99, "32"), (null, "Kosmetik", "Haarshampoo", 3.59, "22"), (null, "Kosmetik", "Gesichtsmaske", 12.99, "7"); SELECT * FROM Artikel Where aKat = "Kosmetik" ORDER BY aPreis ASC limit 3; /*9/4*/ INSERT INTO Artikel values (null, "Unterhaltunselektronik", "PS5", 659.99, "4"), (null, "Unterhaltunselektronik", "Nintendo Wuuuu2", 385.99, "8"); SELECT *, IF(aKat= "Unterhaltunselektronik", aPreis*0.7, aPreis) AS Aktionspreis FROM Artikel Where aKat = "Unterhaltunselektronik"; /*9/5*/ INSERT INTO Kunde values (null, "Maier", "Andreas", 0, "Dreckstraße 17", "8600", "Kapfenberg", "Gut", "privat"); SELECT * FROM Kunde WHERE kNName IN ("Meier", "Maier", "Mayer", "Mayr") AND kFemale = 0 AND kOrt = "Kapfenberg"; /*-------------------------*/ /*Übung 10*/ /*10/1 Welcher Kunde hat noch nie etwas gekauft?*/ SELECT Kunde.KNr, kNName, kVName, kOrt, kPLZ, kStrasse, kFemale, BNr, bRNr FROM Kunde LEFT JOIN Bestellung ON Kunde.KNr = Bestellung.kNr WHERE BNr IS NULL; /*10/2 Welche Kunden haben etwas bestellt aber die Rechnung ist noch nicht gelegt?*/ INSERT INTO Bestellung values (null, 2, 4, null, DEFAULT, DEFAULT, 0.00, "Nixstraße 001"); SELECT Kunde.KNr, kNName, kVName, kOrt, kPLZ, kStrasse, kFemale, BNr, bRNr FROM Bestellung LEFT JOIN Kunde ON Bestellung.kNr = Kunde.KNr WHERE bRNr IS NULL; /*10/3 Welcher Mitarbeiter betreut welche Kunden?*/ /*SELECT Mitarbeiter.MNr, mNName, mVName, Kunde.KNr, kNName, kVName*/ SELECT BNr, Mitarbeiter.MNr, Bestellung.mNr, mNName, mVName, Kunde.KNr, kNName, kVName FROM (Mitarbeiter INNER JOIN Bestellung ON Mitarbeiter.MNr = Bestellung.mNr) INNER JOIN Kunde ON Kunde.KNr = Bestellung.kNr; /*10/4 Wann wurde ein bestimmter Artikel X (z.B. Mantel) das letzte mal verkauft?*/ INSERT INTO Bestellung values (null, 2, 1, 103, "2022-02-14", DEFAULT, 0.00, "Neuweg 4"), (null, 3, 3, 104, "2021-07-17", DEFAULT, 0.00, "Testgasse 34"); SELECT Artikel.ANr, aBez, bDatum FROM (Artikel INNER JOIN Positionen ON Artikel.ANr=Positionen.aNr) INNER JOIN Bestellung ON Bestellung.BNr=Positionen.bNr WHERE aBez = "Gamingtisch3000" ORDER BY Bestellung.bDatum ASC; /*10/5 Welche Artikel werden mit mehr als 50% Rabatt verschleudert*/ INSERT INTO Positionen values (null, 4, 3, 7, 120.99, 0.70); SELECT * From Positionen; SELECT PNr, Artikel.ANr, Positionen.aNr, aBez, aPreis, pRabatt FROM Artikel INNER JOIN Positionen ON Artikel.ANr=Positionen.aNr WHERE pRabatt > 0.5; /*10/6 Wieviel %Rabatt hat ein bestimmter Kunde aus Ihrer Datenbank auf einen bestimmten Artikel bei seinem letzten Einkauf erhalten?*/ SELECT Kunde.KNr, kNName, kVName, pRabatt, aBez, bRNr, bRDatum FROM ((Artikel INNER JOIN Positionen ON Artikel.ANr = Positionen.aNr) INNER JOIN Bestellung ON Positionen.bNr = Bestellung.BNr) INNER JOIN Kunde ON Kunde.KNr = Bestellung.kNr WHERE Kunde.KNr = 3 AND aBez = "GPU" ORDER BY Bestellung.bRDatum DESC;