Bayerische Schuldaten importieren und auswerten

aktu­al­isiert: 17-08-2022

Das Bay­erische Kul­tus­min­is­teri­um stellt die all­ge­meinen Schul­dat­en (Name der Schule, Adresse, Schul­typ, …) der bay­erischen Schulen zum freien Down­load bere­it. Man kann diese als CSV-Datei herunter laden, zur Zeit mit ~6100 Datensätzen.

Die Dat­en eignen sich sehr gut, um hier mit Daten­mod­el­lierung, SQL und JAVA zu üben. Es wird dabei eine MySQL- bzw. Mari­aDB-Daten­bank verwendet.

Diese Skript- / Tuto­ri­al­samm­lung ist für Schü­lerin­nen und Schüler aus den  IT-Berufen, speziell Fach­in­for­matik­er/-in-Anwen­dungsen­twick­lung gedacht, um hier pri­vat zu üben.

Voraus­set­zun­gen / Erfahrun­gen mit … soll­ten vorhan­den sein:

  • Java, speziell mit JDBC
  • Daten­mod­el­lierung und SQL
  • MySQL bzw. MariaDB
  • Open­JDK 11 und Eclipse EE Photon
  • MySQL-Work­bench

Daten importieren, normalisieren und erweitern

Eclipse Projekt

Ein schon fer­tiges Eclipse-Pro­jekt kann mit allen zuge­höri­gen Dat­en importiert werden.
Eclipse aufrufen — File — Import — Gen­er­al — Exist­ing Projects into Workspace

Icon

Bayerische Schuldaten importieren und auswerten: Teil A — Eclipse Projekt

1 Datei(en) 3.15 MB
Down­load

Datenbank anlegen

Mit nach­fol­gen­dem Skript wird eine Daten­bank mit dem Namen schul­dat­en und dem User schule (ohne Pass­wort) angelegt (siehe src/sql/00_create_db.sql).

CONNECT mysql;

DROP DATABASE IF EXISTS schuldaten;
CREATE DATABASE schuldaten CHARACTER SET utf8;

DROP USER IF EXISTS 'schule'@'localhost';
CREATE USER 'schule'@'localhost' IDENTIFIED BY '';

GRANT ALL ON schuldaten.* TO 'schule'@'localhost';
 
FLUSH PRIVILEGES;

Tabelle anlegen

Die Grund­struk­tur der CSV-Datei ist wie fol­gt aufgebaut:

Schulnummer;Schultyp;Name;Straße;PLZ;Ort;Link
"7180";"Berufsfachschule des Gesundheitswesens";"Berufsfachschule für Krankenpflege am Klinikum Aschaffenburg-Alzenau  ";"Am Hasenkopf 1";"63739";"Aschaffenburg";"/schule/7180.html"
  • Schul­num­mer
  • Schul­typ
  • Name der Schule
  • Straße
  • PLZ
  • Ort
  • Link

Die Kodierung der Datei ist ISO-8859–15.

Für die Tabelle ver­wen­det man fol­gende Struktur.

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(5)       | NO   | PRI | NULL    |       |
| schultyp | varchar(100) | YES  |     | NULL    |       |
| name     | varchar(255) | YES  |     | NULL    |       |
| strasse  | varchar(100) | YES  |     | NULL    |       |
| plz      | varchar(5)   | YES  |     | NULL    |       |
| ort      | varchar(100) | YES  |     | NULL    |       |
| link     | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Die Schul­num­mer ist für jede Schule ein­deutig und kann daher gle­ich als Primärschlüs­sel ver­wen­det wer­den. Die anderen Spal­ten, vor allem die Spal­tenbre­ite sind Schätzwerte.

Mit der Java-Datei A01_CreateTableSchule wird die Daten­bank­ta­belle angelegt und die CSV-Datei importiert.

public class A01_CreateTableSchule extends AbstractSchuldatenDB {

Damit man ger­ade die Daten­bank­funk­tio­nen nicht ständig kopieren muss, sind diese in die Klasse AbstractSchuldatenDB zur Vere­in­fachung aus­ge­lagert worden.

package de.gentianaclusii.it;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.nocrala.tools.texttablefmt.BorderStyle;
import org.nocrala.tools.texttablefmt.Table;

public class AbstractSchuldatenDB {

   private static Connection con;
   private static final String DB_URL = "jdbc:mysql://localhost/schuldaten?serverTimezone=Europe/Berlin";
   private static final String PASS = "";
   private static final String USER = "schule";

   protected static void close(final AutoCloseable obj) {
      try {
         obj.close();
      } catch (final Exception e) {
         // ignore
      }
   }

   protected static ResultSet executeQuery(final String sql) throws SQLException {
      getConnection();
      final Statement st = con.createStatement();
      return st.executeQuery(sql);
   }

   protected static int executeUpdate(final String sql) throws SQLException {
      getConnection();
      final Statement st = con.createStatement();
      return st.executeUpdate(sql);
   }

   protected static String fileNameBeautify(final String str) {
      return str.toLowerCase().replace(" ", "_").replace("ö", "oe").replaceAll("ä", "ae").replace("ü", "ue")
            .replaceAll("ß", "ss");
   }

   protected static Connection getConnection() throws SQLException {
      if (con == null) {
         con = DriverManager.getConnection(DB_URL, USER, PASS);
      }
      return con;
   }

   protected static void log(final String str) {
      if (str != null) {
         System.out.println("### " + str);
      }
   }

   public static void printResultSet(final ResultSet rs) throws SQLException {

      final int colcnt = rs.getMetaData().getColumnCount();
      final Table t = new Table(colcnt, BorderStyle.UNICODE_DOUBLE_BOX);
      for (int col = 1; col <= colcnt; col++) {
         t.addCell(rs.getMetaData().getColumnLabel(col));
      }
      while (rs.next()) {
         for (int col = 1; col <= colcnt; col++) {
            final Object o = rs.getObject(col);
            t.addCell(o == null ? "null" : o.toString());
         }
      }
      System.out.println(t.render());
   }

   protected static String removeQuote(String str) {
      str = str.trim();
      if (str.startsWith("\"")) {
         return str.substring(1, str.length() - 1);
      }
      return str;
   }

}

Damit man immer von einem gle­ichen Anfangspunkt aus­ge­ht, wird zuerst eine evtl. vorhan­dene Tabelle schule gelöscht.

log("create table schule");
executeUpdate("DROP TABLE IF EXISTS schule");

Danach wird die Tabelle mit Hil­fe eines SQL-DDL-Befehls erzeugt.

executeUpdate("CREATE TABLE schule ("
               + "id int(5) NOT NULL,"
               + "schultyp varchar(100),"
               + "name varchar(255),"
               + "strasse varchar(100),"
               + "plz varchar(5),"
               + "ort varchar(100),"
               + "link varchar(100),"
               + "PRIMARY KEY (id)"
               + ")ENGINE=InnoDB DEFAULT CHARSET=utf8");

Damit man das Ergeb­nis sieht, wird die Tabel­len­struk­tur auf der Kon­sole aus­geben. Für die Aus­gabe wird die Bib­lio­thek Java Text Table For­mat­ter verwendet.

printResultSet(executeQuery("desc schule"));

Im näch­sten Schritt wird die CSV-Datei ein­ge­le­sen. Dies wird über die Klasse Reader und deren abgeleit­eten Klassen real­isiert und dann zeilen­weise der Daten­satz über ein PreparedStatement an die Daten­bank gesendet. Der ver­wen­dete Code ist sehr ein­fach und lässt sich sicher­lich auch noch verbessern.

         log("insert schule csv");
         final File file = new File("src/daten/schulsuche_export_2019_01_01.csv");
         final String encoding = "ISO8859-15";
         final String separator = ";";

         final BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(file), encoding));

         final Connection con = getConnection();

         final PreparedStatement stmt = con.prepareStatement(
                  "INSERT INTO schule"
                  + "(id, schultyp, name, strasse, plz, ort, link) "
                  + "VALUES (?,?,?,?,?,?,?)");

         String line;
         String[] split;
         int cnt = 0;
         boolean firstline = true;
         while ((line = in.readLine()) != null) {
            if (firstline) {
               firstline = false;
               continue;
            }
            // Schulnummer;Schultyp;Name;Straße;PLZ;Ort;Link
            split = line.split(separator);
            stmt.setInt(1, Integer.parseInt(removeQuote(split[0])));
            stmt.setString(2, removeQuote(split[1]));
            stmt.setString(3, removeQuote(split[2]));
            stmt.setString(4, removeQuote(split[3]));
            stmt.setString(5, removeQuote(split[4]));
            stmt.setString(6, removeQuote(split[5]));
            stmt.setString(7, removeQuote(split[6]));
            stmt.executeUpdate();
            cnt++;
         }
         in.close();
         log("insert " + cnt + " rows");

Ruft man die Java-Klasse auf, so erhält man fol­gende Aus­gabe, wenn nichts “schief” gegan­gen ist.

### create table schule
╔════════╦════════════╦════╦═══╦═══════╦═════╗
║Field   ║Type        ║Null║Key║Default║Extra║
╠════════╬════════════╬════╬═══╬═══════╬═════╣
║id      ║int(5)      ║NO  ║PRI║null   ║     ║
║schultyp║varchar(100)║YES ║   ║null   ║     ║
║name    ║varchar(255)║YES ║   ║null   ║     ║
║strasse ║varchar(100)║YES ║   ║null   ║     ║
║plz     ║varchar(5)  ║YES ║   ║null   ║     ║
║ort     ║varchar(100)║YES ║   ║null   ║     ║
║link    ║varchar(100)║YES ║   ║null   ║     ║
╚════════╩════════════╩════╩═══╩═══════╩═════╝
### insert schule csv
### insert 6109 rows

Die Tabelle sieht dann in der MySQL-Work­bench wie fol­gt aus:

Jet­zt sind die Dat­en importiert, aber man stellt fest, die Tabelle ist nicht normalisiert.

Normalisieren

Die Spalte schultyp soll jet­zt in die Tabelle schultyp aus­ge­lagert und mit ein­er schultypid verknüpft wer­den. Auch die Spal­ten plz und ort sollen entsprechend aus­ge­lagert und dann mit der ortid verknüpft werden.

Die hier dargestell­ten Verknüp­fun­gen (Primärschlüs­sel zu Fremd­schlüs­sel) wer­den erst ganz am Ende hinzuge­fügt, da es son­st bei den weit­eren Änderun­gen Prob­leme mit der Ref­eren­ziellen Integrität geben kann.

Bei der Beze­ich­nung des Schul­typs hat sich gezeigt, dass die Schul­dat­en nicht kon­se­quent die selbe Kodierung ver­wen­den. Hier muss von Hand nachgebessert werden.

select * from schultyp where bez like '%?%';
+----+-------------------------------------------+
| id | bez                                       |
+----+-------------------------------------------+
| 4  | Ausl?ndische Schule                       |
| 12 | Berufsschule zur sonderp?dagog. F?rderung |
| 14 | Fachakademie f?r Landwirtschaft           |
| 21 | Kaufm?nnische Berufsfachschule            |
| 22 | Kaufm?nnische Fachschule                  |
| 26 | Realschule zur sonderp?dagog. F?rderung   |
| 31 | Volksschule zur sonderp?dagog. F?rderung  |
+----+-------------------------------------------+

Aufgabe

Lösen Sie diese Auf­gabe und schauen erst danach in die Java-Klasse A02_Normalize.

Erweiterung Landkreise

Für Schulen ist es auch inter­es­sant, in welchem Land­kreis sich diese befind­en bzw. welch­er Land­kreis welche Schulen hat.

Unter www.suche-postleitzahl.org (Open­Da­ta) kann man sich die Postleitzahlen mit den zuge­höri­gen Land­kreisen als CSV-Datei herunter laden. Die Dat­en sind aus dem Open­streetmap-Pro­jekt gewon­nen worden.

Die Dateistruk­tur (UTF‑8) sieht dabei wie fol­gt aus (Datei src/daten/zuordnung_plz_ort_landkreis.csv):

osm_id,ort,plz,landkreis,bundesland
1104550,Aach,78267,Landkreis Konstanz,Baden-Württemberg

Aufgabe

Erstellen Sie eine entsprechende Tabelle für die Land­kreise und importieren Sie die Dat­en aus der CSV-Datei. Schauen Sie erst danach in die Java-Klasse A03_CreateTableLandkreis.

Ist die Spalte ‘land­kreis’ leer, so han­delt es sich um eine kre­is­freie Stadt. Hier soll dann ‘Stadt <Name>’, beispiel­sweise ‘Stadt München’ ver­wen­det werden.

Aufgabe

Nor­mal­isieren Sie jet­zt die Tabellen so, dass es eine eigene Tabelle für die Land­kreise und auch für die Bun­deslän­der gibt.
Beacht­en Sie dabei, dass eine PLZ mehrere Orte (auf dem Land sehr üblich) haben kann und ein Ort (z.B. München) mehrere PLZs.
Bei der Verknüp­fung ort — landkreis muss man etwas auf­passen, da die Ort­sna­men in den bei­den Tabellen nicht immer gle­ich geschrieben sind, beispiel­sweise “Obern­burg a.Main” bei den Schul­dat­en und “Obern­burg am Main” bei den Landkreisdaten.

Erst jet­zt fügen Sie die Verknüp­fun­gen (Primärschlüs­sel zu Fremd­schlüs­sel) ein.
Schauen Sie erst danach in die Java-Klasse A04_Normalize.

Wenn Sie einen anderen Lösungsweg gegan­gen sind, ist dies auch in Ordnung.

Dieses Skript/Tutorial dient dazu, dass Sie sich mit der Daten­mod­e­lierung, der Nor­mal­isierung, dem SQL-Syn­tax und etwas Java vor der IHK-Prü­fung erneut beschäftigen.

Viel Erfolg bei der Prüfung!

 

SQL-Aufgaben (join, group by, order by, …)

Die Aus­gaben der SQL-Befehle sind mit LIMIT begren­zt, damit es über­sichtlich bleibt.

1. Geben Sie alle Schulen (Name, Schul­typ) mit der Beze­ich­nung ‘Beruf­sober­schule’ nach dem Schul­na­men sortiert aus.
╔══════════════════════════════════════════════════════════════════════════════════════╦════════════════╗
║Schulname                                                                             ║Schultyp        ║
╠══════════════════════════════════════════════════════════════════════════════════════╬════════════════╣
║Adalbert-Raps-Schule Staatliche Berufsoberschule Kulmbach                             ║Berufsoberschule║
║Aloys-Fischer-Schule Staatliche Berufsoberschule Deggendorf                           ║Berufsoberschule║
║Berufsoberschule Bad Wörishofen des Zweckverbandes Beruflicher Schulen Bad Wörishofen ║Berufsoberschule║
║Friedrich-Fischer-Schule Staatl.Berufsoberschule Schweinfurt                          ║Berufsoberschule║
║Gustav-von-Schlör-Schule Staatliche Berufsoberschule Weiden i.d.OPf.                  ║Berufsoberschule║
║Hans-Leipelt-Schule Staatliche Berufsoberschule Donauwörth                            ║Berufsoberschule║
║Max-Grundig-Schule Staatliche Berufsoberschule Fürth                                  ║Berufsoberschule║
║Maximilian-Kolbe-Schule Staatliche Berufsoberschule Neumarkt i.d.OPf.                 ║Berufsoberschule║
║Regiomontanus-Schule Staatliche Berufsoberschule Coburg                               ║Berufsoberschule║
║Staatl. Berufsoberschule Erding                                                       ║Berufsoberschule║
╚══════════════════════════════════════════════════════════════════════════════════════╩════════════════╝

SELECT s.name AS 'Schulname', t.bez AS 'Schultyp' FROM schule s, schultyp t WHERE s.schultypid=t.id AND t.bez='Berufsoberschule' ORDER BY 1 LIMIT 10

 

2. Geben Sie alle Schulen (Name, Ort mit PLZ, Schul­typ) mit der Beze­ich­nung ‘Beruf­sober­schule’ nach dem Ort sortiert aus.
╔══════════════════════════════════════════════════════════════════════════════════════╦════════════════════════════╦════════════════╗
║Schulname                                                                             ║Ort                         ║Schultyp        ║
╠══════════════════════════════════════════════════════════════════════════════════════╬════════════════════════════╬════════════════╣
║Staatliche Berufsoberschule Altötting                                                 ║84503 Altötting             ║Berufsoberschule║
║Staatliche Berufsoberschule Amberg                                                    ║92224 Amberg                ║Berufsoberschule║
║Staatliche Berufsoberschule Ansbach                                                   ║91522 Ansbach               ║Berufsoberschule║
║Staatliche Berufsoberschule Aschaffenburg                                             ║63741 Aschaffenburg         ║Berufsoberschule║
║Staatliche Berufsoberschule Augsburg                                                  ║86159 Augsburg              ║Berufsoberschule║
║Städtische Berufsoberschule Augsburg                                                  ║86161 Augsburg              ║Berufsoberschule║
║Staatliche Berufsoberschule Bad Neustadt a.d.Saale                                    ║97616 Bad Neustadt a.d.Saale║Berufsoberschule║
║Staatliche Berufsoberschule Bad Tölz                                                  ║83646 Bad Tölz              ║Berufsoberschule║
║Berufsoberschule Bad Wörishofen des Zweckverbandes Beruflicher Schulen Bad Wörishofen ║86825 Bad Wörishofen        ║Berufsoberschule║
║Staatliche Berufsoberschule Bamberg                                                   ║96050 Bamberg               ║Berufsoberschule║
╚══════════════════════════════════════════════════════════════════════════════════════╩════════════════════════════╩════════════════╝

SELECT s.name AS 'Schulname', concat(o.plz, ' ' , o.ort) AS 'Ort', t.bez AS 'Schultyp' FROM schule s, schultyp t, ort o WHERE s.schultypid=t.id AND s.ortid=o.id AND t.bez='Berufsoberschule' ORDER BY o.ort LIMIT 10

 

3. Geben Sie alle Schulen (Name, Ort mit PLZ, Land­kreis, Schul­typ) mit der Beze­ich­nung ‘Fachober­schule’ im Land­kreis Dachau nach dem Ort sortiert aus.
╔═════════════════════════════════════════════════════════════════╦══════════════════════╦════════════════╦══════════════╗
║Schulname                                                        ║Ort                   ║Landkreis       ║Schultyp      ║
╠═════════════════════════════════════════════════════════════════╬══════════════════════╬════════════════╬══════════════╣
║Fachoberschule Karlsfeld des Fachoberschule Dachau e.V.          ║85757 Karlsfeld       ║Landkreis Dachau║Fachoberschule║
║Erzbischöfliche Fachoberschule Vinzenz von Paul Markt Indersdorf ║85229 Markt Indersdorf║Landkreis Dachau║Fachoberschule║
╚═════════════════════════════════════════════════════════════════╩══════════════════════╩════════════════╩══════════════╝

SELECT s.name AS 'Schulname', concat(o.plz, ' ' , o.ort) AS 'Ort', l.name AS 'Landkreis', t.bez AS 'Schultyp' FROM schule s, schultyp t, ort o, landkreis l WHERE s.schultypid=t.id AND s.ortid=o.id AND o.landkreisid=l.id AND t.bez='Fachoberschule' AND l.name='Landkreis Dachau' ORDER BY o.ort LIMIT 10

 

4. Geben Sie alle Schul­typen (Land­kreis, Schul­typ) im Land­kreis Dachau nach dem Schul­typ sortiert aus.
╔════════════════╦══════════════════════════════════════════════╗
║Landkreis       ║Schultyp                                      ║
╠════════════════╬══════════════════════════════════════════════╣
║Landkreis Dachau║Ausländische Schule                           ║
║Landkreis Dachau║Berufsfachs. f. Hauswirtschaft u. Sozialberufe║
║Landkreis Dachau║Berufsfachschule des Gesundheitswesens        ║
║Landkreis Dachau║Berufsschule                                  ║
║Landkreis Dachau║Fachakademie                                  ║
║Landkreis Dachau║Fachoberschule                                ║
║Landkreis Dachau║Gymnasium                                     ║
║Landkreis Dachau║Hauswirtschaftliche Fachschule                ║
║Landkreis Dachau║Realschule                                    ║
║Landkreis Dachau║Volksschule (Grund- u. Mittelschulen)         ║
╚════════════════╩══════════════════════════════════════════════╝

SELECT l.name AS 'Landkreis', t.bez AS 'Schultyp' FROM schule s, schultyp t, ort o, landkreis l WHERE s.schultypid=t.id AND s.ortid=o.id AND o.landkreisid=l.id AND l.name='Landkreis Dachau' GROUP BY s.schultypid ORDER BY t.bez LIMIT 10

 

5. Geben Sie alle Schul­typen mit Anzahl (Land­kreis, Schul­typ, Anzahl) im Land­kreis Dachau nach der Anzahl absteigend sortiert aus.
╔════════════════╦══════════════════════════════════════════════╦══════╗
║Landkreis       ║Schultyp                                      ║Anzahl║
╠════════════════╬══════════════════════════════════════════════╬══════╣
║Landkreis Dachau║Volksschule (Grund- u. Mittelschulen)         ║33    ║
║Landkreis Dachau║Berufsfachschule des Gesundheitswesens        ║5     ║
║Landkreis Dachau║Realschule                                    ║4     ║
║Landkreis Dachau║Volksschule zur sonderpädagog. Förderung      ║3     ║
║Landkreis Dachau║Gymnasium                                     ║3     ║
║Landkreis Dachau║Fachoberschule                                ║2     ║
║Landkreis Dachau║Berufsschule                                  ║1     ║
║Landkreis Dachau║Fachakademie                                  ║1     ║
║Landkreis Dachau║Hauswirtschaftliche Fachschule                ║1     ║
║Landkreis Dachau║Wirtschaftsschule                             ║1     ║
║Landkreis Dachau║Ausländische Schule                           ║1     ║
║Landkreis Dachau║Berufsfachs. f. Hauswirtschaft u. Sozialberufe║1     ║
╚════════════════╩══════════════════════════════════════════════╩══════╝

SELECT l.name AS 'Landkreis', t.bez AS 'Schultyp', count(s.schultypid) AS 'Anzahl' FROM schule s, schultyp t, ort o, landkreis l WHERE s.schultypid=t.id AND s.ortid=o.id AND o.landkreisid=l.id AND l.name='Landkreis Dachau' GROUP BY s.schultypid ORDER BY 3 DESC

 

6. Geben Sie den Land­kreis mit den meis­ten Schulen aus. Ver­wen­den Sie dazu LIMIT
╔═════════════╦══════╗
║Landkreis    ║Anzahl║
╠═════════════╬══════╣
║Stadt München║556   ║
╚═════════════╩══════╝

SELECT l.name AS 'Landkreis', count(s.id) AS 'Anzahl' FROM schule s, ort o, landkreis l WHERE s.ortid=o.id AND o.landkreisid=l.id GROUP BY l.id ORDER BY 2 DESC LIMIT 1

 

7. Geben Sie alle Schul­typen und deren Anzahl aus (Schul­typ, Anzahl). Sortiert nach der Anzahl absteigend.
╔══════════════════════════════════════════════╦══════╗
║Schultyp                                      ║Anzahl║
╠══════════════════════════════════════════════╬══════╣
║Volksschule (Grund- u. Mittelschulen)         ║3301  ║
║Berufsfachschule des Gesundheitswesens        ║472   ║
║Gymnasium                                     ║430   ║
║Realschule                                    ║375   ║
║Volksschule zur sonderpädagog. Förderung      ║350   ║
║Berufsschule                                  ║183   ║
║Berufsfachs. f. Hauswirtschaft u. Sozialberufe║161   ║
║Fachoberschule                                ║118   ║
║Gewerbliche Fachschule                        ║112   ║
║Fachakademie                                  ║99    ║
║Wirtschaftsschule                             ║77    ║
║Berufsoberschule                              ║68    ║
║Hauswirtschaftliche Fachschule                ║62    ║
║Berufsschule zur sonderpädagog. Förderung     ║46    ║
║Landwirtschaftliche Fachschule                ║43    ║
║Sonstige Berufsfachschule                     ║30    ║
║Kaufmännische Berufsfachschule                ║29    ║
║Berufsfachschule f. Fremdsprachenberufe       ║28    ║
║Freie Waldorfschule                           ║25    ║
║Gewerbliche Berufsfachschule                  ║25    ║
║Berufsfachschule f. techn. Assistenzberufe    ║21    ║
║Ausländische Schule                           ║13    ║
║Berufsfachschule f. Musik                     ║12    ║
║Kolleg                                        ║6     ║
║Abendgymnasium                                ║5     ║
║Schule besonderer Art                         ║5     ║
║Realschule zur sonderpädagog. Förderung       ║4     ║
║Abendrealschule                               ║3     ║
║Kaufmännische Fachschule                      ║2     ║
║                                              ║2     ║
║Fachakademie für Landwirtschaft               ║1     ║
║Sonstige Fachschule                           ║1     ║
╚══════════════════════════════════════════════╩══════╝

SELECT t.bez AS 'Schultyp', count(s.id) AS 'Anzahl' FROM schule s, schultyp t WHERE s.schultypid=t.id GROUP BY 1 ORDER BY 2 DESC

 

8. Geben Sie die Schulen aus, die keinen bzw. einen leeren Schul­typ angegeben haben. Ver­wen­den Sie hierzu einen LEFT JOIN!
╔═══════════════════════════════════════════════════════════════════════╦════════╗
║Schulname                                                              ║Schultyp║
╠═══════════════════════════════════════════════════════════════════════╬════════╣
║Studienkolleg bei den Universitäten des Freistaates Bayern in München  ║        ║
║Studienkolleg b. d. Fachhochschulen des Freistaates Bayern in Coburg   ║        ║
╚═══════════════════════════════════════════════════════════════════════╩════════╝

SELECT s.name AS 'Schulname', t.bez AS 'Schultyp' FROM schule s LEFT JOIN schultyp t ON s.schultypid=t.id WHERE t.bez is NULL OR length(t.bez) = 0

 

9. Geben Sie die Land­kreise mit der Summe des Schul­typs ‘Beruf­ss­chule’ aus. Sortiert nach der Anzahl der Schule absteigend.
╔══════════════════════╦══════╗
║Landkreis             ║Anzahl║
╠══════════════════════╬══════╣
║Stadt München         ║37    ║
║Stadt Nürnberg        ║11    ║
║Stadt Augsburg        ║7     ║
║Stadt Würzburg        ║5     ║
║Stadt Landshut        ║4     ║
║Stadt Regensburg      ║4     ║
║Stadt Schweinfurt     ║3     ║
║Stadt Kempten (Allgäu)║3     ║
║Landkreis Traunstein  ║3     ║
║Stadt Straubing       ║3     ║
╚══════════════════════╩══════╝

SELECT l.name AS 'Landkreis', count(s.id) AS 'Anzahl' FROM schule s, schultyp t, ort o, landkreis l WHERE s.ortid=o.id AND o.landkreisid=l.id AND s.schultypid=t.id AND t.bez='Berufsschule' GROUP BY l.id ORDER BY 2 DESC LIMIT 10

 

10. Geben Sie pro Land­kreis die Anzahl (Land­kreis, Schul­typ, Anzahl) an Schulen (‘Fachober­schule’ und ‘Beruf­sober­schule’) aus. Sortiert nach Land­kreis, Schul­typ und Anzahl.
╔═════════════════════════════════╦════════════════╦══════╗
║Landkreis                        ║Schultyp        ║Anzahl║
╠═════════════════════════════════╬════════════════╬══════╣
║Landkreis Aichach-Friedberg      ║Berufsoberschule║1     ║
║Landkreis Aichach-Friedberg      ║Fachoberschule  ║1     ║
║Landkreis Altötting              ║Berufsoberschule║1     ║
║Landkreis Altötting              ║Fachoberschule  ║2     ║
║Landkreis Ansbach                ║Berufsoberschule║1     ║
║Landkreis Ansbach                ║Fachoberschule  ║2     ║
║Landkreis Augsburg               ║Berufsoberschule║1     ║
║Landkreis Augsburg               ║Fachoberschule  ║1     ║
║Landkreis Bad Tölz-Wolfratshausen║Berufsoberschule║1     ║
║Landkreis Bad Tölz-Wolfratshausen║Fachoberschule  ║2     ║
╚═════════════════════════════════╩════════════════╩══════╝

SELECT l.name AS 'Landkreis', t.bez AS 'Schultyp', count(s.schultypid) AS 'Anzahl' FROM schule s, schultyp t, ort o, landkreis l WHERE s.schultypid=t.id AND s.ortid=o.id AND o.landkreisid=l.id AND t.bez IN ('Fachoberschule', 'Berufsoberschule') GROUP BY l.id, s.schultypid ORDER BY 1,2,3 LIMIT 10

 

11. Geben Sie pro Land­kreis die Anzahl (Land­kreis, Anzahl Fachober­schule, Anzahl Beruf­sober­schule, Gesamtzahl) aus (Piv­ot-Tabelle). Sortiert nach der Gesamtzahl an Schulen im Land­kreis absteigend. Ver­wen­den Sie dazu ‘CASE … THEN … ELSE … END’ oder if(…) ver­bun­den mit count()!
╔══════════════════════════╦═════════════════════╦═══════════════════════╦══════════╗
║Landkreis                 ║Anzahl Fachoberschule║Anzahl Berufsoberschule║Gesamtzahl║
╠══════════════════════════╬═════════════════════╬═══════════════════════╬══════════╣
║Stadt München             ║19                   ║4                      ║23        ║
║Stadt Nürnberg            ║7                    ║2                      ║9         ║
║Stadt Regensburg          ║4                    ║2                      ║6         ║
║Landkreis Rosenheim       ║4                    ║1                      ║5         ║
║Stadt Landshut            ║2                    ║2                      ║4         ║
║Landkreis München         ║3                    ║1                      ║4         ║
║Landkreis Ansbach         ║2                    ║1                      ║3         ║
║Stadt Würzburg            ║2                    ║1                      ║3         ║
║Landkreis Fürstenfeldbruck║2                    ║1                      ║3         ║
║Landkreis Altötting       ║2                    ║1                      ║3         ║
╚══════════════════════════╩═════════════════════╩═══════════════════════╩══════════╝

SELECT l.name AS 'Landkreis', count(if(t.bez='Fachoberschule',t.bez,NULL)) AS 'Anzahl Fachoberschule', count(if(t.bez='Berufsoberschule',t.bez,NULL)) AS 'Anzahl Berufsoberschule', count('Anzahl Fachoberschule' + 'Anzahl Berufsoberschule') AS 'Gesamtzahl' FROM schule s, schultyp t, ort o, landkreis l WHERE s.schultypid=t.id AND s.ortid=o.id AND o.landkreisid=l.id AND t.bez IN ('Fachoberschule', 'Berufsoberschule') GROUP BY l.id ORDER BY 4 DESC LIMIT 10

 

weit­ere Teile / Auf­gaben folgen.…..

 

Lizenz für das Skript/Tutorial: