Sample queries

The names of all the languages in the table ‘language’

SELECT language_name
FROM language ;

All the columns in the table ‘genre’

SELECT *
FROM genre ;

Full names of all the authors whose last names starts with an ‘s’

SELECT last_name, given_name
FROM author
WHERE last_name LIKE 'S%' ;

All the printers based in city with ID 5259 (which is the identifier for Leiden)

SELECT name
FROM printer
WHERE city = 5259 ;

How many books were printed in 1750?

SELECT COUNT(*)
FROM book
WHERE date = 1750 ;

How many printers were based in Breda? The ID of Breda is 716

SELECT COUNT(*)
FROM printer
WHERE city = 716 ;

The number of books published per year

SELECT date, (COUNT(DISTINCT book_id) )
FROM book
WHERE date IS NOT NULL
GROUP BY date

The number of books published per year, ordered chronologically

SELECT date, (COUNT(DISTINCT book_id) )
FROM book
WHERE date IS NOT NULL
GROUP BY date
ORDER BY date

The number of books published per year, ordered chronologically ordered according to the number of titles

SELECT date, (COUNT(DISTINCT book_id) )
FROM book
WHERE date IS NOT NULL
GROUP BY date
ORDER BY (COUNT(DISTINCT book_id) ) DESC

Most prolific publishers overall

SELECT printer_id, printer.name, COUNT( DISTINCT book_id)
FROM publication
LEFT JOIN book
on book = book_id
LEFT JOIN printer
ON printer = printer_id
GROUP BY printer_id, printer.name
ORDER BY COUNT( DISTINCT book_id) DESC ;

Most prolific publishers from Utrecht

SELECT date, COUNT( DISTINCT book_id)
FROM publication
LEFT JOIN book
on book = book_id
LEFT JOIN printer
ON printer = printer_id
LEFT JOIN city
ON city = city_code
WHERE city_name LIKE '%Utrecht%'
AND date is not NULL
GROUP BY date
ORDER BY date ;

Development of the printing industry in Utrecht

All books that have been assigned the genre ‘Drama’

SELECT *
FROM classification_genre
LEFT JOIN genre
ON genre = genre_id
LEFT JOIN book
ON book = book_id
WHERE genre_term LIKE 'Drama'
LIMIT 5000

Books on Africa

SELECT *

FROM classification_subject
LEFT JOIN subject
ON subject = subject_id
LEFT JOIN book
ON book = book_id
WHERE subject_term LIKE '%Africa%'
LIMIT 100

Books written by Shakespeare

SELECT author.last_name, author.given_name, title, printer.name
FROM authorship
LEFT JOIN book
ON book = book_id
LEFT JOIN author
ON author = author_id
LEFT JOIN publication
ON publication.book = book_id
LEFT JOIN printer
ON printer = printer_id
WHERE last_name LIKE 'Shakespeare%'

Children's books

SELECT *
FROM classification_genre
LEFT JOIN genre
ON genre = genre_id
LEFT JOIN book
ON book = book_id
WHERE genre_term LIKE 'Children%s books'

The number of children's books

SELECT COUNT(DISTINCT book_id)
FROM classification_genre
LEFT JOIN genre
ON genre = genre_id
LEFT JOIN book
ON book = book_id
WHERE genre_term LIKE 'Children%s books'

The formats of children's books

SELECT format, COUNT(*)
FROM classification_genre
LEFT JOIN book
ON classification_genre.book = book_id
LEFT JOIN genre
ON classification_genre.genre = genre_id
WHERE genre_term LIKE '%Children%s books%'
GROUP BY format
ORDER BY COUNT(*) ASC

Which printers focused on children’s books?

SELECT printer.name,printer_id,COUNT(*)
FROM classification_genre
LEFT JOIN book
ON classification_genre.book = book_id
LEFT JOIN genre
ON classification_genre.genre = genre_id
LEFT JOIN publication
on publication.book = book_id
LEFT JOIN printer
ON publication.printer = printer_id
WHERE genre_term LIKE '%Children%s books%'
AND printer.name IS NOT NULL
GROUP BY printer.name
ORDER BY Count(*) DESC

How can you view the Children's books published by a specific printer (Johannes Allart)?

SELECT title, format, printer.name, date
FROM classification_genre
LEFT JOIN book
ON classification_genre.book = book_id
LEFT JOIN genre
ON classification_genre.genre = genre_id
LEFT JOIN publication
on publication.book = book_id
LEFT JOIN printer
ON publication.printer = printer_id
WHERE genre_term LIKE '%Children%s books%'
AND printer.printer_id = 'http://data.bibliotheken.nl/id/thes/p075568276'

Which authors wrote children’s books?

SELECT book_id, title, date, author_id, given_name, last_name
FROM authorship
LEFT JOIN book
ON authorship.book = book_id
LEFT JOIN author
ON author = author_id
LEFT JOIN classification_genre
ON classification_genre.book = book_id
LEFT JOIN genre
ON classification_genre.genre = genre_id
WHERE genre_term LIKE 'Children%s books'

How many atlases were printed annually?

SELECT date, COUNT(*)
FROM classification_genre
LEFT JOIN genre
ON genre = genre_id
LEFT JOIN book
ON book = book_id
WHERE genre_term = 'Atlases'
AND date is not NULL
GROUP BY date

Analysis of a specific printer: Bonaventura Elsevier

First, we find the STCN identifier(s) of Bonaventura Elsevier

SELECT *
FROM printer
WHERE name LIKE '%Elzevier%' AND name LIKE '%Bonaventura%'

Which book did this printer publish?

SELECT *
FROM publication
LEFT JOIN book
ON book = book_id
LEFT JOIN printer
ON printer = printer_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )

How did the productivity develop chronologically?

SELECT date, COUNT(*)
FROM publication
LEFT JOIN book
ON book = book_id
LEFT JOIN printer
ON printer = printer_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )
AND date is not NULL
GROUP BY date

In which formats did Bonaventura Elsevier publish books?

SELECT format, COUNT(*)
FROM publication
LEFT JOIN book
ON book = book_id
LEFT JOIN printer
ON printer = printer_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )
GROUP BY format

In which languages did Bonaventura Elsevier publish books?

SELECT language_name, COUNT(*)
FROM publication
LEFT JOIN book
ON publication.book = book_id
LEFT JOIN printer
ON printer = printer_id
LEFT JOIN language_use
ON language_use.book = book_id
LEFT JOIN language
ON language_use.language = lang_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )
GROUP BY language_name

Which subjects did he focus on?

SELECT subject_term, COUNT(*)
FROM publication
LEFT JOIN book
ON publication.book = book_id
LEFT JOIN printer
ON printer = printer_id
LEFT JOIN classification_subject
ON classification_subject.book = book_id
LEFT JOIN subject
ON classification_subject.subject = subject_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )
AND date is not NULL
GROUP BY subject_term

or

Which genres did he focus on?

SELECT genre_term, COUNT(*)
FROM publication
LEFT JOIN book
ON publication.book = book_id
LEFT JOIN printer
ON printer = printer_id
LEFT JOIN classification_genre
ON classification_genre.book = book_id
LEFT JOIN genre
ON classification_genre.genre = genre_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )
AND genre_term is not NULL
GROUP BY genre_term

Which authors did he collaborate with?

SELECT concat( given_name, ' ', last_name ) ,COUNT(*)
FROM publication
LEFT JOIN book
ON publication.book = book_id
LEFT JOIN printer
ON printer = printer_id
LEFT JOIN authorship
ON authorship.book = book_id
LEFT JOIN author
ON authorship.author = author_id
WHERE ( printer_id = 'http://data.bibliotheken.nl/id/thes/p075545349' OR printer_id = 'http://data.bibliotheken.nl/id/thes/p266999905' )
AND concat( given_name, ' ', last_name ) is not NULL
GROUP BY concat( given_name, ' ', last_name )
ORDER BY COUNT(*) DESC