SELECT mit Sub
Werbung
SQL-Übung zur Datenbank Beschaffung Folgende Datenbank soll ausgewertet werden: 1. Lassen Sie sich alle Artikel mit Bezeichnungen und Artikelnummern anzeigen. [68 DS] SELECT Artikel.Artikelnummer, Artikel.Artikelbezeichnung FROM Artikel; 2. Lassen Sie sich alle Artikel (siehe Aufgabe 1) anzeigen, allerdings nur die mit dem Warengruppenschlüssel 2. [23 DS] SELECT Artikel.Artikelnummer, Artikel.Artikelbezeichnung FROM Artikel WHERE Warengruppenschlüssel=2; 3. Lassen Sie sich in Erweiterung der Aufgabe 2 nun auch die jeweiligen Preise mit anzeigen. [23 DS] SELECT Artikel.Artikelnummer, Artikel.Artikelbezeichnung, Artikel_Lieferer.Preis FROM Artikel, Artikel_Lieferer WHERE Warengruppenschlüssel=2 AND Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer; 4. Lassen Sie sich alle Artikel mit der Artikelnummer, Artikelbezeichnung und Lieferer incl. Lieferantennummer für die Warengruppe 1 (Computer) anzeigen. [13 DS] SELECT Artikel.Artikelnummer, Artikel.Artikelbezeichnung, Artikel_Lieferer.Lieferantennummer, Lieferer.Firmenname FROM Artikel, Artikel_Lieferer, Warengruppe, Lieferer WHERE Artikel.Artikelnummer = Artikel_Lieferer.Artikelnummer AND Artikel.Warengruppenschlüssel=Warengruppe.Warengruppenschlüssel AND Artikel_Lieferer.Lieferantennummer=Lieferer.Lieferernummer AND Warengruppe.Warengruppenschlüssel=1; 5. Lassen Sie sich alle Artikel mit der Artikelnummer, Bezeichnung, Ist- und Mindestbestand und Lieferer anzeigen, die bestellt werden müssen. Es soll eine Bestellmenge vorgeschlagen werden (Differenz aus Mindest- und Istbestand zuzüglich 50% des Mindestbestands). [8 DS] SELECT Artikel.Artikelnummer, Artikel.Artikelbezeichnung, Artikel_Lieferer.Lieferantennummer, Lieferer.Firmenname, Artikel.Istbestand, Artikel.Mindbestand, (Artikel.Mindbestand-Artikel.Istbestand)+0.5*Artikel.Mindbestand AS Bestellvorschlag FROM Artikel, Artikel_Lieferer, Lieferer WHERE Artikel.Artikelnummer = Artikel_Lieferer.Artikelnummer AND Artikel_Lieferer.Lieferantennummer=Lieferer.Lieferernummer AND Artikel.Mindbestand > Artikel.Istbestand; 6. Erstellen Sie eine neue Tabelle Auslaufartikel_Nachname (Ihr Nachname, damit jeder seine eigene Tabelle erstellt!), die den gleichen Aufbau wie die Artikeltabelle haben soll. CREATE TABLE Auslaufartikel_Nachname ( Artikelnummer int(11), Artikelbezeichnung varchar(30), Steuerschlüssel int(11), Warengruppenschlüssel int(11), Mindbestand int(11), Istbestand int(11), PRIMARY KEY (Artikelnummer) ); 7. Da demnächst alle Artikel, die zur Gruppe der Büromöbel oder zur Gruppe Sonstiges gehören, aus dem Sortiment genommen und somit gelöscht werden, sollen Sie diese Datensätze jetzt in die soeben erstellte Auslauf_Nachname-Tabelle übernehmen. [ 13 DS] INSERT INTO Auslaufartikel_Nachname SELECT * FROM Artikel WHERE Artikel.Warengruppenschlüssel=4 OR Artikel.Warengruppenschlüssel=5; Oder INSERT INTO Auslaufartikel_Nachname SELECT Artikel.* FROM Artikel, Warengruppe WHERE Artikel.Warengruppenschlüssel = Warengruppe.Warengruppenschlüssel AND (Warengruppe.Warengruppenbezeichnung = 'Büromöbel' OR Artikel.Warengruppenschlüssel=5); 8. Eigentlich sollten jetzt in der Artikel-Tabelle alle betroffenen Datensätze gelöscht werden. Da diese aber hier nur einmal existiert, löschen Sie bitte alle Büromöbel aus der Auslauf_Nachname-Tabelle, die Sie angelegt haben! [7 DS] DELETE FROM Auslaufartikel_Nachname WHERE Auslaufartikel_Nachname.Warengruppenschlüssel=4; oder DELETE Auslaufartikel_Nachname.* FROM Auslaufartikel_Nachname, Warengruppe WHERE Auslaufartikel_Nachname.Warengruppenschlüssel = Warengruppe.Warengruppenschlüssel AND Warengruppe.Warengruppenbezeichnung='Büromöbel' 9. Setzen Sie den Istbestand aller Artikel in der Auslaufartikel_NachnameTabelle auf 0! UPDATE Auslaufartikel_Nachname SET Istbestand=0; 10. Löschen Sie unsere Übungstabelle Auslaufartikel_Nachname! DROP TABLE Auslaufartikel_Nachname; 11. Summieren Sie den Gesamtwert der gelagerten Ware. Nutzen Sie hierzu eine Funktion, die in SQL bereitgestellt wird. SELECT sum(Artikel.Istbestand*Artikel_Lieferer.Preis) FROM Artikel, Artikel_Lieferer WHERE Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer; [937.166,45084083] 12. Führen Sie Aufgabe 10 noch einmal durch. Jetzt soll aber für jede einzelne Warengruppe der Warenwert ermittelt werden (sog. Gruppierung) und die Ergebnisse die Spaltenüberschrift Lagerwerte je Warengruppe erhalten! SELECT sum(Artikel.Istbestand*Artikel_Lieferer.Preis) AS Werte_je_Warengruppe FROM Artikel, Artikel_Lieferer WHERE Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer GROUP BY Artikel.Warengruppenschlüssel; Werte_je_Warengruppe 176.622,001190186 271.102,999967098 246.500,949792504 222.118,999999762 20.821,4998912811 13. Zusatzaufgabe: Lassen Sie sich für die einzelnen Warengruppen die absoluten und prozentualen Teilwerte anzeigen. SELECT mit Sub-SELECT! SELECT Artikel.Warengruppenschlüssel, sum(Artikel.Istbestand*Artikel_Lieferer.Preis) AS Teilwerte, sum(Artikel.Istbestand*Artikel_Lieferer.Preis)*100 / (select sum(Artikel.Istbestand*Artikel_Lieferer.Preis) FROM Artikel, Artikel_Lieferer WHERE Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer) AS Prozentwerte FROM Artikel, Warengruppe, Artikel_Lieferer WHERE Artikel.Warengruppenschlüssel=Warengruppe.Warengruppenschlüssel AND Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer GROUP BY Artikel.Warengruppenschlüssel WarengruppenTeilwerte Prozentwerte schlüssel 1 176622.001190186 18.846385402691 2 271102.999967098 28.927945481175 3 246500.949792504 26.302792803919 4 222118.999999762 23.701125856615 5 20821.4998912811 2.2217504555994 Warengruppenschlüssel Teilwerte Prozentwerte 1 176.622,001190186 18,846385402691 2 271.102,999967098 28,927945481175 3 246.500,949792504 26,302792803919 4 222.118,999999762 23,701125856615 5 20.821,4998912811 2,2217504555994 SELECT nach MYSQL ohne Sub-Select und Alias-Werte (Hilfstabelle) CREATE TABLE Hilfstabelle_Gesamtwert ( Gesamtwert DOUBLE ); INSERT INTO Hilfstabelle_Gesamtwert SELECT sum(Artikel.Istbestand*Artikel_Lieferer.Preis) FROM Artikel, Artikel_Lieferer WHERE Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer; SELECT Artikel.Warengruppenschlüssel, sum(Artikel.Istbestand*Artikel_Lieferer.Preis) AS Teilwerte, sum(Artikel.Istbestand*Artikel_Lieferer.Preis)*100 / Hilfstabelle_Gesamtwert.Gesamtwert AS Prozentwerte FROM Artikel, Artikel_Lieferer, Hilfstabelle_Gesamtwert WHERE Artikel.Artikelnummer=Artikel_Lieferer.Artikelnummer GROUP BY Artikel.Warengruppenschlüssel; SQL-Übung zur Datenbank Beschaffung Folgende Datenbank soll ausgewertet werden: 1. Lassen Sie sich alle Artikel mit Bezeichnungen und Artikelnummern anzeigen. [68 DS] 2. Lassen Sie sich alle Artikel (siehe Aufgabe 1) anzeigen, allerdings nur die mit dem Warengruppenschlüssel 2. [23 DS] 3. Lassen Sie sich in Erweiterung der Aufgabe 2 nun auch die jeweiligen Preise mit anzeigen. [23 DS] 4. Lassen Sie sich alle Artikel mit der Artikelnummer, Artikelbezeichnung und Lieferer incl. Lieferantennummer für die Warengruppe 1 (Computer) anzeigen. [13 DS] 5. Lassen Sie sich alle Artikel mit der Artikelnummer, Bezeichnung, Ist- und Mindestbestand und Lieferer anzeigen, die bestellt werden müssen. Es soll eine Bestellmenge vorgeschlagen werden (Differenz aus Mindest- und Istbestand zuzüglich 50% des Mindestbestands). [8 DS] 6. Erstellen Sie eine neue Tabelle Auslaufartikel_Nachname (Ihr Nachname, damit jeder seine eigene Tabelle erstellt!), die den gleichen Aufbau wie die Artikeltabelle haben soll. 7. Da demnächst alle Artikel, die zur Gruppe der Büromöbel oder zur Gruppe Sonstiges gehören, aus dem Sortiment genommen und somit gelöscht werden, sollen Sie diese Datensätze jetzt in die soeben erstellte Auslaufartikel_Nachname-Tabelle übernehmen. [ 13 DS] 8. Eigentlich sollten jetzt in der Artikel-Tabelle alle betroffenen Datensätze gelöscht werden. Da diese aber hier nur einmal existiert, löschen Sie bitte alle Büromöbel aus der Auslaufartikel_Nachname-Tabelle, die Sie angelegt haben! [7 DS] 9. Setzen Sie den Istbestand aller Artikel in der Auslaufartikel_NachnameTabelle auf 0! 10. Löschen Sie unsere Übungstabelle Auslaufartikel_Nachname! 11. Summieren Sie den Gesamtwert der gelagerten Ware. Nutzen Sie hierzu eine Funktion, die in SQL bereit gestellt wird. 12. Führen Sie Aufgabe 10 noch einmal durch. Jetzt soll aber für jede einzelne Warengruppe der Warenwert ermittelt werden (sog. Gruppierung) und die Ergebnisse die Spaltenüberschrift Lagerwerte je Warengruppe erhalten! 13. Zusatzaufgabe: Lassen Sie sich für die einzelnen Warengruppen die absoluten und prozentualen Teilwerte anzeigen.
