SQL University 
Welcome to SQL University!
This website gives you access to a SQLite database that you can use for testing your SQL skills.
We have made this website to make it easy for any SQL learner to practice working with databases and explore the basics of SQL.
Comments/Questions: contact us!
How to use
You need to download two things:
- Our SQLite database, and
- SQLiteStudio, or any other SQLite IDE.
You will then be able to use our database to practice SQL. We have prepared a series of concrete challenges meant to mimic real-life scenarios. Pretend like you are a business intelligence analyst working for an insurance company, and your role is to provide end users with answers to their questions.
Some of the challenges are for you only (e.g., "getting to know the environment"). They are meant to show you what type of knowledge you need to gather about the databases you will be working with. You can also take a look at the database's entity-relationship diagram, which provides a bird's-eye view of the database structure.
Some of the challenges sound very reasonable at first, but you will soon find out how messy they can be! Will you solve them all?
Download
SQLite database
Click the following link to download the database: sqluniversity.db. Make sure to save the file somewhere where you will be able to retrieve it later. We recommend saving it in "My Documents" for Windows users.
SQLiteStudio
Please find a list of available packages for SQLiteStudio on this page: download SQLiteStudio releases.
We recommend getting the portable version for your current system. For most Windows users, we recommend downloading the .zip file under Windows x64 / Portable. Portable versions can be installed on any machine (even work laptops without admin rights).
SQLiteStudio is a free, open-source, cross-platform IDE for SQLite databases. This should be all you need to install to query our database.
Installation instructions
- Download our SQLite database (see "SQLite database" above).
- Download a portable version of SQLiteStudio (see "SQLiteStudio" above).
- Unzip the SQLiteStudio .zip file somewhere on your computer. For Windows users, we recommend using "My Documents".
- Execute SQLiteStudio. Windows users can navigate to the unzipped folder from the previous step and double-click on "SQLiteStudio.exe"
- Find the "Add a database" button in the toolbar and click on it. In the "File" field, find the "sqluniversity.db" file from the first step. Click "OK".
- In the "Databases" pane, right-click on your database and select "Connect to the database".
- To start writing SQL, find the "Open SQL editor" button in the toolbar and click on it.
- Type some SQL code, and hit the "play" button from the toolbar to execute it.
Entity-relationship diagram (ERD)
An entity-relationship diagram shows the tables that exist in a database and the links (relationships) that exist between them. It also shows the list of fields from each table, along with their corresponding data types.
Click here to show the entity-relationship diagram.
Challenges
Series A: Getting to know the environment
| Code | Question | Answer |
|---|---|---|
| A001 | What is the table with the fewest number of rows? | coverage_group |
| A002 | What is the table with the most number of rows? | claim |
| A003 | What is the earliest policy effective date? | 2015-01-01 |
| A004 | What is the latest car year? | 2015 |
| A005 | How many different provinces are there? | 3 |
| A006 | How many coverage descriptions belong to the largest coverage group? | 3 |
| A007 | What province has the most insured cars? | ON |
| A008 | What is the sum of all coverage premiums? | $3,874,540.00 |
| A009 | What is the sum of all reported losses? | $2,599,054.28 |
| A010 | What are the tables with the most foreign keys? | coverage and vehicle |
Series B: Simple SELECT
| Code | Question | Answer |
|---|---|---|
| B001 | What is the expiry date for policy number 6E4M759P0? | 2024-02-01 |
| B002 | What is the VIN for the vehicle insured by policy number 9W8X086W2? | NM4ML04M9KOEI47X3 |
| B003 | What is the VIN that has "DQ" at the 11th character position? | WX3SZ38E0BDQW23T2 |
| B004 | What is the claim number that was reported for VIN FN7DL28E5RTXB23A0? | YLLV7736089 |
Series C: More SELECT
| Code | Question | Answer |
|---|---|---|
| C001 | Among policies without claims, what is the first policy number if you sort the list alphabetically? | 9W8X086W2 |
| C002 | What is the VIN of the vehicle with no coverages? | TI4BC28O8FALC78C3 |
| C003 | How many policies have ever been written in July? | 3 |
Series D: Simple aggregations
| Code | Question | Answer |
|---|---|---|
| D001 | How many policies expired in 2017? | 1 |
| D002 | How many policies had at least 1 day of exposure in 2017? | 5 |
| D003 | How many policies were in force as of the end of 2020-Q3? | 2 |
| D004 | How many vehicles are not from Mazda? | 34 |
| D005 | How many claims in 2022 exceeded $15,000? | 2 |
| D006 | What is the largest average reported loss per claim per car make? | $10,715.03 |
| D007 | Which policy has the most claims? | 2E2C545I0 |
Series E: More aggregations
| Code | Question | Answer |
|---|---|---|
| E001 | What is the maximum number of coverages a vehicle can have? | 5 |
| E002 | What is the sum of 2023 reported losses for 2010 vehicles? | $66,795.32 |
| E003 | What is the average number of claims in 2023 for all 2001 vehicles? | 3.75 |
| E004 | What is the average number of claims in 2023 for 2001 vehicles with at least 1 claim? | 7.5 |
| E005 | What is the average reported loss amount in 2023 for liability claims? | $8,410.33 |
| E006 | What is the excess of written premiums over all reported losses for policies/vehicles written in 2023 in Ontario? | $30,882.15 |
| E007 | How many policies have at least one insured car manufactured before 2000 and one insured car manufactured after 2009? | 3 |