1. Создайте таблицы с ограничениямы:
-
books: книги
- id (Serial Primary Key)
- title: название (Text)
- publisher_id: id издателя (Integer)
- format: формат
- pages: страницы
- published_date: дата публикации (Date)
-
authors
- id
- name: имя
- country: страна
- birthdate: дата рождения (Date)
-
authors_books: авторы_книги
- author_id: id авторы
- book_id: id книги
-
publishers: издатели
- id
- name: название
-
translators: переводчики
- id
- name: имя
-
translators_books: переводчики_книги
- translator_id: id переводчик
- book_id: id книги
-
rating: рейтинг
- book_id: id книги
- rating: рейтинг
Таким образом мы можем создать таблицы книги и издатели.
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
publisher_id INT,
format TEXT CHECK(format IN ('твердый', 'мягкий')),
pages INT,
published_date DATE DEFAULT CURRENT_DATE,
FOREIGN KEY (publisher_id) REFERENCES publishers(id)
);
CREATE TABLE publishers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
Так как мы в таблице books пишем что publisher_id является Foreign Key (внешним ключом) и ссылается на столбец id из таблицы publishers, нам нужно сперва создать таблицу publishers.
Без таблицы publishers, мы не сможем создать таблицу books и указать там внешний ключ на таблицу publishers.
Создайте остальные таблицы.
2. Анализируйте таблицы
-- Решите вопросы 1-3 используя подзапросы (subqueries)
-- 1. Выведите все книги автора 'Ариана Арвис'
-- 2. Выведите все авторы книги 'Валун'
-- 3. Выведите все рейтинги книги 'Пока мы мечтали'
-- Решите вопросы 4-8 используя JOIN
-- 4. Объедините таблицы books и publishers
-- 5. Объедините таблицы books и publishers и выведите все книги опубликованные издателем у которого название начинается с 'And'
-- 6. Объедините таблицы books и rating и выведите все рейтинги книги 'Парадайс'
-- 7. Объедините таблицы authors и books по третьей таблице authors_books
-- 8. Объедините таблицы authors и books по таблице authors_books,
-- выведите столбцы name из таблицы authors,
-- title, pages, published_date из таблицы books
-- Aggregate функции, GROUP BY, ORDER BY
-- 9. Выведите средний рейтинг каждой книги (таблицы books, rating)
-- 10. Выведите средний рейтинг книги 'Парадайс'
SELECT b.title, ROUND(AVG(r.rating),2) AS "средний_рейтинг"
FROM books b
JOIN rating r ON b.id = r.book_id
GROUP BY b.title
HAVING b.title='Парадайс';
-- 11. Выведите средний рейтинг каждой книги и сортируйте их по средний_рейтинг по убыванию
-- View (Представление)
-- 12. Объедините таблицы authors и books по таблице authors_books,
-- выведите столбцы name из таблицы authors,
-- title, pages, published_date из таблицы books,
-- и создайте View (представление) authors_books_view
-- 13. Из представлении authors_books_view, выведите книги которые были опубликованы в 2020 году и сортируйте по количеству страниц (pages) по убыванию
SELECT * FROM authors_books_view
WHERE published_date>='2020-01-01' AND published_date<='2020-12-12'
ORDER BY pages DESC;
-- 14. Объедините таблицы books и rating,
-- выведите столбцы id, title, pages, published_date из таблицы books, средниц рейтинг из таблицы rating,
-- сгруппируйте по id книги и сортируйте по среднему рейтингу по обыванию (DESC)
-- и создайте временное(Temporary) View(представление) books_ratings_view,
-- 15. Из представлении books_ratings_view, выведите топ 5 книг с самым высоким средним рейтингом
-- 16. Объедините таблицы books и publishers
-- выведите столбцы title из таблицы books и name из таблицы publishers,
-- создайте CTE(Common Table Expression, одноразовое) View(представление) books_publishers_view
-- Insert, Update, Delete
-- 17. Добавьте новый автор в таблицу authors с именем 'Ким Чен Ын', country: 'Korea', birthdate: '1984-01-08'
-- 18. Обновите имя авторы 'Ким Чын Ын' на 'Rumi', country на: Persian, birthdate: '1273-09-30'
-- 19. Удалите автор с именем 'Rumi'
-- CASE
-- 20. Выведите столбцы title, pages,
-- и в новом столбце
-- если количество страниц больше 0 и меньше 100, напишите 'маленькая книги'
-- если количество страниц больше 100 и меньше 200, напишите 'средняя книги'
-- в ином случае напишите 'большая книги' (ELSE)
-- из таблицы books
-- 21. Выведите столбцы title и
-- если формат='мягкий', то 'м'
-- если формат='твердый', то т'
-- из таблицы books
-- INDEX
-- 22. Создайте индекс с названием books_title_index для столбца title из таблицы books чтобы получение данных по названию книги было очень быстрым
CREATE INDEX books_title_index
ON books(title);
-- Процедура (Procedure)
-- 23. Создайте процедуру add_author(name text, country text, birthdate date), используя которого сможем добавить данные в таблицу authors
CREATE PROCEDURE add_author(name text, country text, birthdate date)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO authors (name, country, birthdate)
VALUES (name, country, birthdate);
END;
$$;
CALL add_author('Касымалы Баялинов', 'Кыргызстан', '1902-09-25');
-- 24. Создайте процедуру add_book(title text, publisher_id int, format text, pages int, published_date date), используя которого сможем добавить данные в таблицу books
CREATE PROCEDURE add_book(title text, publisher_id int, format text, pages int, published_date date)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO books (title, publisher_id, format, pages, published_date)
VALUES (title, publisher_id, format, pages, published_date);
END;
$$;
CALL add_book('Ажар', NULL, 'твердый', 18, '1928-01-01');
3. Создайте диаграмму и документацию проекта
Создайте диаграмму нашей базы данных через этот сайт: drawsql
# Проект: Управление библиотечной базой данных (SQL)
В проекте реализованы и в процессе научилась(ся) протестировать следующие SQL-операции:
- **Запросы**
- LIMIT, NOT, LIKE, DISTINCT, CASE
- Агрегатные функции (ROUND, SUM, AVG, MAX, MIN)
- **Реляции**
- Подзапросы
- Primary, Foreign keys
- Все типы реляций (1:1, 1:n, n:n)
- IN, JOIN, GROUP BY, HAVING, ORDER BY
- **Дизайнирование**
- CREATE, DROP
- CHECK, DEFAULT, NOT NULL, UNIQUE
- **Изменение данных**
- INSERT INTO
- UPDATE
- DELETE
- Процедуры
- **VIEWS (представлении)**
- Три типы представлений (Permanent(постоянный), Temporary(временный), CTE(одноразовый))
- Мягкое удаление
- **Оптимизация**
- Индексы
- Транзакция (выполнить несколько запросов одновременно)
4. Загрузите ваш проект на сайт
CREATE TABLE covid (
iso_code TEXT,
continent TEXT,
location TEXT,
date DATE,
total_case NUMERIC,
new_cases NUMERIC,
new_cases_smoothed REAL,
total_deaths NUMERIC,
new_deaths NUMERIC,
new_deaths_smoothed REAL,
total_cases_per_million REAL,
new_cases_per_million REAL,
new_cases_smoothed_per_million REAL,
total_deaths_per_million REAL,
new_deaths_per_million REAL,
new_deaths_smoothed_per_million REAL,
reproduction_rate REAL,
icu_patients NUMERIC,
icu_patients_per_million REAL,
hosp_patients NUMERIC,
hosp_patients_per_million REAL,
weekly_icu_admissions REAL,
weekly_icu_admissions_per_million REAL,
weekly_hosp_admissions REAL,
weekly_hosp_admissions_per_million REAL,
new_tests NUMERIC,
total_tests NUMERIC,
total_tests_per_thousand REAL,
new_tests_per_thousand REAL,
new_tests_smoothed NUMERIC,
new_tests_smoothed_per_thousand REAL,
positive_rate REAL,
tests_per_case REAL,
tests_units TEXT,
total_vaccinations NUMERIC,
people_vaccinated NUMERIC,
people_fully_vaccinated NUMERIC,
new_vaccinations NUMERIC,
new_vaccinations_smoothed NUMERIC,
total_vaccinations_per_hundred REAL,
people_vaccinated_per_hundred REAL,
people_fully_vaccinated_per_hundred REAL,
new_vaccinations_smoothed_per_million NUMERIC,
stringency_index REAL,
population NUMERIC,
population_density REAL,
median_age REAL,
aged_65_older REAL,
aged_70_older REAL,
gdp_per_capita REAL,
extreme_poverty REAL,
cardiovasc_death_rate REAL,
diabetes_prevalence REAL,
female_smokers REAL,
male_smokers REAL,
handwashing_facilities REAL,
hospital_beds_per_thousand REAL,
life_expectancy REAL,
human_development_index REAL
);
Top comments (0)