otchet_k_laboratornoy_rabote_4
.docxПсковский государственный политехнический институт.
Кафедра ИСТ
Лабораторная работа № 3
«Итоговые запросы»
Работу выполнили:
Студенты гр. 084-1004
Авдонин А. М.
Проверил:
Преподаватель
Мотина Н. В,
Псков
2011
Ход выполнения задания:
-
Общее количество книг на складе (stock).
SELECT SUM(stock) AS total
FROM stock;
-
Количество книг с розничной ценой выше 30.
SELECT SUM(stock) AS total
FROM stock
WHERE retail >30;
-
Выручка магазина при реализации всех книг.
SELECT SUM(retail*stock) AS total_profit
FROM stock;
-
Средняя цена (cost) книги.
SELECT AVG(cost) AS average_cost
FROM stock;
-
Средняя цена (cost) для имеющихся книг на складе (stock).
SELECT AVG(cost) AS average_cost
FROM stock
WHERE stock > 0;
-
Даты наиболее ранней и наиболее поздней публикаций.
SELECT MIN(publication) AS oldest,MAX(publication) AS earliest
FROM editions;
-
Количество совершенных покупок.
SELECT COUNT(id) AS total
FROM shipments;
-
Количество покупателей, совершавших покупки.
SELECT COUNT(DISTINCT customer_id) AS total
FROM shipments;
-
Количество покупателей с различными фамилиями, совершавших покупки.
SELECT COUNT(DISTINCT last_name) AS total
FROM shipments JOIN customers ON (customer_id = customers.id);
-
Количество авторов, написавших какие-либо книги.
SELECT COUNT(DISTINCT authors) AS total
FROM editions
JOIN books ON (book_id = books.id)
JOIN authors ON (books.author_id = authors.id);
-
Количество изданий, выпущенных в твердой и мягкой обложке.
SELECT type,COUNT(isbn)
FROM editions
GROUP BY type;
-
Количество первых изданий, вторых и так далее. В таком же порядке.
SELECT edition,COUNT(isbn)
FROM editions
GROUP BY edition
ORDER BY edition;
-
Количество книг, которые приобрел каждый покупатель. Отсортировать по покупателю.
SELECT last_name,first_name,COUNT(isbn)
FROM shipments
JOIN customers ON (customer_id = customers.id)
GROUP BY last_name,first_name
ORDER BY last_name,first_name;
-
Список покупателей-однофамильцев с подсчетом их количества.
SELECT last_name,COUNT(id)
FROM customers
GROUP BY last_name;
HAVING COUNT(id) > 1;
-
Количество кодов isbn, которые были изданы каждым издательством. Отсортировать по названию издательства.
SELECT name,COUNT(isbn)
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
GROUP BY name
ORDER BY name;
-
Количество книг различных названий, которые выпустило каждое издательство. Отсортировать по названию издательства.
SELECT name,COUNT(DISTINCT title) AS total
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
JOIN books ON (book_id = books.id)
GROUP BY name
ORDER BY name;
-
Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).
SELECT name,COUNT(isbn) AS total
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
WHERE address LIKE '%New York%'
GROUP BY name
ORDER BY total DESC;
-
Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Издательства, выпустившие менее двух изданий (кодов isbn), не выводить. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).
SELECT name,COUNT(isbn) AS total
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
WHERE address LIKE '%New York%'
GROUP BY name
HAVING COUNT(isbn) >2
ORDER BY total DESC;
-
Первая дата публикации для каждого автора. Упорядочить по датам.
SELECT last_name,first_name,MIN(publication) AS first
FROM editions
JOIN books ON (book_id = books.id)
JOIN authors ON (books.author_id = authors.id)
GROUP BY last_name,first_name
ORDER BY first;
-
Фамилии авторов, чьи произведения не имеют изданий выше второго.
SELECT DISTINCT last_name
FROM editions
JOIN books ON (book_id = books.id)
JOIN authors ON (books.author_id = authors.id)
WHERE edition <= 2 ;
-
Фамилии авторов, все издания которых имеются в наличии в количестве не менее 50 экземпляров.
SELECT last_name
FROM editions
JOIN books ON (book_id = books.id)
JOIN authors ON (books.author_id = authors.id)
JOIN stock ON (editions.isbn = stock.isbn)
GROUP BY last_name,stock
HAVING SUM( stock) >= 50;
-
Фамилии авторов, все книги которых выходили только в бумажной обложке. Отсортировать по фамилии автора.
SELECT DISTINCT last_name
FROM editions
JOIN books ON (book_id = books.id)
JOIN authors ON (books.author_id = authors.id)
GROUP BY last_name,type
HAVING type = 'p';
-
Наибольшее количество книг, написанных одним автором.
SELECT last_name,COUNT(isbn)
FROM editions
JOIN books ON (book_id = books.id)
JOIN authors ON (books.author_id = authors.id)
GROUP BY last_name;
-
Наименьшее количество изданий, выпущенных одним издательством.
SELECT DISTINCT name,COUNT(edition)
FROM editions
JOIN publishers ON (publisher_id = publishers.id)
GROUP BY name,edition;