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 between 1995 and 2005. N.B. Under WHERE, You can combine different criteria using "AND"
  4. Authors whose last name starts with the letter "B"
  5. All the genres, in alphabetic order
  6. A list of the years that occur in the table BOOK, without duplicates.
  7. The most recent title in the database.
  8. The number of unique nationalities in the table AUTHOR
  9. The average number of copies sold for all the books
  10. 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.
  11. The title and the year of all the books, together with a full description of the genres (not only the codes).
  12. The title and the name of the publisher of each book.
  13. What are the best-selling books in the genre "Popular Science"?
  14. What is the average number of sales of a book written by J.K. Rowling?
  15. For each year, show the total number of bestsellers
  16. For each genre, show the average number of sales. Display the name of the genre and not just the code.
  17. For each author, show the total number of sales. Display both the first and the last of the authors.
  18. Which authors have written more than one beststeller?
  19. Which publishers are responsible for more than three bestsellers?
  20. For each genre, show the maximum number of books that have been sold. Display the name of the genre and not just the code.

Answers to these questions

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

Since 2000, OECD's Programme for International Student Assessment (PISA) has conducted a series of surveys every three years to assess the performance of 15-year-olds in the fields reading, mathematics and science. The database named ‘performance’, which you can search using the URL below, contains the scores that have been measured for 46 OECD countries. This assignment concentrates on the scores obtained for reading. You can query the database via the following URL:
https://bookandbyte.universiteitleiden.nl/DMT/MySQL/pisa.php

Answer the folloqing questions about this database:

  1. Create a full list of all the countries which have participated in the PISA survey of 2022.
  2. What was the OECD average in 2022?
  3. What were the OECD averages per continent in 2022? [Tip: group the scores by continent and calculate the average scores for each continent.]
  4. For the various surveys that have been conducted over the years, create a list of all the OECD averages. Has there been a general increase or a decline? Show these averages in a bar chart as well.
  5. How have the PISA scores for reading skills developed chronologically for students in the Netherlands?
  6. Make a list of all the reading scores obtained in Europe in 2018. Which European country had the highest scores for reading in 2018? Which country had the lowest score?

Write brief answers to these questions and also include the SQL queries you worked with to collect the relevant information.

Exercise 4

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