Главная / Базы данных /
Введение в модель данных SQL / Тест 5
Введение в модель данных SQL - тест 5
Упражнение 1:
Номер 1
База данных АВТОРЫ-КНИГИ-ИЗДАТЕЛЬСТВА
(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) |
Автор уникально идентифицируется своим идентификатором AUTHOR_ID
. Уникальным идентификатором книги является ISBN
(как выглядит ISBN
, можно посмотреть в выходных данных любой книги). Издательство идентифицируется уникальным идентификатором PUB_ID
. В таблицах AUTHORS
, BOOKS
и PUBLISHERS
столбцы AUTHOR_ID
, ISBN
и PUB_ID
соответственно являются первичными ключами. В таблице AUTHORS-BOOKS
столбцы AUTHOR_ID
и ISBN
являются внешними ключами, ссылающимися на первичные ключи таблиц AUTHORS
и BOOKS
соответственно. В таблице BOOKS
столбец PUB_ID
является внешним ключом, ссылающимся на первичный ключ таблицы PUBLISHERS
.
У каждой книги могут быть один или несколько авторов. Допускается, что авторы книги временно неизвестны. В таблице авторов могут содержаться данные только об авторах книг, описываемых в таблице BOOKS
. Каждая книга может быть издана только одним издательством, но для некоторых книг издательство может быть временно неизвестно. Столбец DATE_OF_AGREEMENT
содержит дату заключения договора на написание книги между автором и издательством. Столбец AGREEMENT_DURAT
содержит временной интервал, в течение которого действует договор. Столбец DATE_OF_PUB
содержит даты публикации книг.
Также будем использовать таблицы table1 (a1, a2, c1, c2)
и table2 (b1, b2, c1, c2)
со следующими телами:
table1
a1 | a2 | c1 | c2 |
5 | 5 | 5 | 5 |
5 | 5 | 8 | 6 |
5 | 5 | 3 | 7 |
2 | 3 | NULL | 7 |
4 | NULL | NULL | NULL |
table2
b1 | b2 | c1 | c2 |
5 | 5 | 5 | 5 |
1 | 2 | 5 | 6 |
3 | 8 | 5 | 5 |
3 | 8 | 5 | 5 |
8 | 8 | 8 | 8 |
3 | NULL | NULL | 5 |
3 | NULL | NULL | 5 |
Требуется сформулировать запрос "Для каждого издательства выдать название издательства, его адрес, среднюю и максимальную цены издававшихся книг и общее число издававшихся авторов". Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (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;
 
 (2)
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;
 
 (3)
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;
 
Номер 2
База данных АВТОРЫ-КНИГИ-ИЗДАТЕЛЬСТВА
(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) |
Автор уникально идентифицируется своим идентификатором AUTHOR_ID
. Уникальным идентификатором книги является ISBN
(как выглядит ISBN
, можно посмотреть в выходных данных любой книги). Издательство идентифицируется уникальным идентификатором PUB_ID
. В таблицах AUTHORS
, BOOKS
и PUBLISHERS
столбцы AUTHOR_ID
, ISBN
и PUB_ID
соответственно являются первичными ключами. В таблице AUTHORS-BOOKS
столбцы AUTHOR_ID
и ISBN
являются внешними ключами, ссылающимися на первичные ключи таблиц AUTHORS
и BOOKS
соответственно. В таблице BOOKS
столбец PUB_ID
является внешним ключом, ссылающимся на первичный ключ таблицы PUBLISHERS
.
У каждой книги могут быть один или несколько авторов. Допускается, что авторы книги временно неизвестны. В таблице авторов могут содержаться данные только об авторах книг, описываемых в таблице BOOKS
. Каждая книга может быть издана только одним издательством, но для некоторых книг издательство может быть временно неизвестно. Столбец DATE_OF_AGREEMENT
содержит дату заключения договора на написание книги между автором и издательством. Столбец AGREEMENT_DURAT
содержит временной интервал, в течение которого действует договор. Столбец DATE_OF_PUB
содержит даты публикации книг.
Также будем использовать таблицы table1 (a1, a2, c1, c2)
и table2 (b1, b2, c1, c2)
со следующими телами:
table1
a1 | a2 | c1 | c2 |
5 | 5 | 5 | 5 |
5 | 5 | 8 | 6 |
5 | 5 | 3 | 7 |
2 | 3 | NULL | 7 |
4 | NULL | NULL | NULL |
table2
b1 | b2 | c1 | c2 |
5 | 5 | 5 | 5 |
1 | 2 | 5 | 6 |
3 | 8 | 5 | 5 |
3 | 8 | 5 | 5 |
8 | 8 | 8 | 8 |
3 | NULL | NULL | 5 |
3 | NULL | NULL | 5 |
Требуется сформулировать запрос "Найти названия всех издательств, в которых было издано больше пяти книг Дейта". Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
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;
 
 (2)
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;
 
 (3)
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;
 
Номер 3
База данных АВТОРЫ-КНИГИ-ИЗДАТЕЛЬСТВА
(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) |
Автор уникально идентифицируется своим идентификатором AUTHOR_ID
. Уникальным идентификатором книги является ISBN
(как выглядит ISBN
, можно посмотреть в выходных данных любой книги). Издательство идентифицируется уникальным идентификатором PUB_ID
. В таблицах AUTHORS
, BOOKS
и PUBLISHERS
столбцы AUTHOR_ID
, ISBN
и PUB_ID
соответственно являются первичными ключами. В таблице AUTHORS-BOOKS
столбцы AUTHOR_ID
и ISBN
являются внешними ключами, ссылающимися на первичные ключи таблиц AUTHORS
и BOOKS
соответственно. В таблице BOOKS
столбец PUB_ID
является внешним ключом, ссылающимся на первичный ключ таблицы PUBLISHERS
.
У каждой книги могут быть один или несколько авторов. Допускается, что авторы книги временно неизвестны. В таблице авторов могут содержаться данные только об авторах книг, описываемых в таблице BOOKS
. Каждая книга может быть издана только одним издательством, но для некоторых книг издательство может быть временно неизвестно. Столбец DATE_OF_AGREEMENT
содержит дату заключения договора на написание книги между автором и издательством. Столбец AGREEMENT_DURAT
содержит временной интервал, в течение которого действует договор. Столбец DATE_OF_PUB
содержит даты публикации книг.
Также будем использовать таблицы table1 (a1, a2, c1, c2)
и table2 (b1, b2, c1, c2)
со следующими телами:
table1
a1 | a2 | c1 | c2 |
5 | 5 | 5 | 5 |
5 | 5 | 8 | 6 |
5 | 5 | 3 | 7 |
2 | 3 | NULL | 7 |
4 | NULL | NULL | NULL |
table2
b1 | b2 | c1 | c2 |
5 | 5 | 5 | 5 |
1 | 2 | 5 | 6 |
3 | 8 | 5 | 5 |
3 | 8 | 5 | 5 |
8 | 8 | 8 | 8 |
3 | NULL | NULL | 5 |
3 | NULL | NULL | 5 |
Какая из показанных ниже таблиц является результатом операции table1 LEFT OUTER JOIN table2 ON a2=b1 AND a1<b2
?
Ответ:
 
(1)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | NULL | NULL |
5 | 5 | 8 | 6 | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL |
2 | 3 | NULL | 7 | 3 | 8 |
2 | 3 | NULL | 7 | 3 | 8 |
4 | NULL | NULL | NULL | NULL | NULL |
 
 
(2)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | NULL | NULL | NULL | NULL |
5 | 5 | 8 | 6 | NULL | NULL | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL | NULL | NULL |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
 
(3)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | NULL | NULL | NULL | NULL |
5 | 5 | 8 | 6 | NULL | NULL | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL | NULL | NULL |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
2 | 3 | NULL | 7 | 3 | NULL | NULL | 5 |
2 | 3 | NULL | 7 | 3 | NULL | NULL | 5 |
4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
Упражнение 2:
Номер 1
Требуется сформулировать запрос “Найти названия издательств, у которых максимальная цена книги равна средней цене книги какого-либо другого издательства”. Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
SELECT PUB_NAME
FROM PUBLISHERS, BOOKS
WHERE PUBLISHERS.PUB_ID = BOOKS.PUB_ID
GROUP BY PUBLISHERS.PUB_ID
HAVING MAX (PRICE) IN (SELECT AVG (PRICE)
FROM PUBLISHERS PUBLISHERS1, BOOKS
WHERE PUBLISHERS1.PUB_ID <>
PUBLISHERS.PUB_ID
GROUP BY PUBLISHERS1.PUB_ID);
 
 (2)
SELECT PUB_NAME
FROM PUBLISHERS
WHERE (SELECT MAX (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);
 
 (3)
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
Требуется сформулировать запрос “Найти название издательств и общее число изданных ими книг для тех издательств, для которых ни в одном другом издательстве не издавалось то же общее число книг”. Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
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;
 
 (2)
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));
 
 (3)
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));
 
Номер 3
Какая из показанных ниже таблиц является результатом операции table1 NATURAL FULL OUTER JOIN table2
?
Ответ:
 
(1)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 8 | 6 | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL |
2 | 3 | NULL | 7 | NULL | NULL |
4 | NULL | NULL |
NULL | NULL | NULL |
NULL | NULL | 5 | 6 | 1 | 2 |
NULL | NULL | 8 | 8 | 8 | 8 |
NULL | NULL | NULL | 5 | 3 | NULL |
NULL | NULL | NULL | 5 | 3 | NULL |
 
 
(2)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 | 5 | 5 |
5 | 5 | 8 | 6 | NULL | NULL | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL | NULL | NULL |
2 | 3 | NULL | 7 | NULL | NULL | NULL | NULL |
4 | NULL | NULL | 6 | NULL | NULL | NULL | NULL |
NULL | NULL | 5 | 6 | 1 | 2 | 5 | 6 |
NULL | NULL | 8 | 8 | 8 | 8 | 8 | 8 |
NULL | NULL | NULL | 5 | 3 | NULL | NULL | 5 |
NULL | NULL | NULL | 5 | 3 | NULL | NULL | 5 |
 
 
(3)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 8 | 6 | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL |
2 | 3 | NULL | 7 | NULL | NULL |
4 | NULL | NULL | 6 | NULL | NULL |
NULL | NULL | 5 | 6 | 1 | 2 |
NULL | NULL | 8 | 8 | 8 | 8 |
5 | 5 | NULL | 5 | 3 | NULL |
5 | 5 | NULL | 5 | 3 | NULL |
 
Упражнение 3:
Номер 1
Требуется сформулировать запрос “Найти названия издательств, издавших не меньше книг, чем издательство Morgan Kauffman
, и не больше, чем издательство Addison-Wesley
”. Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
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’);
 
 (2)
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’);
 
 (3)
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
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);
 
 (2)
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);
 
 (3)
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);
 
Номер 3
Какая из показанных ниже таблиц является результатом операции table1 FULL OUTER JOIN table2 ON a2=b1 AND a1<b2
?
Ответ:
 
(1)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
5 | 5 | 8 | 6 | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
5 | 5 | 3 | 7 | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | 8 | 8 | 8 | 8 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
NULL | NULL | NULL | NULL | 1 | 2 | 5 | 6 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
NULL | NULL | NULL | NULL | 5 | 5 | 5 | 5 |
4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
 
(2)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | NULL | NULL | NULL | NULL |
5 | 5 | 8 | 6 | NULL | NULL | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL | NULL | NULL |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | 5 | 5 | 5 | 5 |
NULL | NULL | NULL | NULL | 1 | 2 | 5 | 6 |
NULL | NULL | NULL | NULL | 8 | 8 | 8 | 8 |
NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
 
 
(3)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | NULL | NULL |
5 | 5 | 8 | 6 | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL |
2 | 3 | NULL | 7 | 3 | 8 |
2 | 3 | NULL | 7 | 3 | 8 |
4 | NULL | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | 5 | 5 |
NULL | NULL | NULL | NULL | 1 | 2 |
NULL | NULL | NULL | NULL | 8 | 8 |
NULL | NULL | NULL | NULL | 3 | NULL |
NULL | NULL | NULL | NULL | 3 | NULL |
 
Упражнение 4:
Номер 1
Требуется сформулировать запрос “Для каждого издательства выдать имя каждого автора, издававшего книги в данном издательстве, и число изданных им книг в данном издательстве”. Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
SELECT AUTHOR_NAME, COUNT(*)
FROM AUTHORS, AUTHORS-BOOKS,
BOOKS, PUBLISHERS.PUB_ID
WHERE BOOKS.PUB_ID =
PUBLISHERS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
AND AUTHORS-BOOKS.AUTHOR_ID =
AUTHORS.AUTHOR_ID
GROUP BY PUBLISHERS.PUB_ID,
AUTHOR_ID, AUTHOR_NAME;
 
 (2)
SELECT AUTHOR_NAME, COUNT(*)
FROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERS
WHERE BOOKS.PUB_ID = PUBLISHERS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
AND AUTHORS-BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID
GROUP BY PUBLISHERS.PUB_ID, AUTHOR_NAME;
 
 (3)
SELECT AUTHOR_NAME, COUNT(*)
FROM AUTHORS, AUTHORS-BOOKS, BOOKS, PUBLISHERS
WHERE BOOKS.PUB_ID = PUBLISHERS.PUB_ID
AND BOOKS.ISBN = AUTHORS-BOOKS.ISBN
AND AUTHORS-BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID
GROUP BY PUBLISHERS.PUB_ID, AUTHOR_ID;
 
Номер 2
Какая из показанных ниже таблиц является результатом операции table1 NATURAL RIGHT OUTER JOIN table2
?
Ответ:
 
(1)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | 5 | 5 |
NULL | NULL | 5 | 6 | 1 | 2 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 5 | 5 | 3 | 8 |
NULL | NULL | 8 | 8 | 8 | 8 |
NULL | NULL | NULL | 5 | 3 | NULL |
NULL | NULL | NULL | 5 | 3 | NULL |
 
 
(2)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
NULL | NULL | 5 | 6 | 1 | 2 | 5 | 6 |
5 | 5 | 5 | 5 | 3 | 8 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 | 5 | 5 |
NULL | NULL | 8 | 8 | 8 | 8 | 8 | 8 |
NULL | NULL | NULL | 5 | 3 | NULL | NULL | 5 |
NULL | NULL | NULL | 5 | 3 | NULL | NULL | 5 |
 
 
(3)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
NULL | 3 | NULL | NULL | 5 | NULL |
NULL | 3 | NULL | NULL | 5 | NULL |
5 | 3 | 5 | 5 | 5 | 8 |
NULL | 8 | 8 | NULL | 8 | 8 |
5 | 3 | 5 | 5 | 5 | 8 |
NULL | 1 | 5 | NULL | 6 | 2 |
5 | 5 | 5 | 5 | 5 | 5 |
 
Номер 3
Какая из показанных ниже таблиц является результатом операции table1 RIGHT OUTER JOIN table2 ON a2=b1 AND a1<b2
?
Ответ:
 
(1)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
NULL | NULL | NULL | NULL | 5 | 5 | 5 | 5 |
NULL | NULL | NULL | NULL | 1 | 2 | 5 | 6 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
NULL | NULL | NULL | NULL | 8 | 8 | 8 | 8 |
2 | 3 | NULL | 7 | 3 | NULL | NULL | 5 |
2 | 3 | NULL | 7 | 3 | NULL | NULL | 5 |
 
 
(2)
a1 | a2 | b1 | b2 | c1 | c2 |
---|
NULL | NULL | 5 | 5 | 5 | 5 |
NULL | NULL | 1 | 2 | 5 | 6 |
2 | 3 | 3 | 8 | 5 | 5 |
2 | 3 | 3 | 8 | 5 | 5 |
NULL | NULL | 8 | 8 | 8 | 8 |
NULL | NULL | 3 | NULL | NULL | 5 |
NULL | NULL | 3 | NULL | NULL | 5 |
 
 
(3)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
NULL | NULL | NULL | NULL | 5 | 5 | 5 | 5 |
NULL | NULL | NULL | NULL | 1 | 2 | 5 | 6 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
2 | 3 | NULL | 7 | 3 | 8 | 5 | 5 |
NULL | NULL | NULL | NULL | 8 | 8 | 8 | 8 |
NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
 
Упражнение 5:
Номер 1
Какая из показанных ниже таблиц является результатом операции table1 NATURAL LEFT OUTER JOIN table2
?
Ответ:
 
(1)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 5 | 5 | 3 | NULL |
5 | 5 | 5 | 5 | 3 | NULL |
5 | 5 | 8 | 6 | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL |
2 | 3 | NULL | 7 | NULL | NULL |
4 | NULL | NULL | NULL | NULL | NULL |
 
 
(2)
a1 | a2 | c1 | c2 | b1 | b2 |
---|
5 | 5 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 5 | 5 | 3 | 8 |
5 | 5 | 8 | 6 | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL |
2 | 3 | NULL | 7 | NULL | NULL |
4 | NULL | NULL | NULL | NULL | NULL |
 
 
(3)
a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
---|
5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 | 5 | 5 |
5 | 5 | 5 | 5 | 3 | 8 | 5 | 5 |
5 | 5 | 8 | 6 | NULL | NULL | NULL | NULL |
5 | 5 | 3 | 7 | NULL | NULL | NULL | NULL |
2 | 3 | NULL | 7 | NULL | NULL | NULL | NULL |
4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
Номер 2
Требуется сформулировать запрос “Найти имена и общее число изданных книг авторов, которые издали не менее трех книг в издательстве Morgan Kauffman
”. Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
SELECT AUTHOR_NAME, COUNT(*)
FROM AUTHORS AUTH1,
AUTHORS-BOOKS
WHERE AUTHORS.AUTHOR_ID =
AUTHORS-BOOKS.AUTHOR_ID
AND (SELECT COUNT (*)
FROM AUTHORS, AUTHORS-BOOKS,
BOOKS, PUBLISHERS
WHERE AUTHORS-BOOKS.ISBN =
BOOKS.ISBN
AND BOOKS.PUB_ID =
PUBLISHERS.PUB_ID
AND PUB_NAME =
‘Morgan Kauffman’) >= 3
GROUP BY AUTH1.AUTHOR_ID, AUTHOR_NAME;
 
 (2)
SELECT AUTHOR_NAME, COUNT(*)
FROM AUTHORS AUTH1,
AUTHORS-BOOKS
WHERE AUTH1.AUTHOR_ID =
AUTHORS-BOOKS.AUTHOR_ID
AND (SELECT COUNT (*)
FROM AUTHORS, AUTHORS-BOOKS,
BOOKS, PUBLISHERS
WHERE AUTH1.AUTHOR_ID =
AUTHORS.AUTHOR_ID
AND AUTHORS-BOOKS.ISBN =
BOOKS.ISBN
AND BOOKS.PUB_ID =
PUBLISHERS.PUB_ID
AND PUB_NAME =
‘Morgan Kauffman’) >= 3
GROUP BY AUTH1.AUTHOR_ID, AUTHOR_NAME;
 
 (3)
SELECT AUTHOR_NAME, COUNT(*)
FROM AUTHORS AUTH1, AUTHORS-BOOKS
WHERE AUTH1.AUTHOR_ID =
AUTHORS-BOOKS.AUTHOR_ID
AND (SELECT COUNT (*)
FROM AUTHORS, AUTHORS-BOOKS,
BOOKS, PUBLISHERS
WHERE AUTH1.AUTHOR_ID =
AUTHORS.AUTHOR_ID
AND AUTHORS-BOOKS.ISBN =
BOOKS.ISBN
AND BOOKS.PUB_ID =
PUBLISHERS.PUB_ID
AND PUB_NAME =
‘Morgan Kauffman’) >= 3
GROUP BY AUTH1.AUTHOR_ID;
 
Номер 3
Требуется сформулировать запрос “Найти названия книг, изданных Дейтом с двумя соавторами”. Какие из приведенных ниже формулировок являются правильными?
Ответ:
 (1)
SELECT TITLE
FROM AUTHORS-BOOKS, BOOKS
WHERE AUTHORS-BOOKS.ISBN =
BOOKS.ISBN
AND ‘Chris Date’ IN
(SELECT AUTHOR_NAME
FROM AUTHORS,
AUTHORS-BOOKS1
WHERE AUTHORS.AUTHOR_ID =
AUTHORS-BOOKS1.AUTHOR_ID
AND AUTHORS-BOOKS1.ISBN =
AUTHORS-BOOKS.ISBN)
GROUP BY AUTHORS-BOOKS.ISBN, TITLE
HAVING COUNT (*) = 3;
 
 (2)
SELECT TITLE
FROM AUTHORS-BOOKS, BOOKS
WHERE AUTHORS-BOOKS.ISBN = BOOKS.ISBN
AND ‘Chris Date’ IN
(SELECT AUTHOR_NAME
FROM AUTHORS, AUTHORS-BOOKS1
WHERE AUTHORS.AUTHOR_ID =
AUTHORS-BOOKS1.AUTHOR_ID
AND AUTHORS-BOOKS1.ISBN =
AUTHORS-BOOKS.ISBN)
GROUP BY AUTHORS-BOOKS.ISBN
HAVING COUNT (*) = 3;
 
 (3)
SELECT TITLE
FROM AUTHORS,
AUTHORS-BOOKS, BOOKS
WHERE AUTHOR_NAME =
‘Chris Date’
AND AUTHORS.AUTHOR_ID =
AUTHORS-BOOKS.AUTHOR_ID
AND AUTHORS-BOOKS.ISBN =
BOOKS.ISBN
AND (SELECT COUNT (*)
FROM AUTHORS-BOOKS1
WHERE AUTHORS-BOOKS1.ISBN =
AUTHORS-BOOKS.ISBN) = 3;