Требуется сформулировать запрос “Найти названия книг, опубликованных Кристофером Дейтом в издательствах, которые в течение последнего года выпустили книги, суммарная цена которых превышает 100000.00”. Какие из приведенных ниже формулировок являются правильными?
(Отметьте один правильный вариант ответа.)
Варианты ответа
SELECT TITLEFROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERSWHERE AUTHOR_NAME = ‘Chris Date’ AND AUTHORS.AUTHOR_ID = AUTHORS-BOOKS.AUTHOR_ID AND AUTHORS-BOOKS.ISBN = BOOKS.ISBN AND BOOKS.PUB_ID = PUBLISHERS.PUB_ID AND EXISTS (SELECT PUBLISHERS1.PUB_ID FROM PUBLISHERS PUBLISHERS1 WHERE (SELECT SUM (PRICE) FROM BOOKS WHERE BOOKS.PUB_ID = PUBLISHERS1.PUB_ID AND DATE_OF_PUB – CURRENT_DATE <= INTERVAL ‘1’ YEAR) >= 100000.00);
SELECT TITLEFROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERSWHERE AUTHOR_NAME = ‘Chris Date’ AND AUTHORS.AUTHOR_ID = AUTHORS-BOOKS.AUTHOR_ID AND AUTHORS-BOOKS.ISBN = BOOKS.ISBN AND BOOKS.PUB_ID = PUBLISHERS.PUB_ID AND PUBLISHERS.PUB_ID IN (SELECT PUBLISHERS1.PUB_ID FROM PUBLISHERS PUBLISHERS1 WHERE (SELECT SUM (PRICE) FROM BOOKS WHERE BOOKS.PUB_ID = PUBLISHERS1.PUB_ID AND DATE_OF_PUB – CURRENT_DATE <= INTERVAL ‘1’ YEAR) >= 100000.00);(Верный ответ)
SELECT TITLEFROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERSWHERE AUTHOR_NAME = ‘Chris Date’ AND AUTHORS.AUTHOR_ID = AUTHORS-BOOKS.AUTHOR_ID AND AUTHORS-BOOKS.ISBN = BOOKS.ISBN AND BOOKS.PUB_ID = PUBLISHERS.PUB_ID AND PUBLISHERS.PUB_ID = (SELECT PUBLISHERS1.PUB_ID FROM PUBLISHERS PUBLISHERS1 WHERE PUBLISHERS1.PUB_ID = PUBLISHERS.PUB_ID AND (SELECT SUM (PRICE) FROM BOOKS WHERE BOOKS.PUB_ID = PUBLISHERS1.PUB_ID AND DATE_OF_PUB – CURRENT_DATE <= INTERVAL ‘1’ YEAR) >= 100000.00);