SQL Aufgaben (Einstieg)

[Monsieur Bate]

Hi,seit zwei Tagen versuche ich mich an SQL. Könntet ihr mir zu den folgenden Aufgaben ein Lösungsansätze geben, leider fehlt mir der Umgang mit SQL noch schwer, da die Logik von Programmiersprachen stark abweicht. Logisch, ist ja auch keine Programmiersprache. Ich benutze MySQL-Workbench.

Aufgaben:
1. Selektiere den Kunden und seine Lieblingspizza, die er am häufigsten bestellt hat.
2. Welche Pizza hat die meisten Zutaten?
3. Welche Pizza ist teurer als 9€ in normaler Größe?
4. In welchen Städten gibt es Kunden (jede Stadt nur einmal listen).
5. Was kostet die Bestellung, des Kunden mit der Email Adresse „a@f“.
6. Was ist der Durschnittspreis aller Bestellungen.
7. Welche ist die teuerste Bestellung?
8. Zeige alle Kunden an, deren Bestellwert über 15€ liegt.
9. Liste alle Pizzen, nach normalem Preis aufsteigend sortiert, auf.

Bearbeitet 7. Februar 2018 von Monsieur Bate

[Rienne]

Es wäre vermutlich hilfreich, wenn du auch den Aufbau der Datenbank posten könntest; sprich welche Tabellen mit welchen Spalten (und Keys).

[Mttkrb]

Hi,
wie Rienne schon angemerkt hat, fehlt uns die Struktur der Datenbank.
Ohne die DB-Struktur zu kennen ist Nr.9 ein einfaches Select mit einem OrderBy am ende. 

Eine Genauere Lösung kann man mit den Momentanen Infos nicht nennen.

Da du seit zwei Tagen schon dran bist, wie sehen deine bisherigen Ansätze zu den Aufgaben aus? Vielleicht stimmen die schon fast und benötigen nur noch etwas Feinschliff.

[Monsieur Bate]

PIZZA

ID	Int(16)
NAME	Text (30)
TEXT	TEXT(80)
PREIS_NORMAL	DEZ(4,2)
PREIS_FAMILY	DEZ(4,2)

 

KUNDE

ID	INT(16)
EMAIL	Text (50)
PW	Text (16)
NN	Text (30)
VN	Text (30)
STR	Text (30)
NR	Text (10)
PLZ	Text (5)
ORT	Text (30)
TEL	Text (30)

 

ZUTATEN

ID	INT(16)
NAME	TEXT(50)

 

ZUTATEN_LISTE

ID	INT(16)
ZUTAT	INT(16)
PIZZA	INT(16)

 

BESTELLUNG

ID	INT(16)
KUNDE	INT(16)
PIZZA	INT(16)
ANZ_NORMAL	INT(11)
ANZ_FAMILY	INT(11)

 

Was vorgegeben war:

INSERT INTO pizza VALUES (1,'Hawai','Pizza mit Salami,<br>K&auml;se und Ananas',4.00,7.50);
INSERT INTO pizza VALUES (2,'Vegetariana','vegetarische Pizza<br>mit Gr&uuml;nzeug',4.00,7.50);
INSERT INTO pizza VALUES (3,'Chicken','Pizza mit H&uuml;hnerfleisch',4.80,9.00);
INSERT INTO pizza VALUES (4,'American','Pizza mit Mais, K&auml;se,<br>Salami und Essigg&uuml;rkchen',5.50,10.50);
INSERT INTO pizza VALUES (5,'Funghi','Pizza mit Pilzen',3.50,6.00);
INSERT INTO pizza VALUES (6,'CGI','Pizza mit Perl-Zwiebeln<br>und Cookies',8.00,15.00);

INSERT INTO kunde VALUES (1,'a.fortenbacher@fhtw-berlin.de','184895e9508f3f41','Fortenbacher','Albrecht','An der Forst','12','15370','Petershagen','033439 77907');
INSERT INTO kunde VALUES (2,'Claudia@Volk.de','6036ac6925245ec8','Volk','Claudia','Sonnenallee','20','12345','Berlin','030 88775');
INSERT INTO kunde VALUES (3,'a@f','077baa8f491e1489','f','a','adf','12','15370','p','011');
INSERT INTO kunde VALUES (4,'cristina.dammer@web.de','398e7500242ab90c','Dammer','Cristina','An der Forst','12','15370','Petershagen','033439 77907');

INSERT INTO zutaten VALUES (1, 'Käse');
INSERT INTO zutaten VALUES (2, 'Tomaten');
INSERT INTO zutaten VALUES (3, 'Salami');
INSERT INTO zutaten VALUES (4, 'Pilze');
INSERT INTO zutaten VALUES (5, 'Ananas');
INSERT INTO zutaten VALUES (6, 'Hühnerflleisch');
INSERT INTO zutaten VALUES (7, 'Mais');
INSERT INTO zutaten VALUES (8, 'Essiggurken');
INSERT INTO zutaten VALUES (9, 'Zwiebeln');
INSERT INTO zutaten VALUES (10, 'Cookies');

INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (1, 1, 1);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (2, 3, 1);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (3, 5, 1);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (4, 2, 2);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (5, 4, 2);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (6, 5, 2);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (7, 7, 2);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (16, 8, 2);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (17, 9, 2);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (8, 6, 3);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (9, 1, 4);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (10, 3, 4);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (11, 7, 4);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (12, 8, 4);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (13, 4, 5);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (14, 9, 6);
INSERT INTO zutaten_liste (id, zutat, pizza) VALUES (15, 10, 6);

INSERT INTO bestellung VALUES (1,3,1,2,1);
INSERT INTO bestellung VALUES (2,3,6,0,1);

 

ID = PK
bestellung_KUNDE_FK = FK
bestellung_PIZZA_FK = FK

zutaten_liste_ZUTAT_FK = FK
zutaten_liste_PIZZA_FK = FK

Bearbeitet 7. Februar 2018 von Monsieur Bate

[arlegermi]

Ein paar der Anfragen sind auch schon etwas komplexer - da würde ich mir nach zwei Tagen noch keine Gedanken machen. Was ich immer hilfreich fand, ist, die Anfragen ggf. in mehreren Schritten zu behandeln. Am Beispiel von 1 könnte man z.B. so vorgehen:

1) Wie selektiert man Kunden?

2) Wie findest du heraus, welche Pizza wie oft bestellt wurde?

2.a) Wie findest du heraus, welche Pizza am häufigsten bestellt wurde?

3) Wie findest du heraus, welche Pizzen ein Kunde bestellt hat?

Wenn du diese Teilanfragen formulieren kannst (die einfacher sind als die eigentliche Fragenstellung), dann kannst du dir Gedanken darüber machen, wie du die zusammenbauen kannst.

Zum Thema SQL und Programmiersprachen: Nur, weil das Ding Query-Language heißt, bedeutet das nicht, dass das was völlig anderes ist als eine Programmiersprache. Der Unterschied ist, dass SQL deklarativ und nicht imperativ ist - das benötigt ein gewisses Umdenken, wenn man von "normalen" Programmiersprachen kommt. Aber wenn man sich so in modernen Sprachen umguckt (bzw. Konstrukten darin), dann findet man diesen Ansatz durchaus auch (spontan wäre da LINQ in C# zu nennen, bspw.).

Das Datenbankschema, das du gerade gepostet hast, scheint mir nicht komplett zu sein. Es fehlt eine Tabelle "Bestellung" o.ä.

Bearbeitet 7. Februar 2018 von arlegermi

[Monsieur Bate]

vor 25 Minuten schrieb Mttkrb:

Da du seit zwei Tagen schon dran bist, wie sehen deine bisherigen Ansätze zu den Aufgaben aus? Vielleicht stimmen die schon fast und benötigen nur noch etwas Feinschliff.

1. ALTER TABLE zutaten_liste ADD CONSTRAINT zutaten_liste_PIZZA_FK FOREIGN KEY (PIZZA) REFERENCES pizza(ID);
ALTER TABLE zutaten_liste ADD CONSTRAINT zutaten_liste_ZUTAT_FK FOREIGN KEY (ZUTAT) REFERENCES pizza(ID);

ALTER TABLE bestellung ADD CONSTRAINT bestellung_KUNDE_FK FOREIGN KEY (KUNDE) REFERENCES bestellung(ID);
ALTER TABLE bestellung ADD CONSTRAINT bestellung_PIZZA_FK FOREIGN KEY (PIZZA) REFERENCES bestellung(ID);

2. SELECT * FROM pizza;
3. SELECT * FROM kunde;
4. SELECT * FROM zutaten;
5. SELECT * FROM zutaten_liste;
6. SELECT * FROM bestellung;

7. SELECT * FROM kunde WHERE ORT = 'Berlin';

8. DELETE FROM kunde WHERE pw;

9. SELECT COUNT(*) FROM kunde WHERE Ort = 'Petershagen';

10. SELECT MIN(PREIS_NORMAL) FROM pizza;

11. UPDATE kunde SET Ort = 'Petershagen' WHERE EMAIL = 'a@f';

12. SELECT * FROM pizza p, zutaten_liste zl, zutaten z
WHERE z.name = 'Käse' AND z.id = zl.zutat AND zl.pizza = p.id;

13. SELECT * FROM pizza p, zutaten_liste zl, zutaten z
WHERE z.name IN ('Käse', 'Salami') AND z.id = zl.zutat AND zl.pizza = p.id GROUP BY p.name;

14. SELECT CONCAT(k.vn, ' ', k.nn) AS person FROM kunde k, bestellung b
WHERE b.kunde = k.id GROUP BY person;

15. SELECT CONCAT(k.vn, ' ', k.nn) AS person, MAb.anz_normal + b.anz_family) AS total 
FROM kunde k, bestellung b
WHERE k.id = b.kunde
GROUP BY person
ORDER BY total DESC LIMIT 1;

1. Erzeuge Fremdschlüssel für die Tabellen:
- ZUTATEN_LISTE (PIZZA, ZUTAT)
- BESTELLUNG (KUNDE, PIZZA)
2. Wähle alle Einträge der Tabelle PIZZA aus.
3. Wähle alle Einträge der Tabelle KUNDE aus.
4. Wähle alle Einträge der Tabelle ZUTATEN aus.
5. Wähle alle Einträge der Tabelle ZUTATEN_LISTE aus.
6. Wähle alle Einträge der Tabelle BESTELLUNG aus.
7. Wer wohnt in Berlin?
8. Lösche die Spalte „pw“ von der Kunden Tabelle.
9. Wie viele Kunden wohnen in Petershagen?
10. Welche Pizza ist die günstigste?
11. Ändere den Wohnort des Kunden, mit der Email Adresse „a@f“, in Petershagen.
12. Selektiere alle Pizzen, die Käse als Zutat haben.
13. Selektiere alle Pizzen, die Käse und Salami als Zutat haben.
14. Selektiere die Kunden, die eine offene Bestellung haben.
15. Welcher Kunde hat die meisten Pizzen bestellt?

[Monsieur Bate]

Möchte mir niemand mehr helfen? Ich erwarte keine Lösungen, wenn ihr das denkt, mir geht es nur um Ansätze für die Aufgaben.

[Chief Wiggum]

vor 2 Minuten schrieb Monsieur Bate:

Möchte mir niemand mehr helfen?

Nicht so ungeduldig, die meisten helfen hier gerne - müssen aber auch arbeiten und haben somit nicht immer Zeit, sofort zu antworten.

[Whiz-zarD]

Verstehe ich das richtig, dass man pro Bestellung nur eine Pizza bestellen kann?

[Monsieur Bate]

vor 1 Minute schrieb Whiz-zarD:

Verstehe ich das richtig, dass man pro Bestellung nur eine Pizza bestellen kann?

Nö. Die Datenbank ist aber noch nicht in der Anwendung eingebunden, in welchem ich das vorgebe. Die Aufgaben zielen erst mal nur auf die SQL-Querys.

[Rienne]

@Whiz-zarD, so wie @Monsieur Bate die Datenbank dargestellt hat, ist es, zumindest für mein Verständnis, schon möglich mehrere Pizzen zu bestellen, aber nur eine Sorte. Oder nicht? ^^'

Was fehlt ist eine Art Posten-Rechnung-Tabelle, die einer Bestellung die verschiedenen Posten (in diesem Fall Pizzen) zuordnet.

Bearbeitet 7. Februar 2018 von Rienne

[Whiz-zarD]

vor 3 Minuten schrieb Rienne:

@Whiz-zarD, so wie @Monsieur Bate die Datenbank dargestellt hat, ist es, zumindest für mein Verständnis, schon möglich mehrere Pizzen zu bestellen, aber nur eine Sorte. Oder nicht? ^^'

Ja, so meinte ich das. Pro Bestellung nur eine Sorte aber dafür eine gewisse Anzahl.

[Goulasz]

Moin @Monsieur Bate!

Guck dir zum Einstieg mal SQL Island an. Selbst arbeiten bringt mehr als Krempel vorgekaut bekommen und dann versuchen, das nachzuvollziehen.

Wenn du das durch hast und dann noch Fragen offen sind, guck nochmal rein.  

Gruß, Goulasz  

[maestro impostor]

vor 2 Stunden schrieb Monsieur Bate:

Möchte mir niemand mehr helfen? Ich erwarte keine Lösungen, wenn ihr das denkt, mir geht es nur um Ansätze für die Aufgaben.

Wobei brauchst du den jetzt noch konkret Hilfe?
Du hast im vorherigen Post bereits einige SQL Statements gepostet - was funktioniert noch nicht?

[Monsieur Bate]

vor 23 Minuten schrieb r4phi:

Wobei brauchst du den jetzt noch konkret Hilfe?
Du hast im vorherigen Post bereits einige SQL Statements gepostet - was funktioniert noch nicht?

Kurze Erklärung der Logik, wie man die einzelnen Aufgaben "ungefähr" bearbeiten sollte.

[Whiz-zarD]

1) Es geht darum, Dinge zu gruppieren und zu sortieren. Was du gruppieren und wie du es sortieren sollst, steht doch in der Aufgabe.

2) Hier soll ebenfalls was gruppiert werden

3) Es soll über eine WHERE-Bedingung rausgefunden werden, welche Pizza teuerer als 9 € ist

4) Ebenfalls eine Gruppierung

5) Die Tabellen Bestellung und Kunde müssen gejoined werden und dann den Kunden mit der E-Mail-Adresse a@f raussuchen

6) Schau dir mal die AVG()-Funktion (= Average) an.

7) Eine Sortierung auf den Bestellungen

8) Eine Sortierung mit Filterung auf den Bestellungen

9) Genauer, wie die Aufgabe kann man es nicht mehr beschreiben, ohne die Lösung zu präsentieren

[RipperFox]

> 8. Lösche die Spalte „pw“ von der Kunden Tabelle.
DELETE ist für Zeilen, für Spalten mal ALTER TABLE ansehen.
> 12. Selektiere alle Pizzen, die Käse als Zutat haben.
> 13. Selektiere alle Pizzen, die Käse und Salami als Zutat haben.
> 14. Selektiere die Kunden, die eine offene Bestellung haben.
> 15. Welcher Kunde hat die meisten Pizzen bestellt?
Du solltest Dir mal die JOIN Syntax ansehen - es fehlen ON Clauses um die gewünschten Schnittmengen zu erhalten - Du erhältst sonst das Kreuzprodukt mit viel zu vielen Zeilen..

 

Wie @Goulasz schon schrieb: Schau Dir SQL-Island an und mach die Übungen dort..

Bearbeitet 7. Februar 2018 von RipperFox

[Monsieur Bate]

Ich möchte die Kunden und ihre Lieblingsmarke anzeigen. Da Kunde "1" lieber Hohes C trinkt, möchte ich erreichen, dass diese Marke als die Lieblingsmarke des Kunden "1" angezeigt wird und die andere Marke rausfällt. Wie kann ich das machen? Kann ich einen Vergleich zwischen den Mengen machen? Ich möchte aber, dass der Kunde "2" auch angezeigt wird, da er ja nur eine Lieblingsmarke hat.

BESTELLUNG

Kunde	Marke	O-Saft 1l
1	Hohes C	3
1	Grannini	1
2	Rauch	4

 

 

Bearbeitet 7. Februar 2018 von Monsieur Bate

[Goulasz]

Hallo @Monsieur Bate!

Hast du das Spiel schon gespielt? 

vor 1 Stunde schrieb Monsieur Bate:

[...]Da Kunde "1" lieber Hohes C trinkt, möchte ich erreichen, dass diese Marke als die Lieblingsmarke des Kunden "1" angezeigt wird und die andere Marke rausfällt. Wie kann ich das machen?[...]

Mit den richtigen SQL-Abfragen. 

vor 1 Stunde schrieb Monsieur Bate:

[...]Kann ich einen Vergleich zwischen den Mengen machen?[...]

Ja. COUNT, AVG, SUM-Funktionen bieten dir gute Möglichkeiten, z.B. Summen zu ermitteln.

vor 1 Stunde schrieb Monsieur Bate:

[...]Ich möchte aber, dass der Kunde "2" auch angezeigt wird, da er ja nur eine Lieblingsmarke hat.[...]

Ja, geht auch. Guck dir einfach mal die Grundlagen an und recherchier ordentlich nach den Funktionen.

Gruß, Goulasz  

P.S.: SEHE ICH DA EIN LEERZEICHEN IM SPALTENNAMEN? 

 

Bearbeitet 7. Februar 2018 von Goulasz

[Monsieur Bate]

vor 50 Minuten schrieb Goulasz:

P.S.: SEHE ICH DA EIN LEERZEICHEN IM SPALTENNAMEN? 

 

Alles gut, nicht aufregen. 

Aber ernsthaft, ich habe extrem viel im Internet nachgeschaut. Ich weiß wirklich nicht, wie man zwei Datensätze miteinander vergleicht, um dann das mit der größeren Menge anzeigen zu lassen.

BTW. Ich habe alles Aufgaben gelöst, mir fehlt tatsächlich nur noch die Aufgabe. Keiner Ahnung, was an ihr so schwer ist, aber ich hänge schon seit vorgestern an der Kack Aufgabe. Alle anderen habe ich hinbekommen, nur die nicht.

 

Bearbeitet 7. Februar 2018 von Monsieur Bate

[Whiz-zarD]

Du verstehst die Aufgabe nicht richtig

Du sollst nicht die Kunden selektieren, sondern den einen Kunden, der am meisten seine lieblingspizza bestellt hat. Am Ende soll also nur eine einzige Ergebniszeile rauskommen.