CREATE TABLE public.authors (
"Id" int4 NOT NULL,
"name" varchar(100) NULL,
age int4 NULL,
birthday date NULL,
city varchar(20) NULL,
CONSTRAINT authors_pkey PRIMARY KEY ("Id")
);
CREATE TABLE public.books (
"Id" int4 NOT NULL,
authorid int4 NOT NULL,
"name" varchar NOT NULL,
"year" int4 NOT NULL,
CONSTRAINT books_pkey PRIMARY KEY ("Id")
);
ALTER TABLE public.books ADD CONSTRAINT fk_books_authors FOREIGN KEY (authorid) REFERENCES public.authors("Id");
CREATE TABLE public.shops (
"Id" int4 NOT NULL,
"name" varchar NOT NULL,
square numeric NULL,
CONSTRAINT shops_pkey PRIMARY KEY ("Id")
);
CREATE TABLE public.book_shop (
book_id int4 NOT NULL,
shop_id int4 NOT NULL,
CONSTRAINT book_shop_pkey PRIMARY KEY (book_id, shop_id)
);
ALTER TABLE public.book_shop ADD CONSTRAINT book_shop_book_id_fkey FOREIGN KEY (book_id) REFERENCES public.books("Id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.book_shop ADD CONSTRAINT book_shop_shop_id_fkey FOREIGN KEY (shop_id) REFERENCES public.shops("Id") ON DELETE CASCADE ON UPDATE CASCADE;
INSERT INTO public.authors ("Id","name",age,birthday,city) VALUES
(3,'ΠΠ»Π΅ΠΊΡΠ°Π½Π΄Ρ Π‘Π΅ΡΠ³Π΅Π΅Π²ΠΈΡ ΠΡΡΠΊΠΈΠ½',NULL,'1799-05-26','ΠΠΎΡΠΊΠ²Π°'),
(4,'ΠΠ΅Π² ΠΠΈΠΊΠΎΠ»Π°Π΅Π²ΠΈΡ Π’ΠΎΠ»ΡΡΠΎΠΉ',NULL,'1828-08-28','Π―ΡΠ½Π°Ρ ΠΠΎΠ»ΡΠ½Π°'),
(2,'ΠΠ²Π°Π½ΠΎΠ² Π‘Π΅ΡΠ³Π΅ΠΉ ΠΠ΅ΡΡΠΎΠ²ΠΈΡ',41,'1979-05-26','Π£ΡΠ°'),
(1,'ΠΠ΅ΡΡΠΎΠ² ΠΠ°ΡΠΈΠ»ΠΈΠΉ ΠΠΈΡ
Π°ΠΉΠ»ΠΎΠ²ΠΈΡ',31,'1985-05-26','ΠΠΎΡΠΊΠ°');
INSERT INTO public.books ("Id",authorid,"name","year") VALUES
(1,3,'Π‘ΠΊΠ°Π·ΠΊΠ° ΠΎ ΡΠ°ΡΠ΅ Π‘Π°Π»ΡΠ°Π½Π΅',2017),
(2,3,'ΠΠ²Π³Π΅Π½ΠΈΠΉ ΠΠ½Π΅Π³ΠΈΠ½',2018),
(3,3,'Π‘ΠΊΠ°Π·ΠΊΠ° ΠΎ ΠΌΡΡΡΠ²ΠΎΠΉ ΡΠ°ΡΠ΅Π²Π½Π΅ ΠΈ ΠΎ ΡΠ΅ΠΌΠΈ Π±ΠΎΠ³Π°ΡΡΡΡΡ
',2016),
(4,3,'Π‘ΠΊΠ°Π·ΠΊΠ° ΠΎ ΡΡΠ±Π°ΠΊΠ΅ ΠΈ ΡΡΠ±ΠΊΠ΅',2017),
(5,3,'ΠΠ°ΠΏΠΈΡΠ°Π½ΡΠΊΠ°Ρ Π΄ΠΎΡΠΊΠ°. ΠΠΎΠ²Π΅ΡΡΠΈ (ΡΠ±ΠΎΡΠ½ΠΈΠΊ)',2021),
(6,4,'ΠΠ½Π½Π° ΠΠ°ΡΠ΅Π½ΠΈΠ½Π°',2021),
(7,4,'ΠΠΎΠΉΠ½Π° ΠΈ ΠΌΠΈΡ. Π’ΠΎΠΌ 1-2',2020),
(8,4,'ΠΠΎΡΠΊΡΠ΅ΡΠ΅Π½ΠΈΠ΅',2017),
(9,1,'Π‘ΠΊΠ°Π·ΠΊΠΈ',2022),
(10,2,'Π‘ΡΠΈΡ
ΠΈ ΠΈ ΠΏΡΠΎΠ·Π°',2021);
INSERT INTO public.shops ("Id","name",square) VALUES
(1,'Π¦Π£Π',100),
(2,'ΠΠΈΠ²ΠΎΠ΅ ΡΠ»ΠΎΠ²ΠΎ',50),
(3,'Π§ΠΈΡΠ°ΠΉ Π³ΠΎΡΠΎΠ΄',74),
(4,'ΠΠ½ΠΈΠ³ΠΈ',20);
INSERT INTO public.book_shop (book_id,shop_id) VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(2,3),
(2,1),
(3,4),
(4,1),
(4,2),
(4,3);
INSERT INTO public.book_shop (book_id,shop_id) VALUES
(5,2),
(5,3),
(6,1),
(6,2),
(6,3),
(6,4),
(7,1),
(7,3),
(8,4),
(9,1);
INSERT INTO public.book_shop (book_id,shop_id) VALUES
(9,2);
SELECT *
FROM public.shops
ALTER TABLE public.shops RENAME COLUMN "name" TO shop_name;
ALTER TABLE public.book_shop ADD COLUMN sale_count int4 DEFAULT NULL;
UPDATE public.book_shop
SET sale_count = 10
WHERE book_id = 2;
SELECT *
FROM public.shops
Telegram: DB developers channel