A1 a2 c1 c2

5 5 5 5

5 5 8 6

5 5 3 7

NULL 7

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

NULL NULL 5

NULL NULL 5

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

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;

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

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));

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

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;

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;

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; +