A sample database with a set of simple query tasks to complete intended to help learn SQL from scratch. Includes database initialisation file (set_up_datbase.sql) and a set of example queries (example_queries.sql). Database is designed to mimic the typical form of data that would be encountered in the financial department of a business.
Run the initialisation file set_up_database.sql. If you need help, there is a useful guide to setting up SQL avaliable here1. This will build and populate a database of people and their personal details including name, D.O.B, bank etc. Don't worry about the code in this file too much, but you can read it if you feel like it.
Next, look through the example queries in example_queries.sql to see some examples and how they may be joined together.
The following questions may be answered by writing queries to the database. Try to answer them without looking at the set up file.
- What is the average balance in each portfolio?
- What is the average balance by ProductType?
- What is the average balance today (hint: discount previous collections from purchase value) of all customers whose surname begins with a G?
- Which customer has the highest number of accounts?
Without looking at the set up file, answer the following questions:
- Which portfolio are people most likely to pay in?
- Which product type are people most likely to pay in?
- What is the pay rate over the last 12 months? That is, the number of customers who have paid in the last 12 months divided by the number of customers who had not cleared their balance in the last 12 months.
Again without looking at the initialisation file, can you build a model to determine the expected collections of an AccountId based on the information in the database.
Thanks to Paul Russell for providing the initialisation code, questions and general advice.
1 Written for Windows machines but contains links to guides for other operating systems.