SELECT wort, COUNT(*) AS anzahl FROM Schlagwort NATURAL JOIN BuchSchlagwort GROUP BY wort ORDER BY anzahl DESC;
+---------------------+--------+ | wort | anzahl | +---------------------+--------+ | Thermodynamik | 7 | | Lehrbuch | 6 | | Physik | 5 | | Quantenmechanik | 3 | | Datenbank | 2 | | Handbuch | 2 | | SQL | 2 | | Ingenieurwesen | 1 | | Statistische Physik | 1 | +---------------------+--------+
SELECT name AS autor, titel FROM Buch NATURAL JOIN BuchAutor NATURAL JOIN Autor ORDER BY autor, titel;
+---------------+------------------------------------------+ | autor | titel | +---------------+------------------------------------------+ | Bohrmann | Physik - Der Grundkurs | | Cerbe | Einführung in die Thermodynamik | | Cerbe | Grundlagen der Gastechnik | | Greiner | Thermodynamics and Statistical Mechanics | | Halliday | Fundamentals of Physics Extended | | Hering | Physik für Ingenieure | | Hoffmann | Einführung in die Thermodynamik | | Jany | Thermodynamik für Ingenieure | | King | MySQL & mSQL | | Langeheinecke | Thermodynamik für Ingenieure | | Martin | Physik für Ingenieure | | Matthiesen | Relationale Datenbanken und SQL | | Neise | Thermodynamics and Statistical Mechanics | | Pitka | Physik - Der Grundkurs | | Reese | MySQL & mSQL | | Resnick | Fundamentals of Physics Extended | | Sapper | Thermodynamik für Ingenieure | | Stöcker | Physik - Der Grundkurs | | Stöcker | Taschenbuch der Physik | | Stöcker | Thermodynamics and Statistical Mechanics | | Stohrer | Physik für Ingenieure | | Terlecki | Physik - Der Grundkurs | | Unterstein | Relationale Datenbanken und SQL | | Walker | Fundamentals of Physics Extended | | Yarger | MySQL & mSQL | +---------------+------------------------------------------+
SELECT Autor.name AS autor, Verlag.name AS verlag FROM Autor NATURAL JOIN BuchAutor NATURAL JOIN Buch INNER JOIN Verlag ON Buch.verlag_id = Verlag.verlag_id GROUP BY autor, verlag ORDER BY autor;
+---------------+----------------+ | autor | verlag | +---------------+----------------+ | Bohrmann | Harri Deutsch | | Cerbe | Hanser | | Greiner | Springer | | Halliday | Wiley | | Hering | Springer | | Hoffmann | Hanser | | Jany | Vieweg | | King | O'Reilly | | Langeheinecke | Vieweg | | Martin | Springer | | Matthiesen | Addison-Wesley | | Neise | Springer | | Pitka | Harri Deutsch | | Reese | O'Reilly | | Resnick | Wiley | | Sapper | Vieweg | | Stohrer | Springer | | Stöcker | Harri Deutsch | | Stöcker | Springer | | Terlecki | Harri Deutsch | | Unterstein | Addison-Wesley | | Walker | Wiley | | Yarger | O'Reilly | +---------------+----------------+
SELECT Autor.name AS autor, titel FROM Autor NATURAL JOIN BuchAutor NATURAL JOIN Buch NATURAL JOIN BuchSchlagwort NATURAL JOIN Schlagwort WHERE wort = "Thermodynamik" ORDER BY autor;
+---------------+------------------------------------------+ | autor | titel | +---------------+------------------------------------------+ | Bohrmann | Physik - Der Grundkurs | | Cerbe | Einführung in die Thermodynamik | | Greiner | Thermodynamics and Statistical Mechanics | | Halliday | Fundamentals of Physics Extended | | Hering | Physik für Ingenieure | | Jany | Thermodynamik für Ingenieure | | Langeheinecke | Thermodynamik für Ingenieure | | Martin | Physik für Ingenieure | | Neise | Thermodynamics and Statistical Mechanics | | Pitka | Physik - Der Grundkurs | | Resnick | Fundamentals of Physics Extended | | Sapper | Thermodynamik für Ingenieure | | Stohrer | Physik für Ingenieure | | Stöcker | Physik - Der Grundkurs | | Stöcker | Taschenbuch der Physik | | Stöcker | Thermodynamics and Statistical Mechanics | | Terlecki | Physik - Der Grundkurs | | Walker | Fundamentals of Physics Extended | +---------------+------------------------------------------+
SELECT Autor.name AS autor FROM Autor NATURAL JOIN BuchAutor NATURAL JOIN BuchSchlagwort NATURAL JOIN Schlagwort WHERE wort = "Thermodynamik" GROUP BY autor ORDER BY autor;
+---------------+ | autor | +---------------+ | Bohrmann | | Cerbe | | Greiner | | Halliday | | Hering | | Jany | | Langeheinecke | | Martin | | Neise | | Pitka | | Resnick | | Sapper | | Stohrer | | Stöcker | | Terlecki | | Walker | +---------------+
SELECT A2.vorname, A2.name FROM Autor AS A1 NATURAL JOIN BuchAutor AS BA1 INNER JOIN BuchAutor AS BA2 ON BA1.isbn = BA2.isbn INNER JOIN Autor AS A2 ON BA2.autor_id = A2.autor_id WHERE A1.name = "Stöcker" AND BA1.autor_id != BA2.autor_id;
+----------+----------+ | vorname | name | +----------+----------+ | Walter | Greiner | | Ludwig | Neise | | Rudolf | Pitka | | Steffen | Bohrmann | | Günther | Terlecki | +----------+----------+