clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228817 fiddles created (16683 in the last week).

select version();
version
PostgreSQL 12.0 (Debian 12.0-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
 hidden batch(es)


CREATE TABLE author ( author_id SERIAL NOT NULL PRIMARY KEY, author_name TEXT NOT NULL, author_name_no SMALLINT NOT NULL, -- to take account of authors with the same -- name - happens a lot, I've done a small -- bit of work in this area -- other author details - institution, nationality, dob, honours... -- maybe separate table? Institutions/Organisations/Consortia? CONSTRAINT author_name_name_no_uq UNIQUE (author_id, author_name_no) ); INSERT INTO author (author_name, author_name_no) VALUES ('Paul', 1), ('Jim', 1), ('Mary', 1), ('Fred', 1); SELECT * FROM author ORDER BY author_id;
4 rows affected
author_id author_name author_name_no
1 Paul 1
2 Jim 1
3 Mary 1
4 Fred 1
 hidden batch(es)


CREATE TABLE subject ( subject_id SERIAL NOT NULL PRIMARY KEY, subject_name TEXT NOT NULL ); INSERT INTO subject (subject_name) -- only 4 for brevity in example VALUES ('Genetics'), ('Maths'), ('Economics'), ('Comp_Sci'); SELECT * FROM subject ORDER BY subject_id;
4 rows affected
subject_id subject_name
1 Genetics
2 Maths
3 Economics
4 Comp_Sci
 hidden batch(es)


CREATE TABLE work ( work_id SERIAL NOT NULL PRIMARY KEY, work_title TEXT NOT NULL, work_type TEXT NOT NULL -- 'journal', 'book', 'newspaper article'... -- This can get very complicated with 'chapter', 'section' -- and lots of other stuff. Maybe other reference table? -- other work details... -- deliberately not putting in DATE for example, this fiddle is complex enough ); INSERT INTO work (work_title, work_type) VALUES ('G_1', 'Book'), ('G_2', 'Journal'), ('M_1', 'Book'), ('M_2', 'Journal'), ('M_3', 'Journal'), ('E_1', 'Book'), ('CS_1', 'Book'), ('CS_2', 'Book'), ('CS_3', 'Journal'), ('G_CS_1', 'Book'), ('G_CS_2', 'Journal'), ('G_CS_3', 'Journal'), ('G_M_1', 'Journal'), ('G_M_2', 'Journal'), ('M_E_1', 'Book'), ('M_E_2', 'Book'), ('M_E_3', 'Journal'), ('E_CS_1', 'Journal'), ('E_CS_2', 'Book'), ('E_CS_3', 'Journal'), ('G_M_CS_1', 'Book'); SELECT * FROM work ORDER BY work_id;
21 rows affected
work_id work_title work_type
1 G_1 Book
2 G_2 Journal
3 M_1 Book
4 M_2 Journal
5 M_3 Journal
6 E_1 Book
7 CS_1 Book
8 CS_2 Book
9 CS_3 Journal
10 G_CS_1 Book
11 G_CS_2 Journal
12 G_CS_3 Journal
13 G_M_1 Journal
14 G_M_2 Journal
15 M_E_1 Book
16 M_E_2 Book
17 M_E_3 Journal
18 E_CS_1 Journal
19 E_CS_2 Book
20 E_CS_3 Journal
21 G_M_CS_1 Book
 hidden batch(es)


CREATE TABLE work_subject -- obivously a work can have many areas, note that all -- have 1 or 2 except work 21 which has 3 ( work_id INTEGER NOT NULL, subject_id INTEGER NOT NULL, CONSTRAINT ws_pk PRIMARY KEY (work_id, subject_id), CONSTRAINT ws_work_id_fk FOREIGN KEY (work_id) REFERENCES work (work_id), CONSTRAINT ws_subject_id_fk FOREIGN KEY (subject_id) REFERENCES subject (subject_id) ); INSERT INTO work_subject VALUES (1, 1), (2, 1), (3, 2), (4, 2), (5, 2), (6, 3), (7, 4), (8, 4), (9, 4), (10, 1), (10, 4), (11, 1), (11, 4), (12, 1), (12, 2), (13, 1), (13, 2), (14, 1), (14, 2), (15, 2), (15, 3), (16, 2), (16, 3), (17, 2), (17, 3), (18, 3), (18, 4), (19, 3), (19, 4), (20, 3), (20, 4), (21, 1), (21, 2), (21, 4); SELECT * FROM work_subject;
34 rows affected
work_id subject_id
1 1
2 1
3 2
4 2
5 2
6 3
7 4
8 4
9 4
10 1
10 4
11 1
11 4
12 1
12 2
13 1
13 2
14 1
14 2
15 2
15 3
16 2
16 3
17 2
17 3
18 3
18 4
19 3
19 4
20 3
20 4
21 1
21 2
21 4
 hidden batch(es)


CREATE TABLE work_author -- obviously a work can have many authors ( work_id INTEGER NOT NULL, author_id INTEGER NOT NULL, CONSTRAINT wa_pk PRIMARY KEY (work_id, author_id), CONSTRAINT wa_work_id_fk FOREIGN KEY (work_id) REFERENCES work (work_id), CONSTRAINT wa_author_id_fk FOREIGN KEY (author_id) REFERENCES author (author_id) ); INSERT INTO work_author VALUES (1, 1), (2, 1), (2, 3), (3, 3), (4, 3), (4, 4), (5, 3), (5, 4), (6, 4), (7, 2), (8, 2), (9, 1), (9, 2), (10, 1), (11, 1), (11, 2), (12, 1), (12, 2), (13, 3), (14, 1), (14, 3), (15, 2), (15, 4), (16, 2), (17, 4), (18, 2), (18, 4), (19, 4), (20, 2), (21, 1), (21, 2), (21, 3); SELECT * FROM work_author;
32 rows affected
work_id author_id
1 1
2 1
2 3
3 3
4 3
4 4
5 3
5 4
6 4
7 2
8 2
9 1
9 2
10 1
11 1
11 2
12 1
12 2
13 3
14 1
14 3
15 2
15 4
16 2
17 4
18 2
18 4
19 4
20 2
21 1
21 2
21 3
 hidden batch(es)


SELECT COUNT(*) FROM work_subject ws WHERE ws.subject_id = 1;
count
8
 hidden batch(es)


SELECT COUNT(*) FROM work_subject ws WHERE ws.subject_id = ( SELECT s.subject_id FROM subject s WHERE subject_name = 'Genetics' );
count
8
 hidden batch(es)


-- What are the names of authors and their works which are in -- either Genetics or Computer Science? SELECT DISTINCT author_id, waw, author_name, work_title, work_type FROM ( SELECT wa.work_id AS waw, wa.author_id, ws.work_id AS wsw, ws.subject_id, a.author_name, w.work_title, w.work_type FROM work_author wa JOIN work_subject ws ON wa.work_id = ws.work_id JOIN author a ON wa.author_id = a.author_id JOIN work w ON ws.work_id = w.work_id WHERE ws.subject_id = 1 OR ws.subject_id = 4 ORDER BY wa.work_id, wa.author_id ) AS tab_1 ORDER BY waw, author_id, work_title;
author_id waw author_name work_title work_type
1 1 Paul G_1 Book
1 2 Paul G_2 Journal
3 2 Mary G_2 Journal
2 7 Jim CS_1 Book
2 8 Jim CS_2 Book
1 9 Paul CS_3 Journal
2 9 Jim CS_3 Journal
1 10 Paul G_CS_1 Book
1 11 Paul G_CS_2 Journal
2 11 Jim G_CS_2 Journal
1 12 Paul G_CS_3 Journal
2 12 Jim G_CS_3 Journal
3 13 Mary G_M_1 Journal
1 14 Paul G_M_2 Journal
3 14 Mary G_M_2 Journal
2 18 Jim E_CS_1 Journal
4 18 Fred E_CS_1 Journal
4 19 Fred E_CS_2 Book
2 20 Jim E_CS_3 Journal
1 21 Paul G_M_CS_1 Book
2 21 Jim G_M_CS_1 Book
3 21 Mary G_M_CS_1 Book
 hidden batch(es)


SELECT STRING_AGG(DISTINCT a.author_name, ',') AS names, w.work_title AS title FROM work_author wa JOIN work_subject ws ON wa.work_id = ws.work_id JOIN author a ON wa.author_id = a.author_id JOIN work w ON wa.work_id = w.work_id WHERE wa.work_id IN ( SELECT wa.work_id FROM work_author wa GROUP BY wa.work_id HAVING COUNT(wa.work_id) > 2 ) GROUP BY title;
names title
Jim,Mary,Paul G_M_CS_1
 hidden batch(es)