HnfNUmvq

1DROP SEQUENCE IF EXISTS s_av_books CASCADE;
2DROP SEQUENCE IF EXISTS s_genres CASCADE;
3DROP SEQUENCE IF EXISTS s_authors CASCADE;
4DROP SEQUENCE IF EXISTS s_books CASCADE;
5DROP SEQUENCE IF EXISTS s_orders CASCADE;
6DROP SEQUENCE IF EXISTS s_shops CASCADE;
7
8DROP TABLE IF EXISTS av_books CASCADE;
9DROP TABLE IF EXISTS genres CASCADE;
10DROP TABLE IF EXISTS authors CASCADE;
11DROP TABLE IF EXISTS books CASCADE;
12DROP TABLE IF EXISTS orders CASCADE;
13DROP TABLE IF EXISTS shops CASCADE;
14
15drop view restricted_access;
16
17
18CREATE SEQUENCE s_shops;
19CREATE TABLE shops
20(
21  id      int primary key default nextval('s_shops'),
22  name    varchar(20) not null,
23  address varchar(100),
24  phone   varchar(15)
25);
26
27CREATE SEQUENCE s_genres;
28CREATE TABLE genres
29(
30  id    int primary key default nextval('s_genres'),
31  genre varchar(50) not null
32);
33
34CREATE SEQUENCE s_authors;
35CREATE TABLE authors
36(
37  id         int primary key default nextval('s_authors'),
38  name       varchar(100) not null,
39  popularity int
40);
41
42CREATE SEQUENCE s_books;
43CREATE TABLE books
44(
45  id         int primary key default nextval('s_books'),
46  title      varchar(100) not null,
47  author_id  int          not null,
48  genre_id   int          not null,
49  year       int,
50  annotation varchar(500),
51  foreign key (author_id) references authors (id),
52  foreign key (genre_id) references genres (id)
53);
54
55CREATE SEQUENCE s_orders;
56CREATE TABLE orders
57(
58  id               int primary key default nextval('s_orders'),
59  book_id          int not null,
60  shop_id          int not null,
61  approximate_date date,
62  foreign key (book_id) references books (id),
63  foreign key (shop_id) references shops (id)
64);
65
66CREATE SEQUENCE s_av_books;
67CREATE TABLE av_books
68(
69  id      int primary key default nextval('s_av_books'),
70  book_id int not null,
71  shop_id int not null,
72  booked  boolean,
73  foreign key (book_id) references books (id),
74  foreign key (shop_id) references shops (id)
75);
76
77
78
79INSERT INTO shops
80VALUES (default, 'Дом книг', 'ул. Пантелеймоновская, 22', '+380111111111');
81INSERT INTO shops
82VALUES (default, 'Мир книг', 'ул. Королёва, 12', '+380222222222');
83
84INSERT INTO genres
85VALUES (default, 'Ужасы');
86INSERT INTO genres
87VALUES (default, 'Фэнтези');
88INSERT INTO genres
89VALUES (default, 'Автобиография');
90
91INSERT INTO authors
92VALUES (default, 'Стивен Кинг', 5);
93INSERT INTO authors
94VALUES (default, 'Джоан Роулинг', 7);
95INSERT INTO authors
96VALUES (default, 'Адольф Гитлер', 1);
97
98INSERT INTO books
99VALUES (default, 'Майн Кампф', 3, 3, 2003);
100INSERT INTO books
101VALUES (default, 'Зелёная миля', 1, 1, 2003);
102INSERT INTO books
103VALUES (default, 'Гарри Поттер и кубок огня', 2, 2, 2014);
104INSERT INTO books
105VALUES (default, 'Гарри Поттер и кубок огня', 2, 2, 2018);
106
107INSERT INTO orders
108VALUES (default, 2, 1, now());
109
110INSERT INTO av_books
111VALUES (default, 3, 2, FALSE);
112
113INSERT INTO av_books
114VALUES (default, 3, 2, FALSE);
115
116---------------------------------
117--         Это 3я лаба         --
118---------------------------------
119-- 1 Подсчёт доступных книг за 2018 год:
120select count(*)
121from av_books
122where book_id in (select id from books where year = 2018);
123
124-- 2 Выбрать авторов, отсортировав по популярности:
125select name, popularity
126from authors
127ORDER BY popularity DESC;
128
129-- 3 Выбрать все книги, сгруппировав по автору, году,
130-- отсортировав по названию:
131select a.name, b.title, b.year
132from authors a,
133     books b
134where b.author_id = a.id
135group by a.name, b.year, b.title;
136
137-- 4 Подсчёт книг по году издания:
138select books.year as year,
139       count(*)   as count_all
140from books
141group by year order by year;
142
143-- 5 Представление, содержащее список заказов,
144-- которые должны поступить сегодня
145create view will_arrive_today as
146  select o.id, b.title, a.name, b.year, s.name as shop, s.address from orders o, books b, authors a, shops s
147where o.book_id = b.id and o.shop_id = s.id and b.author_id = a.id and o.approximate_date = now()::date;
148
149select * from will_arrive_today;
150
151-- 6 Ужасы, которые выходили с 2001 по 2010 год:
152select * from books where genre_id = (select id from genres where genre = 'Ужасы') and year between 2001 and 2010;
153
154-- 7 Книги, с пустыми аннотациями:
155select  * from books where annotation isnull;
156
157-- 8 Средняя популярность авторов, пишущих Фэнтези:
158select avg(popularity) from authors where id in (select author_id from books where genre_id = (select id from genres where genre = 'Фэнтези'));
159
160-- 9 Премия "Золотая Малина" (топ 3 наименее непопулярных автора:
161select * from authors order by popularity asc limit 3;
162
163-- 10 Авторы с рейтингом выше среднего:
164select * from authors where popularity > (select avg(popularity) from authors);
165
166---------------------------------
167--         Это 4я лаба         --
168---------------------------------
169
170
171---------------------------------
172--         Это 5я лаба         --
173---------------------------------
174
175---------------------------------
176--         Это 6я лаба         --
177---------------------------------
178-- 1 Задание
179create user oleg;
180create user ivan;
181create user petr;
182
183-- 2 Задание
184grant update(name, phone) on shops to oleg;
185
186-- 3 Задание
187grant select on books to public;
188
189-- 4 Задание
190grant insert, update on orders to ivan with grant option;
191
192-- 5 Задание
193grant all on all tables in schema public to petr;
194
195-- 6 Задание
196revoke insert on authors from petr;
197
198-- 7 Задание
199create view restricted_access as select * from orders where approximate_date = now()::date;
200grant all on restricted_access to oleg;
201
202-- 8 Задание
203grant all on all tables in schema public to ivan;