Universiteit Leiden

SQL Exercises


Exercise 1

The database that was created for this exercise is based on a list of UK's 100 best-selling books, which was published by the Guardian. The ERD that was created during the design of this database can be found below. This database has been implemented in MySQL. It can be queried using SQL using the text field on this page.

Write queries that can return the following information:

  1. The last name and the first name of all the persons in the table AUTHOR
  2. All the columns in the table COUNTRY. Sort the rows alphabetically, by name of the country.
  3. All books published in or before the year 2000.
  4. Authors whose last name starts with the letter "B"
  5. All the genres, in alphabetic order
  6. A list of the codes that have been created for the publishers in the table BOOK, without dublicates.
  7. The year of the oldest title in the database.
  8. The number of unique countries in the table AUTHOR
  9. The average number of copies sold for all the books
  10. For each genre, show the average number of sales
  11. For each genre, show the maximum number of books that have been sold
  12. For each author, show the total number of sales
  13. For each year, show the total number of bestsellers
  14. All the countries which have produced more than two best-selling authors
  15. The first name and the last name of all the authors, together with the full name of the countries in which these authors were born.
  16. The title and the year of all the books, together with a full description of the genres (not only the codes).
  17. Which authors have written more than one beststeller?
  18. Which publishers are responsible for more than three bestsellers?
  19. What are the best-selling books in the genre "Popular Science"?
  20. What is the average number of sales of a book written by J.K. Rowling?

Exercise 2

The results of SQL queries can also be visualised.

The web page that you have worked with for exercise 1 also contains a second text field, below the heading 'Bar chart'. When you enter SQL queries in this second text field, the result set will be visualised as a bar chart. The SELECT list of this query needs to mention two sets of values:
(1) The values in the column that you mention firstly will be shown on the X-Axis and
(2) The set of values that you mention secondly will be shown on the Y-axis.
The lengths of the bars will be determined by the values that you mention secondly.

Create bar charts which can display the following information:

  1. For each genre, show the average number of sales
  2. For each genre, show the maximum number of books that have been sold
  3. For each author, show the total number of sales
  4. For each year, show the total number of bestsellers

Exercise 3

For assignment 1 of DMT, you have transcribed and encoded a number of letters. Using the data that you have added via the TEI markup, a database has been created capturing information about all your letters. The SQL INSERT statements have been generated semi-automatically using XSLT.

You can search this database via this webpage. This webpage also contains an ERD of this database

Can you check whether the information about your letters have been entered correctly? Please survey the following aspects:

  • The names of the sender and the recipient
  • The date
  • The location of the sender and the location of the recipient

The database also contains a table named 'MENTIONS_PERSON', which stores references to specific names in the body of the letter. Have all the names that are being referred to in your letter(s) been captured appropriately?

To examine these features of the database, you can use the following queries:

SELECT LETTER_ID , DATE , S.NAME AS 'Sender' , R.NAME AS 'Recipient'
FROM LETTER
LEFT JOIN CORRESPONDENT S
ON SENDER = S.CODE
LEFT JOIN CORRESPONDENT R
ON RECIPIENT = R.CODE

SELECT LETTER_ID , DATE , NAME AS 'Mentions'
FROM MENTIONS_PERSON
LEFT JOIN LETTER
ON LETTER = LETTER_ID
LEFT JOIN CORRESPONDENT
ON PERSON = CODE

Exercise 4

For this exercise, you need to work with a database named "TREASURE". It contains data about some of the notable objects that can be found in national libraries in Europe. You can find the ERD of this database below.

Write queries that return the following information. Your queries can be tested in MySQL.

  1. The last name, the first name and the year of birth of all the persons in the table CREATOR. Sort the rows by year of birth. The most recent year should appear first.
  2. All the columns in the table CITY, and sort the rows alphabetically, by name of the city.
  3. The treasures that were produced after 1800. Display only the title and the year
  4. The creators whose first name starts with the letter "W"
  5. The distinct subject codes in the table TREASURE, and sort the result alphabetically.
  6. The year of the oldest treasure in the database.
  7. The number of countries in the table CREATOR.
  8. The most recent year of death in the table CREATOR.
  9. The oldest treasure from each library. For this query, you only need to work with the identifiers of the libraries and not with their full names.
  10. The number of persons for each country in the table CREATOR.
  11. Those libraries that are represented by two or more treasures.
  12. Those subjects that have been assigned to two or more treasures in the database.
  13. The title, year, and provide a full description of the subject code of all the treasures in the database.
  14. The names of all the libraries and the names of the cities in which they are located.
  15. A list with all names of all cities and the full names of the countries in which they are situated.
  16. The title, year, and a full description of the subject code of all treasures, and the full names of their creators. Sort the list alphabetically by the last name of the creator.