Разработка базы данных «Бакалея»

 

Тексты запросов и примеры работы

 

• Для каждого вида бакалейных товаров указать сведения о нём (наименование, дату выпуска, поставщик, цена, вес и т.п.).

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

 

• Для каждого вида бакалеи выдать список, отсортированный:
• по дате выпуска,
• по весу,
• по стоимости.

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

ORDER BY "Дата выпуска"

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

ORDER BY "Вес"

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

ORDER BY "Цена"

 

• Найти самый дорогой вид бакалеи, самый дешёвый, среднюю стоимость по каждому виду и в целом.

Команда:

 

SELECT

type as "Вид",

AVG(price) as "Средняя стоимость"

FROM product

GROUP BY type

ORDER BY "Средняя стоимость" DESC

LIMIT 1

 

 

Команда:

 

SELECT

type as "Вид",

AVG(price) as "Средняя стоимость"

FROM product

GROUP BY type

ORDER BY "Средняя стоимость"

LIMIT 1

 

 

Команда:

 

SELECT

type as "Вид",

AVG(price) as "Средняя стоимость"

FROM product

GROUP BY type

 

 

 

 

 

Команда:

 

SELECT

AVG(price) as "Средняя стоимость"

FROM product

 

 

• Найти изделия с ценой свыше 20 р. (и любая другая сумма, т.е. предусмотреть ввод цены с клавиатуры).

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE product.price > 20

 

 

• Найти долю изделия заданного производителя (выбор) от общего числа изделий.

 

Команда:

 

SELECT

name as "Поставщик",

(CAST((SELECT COUNT(*) FROM product WHERE provider_id = 22) AS REAL)/

CAST((SELECT COUNT(*) FROM product) AS REAL) * 100.0) AS "Процент товаров поставщика"

FROM provider

WHERE provider_id = 22

 

 

• Найти все изделия с заданной датой выпуска (ввод даты).

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE product.date = '04.05.2013'

 

 

• Найти все изделия, чей вес находится в заданных пределах (ввод интервала) для заданного производителя (выбор) и в целом.

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE

(product.weight BETWEEN 1 AND 2) AND

(provider_id = 22)

 

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE

product.weight BETWEEN 1 AND 2

 

 

• Найти долю изделий, чья стоимость находится в заданных пределах (ввод интервала) от общего количества изделий.

Команда:

 

SELECT

(CAST((SELECT COUNT(*) FROM product WHERE price BETWEEN 30 AND 60) AS REAL)/

CAST((SELECT COUNT(*) FROM product) AS REAL) * 100.0) AS "Процент товаров"

 

 

• Найти все изделия, поступившие от заданного поставщика (выбор поставщика), чья стоимость больше заданной (ввод стоимости).

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE

(product.price > 20) AND

(provider_id = 22)

 

 

• Найти все изделия заданного производителя (выбор).

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE

provider_id = 22

 

• Найти долю изделий, проданных за определённый период (ввод периода) от общего времени продажи.

 

Команда:

 

SELECT

(CAST((SELECT COUNT(*) FROM sales WHERE date BETWEEN '20.05.2013' AND '31.05.2013') AS REAL)/

CAST((SELECT COUNT(*) FROM sales) AS REAL) * 100.0) AS "Процент товаров"

 

 

• Найти долю дешёвых изделий (чья стоимость меньше заданной, ввод стоимости), поступивших от заданного поставщика и в целом.

 

Команда:

 

SELECT

name as "Поставщик",

(CAST((SELECT COUNT(*) FROM product WHERE (provider_id = 22) AND (price < 40)) AS REAL)/

CAST((SELECT COUNT(*) FROM product) AS REAL) * 100.0) AS "Процент товаров поставщика"

FROM provider

WHERE provider_id = 22

 

 

Команда:

 

SELECT

(CAST((SELECT COUNT(*) FROM product WHERE price < 40) AS REAL)/

CAST((SELECT COUNT(*) FROM product) AS REAL) * 100.0) AS "Процент товаров"

 

 

• Найти среднюю стоимость изделий, проданных за определённый промежуток времени (ввод интервала).

 

Команда:

 

SELECT AVG(product.price) AS "Средняя стоимость"

FROM product

INNER JOIN sales USING (product_id)

WHERE sales.date BETWEEN '20.05.2013' and '31.05.2013'

 

 

• Найти все изделия, чья стоимость выше, чем средняя стоимость изделий заданного производителя (выбор).

 

Команда:

 

SELECT

product.type as "Вид",

product.name as "Наименование",

product.date as "Дата выпуска",

provider.name as "Поставщик",

product.price as "Цена",

product.weight as "Вес"

FROM product

INNER JOIN provider USING (provider_id)

WHERE product.price > (SELECT AVG(price) FROM product WHERE provider_id = 15)

 

 

Анализ результатов  и выводы

 

В результате проделанной работы мы изучили базовые операции по работе с базой данных; изучили синтаксис команд; приобрели навыки создания, заполнения и модификации таблиц базы данных, работы с транзакциями в PostgreSQL, приобрели навыки создания запросов в PostgreSQL.

 

Список использованной литературы

 

1. Дейт К.Дж. Введение в системы баз данных. –Пер. с англ. –6-е изд. –К. Диалектика, 1998. Стр. 394 – 410.
2. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: полное руководство: пер.с англ. –К.: Издательская группа BHV, 2000.–608с. Стр. 329–368.