Group assignments - SQL

As was the case in the first research seminar, this group assignment concentrates on bibliographic data exported from the Short Title Catalogue of the Netherlands (STCN). The data have been captured in a relational database. For each book, you can find information about authors, the full title, the date of publication, the subjects, the language of the publication.

Using this information, you can try to answer a number of questions about the history of specific publishing companies:

The questions above are only meant get you started; if you want to focus on other questions in this seminar, this is of course encouraged. Of course, you may want to divide tasks among group members.

Information about the structure of this database can be found in the SQL CREATE TABLE statements that have been used during the implementation of this database.

Results displayed in a table




You can use the following query to link the books to their publishers:

SELECT *
FROM BOOK B
LEFT JOIN PUBLISHERBOOK PB
ON PB.BOOK = B.B_ID
LEFT JOIN PUBLISHER P
ON P.PUBL_ID = PB.PUBLISHER
WHERE P.NAME LIKE "%Elzevier%" ;

If you also want to see the subjects, the query above needs to be expanded: links need to be included to the CLASSIFICATION table and the SUBJECT table, as follows:

SELECT *
FROM BOOK B
LEFT JOIN PUBLISHERBOOK PB
ON B.B_ID = PB.BOOK
LEFT JOIN PUBLISHER P
ON P.PUBL_ID = PB.PUBLISHER
LEFT JOIN CLASSIFICATION C
ON B.B_ID = C.BOOK
LEFT JOIN SUBJECT S
ON S.S_ID = C.SUBJECT
WHERE P.NAME LIKE '%Elzevier%' ;

Results displayed in a bar chart

SQL queries can also be queried directly as a bar chart. To do this, create a query in which the select list consists of two value. The values that you want to see on the X-axis must be mentioned first, and the values that you want to display on the Y-axis must be mentioned second. This type of visualisation is probably most useful if you use it for queries which make use of group by. The column which you mention after GROUP BY can be mentioned first, and you can use COUNT(*) as the second value in this query.

For example:

SELECT LANGUAGE , COUNT(*)
FROM BOOK B
LEFT JOIN PUBLISHERBOOK PB
ON B.B_ID = PB.BOOK
LEFT JOIN PUBLISHER P
ON P.PUBL_ID = PB.PUBLISHER
LEFT JOIN CLASSIFICATION C
ON B.B_ID = C.BOOK
LEFT JOIN SUBJECT S
ON S.S_ID = C.SUBJECT
WHERE P.NAME LIKE '%Elzevier, Bonaventura%'
GROUP BY LANGUAGE ;




Results displayed in a line chart

For instructions, see the text above.




The teams are the same as in the first part of the hackathon:

Team 1 Katya Dmitrieva, Lotte Oudejans, Lotte Savelsberg, Kim van Toorn

  Team 2 Linda Boutellier, Natalie Hoage, Cornalijn Meinders, Gerline Sonneveld   Team 3 Marjorie Heijselaar, Emily Klerks, Amber Malik,Sophie Stelzig   Team 4 Anne van Engelen, Oskar Knötschke, Athina Papadopoulou, Helène Pannekoek   Team 5 Mariska Elst, Joery Kragten, Leneth Witte, On Ki Yip   Team 6 Lisette Grimmius, Pleuni van Laarhoven, Bob Schofield   Team 7 Rowy van Dijk, Manuel Llano, Lolita Sahakjan, Rianne Mense