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

Тесты

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

AUTHORS

AUTHOR_ID: INTEGER

AUTHOR_NAME: VARCHAR (20)

AUTHOR_BDATE: DATE

AUTHOR_ADDRESS: VARCHAR (40)

AUTHOR_PICTURE: BLOB

AUTHORS-BOOKS

ISBN: VARCHAR (20)

AUTHOR_ID: INTEGER

BOOKS

ISBN: VARCHAR (20)

TITLE: VARCHAR (30)

PUB_ID: INTEGER

DATE_OF_AGREEMENT: DATE

AGREEMENT_DURAT: INTERVAL

DATE_OF_PUB: DATE

PRICE: MONEY

NUMBER_OF_PAGES: SMALLINT

COVER_PICTURE: BLOB

DESCR: CLOB

PUBLISHERS

PUB_ID: INTEGER

PUB_NAME: VARCHAR (20)

PUB_ADDRESS: VARCHAR (40)

В таблицах 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

a1	a2	c1	c2
		NULL
	NULL	NULL	NULL

table2

b1	b2	c1	c2
	NULL	NULL
	NULL	NULL

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

(а) -

a1	a2	c1	c2	b1	b2
				NULL	NULL
				NULL	NULL
				NULL	NULL
		NULL
		NULL
	NULL	NULL	NULL	NULL	NULL

(б) +

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
		NULL
		NULL
	NULL	NULL	NULL	NULL	NULL	NULL	NULL

(в) -

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
		NULL
		NULL
		NULL			NULL	NULL
		NULL			NULL	NULL
	NULL	NULL	NULL	NULL	NULL	NULL	NULL

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

(a) -

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
		NULL
		NULL
NULL	NULL	NULL	NULL
		NULL			NULL	NULL
		NULL			NULL	NULL

(б) -

a1	a2	b1	b2	c1	c2
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL		NULL	NULL
NULL	NULL		NULL	NULL

(в) +

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
		NULL
		NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

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

(а) +

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
				NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
				NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
		NULL
NULL	NULL	NULL	NULL
		NULL
NULL	NULL	NULL	NULL
	NULL	NULL	NULL	NULL	NULL	NULL	NULL

(б) +

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
		NULL
		NULL
	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

(с) -

a1	a2	c1	c2	b1	b2
				NULL	NULL
				NULL	NULL
				NULL	NULL
		NULL
		NULL
	NULL	NULL	NULL	NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL	NULL			NULL
NULL	NULL	NULL			NULL

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

(а) -

a1	a2	c1	c2	b1	b2
					NULL
					NULL
				NULL	NULL
				NULL	NULL
		NULL		NULL	NULL
	NULL	NULL	NULL	NULL	NULL

(б) +

a1	a2	c1	c2	b1	b2
				NULL	NULL
				NULL	NULL
		NULL		NULL	NULL
	NULL	NULL	NULL	NULL	NULL

(в) -

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
		NULL		NULL	NULL	NULL	NULL
	NULL	NULL	NULL	NULL	NULL	NULL	NULL

(а) +

a1	a2	c1	c2	b1	b2
NULL	NULL
NULL	NULL
NULL	NULL	NULL			NULL
NULL	NULL	NULL			NULL

(б) -

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

(в) +

a1	b1	c1	a2	c2	b2
NULL		NULL	NULL		NULL
NULL		NULL	NULL		NULL
NULL			NULL
NULL			NULL

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

table1

a1	a2	c1	c2
		NULL
	NULL	NULL	NULL

table2

b1	b2	c1	c2
	NULL	NULL
	NULL	NULL

(а) +

a1	a2	c1	c2	b1	b2
				NULL	NULL
				NULL	NULL
		NULL		NULL	NULL
	NULL	NULL	NULL	NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL	NULL			NULL
NULL	NULL	NULL			NULL

(б) -

a1	a2	table1.c1	table1.c2	b1	b2	table2.c1	table2.c2
				NULL	NULL	NULL	NULL
				NULL	NULL	NULL	NULL
		NULL		NULL	NULL	NULL	NULL
	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL
NULL	NULL	NULL	NULL		NULL	NULL

(в) -

a1	a2	c1	c2	b1	b2
				NULL	NULL
				NULL	NULL
		NULL		NULL	NULL
	NULL	NULL	NULL	NULL	NULL
NULL	NULL
NULL	NULL
		NULL			NULL
		NULL			NULL

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

Дата добавления: 2014-01-03