OBS/Adminhilfe/MySQL Aufgaben: Unterschied zwischen den Versionen

2. Liste der Personen mit der Anzahl an Rechnungen des Vorjahres.
SELECT ps_name, count(ps_name) as anzahl FROM perssta'LEFT JOIN rechsta ON ps_nr = a_knr'WHERE LEFT(a_datum, 4) = year(now())-1'GROUP BY ps_name

3. Pro Kunde die Bruttosummen aller Rechnungen.
SELECT ps_nr, ps_name, ps_vorname, sum(a_bbetrag) as bruttosumme FROM perssta'LEFT JOIN rechsta ON ps_nr = a_knr'WHERE not isnull(a_bbetrag)'GROUP BY ps_nr

4. Verkaufsmengen der Artikel, anhand der Rechnungspositionen, absteigend sortiert nach der Verkaufsmenge.
SELECT az_artnr, az_bez1, az_bez2, sum(az_menge) as menge FROM rechpos'WHERE az_bez1 <> 0'GROUP BY az_bez1 'ORDER BY menge DESC

5. Auflistung aller gekauften Artikel mit Gesamtmenge für die Person 102057 (Tabellen: Perssta, Rechsta und Rechpos)

SELECT ps_nr, ps_name, az_artnr, az_bez1, az_bez2, sum(az_menge) FROM perssta'LEFT JOIN rechsta ON ps_nr = a_knr'LEFT JOIN rechpos ON a_nr = az_nr'WHERE ps_nr = '102057' AND az_menge <> 0 AND az_bez1 <> '''GROUP BY az_bez1

6. Eine Liste aller Warengruppen, mit Anzahl der Artikel und ihrer Verkaufsmengen, im Jahr 2011 und dem letzten Rechnungsdatum

(Tabellen: warengr, artikel, rechpos und rechsta)
SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, az_nr'FROM warengr'LEFT JOIN artikel ON wg_nr = a_wgr'LEFT JOIN rechpos ON artikel.a_nr = az_artnr'LEFT JOIN rechsta ON rechsta.a_nr = az_nr'WHERE year(rechsta.a_datum) = '2011GROUP BY wg_nr

7. Liste aller Personen - mit Personennummer, Name (Vorname + Nachname in einer Spalte) und der Anzahl der Aufträge - die im ersten

Quartal des aktuellen Jahres (Hinweis: Feld a_datum) Aufträge haben. (Neue SQL-Funktionen hierbei: CONCAT und QUARTER)
SELECT ps_nr, concat(ps_vorname, ps_name), count(ps_nr) as aufträge FROM perssta'LEFT JOIN auftrag ON a_knr = ps_nr'WHERE quarter(a_datum) = 1 'AND year(a_datum) = year(now())'GROUP BY ps_nr'ORDER BY ps_nr

8. Die Liste von 1. erweitert um die Informationen höchster Auftragswert und durchschnittlicher Auftragswert.

    (Neue SQL-Funktionen hierbei: AVG und MAX)
SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, '      max(rechsta.a_datum) as letzte_rechnung, max(a_bbetrag) as höchster_auftragswert, '      avg(a_bbetrag)as durchschnittlicher_auftragswert'FROM warengr'LEFT JOIN artikel ON wg_nr = a_wgr'LEFT JOIN rechpos ON artikel.a_nr = az_artnr'LEFT JOIN rechsta ON rechsta.a_nr = az_nr'WHERE year(rechsta.a_datum) = '2011GROUP BY wg_nr

9. Alle Artikel anzeigen, die von allen Lieferanten nicht mehr lieferbar sind. (Hinweis: ein Artikel kann mehrere Lieferanten haben) Dann auf     Inaktiv setzen.
1. Lösung (0,390 sec)'SELECT a.a_nr, a.a_name1, a.a_name2, ek_bemerk FROM artikel as a'LEFT JOIN (SELECT a_nr, min(ek_nolieferbar) as mini FROM artikel '                     LEFT JOIN artiek ON a_nr = ek_artnr '                    GROUP BY a_nr) as b ON b.a_nr = a.a_nr'LEFT JOIN artiek ON a.a_nr = ek_artnr'WHERE ek_nolieferbar = b.mini 'AND ek_nolieferbar > '0' 'AND A_INAKTIV <> '1' 'AND not isnull(ek_nolieferbar) 'AND (a_bestand+a_abestand) = 0'GROUP BY a.a_nr'ORDER BY a.a_nr

2.Lösung (0,187 sec)'SELECT a_nr, a_name1, a_name2, a.ek_bemerk 'FROM (SELECT b.ek_artnr, b.ek_nolieferbar, if(min(b.ek_nolieferbar) > 0, 1, 0) as neu, b.ek_bemerk' FROM artiek as b '           GROUP BY b.ek_artnr) as a 'LEFT JOIN artikel ON a_nr = a.ek_artnr'WHERE a.neu = a.ek_nolieferbar 'AND a.ek_nolieferbar > 0 'AND A_INAKTIV <> '1' 'AND not isnull(ek_nolieferbar) 'AND (a_bestand+a_abestand) = 0'ORDER BY a_nr

3. Lösung (0,265 sec)'SELECT a_nr, a_name1, a_name2, ek_bemerk FROM artiek as a'LEFT JOIN artikel ON a_nr = a.ek_artnr'where A_INAKTIV <> '1' 'AND not isnull(ek_nolieferbar) 'AND (a_bestand+a_abestand) = 0'GROUP BY ek_artnr'Having min(ek_nolieferbar) > '0

4. Lösung von Jörn (0,280 sec)'SELECT a_nr, a_name1 FROM artikel'WHERE '(SELECT min( ek_nolieferbar) FROM ARTIEK 'WHERE a_nr=ek_artnr) = '1AND a_inaktiv <> "1"'AND (a_bestand+a_abestand) = 0

Auto import 15:26, 25. Aug. 2011 (CEST)

@@ Zeile 1: / Zeile 1: @@
-{{Adminhilfe}}<font size="2" color="#000000" face="Arial"> </font>
+{{ZugriffOBS}}<font size="2" color="#000000" face="Arial"> </font>
 <div><center><font size="6">SQL Aufgaben</font></center></div><div><font size="3" color="#000000"><br /><div><font size="3" color="#000000"><u>1. Alle Rechnungen aus dem letzten Jahr.</u><br />''SELECT * FROM rechsta''''WHERE (a_datum, 4) = year(now())''<br /><br /><div><font size="3" color="#000000"><u>2. Liste der Personen mit der Anzahl an Rechnungen des Vorjahres.</u><br />''SELECT ps_name, count(ps_name) as anzahl FROM perssta''''LEFT JOIN rechsta ON ps_nr = a_knr''''WHERE LEFT(a_datum, 4) = year(now())-1''''GROUP BY ps_name''<br /><br /><div><font size="3" color="#000000"><u>3. Pro Kunde die Bruttosummen aller Rechnungen.</u><br />''SELECT ps_nr, ps_name, ps_vorname, sum(a_bbetrag) as bruttosumme FROM perssta''''LEFT JOIN rechsta ON ps_nr = a_knr''''WHERE not isnull(a_bbetrag)''''GROUP BY ps_nr''<br /><br /><div><font size="3" color="#000000"><u>4. Verkaufsmengen der Artikel, anhand der Rechnungspositionen, absteigend sortiert nach der Verkaufsmenge.</u><br />''SELECT az_artnr, az_bez1, az_bez2, sum(az_menge) as menge FROM rechpos''''WHERE az_bez1 &lt;&gt; 0''''GROUP BY az_bez1 ''''ORDER BY menge DESC''<br /><br /><div><font size="3" color="#000000"><u>5. Auflistung aller gekauften Artikel mit Gesamtmenge für die Person 102057 (Tabellen: Perssta, Rechsta und Rechpos)</u><div><font size="3" color="#000000"><br />''SELECT ps_nr, ps_name, az_artnr, az_bez1, az_bez2, sum(az_menge) FROM perssta''''LEFT JOIN rechsta ON ps_nr = a_knr''''LEFT JOIN rechpos ON a_nr = az_nr''''<nowiki>WHERE ps_nr = '102057' AND az_menge &lt;&gt; 0 AND az_bez1 &lt;&gt; ''</nowiki>''''GROUP BY az_bez1''<br /><br /><div><font size="3" color="#000000"><u>6. Eine Liste aller Warengruppen, mit Anzahl der Artikel und ihrer Verkaufsmengen, im Jahr 2011 und dem letzten Rechnungsdatum   </u><div><font size="3" color="#000000">    </font><font size="3" color="#000000"><u>(Tabellen: warengr, artikel, rechpos und rechsta)</u><br />''SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, az_nr''''FROM warengr''''LEFT JOIN artikel ON wg_nr = a_wgr''''LEFT JOIN rechpos ON artikel.a_nr  = az_artnr''''LEFT JOIN rechsta ON rechsta.a_nr  = az_nr''''WHERE year(rechsta.a_datum) = '2011'''''GROUP BY wg_nr''<br /><br /><div><font size="3" color="#000000"><u>7. Liste aller Personen - mit Personennummer, Name (Vorname + Nachname in einer Spalte) und der Anzahl der Aufträge - die im ersten </u><div><font size="3" color="#000000">    </font><font size="3" color="#000000"><u>Quartal des aktuellen Jahres (Hinweis: Feld a_datum)  Aufträge haben. (Neue SQL-Funktionen hierbei: CONCAT und QUARTER)</u><br />''SELECT ps_nr, concat(ps_vorname, ps_name), count(ps_nr) as aufträge FROM perssta''''LEFT JOIN auftrag ON a_knr = ps_nr''''WHERE quarter(a_datum) = 1 ''''AND year(a_datum) = year(now())''''GROUP BY ps_nr''''ORDER BY ps_nr''<br /><br /><div><font size="3" color="#000000"><u>8. Die Liste von 1. erweitert um die Informationen höchster Auftragswert und durchschnittlicher Auftragswert.</u><div><font size="3" color="#000000">    </font><font size="3" color="#000000"><u>(Neue SQL-Funktionen hierbei: AVG und MAX)</u><br />''SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, ''''        ''</font><font size="3">''    max(rechsta.a_datum) as letzte_rechnung, max(a_bbetrag) as höchster_auftragswert, ''''        ''</font><font size="3">''    avg(a_bbetrag)as durchschnittlicher_auftragswert''''FROM warengr''''LEFT JOIN artikel ON wg_nr = a_wgr''''LEFT JOIN rechpos ON artikel.a_nr  = az_artnr''''LEFT JOIN rechsta ON rechsta.a_nr  = az_nr''''WHERE year(rechsta.a_datum) = '2011'''''GROUP BY wg_nr''<br /><br /><u>9. Alle Artikel anzeigen, die von allen Lieferanten nicht mehr lieferbar sind. (Hinweis: ein Artikel kann mehrere Lieferanten haben) Dann auf </u>    </font><font size="3"><u>Inaktiv setzen.</u><br />''<u>1. Lösung (0,390 sec)</u>''''SELECT a.a_nr, a.a_name1, a.a_name2, ek_bemerk FROM artikel as a''''LEFT JOIN (SELECT a_nr, min(ek_nolieferbar) as mini FROM artikel ''''                     LEFT JOIN artiek ON a_nr = ek_artnr ''''                    GROUP BY a_nr) as b ON b.a_nr = a.a_nr''''LEFT JOIN artiek ON a.a_nr = ek_artnr''''WHERE ek_nolieferbar = b.mini ''''AND ek_nolieferbar &gt; '0' ''''AND A_INAKTIV &lt;&gt; '1' ''''AND not isnull(ek_nolieferbar)  ''''AND (a_bestand+a_abestand) = 0''''GROUP BY a.a_nr''''ORDER BY a.a_nr''<br /><br />''<u>2.Lösung (0,187 sec)</u>''''SELECT a_nr, a_name1, a_name2, a.ek_bemerk ''''FROM (SELECT b.ek_artnr, b.ek_nolieferbar, if(min(b.ek_nolieferbar) &gt; 0, 1, 0) as neu, b.ek_bemerk''''        ''</font><font size="3">''FROM artiek as b ''''           GROUP BY b.ek_artnr) as a ''''LEFT JOIN artikel ON a_nr = a.ek_artnr''''WHERE a.neu = a.ek_nolieferbar ''''AND a.ek_nolieferbar &gt; 0 ''''AND A_INAKTIV &lt;&gt; '1' ''''AND not isnull(ek_nolieferbar)  ''''AND (a_bestand+a_abestand) = 0''''ORDER BY a_nr''<br /><br />''<u>3. Lösung (0,265 sec)</u>''''SELECT a_nr, a_name1, a_name2, ek_bemerk FROM artiek as a''''LEFT JOIN artikel ON a_nr = a.ek_artnr''''where A_INAKTIV &lt;&gt; '1' ''''AND not isnull(ek_nolieferbar)  ''''AND (a_bestand+a_abestand) = 0''''GROUP BY ek_artnr''''Having min(ek_nolieferbar) &gt; '0'''<br /><br />''<u>4. Lösung von Jörn (0,280 sec)</u>''''SELECT a_nr, a_name1 FROM artikel''''WHERE ''''(SELECT min( ek_nolieferbar) FROM ARTIEK ''''WHERE a_nr=ek_artnr) = '1'''''AND a_inaktiv &lt;&gt; "1"''''AND (a_bestand+a_abestand) = 0''[[Category:html]]
 ----
 [[Benutzer:Auto import|Auto import]] 15:26, 25. Aug. 2011 (CEST)

OBS/Adminhilfe/MySQL Aufgaben: Unterschied zwischen den Versionen

Version vom 3. Januar 2012, 10:27 Uhr

Navigationsmenü

Suche