otchet_k_laboratornoy_rabote_5
.docxПсковский государственный политехнический институт.
Кафедра ИСТ
Лабораторная работа № 5
«Сложные запросы»
Работу выполнили:
Студенты гр. 084-1004
Авдонин А. М.
Проверил:
Преподаватель
Мотина Н. В,
Псков
2011
Следующие запросы к базе данных booktown рекомендуется набирать в файлах (по одному запросу в файле).
-
Пусть автор получает половину от стоимости (cost) каждой проданной книги. Определить прибыль каждого автора. Отсортировать по авторам.
SELECT DISTINCT T.last_name,T.first_name, SUM(T.PROFIT)
FROM
(SELECT last_name,first_name,COUNT(isbn)*(cost/2) AS PROFIT
FROM editions --доход автора за каждую книгу
LEFT JOIN shipments
LEFT JOIN stock
RIGHT JOIN books ON (book_id = books.id)
RIGHT JOIN authors ON (author_id = authors.id)
GROUP BY last_name,first_name,isbn,cost) AS T
GROUP BY T.last_name,T.first_name,T.PROFIT
ORDER BY T.last_name;
-
Определить, сколько книг было на складе до продажи. Отсортировать по кодам isbn.
SELECT isbn,COUNT(shipments.isbn)+ stock AS ALL
FROM editions
LEFT JOIN stock
LEFT JOIN shipments
GROUP BY isbn,stock
ORDER BY isbn;
-
Список тем, нашедших отражение в произведениях только одного автора. Отсортировать по названию темы.
SELECT subject
FROM books
JOIN subjects ON (subject_id = subjects.id)
GROUP BY subject
HAVING COUNT(DISTINCT author_id) = 1
ORDER BY subject;
-
Список покупателей, купивших более одной книги, но все на одну и ту же тему. Отсортировать по покупателю.
SELECT last_name,first_name
FROM shipments
JOIN customers ON (customer_id = customers.id)
NATURAL JOIN editions
JOIN books ON (editions.book_id = books.id)
JOIN subjects ON (books.subject_id = subjects.id)
GROUP BY last_name,first_name
HAVING COUNT(isbn) > 1 AND COUNT(DISTINCT subject_id) = 1
ORDER BY last_name,first_name;
-
Список покупателей, купивших книги и в твердой, и в бумажной обложках. Отсортировать по покупателям.
SELECT last_name,first_name
FROM shipments
JOIN customers ON (customer_id = customers.id)
NATURAL JOIN editions
GROUP BY last_name,first_name
HAVING COUNT (DISTINCT type) = 2
ORDER BY last_name,first_name;
-
Список тем, книги по которым выдержали наибольшее количество изданий.
SELECT subject
FROM editions AS s1
JOIN books ON (book_id = books.id)
JOIN subjects ON (subject_id = subjects.id)
WHERE s1.edition >= ALL (SELECT s2.edition
FROM editions AS s2)
GROUP BY subject;
-
Название самой популярной у покупателей темы (тем).
SELECT subject
FROM shipments
NATURAL JOIN editions
JOIN books ON (editions.book_id = books.id)
JOIN subjects ON (books.subject_id = subjects.id)
JOIN publishers ON (editions.publisher_id = publishers.id)
GROUP BY subject,isbn
HAVING COUNT(isbn) >= ALL(SELECT COUNT(isbn) –-количество покупок
FROM shipments --каждой книги
GROUP BY isbn);
-
Код isbn, автор и издатель бестселлера(ов) – самой покупающейся книги.
SELECT isbn,name,last_name,first_name
FROM shipments
NATURAL JOIN editions
JOIN books ON (editions.book_id = books.id)
JOIN authors ON (books.author_id = authors.id)
JOIN publishers ON (editions.publisher_id = publishers.id)
GROUP BY isbn,name,last_name,first_name
HAVING COUNT(isbn) >= ALL( SELECT COUNT(isbn) –-подсчет количества
FROM shipments –-покупок для книги
GROUP BY isbn);
-
Издатель, средняя цена книг которого самая дешевая.
SELECT name
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
NATURAL JOIN stock
GROUP BY name
HAVING AVG(retail) <= ALL(SELECT AVG(retail) –-выбор средней
FROM editions --цены по издателям
JOIN publishers ON (publisher_id = publishers.id)
NATURAL JOIN stock
GROUP BY name );
-
Для каждой темы определить, сколько книг издано в жесткой и сколько – в бумажной обложках. Отсортировать по теме.
SELECT subject,type,COUNT(subject)
FROM editions
JOIN books ON (book_id = books.id)
JOIN subjects ON (books.subject_id = subjects.id)
GROUP BY subject,type
ORDER BY subject;
-
Фамилии авторов, писавших на одну и ту же тему несколько раз. Отсортировать по фамилии автора.
SELECT DISTINCT last_name
FROM books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)
GROUP BY last_name,subject
HAVING COUNT (subject) > 1
ORDER BY last_name;
-
Список покупателей, покупавших одно и то же произведение несколько раз. Отсортировать по покупателю.
SELECT isbn,last_name,first_name
FROM shipments
JOIN customers ON (customer_id = customers.id)
GROUP BY isbn,last_name,first_name
HAVING COUNT(isbn) >1
ORDER BY last_name,first_name;
-
Список авторов, не написавших ни одной книги. Отсортировать по автору.
SELECT last_name, first_name
FROM authors LEFT JOIN books ON(author_id = authors.id)
WHERE title IS NULL
ORDER BY last_name,first_name;
-
Названия тем, которые никогда не были изданы. Упорядочить по названию темы.
SELECT subject
FROM subjects
WHERE NOT EXISTS (SELECT isbn – выбор изданных книг и тем
FROM editions JOIN books ON(book_id = books.id)
WHERE subject_id = subjects.id)
ORDER BY subject;
-
Коды ISBN книг с наименьшей разницей между ценой закупки и ценой продажи.
SELECT isbn
FROM stock
WHERE ABS(retail - cost) <= ALL(SELECT ABS(retail-cost)
FROM stock); --выбор наименьшей
-- по величине разницы цен книг
-
Название издательства (издательств), выпустивших наибольшее количество книг (с различными isbn).
SELECT DISTINCT name
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
GROUP BY name,isbn
HAVING COUNT(DISTINCT isbn) >= ALL (SELECT COUNT(DISTINCT isbn)
FROM editions JOIN publishers ON(publisher_id = publishers.id)
GROUP BY name);
--выбор: количество разных книг для каждого издателя
-
Список издателей, книги которых хуже всего продаются.
SELECT name
FROM editions
LEFT JOIN shipments
JOIN publishers ON (publisher_id = publishers.id)
GROUP BY name
HAVING COUNT(shipments.isbn) <= ALL (SELECT COUNT(shipments.isbn)
FROM editions
LEFT JOIN shipments
JOIN publishers ON (publisher_id = publishers.id)-- выбор кол-ва продаж по издателям
GROUP BY name );
-
Все различные пары кодов isbn, относящихся к одной и той же книге.
SELECT DISTINCT T1.isbn,T2.isbn
FROM editions AS T1,editions AS T2
WHERE T1.book_id = T2.book_id AND T1.isbn < T2.isbn;
-
Все различные пары авторов, писавших на одну и ту же тему.
SELECT DISTINCT T1.last_name,T1.first_name,T2.last_name,T2.first_name
FROM (books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)) AS T1 CROSS JOIN (books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)) AS T2
WHERE T1.subject = T2.subject AND T1.first_name < T2.first_name;
-
Все различные пары авторов, никогда не писавших на одну и ту же тему.
SELECT DISTINCT
T1.last_name,T1.first_name,T2.last_name,T2.first_name
FROM (books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)) AS T1 CROSS JOIN (books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)) AS T2
WHERE T1.author_id < T2.author.id
EXCEPT
SELECT DISTINCT T1.last_name,T1.first_name,T2.last_name,T2.first_name
FROM (books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)) AS T1 CROSS JOIN (books
JOIN subjects ON (subject_id = subjects.id)
JOIN authors ON (author_id = authors.id)) AS T2
WHERE T1.subject = T2.subject;
--выбор авторов писавших на одну и ту же тему