· 9 years ago · Nov 22, 2016, 03:28 PM
1-- foodware
2-- 1. Erstellen Sie eine Abfrage zur Tabelle "Artikel". Die Abfrage enthält die Felder:
3 -- Artikel_Nr, Artikelname, Lagerbestand, Liefereinheit, Einzelpreis
4
5SELECT A.Artikel_Nr, A.Artikelname, A.Lagerbestand, A.Liefereinheit, A.Einzelpreis FROM artikel A;
6
7-- 2. Erstellen Sie eine neue Abfrage zur Tabelle "Personal". Die Felder der Abfrage sind:
8 -- Personal_nr, Nachname, Vorname, Strasse, PLZ, Ort, Land.
9 --Sortieren Sie die Abfrage aufsteigend nach dem Ort.
10SELECT Personal_nr, Nachname, Vorname, Strasse, PLZ, Ort, Land FROM personal ORDER BY Ort;
11
12-- 3. erstellen Sie eine Abfrage zur Tabelle "Bestellungen". Die Felder der Abfrage sind:
13 --Bestell_nr, Bestelldatum, Lieferdatum, Versanddatum, Kunden_Nr,
14 --Benötigt werden nur die Datensaätze mit der Kunden_nr "LINOD". Sortieren Sie die Abfrage nach dem Bestelldatum aufsteigend.
15
16SELECT B.Bestell_nr, B.Bestelldatum, B.Lieferdatum, B.Versanddatum, B.Kunden_Nr FROM Bestellungen B WHERE Kunden_Nr = "LINOD" ORDER BY B.Bestelldatum;
17
18-- 4. Erstellen Sie eine Abfrage zur Tabelle "Bestelldetails". Lassen Sie alle Datensätze anzeigen, deren Anzahl über 20 liegt. Die Abfrage enthält alle Felder aus der Tabelle.
19
20SELECT * FROM Bestelldetails WHERE Anzahl > 20 ORDER BY Anzahl DESC;
21
22-- 5. Eine neue Abfrage zur Tabelle "Lieferanten" soll die Felder:
23 -- Lieferanten_Nr, Firma, Ort zeigen.
24 -- Benötigt werden nur Lieferanten aus Cuxhaven bzw Ravenna.
25
26SELECT Lieferanten_Nr, Firma, Ort FROM Lieferanten WHERE ORT IN ('Cuxhaven','Ravenna');
27
28-- 6. In der nächsten Abfrage werden Informationen aus der Tabelle "Artikel" ausgewählt.
29 -- Von den Artikeln der Lieferanten_Nr 6 bzw 8 mit einem Lagerbestand über 30 werden die Felder
30 -- Artikel_nr, Artikelname, Lieferanten_Nr, Lagerbestand angezeigt.
31 -- Die Abfrage ist aufsteigend sortiert nach Artikelname.
32
33SELECT Artikel_nr, Artikelname, Lieferanten_Nr, Lagerbestand
34FROM Artikel WHERE Lieferanten_Nr IN ('6','8')
35AND Lagerbestand > 30 ORDER BY Artikelname;
36
37-- 7. Erstellen Sie eine Abfrage mit Feldern aus der Tabelle "Artikel":
38 -- Artikel_Nr, Artikelname, Lieferanten_Nr
39 -- und den Feldern Firma, Ort, Telefon aus der Tabelle "Lieferanten"
40
41SELECT A.Artikel_Nr, A.Artikelname, A.Lieferanten_Nr, L.Firma, L.Ort, L.Telefon FROM Artikel A JOIN Lieferanten L ON (A.Lieferanten_NR=L.Lieferanten_NR);
42
43-- 8. Erstellen Sie eine Abfrage zur Tabelle "Kunden" mit den Feldern:
44 -- Kunden_nr, Firma, ORT
45 --Es sollen nur Kunden angezeigt werden, deren Firmenname mit S beginnt.
46SELECT Kunden_NR, Firma, Ort FROM Kunden WHERE Firma LIKE 'S%';
47
48-- 9. eine Abfrage enthält aus der Tabelle "Lieferanten" die Felder:
49 -- Lieferanten_NR, Firma, Kontaktperson, Position, Ort.
50 -- Sortieren Sie die Abfrage nach dem Ort aufsteigend.
51 -- Als Ergebnis werden nur Lieferanten aus Tokyo oder Melbourne benötigt.
52 -- deren Kontaktperson die Position Marketingmanager hat.
53
54SELECT Lieferanten_NR, Firma, Kontaktperson, Ort FROM Lieferanten WHERE Ort IN ('Tokyo','Melbourne') AND Position = 'Marketingmanager';
55
56-- 10. Lassen Sie in einer Abfrage zur Tabelle "Bestellungen" alle Datensätze anzeigen, deren Bestelldatum nach dem 31.03.2007 liegt.
57 -- Die Abfrage enthält die Felder Bestell_nr, Kunden_NR, Bestelldatum, Lieferdatum
58
59SELECT Bestell_nr, Kunden_NR, Bestelldatum, Lieferdatum FROM Bestellungen WHERE Bestelldatum > ('2007-03-31');
60
61-- 11. Zur Überarbeitung des Datenbestandes, werden alle Kundendatensätze benötigt, bei denen die Faxnummer fehlt.
62 -- Legen Sie selbst fest, welche Felder die Abfrage enthalten soll.
63
64SELECT * FROM Kunden WHERE TELEFAX IS NULL;
65
66-- 12. Lassen Sie in einer Abfrage alle Bestellungen mit einer Bestellnummer zwischen
67 -- 10400 und 10500 anzeigen.
68SELECT * FROM Bestellungen WHERE Bestell_nr BETWEEN 10400 AND 10500;
69
70-- 13. Eine Abfrage soll den Lagermeister informieren bei welchen Artikeln die bestellten Einheiten größer als 50 sind. Felder in der Abfrage:
71 -- Artikelnummer, Artikelname, Liefereinheit, Lagerbestand und Bestellte Einheiten
72
73SELECT Artikel_Nr, Artikelname, Liefereinheit, Lagerbestand, Bestellte_Einheiten FROM Artikel WHERE Bestellte_Einheiten > 50;
74
75-- 14. Der Einkaufsleiter möchte wissen welche Bestellungen von der Person mit der Personalnummer "4" augenommen wurden. Angezeigt werden sollen folgende Felder:
76 -- Personalnummer, Empfänger, Ort, Bestelldatum.
77 -- Sortierng nach der Kundennummer aufsteigend, die aber nicht mit angezeigt werden soll.
78
79SELECT Personal_Nr, Empfaenger, Ort, Bestelldatum, Kunden_Nr FROM Bestellungen WHERE Personal_Nr =4 ORDER BY Kunden_Nr;
80
81-- 15. Die Geschäftslaage soll regelmäßig beurteilt werden. Dazu wird eine Abfrage auf alle Bestellungen , die in diesem Jahr eingegangen sind benötigt.
82
83SELECT Bestelldatum from bestellungen where Bestelldatum LIKE '2007%';
84
85-- 16. Das Lager möchte wissen welche Artikel in Kartons geliefert werden?
86 -- es sollen die felder Artikelnummer, Artikelname, und Liefereinheiten angezeigt werden.
87
88SELECT Artikel_Nr, artikelname, Liefereinheit FROM artikel WHERE Liefereinheit LIKE "%Kartons%";
89
90-- 17. Die Personalabteilung benötigt die Information, welcher Itarbeiter für welche Bestellungen zuständig ist.
91 -- Die Aufstellung soll aus der Tabelle Personal die Felder
92 -- Personalnummer, Nacname, und Vorname enthalten, aus der Tabelle Bestellungen die Felder
93 -- Bestell_Nr, Empfaenger, und Ort.
94
95SELECT P.personal_nr, P.Nachname, P.Vorname, B.bestell_nr, B.Empfaenger, B.Ort FROM personal P
96JOIN Bestellungen B ON (P.Personal_Nr = B.Personal_Nr);
97
98-- 18. Erstellen Sie mit SQL in der Datenbank foodware die Tabelle "Abteilung" mit den Feldern:
99 -- Abt_Nr, text, Feldgröße 3, Primärschlüssel
100 -- AbtName, text, Feldgröße 40, Eingabe erforderlich, eindeutiger Index.
101
102 -- Die AbtNr muss immer aus einem Großbuchstaben gefolgt von zwei ziffern bestehen zB."A12" -- Kann ich das erzwingen, abseits von concat auto incremetn sstütze?
103
104CREATE TABLE IF NOT EXISTS Abteilung (
105Abt_Nr VARCHAR(3),
106AbtName VARCHAR(40)
107) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
108
109
110ALTER TABLE Abteilung
111 ADD PRIMARY KEY (Abt_Nr),
112 ADD UNIQUE (AbtName),
113 modify column AbtName varchar(40) not null;
114
115-- 19. Fügen Sie einige Abteilungen in die Tabele ein:
116
117INSERT INTO Abteilung VALUES
118("A01", "Geschäftsleitung"),
119("A02", "Marketing"),
120("A03", "Verkauf"),
121("A04", "Einkauf"),
122("A05", "Lager"),
123("A06", "Buchhaltung");
124
125-- 20. Mit einem SQL-Statement soll die tabelle Personal um das Feld Abt-Nr ergänz werden.
126
127ALTER TABLE Personal
128ADD Abt_Nr VARCHAR(3);
129
130-- 21. Per SQL-Statement soll nun das Personal den Abteilunge zugeordnet werden.
131
132UPDATE personal set Abt_Nr="A03" WHERE Personal_nr = "1";
133UPDATE personal set Abt_Nr="A01" WHERE Personal_nr = "2";
134UPDATE personal set Abt_Nr="A03" WHERE Personal_nr BETWEEN 3 AND 9;
135UPDATE personal set Abt_Nr="A01" WHERE Personal_nr = "10";
136UPDATE personal set Abt_Nr="A05" WHERE Personal_nr = "11";
137UPDATE personal set Abt_Nr="A06" WHERE Personal_nr = "12";
138UPDATE personal set Abt_Nr="A02" WHERE Personal_nr BETWEEN 13 AND 15;
139
140-- 22. Erstellen Sie eine Löschabfrage zur Tabelle "Bestelldetails". Die Abfrage löscht alle Datensaätze mit der BestellNr: 10055
141
142DELETE FROM bestelldetails WHERE Bestell_NR = "10055";
143
144-- 23. Erstellen Sie eine weitere Abfrage zur Aktualisierung von Artikel-Datensätzen. Bei Artikel der Kategorie "4" muss der Mindestbestand auf 30 gesetzt werden.
145
146 UPDATE artikel set Mindestbestand="30" WHERE Kategorie_Nr =4;
147
148-- 24. Zeigen Sie in einer Abfrage Artikel von Lieferant_Nr.4. Die Felder der Abfrage stammen aus den tabellen "Artikel" und "Lieferanten":
149 -- Artikel_Nr, Lieferanten_Nr, Firma, Ort.
150
151 SELECT A.Artikel_Nr, A.Lieferanten_Nr, L.Firma, L.Ort
152 FROM artikel A JOIN lieferanten L ON (L.lieferanten_nr=A.lieferanten_nr)
153 WHERE A.Lieferanten_Nr=4;
154
155-- 25. Eine Abfrage soll die Frachtkosten je Kunde zeigen. Es werden das Feld Kunden_Nr aus der Tabelle "Bestellungen" mit der Summe der
156 -- Frachtkosten aus den Bestelllungen des jeweiligen Kunden gelistet"
157
158
159 SELECT Kunden_Nr, SUM(Frachtkosten)
160 FROM bestellungen GROUP BY Kunden_Nr;
161
162 --für extra comfort noch mit anzahl der Bestellungen je Kunden_Nr
163
164 SELECT Kunden_Nr, SUM(Frachtkosten), COUNT(Kunden_Nr)
165 FROM bestellungen GROUP BY Kunden_Nr;
166
167--26. erzeugen Sie mit einer Abfrage eine neue Tabelle ("Lieferanten_N") auf der Basis der Tabelle "Lieferanten".
168 -- Die neue tabelle enthält nur Lieferantendatensätze deren Fimenname mit "N" beginnt.
169
170CREATE TABLE Lieferanten_N
171SELECT * FROM Lieferanten
172WHERE lieferanten.firma LIKE "N%";
173
174--27. Ermitteln Sie in einer Abfrage mit den Tabellen Bestellung und Bestelldetails den
175 -- Nettorechnungsbetrag je Bestellung (Einzelpreis*Anzahl)+Frachtkosten
176
177SELECT BD.Bestell_NR, SUM(BD.einzelpreis*BD.anzahl)+B.Frachtkosten AS Nettorechnungsbetrag
178FROM bestelldetails BD JOIN bestellungen B ON (BD.Bestell_Nr=B.Bestell_NR)
179GROUP BY B.Bestell_NR;
180
181--28. Eine statische Auswertung ergab eine 1,5 prozentige Erhöhung unserer Einzelpreise. Führen Sie nun die Aktualisierung durch.
182
183UPDATE bestelldetails SET einzelpreis = einzelpreis*1.015;
184-- wieder auf 2 dezimalstellen gerunden weil hässlich
185UPDATE bestelldetails SET einzelpreis = ROUND(einzelpreis, 2);
186
187--29. Auf grund neer Verpackungsmittelabgabeverordungen werden alle Artikel, die in Dosen angeboten werden, um 12% teurer.
188 -- Erstellen Sie die dazu nötige Aktualisierungsabfrage.
189UPDATE artikel SET einzelpreis = einzelpreis*1.12 WHERE liefereinheit like "%Dosen%";
190UPDATE artikel SET einzelpreis = ROUND(einzelpreis, 2);
191
192--30. Die Geschäftsleitung hat beschlossen alle Filialen in Frankreich zu schliesen.
193 -- Erstellen Sie eine Löschabfrage die alle französischen Mitarbeiter aus der Tabelle personal entfernt.
194
195DELETE FROM personal WHERE Land = "Frankreich";
196
197--31. Der Einkauf möchte wissenwie hoch der Lagerbestand der Artikel einschließich der bestellten Einheiten wäre.
198 --Definieren Sie ein berechnetes Feld (Feldname:Neubestand), in dem der Lagerbestand und die bestellten Einheiten pro Artikel addiert sind.
199 --Folgende Felder sollen noch enthalten sein: Artikelnummer, Artikelname, Lagerbestand, Bestellte Einheiten.
200
201CREATE VIEW Neubestand AS SELECT Artikel_Nr, Artikelname, Lagerbestand, Bestellte_Einheiten, Lagerbestand+Bestellte_Einheiten AS Neubestand FROM artikel;
202
203--32. Der Verkaufssachbearbeiter hat bisher den Gesamtpreis pro Bestellposition aus der Tabelle Bestelldetails mit dem Taschenrechner ermittelt.
204 --Erstellen Sie nun eine Ansicht (View), in der ein Feld "Gesamtpreis" ihm in Zukunft die gewünschten Informationen liefert. Alle Felder aus
205 -- der Tabelle sollen in dieser Ansicht erscheinen, zusätzlich soll der Artikelname angezeigt werden. Name des View "Bestellposition".
206
207 --rabatt in %
208CREATE VIEW Bestellposition AS SELECT BD.Bestell_NR, A.Artikelname, BD.Artikel_Nr, BD.Einzelpreis, BD.Anzahl, BD.Rabatt, ROUND(BD.Einzelpreis*BD.Anzahl*(1+BD.Rabatt),2) AS Gesamtpreis
209FROM bestelldetails BD JOIN artikel A ON (BD.artikel_nr=A.Artikel_Nr);
210 --rabatt in €/stk. BROKE
211CREATE VIEW Bestellposition AS SELECT BD.Bestell_NR, A.Artikelname, BD.Artikel_Nr, BD.Einzelpreis, BD.Anzahl, BD.Rabatt, ROUND((BD.Einzelpreis*BD.Anzahl-(BD.Anzahl*BD.Rabatt),2) AS Gesamtpreis
212FROM bestelldetails BD JOIN artikel A ON (BD.artikel_nr=A.Artikel_Nr);
213
214--33. Für die Statistik benötigen wir eine Abfrage die aus der Tabelle Bestelldetails die Umsatzsteuer für die einelnen Positionen errechnet (Einzelpreis*Anzahl*0,19).
215 -- Vergeben Sie als Feldname: Umsatzsteuer. Anzeigen aller Felder mit Ausnahme von "Rabatt".
216
217SELECT Bestell_Nr, Artikel_Nr, Einzelpreis, Anzahl, ROUND(Einzelpreis*0.19*Anzahl,2) AS Umsatzsteuer FROM bestelldetails;
218
219--34. Erstellen Sie eine Abfrage (tabelle:Bestelldetails) in der ein berechnetes Feld (Feldname:rabattbetrag) den Rabatt aus
220 -- der Bestellposition (Einzelpreis*Anzahl) in Euro errechnet. Anzeigen aller Felder in dieser Abfrage.
221
222 -- Falls Rabatt in Prozent gemeint
223SELECT *, ROUND(einzelpreis*Anzahl*rabatt,2) AS Rabattbetrag FROM bestelldetails;
224 -- Falls Rabatt in €/stk. gemeint
225SELECT *, ROUND(Anzahl*rabatt,2) AS Rabattbetrag FROM bestelldetails LIMIT 10;