Текст задания 

База данных книготорговой компании (PUBS)

Рассмотрим предметную область, связанную с книгоизданием и маркетингом. В рамках данной предметной области существуют издатели, которые публикуют книги, авторы, которые книги пишут, и издания (сами книги). Разработана база данных pubs, определяющая описанную выше предметную область. Инфологическая модель предметной области с использованием диаграмм “сущность-связь” (ER-диаграмм), представлена на рис.

На данном рисунке прямоугольниками обозначены типы сущностей (объектов), а ромбами – типы связей между сущностями. Атрибуты сущностей указаны мелким шрифтом в том же прямоугольнике, который отображает типы сущностей. Имя типа сущности отмечено в верхней части прямоугольника жирным шрифтом.

1. Изучить состав базы данных книготорговой компании (база данных pubs), структуру и семантику ее таблиц.
   
2. Получить у преподавателя вариант задания.
   
3. Выполнить приведенные в первой части варианта SQL-запросы на SQL-сервере и проинтерпретировать результаты выполнения запросов.
   
4. В соответствии с заданиями второй части варианта составить SQL-запросы по их заданному содержательному описанию. Выполнить SQL-запросы на SQL-сервере и проанализировать результаты их выполнения.
   
5. Оформить отчет.
   

Решение

Для получения доступа к решению варианта №9 (его условия см. ниже) необходимо добавить работу в корзину и оформить заказ. Как только Вы оплатите заказ (прямо на сайте) - вам откроется возможность скачать решение контрольной работы (Вариант №9). Решение оформлено в виде отчета в Word.

Вариант 9

Часть 1

1) SELECT DISTINCT state 

FROM authors 

ORDER BY state

2) SELECT type, title_id, price 

FROM titles 

WHERE title_id like ‘B[AUN]7832’  Цена и тип издания  идентификатора 7832

3) SELECT COUNT(*) 

FROM authors 

WHERE state=’CA’    Авторы штата Калифорния

4) SELECT title 

FROM titles 

WHERE ytd_sales IS NULL   Выбрать издание не проданных.

5) SELECT pub_name, AVG(price), COUNT(DISTINCT title_id) ‘count’ 

FROM titles t JOIN publishers p ON t.pub_id=p.pub_id 

GROUP BY pub_name

6) SELECT type, (MIN(price)+MAX(price))/2, AVG(price) 

FROM titles 

GROUP BY type 

HAVING type<>’UNDECIDED’

ORDER BY 2 DESC

7) SELECT DISTINCT au_lname, au_fname 

FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id 

JOIN titles t ON ta.title_id=t.title_id JOIN publishers p ON p.pub_id=t.pub_id 

WHERE p.state=’CA’

ORDER BY au_lname, au_fname

8) SELECT title, COUNT(DISTINCT a.au_id) 

FROM titles t JOIN titleauthor ta ON t.title_id=ta.title_id 

JOIN authors a ON ta.au_id=a.au_id 

JOIN publishers p ON p.pub_id=t.pub_id 

GROUP BY title

9) SELECT title, type, price 

FROM titles 

WHERE price>ALL (SELECT price 

FROM titles 

WHERE type=’psychology’)

10) SELECT city 

FROM authors 

UNION SELECT city 

FROM publishers

Часть 2

1. Выбрать имена и фамилии авторов, проживающих в Юте.

SELECT au_lname,au_fname

FROM authors 

WHERE state='UT'

2. Выбрать информацию обо всех книгах, упорядоченную по убыванию типа книги и числу проданных книг. Информация о книгах должна включать идентификатор и тип книги, а также число проданных книг.

ELECT type, title_id, ytd_sales 

FROM titles

ORDER BY ytd_sales DESC

3. Выбрать информацию о книгах, идентификаторы которых начинаются буквой “B”, а заканчивается символом “2”. Информация о книгах должна включать тип, идентификатор и цену книги.

SELECT type, title_id, price 

FROM titles 

WHERE title_id like'B%2'

1. Определить среднюю цену и сумму цен на книги по бизнесу и современной кулинарии для каждой комбинации типа книги и идентификатора издателя.

SELECT type,pub_id,AVG(price)'avg',SUM(price)'suma'

FROM titles 

WHERE  type in ('business','mod_cook')

GROUP BY pub_id,type

2. Определить, сколько авторов имеет каждая изданная книга.

3. Найти авторов самых распродаваемых книг.

4. Найти города, в которых живут авторы или находятся издательства.

5. Выбрать информацию о книгах, упорядоченную по убыванию объема продаж (по стоимости). Информация о книгах должна включать идентификатор, цену, объем продаж (по количеству) и объем продаж (по стоимости).

6. Определить, кто из авторов написал какую книгу по психологии. В выбираемые данные включить имя и фамилию автора, а также название книги. 

8. Найти авторов самых дорогих книг, вышедших в США. В запросе использовать подзапрос и операцию группирования.