· 5 years ago · Oct 13, 2020, 06:40 PM
1--Table for book:
2drop table if exists public.book;
3
4CREATE TABLE public.book
5(
6 book_id integer NOT NULL,
7 author character varying(50) COLLATE pg_catalog."default" NOT NULL,
8 year integer NOT NULL,
9 price numeric(6,2) NOT NULL,
10 title character varying COLLATE pg_catalog."default" NOT NULL,
11 editor character varying COLLATE pg_catalog."default" NOT NULL,
12 CONSTRAINT "Book_pkey" PRIMARY KEY (book_id)
13)
14 WITH (
15 OIDS = FALSE
16 )
17 TABLESPACE pg_default;
18
19ALTER TABLE public.book
20 OWNER to postgres;
21
22-- Adding some examples
23insert into public.book (book_id, author, year, price, title, editor) values(13322, 'J.K Rowlink', 1999, 12.99, 'The white spirit', 'Jean C.');
24insert into public.book (book_id, author, year, price, title, editor) values(43232, 'J.K Rowlink', 2001, 11.99, 'The Black spirit', 'Jean C.');
25insert into public.book (book_id, author, year, price, title, editor) values(43324, 'Rollingstone', 1990, 9.99, 'There was a day', 'Pierre Cassis');
26insert into public.book (book_id, author, year, price, title, editor) values(55325, 'Davinci', 1879, 19.99, 'my first artt', 'Oldtimers.inc');
27insert into public.book (book_id, author, year, price, title, editor) values(32223, 'Google', 2019, 2.99, 'There was a time', 'trouxdbal');
28insert into public.book (book_id, author, year, price, title, editor) values(44334, 'Alex lecrivain', 2020, 102.99, 'Mon temps est venu', 'Martens inc.');
29insert into public.book (book_id, author, year, price, title, editor) values(55825, 'Jeje on the top', 2019, 19.99, 'sql is iwipizi', 'UMdistr.');
30insert into public.book (book_id, author, year, price, title, editor) values(63452, 'Luigi a.', 1989, 12.99, 'Mario and I, a long love story', 'Antolini inc');
31insert into public.book (book_id, author, year, price, title, editor) values(76543, 'Yeyetima', 2007, 12.99, ' The absoluut crise', 'Jean Michel Dupre');
32insert into public.book (book_id, author, year, price, title, editor) values(65433, 'VivaLalgerie', 1989, 0.99, 'lalgerie a bruxelles, une nouvelle destination ', 'Jean C.');
33
34--Table for section:
35drop table if exists public.section;
36
37CREATE TABLE public.section
38(
39 name character varying COLLATE pg_catalog."default" NOT NULL,
40 number integer NOT NULL,
41 number_books integer NOT NULL,
42 genre character varying[] COLLATE pg_catalog."default",
43 CONSTRAINT section_pkey PRIMARY KEY (name, "number")
44)
45 WITH (
46 OIDS = FALSE
47 )
48 TABLESPACE pg_default;
49
50ALTER TABLE public.section
51 OWNER to postgres;
52
53COMMENT ON TABLE public.section
54 IS 'Documentation: https://stackoverflow.com/questions/7925050/is-there-a-multivalued-field-type-available-in-postgresql';
55
56insert into public.section (name, number, number_books, genre) values('Section 1', 1, 332, array['Fantasy']);
57insert into public.section (name, number, number_books, genre) values('Section 2', 2, 43, array['Comedy']);
58insert into public.section (name, number, number_books, genre) values('Section 3', 3, 643, array['Music']);
59insert into public.section (name, number, number_books, genre) values('Section 4', 4, 53, array['Films']);
60insert into public.section (name, number, number_books, genre) values('Section 5', 5, 5436, array['Science']);
61insert into public.section (name, number, number_books, genre) values('Section 6', 6, 643, array['History']);
62insert into public.section (name, number, number_books, genre) values('Section 7', 7, 54, array['Tragedy']);
63insert into public.section (name, number, number_books, genre) values('Section 8', 8, 754, array['Action']);
64insert into public.section (name, number, number_books, genre) values('Section 9', 9, 865, array['Thriller']);
65insert into public.section (name, number, number_books, genre) values('Section 10',10, 76, array['Roman']);
66
67--Table for recommendation:
68drop table if exists recommendation;
69
70create table recommendation (
71 Title character varying,
72 Genre character varying,
73 Author character varying,
74 How_it_relates character varying
75);
76
77INSERT INTO recommendation VALUES ('Dragon Prince', 'fantasy', 'Melanie Rawn', 'same genre');
78INSERT INTO recommendation VALUES ('The Bet', 'biography', 'Rachel Van Dyken', 'same length');
79INSERT INTO recommendation VALUES ('Diary of a Worm', 'thriller', 'Harry Bliss', 'same author');
80INSERT INTO recommendation VALUES ('SilverFin', 'thriller', 'Charlie Higson', 'same length');
81INSERT INTO recommendation VALUES ('Rainbow Valley', 'fantasy', 'Joe Montgomery', 'same genre');
82INSERT INTO recommendation VALUES ('Hold Still', 'fiction', ' Nina LaCour', 'same editor');
83INSERT INTO recommendation VALUES ('Blue Bloods', 'fantasy', 'Melissa de la Cruz', 'same genre');
84INSERT INTO recommendation VALUES ('The Colorado Kid', 'drama', 'Stephen King', 'same author');
85INSERT INTO recommendation VALUES ('The Rising', 'fantasy', 'Ron Powers', 'same length');
86INSERT INTO recommendation VALUES ('The Firm', 'fiction', ' John Grisham', 'same editor');
87