Benutzer:Greadle: Unterschied zwischen den Versionen
Keine Bearbeitungszusammenfassung |
Keine Bearbeitungszusammenfassung |
||
| Zeile 1: | Zeile 1: | ||
Einige SQL-Abfragen für das Shadowhelix-Datenbank Projekt. | Einige SQL-Abfragen für das Shadowhelix-Datenbank Projekt. | ||
Anzahl der Shadowrun Cover-Bilder pro Illustrator: | Anzahl der Shadowrun Cover-Bilder pro Illustrator: </br> | ||
SELECT contributors.forename_first, contributors.surname, COUNT(contributions.standard_descriptor) AS ["# of covers"] | SELECT contributors.forename_first, contributors.surname, COUNT(contributions.standard_descriptor) AS ["# of covers"] | ||
FROM contributions INNER JOIN contributors ON contributors.id_contributor=contributions.id_contributor | FROM contributions INNER JOIN contributors ON contributors.id_contributor=contributions.id_contributor | ||
| Zeile 8: | Zeile 8: | ||
ORDER BY contributors.surname; | ORDER BY contributors.surname; | ||
Übersicht über Illustratoren und die dazugehörigen Quellen: | Übersicht über Illustratoren und die dazugehörigen Quellen: </br> | ||
SELECT contributors.surname, contributors.forename_first, contributions.standard_descriptor, sources.description, sources.language_code | SELECT contributors.surname, contributors.forename_first, contributions.standard_descriptor, sources.description, sources.language_code | ||
FROM contributors, contributions, sources | FROM contributors, contributions, sources | ||
WHERE contributions.id_source=sources.id_source And contributors.id_contributor=contributions.id_contributor AND (contributions.standard_descriptor="illustration" OR contributions.standard_descriptor="cover art") | WHERE contributions.id_source=sources.id_source And contributors.id_contributor=contributions.id_contributor AND (contributions.standard_descriptor="illustration" OR contributions.standard_descriptor="cover art") | ||
ORDER BY contributors.surname, sources.description; | ORDER BY contributors.surname, sources.description; | ||
Version vom 9. Februar 2016, 14:25 Uhr
Einige SQL-Abfragen für das Shadowhelix-Datenbank Projekt.
Anzahl der Shadowrun Cover-Bilder pro Illustrator:
SELECT contributors.forename_first, contributors.surname, COUNT(contributions.standard_descriptor) AS ["# of covers"]
FROM contributions INNER JOIN contributors ON contributors.id_contributor=contributions.id_contributor
WHERE contributions.standard_descriptor = "cover art"
GROUP BY contributors.surname, contributors.forename_first
ORDER BY contributors.surname;
Übersicht über Illustratoren und die dazugehörigen Quellen:
SELECT contributors.surname, contributors.forename_first, contributions.standard_descriptor, sources.description, sources.language_code
FROM contributors, contributions, sources
WHERE contributions.id_source=sources.id_source And contributors.id_contributor=contributions.id_contributor AND (contributions.standard_descriptor="illustration" OR contributions.standard_descriptor="cover art")
ORDER BY contributors.surname, sources.description;