Студопедия

КАТЕГОРИИ:


Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748)

Лекция 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; Просмотров: 2197; Нарушение авторских прав?; Мы поможем в написании вашей работы!


Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет



studopedia.su - Студопедия (2013 - 2024) год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! Последнее добавление




Генерация страницы за: 0.067 сек.