SQL Queries gesucht

[Guin]

Moin,
ich komme mir einer Aufgabe nicht ganz klar.
Nach meinem Verstaendnis sollte kein Ergebnis rauskommen. Aber vielleicht verstehe ich das auch komplett falsch und einer von euch sieht da Land.

Wie lautet die Abfrage, die alle Bücher auflistet, die von Lehrenden empfohlen wurden, von denen jedoch noch kein Exemplar vorliegt. (Betroffene Tabellen: Buch, Exemplar und empfehlen)

--
-- Tabellenstruktur für Tabelle `buch`
--

CREATE TABLE IF NOT EXISTS `buch` (
  `ISBN` varchar(20) collate utf8_bin NOT NULL,
  `Titelblatt` blob,
  `Bildgroesse` varchar(20) collate utf8_bin default NULL,
  `Bildformat` varchar(10) collate utf8_bin default NULL,
  `Titel` varchar(100) collate utf8_bin NOT NULL,
  `Text` varchar(1000) collate utf8_bin default NULL,
  `Video` blob,
  `Videogroesse` varchar(20) collate utf8_bin default NULL,
  `Videoformat` varchar(10) collate utf8_bin default NULL,
  `Seitenanzahl` decimal(4,0) NOT NULL,
  `Exemplare` decimal(3,0) NOT NULL,
  `Leihfrist` decimal(5,0) NOT NULL,
  PRIMARY KEY  (`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Daten für Tabelle `buch`
--

INSERT INTO `buch` (`ISBN`, `Titelblatt`, `Bildgroesse`, `Bildformat`, `Titel`, `Text`, `Video`, `Videogroesse`, `Videoformat`, `Seitenanzahl`, `Exemplare`, `Leihfrist`) VALUES
('3802551230', NULL, NULL, NULL, 'Pusteblume', NULL, NULL, NULL, NULL, 22, 2, 15),
('3499225085', NULL, NULL, NULL, 'Der Ekel', NULL, NULL, NULL, NULL, 347, 4, 30),
('3100101065', NULL, NULL, NULL, 'Die Pest', NULL, NULL, NULL, NULL, 362, 3, 30),
('3451280000', NULL, NULL, NULL, 'Die Bibel', NULL, NULL, NULL, NULL, 1863, 5, 60),
('3423101776', NULL, NULL, NULL, 'Solaris', NULL, NULL, NULL, NULL, 236, 5, 30),
('3453186834', NULL, NULL, NULL, 'Der Wüstenplanet', NULL, NULL, NULL, NULL, 1256, 3, 30),
('3423202777', NULL, NULL, NULL, 'Der kleine Hobbit', NULL, NULL, NULL, NULL, 331, 3, 30),
('3453140982', NULL, NULL, NULL, 'Das Vogelmädchen', NULL, NULL, NULL, NULL, 221, 3, 30),
('3897212013', NULL, NULL, NULL, 'Perl 5 kurz und gut', NULL, NULL, NULL, NULL, 70, 3, 30),
('3897212188', NULL, NULL, NULL, 'CGI kurz und gut', NULL, NULL, NULL, NULL, 104, 3, 30);


-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `empfehlen`
--

CREATE TABLE IF NOT EXISTS `empfehlen` (
  `PersNr` char(7) collate utf8_bin NOT NULL default '',
  `ISBN` varchar(20) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`PersNr`,`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Daten für Tabelle `empfehlen`
--

INSERT INTO `empfehlen` (`PersNr`, `ISBN`) VALUES
('1234567', '3897212013'),
('1357924', '3897212188'),
('2468013', '3897212188'),
('5234260', '3423101776'),
('5234260', '3897212013'),
('9652425', '3897212188');

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `exemplar`
--

CREATE TABLE IF NOT EXISTS `exemplar` (
  `ExemplarNr` varchar(2) collate utf8_bin NOT NULL default '',
  `Leihart` char(1) collate utf8_bin NOT NULL,
  `ISBN` varchar(20) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`ExemplarNr`,`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Daten für Tabelle `exemplar`
--

INSERT INTO `exemplar` (`ExemplarNr`, `Leihart`, `ISBN`) VALUES
('1', 'N', '3423101776'),
('1', 'P', '3453186834'),
('1', 'K', '3423202777'),
('1', 'N', '3453140982'),
('2', 'N', '3453186834');

Mein Ansatz:

SELECT x.ISBN, b.Titel, count( x.ISBN ) AS Ausgeliehen, b.Exemplare AS Vorhanden
FROM exemplar x, empfehlen e, buch b
WHERE x.ISBN = e.ISBN
AND b.ISBN = x.ISBN
GROUP BY x.ISBN
HAVING count( x.ISBN ) >= b.Exemplare;

 

[terx`]

Hm, dein Ansatz ist richtig, finde ich.

Letzte Zeile kann geändert werden in:

HAVING Ausgeliehen >= Vorhanden;

 

[Mercenary]

Hallo.

Nach meinem Verstaendnis sollte kein Ergebnis rauskommen. Aber vielleicht verstehe ich das auch komplett falsch und einer von euch sieht da Land.

Jo wuerde ich auch so sehen....

Wie lautet die Abfrage, die alle Bücher auflistet, die von Lehrenden empfohlen wurden, von denen jedoch noch kein Exemplar vorliegt. (Betroffene Tabellen: Buch, Exemplar und empfehlen)

Um die Titel zu finden, die zwar empfohlen werden, von denen jedoch noch kein Exemplar vorliegt (Sinn und Zweck koennte sein, dass z.B. diese Buecher naechstens angeschafft werden sollten) ist der Ansatz, die ISBN aus der Exemplare-Tabelle zu ziehen schon nicht wirklich gut. ;>


Ohne einen konkreten SQL-Dialekt anwenden zu wollen wuerde ich mir da eher so etwas vorstellen koennen (in Anlehnung an Deine Loesung):
SELECT b.ISBN, b.Titel, b.Exemplare AS Vorhanden
FROM empfehlen e, buch b
WHERE b.ISBN = e.ISBN
and (select count(*) from exemplar where isbn = b.ISBN) = 0
oder so aehnlich, ist aber klar was wohl gesucht wird.

Wobei man unterstellen muss, dass saemtliches Personal, welches Empfehlungen aussprechen kann auch dem Lehrkoerper angehoert.

Nebenbei noch die Frage was denn in buch.Exemplare steht, wenn trotzdem die Tabelle exemplare direkt angegangen wird?

Ciao,
Mercy.

 

[terx`]

Halt, meine Variante nutzt SELECT ... JOIN:

SELECT x.ISBN, b.Titel, COUNT(x.ISBN) AS Ausgeliehen, b.Exemplare AS vorhanden
FROM buch AS b
JOIN exemplar AS x ON (x.ISBN = b.ISBN)
JOIN empfehlen AS e ON (e.ISBN = b.ISBN)
GROUP BY x.ISBN
HAVING Ausgeliehen >= Vorhanden

 

[Guin]

Danke fuer eure Antworten

Wobei man unterstellen muss, dass saemtliches Personal, welches Empfehlungen aussprechen kann auch dem Lehrkoerper angehoert.

Ja so ist es.

Ich haette die Tabellen mal ein bisschen naeher beschreiben sollen.

Die Tabelle `buch` beinhaltet den Bestand der Buecher. Die Spalte `exemplare` gibt die Anzahl des jeweiligen Buches an.

In der Tabelle `empfehlen` werden Buecher von Angestellten der Lehranstalt empfohlen.

In der Tabelle `exemplar` steht, welche Buecher ausgeliehen sind (denke ich zumindest).

Alle empfohlenen Buecher sind in der Tabelle `buch`vorhanden. Auch ist kein Buch oft genug ausgeliehen, damit die vorhandenen Buecher auf 0 sinken.

Wie lautet die Abfrage, die alle Bücher auflistet, die von Lehrenden empfohlen wurden, von denen jedoch noch kein Exemplar vorliegt.

(Sinn und Zweck koennte sein, dass z.B. diese Buecher naechstens angeschafft werden sollten)

Das war auch mein erster Gedanke

Zuerst hatte ich es auch so verstanden, dass in der Tabelle `exemplar` der vorhandene Bestand steht. Da hatte ich die Spalte `exemplare` aus `buch`noch nicht gesehen. Wenn man `exemplar`mit `empfehlen` vergleicht, kommen naemlich 2 Ergebnisse bei raus.

SELECT b.Titel
FROM buch b
WHERE b.ISBN
IN(
SELECT e.ISBN
FROM empfehlen e
WHERE e.ISBN NOT
IN (
SELECT ISBN
FROM exemplar
)
GROUP BY e.ISBN
)

Letzte Zeile kann geändert werden in:

Stimmt. Ich nutze das AS nur, um eine sinnvolle Tabellenueberschrift zu bekommen (Ausgabe auf der SQL Console). Von daher nutze ich es im weiteren Query nicht mehr.

Die "Join" Loesung ist interessant. Nicht so viele Subselects.

Ich muss nun erst mal abwarten, was der Prof sagt. Es sei denn jemand anderes hat eine zuendene Idee.

 

[terx`]

Mir ist heut Nacht im Traum noch eine andere Lösung eingefallen.

Wie lautet die Abfrage, die alle Bücher auflistet, die von Lehrenden empfohlen wurden, von denen jedoch noch kein Exemplar vorliegt.

= von denen kein Eintrag in der Tabelle "exemplar" ist. D.h. Schlüssel aus Tabelle A zu finden, die nicht in der Tabelle B sind.

Dann wär die Abfrage so:

SELECT b.ISBN, b.Titel, COUNT(x.isbn) AS Ausgeliehen, b.Exemplare AS Vorhanden
FROM buch AS b
LEFT JOIN exemplar AS x ON (x.ISBN = b.ISBN)
JOIN empfehlen AS e ON (e.ISBN = b.ISBN)
WHERE x.ISBN IS NULL
GROUP BY x.ISBN;

Und das Ergebnis:

+------------+---------------------+-------------+-----------+
| ISBN       | Titel               | Ausgeliehen | Vorhanden |
+------------+---------------------+-------------+-----------+
| 3897212013 | Perl 5 kurz und gut |           0 |         3 |
+------------+---------------------+-------------+-----------+

Hintergrundinformationen:

MySQL :: MySQL 5.0 Reference Manual :: 7.2.9 LEFT JOIN and RIGHT JOIN Optimization