Datenbanken
Werbung
Datenbanken – Übung – Ü00 SQL Abf SQL-Abfragen Dr.-Ing. Peter Steininger Steinbuch Centre for Computing (SCC) www.kit.edu KIT – die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) Datenbankschema – Supermarkt tbl_Waren tbl_Verkauf Spaltenname Datentyp NULL-Werte zulassen Spaltenname Datentyp id bigint id bigint strWarenName nvarchar(100) dtDate datetime mPreis money fk_Waren bigint fk_Warengruppe bigint intMenge int NULL-Werte zulassen tbl_warengruppe Spaltenname Datentyp id bigint strWarengruppenName nvarchar(50) 13709030 DBMS Dr. Peter Steininger NULL-Werte zulassen 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 1 Supermarkt – Tabellendaten I/II tbl_Waren id ----------1 2 3 4 5 6 7 strWarenName ----------------------------Papiertaschentücher Erdbeer Erbsen Bohnen Elmex Birne Apfel mPreis --------------------1,99 0,99 1,59 2,35 3,12 3,34 1,29 fk_Warengruppe -------------1 2 6 6 5 1 2 tbl_Verkauf id ----------1 2 3 4 5 6 7 13709032 DBMS dtDate ----------------------2009-01-10 19:47:29.227 2009-01-10 19:47:36.680 2009-01-10 19:47:46.403 2009-01-10 19:47:54.440 2009-01-10 19:48:04.110 2009-01-10 19:48:13.113 2009-01-10 19:48:24.153 Dr. Peter Steininger fk_Waren ----------3 2 7 3 2 5 1 intMenge ----------2 5 10 2 10 2 3 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) Supermarkt – Tabellendaten II/II tbl_Warengruppe id ----------1 2 3 4 5 6 13709033 DBMS strWarengruppenName -------------------------------------------------Papier Obst Marmelade Honig Zahnpasta Tiefkühlprodukt Dr. Peter Steininger 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 2 Datenbankschema – Supermarkt (erweitert) tbl_Waren tbl_Verkauf Spaltenname Datentyp NULL-Werte zulassen Spaltenname Datentyp id bigint id bigint strWarenName tW N nvarchar(100) h (100) dtD t dtDate d t ti datetime mPreis money fk_Waren bigint fk_Warengruppe bigint intMenge int fk_Mitarbeiter bigint tbl_warengruppe tbl_Mitarbeiter Spaltenname Datentyp NULL-Werte zulassen Spaltenname Datentyp id bi i t bigint id bi i t bigint strWarengruppenName nvarchar(50) StrVerkaeufer nchar(100) 13709031 DBMS NULL-Werte zulassen Dr. Peter Steininger NULL-Werte zulassen KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 20090405 Supermarkt (erweitert) – Tabellendaten I/II tbl_Verkauf id ----------1 2 3 4 5 6 7 dtDate ----------------------2009 01 10 19:47:29.227 2009-01-10 2009-01-10 19:47:36.680 2009-01-10 19:47:46.403 2009-01-10 19:47:54.440 2009-01-10 19:48:04.110 2009-01-10 19:48:13.113 2009-01-10 19:48:24.153 fk_Waren ----------3 2 7 3 2 5 1 intMenge ----------2 5 10 2 10 2 3 fk_Mitarbeiter -------------101 103 100 105 100 101 102 tbl_Mitarbeiter id ----------100 101 102 103 104 105 13709034 DBMS strVerkaeufer -------------------------------------------------Claudia Hans Dieter Maria Simone Elly Dr. Peter Steininger 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 3 SQL-Abfragen schreiben I/V Ausgabe der Produkte, Preise und der Warengruppe sortiert nach Preis, wie im Bsp.: Produkt Preis Warengruppe ------------------------------------------ -------------------------------------Birne 3,34 3 34 Papier Elmex 3,12 Zahnpasta Bohnen 2,35 Tiefkühlprodukt Papiertaschentücher 1,99 Papier Erbsen 1,59 Tiefkühlprodukt Apfel 1,29 Obst Erdbeer 0,99 Obst 13709035 DBMS Dr. Peter Steininger 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) SQL-Abfragen schreiben I/V – Lösung select tbl_Waren.strWarenName as Produkt, tbl_Waren.mPreis as Preis, tbl Warengruppe.strWarengruppenName as Warengruppe from tbl_Waren, tbl_Warengruppe where tbl_Waren.fk_Warengruppe = tbl_Warengruppe.id order by mPreis desc 13709040 DBMS Dr. Peter Steininger 20090531 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 4 SQL-Abfragen schreiben II/V Ausgabe der Warengruppen mit deren Umsätzen, sortiert nach Warengruppen, wie im Bsp.: strWarengruppenName -------------------------------------------------Obst Papier Tiefkühlprodukt Zahnpasta 13709036 DBMS Dr. Peter Steininger Umsatz --------------------27,75 27 75 5,97 6,36 6,24 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) SQL-Abfragen schreiben II/V – Lösung select tbl_Warengruppe.strWarengruppenName, sum(tbl_Verkauf.intMenge*tbl_Waren.mPreis) as Umsatz from tbl_Warengruppe, tbl_Verkauf, tbl_Waren where tbl_Warengruppe.id = tbl_Waren.fk_Warengruppe and tbl_Verkauf.fk_Waren = tbl_Waren.id group by tbl_Warengruppe.strWarengruppenName order by tbl_Warengruppe.strWarengruppenName 13709041 DBMS Dr. Peter Steininger 20090531 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 5 SQL-Abfragen schreiben III/V Die Filialleitung will wissen welche Artikelgruppen noch nicht verkauft wurden. Schreiben Sie die SQL-Anweisung, die folgendes Ergebnis liefert: Warengruppen ohne Umsatz -------------------------------------------------Honig Marmelade 13709037 DBMS Dr. Peter Steininger 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) SQL-Abfragen schreiben III/V – Lösung I/II select tbl_Warengruppe.strWarengruppenName as 'Warengruppen ohne Umsatz' from tbl Warengruppe except select distinct tbl_Warengruppe.strWarengruppenName from tbl_Verkauf join tbl_Waren on tbl_Verkauf.fk_Waren = tbl_Waren.id join tbl_Warengruppe on tbl_Waren.fk_Warengruppe = tbl_Warengruppe.id 13709042 DBMS Dr. Peter Steininger 20090531 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 6 SQL-Abfragen schreiben III/V – Lösung II/II select tbl_Warengruppe.strWarengruppenName from tbl Warengruppe where tbl_Warengruppe.strWarengruppenName not in ( select tbl_Warengruppe.strWarengruppenName from tbl_Waren join tbl_Verkauf on tbl_Waren.id = tbl_Verkauf.fk_Waren join tbl_Warengruppe on tbl_Waren.fk_Warengruppe = tbl_Warengruppe.id group by tbl_Warengruppe.strWarengruppenName ) 13709043 DBMS Dr. Peter Steininger 20090531 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) SQL-Abfragen schreiben IV/V Schreiben Sie eine SQL-Anweisung, die alle Verkäufe in folgender Art ausgibt: intMenge ----------2 5 10 2 10 2 3 13709038 DBMS strWarenName mPreis Warenwert ----------------------------------------- --------------------Erbsen 1,59 3,18 1 59 3 18 Erdbeer 0,99 4,95 Apfel 1,29 12,90 Erbsen 1,59 3,18 Erdbeer 0,99 9,90 Elmex 3,12 6,24 Papiertaschentücher 1,99 5,97 Dr. Peter Steininger 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 7 SQL-Abfragen schreiben IV/V – Lösung select tbl_Verkauf.intMenge, tbl_Waren.strWarenName, tbl Waren.mPreis, (tbl_Waren.mPreis * tbl_Verkauf.intMenge) as Warenwert from tbl_Verkauf join tbl_Waren on tbl_Verkauf.fk_Waren = tbl_Waren.id 13709044 DBMS Dr. Peter Steininger 20090531 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) SQL-Abfragen schreiben V/V Schreiben Sie eine SQL-Anweisung, die für einen vorgegebenen Mitarbeiter alle Verkäufe wie im folgenden Beispiel ausgibt: strWarenName strWarengruppenName intMenge ------------------------------------------------------------------ ----------Apfel Obst 10 Erdbeer Obst 10 13709039 DBMS Dr. Peter Steininger 20090405 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 8 SQL-Abfragen schreiben V/V – Lösung select tbl_Waren.strWarenName, tbl_Warengruppe.strWarengruppenName, tbl Verkauf.intMenge from tbl_Verkauf join tbl_Mitarbeiter on tbl_Verkauf.fk_Mitarbeiter = tbl_Mitarbeiter.id join tbl_Waren on tbl_Waren.id = tbl_Verkauf.fk_Waren join tbl_Warengruppe on tbl_Warengruppe.id = tbl_Waren.fk_Warengruppe where tbl_Mitarbeiter.strVerkaeufer = 'MITARBEITER'; 13709045 DBMS Dr. Peter Steininger 20090531 KIT - die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH) 9
