Лекция 16. Язык баз данных SQL: средства формулировки аналитических и рекурсивных запросов

Тесты

В первых упражнениях мы снова будем использовать базу данных АВТОРЫ-КНИГИ-ИЗДАТЕЛЬСТВА (AUTHORS-BOOKS-PUBLISHERS). Для удобства повторим структуру таблиц:

AUTHORS

AUTHORS-BOOKS

BOOKS

PUBLISHERS

В таблицах AUTHORS, BOOKS и PUBLISHERS столбцы AUTHOR_ID, ISBN и PUB_ID соответственно являются первичными ключами. В таблице AUTHORS-BOOKS столбцы AUTHOR_ID и ISBN являются внешними ключами, ссылающимися на первичные ключи таблиц AUTHORS и BOOKS соответственно. В таблицу BOOKS столбец PUB_ID является внешним ключом, ссылающимся на первичный ключ таблицы PUBLISHERS.

1 (1) Требуется сформулировать запрос “Для каждого издательства выдать название издательства, его адрес, среднюю и максимальную цены издававшихся книг и общее число издававшихся авторов”. Какие из приведенных ниже формулировок являются правильными?

(а) +

SELECT PUB_NAME, PUB_ADDRESS,
AVG (PRICE), MAX (PRICE), COUNT (DISTINCT AUTHOR_ID)
FROM AUTHORS-BOOKS, BOOKS, PUBLISHERS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
GROUP BY PUBLISHERS.PUB_ID;

(б) +

SELECT DISTINCT PUB_NAME, PUB_ADDRESS,
(SELECT AVG (PRICE)
FROM BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID),
(SELECT MAX (PRICE)
FROM BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID),
(SELECT COUNT (DISTINCT AUTHOR_ID)
FROM BOOKS, AUTHORS-BOOKS
WHERE BOOKS.PUB_ID = PUBLISHERS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
FROM PUBLISHERS;

(в) -

SELECT PUB_NAME, PUB_ADDRESS,
AVG (PRICE), MAX (PRICE), COUNT (DISTINCT AUTHOR_ID)
FROM AUTHORS-BOOKS, BOOKS, PUBLISHERS
GROUP BY PUBLISHERS.PUB_ID
HAVING PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN;

1 (2) Требуется сформулировать запрос “Найти названия всех издательств, в которых было издано больше пяти книг Дейта”. Какие из приведенных ниже формулировок являются правильными?

(а) +

SELECT PUB_NAME
FROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
..AND AUTHORS-BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID
AND AUTHOR_NAME = ‘Chris Date’
GROUP BY PUBLISHERS.PUB_ID
HAVING COUNT (DISTINCT BOOKS.ISBN) > 5;

(б) +
SELECT PUB_NAME
FROM PUBLISHERS
WHERE (SELECT COUNT (DISTINCT BOOKS.ISBN)
FROM AUTHORS, AUTHORS-BOOKS, BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
..AND AUTHORS-BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID
AND AUTHOR_NAME = ‘Chris Date’) > 5;

(в) -

SELECT PUB_NAME
FROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERS
GROUP BY PUBLISHERS.PUB_ID, PUB_NAME, BOOKS.PUB_ID,
BOOKS.ISBN, AUTHORS-BOOKS.ISBN, AUTHORS-BOOKS.AUTHOR_ID,
AUTHORS.AUTHOR_ID, AUTHOR_NAME
HAVING PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
AND AUTHORS-BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID
AND AUTHOR_NAME = ‘Chris Date’
AND COUNT (DISTINCT BOOKS.ISBN) > 5;

1 (3) Требуется сформулировать запрос “Найти названия издательств, издавших не меньше книг, чем издательство Morgan Kauffman, и не больше, чем издательство Addison-Wesley”. Какие из приведенных ниже формулировок являются правильными?

(а) +

SELECT PUB_NAME
FROM BOOKS, PUBLISHERS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
GROUP BY PUBLISHERS.PUB_ID
HAVING COUNT (BOOKS.ISBN) BETWEEN
(SELECT COUNT (ISBN);
FROM BOOKS
WHERE PUB_ID = (SELECT PUBLISHERS.PUB_ID
FROM PUBLISHERS
WHERE PUB_NAME = ‘Morgan Kauffman’)
AND (SELECT COUNT (ISBN);
FROM BOOKS
WHERE PUB_ID = (SELECT PUBLISHERS.PUB_ID
FROM PUBLISHERS
WHERE PUB_NAME = ‘Addison-Wesley’);

(б) +

SELECT PUB_NAME
FROM PUBLISHERS
WHERE (SELECT COUNT (ISBN);
FROM BOOKS
WHERE BOOKS.PUB_ID = PUBLISHERS.PUB_ID) BETWEEN
(SELECT COUNT (ISBN);
FROM BOOKS
WHERE PUB_ID = (SELECT PUBLISHERS.PUB_ID
FROM PUBLISHERS
WHERE PUB_NAME = ‘Morgan Kauffman’) AND
(SELECT COUNT (ISBN);
FROM BOOKS
WHERE PUB_ID = (SELECT PUBLISHERS.PUB_ID
FROM PUBLISHERS
WHERE PUB_NAME = ‘Addison-Wesley’);

(в) -

SELECT PUB_NAME
FROM PUBLISHERS
WHERE (SELECT COUNT (ISBN);
FROM BOOKS
WHERE BOOKS.PUB_ID = PUBLISHERS.PUB_ID) BETWEEN
(SELECT COUNT (ISBN);
FROM BOOKS, PUBLISHERS
WHERE PUB_NAME = ‘Morgan Kauffman’) AND
(SELECT COUNT (ISBN);
FROM BOOKS, PUBLISHERS
WHERE PUB_NAME = ‘Addison-Wesley’);

2 (1) Требуется сформулировать запрос “Найти названия издательств, у которых максимальная цена книги равна средней цене книги какого-либо другого издательства”. Какие из приведенных ниже формулировок являются правильными?

(а) +

SELECT PUB_NAME
FROM PUBLISHERS, BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
GROUP BY PUBLISHERS.PUB_ID
HAVING AVG (PRICE) IN (SELECT AVG (PRICE)
FROM PUBLISHERS PUBLISHERS1, BOOKS
WHERE PUBLISHERS1.PUB_ID <> PUBLISHERS.PUB_ID
GROUP BY PUBLISHERS1.PUB_ID);

(б) +

SELECT PUB_NAME
FROM PUBLISHERS
WHERE (SELECT AVG (PRICE)
FROM BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID) = SOME
(SELECT AVG (PRICE)
FROM PUBLISHERS PUBLISHERS1, BOOKS
WHERE PUBLISHERS1.PUB_ID <> PUBLISHERS.PUB_ID
GROUP BY PUBLISHERS1.PUB_ID);

(в) -

SELECT PUB_NAME
FROM PUBLISHERS, BOOKS
WHERE EXISTS (SELECT PUBLISHERS1.PUB_ID
FROM PUBLISHERS PUBLISHERS1, BOOKS BOOKS1
WHERE PUBLISHERS1.PUB_ID <> PUBLISHERS.PUB_ID
GROUP BY PUBLISHERS1.PUB_ID
HAVING AVG (BOOKS1.PRICE) = AVG (BOOKS.PRICE))
GROUP BY PUBLISHERS.PUB_ID;

2 (2) Требуется сформулировать запрос “Найти название издательств и общее число изданных ими книг для тех издательств, для которых ни в одном другом издательстве не издавалось то же общее число книг”. Какие из приведенных ниже формулировок являются правильными?

(а) -

SELECT PUB_NAME, COUNT (ISBN)
FROM PUBLISHERS, BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND COUNT (ISBN) NOT IN
(SELECT COUNT (ISBN)
FROM PUBLISHERS PUBLISHERS1, BOOKS BOOKS1
WHERE PUBLISHERS1.PUB_ID = BOOKS1.PUB_ID
AND PUBLISHERS1.PUB_ID <> PUBLISHERS.PUB_ID
GROUP BY PUBLISHERS1.PUB_ID)
GROUP BY PUBLISHERS.PUB_ID;

(б) +

SELECT PUB_NAME, COUNT (ISBN)
FROM PUBLISHERS, BOOKS

WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
GROUP BY PUBLISHERS.PUB_ID
HAVING NOT EXISTS (SELECT PUBLISHERS1.PUB_ID
FROM PUBLISHERS PUBLISHERS1, BOOKS BOOKS1
WHERE PUBLISHERS1.PUB_ID <> PUBLISHERS.PUB_ID
GROUP BY PUBLISHERS1.PUB_ID
HAVING COUNT(BOOKS1.ISBN) = COUNT (BOOKS.ISBN));

(в) -

SELECT PUB_NAME, COUNT (ISBN)
FROM PUBLISHERS, BOOKS
GROUP BY PUBLISHERS.PUB_ID
HAVING PUBLISHERS.PUB_ID = BOOKS.PUB_ID
AND UNIQUE (SELECT PUBLISHERS1.PUB_ID
FROM PUBLISHERS, BOOKS BOOKS1
WHERE PUBLISHERS.PUB_ID = BOOKS1.PUB_ID
GROUP BY PUBLISHERS.PUB_ID
HAVING COUNT(BOOKS1.ISBN) = COUNT (BOOKS.ISBN));

2 (3) Требуется сформулировать запрос “Найти названия издательств, у которых средняя цена книги такова, что найдется хотя бы одно другое издательство с меньшей средней ценой книги”. Какие из приведенных ниже формулировок являются правильными?

(а) +

SELECT PUB_NAME
FROM PUBLISHERS

WHERE EXISTS
(SELECT *
FROM BOOKS
GROUP BY BOOKS.PUB_ID
HAVING AVG (PRICE) < (SELECT AVG (PRICE)
FROM BOOKS
WHERE BOOKS.PUB_ID = PUBLISHERS.PUB_ID);

(б) +

SELECT PUB_NAME
FROM PUBLISHERS, (SELECT AVG (PRICE)
FROM BOOKS
GROUP BY PUB_ID) AS PUB_AVG_PRICE (AVGPR)
GROUP BY PUB_ID
HAVING (SELECT AVG (PRICE)
FROM BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID) >

(SELECT MIN (AVGPR)
FROM PUB_AVG_PRICE);

(в) -

SELECT PUB_NAME
FROM PUBLISHERS
WHERE (SELECT AVG (PRICE)
FROM BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID) > ALL
(SELECT AVG (PRICE)
FROM BOOKS
GROUP BY PUB_ID);

Пусть имеются таблицы table1 (a1, a2, c1, c2) и table2 (b1, b2, c1, c2) со следующими телами:

table1

table2

3 (1) Какая из показанных ниже таблиц является результатом операции table1 LEFT OUTER JOIN table2 ON a2=b1 AND a1<b2?

(а) -

(б) +

(в) -

3 (2) Какая из показанных ниже таблиц является результатом операции table1 RIGHT OUTER JOIN table2 ON a2=b1 AND a1<b2?

(a) -

(б) -

(в) +

3 (3) Какая из показанных ниже таблиц является результатом операции table1 FULL OUTER JOIN table2 ON a2=b1 AND a1<b2?

(а) +

(б) +

(с) -

4 (1) Какая из показанных ниже таблиц является результатом операции table1 NATURAL LEFT OUTER JOIN table2?

(а) -

(б) +

(в) -

(а) +

(б) -

(в) +

4 (3) Какая из показанных ниже таблиц является результатом операции table1 NATURAL FULL OUTER JOIN table2?

table1

table2

(а) +

(б) -

(в) -

В этой лекции мы завершаем обсуждение средств выборки данных языка SQL коротким введением в сравнительно недавно появившиеся в языке SQL средства формулировки аналитических и рекурсивных запросов.