Database Exercises
SERIE 1: Project database

In this exercise, you will need the Project-database, which is described in Here . You can initialize the Project database like this:

  • Download the file project.sql
  • Execute the code from the file in your SQL-client

SELECT and WHERE / Project database
    Make Query which,...
  1. Shows all data from all fields in person-table.
  2. Shows firstnames and lastnames in person table in alphabetical order based to lastname.
  3. Shows all cities from person-table, but same code city be mentioned only once.
  4. Shows all data from person-table if the firstname is Jim.
  5. Shows all data from person-table if the city is Turku and birthyear is bigger than 1980.
  6. Shows all data from project-table if the place is NULL
  7. Shows all data from project-table if the place is NOT NULL
  8. Shows firstname, lastname and age of each person. Note you have to calculate the age based on birthyear and current year.
SERIE 2: Library database

In this exercise, you will need the Library-database, which is described in Here . You can initialize the Library database like this:

  • Download the file library.sql
  • Execute the code from the file in your SQL-client

SELECT and WHERE / Library database
    Make Query which,...
  1. Shows all data from all fields in borrower-table.
  2. Shows firstnames and lastnames in borrower table in alphabetical order based to lastname.
  3. Shows all postalcodes, but same code should be mentioned only once.
  4. Shows first 5 rows in borrower-table (use Google to find the answer).
  5. Shows rows 6-10 in borrower table.
  6. Shows all fields from borrower if id_borrower is 9.
  7. Shows all fields from borrower if id_borrower is 9 or 11.
  8. Shows all fields from borrower if id_borrower is 9 or 11 or 13.
  9. Shows all fields from borrower if id_borrower is 5-10.
  10. Shows all fields from borrower if id_borrower is not 5-10.
  11. Shows all fields from borrower if id_borrower is 1-12 and postalcode is 90101.
  12. Shows borrower's lastname and firstname if firstname is Carla.
  13. Shows borrower's lastname and firstname if firstname is Carla or Ralph.
  14. Shows borrower's lastname and firstname if streetaddress is Uusikatu 4 or Isokatu 66.
  15. Shows borrower's lastname and firstname if the person doesn't have a phone.
  16. Shows borrower's lastname and firstname and phone numbers if the person has phone.
  17. MySQL has several functions which will return current date. Use Google to find out them. You can test them by writing Query: "SELECT function-name;"
  18. Shows all fields from Borrow-table if the book is late.
LIKE / Library database
    Make Query which,...
  1. Shows all fields from book if the book name starts with 'A'
  2. Shows all fields from book if the book name includes word Management
  3. Shows all fields from book if the book name includes letter e
  4. Shows all fields from book if the book name includes letter e and a
  5. Shows firstnames and lastnames of borrower's who lives on Isokatu
  6. Shows firstnames and lastnames and phonenumber of borrower, which phonenumber starts with 040



Toggle Menu

Copyright © Pekka Alaluukas / 2024
email